SqlPackager with Sql Server 2005 SP1

Freda

Hi
We have been using the sql packager to deploy our databases on Sql Server 2005 and all was working fine until a client upgraded to Service Pack 1. Now when the deployment executable is executed, we get the following error -

<SQLError Package="WellnomicsSQLDatabase1"><Error>Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</Error><Assembly>WellnomicsSQLDatabase1</Assembly><Package>Package1.resx</Package><Batch>Batch341</Batch><SQL>


It happens when it is trying create a function which uses an xml schema collection. The same error occurs when we tried to deploy it on the newest version of SqlExpress.

Any help would be very much appreciated.
Thanks
Karen
[/code]

Brian Donahue

Hi Karen,

I think it may help to use the 'disable DDL triggers' option in Packager's schema settings, and possibly even in the data settings. The deadlock could be caused by Packager and SQL Server 2005 both fighting to update the same object. This could possibly prevent this situation from happening if it is caused by a DDL trigger in the database.

Freda

Thanks for the reply Brian

I disabled ddl triggers in the schema first and reran the deployment but no joy. So disabled the ddl triggers from the data and when I ran the packager UI, I got the following error:
Generating SQL - Generating Sql script-Data-Generating Sql scripts
Object reference not set to an instance of an object

I then used the packager on the command line. Using the same package project, it created the executable file properly. Unfortunately the original problem is still there.
I ran the produced script directly in management studio and it has the same problem in the same place. If I take out the offending function, the problem happens later on, again with a function using an xml schema collection.
Do you know if anything has changed with the xml schema functionality within packager or sql server?

Thanks

Karen

Brian Donahue

Hi Karen,

Is this an 'upgrade' package or a 'new database' package?

Thanks!

Freda

Hi Brian

Its a new database package.

Thanks

Karen

Brian Donahue

Hi Karen,

Are you using the package to create a new database? You are not running the package against an already existing database?

Freda

Hi

We are using the package to create a brand new database.

Thanks

Brian Donahue

Are there any DDL triggers being created in the database package? Sorry I keep going in this direction, but since there are two processes contending for the same resources, and packager only opens one connection, the deadlock winner can't be a process that Packager is running under. The only explanation I can come up with is a SQL Server process and DDL triggers would be easy to blame.

Brian Donahue

Hello,

Apparently this is a bug in Microsoft SQL Server 2005 that was introduced in SP1. It looks like there is no workaround at the present time except to split your package into several smaller ones.

Andras has the full scoop on the Simple-Talk website:

http://www.simple-talk.com/community/bl ... 9/859.aspx

Sorry for the inconvenience.

Freda

Thanks for your help Brian. Its a relief to have an answer and now a plan of attack for this.

Thanks again