问题描述
问候所有，我希望之前有人遇到过这个问题，可以为我解释一下这个问题。我有一个存储的
程序，它基本上是从源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
推荐答案
由于程序完全在服务器上执行，因此客户端操作系统的重要性并不明显（对于我来说）。话虽如此，你提到那里
是错误，但是你没有说出它们是什么 - 你可以发布它们，也许
来自相应的代码行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使用SET QUOTED_IDENTIFER OFF运行。如果您使用索引视图或索引计算列，这可能会产生
效果。
或者，当然，如果您使用的是"引用对象名称。
使用-I选项从命令行启用QUOTED_IDENTIFIER。
-
Erland Sommarskog ，SQL Server MVP，
联机丛书适用于SQL Server SP3
这篇关于奇怪的OSQL行为。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！