DTS execution error

DTS execution error

Post by Dhava » Wed, 23 Jul 2003 02:19:57


Hi

Dhaval made DTS to fetch data from oracle. While exectuing
a particular step it gives me the following error. It
fetches around 35,00,000 records and it gives me error at
around 34,00,000 record. can any one help me in this...

Error description Description:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this
time. Rerun your statement when there are fewer active
users or ask the system administrator to check the SQL
Server lock and memory configuration.

Regards
Dhaval
 
 
 

DTS execution error

Post by Allan Mitc » Wed, 23 Jul 2003 15:52:09

Have a look at this in BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\trb
lsql.chm::/tr_reslsyserr_1_6gxg.htm
----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.yqcomputer.com/

 
 
 

DTS execution error

Post by Olivi » Wed, 04 Feb 2004 04:26:54

My DTS excution failed because one of my ActiveX script
got timed out. I run the same epackage again and it run
just fine including this particular ActiveX script. How
can I find out why this happen so I can prevent it? I've
also checked the server events during the time of the
problem and I didn't find anything to justify this. Thank
you.

Here is the log error and the ActiveX script(this script
will not parse but I just want to show the variables that
I am using)

--LOG
Step 'DTSStep_DTSActiveScriptTask_7' failed

Step Error Source: Microsoft Data Transformation Services
(DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft OLE DB Provider for SQL Server
Error Description: Timeout expired

Error on Line 47
(Microsoft OLE DB Provider for SQL Server (80040e31):
Timeout expired)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500

Step Execution Started: 2/2/2004 2:05:51 AM
Step Execution Completed: 2/2/2004 2:08:55 AM
Total Step Execution Time: 184.391 seconds
Progress count in Step: 0

--ActiveX script that got time out

Function Main()

'Getting the Prs Key from the Global Variables
dim prsKeyRS
dim updateSQL
dim prsCount
dim count

Dim DTSServer
DTSServer=DTSGlobalVariables("DTSServer").value
set prsKeyRS = CreateObject("ADODB.Recordset")
set prsKeyRS = DTSGlobalVariables("BP_KEY").value

'Creating the Connection to the sap table for deleting
the duplicate records

Dim dwConnection
set dwConnection = CreateObject("ADODB.Connection")

dwConnection.provider="sqloledb"
dwConnection.open DTSServer, "smithj", "mypass" '
Create runtime variable
dwConnection.DefaultDatabase = "mydb"

'Looping through all the duplicate records and deleting
the the records from sales tables.

for i=1 to prsKeyRS.RecordCount

pr_num=prsKeyRS.Fields("pr_num")
role=prskeyRS.Fields("role")
prs_key=prsKeyRS.Fields("prs_key")

If role="sales_manager" then
updateSQL= "update dbo.table1 set col1_key=" & prs_key
& " where pr_num='" & prNum &"'"
Else
If role="accountant" Then
updateSQL= "update dbo.table2 set col1=" & prs_key
& " where pr_num='" & prNum &"'"
Else
If role="finance_manager" Then
updateSQL= "update dbo.table3 set col1=" & prs_key & "
where pr_num='" & prNum &"'"
End If
End If
End If

dwConnection.execute(updateSQL)

personKeyRS.MoveNext

Next
Main = DTSTaskExecResult_Success
End Function
 
 
 

DTS execution error

Post by Lee Gilli » Wed, 04 Feb 2004 05:10:51

livia -

I think DTS timeouts only applies to CreateProcess tasks.

Yours is more of an ADO issue. Evidence is that the error source is an OLE DB provider. There are a couple of kinds of ADO
timeouts, connection, and command. I note yours timed out after a little more than 180 seconds. I would tend to suspect a command
timeout over a connection timeout.

Commands can take varying amounts of time, depending upon load and contention. I'll bet the times it DID work the total elapsed was
a little under 18# seconds?

Anyway, you probably already realize you can set an ADO command timeout after opening your connection, but before opening your
command / recordset such as:

dwConnection.CommandTimeout = (some # greater than 180)

HTH - Best regards, Lee Gillie - Spokane WA

"Olivia" < XXXX@XXXXX.COM > wrote in message news:8cf601c3e9c2$843f6420$ XXXX@XXXXX.COM ...


 
 
 

DTS execution error

Post by Olivi » Wed, 04 Feb 2004 11:08:09

ee,

Thank you. What you said make sense. In my script (see
attached) I don't use the Command object I use
dwConnection.execute(updateSQL) so in this case where do I
place the commandtimeout property?
Also, I'm not familiar with Connection.CommandTimeout=# I
only know Connection.ConnectionTimeout=#. I didn't find on
Books OnLine either.
I guess what I want to say is: "I need more help":)


source is an OLE DB provider. There are a couple of kinds
of ADO
after a little more than 180 seconds. I would tend to
suspect a command
load and contention. I'll bet the times it DID work the
total elapsed was
command timeout after opening your connection, but before
opening your
message news:8cf601c3e9c2$843f6420$ XXXX@XXXXX.COM ...
Thank
that
Services
deleting
deleting
prs_key
& "
 
 
 

DTS execution error

Post by Lee Gilli » Thu, 05 Feb 2004 00:59:24

livia -

dwConnection.Execute creates and uses a command implicitly. Simply set the CommandTimeout property on your dwConnection object
prior to using the Execute method. BOL is a SQL SERVER reference. This timeout property is in the MDAC / ADO realm.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocommandtimeout.asp

HTH - Lee

"Olivia" < XXXX@XXXXX.COM > wrote in message news:92de01c3e9fa$920fa320$ XXXX@XXXXX.COM ...


 
 
 

DTS execution error

Post by SmFzb2 » Sat, 17 May 2008 03:25:02

I'm running SQL 2005 sp2 on a Windows 2003 active/passive cluster.

We have some legacy DTS packages that are being called directly from a
scheduled SQL job. They have been running fine for a few weeks, until this
morning the jobs started failing, and I can't seem to figure out why.

If I run the DTS package manually, it runs without error, even when I'm
logged into the server using the account that both SQL and the SQL Agent are
running under.

Does anyone have any ideas on where I might start to debug this?

Here is the error I'm getting:
DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_1 DTSRun OnStart:
DTSStep_DTSActiveScriptTask_3 DTSRun OnError:
DTSStep_DTSActiveScriptTask_3, Error = -2147220482 (800403FE) Error
string: Error Code: 0 Error Source= Microsoft VBScript runtime error
Error Description: File not found



This error is a little misleading. The file it is looking for (File Not
found error), is a log file that is created earlier in the VBscript. The
file is being created by running a .bat file, which as best as I can tell is
not being executed, and is not logging any errors.

Does anyone have any ideas on what to check? It seems funning that the
package would run fine manually, but then fails when run from a job.
 
 
 

DTS execution error

Post by changli » Sat, 17 May 2008 15:49:56

Hi Jason,
I understand that your SQL job failed accidently with the error "File not
found" while it had been working fine for a few days.
If I have misunderstood, please let me know.

For further research, I recommend that you mail me
(changliw_at_microsoft_dot_com) the following information:
1. SQL error logs which are located in the folder %ProgramFiles%\Microsoft
SQL Server\MSSQL(.x)\MSSQL\LOG by default.
2. Trace file from Process Monitor.
You can use Process Monitor to monitor SQLAGENT90.EXE and DTEXEC.EXE.

You can download Process Monitor at this link:
Process Monitor v1.33
http://www.yqcomputer.com/
bdd849b5aab10f7263dd7f5904f2

Please feel free to let me know if you have any other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: XXXX@XXXXX.COM .
===========================================================
Get notification to my posts through email? Please refer to
http://www.yqcomputer.com/ #notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://www.yqcomputer.com/
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
 
 

DTS execution error

Post by changli » Wed, 21 May 2008 19:26:25

Hi,
Just a kind reminder, I have not received your response. Please feel free
to post back at your convenience if you need further assistance.

Have a great day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: XXXX@XXXXX.COM .
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================