Connecting to SQL Server 2005 Express

I am having major issues connecting to SQL Server 2005 Express from Visual Basic 2005 Express, which I have tried on multiple PCs but to no avail. Can somebody please explain to me what I'm doing wrong?

1) I install SQL Server 2005 Express and Visual Basic 2005 Express (With .NET framework and SQL native client of course)
2) I open SQL Server using Management Studio and going in with windows authentication. It automatically sets the log on name to computername\SQLEXPRESS.
3) I create a new database called Test and add one table to it
4) I go into Visual Basic 2005 and create a new project.
5) I go to the projects application settings (Properties - Settings).
6) I set up a new connection string, choosing SQL server as the conection type and then on the Connection Properties window it asks me to select the mdf file for the database. I choose the file Test.mdf from the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
7) I click on Test Connection.

When I do this on two out of the three home PCs I get the error:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf".Operating system error 32: "32 (error not found)".
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The third PC gives me a different error:

Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

I get the same errors when I try to use a connection string inside the code directly. Does anyone have a clue as to why these errors are occuring? Do I need to set up security or something else on the database first, or is there another reason? If anyone can please help me get to the bottom of this or provide a web address where I can get the answer (step by step instructions on how to connect) then I would be most grateful.

error code 32 means:
"The process cannot access the file because it is being used by another process."

So, either use a different file name for your connection in Visual Basic 2005, or try disconnect your connection in SQL Server using Management Studio.

|||

pse post the Connection string... it seems like u r trying to attach a db called Test....

in the second error .. check what is the Default Database given for user DAVE\David.... the default database should exists and this user should be a user of that db.... for debuggin purpose ... just change the default database of this user to Master and try... it will connect...

Madhu

|||

Hi, I get the same error as the original poster of this thread. I have no other database with similar name or anything, and i have not, as far as i know, any other connections to this database, since I just created it. Do you have any idea of how I should go about this?

Thanks

Niclas

sqlsql

Connecting to multiple SQLserver

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Farhan H Soomro wrote:

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Use multiple connection managers.

-Jamie

|||Is there any way I can read from text file salserver name and connect to particular server and run the scripts?|||

Farhan H Soomro wrote:

Is there any way I can read from text file salserver name and connect to particular server and run the scripts?

Yes. SSIS Configurations are provided to help you do exactly this.

Creating Package Configurations
(http://msdn2.microsoft.com/en-us/library/ms141132.aspx)

-Jamie

|||

If you have a list of servers that you want to run some SQL against, you can create two packages.

The simplest way to do this is create a SQL Server configuration table.
In the first package create a ForEach loop that processes each server in a resultset (I'd stick the list of servers in a SQL table so you can use it in reports).
In the ForEach loop
execute a SQL task to update the connection for the second package (the source server connection).
Then execute the second Package

In the Second package just create two connections, the source server to run the scripts against and the destination server to put the results in. Just use a data flow task to move the data.

|||

Thanks for the help. Just wondering how to setup SQL task to update the connection for the second package ?

TIA

--

Farhan

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

Connect to Sql Sevrer on non default?

Hi,

- Can I use set multiple port for connecting to the Sql Server?
- Where/How can I set it?
- If so, i've set it, how can i connect it on a non-default port?

Thx & RegardsFrom the Microsoft KB article 307645PRB: Cannot Connect to SQL Server on Any Port Other Than 1433:

To resolve this problem, use TCP/IP protocol, and include "Server=ComputerName, PortNumber" in the connection string.

Terri|||how to user the tcp ip protocole? we must put sth on the connection string?

(ive got the same prob)