August 28, 2016 at 10:23 pm
Hi,
I'm trying to execute a script that uses OPENROWSET to execute a procedure which intern has another procedure.
But it fails with following error:
Msg 7357, Level 16, State 1, Line 28
Cannot process the object "ash_test". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Command:
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=vm01;Trusted_Connection=Yes;', 'ash_test')
AS derivedtbl_1
If I execute procedure without OPENROWSET, it works fine.
EXEC ash_test
Result:
str1str2
I am top most proc
ash_test -> Outermost procedure calls "inside_proc" to print a message on screen.
inside_proc -> generic procedure to print message on screen.
inside_proc:
CREATE PROCEDURE [dbo].[inside_proc]
@logmsg varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@str1 AS varchar(100)
DECLARE @logtime AS datetime;
SET @logtime = CONVERT(datetime,SYSDATETIME())
print '['+CONVERT(varchar,@logtime,120)+'] '+@logmsg;
SET NOCOUNT OFF;
END
GO
ash_test:
CREATE PROCEDURE [dbo].[ash_test]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@str1 AS varchar(100)=''
,@str2 AS varchar(100);
EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'
SET @str2 = 'I am top most proc';
select @str1 as str1,@str2 as str2
SET NOCOUNT OFF;
END
GO
I observed that, if I delete "print" statement from inside_proc procedure, it works fine.
Also, if I put any number of "print" statements in ash_test procedure, it works fine.
My question is, why OPENROWSET throws an error when there is "print" statement in a child procedure (inside_proc) and why not if its present in "ash_test" procedure.
And how do I resolve this error?
-Regards,
Ashok M
August 29, 2016 at 7:50 am
OPENROWSET returns a result set and only 1 result set. It doesn't know what to do with output from a print statement, which is a separate result set, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2016 at 6:23 pm
Thank you for the reply Jeff.
If OPENROWSET can return only one result set, why not return it from "inside_proc", which is first statement (EXEC statement) in "ash_test" procedure?
If I place EXEC statement at the end in ash_test procedure as follows, it works fine:
SET @str2 = 'I am top most proc';
select @str1 as str1,@str2 as str2
EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'
Even here, we have two result sets, but OPENROWSET works fine printing whats in @str2.
I'm bit confused on why it fails when EXEC statement is run first,but doesn't fail when runs at the end.
August 29, 2016 at 8:48 pm
Ah, my apologies. I didn't look at what your code was doing and went only by your description. I thought you were returning something besides the print statement.
To be honest, I'm not sure why you're bothering with OPENROWSET for this task. That, notwithstanding, I don't know the answer to your question.
I am curious, though. Instead of using a stored procedure for this, why not just use an iTVF (inline Table Valued Function) for this and avoid both nested stored procedures and OPENROWSET all together?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2016 at 10:48 pm
Hi Jeff,
Thank you for your reply.
I've a generic procedure which prints messages on screen and used in validation step in other procedures.
Ex: Print error message if querying table doesn't have data for a specified date.
I've to create a text file using output of a procedure, which intern uses above procedure for validation and displays required result if no validation errors. Now, I copy this data manually into a text file.
However, I wanted to create a generic script to copy output data into text file.
Sample code:
@tempsql AS varchar(max)
,@execstring AS varchar(max) = 'exec ash_test 160808;';
SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';
EXEC (@tempsql);
SELECT top 10 * FROM ##SwitchInput;
Later, I'm planning to use master..xp_cmdshell (BCP) to copy this data to a specific text file.
However, got stuck in the EXEC(@tempsql) step and am trying to resolve this error.
August 30, 2016 at 12:27 am
ash_m (8/29/2016)
Hi Jeff,Thank you for your reply.
I've a generic procedure which prints messages on screen and used in validation step in other procedures.
Ex: Print error message if querying table doesn't have data for a specified date.
I've to create a text file using output of a procedure, which intern uses above procedure for validation and displays required result if no validation errors. Now, I copy this data manually into a text file.
However, I wanted to create a generic script to copy output data into text file.
Sample code:
@tempsql AS varchar(max)
,@execstring AS varchar(max) = 'exec ash_test 160808;';
SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';
EXEC (@tempsql);
SELECT top 10 * FROM ##SwitchInput;
Later, I'm planning to use master..xp_cmdshell (BCP) to copy this data to a specific text file.
However, got stuck in the EXEC(@tempsql) step and am trying to resolve this error.
xp_CmdShell? BCP? Ok, now you're cooking with gas!
Why not just create a dynamic command that uses the QUERYOUT feature of BCP and execute that command using xp_CmdShell. It avoids the whole nasty thing with OPENROWSET.
For that matter, you could create a dynamic SQLCMD and execute that. It would allow for exquisite control over whether you append or overwrite the file by using the DOS redirection tokens of ">>" for append and ">" for overwrite instead of using the -o option. And ">>" creates a file if it doesn't already exist.
Once the dynamic SQLCMD statement is created, use xp_CmdShell to execute that. The side benefit here is that you can also direct the output of xp_CmdShell to a table (using INSERT/EXEC) so that you can read for errors and the whole shebang. I use that same method to simplify by-row error checking when using BULK_INSERT because the message capture feature creates comparatively cryptic output instead of what you would see on the screen. xp_CmdShell takes care of that little nuance by allowing you to capture what you would normally see on the screen into a table for interrogation.
As we used to say in the Navy, "Works fine, fails safe, and drains to the bilge." 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2016 at 6:03 pm
Hi Jeff,
Thank you for the suggestions.
In output file, I need to add header and footer.
By loading output of procedure in temp table, I'll derive the header.
Later, use the xp_cmdshell to copy file to destination.
Sample code:
IF object_id('tempdb..##SwitchInput') IS NOT NULL DROP TABLE ##SwitchInput;
DECLARE
@tempsql AS varchar(max)
,@execstring AS varchar(max) = 'exec ash_test;';
SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';
EXEC (@tempsql);
DECLARE
@hdrrow AS nvarchar(max) = 'SELECT '''
,@ftrrow AS varchar(50) = ' UNION ALL SELECT ''End of File'' AS rowdata'
,@delmtr AS nvarchar(10) = CHAR(124)
,@effdate AS date = '2016-8-30'
,@destloc AS varchar(max) = 'C:\SSData\'
,@filename AS varchar(250) = 'Strategy_Update_Output_';
SELECT @hdrrow = @hdrrow + name + @delmtr FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') ORDER BY column_id ASC;
SET @hdrrow = LEFT(@hdrrow,LEN(@hdrrow)-1) + ''' AS rowdata UNION ALL ';
DECLARE @selectstring AS varchar(max), @detailrow AS varchar(max), @trailerrow AS varchar(max), @dsql AS varchar(max), @bcpsql AS varchar(8000);
SELECT @selectstring = +'[' + name + ']' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') AND column_id = 1;
SELECT @selectstring = @selectstring + '+''' + @delmtr + '''+[' + name + ']' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') AND column_id > 1 ORDER BY column_id ASC;
SET @detailrow = 'SELECT ' + @selectstring + ' AS rowdata FROM ##SwitchInput';
SET @dsql = @hdrrow + @detailrow + @ftrrow
SELECT @bcpsql = 'EXEC master..xp_cmdshell ''BCP "'+REPLACE(@dsql,'''','''''')+'" queryout "'+@destloc+@filename+CONVERT(varchar,@effdate,112)+'_'+FORMAT(GETDATE(),'yyyyMMddHHmmss')+'.txt" -c -T -C 1252''';
EXEC (@bcpsql);
This script is generic and needs work with any procedure that produces output records.
For this procedure, I'm expecting following output:
str1|str2
|I am top most proc
End of File
August 30, 2016 at 9:01 pm
I've not done a detailed look at your code but it looks like you have pretty much everything covered except the possible problems that may occur with concurrency due to use of a global temp table. I also tell you that it will only work with code that is designed to return a single result set. Other than that, what's it not doing for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2016 at 9:24 pm
Hi Jeff,
Data doesn't get loaded in temp table, fails at EXEC(@tempsql) statement.
Error:
Cannot process the object "exec ash_test". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
ash_test procedure does 2 things:
1> Print validation messages
2> Print output data
If I'm guessing it correctly, it is trying to grab validation messages in resultset and fails with aforesaid error.
Nonetheless, I need to ignore validation messages and load "output data" into temp table.
Later, I'll add header and footer to it and send it to output text file.
August 30, 2016 at 9:53 pm
ash_m (8/30/2016)
Hi Jeff,Data doesn't get loaded in temp table, fails at EXEC(@tempsql) statement.
Error:
Cannot process the object "exec ash_test". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
ash_test procedure does 2 things:
1> Print validation messages
2> Print output data
If I'm guessing it correctly, it is trying to grab validation messages in resultset and fails with aforesaid error.
Nonetheless, I need to ignore validation messages and load "output data" into temp table.
Later, I'll add header and footer to it and send it to output text file.
I don't believe there's a way to do that with OPENROWSET or any other built in functionality. You either need to add a parameter to each stored procedure and modify the messaging in the proc to "switch off" the validation messages depending on the value of the parameter or you're going to need to do a bulk operation where you can capture the lines of output and decide if they're data or not, parse the lines of data, and then "warp" them into a file. In other words, because the mix of the presentation and data layers isn't option in your procs, you may have to resort to proverbial "screen scraping" on the return. The SQLCMD call through xp_CmdShell I was talking about is one way to do that but only if the returns aren't too wide. I don't remember what the max width of the output is without an eventual forced wrap but you may have to embed a DOS "MODE" command to change the "COLS" setting to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2016 at 12:32 am
Thank you Jeff.
I tweaked code a bit by introducing a view and it worked!
I created a view in ash_test procedure which holds the required output data (that way ignored validation messages).
Now, OPENROWSET is happy to load data from a view to temp table.
Rest of code was smooth enough to create a output text file with header and footer 🙂
Changed code as follows:
IF object_id('tempdb..##SwitchInput') IS NOT NULL DROP TABLE ##SwitchInput;
DECLARE
@tempsql AS varchar(max)
,@execstring AS varchar(max) = 'SELECT * FROM ash_vw';
SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';
EXEC (@tempsql);
ash_test:
CREATE PROCEDURE [dbo].[ash_test]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@str1 AS varchar(100)=''
,@str2 AS varchar(100);
EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'
SET @str2 = 'I am top most proc';
CREATE VIEW AS ash_vw
select @str1 as str1,@str2 as str2
SET NOCOUNT OFF;
END
GO
Now, when I execute ash_test procedure, validation errors could be seen on the screen, if any.
Once validation is passed, it creates a view with required data.
Later, execute aforesaid steps to create output file.
Thank you very much for your suggestions and timely response.:-)
August 31, 2016 at 12:39 am
ash_m (8/31/2016)
Thank you Jeff.I tweaked code a bit by introducing a view and it worked!
I created a view in ash_test procedure which holds the required output data (that way ignored validation messages).
Now, OPENROWSET is happy to load data from a view to temp table.
Rest of code was smooth enough to create a output text file with header and footer 🙂
Changed code as follows:
IF object_id('tempdb..##SwitchInput') IS NOT NULL DROP TABLE ##SwitchInput;
DECLARE
@tempsql AS varchar(max)
,@execstring AS varchar(max) = 'SELECT * FROM ash_vw';
SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';
EXEC (@tempsql);
ash_test:
CREATE PROCEDURE [dbo].[ash_test]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@str1 AS varchar(100)=''
,@str2 AS varchar(100);
EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'
SET @str2 = 'I am top most proc';
CREATE VIEW AS ash_vw
select @str1 as str1,@str2 as str2
SET NOCOUNT OFF;
END
GO
Now, when I execute ash_test procedure, validation errors could be seen on the screen, if any.
Once validation is passed, it creates a view with required data.
Later, execute aforesaid steps to create output file.
Thank you very much for your suggestions and timely response.:-)
Thanks for the feedback but that doesn't exactly meet the criteria of giving it "any" query (including a call to a sproc) though. It also doesn't resolve the concurrency issue if the view needs to change.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2022 at 4:16 pm
Thanks for share this discussion about the use of OPENROWSET. I was dealing with a similar problem, when i execute the OPENROWSET function i was receiving the same error message:
The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
I my case the store procedure that i was using doesn't call an another procedure but following their coments i traid to restrings the comments that my sp was returning as result of the rows affected in tables. I used the comand "SET NOCOUNT ON;" inside of my procedure and it works fine.
Thanks a lot for share!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply