locked
System.data.sqlclient.SqlException: Database 'Universal' already exists. Choose a different database name. RRS feed

  • Question

  • Hello,

    I have a Sql command that attach database to sql server on onLoad event of a window form. When i run an application for the first time it attach the database successfully. But when i run application for the second time it gives the following error.

    System.data.sqlclient.SqlException: Database 'Universal' already exists. Choose a different database name.

    Interestingly my application runs on Windows XP without any problem. But this problem is only coming on Windows 7. How to overcome this???? Please help me on this issue. An immediate response with concrete solution will be highly appreciated. Thanks
    Thursday, October 6, 2011 6:05 PM

Answers

  • Thanks for the reply,

    when i check the database existence it says the database exists. But it becomes read only at the time of login. I have written in this thread that this code is working fine in Windows XP but not on Windows 7, don't you think that this may be the problem of Windows 7 security settings/permissions on the drive where the software is installed? like i have installed in C drive, thanks.

    Hello!

    Marreena, you've mentioned a number of times that this works on Windows XP, and you've illuded and-or suggested the possibility that this relates to (as you put it) "Windows 7 security settings/permissions". If you believe the problem is caused by UAC in Windows 7, turn it off. If you feel the problem is specific to permissions on the data drive (I believe you mentioned the C-Drive), then model them after your XP environment.

    Here is what I suggest you do:

    (1) Supply the text of the error you're seeing as Erland requests. Just to add, include errors from the instance's error log as well as errors reported by the client.
    (2) Mimic the behavior of your application without using your application and report what happens. Attempt to restore the database manually.

    ~CA


    Adam
    • Marked as answer by Rauf Ab Tuesday, October 11, 2011 4:08 AM
    Monday, October 10, 2011 3:45 PM

All replies

  • Are you using User Instances?

    Judging from the error message, there is no need to attach the database the second time, but just connect to it. Trap the error and use a different connection string.

    Although I think it would be better to attempt to connect the database in the normal way, and only use the attach option if the database is missing. Rather than connecting and trap an error, you could connect to tempdb, and check if the database is in sys.databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 6, 2011 10:01 PM
  • Actually i think this is the permission problem in the windows 7, because it is working perfectly in windows XP. I think i have to set the security of my C dive, but i don't know how to do this........thanks and waiting for the reply.
    Friday, October 7, 2011 3:59 AM
  • I don't know why it works in Windows XP, but obviously for it to work you don't only need to attach the database when you connect, you also need to detach before you disconnect. Why would you do this?

    Maybe you should start investigating why this works in XP at all. Then you can identify the step that falls on Windows 7.

    Or you can implement a solution which is robust and always works.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 7, 2011 7:26 AM
  • Hello, thanks for the reply

    Don't you think it is a problem of permissions in windows 7 on that drive where the software installed?. The only thing i want to do is that when an application runs i need to check that the database exists or not, if not then in exception i attach a database with the help of sql command on OnLoad event of a main form.

    I wasted alot of time of investigating this. So i need a concrete solution to my problem.

    Thanks v much agian.
    Friday, October 7, 2011 4:27 PM
  • First off, don't conclude your post with "An immediate response with concrete solution will be highly appreciated." I'm sure a quick answer would be appreciated, but you're on a public form. Responders aren't paid. You'll get an answer when you get an answer and you'll be thankful when you do. If you want something more then pay someone to provide you with better service.

    Second, what makes you think this is a "SQL Server Security" issue? If I were to agree with your assessment about it being a Windows 7 permission issue, that would still be a different posting-group.

    Third, I don't think it's that you're trying to attach the same database twice. I think you're trying to attach a database under the name as an existing database. The attach aborts first and foremost if a database with the same name already exists.

    Good Luck and remember what I've told you about form etiquette.

    ~CA


    Adam
    Friday, October 7, 2011 8:39 PM
  • Don't you think it is a problem of permissions in windows 7 on that drive where the software installed?.

    From the information you have shared, it is not obvious to me that permissions are an issue. And why would it be the permissions where the software is installed? Wouldn't it be the drive where the database resides?

    The only thing i want to do is that when an application runs i need to check that the database exists or not, if not then in exception i attach a database with the help of sql command on OnLoad event of a main form.

    So you check whether a database exists, find that it doesn't, and then you get an error telling you that the database exists?

    So how do you check that the database exists? Could you share that code? Could you also share the connection string.

    I wasted alot of time of investigating this. So i need a concrete solution to my problem.Thanks v much agian.

    Please bear in mind that the help you get is often in close relation with the amount you share. The less information you give us, the more we have to guess.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 7, 2011 10:09 PM
  • Here is my code which works perfect for Windows XP and my software installed on drive C.

     

    Public Sub dbconnect()
        
            Try
                con = New SqlConnection("Data Source = .\SQLEXPRESS;Initial Catalog=Universal;Integrated Security=True")
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
            Catch ex As Exception
                Dim con1 As SqlConnection = New SqlConnection("Data Source= .\SQLEXPRESS;Initial Catalog=master;Integrated Security=True")
                con1.Open()
                Dim cmd As SqlCommand = New SqlCommand("EXEC sp_attach_db @dbname = 'Universal', @filename1 = '" & Application.StartupPath & "\database" & "\Universal_Data.mdf', @filename2 = '" & Application.StartupPath & "\database" & "\Universal_Log.ldf'", con1)
                Try
                    cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    MsgBox(ex1.ToString)
                    Home.DialogResult = MessageBox.Show("Access denied: please change your security settings     ", "Access denied", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                    Home.Close()
                End Try
                con1.Close()
                Home.DialogResult = MessageBox.Show("Please restart application for the new security measures to take place", "Restart", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Home.Close()

            End Try
        End Sub

     

    In the try section it is definite that if database exists 'try section' code runs, if exception then it attach the database. If you can improve my code then please correct it. Iam still wordering that why it sucessfully runs on windowx XP and why not on Windows 7.

    Thanks a lot for your reply.

     

     

    Saturday, October 8, 2011 4:33 AM
  • So the connection fails, and the code assumes that this is because the database does not exist. Apparently this is a foregone conclusion. You need to investigate why the first connection attempt fails.

    I do have a guess. Do I understand this correctly:

    1) You have a workgroup, not a domain?
    2) You start the application the first time, the database attaches.
    3) You close the application.
    4) You start the application again, now you get the error the application
       does not exist.

    I suspect the reason that you cannot connect to the database the second time is that your login does not map to a user in that database. You had an old computer, call it OLDBOX on which you had a user OLDBOX\Marreena. This user was granted access to SQL Server on the old machine and to the database.

    Now you are on the new machine, call it NEWBOX, and you have added NEWBOX\Marreena as a login on the new server, but you have not added NEWBOX\Marreena to the new machine. Here you may object that the two machines actually has the same names, but that's irrelevant, because the mapping is done by SID, and they will be different.

    If my theory is correct, you need to connect to SQL Server through Management Studio or SQLCMD and add yourself as a user to the database.

    If this theory is correct, the difference between the OSs does not matter.

    I like to add that this is a complete guess, and we would need to see the error message from the failed connection attempt.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 8, 2011 9:01 AM
  • Hello,

    Sorry for the late reply because of sunday.

    1) I dont have workgroup or domain this is a single standalone computer/server.

    2) I start the application the first time, the database attaches and immediately database becomes read only when second time i try to extract the  data or just login. and neither i detach or delete the database.

    That is actually the problem.

    Many thanks for your kind help.

     

     

     

    Monday, October 10, 2011 4:58 AM
  • Hi There 

    I Failed to Understand. Why your connection is failing. if it is because of you universal database doesnt exists then why its says universal database already exists why you try to attached the db. Use Following Function which checks whether db exists before attaching it.

    Public Sub dbconnect()
         
            Try
                con = New SqlConnection("Data Source = .\SQLEXPRESS;Initial Catalog=Universal;Integrated Security=True")
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
            Catch ex As Exception
                Dim con1 As SqlConnection = New SqlConnection("Data Source= .\SQLEXPRESS;Initial Catalog=master;Integrated Security=True")
                con1.Open()
                Dim cmd As SqlCommand = New SqlCommand("If DB_ID('Universal') is null EXEC sp_attach_db @dbname = 'Universal', @filename1 = '" & Application.StartupPath & "\database" & "\Universal_Data.mdf', @filename2 = '" & Application.StartupPath & "\database" & "\Universal_Log.ldf'", con1)
                Try
                    cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    MsgBox(ex1.ToString)
                    Home.DialogResult = MessageBox.Show("Access denied: please change your security settings     ", "Access denied", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                    Home.Close()
                End Try
                con1.Close()
                Home.DialogResult = MessageBox.Show("Please restart application for the new security measures to take place", "Restart", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Home.Close()
    
            End Try
        End Sub
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Monday, October 10, 2011 6:21 AM
  • Thanks for the reply,

    when i check the database existence it says the database exists. But it becomes read only at the time of login. I have written in this thread that this code is working fine in Windows XP but not on Windows 7, don't you think that this may be the problem of Windows 7 security settings/permissions on the drive where the software is installed? like i have installed in C drive, thanks.

    Monday, October 10, 2011 7:28 AM
  • Hi Marreena

    You can following code. This will set your database to read_write mode after attaching it.

    if your database already exists in sql instance and read only mode then run following query to bring it in read_write mode

    alter database Universal set read_write
    


    Public Sub dbconnect()
         
            Try
                con = New SqlConnection("Data Source = .\SQLEXPRESS;Initial Catalog=Universal;Integrated Security=True")
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
            Catch ex As Exception
                Dim con1 As SqlConnection = New SqlConnection("Data Source= .\SQLEXPRESS;Initial Catalog=master;Integrated Security=True")
                con1.Open()
                Dim cmd As SqlCommand = New SqlCommand("If DB_ID('Universal') is null EXEC sp_attach_db @dbname = 'Universal', @filename1 = '" & Application.StartupPath & "\database" & "\Universal_Data.mdf', @filename2 = '" & Application.StartupPath & "\database" & "\Universal_Log.ldf' ; alter database Universal set read_write", con1)
                Try
                    cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    MsgBox(ex1.ToString)
                    Home.DialogResult = MessageBox.Show("Access denied: please change your security settings     ", "Access denied", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                    Home.Close()
                End Try
                con1.Close()
                Home.DialogResult = MessageBox.Show("Please restart application for the new security measures to take place", "Restart", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Home.Close()
    
            End Try
        End Sub
    
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Monday, October 10, 2011 8:34 AM
  • when i check the database existence it says the database exists. But it becomes read only at the time of login. I have written in this thread that this code is working fine in Windows XP but not on Windows 7, don't you think that this may be the problem of Windows 7 security settings/permissions on the drive where the software is installed?

    When you try to connect the second time, and the database exists, you get an exception. Could you please post the text of that message? Not the message that "Universal already exists" you get when you incorrectly tries to attach it a second time, but the first exception.

    And, no, file permissions in Windows 7 does not have anything to do with it.
    The fact that you are not running as administrator by default in Win7 may matter, but we'll see.


    Erland Sommarskog, Stockholm, esquel@sommarskog.se


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 10, 2011 1:54 PM
  • Thanks for the reply,

    when i check the database existence it says the database exists. But it becomes read only at the time of login. I have written in this thread that this code is working fine in Windows XP but not on Windows 7, don't you think that this may be the problem of Windows 7 security settings/permissions on the drive where the software is installed? like i have installed in C drive, thanks.

    Hello!

    Marreena, you've mentioned a number of times that this works on Windows XP, and you've illuded and-or suggested the possibility that this relates to (as you put it) "Windows 7 security settings/permissions". If you believe the problem is caused by UAC in Windows 7, turn it off. If you feel the problem is specific to permissions on the data drive (I believe you mentioned the C-Drive), then model them after your XP environment.

    Here is what I suggest you do:

    (1) Supply the text of the error you're seeing as Erland requests. Just to add, include errors from the instance's error log as well as errors reported by the client.
    (2) Mimic the behavior of your application without using your application and report what happens. Attempt to restore the database manually.

    ~CA


    Adam
    • Marked as answer by Rauf Ab Tuesday, October 11, 2011 4:08 AM
    Monday, October 10, 2011 3:45 PM