outputing a file from SQL query if query result = true... 1

[misuser]

Can I run a stored proc/DTS package that looks for a condition, ie sales > 1000, and then if this query is true it outputs a file to a specific location - (we can then poll for this file with the Crystal Enterprise Event Server...)

The bit I'm really struggling on is how to output a file to a predefined location if the query meets a certain criteria.

Help much appreciated!

 

[JamesLean]

You might construct your logic like this:

IF (SELECT SUM(sales) FROM tbl) > 1000
BEGIN
  --do export here
END

Your export might use bcp or dtsrun utility depending on how you want to export the file. You can use xp_cmdshell to execute either of these.

--James

 

[misuser]

Thanks for this - It's exactly the second bit im struggling on after the begin statement - I want to load a file, (any file), to a location , ie c:\\mis....

Do you have any help with the syntax?

 

[JamesLean]

1) Using bcp:

EXEC master..xp_cmdshell 'bcp mydb..mytbl out "c:\mis\myfile.txt" -Smyserver -T -c -t,'

This will output the table mytbl to the file myfile.txt. It uses the server myserver and connects using a trusted connection. It uses character data with comma-separated fields. Look up bcp in BOL for full syntax details.

2) Using DTS:

Assume you have set up a DTS package to do the export. You can execute it using this:

EXEC master..xp_cmdshell 'dtsrun /Smyserver /E /Nmypackage'

This executes mypackage on myserver connecting with a trusted connection. Again full details on dtsrun are in BOL.

--James

 

[misuser]

Thanks I seem to be getting somewhere with this..!

Cheers again