none
Newbie question? : VB application with SQLExpress database giving error 26

    Question

  • I have made an application using VB 2010 express, which uses a SQLexpress database.

    In my application I have the following connection string: "Data Source=.\SQLEXPRESS;AttachDBFilename=D:\CoMo-IE\CoMo-IE mantools\CoMo-iE mantools.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True".

    I have published this application and installed it on the Vista 64-bit machine where also VB 2010 Express and SQL server 2008 express are installed. Everything works fine there. Now I also installed the application to a new Win7 64-bit machine and I created exact the same location for the database and copied the database from the old machine to the new one.

    When starting the application and its first usage of the DB, I get an error 26 (Server not found or cannot connect). I thought that it should be possible to run this app without installing any instances of SQL on the new machine. I tried to install SQL server 2008 on this new machine, but that installation required a lot of settings which I didn't need to enter when installing VB + SQL on the old machine. Therefore I aborted this installation.

    What have I done wrong or what do I need to do to get this application running using the SQL express database?

    Thanks in advance for your replies.

    Wednesday, February 27, 2013 6:53 PM

Answers

  • Hello,

    1st remark : the user instance feature is depreciated since SQL Server 2008 and SQL Server 2012 will be the last version supporting this feature.You should think to use SQL Server Express as a 'normal" edition with some limitations compared to the not-free editions (Web,Standard,Entreprise,...).Moreover, if you have to upgrade to a not-free edition, your application will stop to work as the user instance instance feature is not supported on Web,Standard,... editions. A classical problem several times found in this forum or other SQL Server forums ( and believe me, user instance is a nice feature but it should be reserved to specialists and after having used it during 3 years , i am considering myself as a beginner ).Some problems : backups and restores really complicated and tricky, no remote connections, a database by connected user.For more informations, i would suggest to have a look athttp://blogs.msdn.com/b/sqlexpress/ ( the official blog for the SQL Server Express Team ). You will find many excellent articles written by Mike Wachal ( hiding behind the "nickname" sqlexpress ) who is an excellent teacher.

    2nd remark : as you are using a Visual Studio Express , you have fallen in a classical "trap for beginners" : when you are using the form to connect to the datasource, you have to click on the Advanced button in the right downwards part, you arrive to a new form , at the bottom part of the gridview, you will find the line related to the user instance property, click on the end of the value part, , click on the little appearing arrow and now you are ready to connect to your SQL Server ( any edition ). A conception error from the Visual Studio Team : i met this problem with my SQL Server 2008 Developer with the infameous message : not supported by this edition ( but no problem when i was using my VS Standard 2008 as it is more clever : it does not propose user instance as default ).

    3 rd remark : never use Visual Studio ( except if the Databases Tools edition is included in it ) to create a database.Use always SSMS ( SQL Server Management Studio for not free editions or SSMSE for SQL Server Express ).If you use a VS ( VB/VC# ) Express, it is unable to create a "real" database, only user instance with enormeous potential problems which are outside the knowledges of a beginner and these problems are always treated in the SQL Server Express Forum ).

    Your problem is coming from the use of the "user instance" and from the fact that VS Express is unable to connect to a database without needing a filename instead the name of the database.Moreover, the security has been enforced on Windows 7 compared to Vista.

    Please, could you explain us how you have copied your database ? Maybe your file is in read-only ( if you have copied thru a DVD, it is a classical error and it happened to me several times, you have to give the READ/WRITE permissions on the .mdf and .ldf files ) and sometimes, the error message is not clear.

    Have you copied the transaction logfile  for your database (.ldf) ?

    It is possible that the user of the application has no access ( Read/Write ) permissions on the .mdf file (datafile) or worst on a folfder of the path of your datafile

    Please, could you tell what is the name of your database ( SQL Server does not appreciate spaces or special characters inside the name of database , tables, columns, views,... as you have to surround these names with brackets ie [CoMo-iE mantools], difficult to read and  too many times forgotten ) ?

    A good connection string is Data Source = ComputerName\SQLExpress;DataBase = myDatabase; Trusted_Connection = True

    See http://www.connectionstrings.com/sql-server-2008

    Last remark : the best and surest way to copy a database is to use the backup/restore method. But not evident to do when you are using user instances....

    We are waiting for your feedback to try to help you more efficiently

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, February 27, 2013 9:19 PM
    Moderator