Classic ASP Remote SQL Connection String

I have a dilemma which is completely and utterly baffling me...

We have a Classic ASP site which has been running for about a year, its powered via a local Win2008 SQL database (The SQL Express engine is on the same server as IIS). This is the connection string we currently use

Provider=SQLNCLI10;Server=SERVERNAME\SQLEXPRESS;Database=dbname;user id=username;password=password;

We now have a need to move the database to a dedicated DB server, and connect to it remotely - We restored the DB on the server, and checked everything looked ok (All data is present in all tables)

So we changed the server name to the IP address and was unable to get it to connect, in the end the only way we could get it to connect was using this connection string.

driver={SQL Server Native Client 10.0};server=IPADDRESS\SQLEXPRESS,1433;uid=user;pwd=p开发者_JS百科assword;database=dbname;

HOWEVER... Now it's randomly missing product names and SKU's and prices in the front and back end, even though the data is present in the DB AND it is REALLY slow to display the webpages? If I use the same DB locally with the previous connection string its fine and all data is appearing?

I know this DB server is fine as it powers other ASP.NET sites with via a remote connection and they are very fast to connect?

Any hints... or help on why this new connection string is causing problems with reading data from the DB???

---

To update the native client on the web server to the most recent public version, go here:

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2567714

Check the box for "2008R2_SP1_SNAC_CU2_2567714_10_50_2772_x86" if your web server is an X86 machine, and "2008R2_SP1_SNAC_CU2_2567714_10_50_2772_x64" if it is 64-bit. Enter your e-mail address, fill in the captcha, hit "Request hotfix" and the download link will be e-mailed to you. Download it to the web server, run the installer (using the password sent to you in the e-mail) and restart the web server. Now try your original connection string again.

EDIT

Adding some other sample connection strings that I have used in the past (I've been a classic ASP guy since the 90s, founding and writing 95% of the content on aspfaq.com, including this article on connection strings). As requested in a comment to the original question.

Note that "srv" could be IP address, or localhost, or servername, or . and it could be followed by \INSTANCE_NAME (e.g. \SQLEXPRESS). I would try them in this order, and pay attention to the parameter names as well as the values - you seem to mixing modern parameter names (e.g. Provider) with less modern ones (e.g. Database).

Provider=SQLNCLI10;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Provider=SQLOLEDB.1;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Provider=SQLOLEDB;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Driver={SQL Server};Server=srv;Database=db;UID=user;PWD=pwd;

Sometimes if you are having issues with Named Pipes or Shared Memory it can help to enforce TCP/IP by adding the following parameter:

NETWORK=DBMSSOCN;

You can get a lot more information over at Carl Prothman's site or ConnectionStrings.com. They no longer cater to classic ASP specifically, for obvious reasons, but you can still glean a lot of information from the samples they provide for other languages.