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?

The OLE DB provider "SQLNCLI11" for linked server "(null)" supplied inconsistent metadata for a column. The name was changed at execution time.

• 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