dynamic archiving

I need to set up a stored procedure or DTS that can export data to a text file - based on parametres passed when it's called. We have a scheduling server, where all our logging is stored and procedures are called. The data I need to export can be on diffierent servers in different databases.

I started with the following:

DECLARE
-- @.TableName varchar(128),
-- @.Filename varchar(200),
-- @.date_field varchar(20),
@.SQL varchar(2000)
-- SET @.TableName =

SET @.SQL = ''''+ 'bcp "SELECT * FROM '+@.TableName+ ' WHERE ' + @.Date_Field+
' > GETDATE()"'+ ' queryout "c:\test.txt" -c -q -U"sa" -P"" '+ ''''
print @.SQL

-- set @.SQL = 'EXEC master.dbo.xp_cmdshell ' + @.SQL
-- EXEC (@.SQL)

However, I'm not sure how to work in the dynamic server names. A variation of the above works fine locally on my machine - but I'm thinking I may need to use a DTS to get the servers in there...if so, does bcp even work through DTS? WHat about the variables? Does anyone have a better way of doing this?

for server name, you specify -S. Does this not work?

|||

Well, der. Thanks, I forgot about that.

I have come up with another problem, however:

I'm running BCP through a stored procedure so that the exports can be dynamic - however, I can't get the proc to fail if the bcp command fails. It returns an out put indicating failure, but won't actually fail the proc - I can't seem to use raiserror.

Is there a way to take the output from queryout and do something with it?

|||

I think I figured this out.

Still testing, however, it looks like adding "NO_OUTPUT" forces SQL server to interpret the results as either failure or success - if you don't specify this, it seems to consider the execution of xp_cmdshell rather than the result of the execution. (i.e., I would get the output results returned indicating the specifics of the error, but the value of @.@.ERROR within the proc would not change)

I could put the output results into a file, but that would complicate the process as I would then need to evaluate the output before proceeding (which would be a pain with a text file).

If anyone knows how to get the output into a table on execution, especially a temp table, that would be nice to know...

|||This might be fixed in SQL 2005, where if command inside xp_cmdshell fails, then the batch fails, but not sure, you should test it out. I'm assuming you're on SQL 2000.|||

We don't have 2005. Still on 2000.

This is terribly frustrating. :(

Any further suggestions would be greatly appreciated, as this still seems to be wonky.

|||

did you try checking the return value from xp_cmdshell?

i.e.

declare @.ret int

exec @.ret = xp_cmdshell 'bcp xxxxx'

if @.ret <> 0 raiserror ('Error!', 16,1)

go

|||

This worked great! it forces an error when run from a DTS.

In addition, I forgot that most system stuff can be saved in a temp table - i.e.,

create table ##output

(output varchar(255))

Insert ##output

(output)

exec xp_cmdshell @.SQL

So this was great! thanks so much for your help.