HI Experts !
I am trying to Insert the Records from Stored procedure to #TempTable . Finding the error.
The requirement is to create a stored procedure and insert the records by Passing the date parameters @StartDate1 & @EndDate1. Please help me out.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 201, Level 16, State 4, Procedure TEST1, Line 0
Procedure or function 'TEST1' expects parameter '@StartDate1', which was not supplied
CREATE PROCEDURE TEST1
@StartDate1 DATETIME OUTPUT,
@EndDate1 DATETIME OUTPUT
AS
DECLARE @query nvarchar(max)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @ProcessDate1 DATETIME
DECLARE @ProcessDate2 DATETIME
SET @StartDate=@StartDate1
SET @EndDate =@EndDate1
SET @ProcessDate1 = CONVERT(VARCHAR,dateadd(month,1+datediff(month,0,@StartDate),-1),101) --- Run on any date will calculate from first day to last date of the month.
SET @ProcessDate2 = CONVERT(VARCHAR,dateadd(month,1+datediff(month,0,@EndDate),-1),101) --- Run on any date will calculate from first day to last date of the month.
SET @query = ''SELECT
convert(varchar,min([Start date]),101) 'Range start date',
convert(varchar,max([Start date]),101) 'Range end date'
From Table
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
EXEC sp_serveroption 'COMPUTERNAME\MSSQLSERVER2012', 'DATA ACCESS', TRUE
SELECT *
INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=COMPUTERNAME\MSSQLSERVER2012;Trusted_Connection=yes;','EXEC DBName.[dbo].[TEST1]')
GO
[quote="ScottPletcher, post:3, topic:14139, full:true"]
You must pass in the dates to the proc. For example:
FROM OPENROWSET(..., ' EXEC DBName.[dbo].[TEST1] ''20181001'', ''20181005'' ')
[/quote]
Exactly I am missing the date parameters to pass runs fine...
I am trying to Pass declared parameters @startdate1 & Enddate1 instead of data ''20181001'', ''20181005''
but finding error message
DECLARE
@StartDate1 datetime,
@EndDate1 datetime
Set @StartDate1 = '01/01/2017'
Set @EndDate1 = '01/31/2017'
SELECT *
INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=COMPUTERNAME\MSSQLSERVER2012;Trusted_Connection=yes;','EXEC DBNAME.[dbo].[TEST1] @StartDate1,@EndDate1')
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@StartDate1".
A couple of different options, but these would require defining the temp table instead of relying on OPENROWSET. If possible, instead of using a procedure on the linked server - you could define the code you want to execute so you can control what is being returned.
Each option will require building the temp table first...then using INSERT INTO #temp {execute statement here};
With Option 3 - you could dynamically build the code to sent instead of using a stored procedure on the linked server which gives you the ability to control what columns are returned. This way - you would not have any issues if someone modified the stored procedure you are calling and any changes to data elements being returned are controlled by your procedure.
...
