How do i use SQL Server 2008? With Visual Studios?

Question

Its a two part question. How do i use SQL Server 2008? With Visual Studios?

I started up a dummy project and with server explorer i tried with create new sql server database and add connection using my computer name (it came from a dropdown) as the server location. When i tried to create the database 'TestDB1' i got an error. I dont understand why. Its a fresh install and i have restarted the comp a few times since then. I havent messed with visual studios or the servers or even the control options to disable anything that would have been automatic. So whats with this?

-edit- My goals are 1) create a database. 2) Be able to see all the database that exist on the server 3) execute sql queries in the ide 4) be able to browse tables. I dont need all of these but as many possible would be nice.

-edit2- I cd to C:\Program Files\Microsoft SQL Server\100\Tools\Binn and tried sqlcmd after enabling named pipes and restarting the server. I get an error with that as well

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>sqlcmd HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server . Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Answer 1

TCP connections are disallowed by default. Go into Start | Programs where you SQL Server install stuff is and run the SQL Server Configuration Manager, and allow TCP connections

Answer 2

If SQL server is on the same computer your're developing on, try using . (dot) as server name, and use the Test Connection button to see whether it is accessible.

Answer 3

The version installed with Visual Studio is the SQL Server 2008 Express edition, which is typically installed as a .\SQLExpress instance (SQLExpress being the instance name).

So when you try to run a command on that instance, you'll need to specify that very instance in your commands - either in your connection strings from C#, or on the command line using sqlcmd:

sqlcmd -S .\SQLExpress -E -d master -Q "select name from sys.tables"

This would connect to the server instance .\SQLExpress, use the master database, and list the tables contained in that database. Does that work from the command line for you??

UPDATE: to access this same database from your .NET app, you need a connection string something like this:

server=.\SQLExpress;database=master;Integrated Security=SSPI;

Of course, depending on what you want to do, you will want to use a different value for the database= attribute.

If you want to simply attach a MDF file to a SQL Server Express edition, you can also specify the path to the MDF directly:

server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
  database=master;Trusted_Connection=yes;

and if that MDF file is in your app's app_data directory, you can even use a |DataDirectory| alias:

server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;
  database=master;Trusted_Connection=yes;

See more samples of connection strings for SQL Server (including Express editions) at http://www.connectionstrings.com (or this link for SQL Server 2008 connection strings).