ERROR: Subreport: records

Showing posts with label records. Show all posts

Friday, March 9, 2012

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.
>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>> We have been getting an error on a DTS package
>> that is trying to load about 800,000 records.
>> It used to work without problems.
>> Now.. It will fail anywhere between record 8000
>> and record 160,000.
>> It is not one set record. Here is the error message.
>> [DBNETLIB][CONNECTIONWRITE (SEND()).]
>> GENERAL NETWORK ERROR
>> It will get this error even when the server itself
>> runs the package, and the file is on the server's
>> disk drive.
>> Does anyone have any ideas?
>
>.
>|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
> >--Original Message--
> >Looks to me like the target SQL Server has re-set the sql
> connection due to
> >severe error of some sort - either sql engine error or
> windows error
> >perhaps. Have you checked the server logs on the target
> server - both SQL &
> >Windows logs to see if there's any further information
> available?
> >
> >This could be such a wide variety of things - even disk
> failure or table /
> >index corruption within SQL Server. You might try running
> a dbcc checkdb on
> >the target server as well as part of your investigations.
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Scot" <sstyer@.alltel.net> wrote in message
> >news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> >> We have been getting an error on a DTS package
> >> that is trying to load about 800,000 records.
> >> It used to work without problems.
> >> Now.. It will fail anywhere between record 8000
> >> and record 160,000.
> >> It is not one set record. Here is the error message.
> >>
> >> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> >> GENERAL NETWORK ERROR
> >>
> >> It will get this error even when the server itself
> >> runs the package, and the file is on the server's
> >> disk drive.
> >>
> >> Does anyone have any ideas?
> >>
> >
> >
> >.
> >

Wednesday, March 7, 2012

Errors (ADODB)

Hi ! I'm using adodb in my program (Visual Basic)

I'm controlling the records with sql in a timer..Timer Interval = 7000

But i am getting some errors sometimes

Connectionwrite(send())

connectionwrite(recv())

It's often working non-problem but sometimes i am getting above errors

What are theese? and how can i solve this problem?

Thanks.

There might be several reasons.
Please have a look at A special GNE (General Network Error) messages when running SQL Server after installing service pack 1 for Windows Server 2003 and TCP registry key SynAttackProtect
You might also want to run a network trace to see what happens on a network layer.

ErrorCode and ErrorColumn in Excel Destination

I am exporting records with errors to Excel using the Excel Destination tool. The ErrorColumn is a numeric. How do I find out which column it is?

If you want to do this manually (i.e. not programmatically), you can open the Advanced UI for the Excel Dest, and go to the "Input and Output Properties" tab. There, under "Excel Destination Input", you can look through the colums. As you click on each one, look for the "ID" property on the right that has the value you found in the ErrorColumn field of the error row.

Let me know if this helps or not.

Thanks,
Mark|||Hi

How would you do this programmatically? Ideally, I'd like to include the actual column name as another output column in my error destination table. Also, is it possible to obtain the error description as well as the ErrorCode (similar to what you might see in the Execution Results pane when debugging)?

Thanks

Rob|||Hi Rob

I want to do something similar but write out the column name to a table along with the data when rejected. Did you ever figure out how to do this?

Thanks

Marcus|||While it's fairly easy to add an ErrorDescription column on top of the ErrorCode, it's NOT so easy to add an ErrorColumnName on top of the ErrorColumn numeric ID value, because of all the ifs ands and buts that determine whether the column name is readily available or not. (For example, did the preceding component have synchronous or asynchronous outputs, and so forth.)

You can run your error output through a Script Component and try looping through the columns in its InputColumnCollection and VirtualInputColumnCollection looking for the ID value that you've got in the ErrorColumn column to see that this information is usually unavailable.

-Doug

Friday, February 24, 2012

Error: There is already an open DataReader associated with this Command

I'm trying to loop through all the records in a recordset and perform a database update within the loop. The problem is that you can't have more than one datareader open at the same time. How should I be doing this?

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & dtrPhoto("AuthorityID"), conWhitt)

cmdUpdate.ExecuteNonQuery()

EndIf

EndWhile

Thanks

What you could do is create a update sub that you just passed the ID of the photo to, which then set the NoPhoto to 1

i.e.

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

SetPhoto(dtrPhoto("AuthorityID"))

cmdUpdate.ExecuteNonQuery()

Then create a sub

Sub SetPhoto(Byval id as Integer)

' Your database stuff

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & id, conWhitt)

End Sub

Hope that helps Smile

|||

Thanks, I thought that would work but I get the same error Sad

PrivateSub Page_Load(ByVal senderAs System.Object, _

ByVal eAs System.EventArgs)HandlesMyBase.Load

Dim cmdPhotoAs SqlCommand

Dim dtrPhotoAs SqlDataReader

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") &".jpg")And dtrPhoto("Pref") <>""Then

NoPhotoUpdate(dtrPhoto("Pref"))

EndIf

EndWhile

conWhitt.Close()

dtrPhoto.Close()

EndSub

Sub NoPhotoUpdate(ByVal PrefAsInteger)

Dim cmdUpdateAs SqlCommand

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref =" & Pref, conWhitt)

cmdUpdate.ExecuteNonQuery()

EndSub

|||

Hi there

Sorry i didnt reply sooner, i was moving house!

The problem your having is that the NoPhotoUpdate sub cannot access the datareader of the page_load sub.

(its a bit of a git to get your head around at first, then when you do you will wonder how you ever managed without it!)

You might want to get a book on OOP for asp.net - The dummies book one is quite good (and written by a comedian!), but covers only vb1.1 (AFAIK).

Anyways that said..

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load
SelectPhoto()

End Sub

Sub SelectPhoto()
Dim cmdPhoto As SqlCommand
Dim dtrPhoto As SqlDataReader

cmdPhoto = New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()
dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

If Not File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") & ".jpg") And dtrPhoto("Pref") <> "" Then

NoPhotoUpdate(dtrPhoto("Pref"))

End If

End While

conWhitt.Close()

dtrPhoto.Close()

End Sub

Sub NoPhotoUpdate(ByVal Pref As Integer)

Dim strConnectionString As String
strConnectionString = ' Your connection String

Dim DBConn As New SqlConnection(strConnectionString)
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
DBConn.Open()

DBCmd = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref = @.Pref", DBConn)
DBCmd.Parameters.Add("@.Pref", SqlDbType.NVarChar).Value = Pref
DBCmd.ExecuteNonQuery()
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing

End Sub

The whole @. thing is a safer way to pass paramaters to your SQL

http://weblogs.asp.net/scottgu/archive/2006/09/30/Tip_2F00_Trick_3A00_-Guard-Against-SQL-Injection-Attacks.aspx for a explanation

Notice how each sub is a seperate little program, with one activating the other whilst passing data.

I hope ive been of some help

|||

Thanks that works. Thanks also for the heads up about sql injection attacks. I do normally pass my parameters the safe way but was not aware of the dangers of not doing so.

Enjoy your new house Smile

|||

I'm having the same problem and i have to believe there is a better solution then creating a sep sub and then creating another connection. Based on your solution you should problably just create a second connection in the first sub and just use that, no need for the whole extra nophotoupdate sub.

Having said that there needs to be a solution for activly using the same connection at the same time. In good old ADO days we were able to open many recordsets and execute sql commands all at the same time with one DB connection. There is a way to do this here.

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

End Sub

|||

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

End Sub

|||

I found the solution on the msdn forums. Seems to only work for SQL 2005

"This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string)."

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

Error: The sort order specified for distinct count records is incorrect

When processing a measure group with a distinct count measure in it, i get the following error:

"The sort order specified for distinct count records is incorrect."

I have no idea what this means - any ideas?

whoops - looks like there's some info here:

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

Friday, March 9, 2012

Errors during DTS package

Errors during DTS package

Errors during DTS package

Wednesday, March 7, 2012

Errors (ADODB)

ErrorCode and ErrorColumn in Excel Destination

Friday, February 24, 2012

Error: There is already an open DataReader associated with this Command

Error: The sort order specified for distinct count records is incorrect

ERROR: Subreport

Blog Archive

About Me