Embedded SQL Server Express Deployment - Unhandled Exception: Database already exists

Good Afternoon.

I am trying to write a short application within which I would like to embed a SQL Server Express database, all of which would be deployed via ClickOnce through a web site. The installation process seems to be working without trouble, including installation of the .NET Framework 2.0 and SQL Server Express 2005.

When the application launches on the new machine, however, I get a big Microsoft .NET Framework "Unhandled exception" error dialog box that indicates the database already exists and that the database file could not be attached. I am working with a clean virtual machine that I know has never had the database installed on it.

I fear I am overlooking something quite straightforward, but since this is the first time I have ever attempted to build a data-bound application, I am not sure where I am going wrong.

Perhaps the source of the problem, or a related problem, might be revealed even when I run the application in debug mode from within Visual Studio. I can run the application once, but if I try to run it again, I get an error when I try to open the database connection that is similar in content (database already exists...could not attach). I can run the SSEUtil and detach the database and then run the application again in debug mode and it works no problem (the first time!).

Lastly, if I install the application on the machine on which it was developed and make sure the database is detached, it will run without any trouble (even repeatedly). But, on a new blank machine, there is no database listed that matches my database name, so I cannot try to detach anything!?

My apologies for such a novice question!

Sean

Hi Sean,

I would suggest removing the ‘Initial Catalog’ keyword from your connection string. Among the things that are stored about a database when it is attached is the Name/Path pairing; SQL Server gets a little upset if you try to attach a database using an existing name but with a different path. Enter the |DataDirectory| macro…

I’m guessing you’re using ClickOnce deployment for this application. There are things that will cause the install directory of a ClickOnce deployment to move, when this happens, VS magically fixes up things as far as the application goes, but it cannot fix up the fact that SQL Server has recorded your explicit database as being linked to the old file location. When you attempt to open the application now, the attach fails because there is already a database named JMU_2007 listed in the master database, but it is associated with a different path, so you get the error that the database already exists. I’m guessing this is the problem you’re hitting.

By removing the ‘Initial Catalog’ keyword, you’re forcing SQL Express to give the database an autoname based on its path. If the database moves as a result of VS behavior, the name will be changed because the path has changed, so it will appear to be a different database from the SQL perspective. Hopefully this helps.

Mike

|||

Thanks very much Mike. That did the trick...I can't believe I overlooked that after spending too much time trying to fix this problem.

Sean

Email if errors Exists in the error tables

I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.

How can i achieve this?

Please let me know

How are you populating those tables? do you want to receive an email on the first/each error or do you want to receive a single email at the end?

|||

Create a SQL Task that returns a value if you want to send an email or not.

Put the returned value in a variable.

Conditionally branch to email if the variable has the found value.

The SQL could be something like SELECT COUNT(1) cnt FROM ErrorTable. Have the SQL task return a single row. In mappings put variable 0 into a variable such as FoundError.

Add an email task, connect the two. Double click the line (constraint) and set it to an expression such as

@.FoundError > 0.

Hope that helps.

|||one single email only

|||

sureshv wrote:

one single email only

Then, Larry's suggestion should work for you. Wait until all data flow are done and then use an Execute SQl task in control flow to check is there were any errors; then you can use an expression in a precedence constraint to decide whether the email should be sent or not. There are another threads in this forums that tackle the how-to-send-an-email question

|||

I have Sql task and in General Tab in the

sql statement : i type SELECT COUNT(1)
FROM dbo.hb_test_error

when i execute and see now it workes fine

result set: from none to singlerow

Then Result set Tab

Variable Name : User::FoundError

Result Name :any name

Then i connect to email task

In precedence constraint editor

Evaluation operator : expression

Expression FoundError >0

i have this error

[Execute SQL Task] Error: Executing the query "SELECT COUNT(1) FROM dbo.hb_test_error" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

How do i solve this?

|||Set Result Name to: 0 (that's a zero)