exec master..xp_cmdshell @var <- I cant make it work!
-
Hi Guys,
If I declare a variable and assign it some string like: 'ping 192.168.2.1' then execute master..xp_cmdshell with the variable, it works fine.
If I follow it with an update command I get a error that reads: Invalid syntax near '='
Im using a stored proceedure with the following logic:
----
1 declare @var as nvarcar(255)
2 select @var = 'some dos command'
3 exec master..xp_cmdshell @var
4 update mytable
5 set myfield = 'Yes'
6 where myfield is NULL
-------
the error for this would be:
Invalid syntax near '=' on line 4
Any help with this would be greatly appreciated as I'm working on a tight schedule and this has me completely stuck.
Thanks,
Ben
-
In your example there's a typo - declare @var as nvarchar(255) - (missing h)
However, if it's not that (probably not), I think that we may need to see the actual code instead of an example.
I can't reproduce it, this works just fine (on SQL 2000 and XP)
create table mytable ( id int not null, myfield varchar(10) null )
insert mytable (id) select 1
go
declare @var as nvarchar(255)
select @var = 'ping 127.0.0.1'
exec master..xp_cmdshell @var
update mytable
set myfield = 'Yes'
where myfield is NULL
go
select * from mytable
id myfield
----------- ----------
1 Yes
/Kenneth
-
I've just run your code and it works perfectly...
Heres my (slghtly abridged) code:
DECLARE @copycommand AS nvarchar(255)
DECLARE @transdate AS DATETIME
DECLARE @transdate2 AS CHAR(8)
DECLARE @dtschecker AS int
SELECT @transdate = (SELECT date_of_entry FROM date_to_run WHERE extract_name = 'Pay Insurer')
SELECT @transdate2 = convert(char(8),@transdate,112)
exec @dtschecker = master..xp_cmdshell 'DTSRun /~very long dts code'
IF @dtschecker = 0
BEGIN
-- copy new csv file to correct location
SELECT @copycommand = 'copy "\\myserver\workarea\payinsurer.csv" "\\myserver\livefolder\payinsurer' + @transdate2 + '.csv"'
exec master..xp_cmdshell @copycommand, no_output
-- report a successful extract
UPDATE date_to_run
SET run_notes = 'Successfully created extract file' , has_run = 'Y'
WHERE extract_name = 'Pay Insurer'
END
-
WTH??
I just copied the code into notepad, canceled the stored proc window, reopenned it and pasted the code back from notepad and it worked??
oh well.
Thanks for looking.
Ben
-
It's the magic that makes our days interesting
/Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply