osql form t-sql with select statement

Question

osql form t-sql with select statement

Jacobus-785651

Ten Centuries

Points: 1106
More actions
June 20, 2008 at 7:55 am

#70865

Hi guys,
I am getting a littlebit nervous here. Just trying to write some files with a list of tables in a table. Every record points to a table. With a cursor i am running thru the records and that works fine. Now i want in the loop to use the osql for every table name to create a file including headers.
I tried the code below but that refuses to work anybody have a suggestion what is wrong. I know there is a enclotioin mark missing but i don't see it.
declare @table varchar(100)
declare @FileName varchar(100)
set @table ='cvr_extracties' --can be any table
set @Filename = 'c:\test.txt' --can be any name
--extract_view is the table with the table names
If exists(Select * from CVR.dbo.EXTRACT_VIEW where Tabelnaam=@table)
Begin
Declare @STR varchar(1000)
set @STR='Exec Master..xp_Cmdshell ''osql -E -S NLLSJAWOO -d CVR -h -n -s; -w1000 -q "Select * from '+db_name()+'..'+@table+'" -o "'+@FileName+'"'
print @STR
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'
:hehe:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

Try adding two single quotes at the end of your string creation:

set @STR='Exec Master..xp_Cmdshell ''osql -E -S NLLSJAWOO -d CVR -h -n -s; -w1000 -q "Select * from '+db_name()+'..'+@table+'" -o "'+@FileName+'"'''

Jacobus-785651 Ten Centuries Points: 1106 More actions · Answer 2

It's amazing. I tried adding the two '' it and it is not giving me the error message again. Thanx very much. You are th best.

Thanx

Sjaak

:hehe:

shalabh.sachdeva SSC-Addicted Points: 422 More actions · Answer 3

The problem is a little different here,

I am trying to create a file using sp_cmdshell command and osql but the format I want is to be UTF-8 character format. Which is coming out to be the unicode character set.As it shows me some other format inside the ouput file.

I applied :

master..xp_cmdshell 'osql -Ulogin -Ppassword -Q"select ''???? ??????? ???.''" -ddatabase -oc:\output.txt'

But nothing worked as per I required.

Please help.:)

Jacobus-785651 Ten Centuries Points: 1106 More actions · Answer 4

The osql utility is not something Microsoft is going to keep in later versions. So try to use the bcp utility. Further more when using the bcp you have to be aware that SQL server only deals with unicode format files. You have to use the -w flag.

Does this helps you in any way?

:hehe:

Jacobus-785651 Ten Centuries Points: 1106 More actions · Answer 5

SET @bcpCommand = @bcpCommand + @FileAndPath + '" -b 1000000 -w -T '

EXEC master..xp_cmdshell @bcpCommand

:hehe: