using osql.exe to spool a csv file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using osql.exe to spool a csv file

Author

Topic

Seanryan
Starting Member

16 Posts

Posted - 2011-10-27 : 05:51:59

I have a stored procedure that runs fine as a scheduled agent task and generates a file each night (output file in advanced step for tsql task) but the file is a straight forward space delimited sql output.

I have read that osql.exe can be used with - options to create a comma delimited output for SQL.

Seems to work but I need to run the stored procedure with @dates and @name prompts of values. Osql.exe doesn't seem to like the use of @name = 'myname" string values?

worked it out - just syntax issue. thanks

Seanryan
Starting Member

16 Posts

Posted - 2011-10-27 : 06:01:25

This works as straight execution:

declare @st_dat datetime
declare @en_dat datetime
declare @shipping_line nchar(30)

set @st_dat = dateadd(hour, -23, getdate())
set @en_dat = getdate()

exec wct_cms_live.dbo.sp_rep_gatehouse
@st_dat = @st_dat,
@en_dat = @en_dat,
@shipping_line = DFDSContainerLine

but if I call it as:

EXEC master..xp_cmdshell 'osql.exe -S WCTMS01 -E -Q "

declare @st_dat datetime
declare @en_dat datetime
declare @shipping_line nchar(30)

set @st_dat = dateadd(hour, -23, getdate())
set @en_dat = getdate()

exec wct_cms_live.dbo.sp_rep_gatehouse
@st_dat = @st_dat,
@en_dat = @en_dat,
@shipping_line = DFDSContainerLine" '

I get no rows - I'm presuming the dates are getting messed up

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources