Multi Threading exception

Apologies if this is not the right forum.

We have some c~ code which calls a stored proc to trnsfer data from one db to another.

We are using multi threading to allow a whole days data to be processed in parrallel.

However, we are experiencing problems. After a while we get the following message:

"An exception during changed data captured caused all processing threads to abort".

Can anyone tell me what posssible reasons would cause this to happen?

It is not an area in which I have a lot of expertise, the code was written by a third party.

Many thanks

This is not an error message thrown by SQL Server (or the CLR), so it's likely one produced by the third-party code. You'll need to look at their code to see what actual error is happening to cause them to display this message.

Steven

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

Multi select parameter and code module

How do I pass a multi-select parameter to a code module?Depending on what you want to do with it, probably the easiest way to pass
its values is to use the Join() method.
join(Parameters!myMultieValueParam.Value, ",")
"Asher_N" <ashernat@.gmail.com> wrote in message
news:Xns97E875A63AC991203214562@.207.46.248.16...
> How do I pass a multi-select parameter to a code module?|||That worked, Thanks.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in
news:eBAQtOIlGHA.4512@.TK2MSFTNGP04.phx.gbl:
> Depending on what you want to do with it, probably the easiest way to
> pass its values is to use the Join() method.
> join(Parameters!myMultieValueParam.Value, ",")
>
> "Asher_N" <ashernat@.gmail.com> wrote in message
> news:Xns97E875A63AC991203214562@.207.46.248.16...
>> How do I pass a multi-select parameter to a code module?
>
>

Multi Row Insert

According to the CTP3 BOL the following code should work just fine, unfortunately is giving me the error message (below the code):

create table FamilyNames (

Id int identity,

[Name] varchar(30)

);

go

insert into FamilyNames values (

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen')

);

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Does anybody know why this code fails?

Hi,

Try

insert into FamilyNames values

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen');

Kind regards,

Wesley

|||Thanks.

Multi Column grouping

I have a table with 9 code columns in it. I want a listing of every
possible code in any of the 9 columns with a count of each. Is there a way
to do this without creating a new table that has 9x the rows that the
current table has or without 9 queries plus a sumation query?
The two ways I can get the correct number now are more time consuming that I
would like because they both require "running the table" a number of times
and the table is very large (20MM+ rows).
Thanks,
ScottGROUP BY, of course
SELECT
Col1
, Col2
, Col3
, etc.
, count(1)
FROM MyTable
GROUP BY
Col1
, Col2
, Col3
, etc.
This will provide a row (and its count) for each distinct combination of cod
es.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Scott Cadreau" <scadreau@.aros.net> wrote in message news:2uDmg.289$Mz3.260@.fed1read07...[c
olor=darkred]
>I have a table with 9 code columns in it. I want a listing of every
> possible code in any of the 9 columns with a count of each. Is there a wa
y
> to do this without creating a new table that has 9x the rows that the
> current table has or without 9 queries plus a sumation query?
>
> The two ways I can get the correct number now are more time consuming that
I
> would like because they both require "running the table" a number of times
> and the table is very large (20MM+ rows).
>
> Thanks,
>
> Scott
>
>[/color]

MSSQLServerADHelper

MSSQLServerADHelper failed to start with error code
1073741724 on the initial error message box. When going
into event viewer the error code for the failed service
is 3221225572.
I have tried restarting the service and restarting the
server but it still fails, I have also tried starting the
service with the Admin account.
Any advice would be much appreciated.
ThanksHello,
Theres something it doesn't like about the account. See
http://support.microsoft.com/default.aspx?scid=kb;en-
us;273499.
Try deleing then recreating it.
>--Original Message--
>MSSQLServerADHelper failed to start with error code
>1073741724 on the initial error message box. When going
>into event viewer the error code for the failed service
>is 3221225572.
>I have tried restarting the service and restarting the
>server but it still fails, I have also tried starting the
>service with the Admin account.
>Any advice would be much appreciated.
>Thanks
>.
>

MSSQL Nested queries

Hi,

I have been trying to write a nested query, however i am facing errors while executing that.

The code is as below.

<%

Dim MyConnection =New SqlConnection()

MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString

MyConnection.Open()

Dim cmdAsNew SqlCommandDim rsAs SqlDataReaderDim rs2As SqlDataReaderDim query ="select count(bank_id) from bank_master"Dim query2AsString

cmd.CommandType = CommandType.Text

cmd.Connection = MyConnection

cmd.CommandText = queryrs = cmd.ExecuteReaderIf rs.Read()ThenDim rec = rs(0)If (rec > 0)Then

Response.Write(rec &

" banks registered in the system")rs.Close()

query2 ="select * from bank_master order by bank_id"

