DTS Problem in stored proc

Hi all,
I'm having a problem I was hoping someone out there could shed some light on
. I have a stored procedure in SQL server 2000 that opens a cursor on a tab
le that contains a list of files on my heard drive. It then loops through t
he cursor, and first copies the file to a sub folder using xp_cmdshell. It
then moves the same file to a location and renames it to specific name that
a DTS package will import from (also done using xp_cmdshell). I am then exe
cuting the DTS package using DTSRun and xp_cmdshell. That is pretty straigh
t forward. Now to my problem. All this works for a random number of files,
and then it hangs. A few times it can process all files, but usually not.
To debug it, I figured I could create a log file for the DTS package (by us
ing the /L option), but by doing that, the problem disappeared. One thing I
have observed is that when it hangs, the CMD.EXE process is not terminating
. Does anyone have any explanation for this? I know I could just leave t
he logging, but I am curious why logging it also fixes it. FYI, the reason
I'm using a DTS package to load the text file instead of BULK INSERT is that
I have a rowterminator of CR and not CRLF, and I haven't found any document
ations stating that it even supports CR as the rowterminator. Also, I can't
stop the query analyzer after it hangs, so I have to stop the SQL server fo
r it to stop, and the error message that shows in the query analyzer then is
as follow.
Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
I assume that could have to do with the fact that I stopped the SQL server,
but maybe not, and it might be helpful to someone out there.
Any help would be much appreciated,
TKhi,
At the outset I would create a log for that DTS (Package
properties->Logging) and after that I would create another one (partial log)
inside the PUMP task (Transform Data Task Properties->Options->Exception Fil
e)
current location: alicante (es)
"Tor" wrote:

> Hi all,
> I'm having a problem I was hoping someone out there could shed some light on. I h
ave a stored procedure in SQL server 2000 that opens a cursor on a table that contai
ns a list of files on my heard drive. It then loops through the cursor, and first c
opi
es the file to a sub folder using xp_cmdshell. It then moves the same file
to a location and renames it to specific name that a DTS package will import
from (also done using xp_cmdshell). I am then executing the DTS package us
ing DTSRun and xp_cmdshell.
That is pretty straight forward. Now to my problem. All this works for a r
andom number of files, and then it hangs. A few times it can process all fi
les, but usually not. To debug it, I figured I could create a log file for
the DTS package (by using
the /L option), but by doing that, the problem disappeared. One thing I hav
e observed is that when it hangs, the CMD.EXE process is not terminating.
Does anyone have any explanation for this? I know I could just leave the
logging, but I am curious why logging it also fixes it. FYI, the reason I'm
using a DTS package to load the text file instead of BULK INSERT is that I
have a rowterminator of CR and not CRLF, and I haven't found any documentati
ons stating that it even su
pports CR as the rowterminator. Also, I can't stop the query analyzer after it hangs, so I
have to stop the SQL server for it to stop, and the error message that shows in the query
analyzer then is as follow.
> Query cancelled by User
> [Microsoft][ODBC SQL Server Driver]Operation canceled
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
> I assume that could have to do with the fact that I stopped the SQL server
, but maybe not, and it might be helpful to someone out there.
> Any help would be much appreciated,
> TK|||Thanks Enric. I tried all that, and I am getting the same result. When I
log the DTS package (not by the /L option but via the property of the
package), it works too. When I'm specifying only the exception file, it
does hang, but it states that all executions up to then were completed. I
don't even know if the problem is the actual DTS package, but if it wasn't,
why would logging it fix the problem? Could the calls to xp_cmdshell hang?
It is leaving a CMD.EXE running when it hangs after all, and I do know it is
one CMD.EXE and not several processes that come and go because of the PID.
I even tried to move the COPY and MOVE commands to a BAT file, and call the
BAT file from the stored proc, but no difference. Do you, or anyone else,
have any other ideas I can try?
Thanks,
TK
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:A42A33E7-C4C7-4BEC-B470-4077F404E9B8@.microsoft.com...
> hi,
> At the outset I would create a log for that DTS (Package
> properties->Logging) and after that I would create another one (partial
> log)
> inside the PUMP task (Transform Data Task Properties->Options->Exception
> File)
> --
> current location: alicante (es)
>
> "Tor" wrote:
>
> logging, but I am curious why logging it also fixes it. FYI, the reason
> I'm using a DTS package to load the text file instead of BULK INSERT is
> that I have a rowterminator of CR and not CRLF, and I haven't found any
> documentations stating that it even supports CR as the rowterminator.
> Also, I can't stop the query analyzer after it hangs, so I have to stop
> the SQL server for it to stop, and the error message that shows in the
> query analyzer then is as follow.sqlsql

DTS Parametirized ExecuteSQlTask

Hi,

I have a DTS whit several SQl tasks that executes a stored procedure. The result of this execution is stored in an output parameter as global variable.

The problem is if i manually launch the DTS package, it works with no problems but after i schedule the job, i received an error in the SQl tasks i said before. What can i do to fix it?

The Error is:
-----
Executed as user: SCCCOL1\sqlservices. ...t: DTSStep_DTSActiveScriptTask_4 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_26 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_26 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnError: DTSStep_DTSExecuteSQLTask_4, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Er... Process Exit Code 1. The step failed.When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?|||Originally posted by rnealejr
When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?

Hi,

Thanks for ur answer. The owner of my job is an windows authenticated user how has administrator permissions over the server, but user used to run sqlserver agent is default user: SqlServices. However in the properties, i've configured the connections like windows authentication ( i supuse that it's using my administrative account to run jobs).

In a previous post, i found that i have to doing bigger the login time-out for SQL Agent. I did it and i rebuild the package into one new with a connection with clear specifications to the server. i mean that before i've reference to [local] server and after i changed it to [NAMESERVER] SQL on my network. I scheduled this package and it works.

is it a bug of SQl Server? why Agent SQl works with a form and not with another?

Thanks,
Maritzita

DTS package with unexpected issues

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. After spending an inordinate amount of
time on that... I decided to just create a SQL Server connection
object and an Excel Object and then use a transformation to load the
query results. Simple enough, or so I thought. So in the
transformation object under the Source tab, I typed in the query to run
the Stored Procedure:

Declare @.S nvarchar(30)
Declare @.E nvarchar(30)
SET @.S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'
SET @.E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'
exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E

And then I clicked on the preview button. I got the message that no
rowset was returned. In a way, that explains the issue with the
Active-X script. BUT, I know darn well it returns data. It returns
438 rows of data when I run this in Query Analyser.

So, here's my question...how could that be? Is there some issue that
DTS packages have with temporary tables? I do use a couple in the
Stored Procedure. Without having to post the stored procedure and
tables, etc. could someone let me know if they've run into something
like this before?

Thanks,
Jenniferjennifer1970@.hotmail.com wrote:

> Declare @.S nvarchar(30)
> Declare @.E nvarchar(30)
> SET @.S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'
> SET @.E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'
> exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E

I've never used a stored procedure in DTS, but i presume that if any
error occurs in SP, DTS won't catch it.
Try taking source from SP and execute it DTS.
Change:
exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E
with procedure code

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:
> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> > Hi,
> >
> > Please let me know if there is any system stored procedure which can be
> > used
> > to delete the older versions of dts packages in SQL Server 2000 as in my
> > environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> > eat
> > up the disk space.
> >
> > Any other user SP(if no system SP is thr) if provided will also do.
> >
> > Thanks
> > Manu Jaidka
>
>

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and ea
t
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it free
d
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely becaus
e
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>sqlsql

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu Jaidka
Hello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka
|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>

DTS Package Problem

I have a stored procedure that calls a DTS package using xp_cmdshell dtsrun. The problem is strange, I can execute the stored procedure from Query Analyzer and it works fine. I call the stored procedure from inside of an ASP.NET application and it seems to execute but doesn't. The user account for the ASP.NET application has rights to execute the stored procedure and I have run a trace and so see any login failures. I'm stumped, any clues??

neel

xp_cmdshell can run an enterprise but it is a SQL Server Agent dependent service, you have to install SQL Server Agent with a service account to run all the dependent services. Hope this helps|||

Does the SQL Server agent have to be on the client computer or just the computer with SQL Server on it. I would think since I'm just sending the stored procedure name of the stored procedure on SQL Server that that server would be the only place I need the SQL Server Agent?

Thanks

Neel

|||The SQL Server Agent in the computer executing xp_cmdshell needs a service account because you can schedule xp_cmdshell with a job to transfer data from main frame to another SQL Server in the same network and it will run it. SQL Server Agent needs a service account if your application will use any of its dependent services like DTS Automation, Replication and jobs. Microsoft keep this from developers because there is a lot to know in development. Hope this help|||

Thanks it is working now. I also had to go in and setup a proxy account in the SQL Server Agent that had sysadmin rights because the users of the program and the program user account do not have sysadmin rights.

Thanks Again

Neel

DTS Package execution

Is there a method to either:
A) Kick of a DTS package directly from a stored procedure without shelling out to the DTSRun facility.
B) Kick off a scheduled job containing a DTS task from a stored procedure?
What's the best method?Choose option as stated in this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to schedule DTS as a job.|||Thanks Satya. Good info.

