Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding datafile breaks my log shipping process

Hi All,

I am on sql server 2005. I have a production database that i log ship to another server and keep a standby copy of that database. Transaction logs are backed up every 15 minutes on the production database then copied to the standby server and then applied in order to the read-only standby database.

Every month we add a new partition and datafile to the production database. This causes the log shipping process to break because the read-only standby database doesn't have the new datafile present. I had hoped that the alter database command to create the datafile would be logshipped. It forces me to do a full db restore every month which is a major pain.

Has anyone encountered a similiar scenario? How can I 'log ship' the addition of a datafile every month and avoid doing a full restore of my standby db?

I should add that this is a home grown log ship process, we aren't using the sql server built-in log shipping. Here is a typical backup transaction log script that i'm using:

-- using sql litespeed
exec master..xp_backup_log @.database='dbname,
@.filename='d:\dbbackups\dbname_txlog_<uniqueidentifier>.bak', @.init=1

Any help would be greatly appreciated.
<!--[endif]--> Log shipping cannot handle database file operation, eg. adding new database files. Same operation must be done manually in standby database. <!--[endif]-->

In database mirroring these operations will be handled automatically.