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

[LineVoltageHalogen]

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_TotalRowsLoa ded = @@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_TotalRowsLoa ded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRows Loaded integer OUTPUT'',
@v_TotalRowsLoa ded OUTPUT,
N''@v_ExitStatu s integer OUTPUT'',
@v_ExitStatus OUTPUT,
N''@v_OLTPQuali fiedPath nvarchar(1000)' ',
@v_OLTPQualifie dPath = ''' + @v_OLTPQualifie dPath + ''',
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.

[David Portas]

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

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
--

[LineVoltageHalogen]

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

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

[Erland Sommarskog]

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

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> I want to catch this output and I am having problems, here is what my
> execute looks like:
>
> EXEC('DECLARE @v_TotalRowsLoa ded integer
> DECLARE @v_ExitStatus integer
> EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
> @v_SqlString3 + ''',
> N''@v_TotalRows Loaded integer OUTPUT'',
> @v_TotalRowsLoa ded OUTPUT,
> N''@v_ExitStatu s integer OUTPUT'',
> @v_ExitStatus OUTPUT,
> N''@v_OLTPQuali fiedPath nvarchar(1000)' ',
> @v_OLTPQualifie dPath = ''' + @v_OLTPQualifie dPath + ''',
> 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.[/color]

The error message as such is self-explanatory. You have

@v_OLTPQualifie dPath = <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.
[color=blue]
> 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.[/color]

Hm, depends on how you use that linked server. Remember that a linked
server is really just an alias. Thus, you could use sp_addlinkedser ver
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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[LineVoltageHalogen]

Re: sp_executesql &amp; Output Variables. One Ball#$#%% of a problem.

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.

[Erland Sommarskog]

Re: sp_executesql &amp; Output Variables. One Ball#$#%% of a problem.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[LineVoltageHalogen]

Re: sp_executesql &amp; Output Variables. One Ball#$#%% of a problem.

So, is this the correct form?


EXEC('DECLARE @v_TotalRowsLoa ded integer
DECLARE @v_ExitStatus integer
EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
@v_SqlString3 + ''',
N''@v_TotalRows Loaded integer OUTPUT, @v_ExitStatus integer
OUTPUT,
@v_OLTPQualifie dPath nvarchar(1000)' ', @v_OLTPQualifie dPath = ''' +
@v_OLTPQualifie dPath + ''',
@v_LoadTime datetime'', @v_LoadTime = ''' + @v_LoadTime + '''
''
'
)

[Erland Sommarskog]

Re: sp_executesql &amp; Output Variables. One Ball#$#%% of a problem.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> So, is this the correct form?
>
>
> EXEC('DECLARE @v_TotalRowsLoa ded integer
> DECLARE @v_ExitStatus integer
> EXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +
> @v_SqlString3 + ''',
> N''@v_TotalRows Loaded integer OUTPUT, @v_ExitStatus integer
> OUTPUT,
> @v_OLTPQualifie dPath nvarchar(1000)' ', @v_OLTPQualifie dPath = ''' +
> @v_OLTPQualifie dPath + ''',
> @v_LoadTime datetime'', @v_LoadTime = ''' + @v_LoadTime + '''
> ''
> '
> )[/color]

I guess that if it works, it is, and if it doesn't work, it is not. :-)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp