March 16, 2009 at 12:58 am
I have a SP which has getdate() has parameters
The data needs to be exported to an FTP site or a share drive.
I would like to append a date to the excel file when created dynamically e.g data_CreatedDate
I want to attach the SP to a job
I reckon i would need to check for the file first in the folder if it exist using XP_CMDSHELL and if yes delete or will it just override it
Can i use OPENrowset or OPENDATASOURCE for this?
** this is SQL 2000
Thanks in Advance
March 16, 2009 at 1:14 am
If you want to export data to a excel file you need to be looking at the bcp utility.
"Keep Trying"
March 16, 2009 at 1:59 am
Need more info.
Can BCP export to excel.
About xp_cmdshell
thanks in advance
March 16, 2009 at 5:41 am
You can use openrowset or opendatasource to export data to excel. If the data set is large its better to use bcp.
Example of bcp - xp_cmdshell 'bcp "select col1,xol2 from table1" queryout "C:\test.xls" -c -t"+" -r"" -S "YourServerInstance" -U"user" -P"pwd"'.
bcp is a command line utility so you need to xp_cmdshell to execute it from within sql server.
Pls lookup BOL for more details.
"Keep Trying"
March 17, 2009 at 3:12 am
Patrick
"** this is SQL 2000"
Will you stay in 2000 ? If so DTS and VB.script may help you.
I have left DTS behind me so I cannot help you there.
/Gosta
March 17, 2009 at 8:32 pm
Patrick,
actually Excel can get data by running a query against SQL Server.
Did you consider this option?
_____________
Code for TallyGenerator
March 18, 2009 at 12:10 am
Gosta i know and i feel u.
I would def could have done this easier using SSIS.
Thanks
March 18, 2009 at 12:13 am
Yeah i know you can query sql data via excel etc..
But the people who need the data are not in our network.
The data needs to be send in interval across a network externally.
Thx guys...
I have used a package > job and xp_cmdshell to move the data
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply