DTS Question

I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >

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 scheduled to run as Job Fails

Hi,

I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.

I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.

Any ideas anyone?

MariaIs sa the owner of the job ? If not make sa the owner

DTS in Sproc help....

I have been trying to schedule a DTS package that executes perfectly fine
when fired with Enterprise Manager. However, I need this package to run
during a run-time process inside my application. So, I have added the exec
params inside a StoredProc -- everything is working as expected. But, the
affected table is not getting populated with the data from the .xls file.
So - I am pretty sure that it's a connection/filepath/user issue. But I am
not sure how to handle this problem. The process goes like this:
1. Drop existing dbo.SR_Traps
2. Create dbo.SR_Traps
3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
4. Data Pump from excel table into dbo.SR_Traps table
Looking at the DTS Package Logs, it seems to fail on number 4. above. Here
is the actual error message contained within the log:
Step Error Source: Microsoft JET Database Engine
Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
2005.xls' is not a valid path. Make sure that the path name is spelled
correctly and that you are connected to the server on which the file
resides.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003044
Any input/suggestions are greatly appreciated.
j
Message posted via http://www.droptable.com
Can you send us the string you think is blowing up?
"j c via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:70ef433752634d96a99ea2a3916fa27a@.droptable.co m...
>I have been trying to schedule a DTS package that executes perfectly fine
> when fired with Enterprise Manager. However, I need this package to run
> during a run-time process inside my application. So, I have added the
> exec
> params inside a StoredProc -- everything is working as expected. But, the
> affected table is not getting populated with the data from the .xls file.
> So - I am pretty sure that it's a connection/filepath/user issue. But I
> am
> not sure how to handle this problem. The process goes like this:
> 1. Drop existing dbo.SR_Traps
> 2. Create dbo.SR_Traps
> 3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
> 4. Data Pump from excel table into dbo.SR_Traps table
> Looking at the DTS Package Logs, it seems to fail on number 4. above.
> Here
> is the actual error message contained within the log:
> Step Error Source: Microsoft JET Database Engine
> Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
> 2005.xls' is not a valid path. Make sure that the path name is spelled
> correctly and that you are connected to the server on which the file
> resides.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:5003044
>
> Any input/suggestions are greatly appreciated.
> j
> --
> Message posted via http://www.droptable.com
|||Ok - here is the StoredProc:
****
CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"SR_Trapping_Data"'
GO
****
Here is the VB code I am using to fire the SP (which it is firing! But,
the resulting table gets dropped then recreated, and the last step of data
pump is not populating the table. It's empty after the DTS runs).
VB Code:
****
Dim pConn As New ADODB.Connection
pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
"Initial Catalog=; User Id=; Password="
pConn.Open
Dim pDTSCommand As New ADODB.Command
pDTSCommand.ActiveConnection = pConn
pDTSCommand.CommandType = adCmdStoredProc
pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
pDTSCommand.Execute
*****
Thanks for your input!
James
Message posted via http://www.droptable.com
|||If you run the sp in query anylyzer, do you have the same outcome?
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:c7fcc5e00f7243d4a2774c44dc1c37d0@.droptable.co m...
> Ok - here is the StoredProc:
> ****
> CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "SR_Trapping_Data"'
> GO
> ****
> Here is the VB code I am using to fire the SP (which it is firing! But,
> the resulting table gets dropped then recreated, and the last step of data
> pump is not populating the table. It's empty after the DTS runs).
> VB Code:
> ****
> Dim pConn As New ADODB.Connection
> pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
> "Initial Catalog=; User Id=; Password="
> pConn.Open
> Dim pDTSCommand As New ADODB.Command
> pDTSCommand.ActiveConnection = pConn
> pDTSCommand.CommandType = adCmdStoredProc
> pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
> pDTSCommand.Execute
> *****
>
> Thanks for your input!
> James
> --
> Message posted via http://www.droptable.com
|||Hi Chris,
I ran the following in QueryAnalyzer...
****
EXEC master..xp_cmdshell 'DTSRun /S "MMSQL1" /U "mms" /P "mmspass" /N
"SR_Trapping_DataNoNulls"'
****
This is the actual string used in the StoredProc. The error in QA is:
" Error string: The specified DTS Package ('Name =
'SR_Trapping_DataNoNulls'; ID.VersionID = {[not specified]}.{[not
specified]}') does not exist. "
After looking in the Data Transformation Services/ Local Packages area, I
can see that it is there. If I "Execute" the DTS package from Enterprise
Manager, there is no problem.
James
Message posted via http://www.droptable.com

DTS in Sproc help....

I have been trying to schedule a DTS package that executes perfectly fine
when fired with Enterprise Manager. However, I need this package to run
during a run-time process inside my application. So, I have added the exec
params inside a StoredProc -- everything is working as expected. But, the
affected table is not getting populated with the data from the .xls file.
So - I am pretty sure that it's a connection/filepath/user issue. But I am
not sure how to handle this problem. The process goes like this:
1. Drop existing dbo.SR_Traps
2. Create dbo.SR_Traps
3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
4. Data Pump from excel table into dbo.SR_Traps table
Looking at the DTS Package Logs, it seems to fail on number 4. above. Here
is the actual error message contained within the log:
Step Error Source: Microsoft JET Database Engine
Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
2005.xls' is not a valid path. Make sure that the path name is spelled
correctly and that you are connected to the server on which the file
resides.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003044
Any input/suggestions are greatly appreciated.
j
Message posted via http://www.droptable.comCan you send us the string you think is blowing up?
"j c via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:70ef433752634d96a99ea2a3916fa27a@.SQ
droptable.com...
>I have been trying to schedule a DTS package that executes perfectly fine
> when fired with Enterprise Manager. However, I need this package to run
> during a run-time process inside my application. So, I have added the
> exec
> params inside a StoredProc -- everything is working as expected. But, the
> affected table is not getting populated with the data from the .xls file.
> So - I am pretty sure that it's a connection/filepath/user issue. But I
> am
> not sure how to handle this problem. The process goes like this:
> 1. Drop existing dbo.SR_Traps
> 2. Create dbo.SR_Traps
> 3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
> 4. Data Pump from excel table into dbo.SR_Traps table
> Looking at the DTS Package Logs, it seems to fail on number 4. above.
> Here
> is the actual error message contained within the log:
> Step Error Source: Microsoft JET Database Engine
> Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
> 2005.xls' is not a valid path. Make sure that the path name is spelled
> correctly and that you are connected to the server on which the file
> resides.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:5003044
>
> Any input/suggestions are greatly appreciated.
> j
> --
> Message posted via http://www.droptable.com|||Ok - here is the StoredProc:
****
CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"SR_Trapping_Data"'
GO
****
Here is the VB code I am using to fire the SP (which it is firing! But,
the resulting table gets dropped then recreated, and the last step of data
pump is not populating the table. It's empty after the DTS runs).
VB Code:
****
Dim pConn As New ADODB.Connection
pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
"Initial Catalog=; User Id=; Password="
pConn.Open
Dim pDTSCommand As New ADODB.Command
pDTSCommand.ActiveConnection = pConn
pDTSCommand.CommandType = adCmdStoredProc
pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
pDTSCommand.Execute
*****
Thanks for your input!
James
Message posted via http://www.droptable.com|||If you run the sp in query anylyzer, do you have the same outcome?
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:c7fcc5e00f7243d4a2774c44dc1c37d0@.SQ
droptable.com...
> Ok - here is the StoredProc:
> ****
> CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "SR_Trapping_Data"'
> GO
> ****
> Here is the VB code I am using to fire the SP (which it is firing! But,
> the resulting table gets dropped then recreated, and the last step of data
> pump is not populating the table. It's empty after the DTS runs).
> VB Code:
> ****
> Dim pConn As New ADODB.Connection
> pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
> "Initial Catalog=; User Id=; Password="
> pConn.Open
> Dim pDTSCommand As New ADODB.Command
> pDTSCommand.ActiveConnection = pConn
> pDTSCommand.CommandType = adCmdStoredProc
> pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
> pDTSCommand.Execute
> *****
>
> Thanks for your input!
> James
> --
> Message posted via http://www.droptable.com|||Hi Chris,
I ran the following in QueryAnalyzer...
****
EXEC master..xp_cmdshell 'DTSRun /S "MMSQL1" /U "mms" /P "mmspass" /N
"SR_Trapping_DataNoNulls"'
****
This is the actual string used in the StoredProc. The error in QA is:
" Error string: The specified DTS Package ('Name =
'SR_Trapping_DataNoNulls'; ID.VersionID = {[not specified]}.{
[not
specified]}') does not exist. "
After looking in the Data Transformation Services/ Local Packages area, I
can see that it is there. If I "Execute" the DTS package from Enterprise
Manager, there is no problem.
James
Message posted via http://www.droptable.com

DTS in Sproc help....

I have been trying to schedule a DTS package that executes perfectly fine
when fired with Enterprise Manager. However, I need this package to run
during a run-time process inside my application. So, I have added the exec
params inside a StoredProc -- everything is working as expected. But, the
affected table is not getting populated with the data from the .xls file.
So - I am pretty sure that it's a connection/filepath/user issue. But I am
not sure how to handle this problem. The process goes like this:
1. Drop existing dbo.SR_Traps
2. Create dbo.SR_Traps
3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
4. Data Pump from excel table into dbo.SR_Traps table
Looking at the DTS Package Logs, it seems to fail on number 4. above. Here
is the actual error message contained within the log:
Step Error Source: Microsoft JET Database Engine
Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
2005.xls' is not a valid path. Make sure that the path name is spelled
correctly and that you are connected to the server on which the file
resides.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003044
Any input/suggestions are greatly appreciated.
j
--
Message posted via http://www.sqlmonster.comCan you send us the string you think is blowing up?
"j c via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:70ef433752634d96a99ea2a3916fa27a@.SQLMonster.com...
>I have been trying to schedule a DTS package that executes perfectly fine
> when fired with Enterprise Manager. However, I need this package to run
> during a run-time process inside my application. So, I have added the
> exec
> params inside a StoredProc -- everything is working as expected. But, the
> affected table is not getting populated with the data from the .xls file.
> So - I am pretty sure that it's a connection/filepath/user issue. But I
> am
> not sure how to handle this problem. The process goes like this:
> 1. Drop existing dbo.SR_Traps
> 2. Create dbo.SR_Traps
> 3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
> 4. Data Pump from excel table into dbo.SR_Traps table
> Looking at the DTS Package Logs, it seems to fail on number 4. above.
> Here
> is the actual error message contained within the log:
> Step Error Source: Microsoft JET Database Engine
> Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
> 2005.xls' is not a valid path. Make sure that the path name is spelled
> correctly and that you are connected to the server on which the file
> resides.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:5003044
>
> Any input/suggestions are greatly appreciated.
> j
> --
> Message posted via http://www.sqlmonster.com|||Ok - here is the StoredProc:
****
CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"SR_Trapping_Data"'
GO
****
Here is the VB code I am using to fire the SP (which it is firing! But,
the resulting table gets dropped then recreated, and the last step of data
pump is not populating the table. It's empty after the DTS runs).
VB Code:
****
Dim pConn As New ADODB.Connection
pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
"Initial Catalog=; User Id=; Password="
pConn.Open
Dim pDTSCommand As New ADODB.Command
pDTSCommand.ActiveConnection = pConn
pDTSCommand.CommandType = adCmdStoredProc
pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
pDTSCommand.Execute
*****
Thanks for your input!
James
--
Message posted via http://www.sqlmonster.com|||If you run the sp in query anylyzer, do you have the same outcome?
"j c via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7fcc5e00f7243d4a2774c44dc1c37d0@.SQLMonster.com...
> Ok - here is the StoredProc:
> ****
> CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "SR_Trapping_Data"'
> GO
> ****
> Here is the VB code I am using to fire the SP (which it is firing! But,
> the resulting table gets dropped then recreated, and the last step of data
> pump is not populating the table. It's empty after the DTS runs).
> VB Code:
> ****
> Dim pConn As New ADODB.Connection
> pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
> "Initial Catalog=; User Id=; Password="
> pConn.Open
> Dim pDTSCommand As New ADODB.Command
> pDTSCommand.ActiveConnection = pConn
> pDTSCommand.CommandType = adCmdStoredProc
> pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
> pDTSCommand.Execute
> *****
>
> Thanks for your input!
> James
> --
> Message posted via http://www.sqlmonster.com|||Hi Chris,
I ran the following in QueryAnalyzer...
****
EXEC master..xp_cmdshell 'DTSRun /S "MMSQL1" /U "mms" /P "mmspass" /N
"SR_Trapping_DataNoNulls"'
****
This is the actual string used in the StoredProc. The error in QA is:
" Error string: The specified DTS Package ('Name ='SR_Trapping_DataNoNulls'; ID.VersionID = {[not specified]}.{[not
specified]}') does not exist. "
After looking in the Data Transformation Services/ Local Packages area, I
can see that it is there. If I "Execute" the DTS package from Enterprise
Manager, there is no problem.
James
--
Message posted via http://www.sqlmonster.com