Connecting to SQL Server using VS 2005

Is there any way to connect to Hosting SQL Server using VS 2005.

I have only VS 2005 on my computer and Dont have SQL Server.

I have a SQL Server credentials on the Hosting server but the problem is the hosting website control panel didnt provide any enterprise manager to work on the database.

Thanks in advance.

Dear,

Load the VS 2005. Choose View -> Server Explorer and it will appear on the left side of the VS. click onServer Explorer and right click on theData Connections and chooseAdd Connection. a window will appear. in theserver name text box enter the server name then chooseUse SQL Server Authentication radio button and supplyuser name andpasswordthen enterdatabase name and click ok. your database with features will be added to server explorer. now you can enjoy designing and playing with your database.

|||

Hi,

I did exactly like how u said. I am getting this error message.

" An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server )"

|||

I wouldn't be surprised if your hosting service doesn't allow remote connections to the server for security reasons. You could download SQL express and work with that, perhaps. If you detach the db, and put it in your app_data directory, then it can be set up to automatically attach to whatever SQL server you specify in the connection string.

|||

I have done it by detaching the database and kept it in app_data folder ( db1.mdf ).

here is my connection string in web.config file. I am still getting that remote connections error ( though i dont use sql server and using app_data folder instead )

<connectionStrings>

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db1.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

Could you please correct if there are any mistakes in the code.

|||

does .\SQLEXPRESS actually work? never tried it. Anyhow, I am assuming this is working when you run locally, but not when you deploy it to your hosted site? I would check with their technical support.

|||

Your connection string looks fine for a local SQL Express database. The next thing to check - is your SQL Express server actually running? You should see it as a service called SQL Server (SQLEXpress) - if it's not running, then start it.

|||

Let me make it much more clear

I DONT HAVE sql server on my computer.

I have a MDF file in App_Data folder in my project. I use the connection string which i mentioned before and it works perfectly.

I Dont want to convert that MDF file as a seperate database on SQL Server. Insted, I want it to be there in App_Data folder only.

Now, I uploaded all the files to the hosting server. When trying to access a page that is related to mdf file in App_Data folder, It throws an error which i mentioned on the TOP.

Please help me out.

|||

I'm tracking that part, but I don't understand how that can work if you don't have SQLExpress on your local machine... It is sort of a prerequisite to being able to attach a .mdf file. Anyhow, it is normally installed by default along with Visual Studio or Visual Web Developer Express.

So let me walk through this for a sec...

When you have an MDF file in App_Data, the way the connectionstring works, is it tries to attach the MDF to the SQL Server instance specified in the connectionstring. If that is successful, you are good to go.

You have .\SqlExpress as your server instance. I assume this means the named instance "SqlExpress" (default) running on localhost. This would make sense if it is working on your local machine, and you do in fact have SqlExpress installed... i.e. it should work.

If the above is accurate, and you are experiencing failures on the hosted site, it probably doesn't mean what it sounds like it means. First, it has nothing to do with "remote connections", since you are trying to connect to "localhost". It basically means there is no server instance called SqlExpress running on the hosted server. Either the server is running on a different machine and you need to find out what it is (doubtful), or the Instance name is something different, and you need to find out what it is (likely).

I wouldn't be surprised if the hosting service provides each customer with a unique named instance of the Sql database, otherwise, everyone would be connecting to the same thing.

|||

yennares:

I DONT HAVE sql server on my computer.

Actually, you do have SQL Server on your computer - specifically the Express edition. Microsoft does a great job of hiding the fact that you have a SQL Server engine running on your PC - believe me, it's there (you can see it in the services window as SQL Server (SQLEXPRESS) ).

There are very few hosting providers that allow you to run SQL Server Express on the site. It's not designed for production use. Does your hosting provider support SQL Express? Ask them. If they say no, than you have to get your remote site working with SQL Server.

|||

Give this a try...

1. Drag an SqlDataSource control on your page, Click configure data source.
2. Select New connection, Change data source to microsoft SQL Server, Data Provider as .NET Framework Data provider for SQL Server, Press OK.
3. Enter Server name (this should be your web host's MS SQL Server), you may have to ask your web host for this.
4. Check 'Use SQL Server Authentication. Enter username, password.
5. Click Test connection (if the server address and credentials are correct, it should say [Test connection succeeded].
6. Once succeeded, you can connect to your database. Press OK.
7. VS 2005 should offer to save your connection info in a connection string in your web.config file.
8. In Server Explorer (VS 2005) you can edit your database.

Hope this helps,

|||

Thanks for the suggestions.

Now, i am in the process of finding whether my hosting provider has SQL Express running.

|||

Hi valenumr,

If you detach the db, and put it in your app_data directory, then it can be set up to automatically attach towhatever SQL server you specify in the connection string.

How to set up the App_Data database to automatically attach to whatever sql server? Say, if i have only sql server 2005 installed on my machine, and i want to use the App_Data folder, so, how can i set up the database to make it automatically attached to sql 2005? (i used to explicitly attach it to sql2005). thanks.

|||

Just detach the Database in the Sql Server Manager and move the .mdf file to the App_Data folder (or right click on App_Data and select Add New Item -> SQL Database). Then, update the connection string in web.config. It should look like this (for maximum portability):

<connectionStrings> <add name="YourConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|YourDataBase.mdf; IntegratedSecurity=True" /></connectionStrings>

Apparently .\SQLEXPRESS means the SQL server instance named SQLEXPRESS on localhost (new to me, but it works... cool), and |DataDirectory| means the resolved App_Data path no matter what path the server is running the site from.

Anyhow, the key is the AttachDbFileName tag in the connection string. This will causes the mdf file to automatically get attached to the SQL server instance you specify with the Data Source tag.