Attempting to Connect to SQLServer 2k

This is the code I'm using on the web page located on the server:

Dim strConn as String
Dim conn as SQLConnection
strConn="server=localhost;Trusted_Connection=Yes;database=CIS"

conn=New SQLConnection(strConn)
conn.Open()

and the error messgae I receive is: Login failed for user 'MSP00427\ASPNET'.

msp00427 is the name of the server
= = = = = = = = =

I have an MSAccess application that accesses the same database as follows with no trouble.

glblConnectString = "Driver=SQL Server;Server=msp00427;Database=CIS;Trusted_Connection=Yes"

Set cnn = New ADODB.Connection
cnn.Open glblConnectString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "Scorecard_Delete_ATLASDataDump"
cmd.CommandType = adCmdStoredProc
cmd.Execute

This is run on my PC and I have sa capabilities on the server.

The .NET framework is installed.

Any ideas why the data access on the web page fails?you need to add the user 'MSP00427\ASPNET' to the db and giv ehim permissions. if yo xpand the databases tab on your sql server xplorer and then the database you are trying to connect to, you would see a Users tab. right click and add new user..

hth|||In SQL Server Enterprise Manager (or using scripts) you must add the 'MSP00427\ASPNET' windows user as a SQL Server user.

When you access an ASP.NET app, it is not YOUR rights that count, but the rights of the ASPNET user (by default).|||Thanks to those who responded so quickly.

I had just figured it out and tested it.

I was now coming back to post the solution for others.

You beat me to it.

Attaching MDF with AttachDBFilename?

I am attempting to attach an .mdf to SQL Express using "AttachDBFilename" in
the connection string, and I have a few questions on the issue.
I am planning on setting this up on a network with both the .mdf and the
instance of SQL Server residing on a server. All the clients will be runnin
g
an app that will have access to the SQL Server instance and the fileshare,
and they will attempt to use a connection string with the "AttachDBFilename"
.
I have successfully connected to the instance of SQL Server and have access
to the .mdf from a client, but I have not been able to get the connection to
attach the .mdf file. If I run the app on the server it works, but on a
client I get an error saying something like "The file '..' is on a network
path that is not supported for database files."
My questions are:
Can this be done using "AttachDBFilename"?
If yes, any suggestion on what I may be missing?
Even if I do get this method to work will it support multiple users?
Thanks,
JimThe database file (mdf) only needs to be attached one time -at the server.
Clients only need to connect to the server and have access to the database.
If SQL Express was installed on each client, and those clients used the
'AttachDBFilename', they would be attempting to attach a database file to
their local copy of SQL Express. Database files cannot be attached from a
unc network path. They would each need a local copy of the database for that
to work. However, it sounds like you want a centrally located database, not
individual copies.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:5732D62A-EE09-4ED6-8548-FFB7AC58EC2E@.microsoft.com...
>I am attempting to attach an .mdf to SQL Express using "AttachDBFilename"
>in
> the connection string, and I have a few questions on the issue.
> I am planning on setting this up on a network with both the .mdf and the
> instance of SQL Server residing on a server. All the clients will be
> running
> an app that will have access to the SQL Server instance and the fileshare,
> and they will attempt to use a connection string with the
> "AttachDBFilename".
> I have successfully connected to the instance of SQL Server and have
> access
> to the .mdf from a client, but I have not been able to get the connection
> to
> attach the .mdf file. If I run the app on the server it works, but on a
> client I get an error saying something like "The file '..' is on a network
> path that is not supported for database files."
> My questions are:
> Can this be done using "AttachDBFilename"?
> If yes, any suggestion on what I may be missing?
> Even if I do get this method to work will it support multiple users?
> --
> Thanks,
> Jim

AttachDBFileName refers to original log file

Hello,

I'm using the AttachDBFilename option in the SQL connection string to attach
a database mdf file copied to another machine.

There is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:

Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Database\Strategic.TimeMaster.Database.SQL2005\TimeMasterDat_log.LDF" may be incorrect

How can I get the attach to avoid referring to this log file?

Coudl you please post your connection string here ? Seems that you are mixing up the setting for server / user instances.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Here it is:

Data Source=.\SQLExpress;AttachDBFileName=C:\Program Files\Time Master\TimeMasterDat.mdf;Initial Catalog=TimeMaster;Trusted_Connection=yes;

The mdf file should be attached to a local instance of SQLExpress. The mdf has been copied from my development machine and the it seems to think that this mdf file is refering to this log file.

It shouldn't, it should create a log file on the local machine.

|||Try to add the "User Instance=true;" to the connectionstring.|||

That worked, but creating another User Instance was not really what I was looking to do, but will probably do

Thanks

|||Did you want to attach the database to the server for a server instance ? or did you want to use the user instance feature of SQL Express ? If you want to use the user instance you WILL have to create one instance per database.

-Jens.|||A server instance I suppose. The server and client being on the same machine.|||

If you want to use a server attached instance you will have to attach the database first (if you have no log LDF file you will have to try to attach the database using sp_attach_single_file_db) using sp_attachdb or the equivalent GUI action in the managment studio.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

http://msdn2.microsoft.com/en-us/library/ms130822.aspx

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

http://msdn2.microsoft.com/en-us/library/ms130822.aspx

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John

AttachDBFileName - refers to previous log file

Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Databas e\Strategic.TimeMaster.Database.SQL2005\TimeMaster Dat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?
Answered in
http://forums.microsoft.com/forums/ShowPost.aspx?PostID=1081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

AttachDBFileName - refers to previous log file

Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMast
er\Database\Strategic.TimeMaster.Dat
abase.SQL2005\TimeMasterDat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?Answered in
http://forums.microsoft.com/forums/...081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--