Connecting to SQL Server database

[Marmalade]

Hi,

I've uploaded a SQL Server database that I developed on my own workstation (using SQL Server Eval edition) to a server, and am trying to access it via an ASP page on the same server. However, I keep getting the following error:

Microsoft OLE DB Provider for SQL Server error '80004005' 
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I'm using the following connection string (and have tried several variations on it), using the userid and password I set in SQL Server security:

"Provider=SQLOLEDB;Persist Security Info=False;Initial File Name=PhysicalPathToFile;Initial Catalog=DatabaseName;User ID=sa;Password=pw"

The string works fine when I try accessing the database from the ASP page on my own workstation. How can I get it to work in the "real" world?

Thanks,

M

 

[ehsant]

Hi There ...
You have to check one thing:
The server name.
you can add "Data Source=127.0.0.1" in the connection string and then put the IP of the SQL server or the name
of the SQL server in it.

Thst's it.
TNX
E.T.

 

[denoxis]

After

Provider=SQLOLEDB;

parameter

add

Server=xxx.xxx.xxx.xxx

where xxx.xxx.xxx.xxx is your IP

Name parameter is not necessary since you have nothing to do with physical file when you use MSSQL

 

[Marmalade]

Thanks, guys. I'm still a bit confused, though.

Should I remove the Initial File Name parameter completely and add either "Data Source=xxx.xxx.xxx.xxx" or "Server=xxx.xxx.xxx.xxx," where xxx.xxx.xxx.xxx is the IP of the server where I've placed the database?

I should add that I simply uploaded the SQL Server .mdf file that I built on my workstation - just as I had done with an earlier version of the database that I had built in Access (uploading the .mdb file). Will this work with a SQL Server database, or do I need to make sure that SQL Server software has actually been installed on the server?

 

[Antzz]

You can use the following string :

"uid=[user_id];pwd=[password];driver={SQL Server};server=[ServerName/IPaddress];database=[database name];dsn=,,connection=adConnectAsync"

The parts in the string within the square brackets are the variables that you need to substitute with your own values. The above string is for DSN-less conenction. If you have a DSN entry using ODBC, then you will have to add the value here and you can ommit the entry for "database".

As far as the second question, is conrerned, you will probably have to build the database with SQL Server instead of copying it from a different location.

 

[denoxis]

You MUST have a SQL server over there. Then you can export your database from your SQL server to the remote SQL server. Enterprise Manager is perfect for this job. You have nothing to do with your physical file BTW.

 

[kris]

or you can use

Dim conn
Set conn = Server.CreateObject("ADODB.Connection&quot
conn.open "DRIVER={SQL Server};SERVER=[computer name;DATABASE=[database name];UID=[user ID];PWD=[password]"

replace the text between "[" & "]" with your own database setting

 

[Marmalade]

Thanks, everyone, for being so helpful. I'll make sure that SQL Server is installed on my server, then try the connection strings you recommend.

Denoxis, I'm curious - what do you mean when you write that I have nothing to do with my physical file? Is that why I'll have to export the database file on my workstation to a remote instance of SQL Server using Enterprise Manager, rather than just uploading a copy of the database file myself? And once the database is up and running on a remote instance of SQL Server, will I still be able to access and manage it using the SQL Server software on my workstation? (Guess it's time to move to the SQL Server forum!)