OSQL command problem 1

[thamms]

Hi,

Osql does not seem to like temp tables of any kind. I get errors when trying to run either one of these:

declare @cmd varchar(4000)
select @cmd = 'osql -E -Q "select * from @table" -o "C:\myfile.csv" -w500'
exec master..xp_cmdshell @cmd

-OR-

declare @cmd varchar(4000)
select @cmd = 'osql -E -Q "select * from #table" -o "C:\myfile.csv" -w500'
exec master..xp_cmdshell @cmd

Any ideas on how to work around this? I don't want to use a regular table.

Thanks

 

[mrdenny]

You have to use a global temporary table. When you run osql from within xp_cmdshell you are creating a new session into the database. This means that your session level objects (temp tables, table variables, etc) are not avaialble to the osql session.

Also you should use BCP to export data to csv files. osql is a query execution tool, BCP is a data export tool.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog