奇怪的OSQL行为。 [英] Strange OSQL behavoir.
问题描述
问候所有，我希望之前有人遇到过这个问题，可以为我解释一下这个问题。我有一个存储的
程序，它基本上是从源OLTP DB到目标操作数据存储的一个迷你ETL。这仍然在开发中所以
为了方便起见，两个DB都驻留在同一台机器上。存储的proc
在查询分析器中成功运行，这在以下平台上是正确的：XP Pro，2000 Pro和2000 Server。但是，如果我试图在2000台机器上的OSQL提示符中调用proc，那么proc
会因为错误而中途死掉，而且只是
不存在（记得proc可以在查询分析器中运行
成功）。当OSQL实用程序通过XP专业版运行时，2000 Pro和2000 Server是否有可能以不同方式运行？
更多信息：
proc使用动态sql，而sp_executesql正在使用
proc。创建的动态sql字符串非常接近nvarhcar数据类型强加的4000
字节限制，我有变量
@v_SQLString定义为varchar（4000）。 />
似乎这个proc在
proc的部分内某处死亡，其中执行了这个4000字节的动态sql字符串。
是这个proc中的许多动态sql字符串，有几个执行得很好
才能到达这个大字符串。
它我认为，由于某种原因，2000平台正在看到
动态sql字符串> 4000字节，这导致查询失败。
XP机器不执行此操作，查询分析器和OSQL实用程序查询执行得很好吗？
如果这很模糊我道歉，我愿意提供任何
的进一步信息。
如果你让我知道可以帮忙。
TFD
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
推荐答案
" ; LineVoltageHalogen" < TR **************** @ yahoo.com>在消息中写道
news：11 ********************* @ g14g2000cwa.googlegro ups.com ...
"LineVoltageHalogen" <tr****************@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
问候所有，我希望之前有人遇到过这个问题，并且可以为我解释一下。我有一个存储的程序，它基本上是从源OLTP DB到目标操作数据存储的迷你ETL。这仍然在开发中，所以为了方便起见，两个DB都驻留在同一台机器上。存储的proc
在查询分析器中成功运行，这在以下平台上是正确的：XP Pro，2000 Pro和2000 Server。但是，如果我试图在2000台机器上的OSQL提示符下调用proc，那么proc
会因为错误而中途消失，而这些错误根本没有意义，而且根本就没有（记住proc可以在查询分析器中成功运行）。当OSQL实用程序在XP专业版上运行时，2000 Pro和2000 Server可能会有不同的行为吗？
进一步的信息：
proc是使用动态sql和proc中正在使用sp_executesql。创建的动态sql字符串非常接近nvarhcar数据类型强加的4000字节限制，我有变量
@v_SQLString定义为varchar（4000）。
似乎这个proc正在执行这个4000字节动态sql字符串的
proc部分内某处死亡。这个过程中有很多动态的sql字符串，有几个执行得很好
才能进入这个大的。
我认为由于某种原因，2000平台正在看到
动态sql字符串> 4000字节，这导致查询失败。
XP机器不执行此操作，查询分析器和OSQL实用程序的查询执行得很好??
如果这很模糊，我很抱歉，我愿意提供任何进一步的信息。
如果你能提供帮助，请告诉我。
TFD
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
由于程序完全在服务器上执行，因此客户端操作系统的重要性并不明显（对于我来说）。话虽如此，你提到那里
是错误，但是你没有说出它们是什么 - 你可以发布它们，也许
来自相应的代码行proc（简化为
必要）？查看osql.exe命令行，
也很有用，特别是如果你正在向它传递参数。
一种可能性是错误是由于QA和osql.exe之间的SET选项不同造成的，但这只是猜测 - 你可以使用DBCC
USEROPTIONS进行调查。但是，不同的操作系统似乎不太可能导致不同的连接设置，除非你正在使用具有不同属性的ODBC DSN进行连接。 br />
Simon
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
当我回到家并发布错误时，今晚我将重新开始运行。在
同时这里是osql命令：
- osql -S bobo2 -U sa -P sa -i go_bobo2.sql -o go_bobo2.out
这是go_bobo2.sql的内容：
-
使用mydb
go
截断表table1
截断表table2
go
select getdate（）
go
exec uspDbLoad''8CD1F53B-A463-4E4A-B7A3-4B515B061729''，N''bobo2''，
N''OltpData'' ，N''dbo''
选择getdate（）
go
select''Table => ; ''+ cast（o.name as varchar）+''Num Rows => ''
cast（rowcnt as varchar）
来自sysobjects o inner join sysindexes i
on o.id = i.id
其中i.rowcnt<> 0
和o.xtype =''U''
go
至于代码，程序大概是20页。但是，如果
你觉得它可以帮助我发送代码是OSQL认为它是/ b $ b崩溃了。上面列出的osql命令也是从
a * .bat文件中调用的。
TFD
感谢你花时间和我一起解决这个问题。
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.
LineVoltageHalogen（tr **************** @ yahoo .com）写道：
LineVoltageHalogen (tr****************@yahoo.com) writes:
问候所有，我希望之前有人遇到过这个问题，并且可以为我提供一些启示。我有一个存储的程序，它基本上是从源OLTP DB到目标操作数据存储的迷你ETL。这仍然在开发中，所以为了方便起见，两个DB都驻留在同一台机器上。存储的proc
在查询分析器中成功运行，这在以下平台上是正确的：XP Pro，2000 Pro和2000 Server。但是，如果我试图在2000台机器上的OSQL提示符下调用proc，那么proc
会因为错误而中途消失，而这些错误根本没有意义，而且根本就没有（记住proc可以在查询分析器中成功运行）。当OSQL实用程序通过XP专业版运行时，2000 Pro和2000 Server可能会有不同的行为吗？
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?
如果错误信息更有意义，你不发布它们。发布错误消息对于这类问题至关重要。
与查询分析器的OSQL不同的一点是
默认情况下，OSQL使用SET QUOTED_IDENTIFER OFF运行。如果您使用索引视图或索引计算列，这可能会产生
效果。
或者，当然，如果您使用的是"引用对象名称。
使用-I选项从命令行启用QUOTED_IDENTIFIER。
-
Erland Sommarskog ，SQL Server MVP， es****@sommarskog.se
联机丛书适用于SQL Server SP3
http：/ /www.microsoft.com/sql/techinf...2000/books.asp
这篇关于奇怪的OSQL行为。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
