473,836 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Strange OSQL behavoir.

Greetings All, I was hoping that someone out there has run into this
issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a
the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc
runs successfully from within Query analyzer and this holds true on the
following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I
try to call the proc from the OSQL prompt on the 2000 machines the proc
dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?

Further information:

The proc is using dynamic sql and sp_executesql is being used within
the proc. The dynamic sql string created is very close to the 4000
byte limit imposed by the nvarhcar datatype, I have the variable
@v_SQLString defined as varchar(4000).

It seems that this proc is dying somewhere within the portion of the
proc where this 4000 byte dynamic sql string is being executed. There
are many dynamic sql strings in this proc and several execute just fine
before getting to this big one.

It is my opinion that for some reason the 2000 platforms are seeing the
dynamic sql string > 4000 bytes and this is causing the query to fail.
The XP machine does not do this and the query executes just fine from
both Query Analyzer and the OSQL utility??
I apologize if this is nebulous and I will be willing to provide any
further information.

Let me know if you can help.

TFD

Jul 23 '05 #1
24 4757

"LineVoltageHal ogen" <tr************ ****@yahoo.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
Greetings All, I was hoping that someone out there has run into this
issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a
the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc
runs successfully from within Query analyzer and this holds true on the
following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I
try to call the proc from the OSQL prompt on the 2000 machines the proc
dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?

Further information:

The proc is using dynamic sql and sp_executesql is being used within
the proc. The dynamic sql string created is very close to the 4000
byte limit imposed by the nvarhcar datatype, I have the variable
@v_SQLString defined as varchar(4000).

It seems that this proc is dying somewhere within the portion of the
proc where this 4000 byte dynamic sql string is being executed. There
are many dynamic sql strings in this proc and several execute just fine
before getting to this big one.

It is my opinion that for some reason the 2000 platforms are seeing the
dynamic sql string > 4000 bytes and this is causing the query to fail.
The XP machine does not do this and the query executes just fine from
both Query Analyzer and the OSQL utility??
I apologize if this is nebulous and I will be willing to provide any
further information.

Let me know if you can help.

TFD


Since the procedure executes entirely on the server, it's not obvious (to
me) why the client OS would matter. Having said that, you mentioned there
are errors, but you didn't say what they are - can you post them, perhaps
with the corresponding lines of code from the proc (simplified if
necessary)? It would also be useful to see the osql.exe command line,
especially if you're passing parameters to it.

One possibility is that the errors are caused by SET options being different
between QA and osql.exe, but that's just a guess - you can use DBCC
USEROPTIONS to investigate. It seems unlikely that a different OS would
result in different connection settings, though, unless you're doing
something like using ODBC DSNs with different properties to connect.

Simon
Jul 23 '05 #2
I will re-run tonight when I get home and post the error. In the
meanwhile here is the osql command:
- osql -S bobo2 -U sa -P sa -i go_bobo2.sql -o go_bobo2.out
Here is the contents of go_bobo2.sql:
-
use mydb
go

truncate table table1
truncate table table2
go

select getdate()
go

exec uspDbLoad '8CD1F53B-A463-4E4A-B7A3-4B515B061729', N'bobo2',
N'OltpData', N'dbo'

select getdate()
go

select 'Table=> ' + cast(o.name as varchar) + ' Num Rows=> ' +
cast(rowcnt as varchar)
from sysobjects o inner join sysindexes i
on o.id = i.id
where i.rowcnt <> 0
and o.xtype = 'U'
go
As for the code, the procedure is probably 20 pages long. However, if
you feel that it would help I could send the code were OSQL thinks it
is crashing. Also the osql command listed above is called from within
a *.bat file.

TFD

Thanks for taking the time to work through this with me.

Jul 23 '05 #3
LineVoltageHalo gen (tr************ ****@yahoo.com) writes:
Greetings All, I was hoping that someone out there has run into this
issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a
the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc
runs successfully from within Query analyzer and this holds true on the
following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I
try to call the proc from the OSQL prompt on the 2000 machines the proc
dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?


The error message makes even less sense, if you don't post them. It's
essential for this sort of problem to post the error messages.

The one thing which is different with OSQL from Query Analyzer is that
OSQL by default runs with SET QUOTED_IDENTIFE R OFF. This can have an
effect if you working with indexed views or indexed computed columns.
Or, of course, if you are using "" to quote object names.

Use the -I option to enabled QUOTED_IDENTIFI ER from the command line.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

Simon Hayes wrote:
"LineVoltageHal ogen" <tr************ ****@yahoo.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc runs successfully from within Query analyzer and this holds true on the following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I try to call the proc from the OSQL prompt on the 2000 machines the proc dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?

Further information:

The proc is using dynamic sql and sp_executesql is being used within the proc. The dynamic sql string created is very close to the 4000
byte limit imposed by the nvarhcar datatype, I have the variable
@v_SQLString defined as varchar(4000).

It seems that this proc is dying somewhere within the portion of the proc where this 4000 byte dynamic sql string is being executed. There are many dynamic sql strings in this proc and several execute just fine before getting to this big one.

It is my opinion that for some reason the 2000 platforms are seeing the dynamic sql string > 4000 bytes and this is causing the query to fail. The XP machine does not do this and the query executes just fine from both Query Analyzer and the OSQL utility??
I apologize if this is nebulous and I will be willing to provide any further information.

Let me know if you can help.

TFD

Since the procedure executes entirely on the server, it's not obvious

(to me) why the client OS would matter. Having said that, you mentioned there are errors, but you didn't say what they are - can you post them, perhaps with the corresponding lines of code from the proc (simplified if
necessary)? It would also be useful to see the osql.exe command line, especially if you're passing parameters to it.

One possibility is that the errors are caused by SET options being different between QA and osql.exe, but that's just a guess - you can use DBCC
USEROPTIONS to investigate. It seems unlikely that a different OS would result in different connection settings, though, unless you're doing
something like using ODBC DSNs with different properties to connect.

Simon


Simon, I went ahead and ran the "dbcc useroptions" on all three
machines: XP PRO, 2000 PRO, and 2000 SERVER and they all returned the
same values. If it was not clear all three machines are running SQL
Server and the command was given to each database from that machine. I
managed to get more information regarding this problem. I manually
logged into osql with all the same flags as in the bat file, I then
manually initiated the command in the *.sql file:
exec uspDbLoad '8CD1F53B-A463-4E4A-B7A3-4B515B061729', N'bobo2',
N'OltpData', N'dbo'

and it ran successfully! I then tried to run the batch file again and
I get the same error:

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

-----------------------
2005-02-08 21:15:30.587

(1 row affected)
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 63 63 56 56

(1 row affected)
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
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.

-----------------------
2005-02-08 21:15:34.890

(1 row affected)

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

Please keep in mind that this error does not occur when I run the
stored proc from query analyzer and as I just discoverd it does not
occur when I manually log into osql and call the procedure just as it
is being called in the batch file, below is the output from running the
command manually at the osql prompt:

*************** *************** *************** *************** *************** **
1> exec uspDbLoad '8CD1F53B-A463-4E4A-B7A3-4B515B061729', N'',
N'OltpData, N'dbo'
2> go
DbId bFileIdbCurrent SizebMinimumSiz ebUsedPages bEstimatedPages
------b------b-----------b-----------b-----------b--------------
7b 2b 63b 63b 56b 56

(1 row affected)
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your
system
administrator.
1>
*************** *************** *************** *************** *************** **
Please let me know if this helps.

TFD

Jul 23 '05 #5

"LineVoltageHal ogen" <tr************ ****@yahoo.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
Greetings All, I was hoping that someone out there has run into this
issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a
the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc
runs successfully from within Query analyzer and this holds true on the
following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I
try to call the proc from the OSQL prompt on the 2000 machines the proc
dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?

Further information:

The proc is using dynamic sql and sp_executesql is being used within
the proc. The dynamic sql string created is very close to the 4000
byte limit imposed by the nvarhcar datatype, I have the variable
@v_SQLString defined as varchar(4000).

It seems that this proc is dying somewhere within the portion of the
proc where this 4000 byte dynamic sql string is being executed. There
are many dynamic sql strings in this proc and several execute just fine
before getting to this big one.

It is my opinion that for some reason the 2000 platforms are seeing the
dynamic sql string > 4000 bytes and this is causing the query to fail.
The XP machine does not do this and the query executes just fine from
both Query Analyzer and the OSQL utility??
I apologize if this is nebulous and I will be willing to provide any
further information.

Let me know if you can help.

TFD


This may or may not help (free advice is worth the price).

I would set the -a parameter (packet size) as large as your biggest batch
(i.e character count between GO's),

and see if that does anything.


Jul 23 '05 #6
I will try that and get back to you. Thank you for you input David.

TFD

Jul 23 '05 #7
LineVoltageHalo gen (tr************ ****@yahoo.com) writes:
Please keep in mind that this error does not occur when I run the
stored proc from query analyzer and as I just discoverd it does not
occur when I manually log into osql and call the procedure just as it
is being called in the batch file, below is the output from running the
command manually at the osql prompt:


Exactly how does that batch file look like?

The possibilibites I can think of are:

1) There are more than one procedure with the same name, but with
different owners, of which one works and the other not.
2) Same thing, but they are in different databases.
3) The batch file has some new line, or formatting issue which cases
one or more of the parameters to be lost.

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

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

David Rawheiser wrote:
"LineVoltageHal ogen" <tr************ ****@yahoo.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source OLTP DB to a the target Operational Data Store. This is still in development so
both DB's reside on the same machine for convenience. The stored proc runs successfully from within Query analyzer and this holds true on the following platforms: XP Pro, 2000 Pro, and 2000 Server. However, if I try to call the proc from the OSQL prompt on the 2000 machines the proc dies halfway through with errors that don't make sense and simply
aren't there (remember the proc can run from within query analyzer
successfully). Is it possible that 2000 Pro and 2000 Server act
differently when the OSQL utility is run over XP pro?

Further information:

The proc is using dynamic sql and sp_executesql is being used within the proc. The dynamic sql string created is very close to the 4000
byte limit imposed by the nvarhcar datatype, I have the variable
@v_SQLString defined as varchar(4000).

It seems that this proc is dying somewhere within the portion of the proc where this 4000 byte dynamic sql string is being executed. There are many dynamic sql strings in this proc and several execute just fine before getting to this big one.

It is my opinion that for some reason the 2000 platforms are seeing the dynamic sql string > 4000 bytes and this is causing the query to fail. The XP machine does not do this and the query executes just fine from both Query Analyzer and the OSQL utility??
I apologize if this is nebulous and I will be willing to provide any further information.

Let me know if you can help.

TFD

This may or may not help (free advice is worth the price).

I would set the -a parameter (packet size) as large as your biggest

batch (i.e character count between GO's),

and see if that does anything.


I tried changing the packet size as recommended and it did not work. I
did however get some usefull informatin in the output file for the
change:

=============== =============== =============== =============== ========
[ODBC Driver Manager] Driver's SQLSetConnectAt tr failed
Packet size change not honored by server, server size used
1> 2> 1> 2> 3> 4> 1> 2> 3>
-----------------------
2005-02-09 22:08:19.380

(1 row affected)
1> 2> 3> DbId FileId CurrentSize MinimumSize UsedPages
EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 63 63 56 56

(1 row affected)
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
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.
1> 2> 3>
=============== =============== =============== =============== ========

I am out of ideas on this one? It is so strange that it works on my XP
machine but not on the 2000 machines?

L

Jul 23 '05 #9
Here are the exact contents of the batch file:

SET SERVER=BOBO2
SET SA_PW=SA
SET DB_NM=Persisten tStore
SET OSQL_DIR=C:\Pro gram Files\Microsoft SQL Server\80\Tools \Binn

ECHO Creating database tables. Time %TIME%
"%OSQL_DIR%\osq l" -a 65535 -U sa -P "%SA_PW%" -S "%SERVER%" -d"%DB_NM%"
-i go_bobo2.sql -o go_bobo2.out
The file go_bobo2.sql looks like:

=============== =============== =============== =============== =
use persistentstore
go

truncate table PsOltpLoadMessa geLog
truncate table PsOltpLoadStats Log
go

select getdate()
go

exec uspPersistentSt oreLoad '8CD1F53B-A463-4E4A-B7A3-4B515B061729',
N'bobo2', N'support846_db ', N'dbo'
go

select getdate()
go

select 'Table=> ' + cast(o.name as varchar) + ' Num Rows=> ' +
cast(rowcnt as varchar)
from sysobjects o inner join sysindexes i
on o.id = i.id
where i.rowcnt <> 0
and o.xtype = 'U'
go
=============== =============== =============== =============== =

Erland, I really appreciate your taking the time to consider this
issue, in response to your three questions:

1) There are more than one procedure with the same name, but with
different owners, of which one works and the other not.
- This is not the case. There is only one database called
PersistentStore and I completely wipe it out each time I try to run the
load scripts. The wiping out actually drops the database and rebuilds
all the objects.

2) Same thing, but they are in different databases.
-- There is no other database referenced in the scripts.

3) The batch file has some new line, or formatting issue which cases
one or more of the parameters to be lost.
-- I checked and the file seems to be clean. I even recreated the
file and typed in the command again?

This is so strange ???

TFD

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1442
by: Markus Franz | last post by:
Hi. Today I created a script called load.py for using at the command line written in Python 2.3. This script should load as many websites as given on the comand line and print them with a seperation string to stdout. The loading was done in parallel. (I used processes for this.) The script was started by the following command:
3
14064
by: Ajay Garg | last post by:
I am running the following OSQL command and capturing the return code for the error .Whenver i have an error like server not exists or uable to login I get a return code of 1 for the %ERRORLEVEL%.However whenever I have an errorof a wrong dbcompatibility error the retun code id 1 even though sql returns an iformation message from OSQL that the right copatibilty levels are 60,70 and 80.How can i get OSQL to return the right return code...
2
13508
by: Bob | last post by:
Everybody, I've been doing a lot of on-line research and cannot find any reference to the exact problem I'm having. Let me preface this question with the fact that I'm coming from an Oracle background so my approach may not be the best way to tackle this. However, from the research I have done this approach seems reasonable. Also, I know about the undocumented procedure sp_MSforeachtable. That can give me a
2
18989
by: Murtix Van Basten | last post by:
Hi, I have dumped a very large database from mysql (using mysqldump program) as a raw sql file. The reason was, convert this database to a MSSQL database. Since mysqldump creates the file as raw sql file with the database-table structures and the data in it, I thought using OSQL command line utilities should work to out this whole database in MSSQL server. I have run this command from command line:
1
3335
by: Alan Zhong | last post by:
hi, i had been removed the row counts and the column spaces... but i failed to remove the extra tabs between rows of data return from the osql output. how to detect the row delimiter? i noticed i can use bcp to have a more decent output file, but i don't want to do too much of file read-write. the programming enviroment is quite easy to "capture" the output from the comand prompt. secondary, char(252), 253, 254, 255 is not being read...
2
4958
by: second714 | last post by:
Hi, I have several big tables with rows more than 25 mil rows and to update/delete/insert data in these tables, it can take minutes. I use BULK Insert/DELETE/Update with osql. While I run one of these updates, if I try to select, it seems like both read and write get locked. Shouldn't SQL resolve this kind of locking? I left these to see if it gets resolved but both never returned.
1
2017
by: john_20_28_2000 | last post by:
Will osql product an SQL backup? By this I mean a text file with the transact-sql in it so I can use it to recreate the database elsewhere? Thank you.
11
3636
by: gopal | last post by:
Hi, I am trying to call the OSQL utility from my C# console application and i am having problems i have the following code In Main method ProcessStartInfo psi = new ProcessStartInfo("osql",""); psi.RedirectStandardInput = false;
7
2286
by: gopal | last post by:
Hi, I have a console application whose purpose is to run the OSQL utility from my console application. When my application is run, the OSQL utility is started and it has to prompt for Database UserName & Password .
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10844
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5649
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4452
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3115
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.