473,237 Members | 1,233 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Tracing values in DTS package/stored procedure

Hello,

I'm currently working on debugging a very large DTS package that was
created by someone else for the purpose of importing data into my
company's database. The data is mainly user/contact-related data for
our customer base.

We ran into problems when one import, of about 40,000 rows, took
upwards of six hours to complete. Many of the stored procedures used
by this package were written using XML. I've re-written many of them
using native SQL to see if that improves the performance, but I'm
getting some errors that I haven't been able to diagnose.

Instead of asking about my specific errors, I'd like to know more
generally what ways are there to debug DTS packages and stored
procedures? I'm aware of, and experienced with SQL Profiler but it's
not giving me the info I need. I need the ability to see exactly what
values are being passed to every call to a stored procedure from
within the DTS package or another stored procedure.

I've used it very successfully to debug .asp, .aspx, .vb and the like,
but right now I'm running it while running this huge stored procedure
that is called by the DTS package and does the lion's share of the
work, including multiple updates and inserts into about 10 tables.

The problem is, I see the calls to the "sub-procedures" from the main
one, but I can't see the values of any of the input or output
parameters. Instead of

Insert_Contact 'John', 'Q', 'Smith', '333-333-3333'......etc.

I see

Insert_Contact @FirstName, @Initial, @LastName, @PhoneNumber......etc.
My trace includes Stored Procedure events:
RPC: Completed
RPC: Starting
SP: Starting
SP: StmtCompleted
SP: StmtStarting

and TSQL:
Exec Prepared SQL
Prepare SQL
SQL: BatchCompleted
SQL: StmtStarting
I figured with these I would've covered the bases but I don't see any
of the parameters, which is critical for my debugging, as some of them
are not being properly set.
Any ideas or help would be greatly appreciated!

TIA,
Mike
Jul 20 '05 #1
4 6081
Hi, Mike

If you have Visual Studio 6 Enterprise, you might try exporting the
package(s) to VB format, then using the T-SQL Debugger within VB to peek
into the stored procs that it's running.

- Rick

"Mike" <ga_harley_guy@REMOVE_yahoo.com> wrote in message
news:d9********************************@4ax.com...
Hello,

I'm currently working on debugging a very large DTS package that was
created by someone else for the purpose of importing data into my
company's database. The data is mainly user/contact-related data for
our customer base.

Instead of asking about my specific errors, I'd like to know more
generally what ways are there to debug DTS packages and stored
procedures? I'm aware of, and experienced with SQL Profiler but it's
not giving me the info I need. I need the ability to see exactly what
values are being passed to every call to a stored procedure from
within the DTS package or another stored procedure.

Any ideas or help would be greatly appreciated!

TIA,
Mike

Jul 20 '05 #2
What I did was this

In a DTS package I had an ExecuteSQL task which dod

Exec dbo.ByRoyalty 10

a trace showed me for the sp:Completed

Exec dbo.ByRoyalty 10

Now I changed the 10 to be a ? and mapped a GV to it

Now in the trace I can see (passing in a 2 to the GV)

RPC:Starting
exec sp_executesql N'EXEC dbo.ByRoyalty @P1', N'@P1 int', 2

SP:StmtStarting
EXEC dbo.ByRoyalty @P1

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"RickT" <ri**@npspamplease.kqrs.com> wrote in message
news:uZ**************@TK2MSFTNGP10.phx.gbl...
Hi, Mike

If you have Visual Studio 6 Enterprise, you might try exporting the
package(s) to VB format, then using the T-SQL Debugger within VB to peek
into the stored procs that it's running.

- Rick

"Mike" <ga_harley_guy@REMOVE_yahoo.com> wrote in message
news:d9********************************@4ax.com...
Hello,

I'm currently working on debugging a very large DTS package that was
created by someone else for the purpose of importing data into my
company's database. The data is mainly user/contact-related data for
our customer base.

Instead of asking about my specific errors, I'd like to know more
generally what ways are there to debug DTS packages and stored
procedures? I'm aware of, and experienced with SQL Profiler but it's
not giving me the info I need. I need the ability to see exactly what
values are being passed to every call to a stored procedure from
within the DTS package or another stored procedure.

Any ideas or help would be greatly appreciated!

TIA,
Mike


Jul 20 '05 #3
Allan,

I've actually got something very similar for the initial call to the
package, there are about 3 GV's mapped to it. However, my problem is
once I get *inside* the ExecuteSQL task in this big stored proc I've
got values that are being set through lookups, output parms, etc.
That's my real problem, and I'm not quite sure how to handle them
using your suggestion.

Thanks,
Mike


On Fri, 3 Oct 2003 07:22:11 +0100, "Allan Mitchell"
<al***@no-spam.sqldts.com> wrote:
What I did was this

In a DTS package I had an ExecuteSQL task which dod

Exec dbo.ByRoyalty 10

a trace showed me for the sp:Completed

Exec dbo.ByRoyalty 10

Now I changed the 10 to be a ? and mapped a GV to it

Now in the trace I can see (passing in a 2 to the GV)

RPC:Starting
exec sp_executesql N'EXEC dbo.ByRoyalty @P1', N'@P1 int', 2

SP:StmtStarting
EXEC dbo.ByRoyalty @P1

--


Jul 20 '05 #4
Rick,

That's a good idea, and one I hadn't thought of. I actually do have
VS 6 Ent. and VS.NET Ent. I don't think I've had much luck working
with the T-SQL debugger in the past, though. I seem to recall it was
very difficult to set up. I'm not sure I ever got it working, really.
Thanks,
Mike


On Thu, 2 Oct 2003 22:15:34 -0500, "RickT"
<ri**@npspamplease.kqrs.com> wrote:
Hi, Mike

If you have Visual Studio 6 Enterprise, you might try exporting the
package(s) to VB format, then using the T-SQL Debugger within VB to peek
into the stored procs that it's running.

- Rick

"Mike" <ga_harley_guy@REMOVE_yahoo.com> wrote in message
news:d9********************************@4ax.com.. .
Hello,

I'm currently working on debugging a very large DTS package that was
created by someone else for the purpose of importing data into my
company's database. The data is mainly user/contact-related data for
our customer base.

Instead of asking about my specific errors, I'd like to know more
generally what ways are there to debug DTS packages and stored
procedures? I'm aware of, and experienced with SQL Profiler but it's
not giving me the info I need. I need the ability to see exactly what
values are being passed to every call to a stored procedure from
within the DTS package or another stored procedure.

Any ideas or help would be greatly appreciated!

TIA,
Mike


Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Leo Kozhushnik | last post by:
Hello there, Im trying to change the owership of the DTS package but am a little confused. sp_changeobjectowner changes the ownership of table, view, or stored procedure in the current...
1
by: jennifer1970 | last post by:
I've got a DTS package that runs an active-x script. The script is simple - it runs a stored procedure and saves the results to a CSV file. I kept getting this error message when trying to run it...
1
by: robin via SQLMonster.com | last post by:
I've tried several different way to execute a oracle stored procedure from a DTS package but to no avail. I have a Linked Server setup which does bring back Oracle tables from the server when I...
3
by: Serge Rielau | last post by:
Hi SQL Procedure developers, As threatened in earlier posts the SQL PL Tracing facility is finally oot-and-aboot. SQL procedure tracing: Analyzing SQL procedure flow This article describes an...
1
by: Alex | last post by:
Hello, I have a stored procedure that processes an individual file from a directory and archives it in a subdirectory.Now, the problem is, when i execute it , it will only process one file. What...
2
AdusumalliGopikumar
by: AdusumalliGopikumar | last post by:
Procedure also stored in database and package also stored in a database, we can call the procedure to do the task , and same one using package.procedure name if it exists in that package. ...
5
by: smadala | last post by:
Hi All, I created a stored procedure with parameters in package, i used that stored procedure in crystal Reports XI..till now its working fine. Now i need to retrieve a report based on Parameter...
2
by: riyap | last post by:
hi can anyone help me to simplify my logic in writing code, i have ms acess database table.i want to get the data from there(using select command with where clause) and insert into a stored...
0
by: riyap | last post by:
i understood the oracle procedure but only question is after executing the access db query, how do i assign those values(each field values) to the parameters iam passing into oracle procedure i.e...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.