March 12, 2015 at 8:11 am
I have a process that keeps check on the row counts of about 100 stored procedures. The input parameters and "certified" row counts for all of the stored procedures are stored in a database. The process runs every day and executes all of the stored procedures using the parameters from the database with syntax below. The row count returned is compared against the known "certified" row count. If the counts are different, we receive an email alerting us that something has changed with the data or the sp query.
(This code is dynamically generated for all 100 + stored procedures)
SELECT COUNT(*) FROM OPENQUERY(SQLSERVER,'EXEC 'usp_HR_My_Stored_Procedure @inputparam1="12345",@inputparam2="12345"')
This worked well until I upgraded from SQL Server 2008 R2 to SQL Server 2014. Evidently Microsoft fixed this for me. The error below is now received anytime we attempt to execute a stored procedure with dynamic SQL through OPENQUERY.
The metadata could not be determined because statement 'EXEC (@sql_str)' in procedure 'usp_HR_My_Stored_Procedure ' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
I have read through several threads on this and have not been able to find a good solution. The stored procedures that are monitored change frequently, so it isn't reasonable to create tables with fixed column structures for all for all of the stored procs.
Any suggestions would be greatly apprecitated.
March 12, 2015 at 8:18 am
patterson1911 (3/12/2015)
I have a process that keeps check on the row counts of about 100 stored procedures. The input parameters and "certified" row counts for all of the stored procedures are stored in a database. The process runs every day and executes all of the stored procedures using the parameters from the database with syntax below. The row count returned is compared against the known "certified" row count. If the counts are different, we receive an email alerting us that something has changed with the data or the sp query.(This code is dynamically generated for all 100 + stored procedures)
SELECT COUNT(*) FROM OPENQUERY(SQLSERVER,'EXEC 'usp_HR_My_Stored_Procedure @inputparam1="12345",@inputparam2="12345"')
This worked well until I upgraded from SQL Server 2008 R2 to SQL Server 2014. Evidently Microsoft fixed this for me. The error below is now received anytime we attempt to execute a stored procedure with dynamic SQL through OPENQUERY.
The metadata could not be determined because statement 'EXEC (@sql_str)' in procedure 'usp_HR_My_Stored_Procedure ' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
I have read through several threads on this and have not been able to find a good solution. The stored procedures that are monitored change frequently, so it isn't reasonable to create tables with fixed column structures for all for all of the stored procs.
Any suggestions would be greatly apprecitated.
Let me get this straight: You invoke 100 procs via openquery against a remote server, returning all the results to be counted locally? OR is this just to get around the limitation that you can't write SELECT ... FROM EXEC usp_... ?
Gerald Britton, Pluralsight courses
March 12, 2015 at 8:26 am
The procs are actually local. I'm using a linked server to my local instance so that the returned row counts can easily be counted.
March 12, 2015 at 8:29 am
Thought so. I did this and it worked:
using tempdb
create proc usp_test as
set nocount on
select 1
select count(*) from openquery(sqlserver, 'exec tempdb.dbo.usp_test')
Gerald Britton, Pluralsight courses
March 12, 2015 at 8:45 am
Duplicate Post
March 12, 2015 at 8:48 am
The problem surfaces when the stored procedure uses dynamic SQL.
using tempdb
create proc usp_test as
set nocount on
declare @sql_str as varchar(15)
set @sql_str = 'SELECT 1 FROM MyTable'
exec (@sql_str)
------------------------------------------------------------------
SELECT COUNT(*) FROM OPENQUERY(SQLSERVER, 'exec tempdb.dbo.usp_test')
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply