生产中的服务器端 Javascript 无法打开与 SQL2008 命名实例的连接
我有一个生产站点，多年来一直使用名为 MDWDATA 的服务器上的 SQL Server 2000 默认实例。 TCP 端口 1433 和命名管道已在那里启用。我的目标是让这个 Web 应用程序与升级到 SQL Server 2008 的数据库副本一起使用。我在名为 DEVMOJITO 的服务器上安装了带有 SP1 的 SQL2008，并使用各种 VB6 桌面程序测试了新数据库，这些程序在客户端-服务器方式和网站本身的某些部分可以在驻留在 SQL2008 命名实例上的升级数据库上正常工作。因此，虽然我很高兴数据库升级似乎很好，但该网站的一部分因以下错误而失败：命名管道提供程序：无法打开与 SQL Server [1231] 的连接。 我认为此错误具有误导性。我在生产站点使用的 SQL2000 实例上禁用了命名管道，重新启动了 SQL，所有 ASP 代码仍然可以正常工作（此外，我们在数据库服务器和面向公众的 Web 服务器上的这些 Web 虚拟目录之间有防火墙。
所有代码在产品和开发站点上都是相同的，除了在开发站点上我试图连接到升级后的数据库
我知道有很多东西需要检查我一直在寻找，但这里有一些我可以提供的东西。来帮助你帮助我：
失败的代码是服务器端Javascript，改编自多年前Brent Ashley的“Javascript远程脚本（JSRS）”代码包。它以类似 AJAX 的方式运行，将请求发送回不同的 ASP 页面，然后处理回调。我认为这里要指出的关键是我如何更改与数据库的连接：（我无法在此处设置 Javascript 格式！）
函数setDBConnect(数据源) { var strConnect； //ADO连接字符串 //strConnect = "DRIVER=SQL Server;SERVER=MDWDATA;UID=uname;PASSWORD=x;DATABASE=StagingMDS;";
strConnect = "提供商=SQLNCLI10;服务器=DEVMOJITO\MSSQLSERVER2008;Uid=uname;Pwd=x;DATABASE=StagingMDS;"; 返回 strConnect; }函数serializeSql（sql，数据源） { var conn = new ActiveXObject("ADODB.Connection"); var ConnectString = setDBConnect(数据源); conn.Open( ConnectString ); var rs = conn.Execute( sql );
请注意连接字符串的不同之处。我认为这可能是问题所在，但我不知道该怎么办。我很惊讶返回的错误说涉及“命名管道”，因为我真的想使用 TCP。这里的连接字符串语法与在使用 VBScript 的网站的不同部分成功使用的语法相同，我将在此处粘贴该语法以显示：
if DataBaseConnectionsAreNeeded(strScriptName) then
dim strWebDB
Set objConn = Server.CreateObject("ADODB.Connection")
if IsProductionWeb() Then
strWebDB = "DATABASE=MDS;SERVER=MDWDATA;DRIVER=SQL Server;UID=uname;PASSWORD=x;"
end if
if IsDevelopmentWeb() Then
strWebDB = "Provider=SQLNCLI10;Server=DEVMOJITO\MSSQLSERVER2008;Database=StagingMDS;UID=uname;PASSWORD=x;"
end if
objConn.ConnectionString = strWebDB
objConn.ConnectionTimeout = 30
objConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = objConn
此代码适用于 prod 和 dev 虚拟目录以及网络其他部分的其他代码，其中正确使用 ASP.NET 对两个数据库进行工作。每台服务器上都启用了命名管道和 TCP。我不明白管道使用的字符串，但我始终使用默认值。
我想知道为什么上面的 Javascript 调用会导致使用命名管道而不是 TCP。任何想法将不胜感激。
I've got a production site that has been working for years with a SQL Server 2000 default instance on server named MDWDATA. TCP port 1433 and Named Pipes are enabled there. My goal is to get this web app working with a copy of the database upgraded to SQL Server 2008. I've installed SQL2008 with SP1 on a server called DEVMOJITO and tested the new database using various VB6 desktop programs that exercise various stored procs in a client-server fashion and parts of the website itself work fine against the upgraded database residing on this named instance of SQL2008. So, while I am happy that the database upgrade seems fine there is a part of this website that fails with this Named Pipes Provider: Could not open a connection to SQL Server [1231]. I think this error is misleading. I disabled Named Pipes on the SQL2000 instance used by the production site, restarted SQL and all the ASP code still continued to work fine (plus we have a firewall between both database servers and these web virtual directories on a public facing webserver.
URL to my production virtual directory which demos the working page:
URL to my development v-directory which demos the failing page:
All the code is the same on both prod and dev sites except that on dev I'm trying to connect to the upgraded database.
I know there are dozens of things to check which I've been searching for but here are a few things I can offer to help you help me:
The code that is failing is server-side Javascript adapted from Brent Ashley's "Javascript Remote Scripting (JSRS)" code package years ago. It operates in an AJAX-like manner by posting requests back to different ASP pages and then handling a callback. I think the key thing to point out here is how I changed the connection to the database: (I cannot get Javascript to format right here!)
function setDBConnect(datasource)
{
var strConnect; //ADO connection string
//strConnect = "DRIVER=SQL Server;SERVER=MDWDATA;UID=uname;PASSWORD=x; DATABASE=StagingMDS;";
strConnect = "Provider=SQLNCLI10;Server=DEVMOJITO\MSSQLSERVER2008;Uid=uname;Pwd=x;DATABASE=StagingMDS;";
return strConnect;
}function serializeSql( sql , datasource)
{
var conn = new ActiveXObject("ADODB.Connection");
var ConnectString = setDBConnect(datasource);
conn.Open( ConnectString );
var rs = conn.Execute( sql );
Please note how the connection string differs. I think that could be the problem but I don't know what to do. I am surprised the error returned says "named pipes" was involved because I really wanted to use TCP. The connection string syntax here is the same as used successfully on a different part of the site which uses VBScript which I'll paste here to show:
if DataBaseConnectionsAreNeeded(strScriptName) then
dim strWebDB
Set objConn = Server.CreateObject("ADODB.Connection")
if IsProductionWeb() Then
strWebDB = "DATABASE=MDS;SERVER=MDWDATA;DRIVER=SQL Server;UID=uname;PASSWORD=x;"
end if
if IsDevelopmentWeb() Then
strWebDB = "Provider=SQLNCLI10;Server=DEVMOJITO\MSSQLSERVER2008;Database=StagingMDS;UID=uname;PASSWORD=x;"
end if
objConn.ConnectionString = strWebDB
objConn.ConnectionTimeout = 30
objConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = objConn
This code works in both prod and dev virtual directories and other code in other parts of the web which use ASP.NET work against both databases correctly. Named pipes and TCP are both enabled on each server. I don't understand the string used by the Pipes but I am using the defaults always.
I wonder why the Javascript call above results in use of named pipes instead of TCP. Any ideas would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我为实现此目的所做的工作总结：
在连接字符串中添加一个额外的斜杠，因为这是服务器端 Javascript：
<块引用>
服务器=tcp:DEVMOJITO\MSSQLSERVER2008,1219;
显式编码 tcp: 作为协议前缀和端口 1219。我了解到默认情况下 SQL 命名实例使用动态端口。我最终将其关闭，并随意选择了端口 1219，这是在我关闭它之前动态选择的。可能还有其他方法可以让这部分工作。
最后，我发现需要将 SET NOCOUNT ON 添加到正在调用的存储过程中。否则，症状是消息：“对象关闭时不允许操作”。
Summary of what I did to get this working:
Add an extra slash to the connection string since this is server-side Javascript:
Explicitly code tcp: as a protocol prefix and port 1219. I learned that by default a named instance of SQL uses dynamic porting. I ended up turning that off and chose, somewhat arbitrarily, the port 1219, which dynamic had chosen before I turned it off. There are probably other ways to get this part working.
Finally, I discovered that SET NOCOUNT ON needed to be added to the stored procedure being called. Otherwise, the symptom is the message: "Operation is not allowed when the object is closed".