rs2 = cmd.ExecuteReader(query2)

If rs2.Read()Then

Response.Write(rs2(0))

Else

Response.Write(

"No banks yet registered in the system")EndIfElse

Response.Write(

"No banks yet registered in the system")EndIfEndIf

%>

The error message that i am getting at runtime:

Server Error in '/Accounts' Application.

---

Input string was not in a correct format.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.FormatException: Input string was not in a correct format.

Source Error:

Line 117: Line 118: query2 = "select * from bank_master order by bank_id"Line 119: rs2 = cmd.ExecuteReader(query2)Line 120: If rs2.Read() ThenLine 121: Response.Write(rs2(0))

Source File:E:\WEBHOME\Accounts\bank.aspx Line:119

Could you please help?

Best Regards,

Debashis

Hi Debashis,

It looks to me like the problem may be that you don't have the line:

cmd.CommandText = query2

So you could have your code like:

 Dim MyConnection =New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString MyConnection.Open()Dim cmdAs New SqlCommandDim rsAs SqlDataReaderDim rs2As SqlDataReaderDim queryAs String ="select count(bank_id) from bank_master"Dim query2As String ="select * from bank_master order by bank_id" cmd.CommandType = CommandType.Text cmd.Connection = MyConnection cmd.CommandText = query rs = cmd.ExecuteReaderIf rs.Read()Then Dim rec = rs(0)If (rec > 0)Then Response.Write(rec &" banks registered in the system") rs.Close() cmd.CommandText = query2 rs2 = cmd.ExecuteReader()If rs2.Read()Then Response.Write(rs2(0))Else Response.Write("No banks yet registered in the system")End If Else Response.Write("No banks yet registered in the system")End If End If

I haven't actually tried this code yet, but if this doesn't work, you could try having a cmd2 instead of re-using the single cmd line. I'm also not too sure why you need to run two queries as you could try this instead (unless I missed something ):

 Dim resultAs Integer = 0' Initialise connectionDim MyConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)' Initialise commandDim cmdAs SqlCommand =New SqlCommand() cmd.Connection = MyConnection cmd.CommandText ="select count(bank_id) from bank_master"' Execute command Using MyConnection result =CType(cmd.ExecuteScalar(),Integer)End UsingIf result > 0Then Response.Write(result &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If

Hope this helps.

All the best,

Paul

|||

Hi Peter,

Hi Peter,
Thanks for your reply. It seems to be working with: cmd.CommandText = query2
However I am still unable to workout the recordcount from the query, as it returns -1.
The code is:

query2 = "select * from bank_master order by bank_id"
cmd2.CommandType = CommandType.Text
cmd2.Connection = MyConnection
cmd2.CommandText = query2
rs2 = cmd2.ExecuteReader
If rs2.HasRows Then

%>
<table>
<%
While rs2.Read()
%>
<tr>
<td>
<%=rs2(1)%>
</td>
</tr>
<%
End While
%>
</table>
<%
Else
Response.Write("No banks yet registered in the system")
End If

Please note that I havent included the recordcount code, as it wrong!

Thanks

Debashis

|||

Hi Debashis,

Sorry, I had missed a line from my code:

Dim resultAs Integer = 0' Initialise connectionDim MyConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)' Initialise commandDim cmdAs SqlCommand =New SqlCommand() cmd.Connection = MyConnection cmd.CommandText ="select count(bank_id) from bank_master"' Execute command Using MyConnectionMyConnection.open() result =CType(cmd.ExecuteScalar(),Integer)End UsingIf result > 0Then Response.Write(result &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If

I've tested it this time and is fine. The other code I gave you also worked, but because there was an extra If ... End if statement than necessary, it also puts the id of the last bank at the end of the line 'banks registered in the system'. therefore, going back to your original code, this also works:

Imports System.DataImports System.Data.SqlClientPartialClass BankInherits System.Web.UI.PageProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim MyConnection =New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString MyConnection.Open()Dim cmdAs New SqlCommandDim rsAs SqlDataReaderDim queryAs String ="select count(bank_id) from bank_master" cmd.CommandType = CommandType.Text cmd.Connection = MyConnection cmd.CommandText = query rs = cmd.ExecuteReaderIf rs.Read()Then Dim recAs Integer = Convert.ToInt32(rs(0))If (rec > 0)Then Response.Write(rec &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If rs.Close()End If End SubEnd Class 

I put this code in the Page_Load event, and ran it OK.

If this has resolved the issue, please don't forget to mark it as the Answer.

Hope it helps,

Paul (not Peter !)

|||

Hi Paul,

Apologies for the mistake.

It seems to be working fine. Thanks for the help..