Aborting a thread CRASHES Sql Server 2000!

Hi,

I'm creating a new thread and executing a database import operation using a transaction. I have had several problems that I cannot explain, the most serious of them being that aborting this thread sometimes crashes the instance of Sql Server to which I was connected!

The application is a Windows Forms app, and when I launch the import operation I display a form that allows me to abort the import while it is in progress. If the user decides to abort the import in the middle of things, I call the Abort() method of the thread executing the import.

I'm catching any exceptions in the method on the bottom of the call stack (the ThreadStart delegate) and logging the information, and from the stack trace I can see that the ThreadAbortException happened to occur a few levels into the internals of the SqlCommand.ExecuteNonQuery method. This is where it is most likely to occur, because this is where the thread spends most of it's time, as the import consists of executing a bunch of large script files (often many megabytes of script text in a single round trip).

So far so good, but now things get a bit strange. The connection state is Closed when I catch the ThreadAbortException, indicating that ADO.NET code (probably ExecuteNonQuery) caught the excpetion, closed the connection and rethrew it. I would expect in this case that Sql Server would rollback the pending transaction - despite the application not issuing an explicit rollback command, since (local) transactions cannot span across connections anyway.

Instead, this completely CRASHES the Sql Server instance! It's not just a matter of locks aquired during the transaction not being released; it is no longer possible to connect to another *catalog* using Query Analyzer, or to view "current activity" using Enterprise Manager. Nor is it possible to shut down Sql Server using the management console - it just changes status to say that shutdown is in progress (I have the French version, the wording might be slightly different in the English user interface) and then nothing happens. I went to lunch to give Sql Server plenty of time to recover, but nothing changed.

In the end, I had to REBOOT the server in order to bring Sql Server back to life.

I'm using SqlClient with .NET 1.1 and Sql Server 2000 on Windows Server 2003 with all service packs and critical updates.

I should probably mention that I am NOT using SqlTransaction but instead sending "BEGIN TRANSACTION" and the corresponding commit/rollback commands to Sql Server using SqlCommand.ExecuteNonQuery. But please, do not allow this to take the focus away from the question of how to avoid Sql Server crashes, because this is NOT correct behavior on SQL Server's part (or possibly SqlClient) regardless of my application code! There is actually a reason I don't use SqlTransaction: I've no idea why the behavior is not the same, but when I used SqlTransaction and the size of the transaction becomes really large, SqlTransaciton.Rollback() invariably fails with an exception complaining that "the server did not respond". I don't know why it works when I just use text commands instead, but for the moment at least I think it is more important to focus on the crashing of Sql Server.

For now, I've had to change the abort logic so that instead of calling Thread.Abort() I just set a flag, then the thread executing the import will check this flag after each round-trip to the database and throw an exception in the event that abort has been requested. This works, i.e. everything is rolled back and sql server stays alive and all, but it does mean that the user might have to wait quite a while after requesting Abort until the program actually stops executing and rolls back the transaction.

I hope someone can give me some answers with this as I'm beginning to really lose faith in Sql Server as a reliable backend database for anything a bit demanding; completely crashing the server just because I abort a thread seems a tad fragile to be honest.

Here's the stack trace for the ThreadAbortException (it's the French-resourced version of the Framework, but this obviously shouldn't make any difference):

System.Threading.ThreadAbortException: Le thread a t abandonn.
à SNINativeMethodWrapper.SNIPacketGetConnection(IntPtr packet)
à System.Data.SqlClient.TdsParserStateObject.ProcessSniPacket(IntPtr packet, UInt32 error)
à System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
à System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
à System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
à System.Data.SqlClient.TdsParserStateObject.ReadByte()
à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
à Import.Worker.executeSql(String sql, Boolean log)

My program catches the exception, logs it (as you can see) and continues executing (not the aborted thread!) normally, but Sql Server is dead and apparently can be revived only by rebooting the host computer...!

|||

Have you applied the lastes patch for sql server 2000? It might be a bug in the server. The following KB article might be related to your problem.

http://support.microsoft.com/kb/914298/

|||

Moving the thread to the SQL Server Engine.

|||

i saw a similar problem when aborting a backup,

the system particulars are 16P Itanium2, W2K3, SQL2000 sp3 + hotfix 1027, fiber mode.

the problem did not appear w/o fiber mode or on sp4 and later 2000 level builds

some aspects of this problem did not replicate consistently between difference systems,

Aborting a thread CRASHES Sql Server 2000!

Hi,

I'm creating a new thread and executing a database import operation using a transaction. I have had several problems that I cannot explain, the most serious of them being that aborting this thread sometimes crashes the instance of Sql Server to which I was connected!

The application is a Windows Forms app, and when I launch the import operation I display a form that allows me to abort the import while it is in progress. If the user decides to abort the import in the middle of things, I call the Abort() method of the thread executing the import.

I'm catching any exceptions in the method on the bottom of the call stack (the ThreadStart delegate) and logging the information, and from the stack trace I can see that the ThreadAbortException happened to occur a few levels into the internals of the SqlCommand.ExecuteNonQuery method. This is where it is most likely to occur, because this is where the thread spends most of it's time, as the import consists of executing a bunch of large script files (often many megabytes of script text in a single round trip).

So far so good, but now things get a bit strange. The connection state is Closed when I catch the ThreadAbortException, indicating that ADO.NET code (probably ExecuteNonQuery) caught the excpetion, closed the connection and rethrew it. I would expect in this case that Sql Server would rollback the pending transaction - despite the application not issuing an explicit rollback command, since (local) transactions cannot span across connections anyway.

Instead, this completely CRASHES the Sql Server instance! It's not just a matter of locks aquired during the transaction not being released; it is no longer possible to connect to another *catalog* using Query Analyzer, or to view "current activity" using Enterprise Manager. Nor is it possible to shut down Sql Server using the management console - it just changes status to say that shutdown is in progress (I have the French version, the wording might be slightly different in the English user interface) and then nothing happens. I went to lunch to give Sql Server plenty of time to recover, but nothing changed.

In the end, I had to REBOOT the server in order to bring Sql Server back to life.

I'm using SqlClient with .NET 1.1 and Sql Server 2000 on Windows Server 2003 with all service packs and critical updates.

I should probably mention that I am NOT using SqlTransaction but instead sending "BEGIN TRANSACTION" and the corresponding commit/rollback commands to Sql Server using SqlCommand.ExecuteNonQuery. But please, do not allow this to take the focus away from the question of how to avoid Sql Server crashes, because this is NOT correct behavior on SQL Server's part (or possibly SqlClient) regardless of my application code! There is actually a reason I don't use SqlTransaction: I've no idea why the behavior is not the same, but when I used SqlTransaction and the size of the transaction becomes really large, SqlTransaciton.Rollback() invariably fails with an exception complaining that "the server did not respond". I don't know why it works when I just use text commands instead, but for the moment at least I think it is more important to focus on the crashing of Sql Server.

For now, I've had to change the abort logic so that instead of calling Thread.Abort() I just set a flag, then the thread executing the import will check this flag after each round-trip to the database and throw an exception in the event that abort has been requested. This works, i.e. everything is rolled back and sql server stays alive and all, but it does mean that the user might have to wait quite a while after requesting Abort until the program actually stops executing and rolls back the transaction.

I hope someone can give me some answers with this as I'm beginning to really lose faith in Sql Server as a reliable backend database for anything a bit demanding; completely crashing the server just because I abort a thread seems a tad fragile to be honest.

Here's the stack trace for the ThreadAbortException (it's the French-resourced version of the Framework, but this obviously shouldn't make any difference):

System.Threading.ThreadAbortException: Le thread a t abandonn.
à SNINativeMethodWrapper.SNIPacketGetConnection(IntPtr packet)
à System.Data.SqlClient.TdsParserStateObject.ProcessSniPacket(IntPtr packet, UInt32 error)
à System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
à System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
à System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
à System.Data.SqlClient.TdsParserStateObject.ReadByte()
à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
à Import.Worker.executeSql(String sql, Boolean log)

My program catches the exception, logs it (as you can see) and continues executing (not the aborted thread!) normally, but Sql Server is dead and apparently can be revived only by rebooting the host computer...!

|||

Have you applied the lastes patch for sql server 2000? It might be a bug in the server. The following KB article might be related to your problem.

http://support.microsoft.com/kb/914298/

|||

Moving the thread to the SQL Server Engine.

|||

i saw a similar problem when aborting a backup,

the system particulars are 16P Itanium2, W2K3, SQL2000 sp3 + hotfix 1027, fiber mode.

the problem did not appear w/o fiber mode or on sp4 and later 2000 level builds

some aspects of this problem did not replicate consistently between difference systems,

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,
Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thank
s,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

Ability to import Data into SQL Server Express Edition

I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.

a solution

Hi,

I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.

Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.

Set the Excel connection Extended Property, IMEX=1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1294377&SiteID=1|||Thanks, it worked. Appreciated.|||

Imex=1 worked for all the cells except few, this column has data like below

07AWA38V36 0717062042 715018020 07BCB01R17

even in this column some has been imported correctly and few are imported like 7.15E+...

Note: My desination column data type in Varchar.

|||Make sure that Excel doesn't have any formatting for the problem cells or the problem column.|||In the above example for the second row the data was formatted as text becasue it has the leading Zero where as the third is not formatted becuase it doesnt have any leading zero. the issue was in the third row coverting as 7.15E+.. while running the SSIS|||

yes, i′ve got just the same problem.

incredibly it didn′t happen the first time i runned the dts, but now..

|||

..and i just got it..

I selected the whole worksheek, converted the cells into numeric type (format/cells/numeric) and saved. Then i converted the cells into General type again, saved and runned the dts. And it works now.

Better not to use Text types when it happens. When you′ve got a Text type it doesnt work properly, and if you change to General => it doesnt work either. But if you had Numeric types and change to General then it works.

|||

When I change this to Numeric then I will loose the leading zero in the 2 & 3 row.

|||

? no, you wont..

It′s just an excel fail, i doesnt catch properly that there′s a number, not a text, when you had a text previously

A problem with DTS Import/Export wizard....

Hello,
I am trying to replace data in the "region" column in table 2 with data in
the "region" column in table 1...in other words 1 is the source and 2 is the
destination. I go thru the wizard and then use a query to specify the data
to transfer. I go into the query builder and select the column that I want
transfered. I don't specify a sort order or criteria. The query statement
is thus:
select [Customers].[Region]
from [Customers]
easy enough...
I then select the source table and click transform and I do not see an
option to replace the data only append, create destination table, and delete
rows in destination table.
Is this all the functionality of the wizard gives me?
Do I need to create my own script?
Thanks
Ken S.I am sure that someone can help, but we will need some additional =information...
Do the tables (table1 and table2) share a common column that you can =JOIN on in order to perform an update?
What other data exists within Table2? Does all the data come from =Table1? Can you simply delete all the rows within Table2 and insert =from Table1?
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> > I am trying to replace data in the "region" column in table 2 with =data in
> the "region" column in table 1...in other words 1 is the source and 2 =is the
> destination. I go thru the wizard and then use a query to specify the =data
> to transfer. I go into the query builder and select the column that I =want
> transfered. I don't specify a sort order or criteria. The query =statement
> is thus:
> > select [Customers].[Region]
> from [Customers]
> > easy enough...
> > I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and =delete
> rows in destination table.
> > Is this all the functionality of the wizard gives me?
> > Do I need to create my own script?
> > Thanks
> > Ken S.
> >|||Thanks Keith...
both tables are identical tables with the same structure and row count. The
region column in table 1 is the data I want in table 2's region column.
Perhaps your solution of deleting all the rows in table 2 and replacing with
table 1 rows is the way to go.
Thanks
Ken
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
I am sure that someone can help, but we will need some additional
information...
Do the tables (table1 and table2) share a common column that you can JOIN on
in order to perform an update?
What other data exists within Table2? Does all the data come from Table1?
Can you simply delete all the rows within Table2 and insert from Table1?
--
Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am trying to replace data in the "region" column in table 2 with data in
> the "region" column in table 1...in other words 1 is the source and 2 is
the
> destination. I go thru the wizard and then use a query to specify the
data
> to transfer. I go into the query builder and select the column that I
want
> transfered. I don't specify a sort order or criteria. The query
statement
> is thus:
> select [Customers].[Region]
> from [Customers]
> easy enough...
> I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and
delete
> rows in destination table.
> Is this all the functionality of the wizard gives me?
> Do I need to create my own script?
> Thanks
> Ken S.
>|||Since the tables are identical you could also update the data with an =update statement:
UPDATE table2 SET region =3D B.region
FROM table2 A JOIN table1 B ON A.ThePrimaryKeyColumn =3D =B.ThePrimaryKeyColumn
SELECT @.@.rowcount
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:uKByO2jxDHA.3116@.tk2msftngp13.phx.gbl...
> Thanks Keith...
> > both tables are identical tables with the same structure and row =count. The
> region column in table 1 is the data I want in table 2's region =column.
> Perhaps your solution of deleting all the rows in table 2 and =replacing with
> table 1 rows is the way to go.
> > Thanks
> > Ken
> > > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I am sure that someone can help, but we will need some additional
> information...
> > Do the tables (table1 and table2) share a common column that you can =JOIN on
> in order to perform an update?
> > What other data exists within Table2? Does all the data come from =Table1?
> Can you simply delete all the rows within Table2 and insert from =Table1?
> > --
> Keith
> > > "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am trying to replace data in the "region" column in table 2 with =data in
> > the "region" column in table 1...in other words 1 is the source and =2 is
> the
> > destination. I go thru the wizard and then use a query to specify =the
> data
> > to transfer. I go into the query builder and select the column that =I
> want
> > transfered. I don't specify a sort order or criteria. The query
> statement
> > is thus:
> >
> > select [Customers].[Region]
> > from [Customers]
> >
> > easy enough...
> >
> > I then select the source table and click transform and I do not see =an
> > option to replace the data only append, create destination table, =and
> delete
> > rows in destination table.
> >
> > Is this all the functionality of the wizard gives me?
> >
> > Do I need to create my own script?
> >
> > Thanks
> >
> > Ken S.
> >
> >
> >

A good SSIS book?

I am new to SQL2005 and have been given the task of writing some SSIS packages to import some CSV files.

I need to cleans the data as it is imported from my CSV files before it reaches my SQL DB.

I am currently Googling the internet to discover how to do this.

Can anyone recommend a good SSIS book?

I am a C# developer, so a book that has lots of SSIS C# examples would be good.

Any help appreciated.

Regards,

Paul.

SSIS(SQL Server Integration Services) is a full ETL(extraction transformation and loading) tool so moving CVS is very easy task I don't think you need a book to move CVS files to SQL Server because the only issue with CVS files is SQL Server sees null values due to the nature of the file. The solution is to move the file to a temp table before destination because if you move it directly to a table with primary key your package will be rejected. I have found you a two part article by Microsoft and the site run by first DTS and now SSIS experts, this is the calculus end of the relational model, the data is cleaned of algebra and moved to read only tables for Dimension modeling and Prediction modeling. I will not recommend a book because most things on the calculus end of SQL Server is new so you need to browse some books at your local book store and choose the one that meets your needs I found three books on the service. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa964134.aspx

http://www.sqlis.com/

|||

I have ordered a SSIS book from Amazon - Looks like I'm a steep learning curve.

Thanks for the help.

Regards,

Paul.

|||

wadep:

I have ordered a SSIS book from Amazon - Looks like I'm a steep learning curve.

Thanks for the help.

Regards,

Paul.

I am glad I could help, just remember to start with the wizard and use code as needed.