xp_cmdshell

[bads]

Hi there!
Is it possible to supply variable for the xp_cmdshell parameter?
here's an example:

declare @@okay char(20)
select @@okay = "dir c:mssqlinn"
exec master..xp_cmdshell @@okay

but this doesn't work. is there any workaround on this? instead
of writing a new extended sp to cater for this.

thanks in advance.

dion

[Mike Doyle]

On 11/2/98 1:42:05 PM, bads wrote:
> Hi there!
Is it possible to supply variable for the xp_cmdshell
> parameter?
here's an example:

declare @@okay char(20)
select
> @@okay = "dir c:mssqlinn"
exec master..xp_cmdshell
> @@okay

but this doesn't work. is there any workaround on this?
> instead
of writing a new extended sp to cater for this.

thanks in advance.

dion
--
dion, I think the problem is in your variable declaration, use

declare @okay char(20)
select @okay = "dir c:mssqlinn"
exec master..xp_cmdshell @okay

also look at the extended stored procedures for this type of function (dir), they're not documented so they won't necessarily be present in future releases but they come in handy.

cheers..
Mike

[dion]

On 11/2/98 5:07:39 PM, Mike Doyle wrote:
> On 11/2/98 1:42:05 PM, bads wrote:
> Hi there!
Is it possible to supply
> variable for the xp_cmdshell
> parameter?
here's an
> example:

declare @@okay char(20)
select
> @@okay = "dir
> c:mssqlinn"
exec master..xp_cmdshell
> @@okay

but this
> doesn't work. is there any workaround on this?
> instead
of writing
> a new extended sp to cater for this.

thanks in
> advance.

dion
--
dion, I think the problem is in your variable
> declaration, use

declare @okay char(20)
select @okay = "dir
> c:mssqlinn"
exec master..xp_cmdshell @okay

also look at the
> extended stored procedures for this type of function (dir), they're not
> documented so they won't necessarily be present in future releases but
> they come in handy.

cheers..
Mike

tried that one before and doesnt work. i tried all the possible kind
of declaration and still not working. it's just an example of one i did.
where can i find undocumented ex stored proc?

thanks.

[Mike Doyle]

On 11/3/98 12:09:16 PM, dion wrote:
> On 11/2/98 5:07:39 PM, Mike Doyle wrote:
> On 11/2/98 1:42:05 PM, bads
> wrote:
> Hi there!
Is it possible to supply
> variable for the
> xp_cmdshell
> parameter?
here's an
> example:

declare @@okay
> char(20)
select
> @@okay = "dir
> c:mssqlinn"
exec
> master..xp_cmdshell
> @@okay

but this
> doesn't work. is there
> any workaround on this?
> instead
of writing
> a new extended sp to
> cater for this.

thanks in
> advance.

dion
--
dion, I think the
> problem is in your variable
> declaration, use

declare @okay
> char(20)
select @okay = "dir
> c:mssqlinn"
exec
> master..xp_cmdshell @okay

also look at the
> extended stored
> procedures for this type of function (dir), they're not
> documented
> so they won't necessarily be present in future releases but
> they
> come in handy.

cheers..
Mike

tried that one before and doesnt work.
> i tried all the possible kind
of declaration and still not working.
> it's just an example of one i did.
where can i find undocumented ex
> stored proc?

thanks.

Dion,

unsure as to why your code is not working, what error are you getting ? What if you put a begin and end at start and end of your code, i.e.

begin
<code>
end

undocumented ex. stored procs can be found in the master database, try..

select * from master..sysobjects where name like &#34;xp_%&#34;

which should give you a list of the procedures.

let me know the error message you are getting, if any ?

cheers..
Mike

[dion]

On 11/3/98 5:40:09 PM, Mike Doyle wrote:
> On 11/3/98 12:09:16 PM, dion wrote:
> On 11/2/98 5:07:39 PM, Mike Doyle
> wrote:
> On 11/2/98 1:42:05 PM, bads
> wrote:
> Hi there!
Is it
> possible to supply
> variable for the
> xp_cmdshell
>
> parameter?
here&#39;s an
> example:

declare @@okay
>
> char(20)
select
> @@okay = &#34;dir
> c:mssqlinn&#34;
exec
>
> master..xp_cmdshell
> @@okay

but this
> doesn&#39;t work. is there
>
> any workaround on this?
> instead
of writing
> a new extended sp
> to
> cater for this.

thanks in
> advance.

dion
--
dion, I
> think the
> problem is in your variable
> declaration, use

declare
> @okay
> char(20)
select @okay = &#34;dir
> c:mssqlinn&#34;
exec
>
> master..xp_cmdshell @okay

also look at the
> extended stored
>
> procedures for this type of function (dir), they&#39;re not
> documented
>
> so they won&#39;t necessarily be present in future releases but
>
> they
> come in handy.

cheers..
Mike

tried that one before and
> doesnt work.
> i tried all the possible kind
of declaration and still
> not working.
> it&#39;s just an example of one i did.
where can i find
> undocumented ex
> stored proc?

thanks.

Dion,

unsure as to why
> your code is not working, what error are you getting ? What if you put a
> begin and end at start and end of your code,
> i.e.

begin

end

undocumented ex. stored procs can be found in
> the master database, try..

select * from master..sysobjects where name
> like &#34;xp_%&#34;

which should give you a list of the
> procedures.

let me know the error message you are getting, if any ?

cheers..
Mike

++++++++++++++++++++++++++++
Mike,
here&#39;s error message :

(1 row(s) affected)
Error executing extended stored procedure: Invalid Parameter Type

to give you an idea of what i am trying to accomplish ,
my code will select all the name in sysdevices and
compared it in the device directory in NT. And then all the
files not in sysdevices will be deleted. this is one of the things
i want to accomplish.

thnks again.

[KT]

Here&#39;s a code example to use to help you get started with this, if you&#39;re still working on it. It gives a dir listing. Pretty simple, but it should help.

declare @cmd varchar(150)

select @cmd = &#39;exec master..xp_cmdshell &#39; + char(39) +
&#39;dir&#39; + char(39)

exec (@cmd)

On 11/4/98 12:33:34 PM, dion wrote:
> On 11/3/98 5:40:09 PM, Mike Doyle wrote:
> On 11/3/98 12:09:16 PM, dion
> wrote:
> On 11/2/98 5:07:39 PM, Mike Doyle
> wrote:
> On 11/2/98
> 1:42:05 PM, bads
> wrote:
> Hi there!
Is it
> possible to supply
>
> variable for the
> xp_cmdshell
>
> parameter?
here&#39;s an
>
> example:

declare @@okay
>
> char(20)
select
> @@okay = &#34;dir
>
> c:mssqlinn&#34;
exec
>
> master..xp_cmdshell
> @@okay

but
> this
> doesn&#39;t work. is there
>
> any workaround on this?
>
> instead
of writing
> a new extended sp
> to
> cater for
> this.

thanks in
> advance.

dion
--
dion, I
> think the
>
> problem is in your variable
> declaration, use

declare
> @okay
>
> char(20)
select @okay = &#34;dir
> c:mssqlinn&#34;
exec
>
>
> master..xp_cmdshell @okay

also look at the
> extended stored
>
>
> procedures for this type of function (dir), they&#39;re not
> documented
>
>
> so they won&#39;t necessarily be present in future releases but
>
>
> they
> come in handy.

cheers..
Mike

tried that one before and
>
> doesnt work.
> i tried all the possible kind
of declaration and
> still
> not working.
> it&#39;s just an example of one i did.
where
> can i find
> undocumented ex
> stored
> proc?

thanks.

Dion,

unsure as to why
> your code is not working,
> what error are you getting ? What if you put a
> begin and end at start
> and end of your code,
> i.e.

begin

end

undocumented ex. stored
> procs can be found in
> the master database, try..

select * from
> master..sysobjects where name
> like &#34;xp_%&#34;

which should give
> you a list of the
> procedures.

let me know the error message you are
> getting, if any
> ?

cheers..
Mike

++++++++++++++++++++++++++++
Mike,
here&#39;s
> error message :

(1 row(s) affected)
Error executing extended stored
> procedure: Invalid Parameter Type

to give you an idea of what i am
> trying to accomplish ,
my code will select all the name in sysdevices and
>
compared it in the device directory in NT. And then all the
files not in
> sysdevices will be deleted. this is one of the things
i want to
> accomplish.

thnks again.

************************************************** ******
-- The following Script works with SQL 6.5
-- The important thing is to use VARCHAR instead of char

DECLARE @cmd varchar(255)
SELECT @cmd = &#39;dir c:mssqlinn&#39;
CREATE TABLE #temp ( filename varchar(255) NULL )
INSERT #temp EXEC master..xp_cmdshell @cmd
--work with the data
SELECT * FROM #temp
DROP TABLE #temp
GO

Juergen