locked
VB Express - SQL Server Express RRS feed

  • Question

  • I am beyond confused.

    I have developed a full features desktop point of sale application using VB Express and SQL Server Express. My database has a .sdf extension.   I placed the database file on a shared folder and pointed two computers running my application to it.  The apps come up find but after any database write (reads seem to work) activity I get a File Sharing Violation.

    I have read every piece of information on the web about this for the past 24 hours.  I have read that SQL Server Express DOES allow multi user access - and I have read it Doesn't.  I have read so many descriptions of SQL Express - my head is spinning.  Some Call it SQL Server Ce  , some call it SQL Server Moble, some call it SQL Express - so there is no way of knowing what i have - or what it can do.

    My database has a .sdf extension.  Can a SQL Server Express .sdf database file be used by two applications running on the sam lan segment (without file sharing violations).

    Help - ready to pull the plug on 8 months of development.
    Christopher
    Monday, October 19, 2009 5:09 PM

All replies

  • Hello Christopher,

    >> My database has a .sdf extension.
    That is a database for a SQL Server Compact Edition (or Mobile Edition), not for SQL Server Express Edition.
    You can design/edit the database with SSMS, but you can't only run it with the SQL Server.
    And the compact edition is designed to run in a single user mode, you can't connect it with multi user.

    I guess, you have a reference on System.Data.SqlServerCE ?



    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Monday, October 19, 2009 5:25 PM
  • Olaf,

    Thank you.  I think I understand your reply.  So using the .sdf ( or Ce ) type of database - it is single user only.

    As a follow up - My applciation is built in Visual Basic 2008 express edition.  Do I have to throw it out?  Can I just connect it to a SQL Server data source without having to re-write everything? 

    I see that in VB I can connect to two different databases - one is a file the other is s service.  Any advice on getting me past this would be greatly appreciated.

    thanks again
    Monday, October 19, 2009 5:45 PM
  • I never tried to migrate a project from sql server ce to express.
    But all System.Data. Classes are designed very equal, so hopely you need only to use other classes for you objects and it runs/compiles (maybe).

    Before you run into the next trap:
    If you create a database for Sql Server Express Edition with Visual Basic it create a "user instance". It works also in single user mode, but later you can easily reconfig it to a "normal" database on a sql server service.
    You may better create & design your database with Sql Server Management Studio (SSMS).

    If you created the tables / data with SQL scripts you can run them with SSMS as well.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Monday, October 19, 2009 6:07 PM
  • I never tried to migrate a project from sql server ce to express.
    But all System.Data. Classes are designed very equal, so hopely you need only to use other classes for you objects and it runs/compiles (maybe).

    Before you run into the next trap:
    If you create a database for Sql Server Express Edition with Visual Basic it create a "user instance". It works also in single user mode, but later you can easily reconfig it to a "normal" database on a sql server service.
    You may better create & design your database with Sql Server Management Studio (SSMS).

    If you created the tables / data with SQL scripts you can run them with SSMS as well.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

    Olaf,

    Thank you again for your help.  I have spent the past two days changing my VB application - I removed the SQL Express Ce database and moved it to a SQL Express (.mdf) database.  It took a while but I am back up and running.  Now I am back to my inital question. Multi user access.

    I have two computers on the same segment (workgroup).  Computer 'A' is up and running with the application.  On computer 'B' when I change the connection string to access the shared folder on 'A' it has all types of trouble ( none related to access - the public folder is visable and the drive is mapped.  Computer 'B' has no problem seeing the .mdf file on computer 'A'.  The errors I get are things like 'can't find server'

    I think I am very close and that it is how I am constructing my connection string.  I am confused by server name ( i think).

    This is the connection string computer 'A' uses : (works fine - of course because I am local)

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Me\Public\SimplePOSDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True


    The second computer (computer 'B' ) uses

    Server=A;Data Source=.\SQLEXPRESS;AttachDbFilenameZ:\\SimplePOSDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

    When I run the application on 'B' the run time err I get is that it can't find the server or the server doesn't allow multiple connections.

    OK - finally, my question(s) ( sorry for such a long message).

    1. How do I properly build the connection string so computer 'B' can access the .mdf on the shared folder of 'A'.
    2. For the server name - what is it loking for?  the name of the computer (Computer 'A') or the name of the SQL server ( when I run SQL server configuration manager I see three servers running, One called 'Chris" ( that is me),  one called
    SQLEXPRESS and MSSQLSERVER.  )  Which should I be concerned with.


    Help - please.  I feel like I am so close :)
    A
    Wednesday, October 21, 2009 5:25 PM
  • That's what I meant with:
    >> Before you run into the next trap: ... "user instance".

    A user instance runs also in a single user mode; you can't share it.

    Make on of the maschine to your server, running the Sql Server all the time, let's say it maschine A.
    Copy the mdf + log file to the data folder of SQL Server Express, should be
    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLExpress\MSSQL\DATA\

    Start SSMS on the maschine and logon to .\SQLExpress
    Right-click on the node "Databases" => "Attach ..." and select the mdf file. Give the database a name like "SimplePOS" or "SimplePOSDatabase".
    Now the database is accessable from the complete network ... if you enable the remote access and TCP/IP network protocoll; start "SQL Server configuration manager" to make this settings.

    The connection string then is:
    Server=A\SQLEXPRESS;Data Source=.\SQLEXPRESS;Initial Catalog=SimplePos;Integrated Security=True;
    if you are going to use Windows NT account; the next problem, because you don't have a domain, only a workgroup.
    You can create the same Windows account with same password on both maschine; then it works with Windows account.

    Otherwise you have to use SQL accounts, you create them with SSMS under Security
    Then the connection string should look like:
    Server=A\SQLEXPRESS;Data Source=.\SQLEXPRESS;Initial Catalog=SimplePos;user id=SqlAccountName;password=YourPassWord;

    See also:
    http://www.connectionstrings.com/



    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, October 21, 2009 6:17 PM
  • Olaf,

    thanks - not sure i really understand.  hope you can bear with me a little longer.

    1. I did create the mdf in SSMS. 
    2. I tried the following connection string on the 'A' (local machine running the server and application) to see if I could get it right before moving to the 'B' machine.

    'Server=CALISI-PC-01\CCALISI\SQLEXPRESS;Data Source=.\SQLEXPRESS;Initial Catalog=SimplePOSDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True'

    this is the message i get when i try to open the application:


    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.Data.SqlClient.SqlException: Cannot open database "SimplePOSDatabase.mdf" requested by the login. The login failed.
    Login failed for user 'Calisi-PC-01\Calisi'.
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword,
    ....... much more

    My computer ( computer 'A' is called CALISI-PC-01 and I am loged in to the machien as CALISI.  When I open SSMS and attach to the database I am in without a problem.  Ug?

    Wednesday, October 21, 2009 7:41 PM
  • 1. The server name or datasource (is an alias) is "MaschineName\InstanceName"
    2. The initial catalog is the database name, not the file name.
    3. No "User Instance"; that's what we want to avoid.

    =>
    'Server=CALISI-PC-01\SQLEXPRESS;Initial Catalog=SimplePOSDatabase;Integrated Security=True;Connect Timeout=30'

    If the maschine name is "CALISI-PC-01"

    BTW, I might to remind there could occur problems if the maschine name include "-", but possibly only with some software we use... not sure at the moment.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, October 21, 2009 8:16 PM
  • Olaf,

    I really appreciate your patients.  I have now set everything up the way you indicated. 

    i am still getting:

    Cannot open database "SimplePOSDatabase" requested by the login. The login failed.  I created the database, i am the owner of teh database.  ug!   

    (i am still just trying to get the local app running on the local machine accessing the database in multiuser mode (conn str  'Server=CALISI-PC-01\SQLEXPRESS;Initial Catalog=SimplePOSDatabase;Integrated Security=True;Connect Timeout=30')

    any suggestions?
    Wednesday, October 21, 2009 9:34 PM
  • Olaf,

    one other piece of info - not sure if i mentioned it, the app is a VB app.  is thee anything in the VB app that needs to change other then the connection string?

    Wednesday, October 21, 2009 10:14 PM
  • Olaf,

    PARTIAL SUCCESS!!!!!!!!

    using the follwoing connection string I was able to get the local VB app to connect to the local SQL server:

    Server=CALISI-PC-01\CCALISI;Initial Catalog=SimplePOSSystems;Connect Timeout=30;Integrated Security=True


    when i try this from the remote machine (called CALISI-PC-02) it can't find the server.  i am using Vista on both machines.

    thanks
    Thursday, October 22, 2009 12:51 AM
  • >> is thee anything in the VB app that needs to change other then the connection string?
    No, the connection string is independent of the .NET language you use; it's the same for C# and VB; even if you use VB 6 + Ado.

    Have you run "SQL Server configuration manager" to make the settings for "Allow remote connections = True" (in Sql Server Express its per default off) and have you enabled the protocoll TCP/IP for communication with Sql Server (all on CALISI-PC-01)?

    Did you create the Windows account as I mentioned before and granted access to the databases?

    Just for testing the connection you can use
    Server=CALISI-PC-01\CCALISI;Initial Catalog=master;Connect Timeout=30;Integrated Security=True
    The "master" database always exists and anyone how is allowed to connect to the server have minimum read rights for it; so it should work allways.




    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 22, 2009 7:14 AM
  • Olaf,

    I think I have everything set properly but still the remote machines VB app still can't connect to the database.

    1.  The Remote machine has an account set up identical to the one on the server machine and connects to the server machine flawlessly through that account. ( i can see the public folder with ease ).

    2. I am using the same connection string on the remote server that I am using on the local machine to attach to the server. (Server=CALISI-PC-01\CCALISI;Initial Catalog=SimplePOSSystems;Connect Timeout=30;Integrated Security=True
    )

    3. In the SQL Server Config Manager The three instances/servers? (MSSQLSERVER,SQLEXPRESS and CALISI ) all have TCP/IP enabled ( restarted services to ensure it took).

    3. The database (in SSMC ) is set to MULTI_USER. 

    4. Could not find the item you mentioned above: ""SQL Server configuration manager" to make the settings for "Allow remote connections = True" .  How can/where do I find this.

    5. I also tried connecting to the master database and get the same connection errror.

    6. Above you mentioned something about granting access to the database for the account I created - what do you mean by that?

    really appreciate your assistance. 

    Chris
    Thursday, October 22, 2009 2:21 PM
  • Hello Chris,

    On you "client" maschine you have also SSMS, can you connect to your "server"?

    "Allow remote connection" was an other other deccription (in Surface feature config) for "Enable protocol" like TCP/IP, I mixed it, so please forget it.

    When you edit the property of an account you can define (3 option, under "server role") you can grant the user access to the several databases and add him to a database role like db_datareader / db_datawriter.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 22, 2009 2:56 PM
  • Olaf,

    I didn't think to run SSCM on the remote machine.  When I did it does not see any of the protocols/databases/servers on the server running the database.  I must not have the connection/account between the two machines correct ( i guess).

    I have the primary admin account on the server machine set up - lets call it MASTERACCOUNT  and I set up the same account on the remote machine.  Is there something else I need to do do the remote machine can see the server machines SQL server/databases?

    Chris
    Thursday, October 22, 2009 3:15 PM
  • Chris,

    I am running out of ideas.
    Do you have a firewall blocking the connection? If so, in common Port 1433 (TCP/IP) + 1434 (UDP) are needed.

    If the MasterAccount have on both maschine the same pwd, you only need to add the user in Sql Server, that's all.

    If I remind it right, the by default the MSDE runs in "Mixed mode", so you can use a Sql Account without change the configuration.
    Just for the test add a Sql account lets say "TestUser" with a password and then try it with connection string

    Server=CALISI-PC-01\CCALISI;Initial Catalog=SimplePOSSystems;user id=TestUser;password=YourPassWord;


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 22, 2009 4:20 PM
  • Olaf,

    no problem - you were a huge help.  

    i am trying to figure out if the remote machine, when running SSMC, should be able to see the SQL server processes running on the local machine?   currently the remote machine's SSMC does not see the servers running on my other machine.  i have tried everything - any other suggestions - or if you couy could point me to another source that might be able to help would be greatly appreciated.

    you have been great!, thanks
    Thursday, October 22, 2009 4:54 PM