osql -o

I would like to create a dynamic output file with the following code:
Declare @.cmd varchar(1000)
Declare @.filepath varchar(1000)
Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
mm/dd/yy appended to the filename then .csv extension
Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
2000' >>>> here is where I would like the new filename to be used.
Exec master..xp_cmdshell @.cmd
Is there also anyway to log this to a file to track execution ?
Thanks...Hi,
you might be interested in that:
http://www.databasejournal.com/feat...cle.php/3386661
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hoosbruin" <Hoosbruin@.Kconline.com> schrieb im Newsbeitrag
news:BNGdnQ_yUIxBcuvfRVn-1Q@.kconline.com...
>I would like to create a dynamic output file with the following code:
>
> Declare @.cmd varchar(1000)
> Declare @.filepath varchar(1000)
> Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
> mm/dd/yy appended to the filename then .csv extension
> Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
> 2000' >>>> here is where I would like the new filename to be used.
> Exec master..xp_cmdshell @.cmd
>
> Is there also anyway to log this to a file to track execution ?
>
> Thanks...
>
>
>

osql in a cmd file, with SQL statements coming from the same cmd f

I want to run osql in a command file. Instead of having the SQL statements
in a separate .sql file, using "osql -i file.sql..." I want to have the SQL
statements right there in the command file. This way my command file is
self-contained; only one file to worry about instead of separate cmd and sql
files.
In unix (or more precisely in the bash shell) this would be done by what is
called a "here document". Conceptually:
osql <<END_OF_SQL
select * from customers
select * from suppliers
END_OF_SQL
Any way to do this with OSQL, or by some CMD.EXE trick?
forestial wrote:
> I want to run osql in a command file. Instead of having the SQL
> statements in a separate .sql file, using "osql -i file.sql..." I
> want to have the SQL statements right there in the command file.
> This way my command file is self-contained; only one file to worry
> about instead of separate cmd and sql files.
> In unix (or more precisely in the bash shell) this would be done by
> what is called a "here document". Conceptually:
> osql <<END_OF_SQL
> select * from customers
> select * from suppliers
> END_OF_SQL
> Any way to do this with OSQL, or by some CMD.EXE trick?
Sure. You can create a CMD file to run the batch with contents like
this: Substitute -E to use a trusted connection which is recommended
over putting user id and password in the file. Use a capital Q to exit
OSQL immediately. Separate batches with Go and use -O for an output
file.
osql -Uuser -Ppassword (or -E) -Sserver -Q"Select id from sysobjects go
select id from sysindexes" -ooutput.txt
David Gugick
Imceda Software
www.imceda.com

osql

Hello
I use command OSQL and i have in output file strong character when i use the command xp_cmdshell
test.sql
DECLARE @.cmd sysname, @.var sysname
SET @.var =3D 'd:\mssql7\data'
SET @.cmd =3D 'dir ' + @.var
EXEC master..xp_cmdshell @.cmd
I use this osql osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o backup.log -b
and the oupput file :backup.log
there is stong character like '=FF' , R‚p
WHY ?
R‚pertoire de d:\mssql7\data
NULL
12/09/03 12:05 <DIR> .
12/09/03 12:05 <DIR> ..
26/09/03 19:29 2=FF097=FF152 Coffre.ldf
26/09/03 19:29 2=FF097=FF152 Coffre.mdf
27/08/03 14:19 2=FF886=FF721 coffre.zip
16/09/03 14:05 1=FF048=FF576 Etiquettes_Data.MDF
16/09/03 14:05 12=FF320=FF768 master.mdf
16/09/03 14:05 5=FF308=FF416 mastlog.ldf
16/09/03 14:05 1=FF048=FF576 model.mdf
16/09/03 14:05 786=FF432 modellog.ldf
16/09/03 14:05 8=FF388=FF608 msdbdata.mdf
16/09/03 14:05 786=FF432 msdblog.ldf
16/09/03 14:05 1=FF048=FF576 northwnd.ldf
16/09/03 14:05 3=FF801=FF088 northwnd.mdf
16/09/03 14:09 112=FF197=FF632 oscar_Donn‚es.MDF
26/09/03 19:29 3=FF879=FF731=FF200 oscar_Donn‚es2.MDF
26/09/03 19:29 112=FF132=FF096 oscar_Donn‚es3.MDF
16/09/03 14:09 1=FF310=FF720 oscar_Journal.LDF
26/09/03 19:29 1=FF310=FF720 oscar_Journal2.LDF
26/09/03 19:29 1=FF310=FF720 oscar_Journal3.LDF
16/09/03 14:05 1=FF835=FF008 pubs.mdf
16/09/03 14:05 786=FF432 pubs_log.ldf
25/09/03 15:48 63=FF176=FF704 TEMPDB.MDF
16/09/03 14:47 786=FF432 TEMPLOG.LDF
24 fichier(s) 4=FF216=FF196=FF161 octets
491=FF745=FF280 octets libres
(31 lignes affect‚es)This is possibly caused by your code page settings. Run master..xp_cmdshell
'chcp' to see what your settings are for xp_cmdshell (my guess is code page
437), as SQL Server will probably assume Lation 1 and displays different
characters than expected. You could try the -u (Unicode) osql switch to work
around it.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"brinster" <sbrinster@.seafrance.fr> wrote in message
news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
Hello
I use command OSQL and i have in output file strong
character when i use the command xp_cmdshell
test.sql
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'd:\mssql7\data'
SET @.cmd = 'dir ' + @.var
EXEC master..xp_cmdshell @.cmd
I use this osql
osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
backup.log -b
and the oupput file :backup.log
there is stong character like 'ÿ' ,
R‚p
WHY ?
R‚pertoire de d:\mssql7\data
NULL
12/09/03 12:05 <DIR> .
12/09/03 12:05 <DIR> ..
26/09/03 19:29 2ÿ097ÿ152 Coffre.ldf
26/09/03 19:29 2ÿ097ÿ152 Coffre.mdf
27/08/03 14:19 2ÿ886ÿ721 coffre.zip
16/09/03 14:05 1ÿ048ÿ576 Etiquettes_Data.MDF
16/09/03 14:05 12ÿ320ÿ768 master.mdf
16/09/03 14:05 5ÿ308ÿ416 mastlog.ldf
16/09/03 14:05 1ÿ048ÿ576 model.mdf
16/09/03 14:05 786ÿ432 modellog.ldf
16/09/03 14:05 8ÿ388ÿ608 msdbdata.mdf
16/09/03 14:05 786ÿ432 msdblog.ldf
16/09/03 14:05 1ÿ048ÿ576 northwnd.ldf
16/09/03 14:05 3ÿ801ÿ088 northwnd.mdf
16/09/03 14:09 112ÿ197ÿ632 oscar_Donn‚es.MDF
26/09/03 19:29 3ÿ879ÿ731ÿ200 oscar_Donn‚es2.MDF
26/09/03 19:29 112ÿ132ÿ096 oscar_Donn‚es3.MDF
16/09/03 14:09 1ÿ310ÿ720 oscar_Journal.LDF
26/09/03 19:29 1ÿ310ÿ720 oscar_Journal2.LDF
26/09/03 19:29 1ÿ310ÿ720 oscar_Journal3.LDF
16/09/03 14:05 1ÿ835ÿ008 pubs.mdf
16/09/03 14:05 786ÿ432 pubs_log.ldf
25/09/03 15:48 63ÿ176ÿ704 TEMPDB.MDF
16/09/03 14:47 786ÿ432 TEMPLOG.LDF
24 fichier(s) 4ÿ216ÿ196ÿ161 octets
491ÿ745ÿ280 octets libres
(31 lignes affect‚es)|||Thanks for your help
chcp return 850 how do run the commande osql with -u
like parameter osql .. -u437 ?
Regards
>--Original Message--
>This is possibly caused by your code page settings. Run master..xp_cmdshell
>'chcp' to see what your settings are for xp_cmdshell (my guess is code page
>437), as SQL Server will probably assume Lation 1 and displays different
>characters than expected. You could try the -u (Unicode) osql switch to work
>around it.
>-- >Michiel Wories, SQL Server PM
>This posting is provided "AS IS" with no warranties, and confers no rights.
>--
>"brinster" <sbrinster@.seafrance.fr> wrote in message
>news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
>Hello
>I use command OSQL and i have in output file strong
>character when i use the command xp_cmdshell
>
>test.sql
>DECLARE @.cmd sysname, @.var sysname
>SET @.var =3D 'd:\mssql7\data'
>SET @.cmd =3D 'dir ' + @.var
>EXEC master..xp_cmdshell @.cmd
>I use this osql
>osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
>backup.log -b
>and the oupput file :backup.log
>there is stong character like '=FF' ,
>R‚p
>WHY ?
>
>R‚pertoire de d:\mssql7\data
>
>NULL
>
>12/09/03 12:05 <DIR> .
>
>12/09/03 12:05 <DIR> ..
>
>26/09/03 19:29 2=FF097=FF152 Coffre.ldf
>
>26/09/03 19:29 2=FF097=FF152 Coffre.mdf
>
>27/08/03 14:19 2=FF886=FF721 coffre.zip
>
>16/09/03 14:05 1=FF048=FF576 Etiquettes_Data.MDF
>
>16/09/03 14:05 12=FF320=FF768 master.mdf
>
>16/09/03 14:05 5=FF308=FF416 mastlog.ldf
>
>16/09/03 14:05 1=FF048=FF576 model.mdf
>
>16/09/03 14:05 786=FF432 modellog.ldf
>
>16/09/03 14:05 8=FF388=FF608 msdbdata.mdf
>
>16/09/03 14:05 786=FF432 msdblog.ldf
>
>16/09/03 14:05 1=FF048=FF576 northwnd.ldf
>
>16/09/03 14:05 3=FF801=FF088 northwnd.mdf
>
>16/09/03 14:09 112=FF197=FF632 oscar_Donn‚es.MDF
>
>26/09/03 19:29 3=FF879=FF731=FF200 oscar_Donn‚es2.MDF
>
>26/09/03 19:29 112=FF132=FF096 oscar_Donn‚es3.MDF
>
>16/09/03 14:09 1=FF310=FF720 oscar_Journal.LDF
>
>26/09/03 19:29 1=FF310=FF720 oscar_Journal2.LDF
>
>26/09/03 19:29 1=FF310=FF720 oscar_Journal3.LDF
>
>16/09/03 14:05 1=FF835=FF008 pubs.mdf
>
>16/09/03 14:05 786=FF432 pubs_log.ldf
>
>25/09/03 15:48 63=FF176=FF704 TEMPDB.MDF
>
>16/09/03 14:47 786=FF432 TEMPLOG.LDF
>
>24 fichier(s) 4=FF216=FF196=FF161 octets
>
>491=FF745=FF280 octets libres
>
>
>(31 lignes affect‚es)
>
>.
>|||By specifying the -u flag (without parameters), theoratically there cannot
be an issue as osql will return characters as Unicode characters and
therefore the codepages do not need to match. I say "theoratically" as I'm
not 100% sure what's happening.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"brinster" <sbrinster@.seafrance.fr> wrote in message
news:184801c3872f$0f154250$a101280a@.phx.gbl...
Thanks for your help
chcp return 850 how do run the commande osql with -u
like parameter osql .. -u437 ?
Regards
>--Original Message--
>This is possibly caused by your code page settings. Run
master..xp_cmdshell
>'chcp' to see what your settings are for xp_cmdshell (my
guess is code page
>437), as SQL Server will probably assume Lation 1 and
displays different
>characters than expected. You could try the -u (Unicode)
osql switch to work
>around it.
>--
>Michiel Wories, SQL Server PM
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>--
>"brinster" <sbrinster@.seafrance.fr> wrote in message
>news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
>Hello
>I use command OSQL and i have in output file strong
>character when i use the command xp_cmdshell
>
>test.sql
>DECLARE @.cmd sysname, @.var sysname
>SET @.var = 'd:\mssql7\data'
>SET @.cmd = 'dir ' + @.var
>EXEC master..xp_cmdshell @.cmd
>I use this osql
>osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
>backup.log -b
>and the oupput file :backup.log
>there is stong character like 'ÿ' ,
>R‚p
>WHY ?
>
>R‚pertoire de d:\mssql7\data
>
>NULL
>
>12/09/03 12:05 <DIR> .
>
>12/09/03 12:05 <DIR> ..
>
>26/09/03 19:29 2ÿ097ÿ152 Coffre.ldf
>
>26/09/03 19:29 2ÿ097ÿ152 Coffre.mdf
>
>27/08/03 14:19 2ÿ886ÿ721 coffre.zip
>
>16/09/03 14:05 1ÿ048ÿ576 Etiquettes_Data.MDF
>
>16/09/03 14:05 12ÿ320ÿ768 master.mdf
>
>16/09/03 14:05 5ÿ308ÿ416 mastlog.ldf
>
>16/09/03 14:05 1ÿ048ÿ576 model.mdf
>
>16/09/03 14:05 786ÿ432 modellog.ldf
>
>16/09/03 14:05 8ÿ388ÿ608 msdbdata.mdf
>
>16/09/03 14:05 786ÿ432 msdblog.ldf
>
>16/09/03 14:05 1ÿ048ÿ576 northwnd.ldf
>
>16/09/03 14:05 3ÿ801ÿ088 northwnd.mdf
>
>16/09/03 14:09 112ÿ197ÿ632 oscar_Donn‚es.MDF
>
>26/09/03 19:29 3ÿ879ÿ731ÿ200 oscar_Donn‚es2.MDF
>
>26/09/03 19:29 112ÿ132ÿ096 oscar_Donn‚es3.MDF
>
>16/09/03 14:09 1ÿ310ÿ720 oscar_Journal.LDF
>
>26/09/03 19:29 1ÿ310ÿ720 oscar_Journal2.LDF
>
>26/09/03 19:29 1ÿ310ÿ720 oscar_Journal3.LDF
>
>16/09/03 14:05 1ÿ835ÿ008 pubs.mdf
>
>16/09/03 14:05 786ÿ432 pubs_log.ldf
>
>25/09/03 15:48 63ÿ176ÿ704 TEMPDB.MDF
>
>16/09/03 14:47 786ÿ432 TEMPLOG.LDF
>
>24 fichier(s) 4ÿ216ÿ196ÿ161 octets
>
>491ÿ745ÿ280 octets libres
>
>
>(31 lignes affect‚es)
>
>.
>