Issue Connecting Classic ASP pages to SQL Express 2012
-
Hi,
I wonder if anyone out there can assist with this issue I am experiencing whilst trying to upgrade from SQL Server 2005 Express to 2012?
I have a classic ASP website which connects without problems to a SQL Server 2005 instance (on Windows Server 2003) using the following connection string
var Db_Conn = Server.CreateObject("ADODB.Connection");
Db_Conn.Open("Provider=SQLNCLI;Data Source=(local);User ID=auserid;Password=mypassword;Initial Catalog=ADatabaseIUse");
I have now acquired a new server (Windows Web Server 2008 R2) onto which I have installed SQL Server Express 2012. I have restored the database successfully from the 2005 to the 2012 instance, copied across all the ASP pages but cannot connect. I have tried various connection strings and the latest was created using the OLE DB Core Services feature which produced a connection string of
var Db_Conn = Server.CreateObject("ADODB.Connection");
Db_Conn.Open("Provider=SQLNCLI11.1;User ID=auserid;Password=mypassword;Initial Catalog=ADatabaseIUse;Data Source=ServerName\SQLEXPRESS;");
However this still does not work and I keep getting a 500 -Internal server Error each time.
I have ensured that Named Pipes and TCP/IP is enabled.
I am at a complete loss after many hours of scouring the Internet and Googling as to why the pages will not connect.
Anyone have any ideas please?
Many Thanks
-
What error are you getting? You can have IIS7 send error messages to the browser for debugging purposes. Just open the ASP item in IIS and then expand Debugging. You will have to be using a browser on the same machine as IIS but it will give you the old style error messages.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks for that suggestion. Have tried that with Client and Server Side debugging enabled and opened ASP page using IE9 on the local machine to IIS. And I get a Website Cannot Display Page (500) error again. No other details are provided in the page.......
-
You need to either a bunch of response.writes with response.end or send error messages to the browser.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I still cannot get any errors output to the browser but have found the folowing in the IIS logs.
2012-06-13 15:24:55 W3SVC6 MYSERVERNAME nn.nnn.nnn.nn GET /MyDirectory/mypage.asp|4|80004005|Named_Pipes_Provider:_Could_not_open_a_connection_to_SQL_Server_[53]._ 80 - nn.nnn.nnn.nn HTTP/1.1 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+WOW64;+Trident/5.0) ASPSESSIONIDCSTDTCTD=JCCFBBFAKIHOJDEHBMHANAPM;+ASPSESSIONIDSSRDRBQB=DLBLFIFABBJGGKIAGFCKBELD - http://www.mydomain.co.uk 500 0 0 631 406 15046
So I guess we need to take another look at named pipes to get to the bottom of this?
-
I've still been hunting for hours on this one with no luck.
I can ping the server, Windows Firewall is off and still I get the same Named Pipes error.
Could it be anything to do with the ports?
Anyone have any ideas?
Thanks
-
well, sql express does not allow remote connections by default...did you explicitly change that?
can you connect to that server via SSMS from a machine that is not the server itself?
your example string shows you are connecting to an isntance instead of a default:ServerName\SQLEXPRESS
did you if the web server that requires the SQL browser service to be running(does that exist for express? i don't think it does?)
otherwise you want to change the instance to listen to a static port, and change the server to include the port number.
, 
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, Thanks for your input. In answer to your questions....
I do not recall changing the remote connection setting but it is definitley set to allow them.
I do not have another machine on the network to try connecting, but I have tried to connect via SSMS from another remote server using SSMS with the IPaddress\instance name and this did not work.
SQL Browser is running, SQL Agent is not but doesn't with Express apparently.
The port for IP1,IP2....AllIPs have been changed to 1433 - does that need to be in the connection string I wonder?
-
Here's the latest update....
I eventually found someone who could solve this and the answer was to create BOTH 32bit and 64bit System DSNs with the same name to connect to the database. The connection string in the ASP pages was then..
var Db_Conn = Server.CreateObject("ADODB.Connection");
//Connect with following params System DSN Name,username and password
Db_Conn.Open("myDSNname","myDBusername","myDBpassword");
The DSNs were created by running c:\windows\system32\odbcad32.exe and c:\windows\sysWOW64\odbcad32.exe
Quite why the ASP pages need both DSNs to connect, I haven't a clue. All I know is that if either is missing then I get the original error.
If anyone can throw any more light on this issue as to why this is necessary then that would be useful.
-
what you posted originally was using an explicit connection string, and no DSN; did you ever confirm that you can connect from another machine?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
No I didn't get around to trying from another machine.
-
Not being a SQL Server connection expert I still do not really understand why there isn't an explicit connection string that works and you have to go round the houses trying everything until hopefully one method works - I guess that's Microsoft for you!! I'm on the verge of porting the whole application to PHP/MySQL as every time I upgrade to a new Windows server there are significant issues with moving the app and database across and I've lost count as to how many days of my life I've wasted chasing these issues around.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply