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 4 6099
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.co m> wrote in message
news:d9******** *************** *********@4ax.c om... 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
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**@npspample ase.kqrs.com> wrote in message
news:uZ******** ******@TK2MSFTN GP10.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.co m> wrote in message news:d9******** *************** *********@4ax.c om... 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,
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:StmtStartin g EXEC dbo.ByRoyalty @P1
--
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**@npspample ase.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.co m> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 database...
How could i change the DTS package ownership?
|
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 saying
that the recordset object I was using could not be used when closed.
Well, it didn't make a whole lot of sense to me as to why that was
happening, and it doesn't realte to my question except to give you a
sense of what I'm trying to do. ...
|
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 click on the Tables icon.
Here's my DTS statement:
exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);
omsd is the linked server
|
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 API that allows for transparent tracing of SQL
procedures as well as explicit tracing of non-SQL procedures in DB2
Universal Database (UDB) V8.2 for LUW. An example SQL interface to this
API is provided, as well.
|
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 i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an...
| |
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.
my question is why we need a package.procedure as we can get the result by calling the procedure directly if we already created that procedure?
|
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 range values..
My question is:
Is it possible to assign Ranges to Parameters? Bydefault Ranges should be NULL. I have to enter Range values from webpage.
This is my stroed Procedure which i used without Range values:
I need Range values for...
|
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 procedure in oracle database.
stored procedure package has 8 i/p fields and 1 output feilds.
when i insert fields i need to get the o/p value from it and update it into ms acess DB table.
My doubt is how to get data from Acess and enter into stored...
|
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 value=???
how do i give the access fields values to oracle parameters and pass it
sqlComm.Parameters.Add(New OracleParameter("name", OracleType.VarChar)).Value = value
sqlComm.Parameters.Item("name").Direction =...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |