CODEWITHSUNDEEP
sqlsql-server-2008visual-studio-2010
user34537
user34537

Reputation:

How can i test my TSQL syntax?

Quick question: How do I get some kind of database to use to test my SQL syntax and create basic data.

I have Sqlite Code which I'll soon put on a server. I have SQL Server 2008 installed with visual studio 2010. I tried connecting to the database and had no luck.

I also tried using an .mdf file instead thinking it's a file and I won't have connectivity issues. Wrong, I still couldn't connect and I used this site to help me (i'm aware its 2005)

In that case I used:

var conn = new SqlConnection(@"Server=.\SQLExpress;AttachDbFilename=C:\dev\src\test\SQL_DB_VS_Test\test.mdf;Database=dbo;Trusted_Connection=Yes;");

exception

Unable to open the physical file "C:\dev\src\test\SQL_DB_VS_Test\test.mdf". 
Operating system error 5: "5(Access is denied.)".
Cannot attach the file 'C:\dev\src\test\SQL_DB_VS_Test\test.mdf' as database 'dbo'.

With trusted = no I get Login failed for user ''. (What user am I suppose to set?). I created the .mdf with Visual Studio somehow.

Upvotes: 0

Views: 264

Answers (3)

user34537
user34537

Reputation:

After creating the mdf file with visual studios right click the mdf and select properties. In it you'll see a row called Connection String. Copy/paste it into your app and it should connect. The key part is User Instance=True

Upvotes: 0

marc_s
marc_s

Reputation: 755237

What if you try this connection string:

var conn = new SqlConnection(@"Server=.\SQLExpress;
        AttachDbFilename=C:\dev\src\test\SQL_DB_VS_Test\test.mdf;
        Database=test;Integrated Security=SSPI;");

I don't think it's a good idea to call your database "dbo" (that's a SQL Server keyword - I wouldn't use it for my own purposes!), and also I believe you need to use Integrated Security=SSPI; to define Windows authentication - Trusted_Connection is not used for SQL Server connection strings, AFAIK.

Upvotes: 1

Zachary
Zachary

Reputation: 6532

Have you tried using SSMS to access your local instance? It's helpful for getting connected and getting everything setup. Also, I think the default install of Sql Express with VS only support trusted connections.

Upvotes: 0

Related Questions