473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 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 7859
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: cliverama | last post by:
help! fried brains.... asp calling a sqlserver7 stored proc which dynamically builds a sqlstatement & passes it to sp_executesql asp page gives the operation not allowed when object is closed...
1
by: Bkr | last post by:
Gurus, Here is what I ma trying to do. I have numeric expression stored in a table column. for e.g. @a + @b + @c. I supply values to the variables at run time and want them to be computed at run...
27
by: Daniel Vallstrom | last post by:
I'm having problems with inconsistent floating point behavior resulting in e.g. assert( x > 0.0 && putchar('\n') && x == 0.0 ); holding. (Actually, my problem is the dual one where I get...
30
by: James Daughtry | last post by:
char array; scanf("%19s", &array); I know this is wrong because it's a type mismatch, where scanf expects a pointer to char and gets a pointer to an array of 20 char. I know that question 6.12...
1
by: Matik | last post by:
Hi to all, Probably I'm just doing something stupid, but I would like you to tell me that (if it is so), and point the solution. There ist the thing: I' having a sp, where I call other sp...
1
by: satishchandrat | last post by:
Hi, This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure. I have my SQL string exeeding more than 4000...
1
by: phoenix917 | last post by:
I have been working on this code for 5 days... I have read multiple tutorials and looked at various code. This is my third forum try... :) My instructor refuses to help me. I think it's because...
5
by: Yash | last post by:
Hi, I am using SQL 2000 SP4. I have compared 2 scenarios: Scenario 1: insert into #bacs_report SELECT ..... WHERE <conditions>
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.