Strange OSQL behavoir.

[LineVoltageHalogen]

Re: Strange OSQL behavoir.

Simon, I will try your suggestion and bet back to you with the results.
Please read my post to Erland and let me know if that sheds any light
on things, also let me know if you would like me to email the Word
document to you that has the image of the garbled data.

TFD

[Erland Sommarskog]

Re: Strange OSQL behavoir.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> Erland, the stored proc that is bombing out when run from the *.bat
> file is rather larger, especially some of the dynamic sql contained
> within it. The errors seen above are similar to ones I have seen in
> the past when the variable which holds the dynamic sql string isn't
> large enough. The dynamic sql that is bombing out is just on the cusp
> of the 4000 byte nvarchar limit[/color]

You had three error messages:

Msg 515, Level 16, State 2, Server BOBO2, Procedure
uspPsOltpLoadPr oductRevenues, Line 802
Cannot insert the value NULL into column 'PPRIdPK', table
'PersistentStor e.dbo.PsProduct Revenue'; column does not allow nulls.
INSERT fails.

Msg 241, Level 16, State 1, Server BOBO2, Procedure
uspPsOltpLoadPr oductRevenues, Line 826
Syntax error converting datetime from character string.

Msg 137, Level 15, State 2, Server BOBO2, Line 99
Must declare the variable '@v_Exi'.
The statement has been terminated.

The last message is from a batch of dynamic SQL which fails to compile.
This could be a truncation issue, the real name of the variable being
longer than @v_Exi.

However, the first two messages from a stored procedure. Not the
one you call in the batch script. I would guess that you call this
procedure from your uspPersistentSt oreLoad. Or do you call this
procedure through dynamic SQL? In such case that sounds a little
funny. I've rarely written dynamic SQL that calls stored procedures.
(Note that if you need to determine the name of the procedure
dynamically, you can say "EXEC @sp_name @par1, @par2...")

Particular the datetime error makes me suspect that you could have a
query that is not robust, and which will succeed or fail depending
on query plan.
[color=blue]
> and I was wondering if there could be something funky within the O.S.
> environment that could be causing each code point to be larger than two
> bytes?[/color]

Nah, but if you dynamic SQL includes a call to host_name() and the
the machine where this fails has a longer name, that the one where
it succeeds, that could be an explanation.

Anyway, if yuu believe that you are on the brink of overflowing, then
maybe you should split it up into two variables. Look at
http://www.sommarskog.se/dynamic_sql.html#use-which, to see how combine
EXEC() with sp_executesql to achieve this.





--
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: Strange OSQL behavoir.


