any idea what is wrong with this code? i am getting Msg 102, Level 15, State 1, Line 9 Incorrect syntax near '')'.

declare @t int = 1,  @p int = 1

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=.\sqlexpress;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData ''''' + cast(@t as varchar(10)) + ''''',''' + cast(@p as varchar(10)) ''')'
EXEC @sqlCommand

There are errors due to missing quotes and a plus sign, and you need parenthesis around the statement invoked with EXEC.  Try:

DECLARE @t int = 1,  @p int = 1;

DECLARE @sqlCommand varchar(1000);

SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=.\sqlexpress;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData ''''' + cast(@t as varchar(10)) + ''''',''''' + cast(@p as varchar(10)) + ''''''')'

EXEC (@sqlCommand);

thank you Dan,

DECLARE @Param1 as int= 2, @Param2 as int = 1, @Param3 as int = 4, @Param4 as datetime = '2015-06-30', @Param5 as char(1) = 'y'

DECLARE @sqlCommand varchar(1000);

SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=SQL01;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;
							EXEC crm.dbo.SpProcedureName ''''' + cast(@Param1 as varchar(10)) + ''''',''''' + cast(@Param2 as varchar(10)) +  ''''',''''' + 
																						 cast(@Param3 as varchar(10)) +  ''''',''''' + cast(@Param4 as varchar(20)) 
																						 +  ''''',''''' + cast(@Param5 as char(1))+''''''')'


EXEC (@sqlCommand);

i added a couple of more parameters and it is now giving me the error below. can you please help?

• Edited by PeaceOut 10 hours 17 minutes ago

thank you Dan,

DECLARE @Param1 as int= 2, @Param2 as int = 1, @Param3 as int = 4, @Param4 as datetime = '2015-06-30', @Param5 as char(1) = 'y'

DECLARE @sqlCommand varchar(1000);

SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=SQL01;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;
							EXEC crm.dbo.SpProcedureName ''''' + cast(@Param1 as varchar(10)) + ''''',''''' + cast(@Param2 as varchar(10)) +  ''''',''''' + 
																						 cast(@Param3 as varchar(10)) +  ''''',''''' + cast(@Param4 as varchar(20)) 
																						 +  ''''',''''' + cast(@Param5 as char(1))+''''''')'


EXEC (@sqlCommand);

i added a couple of more parameters and it is now giving me the error below. can you please help?

• Edited by PeaceOut Saturday, September 05, 2015 8:47 PM

Check whether the different collations caused this error.

https://social.msdn.microsoft.com/forums/sqlserver/en-US/4a1a7a2e-908d-49c6-ae0c-62de1d67b8f0/sqlncli-supplied-inconsistent-metadata

Please post the problem stored procedure, or a simplified version than reproduces the problem.

the stored procedure is returning different columns (same DATA TYPE) based on the parameters supplied. here is a simple stored procedure that can reproduce this issue. 

can anyone please help?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_test] @Param1 as int, @Param2 as int, @Param3 as int, @Param4 as datetime, @Param5 as char(1)
as
if 1=2
select cast(1 as varchar(10)) as t
else 
select cast(1 as varchar(10)) as p

Hi PeaceOut,

Thanks for posting the store procedure(SP) sample. Based on my test, the error can be reproduced using the SP as below.

SELECT *
FROM OPENROWSET('SQLNCLI','server=SQL01;Trusted_Connection=yes',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC  dbo.[usp_test] 1,1,1,1,1')

If the SP's body changes as below(one more column added), it would lead to another error "The metadata could not be determined...". This error is indeed related to your issue. Actually you're using linked server, this query would access your SQL Server via SQL Native client driver, though you may run this statement in your Local SQL Server(SQL01 in your case). The IF..ELSE block should return the same number of columns with same column names in the same order, otherwise the driver could not accept a non-deterministic result or a metadata inconsistent error when having different column names.

if
select cast(1 as varchar(10)) as t,2 as n
else 
select cast(1 as varchar(10)) as p

So change the test SP's body as below to fix this kind of issue.

if 1=2
select cast(1 as varchar(10)) as p
else 
select cast(1 as varchar(10)) as p

>thank you...i am not using a linked server, just trying to execute a stored procedure without declaring the results table.

I don't know where you learned such a technology to avoid declaring the results table, but it is obviously not a good practice. If what you expect is the SP's result, you don't need to declare a result table, the SP would return a result table, if you'd like to store the result from the SP for further process, use an INSERT INTO ..EXEC SP syntax.

EXEC [usp_test] 1,1,1,1,1

DECLARE @T TABLE(num int)
INSERT INTO @T EXEC [usp_test] 1,1,1,1,1
SELECT * FROM @T

If you have any question, feel free to let me know.

thanks Eric. the stored procedure i have calls another stored procedure which is why i am trying to use open row set.

i get the below error if i try to directly insert into a table by executing the stored procedure as you suggested.

An INSERT EXEC statement cannot be nested.

Hi PeaceOut,

The error seems to indicate that you are trying to "bubble up" the data from a chain of calling stored procedures. If in this case, please see
How to Share Data between Stored Procedures

Using Table-valued Parameters would be a better option.

If you have any question, feel free to let me know.

Hi PeaceOut,

Use table valued parameter(tvp) as a parameter of a stored procedure, since I have no idea on how is your stored procedure called, so tvp is just a potential option.

I'm kind of getting your scenario, are your trying to get the result of a store procedure within which the INSERT INTO ..EXEC is involved("An INSERT EXEC statement cannot be nested." indicates this). For this reason, you tried OPENROWSET but with no luck due to the reason I stated.

If my understanding on your scenario is correct, no option to modify the stored procedure makes it almost a mission impossible. Using the CLR in the link posted can be the last resort, however you would have to handle the meta non-deterministic and inconsistency issue and obvious it is very tough work.