how to recieve status value of a a proc in dtynamic sql

[miq]

      set @v_cmdstr = 'exec [red]@v_result =[/red] master..xp_cmdshell ''copy ' + @v_srcpth + ' ' + @v_despth + '''' + ' , no_output'
      execute (@v_cmdstr)

Here the @v_result variable ussage is not allowed. so how can i verify (in code) that the copy process has been a successful?

p.s. its a part of a bigger transational job.

 

[vbkris]

the scope of a variable inside an exec gets deatroyed within the exec() command. therefore the code above will not execute. try this:

set @v_cmdstr = 'declare @v_result varchar(100)
exec @v_result = master..xp_cmdshell ''copy ' + @v_srcpth + ' ' + @v_despth + '''' + ' , no_output; print @v_result'
execute (@v_cmdstr)



that will print the output, but u still cannot use it in stmts outside exec command. the only way i have overcome that is by using #temporary tables.

try this

create table #tmp_tbl(theResult varchar(100))

set @v_cmdstr = 'declare @v_result varchar(100)
exec @v_result = master..xp_cmdshell ''copy ' + @v_srcpth + ' ' + @v_despth + '''' + ' , no_output; insert into #tmp_tbl values(@v_result);'
execute (@v_cmdstr)

declare @v_result varchar(100)
select @v_result=theResult from #Tmp_Tbl

select @v_result



Known is handfull, Unknown is worldfull

 

[miq]

Thats a nice workaoround.

thanx vbkris :k:

 

[vbkris]

wc...

Known is handfull, Unknown is worldfull