Erland Sommarskog wrote:[color=blue]
> LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=green]
> > Erland, the stored proc that is bombing out when run from the *.bat
> > file is rather larger, especially some of the dynamic sql contained
> > within it. The errors seen above are similar to ones I have seen[/color][/color]
in[color=blue][color=green]
> > the past when the variable which holds the dynamic sql string isn't
> > large enough. The dynamic sql that is bombing out is just on the[/color][/color]
cusp[color=blue][color=green]
> > of the 4000 byte nvarchar limit[/color]
>
> You had three error messages:
>
> Msg 515, Level 16, State 2, Server BOBO2, Procedure
> uspPsOltpLoadPr oductRevenues, Line 802
> Cannot insert the value NULL into column 'PPRIdPK', table
> 'PersistentStor e.dbo.PsProduct Revenue'; column does not allow[/color]
nulls.[color=blue]
> INSERT fails.
>
> Msg 241, Level 16, State 1, Server BOBO2, Procedure
> uspPsOltpLoadPr oductRevenues, Line 826
> Syntax error converting datetime from character string.
>
> Msg 137, Level 15, State 2, Server BOBO2, Line 99
> Must declare the variable '@v_Exi'.
> The statement has been terminated.
>
> The last message is from a batch of dynamic SQL which fails to[/color]
compile.[color=blue]
> This could be a truncation issue, the real name of the variable being
> longer than @v_Exi.
>[color=green]
>> Erland, that is what I am thinking and certainly that is what is
>> within sql, it is being truncated as you imply. But why does it[/color][/color]
only[color=blue][color=green]
>> get truncated when it is called from within a batch file but not[/color][/color]
when[color=blue][color=green]
>> I manually call it from within osql or isql, or for that matter[/color][/color]
from[color=blue][color=green]
>> within QA. I know I keep saying this but it just blows my mind.[/color][/color]
[color=blue]
> However, the first two messages from a stored procedure. Not the
> one you call in the batch script. I would guess that you call this
> procedure from your uspPersistentSt oreLoad. Or do you call this
> procedure through dynamic SQL? In such case that sounds a little
> funny. I've rarely written dynamic SQL that calls stored procedures.
> (Note that if you need to determine the name of the procedure
> dynamically, you can say "EXEC @sp_name @par1, @par2...")
>[color=green]
>> uspPersistentSt oreLoad calls another procecure uspStxOltpEtl which
>> then calls the actual procedures that do the ETL process, there[/color][/color]
are[color=blue][color=green]
>> 35 procedures called from uspStxOltpEtl with one of them being
>> uspPsOltpLoadPr oductRevenues which is the procedure that is[/color][/color]
crapping[color=blue][color=green]
>> out, this procedure is rather large, about 2000 lines of code. It
>> should be noted that there are about 19 other procedures that get
>> called before this one and they all execute successfully, however[/color][/color]
they[color=blue][color=green]
>> are all small in comparison. No procedures are called with[/color][/color]
dynamic[color=blue][color=green]
>> sql.[/color][/color]
[color=blue]
> Particular the datetime error makes me suspect that you could have a
> query that is not robust, and which will succeed or fail depending
> on query plan.
>[color=green]
>> This is an interesting suggestion. Can you provide an example of
>> how a query could fail depending on the query plan. To be honest
>> Erland thes procs really are not doing any thing fancy, they are
>> mostly doing selects and insert with maybe some updates.[/color][/color]
[color=blue][color=green]
> > and I was wondering if there could be something funky within the[/color][/color]
O.S.[color=blue][color=green]
> > environment that could be causing each code point to be larger than[/color][/color]
two[color=blue][color=green]
> > bytes?[/color]
>
> Nah, but if you dynamic SQL includes a call to host_name() and the
> the machine where this fails has a longer name, that the one where
> it succeeds, that could be an explanation.
>
> Anyway, if yuu believe that you are on the brink of overflowing, then
> maybe you should split it up into two variables. Look at
> http://www.sommarskog.se/dynamic_sql.html#use-which, to see how[/color]
combine[color=blue]
> EXEC() with sp_executesql to achieve this.
>
>[color=green]
>> I appreciate your your taking the time to interact on this issue
>> Erland, you have been a great help. I think that this problem was
>> probably a good thing to happen to me/my company at this time.[/color][/color]
The[color=blue][color=green]
>> work I am doing is for a new software product, we currently have[/color][/color]
two[color=blue][color=green]
>> other software products and they too rely on SQL for a back end[/color][/color]
db.[color=blue][color=green]
>> I am new to the company and the dba before me used batch files to[/color][/color]
do[color=blue][color=green]
>> the buildout of the database piece of the software install. I[/color][/color]
think[color=blue][color=green]
>> that I am going to leverage C# and SQL DMO to do this task now,[/color][/color]
this[color=blue][color=green]
>> will get me out of using batch file.[/color][/color]
[color=blue][color=green]
>> Peace and Good day to you Erland.
>> TFD[/color]
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Strange OSQL behavoir.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue][color=green][color=darkred]
> >> uspPersistentSt oreLoad calls another procecure uspStxOltpEtl which
> >> then calls the actual procedures that do the ETL process, there
> >> are 35 procedures called from uspStxOltpEtl with one of them being
> >> uspPsOltpLoadPr oductRevenues which is the procedure that is crapping
> >> out, this procedure is rather large, about 2000 lines of code. It
> >> should be noted that there are about 19 other procedures that get
> >> called before this one and they all execute successfully, however
> >> they are all small in comparison. No procedures are called with
> >> dynamic sql.[/color][/color][/color]

I may be large, but you could do yourself a service and dig out the
lines where it's failing. You have the line numbers in the error messages.
[color=blue][color=green][color=darkred]
> >> This is an interesting suggestion. Can you provide an example of
> >> how a query could fail depending on the query plan. To be honest
> >> Erland thes procs really are not doing any thing fancy, they are
> >> mostly doing selects and insert with maybe some updates.[/color][/color][/color]

Here is a very quick example (I'm at work now):

SELECT * FROM tbl WHERE isdate(col) = 1 AND col > getdate()

Assume that col is a char(8) column and can contain values that are not
legal dates. This particular example may not fail, but my idea is that
the datetime error is due to something like this.




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

[Hugo Kornelis]

Re: Strange OSQL behavoir.

On 10 Feb 2005 19:02:36 -0800, LineVoltageHalo gen wrote:

(snip)[color=blue][color=green][color=darkred]
> >> Erland, that is what I am thinking and certainly that is what is
> >> within sql, it is being truncated as you imply. But why does it[/color][/color]
>only[color=green][color=darkred]
> >> get truncated when it is called from within a batch file but not[/color][/color]
>when[color=green][color=darkred]
> >> I manually call it from within osql or isql, or for that matter[/color][/color]
>from[color=green][color=darkred]
> >> within QA. I know I keep saying this but it just blows my mind.[/color][/color][/color]

Hi TFD,

Wild guess to follow:

Various settings (like ANSI-settings and such) have a default at server-,
database-, or user-level, but can be overriden. Many clients (like QA)
will automatically override these settings with their own defaults.

Could it be possible that an option that controls the formatting of some
pieces in your dynamic SQL is different? If one formatting option uses
more space than another one, this might explain why the string is
truncated in one case but not in another case.

You are using dynamic SQL, right? Or have I gotten some threads mixed up?

[color=blue][color=green]
>> Particular the datetime error makes me suspect that you could have a
>> query that is not robust, and which will succeed or fail depending
>> on query plan.
>>[color=darkred]
> >> This is an interesting suggestion. Can you provide an example of
> >> how a query could fail depending on the query plan. To be honest
> >> Erland thes procs really are not doing any thing fancy, they are
> >> mostly doing selects and insert with maybe some updates.[/color][/color][/color]

With the above theory, another explanation for the datetime error is
possible. If you build datetime constants in your dynamic SQL and rely on
implicit formatting, they could be used in a format that SQL Server won't
recognise. The date constant '03/04/2005' can be either March 4th or April
3rd, depending on regional settings. If you build a date as dd/mm/yyyy but
SQL Server thinks it's mm/dd/yyyy, then you'll get an error when dd > 12.

Check how you build your dynamic SQL. If you use a date constant anywhere,
make sure that it's in either of these three formats, as these are the
only formats that are guaranteed unambiguous:
* yyyymmdd (date only)
* yyyy-mm-ddThh:mm:ss (date plus time)
* yyyy-mm-ddThh:mm:ss.ttt (ditto, including milliseconds)

If you build a date constant from a datetime variable, use CONVERT with a
style parameter of 112 (date only) or 126 (date plus time).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[LineVoltageHalogen]

Re: Strange OSQL behavoir.

Erland, I have decided to break up my SQL into two smaller units and
then combine them as decribed on your website. I think that this is a
good idea because the errors I keep getting strongly resemble those
that have popped up due to the sql string being beyound 4000 bytes. It
is my hope that breaking it up the strange behavoir will go away on the
Windows 2000 platform. With that said I am having a bit of a problem
breaking up the sql, here is the gist of what I need to do, it is a
curtailed version:

*************** *************** *
DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @v_OLTPQualifie dPath nvarchar(1000)
DECLARE @v_TotalRowsLoa ded integer
DECLARE @v_ExitStatus integer

SELECT @v_OLTPQualifie dPath = 'mydata.dbo.'

SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N' from ' + @v_OLTPQualifie dPath + 'customer
SELECT @v_TotalRowsLoa ded = @@ROWCOUNT,
@v_ExitStatus = @@ERROR '

EXEC('
EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@v_TotalRows Loaded integer OUTPUT'',
N''@v_ExitStatu s integer OUTPUT'', N''@v_OLTPQuali fiedPath
nvarchar(1000)' '
@v_TotalRowsLoa ded OUTPUT, @v_ExitStatus
OUTPUT, @v_OLTPQualifie dPath = ''' + @v_OLTPQualifie dPath + '''
')

*************** *************** *************** ******

Obviously sql1 and sql2 will be much larger. However, I am having a
problem getting this little snippet to run. I must be missing a quote
mark somewhere.
As you can see I am grabbing the exit status and error code so that I
can trap and act accordingly if an error took place. Do you see any
gross error in the code I have posted above?

Any help would be greatly appreciated.

TFD

[Erland Sommarskog]

Re: Strange OSQL behavoir.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> Erland, I have decided to break up my SQL into two smaller units and
> then combine them as decribed on your website. I think that this is a
> good idea because the errors I keep getting strongly resemble those
> that have popped up due to the sql string being beyound 4000 bytes.[/color]

It seemed to me quite clear that ther errors you got in that stored
procedure did not come from dynamic SQL.

Sure, rewriting is probably a good thing anyway, but I have the feeling
that you will have to dig into what is going on those lines anyway.
Finding errors in SQL code is not about voodo, it's about getting
your hands dirty.
[color=blue]
> Obviously sql1 and sql2 will be much larger. However, I am having a
> problem getting this little snippet to run. I must be missing a quote
> mark somewhere.[/color]

Not a quote. I reformatted the code, and suddenty the error was all
apparent:

DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @v_OLTPQualifie dPath nvarchar(1000)
DECLARE @v_TotalRowsLoa ded integer
DECLARE @v_ExitStatus integer

SELECT @v_OLTPQualifie dPath = 'mydata.dbo.'

SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N' from ' + @v_OLTPQualifie dPath + 'customer
SELECT @v_TotalRowsLoa ded = @@ROWCOUNT, @v_ExitStatus = @@ERROR '

EXEC('
EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@v_TotalRows Loaded integer OUTPUT'',
N''@v_ExitStatu s integer OUTPUT'',
N''@v_OLTPQuali fiedPath nvarchar(1000)' '
@v_TotalRowsLoa ded OUTPUT,
@v_ExitStatus OUTPUT,
@v_OLTPQualifie dPath = ''' + @v_OLTPQualifie dPath + '''
')

Do you spot it?

There is another problem here, though. The OUTPUT parameters will not
work well here, so you will have do:

INSERT #tmp (...)
EXEC ('DECLARE ....
EXEC sp_execuatesql ...
SELECT @out1, @out2')

This won't fly if you are using INSERT EXEC somewhere in the procedures.
And in any case you will be running the procedures in transaction
context. Hm, this you can avoid:

EXEC ('DECLARE ...
EXEC sp_executesql
INSERT #tmp (...) VALUES(@out1, @out2))

Yes, it's only getting uglier. In SQL 2005 there is nvarchar(MAX), and
you can use sp_executesql without restrictions.


--
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: Strange OSQL behavoir.

Erland, I got it to work. I had to create a global temp table because
othewise when I went to select from it outside of the EXEC() I was out
of scope. Here is what the code looks like now:

*************** *************** *************** ***********
DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @v_OLTPQualifie dPath nvarchar(1000)

SELECT @v_OLTPQualifie dPath = 'mydata.dbo.'
SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N' from ' + @v_OLTPQualifie dPath + 'customer'
EXEC('DECLARE @v_TotalRowsLoa ded integer
DECLARE @v_ExitStatus integer
create table ##tbOperationSt atus (rowsloaded integer,
exitstatus integer)
EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@v_OLTPQuali fiedPath nvarchar(1000)' ',
@v_OLTPQualifie dPath = ''' +
@v_OLTPQualifie dPath + '''
INSERT INTO ##tbOperationSt atus values (@@ROWCOUNT, @@ERROR)
'
)

select * from ##tbOperationSt atus
*************** *************** *************** ***********

Does there exist the concept of a global table variable that I could
implement instead of the temp table?
I guess there would be no benefit though, this table is only going to
hold one row of data that has only two attributes.

TFD

[LineVoltageHalogen]

Re: Strange OSQL behavoir.

I was missing a comma after: N''@v_OLTPQuali fiedPath nvarchar(1000)' ' ?

I think that was it because I got a new error after I put the comma in.

So I need to declare the variables @v_TotalRowsLoa ded and @v_ExitStatus

within the EXEC() to make this code snippet work? I am trying it now.

Thanks for your help

TFD

[Erland Sommarskog]

Re: Strange OSQL behavoir.

LineVoltageHalo gen (tropicalfruitd rops@yahoo.com) writes:[color=blue]
> Erland, I got it to work. I had to create a global temp table because
> othewise when I went to select from it outside of the EXEC() I was out
> of scope. Here is what the code looks like now:[/color]

A global temp table is overkill, and can even cause a mess as they
may not go way as you intended. Just create a regular temp table
outside the EXEC statement and you are fine.
[color=blue]
> Does there exist the concept of a global table variable that I could
> implement instead of the temp table?[/color]

You can have a permanent table that is keyed by @@spid. Sometimes this
is a good technique, but for this case it would be an overkill.




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