DTS package Error

I'm almost new to this one:
I run a store procedure that extracts data from one table.
When manually run, the results returned is what is
expected. But when I try to create a dts package to
export the data to a text file, I get this error:
Error Description: Null accessors are not supported by
this provider.
Can someone help me with this? Thanks.
Mary.I fixed my own problem. I just recreated the package from
scratch and it worked. I don't know why I had that error
in the first place - so if anyone knows, please share your
response with me. Thanks.
Mary
>--Original Message--
>I'm almost new to this one:
>I run a store procedure that extracts data from one table.
>When manually run, the results returned is what is
>expected. But when I try to create a dts package to
>export the data to a text file, I get this error:
>Error Description: Null accessors are not supported by
>this provider.
>Can someone help me with this? Thanks.
>Mary.
>.
>|||Its possible that the MDAC version of the EM used was either higher than
what is currently on the server.
--
Olu Adedeji
"Mary Moore" <anonymous@.discussions.microsoft.com> wrote in message
news:6f8a01c40215$fbb3f460$a501280a@.phx.gbl...
> I fixed my own problem. I just recreated the package from
> scratch and it worked. I don't know why I had that error
> in the first place - so if anyone knows, please share your
> response with me. Thanks.
> Mary
> >--Original Message--
> >I'm almost new to this one:
> >
> >I run a store procedure that extracts data from one table.
> >When manually run, the results returned is what is
> >expected. But when I try to create a dts package to
> >export the data to a text file, I get this error:
> >
> >Error Description: Null accessors are not supported by
> >this provider.
> >
> >Can someone help me with this? Thanks.
> >
> >Mary.
> >.
> >

dts package - stored procedure

I created a dts package and I can execute it.

I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.

I have sql integration services and mssql 2005 services running under a domain account.

I have saved the package as a FILE System stored package.

I just can't find a reason why it won't execute from stored procedure.....

Ron

Do you get any errors?

Thanks

|||

Hi,

An alternative would be to use a JOB to fire the package from a stored procedure or a web page.

Something like the example bellow:

Regards,

Philippe

ALTER PROCEDURE [Users].[up_Backlog2Days]

@.Day1 varchar(15)

, @.Day2 varchar(15)

as

begin

-- call the procedure like that from within an excel pivot

-- Exec sm.users.up_Backlog2Days @.Day1 = '14-Dec-2006', @.Day2 = '15-Dec-2006'

set nocount on

Declare @.Cmd as Varchar(500)

Declare @.ReturnCode as int

set @.Cmd = '/DTS "\Deployed Packages\Backlog to compare 2 days" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\package.variables[Day1].Value";"' + @.Day1 + '" /SET "\package.variables[Day2].Value";"'+ @.Day2 + '"'

EXEC msdb.dbo.sp_update_jobstep @.job_name=N'Backlog 2 days compare', @.step_id=1 ,

@.command= @.Cmd

exec msdb.dbo.sp_start_job @.job_name = N'Backlog 2 days compare'

While (SELECT Count(Status) AS Status

FROM OnGlobals.dbo.tb_Isready

WHERE (Name = 'Backlog_2_Days') and Status = 'Ready') != 1

begin

WaitFor Delay '00:01:00'

-- nothing

end

Select * from staging.dbo.tb_backlog_2_Days

End

|||

no, no errors

none that I can find

no errors in the event viewer

no errors in the sql log

it's like it just passes over the part of the procedure with the dts package

Ron

|||Can you post the code you're using to execute the DTS? Does the SQL Account running the SP have permission to remotely/locally run the package?

DTS Package

Hi,
I want to write a stored procedure that queries the DTS packages
currently running and sends email to user if anyone of them fails. I
want to know how we can know status of any DTS from stored procedure.
Thanks,
NirajHow do you launch the DTS Package? Manual or by SQL Server Agent?
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129807126.770730.251640@.f14g2000cwb.googlegroups.com...
> Hi,
> I want to write a stored procedure that queries the DTS packages
> currently running and sends email to user if anyone of them fails. I
> want to know how we can know status of any DTS from stored procedure.
> Thanks,
> Niraj
>|||I launch the dts package by sql server agent|||Ok, write a two additional steps (one for a failrure and second for a
success) which will send you email if the step was complete or it wasn't
Make a step as on success action got to the step (success)
and on failure action go to step (failure)
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129813759.640710.278460@.o13g2000cwo.googlegroups.com...
>I launch the dts package by sql server agent
>

