Share via

SQLEXPRESS required as a user instance

  • Article

The other day i wasworking with one cx where he was willing to add one .mdf file to the Visual StudioProject in an XP machine under app_data folder

and he was littlefrustrated why that is failing on one Vista machine and does not work on thisXP machine and i found that OS difference  was not the real difference.While looking into his issue and looking at the Documents/articles,Which are already availableI thought to compile it and put it on the blogs.

 

Specially for the developmentenvironment while developing the database centric application all users doesnot get the necessary permission on the databases because of the businessreasons and that make it nasty . The developers 

 

expect something whichwould give them freedom to develop the database application and put the DBA notto think too much about the Dev permission issues all the times .

 And here comes the Userinstance feature which was intentionally added to the SQLEXPRESS edition ...

To read more about howthe SQL User instance works SQL Server 2005 Express Edition User Instances https://msdn.microsoft.com/en-us/library/bb264564.aspx#

 The difference we foundwas the he had SQL Developer edition installed rather than the SQLExpressone. 

We were actually getting2 type of issues there. The database which the cx wanted to attach with the VSProject was actually attached with the SQL Developer edition at that time andwe were getting the error as below 

 "File cannot be opened because it is being used byanother process" Please note whileworking on my machines sometimes I got favorite "Access Denied error”

 After detaching the SQLServer database from the Developer instance we found the real culprit and theerror was 

 Connections to SQL Server files (*.mdf) requires SQL ServerExpress 2005 to function properly. Please verify the installation of thecomponent or download from the URLhttps://go.microsoft.com/fwlink/LinkId=49251"

 while looking intoreproducing this on my windows 2008 machine  found while the VS is lookingfor the SQLEXPRESS it is reaching to the following registry key.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MicrosoftSQL Server\Instance Names\and then searching for SQLEXPRESS.Remember you cannot fool VS by making one ofthe instances named as SQLEXPRESS as other instance maynot be the User instance.

 

After installing the SQLExpressedition there the issue actually got resolved but i have seen some of theissues where the following option in the VS is not properly set even afterinstallation of the SQLExpress edition  and you  would 

 Like to change it to putSQLEXPRESS there.

 TOOLS --> Options-->-->Database Tools-->Data Connections -->SQL Server InstanceName 

 Here the other questionscomes what would i do to change it to some other instance (for ex SQLEnterprise instance) in my dev?

Well... you would needto change the connection string and to put the user instance =false and alsosetting the correct user permission in the SQL server so that the user is not deprived.And you will have to check which user you are 

Actually passing toconnect to the SQL Server instance before that. 

 

Hopefully aboveinformation will help you..

 

~Lalitesh

Comments