Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jhunt
Starting Member
21 Posts |
 Posted - 2002-10-09 : 21:43:46
|
| Hi thereI am trying to write a program that will update existing data on a users database, by deleting all existing records and using DMO and the bulkcopy object to import the updated data from a textfile on a CD.It all works fine when I don't use a transaction. But when I run the code within a transaction, the program hangs at the SQL-DMO Table.ImportData() method call, and never returns. Sorry I don't know much about how SQL Server works with log files and transactions, but I am sure that ImportData can be used within a transaction.What am I doing wrong? ThanksJustin Hunt |
|
|
jhunt
Starting Member
21 Posts |
Posted - 2002-10-09 : 21:54:24
|
| OK sorry my description of the error was a bit off. (I have been trying all sorts of things.)It just hangs, if the DMO transaction is nested inside an ADO transaction. However if we start only the DMO transaction, the error is somthing like:"The UPDATE STATISTICS statement is not permitted inside a multiple statement transaction."The Bulkcopy object settings are pretty vanilla. With objBulkCopy .DataFileType = SQLDMODataFile_TabDelimitedChar .MaximumErrorsBeforeAbort = 0& .IncludeIdentityValues = True .DataFilePath = strFilePathEnd WithHow would I avoid the above error(s). thanks. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-10 : 07:47:22
|
| I'm taking a shot in the dark here ...When you instantiate a SQL-DMO SQLServer object and execute method from it, that connection will exists outside the current connection.Take a look at this thread. It uses this fact as a foundation for a means to log activity that will not be rolled back on error.My guess would be that somewhere in your ADO transaction, probably the spot where you truncate the table, you are placing locks on the object that block your BulkCopy.If I were you, I would try BulkCopying into a staging table, and then in your transaction, truncate and then insert from the staging table.Is this close?Jay White{0} |
|
|
|
jhunt
Starting Member
21 Posts |
Posted - 2002-11-06 : 00:00:29
|
| Sorry I actually had a 2 week vacation and haven't been back to check the forums.Yeah that looks pretty close actually. In the end I decided that using DMO and ADO in the same transaction was a bit too dodgy. For the record what i ended up doing was ..I used the bulk insert transact SQL statement and do it all within the ADO transaction.Had a problem with SQL Server 7.0. It throws an error on bulk insert if a numeric or decimal column has a default value(MS Bug: documented). So had to drop the default and reinstate it after the bulk insert transaction was finished.Thanks for your help. |
|
|
|
|
|
|
|
|
DMO BulkCopy and Transactions