DTS Package

Hi,
I want to write a stored procedure that queries the DTS packages
currently running and sends email to user if anyone of them fails. I
want to know how we can know status of any DTS from stored procedure.
Thanks,
Niraj
How do you launch the DTS Package? Manual or by SQL Server Agent?
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129807126.770730.251640@.f14g2000cwb.googlegr oups.com...
> Hi,
> I want to write a stored procedure that queries the DTS packages
> currently running and sends email to user if anyone of them fails. I
> want to know how we can know status of any DTS from stored procedure.
> Thanks,
> Niraj
>
|||I launch the dts package by sql server agent
|||Ok, write a two additional steps (one for a failrure and second for a
success) which will send you email if the step was complete or it wasn't
Make a step as on success action got to the step (success)
and on failure action go to step (failure)
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129813759.640710.278460@.o13g2000cwo.googlegr oups.com...
>I launch the dts package by sql server agent
>

DTS Package

Hi,
I want to write a stored procedure that queries the DTS packages
currently running and sends email to user if anyone of them fails. I
want to know how we can know status of any DTS from stored procedure.
Thanks,
NirajHow do you launch the DTS Package? Manual or by SQL Server Agent?
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129807126.770730.251640@.f14g2000cwb.googlegroups.com...
> Hi,
> I want to write a stored procedure that queries the DTS packages
> currently running and sends email to user if anyone of them fails. I
> want to know how we can know status of any DTS from stored procedure.
> Thanks,
> Niraj
>|||I launch the dts package by sql server agent|||Ok, write a two additional steps (one for a failrure and second for a
success) which will send you email if the step was complete or it wasn't
Make a step as on success action got to the step (success)
and on failure action go to step (failure)
"nirajshah" <niraj.shah@.travelex.com> wrote in message
news:1129813759.640710.278460@.o13g2000cwo.googlegroups.com...
>I launch the dts package by sql server agent
>

DTS or stored procedure

I need to copy some rows from a table in one server to a table in another
server. The amount of data is not very large and nothing fancy happens to
the data on the way.
Does anyone have any advice on the reletive merits of DTS as opposed to just
doing it in a stored procedure?
regards
SpikeHi
In your situation there is probably very little difference. You are not
really using any of the more specialised features of dts!
If you don't have any other DTS packages then there would be no real reason
to create one just for this.
John
"Spike" wrote:

> I need to copy some rows from a table in one server to a table in another
> server. The amount of data is not very large and nothing fancy happens to
> the data on the way.
> Does anyone have any advice on the reletive merits of DTS as opposed to ju
st
> doing it in a stored procedure?
> regards
> Spike
>|||Spike,
One should use DTS only if it is entirely necessary. For all other situation
one shd code the same thing using a sproc. Specifically in your case where
nothing fancing is happening to your data on the way you are better off usin
g
a sproc.
There can be hundreds of difference between DTS and sproc.
just to start with: sproc can be faster than DTS (when properly coded).
although it can be a complex codingbut then hey thats why we get good money
for doing it isnt it ?
sproc are in your db itself and are backed up by your regular db backup.
nothing special needs to be done.
I believe for DTS if you back up your msDB then its okay. additionally DTS
can also be saved as a file. (but still its an extra effort)
DTS is nice to look at and fancy to work wiht coz you can use your vb script
skills and stuff.
Abhishek Pandey
"Spike" wrote:

> I need to copy some rows from a table in one server to a table in another
> server. The amount of data is not very large and nothing fancy happens to
> the data on the way.
> Does anyone have any advice on the reletive merits of DTS as opposed to ju
st
> doing it in a stored procedure?
> regards
> Spike
>

DTS is crazy! or maybe its me?

Hey all,
I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.
Am I missing something here?
Here is the SPROC:
CREATE PROCEDURE [dbo].[importEmployees] AS

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importEmployees.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

GO

Ok, so it was me... I had the datatype wrong on the excel file which was set to numeric and I put in text.

DTS in a stored procedure

Hi,

I am kind of a beginer to sql so any input is appreciated. I am trying to create a store procedure that will execute a DTS package, which will use as a parameter the DTS's input source file (always an excel file). Can anyone help me pls. Thank you for your time.

Sincerely,

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)

DTS import not working

Hello, I have created a dts structured storage file that I want to execute with a stored procedure. The dts runs fine while in enterprise manager, but when I call the file from the stored procedure below I get this error: error LoadFromStorageFile
anybody have some ideas on what's going on?
Here is the SPROC:
DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importData.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END
GO

I'm not sure why it is failing but why not make it a scheduled job in Enterprise Manager?