January 23, 2020 at 3:04 pm
I am trying to put the result of Stored Procedure execution into Temp Table:
SELECT *
INTO #tmpTable
FROM
OPENROWSET('SQLNCLI','Server=(local)\SQL2014;Trusted_Connection=yes;','EXEC myDB.dbo.sp_write_Data @Start_Date=N2020-01-01, @End_Date=N2020-01-22')
select * from #tmpTable
I received the following errors:
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg -1, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
It means I am doing something wrong. I replaced (local) by actual Server Name and got the same errors. Please tell me what am I doing wrong? Thank you
January 23, 2020 at 3:10 pm
Try replacing "(local)" with ".". Is (local)\SQL2014 the same instance you're running this code on?
John
January 23, 2020 at 3:34 pm
Yes it is
January 23, 2020 at 3:39 pm
OK, good. Did my suggestion work? Can I ask why you're using OPENROWSET instead of just running the EXEC statement?
By the way, I think you need to enclose your date values in quotes. If you're using OPENROWSET you'll also need to escape the quotes by doubling them, since the whole statement is already enclosed in quotes.
John
January 23, 2020 at 3:39 pm
Thank you John. I am wondering why should I specify my version of SQL Server or it should be the Database Name?
January 23, 2020 at 3:42 pm
Thank you John. I am wondering why should I specify my version of SQL Server or it should be the Database Name?
Why are you using OPENROWSET in this case?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 23, 2020 at 3:43 pm
Thank you John. I am wondering why should I specify my version of SQL Server or it should be the Database Name?
You don't need to specify the database name in the connection string, since you've provided the full path to the stored procedure in your EXEC statement. When you say version, I assume you mean the "SQL2014" in the connection string? That is, I assume, the name of the instance, so yes, you do need to specify that.
John
January 23, 2020 at 3:57 pm
Unfortunately it did not work.
I tried this way before:
SELECT *
INTO #tmpTable
FROM
EXEC iMIS_MCLE_Prod.dbo.sp_isg_write_Firm_Data @Start_Date=N2020-01-01, @End_Date=N2020-01-22
and I received this error:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'EXEC'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '-'.
January 23, 2020 at 4:01 pm
You need to create the temp table first, then use an INSERT...EXEC to insert the rows. That applies whether you use OPENROWSET or not. And don't forget to put those quotes in like I suggested earlier.
John
January 23, 2020 at 4:05 pm
I created temp table and put single quotations:
SELECT *
INTO #tmpTable
FROM
EXEC iMIS_MCLE_Prod.dbo.sp_isg_write_Firm_Data @Start_Date=N'2020-01-01', @End_Date=N'2020-01-22'
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'EXEC'.
January 23, 2020 at 4:07 pm
Use INSERT rather than SELECT ... INTO
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 23, 2020 at 4:25 pm
Insert INTO does work. Thank you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply