May 15, 2012 at 12:04 pm
I have a sp and need to run it with parameter
exec spGetCategoriesByDocIDAsTable 811
This will return result like:
ID Category Checked
1 Category1 0
2 Category2 0
3 Category3 1
4 Category4 0
5 Category5 1
Now I and save the result into a temp table with some filtering, I am doing this way:
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC spGetCategoriesByDocIDAsTable 811')
It returns me error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC spGetCategoriesByDocIDAsTable 811". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
What's wrong with the query?
Thank you.
May 15, 2012 at 12:10 pm
This works for me though, but I don't know why the previous one is not working:
SELECT * INTO #Temp FROM
OPENROWSET(
'SQLNCLI',
'Server=localhost;Trusted_Connection=yes',
'EXEC msdb.dbo.sp_help_job')
SELECT * FROM #Temp
Drop table #Temp
Even after I removed the parameter in the previous query, it still won't work, so for sure it is not caused by introducing parameter
May 15, 2012 at 12:22 pm
halifaxdal (5/15/2012)
I have a sp and need to run it with parameterexec spGetCategoriesByDocIDAsTable 811
This will return result like:
ID Category Checked
1 Category1 0
2 Category2 0
3 Category3 1
4 Category4 0
5 Category5 1
Now I and save the result into a temp table with some filtering, I am doing this way:
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC spGetCategoriesByDocIDAsTable 811')
It returns me error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC spGetCategoriesByDocIDAsTable 811". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
What's wrong with the query?
Thank you.
Try this without the semicolon at the end:
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes',
'EXEC spGetCategoriesByDocIDAsTable 811')
May 15, 2012 at 12:49 pm
Thanks. Same error
May 15, 2012 at 1:35 pm
I figured it out, here is the little trick, I hope it's useful to others:
SELECT *
FROM OPENROWSET( 'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'SET FMTONLY OFF; SET NOCOUNT ON; exec Db_Name.dbo.spGetCategoriesByDocIDAsTable 811'
)
You must include the database name(here it is Db_Name) in the exec. and also both SET FMTONLY OFF and SET NOCOUNT ON
May 16, 2012 at 2:09 pm
Might I suggest another option?
CREATE TABLE #Temp
(
ResultColumn1FromProcedure INT,
ResultColumn2FromProcedure INT
-- etc.
);
INSERT INTO #Temp
(
ResultColumn1FromProcedure,
ResultColumn2FromProcedure
)
EXEC Db_Name.dbo.spGetCategoriesByDocIDAsTable
811;
The above will almost certainly perform better than using OPENROWSET and does not require you enable the "Ad Hoc Distributed Queries" option on your instance (OFF by default). Having this option disabled reduces attackable surface area as compared to when it is enabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2012 at 2:14 pm
Thank you for your input, much appreciated
November 2, 2015 at 1:17 pm
I had this problem in SQL 2008 R2 (local & remote). Just removing the USE statement fixed it for me.
Sincerely,
Daniel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply