Tracing values in DTS package/stored procedure

[Mike]

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

[RickT]

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

[Allan Mitchell]

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

[Mike]

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

--

[Mike]

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