现有连接被远程主机强制关闭
请帮助我解决这个问题：
此代码工作正常，除了控件进入“NOT EXSISTS”的 IF 块内的情况，然后执行此块后运行的任何查询都会导致 sql 连接强制关闭，尽管结果从该代码块的运行来看是正确的。运行后我无法运行其他查询。
IF(@Mode='Get')
BEGIN
IF(@Field='manager')
BEGIN
DECLARE @UserUserName NVARCHAR(250)
DECLARE @UserID AS VARCHAR(50)
SELECT @UserUserName=CAST(Value AS NVARCHAR(250)) FROM dbo.UserProperties WHERE [Key]=@Key AND Field='manager'
IF(NOT EXISTS(SELECT * FROM dbo.Users WHERE UserUsername=@UserUserName) OR @UserUserName IS NULL )
BEGIN
SELECT @UserID = dbo.fnGetManagerId(CAST(@Key AS INT)) -- numeric
SELECT @UserUserName=UserUsername FROM dbo.Users WHERE UserID=@UserID
END
SELECT UserName AS Value FROM users WHERE UserUsername=@UserUserName
END
ELSE
BEGIN
SELECT Value FROM dbo.UserProperties WHERE [Key]=@Key AND Field=@Field
END
END
Please help me with this:
This code works fine except for cases in which the control goes inside the IF block of "NOT EXSISTS", then any query run after the execution of this block causes the sql connection to forcibly get closed, although the results from the running of this code block are correct. I can not run other queries after running it.
IF(@Mode='Get')
BEGIN
IF(@Field='manager')
BEGIN
DECLARE @UserUserName NVARCHAR(250)
DECLARE @UserID AS VARCHAR(50)
SELECT @UserUserName=CAST(Value AS NVARCHAR(250)) FROM dbo.UserProperties WHERE [Key]=@Key AND Field='manager'
IF(NOT EXISTS(SELECT * FROM dbo.Users WHERE UserUsername=@UserUserName) OR @UserUserName IS NULL )
BEGIN
SELECT @UserID = dbo.fnGetManagerId(CAST(@Key AS INT)) -- numeric
SELECT @UserUserName=UserUsername FROM dbo.Users WHERE UserID=@UserID
END
SELECT UserName AS Value FROM users WHERE UserUsername=@UserUserName
END
ELSE
BEGIN
SELECT Value FROM dbo.UserProperties WHERE [Key]=@Key AND Field=@Field
END
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我似乎记得看过有关 SQL2000 中标量 UDF 的错误的知识库文章，这些错误可能会导致访问冲突，也许您已经遇到了其中之一？我首先将强制转换与 UDF 参数列表分开，并在另一行中进行
替换，
然后
如果问题仍然存在，则一次注释掉一行，直到找到罪魁祸首。
我会查看 SQL Server 错误日志。您还可以使用 SQL Profiler 来跟踪用户错误消息。严重性足够高的错误将自动关闭连接。
I seem to remember seeing kb articles about bugs with scalar UDFs in SQL2000 that could cause access violations maybe you've hit one of those? I'd first separate the cast from the UDF parameter list and do it in another line
Replace
With
then if the problem persists comment out a line at a time until you find the culprit.
I'd look in the SQL Server Error Logs. Also you can use SQL Profiler to trace user error messages. A high enough severity error would close the connection automatically.
我认为你的 IF 语句是不正确的。尝试：
I believe that your IF statement is incorrect. Try: