471,108 Members | 1,298 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

sp_executesql & Output Variables. One Ball#$#%% of a problem.

Greetings All, I have a very large query that uses dynamic sql. The
sql is very large and it requires it to be broken into three components
to avoid the nvarchar(4000) issue:

SET @v_SqlString(
N'')

SET @v_SqlString2(
N'')

SET @v_SqlString3(
N'')

The sql is large and I don't have a problem with that so I will not
post it. However, in the last string the very last statement looke
like:
SET @v_SqlString3(
N'
...
...
SELECT @v_TotalRowsLoaded = @@ROWCOUNT, @v_ExitStat =
@@ERROR
')
I want to catch this output and I am having problems, here is what my
execute looks like:

EXEC('DECLARE @v_TotalRowsLoaded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRowsLoaded integer OUTPUT'',
@v_TotalRowsLoaded OUTPUT,
N''@v_ExitStatus integer OUTPUT'',
@v_ExitStatus OUTPUT,
N''@v_OLTPQualifiedPath nvarchar(1000)'',
@v_OLTPQualifiedPath = ''' + @v_OLTPQualifiedPath + ''',
N''@v_LoadTime datetime'', @v_LoadTime =
''' + @v_LoadTime + '''
'
)
When I run it as is I am prompted with:

Server: Msg 119, Level 15, State 1, Line 126
Must pass parameter number 8 and subsequent parameters as '@name =
value'. After the form '@name = value' has been used, all subsequent
parameters must be passed in the form '@name = value'.
You are required to pass five "5" arguments.
Can anyone tell me why this is failing? What can I do?

Any help would be greatly appreciated.

Jul 23 '05 #1
7 7738
It isn't possible to exceed the 4000 character limit when calling
sp_executesql. I'm guessing your dynamic SQL is failing because it will
be truncated at 4000 characters.

If your code is that complex then I'm certain there must be a better
way than using sp_executesql. Why do you need to do this dynamically at
all? If dynamic code is unavoidable then have you considered using DTS,
..NET or some other host that will let you build dynamic code and handle
return values more easily? Maybe if you explain your goal someone can
give some better suggestions.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
I didnt' want to get into all of that but what I meant to say was that
the sql string you have to pass sp_executesql must be of type nvarchar
and nvarchar has a limit of 4000 characters. I have to use dynamic sql
for many reasons. 1.) I am am using linked servers and I have to pass
that link server name to the stored proc that is executing the sql
which requires that I used dynamic sql. 2.) The linked server is
connecting to Oracle and because of data issues on the Oracle end I
have to use OPENQUERY() as well. The overall process is an ETL
process and I am doign it via stored procs. The issue I mentioned
above may be happening because I think you cannot have an output
variable with EXEC(). As I have shown I am wrapping executesql with
EXEC() and I do need to grab the exit status and rowcount from the sql
wrapped by @v_SqlString + @v_SqlString2 + @v_SqlString3.

Thanks, TFD

Jul 23 '05 #3
LineVoltageHalogen (tr****************@yahoo.com) writes:
I want to catch this output and I am having problems, here is what my
execute looks like:

EXEC('DECLARE @v_TotalRowsLoaded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRowsLoaded integer OUTPUT'',
@v_TotalRowsLoaded OUTPUT,
N''@v_ExitStatus integer OUTPUT'',
@v_ExitStatus OUTPUT,
N''@v_OLTPQualifiedPath nvarchar(1000)'',
@v_OLTPQualifiedPath = ''' + @v_OLTPQualifiedPath + ''',
N''@v_LoadTime datetime'', @v_LoadTime =
''' + @v_LoadTime + '''
'
)
When I run it as is I am prompted with:

Server: Msg 119, Level 15, State 1, Line 126
Must pass parameter number 8 and subsequent parameters as '@name =
value'. After the form '@name = value' has been used, all subsequent
parameters must be passed in the form '@name = value'.
You are required to pass five "5" arguments.
The error message as such is self-explanatory. You have

@v_OLTPQualifiedPath = <value>, <value>

which is not legal.

But would fix this, you would run into more error messages. You have
declared the parameter list for sp_executesql to have one parameter,
and then you pass a whole bunch. That '@v_ExitStatus integer OUTPUT'
may look like a parameter declaration for you, but it's a value for
a non-existing parameter. The second parameter to sp_executesql must
include all parameters.
1.) I am am using linked servers and I have to pass that link server name
to the stored proc that is executing the sql which requires that I used
dynamic sql.


Hm, depends on how you use that linked server. Remember that a linked
server is really just an alias. Thus, you could use sp_addlinkedserver
to set your server name to point to the linked server you are about
to talk to.

But if you are into OPENQUERY and needs to parameterize that SQL, you
are in for dynamic SQL anyway.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Erland, thanks for the feedback. Please forgive my lack of
understanding but I am not sure if you are saying that I can do what I
want or if it is not possible? I guess what I am looking for is a bit
of guidance as to how this this thing should look, that is, to make it
work if at all possible?

Regards, TFD.

Jul 23 '05 #5
LineVoltageHalogen (tr****************@yahoo.com) writes:
Erland, thanks for the feedback. Please forgive my lack of
understanding but I am not sure if you are saying that I can do what I
want or if it is not possible? I guess what I am looking for is a bit
of guidance as to how this this thing should look, that is, to make it
work if at all possible?


I think it will work. David say that you cannot cram in more than
4000 characters to sp_executesql, but I he is wrong there. Look at this:

declare @sql1 nvarchar(4000),
@sql2 nvarchar(4000)
select @sql1 = 'SELECT ' + replicate(' ', 3890)
select @sql2 = replicate(' ', 3890) + '4711'
exec ('exec sp_executesql N''' + @sql1 + @sql2 + '''')

Your mistake was that you had spliced up the @params parameter into
several, and also mixed named and positional parameters with each other.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
So, is this the correct form?
EXEC('DECLARE @v_TotalRowsLoaded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRowsLoaded integer OUTPUT, @v_ExitStatus integer
OUTPUT,
@v_OLTPQualifiedPath nvarchar(1000)'', @v_OLTPQualifiedPath = ''' +
@v_OLTPQualifiedPath + ''',
@v_LoadTime datetime'', @v_LoadTime = ''' + @v_LoadTime + '''
''
'
)

Jul 23 '05 #7
LineVoltageHalogen (tr****************@yahoo.com) writes:
So, is this the correct form?
EXEC('DECLARE @v_TotalRowsLoaded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRowsLoaded integer OUTPUT, @v_ExitStatus integer
OUTPUT,
@v_OLTPQualifiedPath nvarchar(1000)'', @v_OLTPQualifiedPath = ''' +
@v_OLTPQualifiedPath + ''',
@v_LoadTime datetime'', @v_LoadTime = ''' + @v_LoadTime + '''
''
'
)


I guess that if it works, it is, and if it doesn't work, it is not. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bkr | last post: by
27 posts views Thread by Daniel Vallstrom | last post: by
30 posts views Thread by James Daughtry | last post: by
1 post views Thread by Matik | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.