Re: SQL Express 2005 doesn't support multiple users....

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Jasper,

Your instructions worked perfectly. The computers are accessing the
database exactly as planned. I really appreciate you taking the time to
help me thru this problem. I am, as you've probably realized, very new to
SQL - my experience is in MSAccess - so I have a lot to learn and you've
helped considerably. Thanks again.

"Jasper Smith" <jasper_smith9@xxxxxxxxxxx> wrote in message
news:u7kLR32%23FHA.344@xxxxxxxxxxxxxxxxxxxxxxx
> Because the Filewatcher connection string specifies AttachDbFilename +
> User Instance=True then all clients that would want to connect to that
> database should specify the same connection. With user instances, you are
> in effect creating a whole new instance of SQL Server (with it's own
> system databases etc) for yourself (or in this case the Filewatcher
> application). This is not accessible to other users unless they specify
> the same AttachDbFilename (it won't create a second user instance if
> they're running under the same account). Having said all that, I don't see
> why you would want to be useing userinstances in this case. Simply attach
> the database to the .\SQLExpress instance (Using Management Studio
> Express) and set both client connection strings to
>
> "Data Source=.\SQLExpress;Integrated Security=SSPI;Initial Catalog=DBName"
>
> For more information on user instances have a look at
> http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Wade" <wnederveld@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:OcpCbv1%23FHA.1676@xxxxxxxxxxxxxxxxxxxxxxx
>>
>> Thanks for the response...
>> The Filewatcher program (which is also running SQL) uses:
>> strConnection = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Program
>> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.mdf';Integrated
>> Security=True;Connect Timeout=30;User Instance=True"
>>
>> The other program uses:
>>
>> Protected Const SqlConnectionString As String =
>> "Server=tcp:COMPUTERNAME;Integrated Security=SSPI;Initial Catalog=DBName"
>>
>> Should both connection strings be the same?
>>
>> The first one (FileWatcher) opens the connection (conn = new
>> SqlConnection(strConnection) ... conn.Open()) as soon as a file
>> modification event has occured, it then adds the record, then closes the
>> connection (conn.Close()).
>>
>> The other program creates a New SQLconnection (Dim conn as New
>> SQLConnection(SqlConnectionString)) when the user wants to query the
>> database, then fills a DataSet on the Frontend form with the Query
>> results -- (Dim cmd as new SqlCommand(QueryStr,conn) ... Dim da as New
>> SqlDataAdapter(cmd)), but it doesn't set the variables (conn, cmd) to
>> nothing after the DataSet is filled.
>>
>> Should these variables be set to Nothing after the dataset is filled?
>>
>> "Jasper Smith" <jasper_smith9@xxxxxxxxxxx> wrote in message
>> news:ewxbDez%23FHA.160@xxxxxxxxxxxxxxxxxxxxxxx
>>> What do your connection strings look like? If you are using a standard
>>> connection as you would for other editions of SQL you should be fine. If
>>> you are using user instances or attaching the database file in the
>>> connection string (which I believe may be the default if using VB
>>> Express) then this may go some way to explaining the issue you are
>>> seeing.
>>>
>>> --
>>> HTH
>>>
>>> Jasper Smith (SQL Server MVP)
>>> http://www.sqldbatips.com
>>> I support PASS - the definitive, global
>>> community for SQL Server professionals -
>>> http://www.sqlpass.org
>>>
>>> "Wade" <wnederveld@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>> news:%23w1Xnjy%23FHA.2708@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Hi,
>>>> I'm glad someone has responded. I'm glad that I'm wrong on this also.
>>>> The reason I came up with this is by trying to have two computers
>>>> establish links to one database. The two computers could establish a
>>>> connection, but only one at a time - if one was connected, the other
>>>> couldn't. I posed this problem on the newsgroups (11/22/2005) and
>>>> received no response. I've been pouring over the newsgroups, Books on
>>>> Line, MSDN site, looking for cases like mine and couldn't find
>>>> anything. I've tried sample databases, and even those cannot support
>>>> multiple connections. I've tried connecting thru DSN using MS Access
>>>> as a frontend, I've tried coding the connection in VB 2005 express
>>>> (using OLE, ODBC) and also using sample code found at the MSDN and
>>>> other sites, but still cannot make more than one connection.
>>>>
>>>> So based on all the trials, all the research, and lack of response from
>>>> the community, I had no choice but to come to that conclusion.
>>>>
>>>> Now that at least one person (Mike) has responded to the contrary, I'm
>>>> hoping to receive some guidance.
>>>>
>>>> I have a computer (WinXP sp2, 1GB ram, 20+ GB free space) running SQL
>>>> express. Also on that computer I have a command line program (DOS if
>>>> you will) that watches for file changes on a network drive
>>>> (FileWatcher - sample found on the internet) and creates records in the
>>>> database of files that have been created, deleted, or changed and when
>>>> that event occured. This works fine.
>>>>
>>>> On another computer I have a front end that I created in VB 2005
>>>> express (I also tried with Access as a front end) which will query the
>>>> database and list the records that satify the query - i.e. show all
>>>> files have been been created since yesterday, or show files that
>>>> contain the extension .doc. And this works fine - but only if the
>>>> FileWatcher program is not running. If FileWatcher is running, I
>>>> cannot establish a connection. If I have this front end running and
>>>> try to start the FileWatcher program on the other computer, the
>>>> FileWatcher program errors out as soon as it trys to add a record.
>>>>
>>>> Should the FileWatcher program be running on a third computer - and not
>>>> have anything running on the SQL computer? Does anyone have a similiar
>>>> example or situation that I could learn from?
>>>>
>>>> Thanks again Mike for responding.
>>>>
>>>> "Mike Epprecht (SQL MVP)" <mike@xxxxxxxxxxxx> wrote in message
>>>> news:%23u0Uiyp%23FHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> Hi
>>>>>
>>>>> That is not correct. It does support multiple connections
>>>>> simultaneously.
>>>>>
>>>>> How did you establish that is does not?
>>>>>
>>>>> --------------------------------
>>>>> Mike Epprecht, Microsoft SQL Server MVP
>>>>> Zurich, Switzerland
>>>>>
>>>>> IM: mike@xxxxxxxxxxxx
>>>>>
>>>>> MVP Program: http://www.microsoft.com/mvp
>>>>>
>>>>> Blog: http://www.msmvps.com/epprecht/
>>>>>
>>>>> "Wade" <wnederveld@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>>>> news:OETGx7o%23FHA.1288@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Apparently SQL Express 2005 doesn't support multiple
>>>>>> users/connections ... only one user connection can be established at
>>>>>> a time. Does any know if this will this change in future
>>>>>> versions/updates?
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: SQL Express 2005 doesnt support multiple users....
    ... > database should specify the same connection. ... With user instances, you are ... > Express) and set both client connection strings to ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Express 2005 doesnt support multiple users....
    ... Glad to have helped, user instances are new to all of us, I'm still trying ... Jasper Smith (SQL Server MVP) ... >> database should specify the same connection. ... >> Express) and set both client connection strings to ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Express 2005 doesnt support multiple users....
    ... Jasper Smith (SQL Server MVP) ... > Because the Filewatcher connection string specifies AttachDbFilename + ... With user instances, you are ... > Express) and set both client connection strings to ...
    (microsoft.public.sqlserver.connect)
  • RE: How do I enable the web server to create a database?
    ... Although I have still had no success creating the database, ... I tried some new connection strings in web.config. ... also be caused by the role manager feature not being enabled. ... tried using SQL Server Authentication is a member of every role in the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Connections String Questions
    ... As far as Q1 - always use SqlClient with a SQL Server or SQL Express ... Help on connection strings to see what best suits your application. ... Which provider should be preferred when defining a connection, ... I have a database on my local PC and the same ...
    (microsoft.public.dotnet.framework.adonet)