Connect to a SQL Server database file

Hi,

I am a beginner on using SQL Server Express. I have two different questions. Here is the first:

I've developed a simple C# application in Visual Studio 2005 that connects to a SQL Server database file. In the application I connect to the database using this connection string:

@."Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Databases\mydatabas.mdf ;Integrated Security=True;Connect Timeout=30;User Instance=True";

and It works perfect.

But when I move the database file(and the corresponding log-file) to another computer it fails. I've changed the connection string to this

@."Data Source=.\SQLEXPRESS;AttachDbFilename=\\myserver\Databases\mydatabase.mdf ;Integrated Security=True;Connect Timeout=30;User Instance=True";

This is the error message I get when I try to open the database

The file \\myserver\Databases\myDatabase.mdf is on a network path that is not supported for database files.An attempt to attach an auto-named database for file \\myServer\Databases\myDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Both computer runs Windows XP SP2.

My second question is:

When I deployed my solution to my production PC(witch also runs Windows XP SP2, .NET Framwork 2.0 is installed on the computer) .When I then tried to run the application I did get this error message

An error has occured when establishing a connection to the server. When connection to a SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remomte conections.....

Appearently I cant treat SQL Server database files in the same way as Access-files, or?

Jan Olsson

To use the database on a different machine, SQLExpress would need to be installed on that machine (\\myserver) and the database attached there. Then you would connect to that SQL Server from your local machine. Use the "SQL Server Configuration Manager" to enable network connectivity. The AttachDBFilename really is only intended for local machine use.

SQL Server uses a client/server paradigm and you cannot treat SQL Server database files the same way as Access files.

|||

In Access the runtime is in the database files SQL Server is RDBMS(relational database management systems) the runtime is in the server, there is a fixed location for the data files MDF and LDF log data files. They are located in the following location in your c drive, if you intend to use another location you must let SQL Server by code before creating the database. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

|||

Thanks, for your good answer.

/Jan O.

|||

Thanks Peter, I have installed SQL Server Express on my remote computer and attached my database file to SQL Server. it works properly. But how shall I connect to the database from my local machine? I tried to connect from Server Explorer in Visual Studio but it didn't work. I got the message "Login failed for user \\myserver\Guest" and if I want to connect from my C# application how shall the connection string look like.

Jan Olsson

Connect IIS to sql server 2005 db

I have developed a website that connected to sql2000 db

Now that I have upgraded to SQLserver2005, I always get the follwing message after launching the website:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/iisHelp/common/500-100.asp, line 11 Dim objASPError, blnErrorWritten, strServername, strServerIP, strRemoteIP Dim strMethod, lngPos, datNow, strQueryString, strURL --^

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][Gestionnaire de pilotes ODBC] Source de donnes introuvable et nom de pilote non spcifi

/FMCQ_Web/Include/Modules/modADO.asp, line 18

I have tried different things (security access, DSN, etc...)but seems that nothing is working,

Can someone out there suggest a move to get this going..

The error is so obvious:

Dim objASPError, blnErrorWritten, strServername, strServerIP, strRemoteIP Dim strMethod, lngPos, datNow, strQueryString, strURL

Change to two lines:

Dim objASPError, blnErrorWritten, strServername, strServerIP, strRemoteIP

Dim strMethod, lngPos, datNow, strQueryString, strURL

Hope this helps!
Joe

Conflict Management

Hi,

I developed an ppc application, where i am using web service for synchronisation, but here i am getting lot of problem while handling conflict management.

Please suggest me how to manage conflicts, using web service.

Thank you,

Prashant

Perhaps the easiest way to ensure that the unit of work that each mobile client is performing is guaranteed unique and avoid conflicts at synch time is to assign each data row a uniqueidentifier or GUID as it's PK and introduce the concept of a last changed datetime so that any changes to a unique data row from more that one mobile user can be applied on the server in the proper chronological order. Even this approach will break down in some synchronization scenarios, so web services are really best when data that needs to be operated on by mobile users can be strongly partitioned from other users or server-side changes to the data while on device.

-Darren

|||

Hi,

Thanks Darren for your reply,

I want to validate on column level of conflicts, i have given each row a different guid, but column was conflicts i am not able to handle.

Please suggest me.

|||

it's pretty hard to implement your own column level tracking of a single row that can be in multiple states across a population of mobile users. Merge replication uses 3 GUID columns, logic in the Server Agent, and tracking in the Distribution database to pull this off. I guess what I would do is get your web service synch algorithm down to working well with row-level changes and then try to eliminate scenarios where conflicts can occur. You may have to maintain some metadata about "who-pulled-what" on the server to do this. If your problem domain is such that more than one mobile user can pull the same row, you allow any or all of them to change that row (and perhaps the row is changing on the server as well), then you are biting off a pretty complex software engineering challenge to prevent the possibility of conflicts using web service synch. Start out simple and try to eliminate the scenarios where conflicts can occur by strongly partitioning data as much as possible. Column level tracking is nice in that it reduces the amount of data that has to be sent back and forth, but I wouldn't bite that off until you have a simple PutUpdates and GetUpdates working at the table and row level.

-Darren