SQLTeam.com | Weblogs | Forums

SQL code in a variable with more than 8000 characters


#1

Declare @strSQL VARCHAR(MAX)
--retrieve the sql code from a field from a table
select @strSQL = [QuerySQL] FROM [DatiQuerySql].[dbo].[_tabelle] WHERE ID_Tabella = 39
EXEC sp_serveroption 'NameLinkSrv', 'rpc out', true;
exec (@strSQL) AT NameLinkSrv;

this is the error:
OLE DB provider "OraOLEDB.Oracle" for linked server "NameLinkSrv" returned message "ORA-00905: parola chiave mancante".
OLE DB provider "OraOLEDB.Oracle" for linked server "NameLinkSrv" returned message "ORA-00905: parola chiave mancante".
Msg 7215, Level 17, State 1, Line 11
Could not execute statement on remote server 'NameLinkSrv'.

But if I write the code directly in the variable, this is executed without errors:
Declare @strSQL VARCHAR(MAX)
set @strSQL = ' my SQL code (the code can be greater than 8000 characters)'
EXEC sp_serveroption 'NameLinkSrv', 'rpc out', true;
exec (@strSQL) AT NameLinkSrv;

runs without errors


#2

translates to "keyword missing" in english, right? there's probably an error in your sql that causes the error . try adding
PRINT @SQL just before the EXEC (@strsql)

Oh, BTW, the variable @strSQL should be defined as nvarchar, not varchar