Server-side Javascript in production fails to open connection to a named instance of SQL2008

Question

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:

1. 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.

Answer 1

Summary of what I did to get this working:

1. Add an extra slash to the connection string since this is server-side Javascript:

   Server=tcp:DEVMOJITO\MSSQLSERVER2008,1219;

2. 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.

3. 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".