Using dtsrun in stored procedure

1 May 2006 7:23 PM

tyrus

I made this sp

CREATE PROCEDURE [dbo].[spTableLoad]
AS
Declare @Command varchar(1000)
SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
"mypasswrd" ~/N "Import into TABLE1"'
exec master.dbo.xp_cmdshell @Command
GO

but when I run it I get
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: DTSRun: Must specify a package name or guid or version
guid, or a source filename.

I don't understand what part I'm missing. The Package is on the local server
and named correctly in the ~/N option

1 May 2006 10:52 PM

Sue Hoegemeier

Is it stored in a repository database named GPIntegration
(storage location of Meta Data Services). I'm guessing it's
not - you are probably just storing it to SQL Server. In
that case, you can start by removing the argument for the
repository. You can also remove the tildes prior to the
parameters. The tildes are used when the parameters are in
hexadecimal.

-Sue

On Mon, 1 May 2006 12:23:01 -0700, tyrus
<ty***@discussions.microsoft.com> wrote:

Show quote

>I made this sp
>
>CREATE PROCEDURE [dbo].[spTableLoad]
> AS
>Declare @Command varchar(1000)
>SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
>"mypasswrd" ~/N "Import into TABLE1"'
>exec master.dbo.xp_cmdshell @Command
>GO
>
>but when I run it I get
>Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: DTSRun: Must specify a package name or guid or version
>guid, or a source filename.
>
>I don't understand what part I'm missing. The Package is on the local server
>and named correctly in the ~/N option

2 May 2006 3:00 PM

tyrus

Yes. I am just storing it in SQL Server. I removed the tildes and the /R arg.
But still get the same error. Thanks for your help.

Error: -2147467259 (80004005); Provider Error: 0 (0)
   Error string: DTSRun: Must specify a package name or guid or version
guid, or a source filename.
   Error source: Microsoft Data Transformation Services (DTS) Package
   Help file: sqldts80.hlp
   Help context: 0

Show quote

"Sue Hoegemeier" wrote:

> Is it stored in a repository database named GPIntegration
> (storage location of Meta Data Services). I'm guessing it's
> not - you are probably just storing it to SQL Server. In
> that case, you can start by removing the argument for the
> repository. You can also remove the tildes prior to the
> parameters. The tildes are used when the parameters are in
> hexadecimal.
>
> -Sue
>
> On Mon, 1 May 2006 12:23:01 -0700, tyrus
> <ty***@discussions.microsoft.com> wrote:
>
> >I made this sp
> >
> >CREATE PROCEDURE [dbo].[spTableLoad]
> > AS
> >Declare @Command varchar(1000)
> >SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
> >"mypasswrd" ~/N "Import into TABLE1"'
> >exec master.dbo.xp_cmdshell @Command
> >GO
> >
> >but when I run it I get
> >Error: -2147467259 (80004005); Provider Error: 0 (0)
> > Error string: DTSRun: Must specify a package name or guid or version
> >guid, or a source filename.
> >
> >I don't understand what part I'm missing. The Package is on the local server
> >and named correctly in the ~/N option
>
>

2 May 2006 3:04 PM

tyrus

Actually I was wrong that got it to run but I get

DTSRun: Loading...

Error: -2147467259 (80004005); Provider Error: 17 (11)
   Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or access denied.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context: 0

NULL

It doesn't need to return anything. Is it the "local" that's causing the
problem maybe?

Show quote

"Sue Hoegemeier" wrote:

> Is it stored in a repository database named GPIntegration
> (storage location of Meta Data Services). I'm guessing it's
> not - you are probably just storing it to SQL Server. In
> that case, you can start by removing the argument for the
> repository. You can also remove the tildes prior to the
> parameters. The tildes are used when the parameters are in
> hexadecimal.
>
> -Sue
>
> On Mon, 1 May 2006 12:23:01 -0700, tyrus
> <ty***@discussions.microsoft.com> wrote:
>
> >I made this sp
> >
> >CREATE PROCEDURE [dbo].[spTableLoad]
> > AS
> >Declare @Command varchar(1000)
> >SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
> >"mypasswrd" ~/N "Import into TABLE1"'
> >exec master.dbo.xp_cmdshell @Command
> >GO
> >
> >but when I run it I get
> >Error: -2147467259 (80004005); Provider Error: 0 (0)
> > Error string: DTSRun: Must specify a package name or guid or version
> >guid, or a source filename.
> >
> >I don't understand what part I'm missing. The Package is on the local server
> >and named correctly in the ~/N option
>
>

2 May 2006 3:08 PM

tyrus

I'm sorry. I should be more diligent. "local" was the problem. THANK YOU

Show quote

"Sue Hoegemeier" wrote:

> Is it stored in a repository database named GPIntegration
> (storage location of Meta Data Services). I'm guessing it's
> not - you are probably just storing it to SQL Server. In
> that case, you can start by removing the argument for the
> repository. You can also remove the tildes prior to the
> parameters. The tildes are used when the parameters are in
> hexadecimal.
>
> -Sue
>
> On Mon, 1 May 2006 12:23:01 -0700, tyrus
> <ty***@discussions.microsoft.com> wrote:
>
> >I made this sp
> >
> >CREATE PROCEDURE [dbo].[spTableLoad]
> > AS
> >Declare @Command varchar(1000)
> >SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
> >"mypasswrd" ~/N "Import into TABLE1"'
> >exec master.dbo.xp_cmdshell @Command
> >GO
> >
> >but when I run it I get
> >Error: -2147467259 (80004005); Provider Error: 0 (0)
> > Error string: DTSRun: Must specify a package name or guid or version
> >guid, or a source filename.
> >
> >I don't understand what part I'm missing. The Package is on the local server
> >and named correctly in the ~/N option
>
>

2 May 2006 4:18 PM

Sue Hoegemeier

No problem...thanks for posting back.

-Sue

On Tue, 2 May 2006 08:08:02 -0700, tyrus
<ty***@discussions.microsoft.com> wrote:

Show quote

>I'm sorry. I should be more diligent. "local" was the problem. THANK YOU
>
>"Sue Hoegemeier" wrote:
>
>> Is it stored in a repository database named GPIntegration
>> (storage location of Meta Data Services). I'm guessing it's
>> not - you are probably just storing it to SQL Server. In
>> that case, you can start by removing the argument for the
>> repository. You can also remove the tildes prior to the
>> parameters. The tildes are used when the parameters are in
>> hexadecimal.
>>
>> -Sue
>>
>> On Mon, 1 May 2006 12:23:01 -0700, tyrus
>> <ty***@discussions.microsoft.com> wrote:
>>
>> >I made this sp
>> >
>> >CREATE PROCEDURE [dbo].[spTableLoad]
>> > AS
>> >Declare @Command varchar(1000)
>> >SET @Command = 'DTSrun ~/S "local" ~/R "GPIntegration" ~/U "user" ~/P
>> >"mypasswrd" ~/N "Import into TABLE1"'
>> >exec master.dbo.xp_cmdshell @Command
>> >GO
>> >
>> >but when I run it I get
>> >Error: -2147467259 (80004005); Provider Error: 0 (0)
>> > Error string: DTSRun: Must specify a package name or guid or version
>> >guid, or a source filename.
>> >
>> >I don't understand what part I'm missing. The Package is on the local server
>> >and named correctly in the ~/N option
>>
>>