OSQL quarks?

Question

Post reply

OSQL quarks?

AVB

SSChampion

Points: 11418
More actions
February 26, 2008 at 6:35 am

#76910

I just created a txt file from OSQL on a remote machine.
EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table" -o\\MachineName\C$\var_out.txt'
This worked fine. But if I add a directory that has a space in the name i.e. c$\Test Dir\var_out.xt
it won't work.
EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table" -o\\MachineName\C$\Test Dir\var_out.txt'
Is this a limitation of OSQL or am I not doing something correctly?

Viewing 11 posts - 1 through 10 (of 10 total)

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

ziminski SSC Veteran Points: 221 More actions · Answer 1

Not tested but it would seem that you need to put quotes around the path of the output file.

Rick

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 2

AVB (2/26/2008)
I just created a txt file from OSQL on a remote machine.
EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table" -o\\MachineName\C$\var_out.txt'
This worked fine. But if I add a directory that has a space in the name i.e. c$\Test Dir\var_out.xt
it won't work.
EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_FRCBL_BOL" -o\\MachineName\C$\Test Dir\var_out.txt'
Is this a limitation of OSQL or am I not doing something correctly?

Hello,

Can you try by having square [] brackets for the directory?

I think it worked for me and it was long time back.

Thanks

Lucky

tbeadle SSCommitted Points: 1635 More actions · Answer 3

[font="Arial"]Hello,

The problem ( or challenge ) is with the operating system.

When you create a sub-directory with a space in the name you have to use quotes to go there.

Like :

mkdir The space

cd \"The space"

It's not osql. Try it in a DOS window using the cd command.

Regards,

Terry

[/font]

AVB SSChampion Points: 11418 More actions · Answer 4

Thanks for the reply....

I actually just tried the CD command without quotes and it worked.

C:\>CD documents and settings\all users

it also works with quotes:

C:\>CD "documents and settings\all users"

I'm on XP SP2.

I tried OSQL with quotes and it still doesn't work.

EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table"-o "\\MachineName\c$\To Do\output.txt" '

I also tried the brackets as suggested before.. I guess I'll have to keep tooling around with it.

ziminski SSC Veteran Points: 221 More actions · Answer 5

ziminski

SSC Veteran

Points: 221

February 26, 2008 at 2:12 pm

#782254

Did you try the re-direction symbol >

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Quarks?

You can use the DOS compatible names in your path to eliminate the spaces. The DIR command with the /x options gives you the DOS compatible names. In the example below, PROGRA~1 is the DOS compatible name for Program Files.

C:\>dir p* /x

Volume in drive C has no label.

Volume Serial Number is F41D-1F50

Directory of C:

02/18/2008 03:08 AM PROGRA~1 Program Files

0 File(s) 0 bytes

1 Dir(s) 21,217,181,696 bytes free

C:\>dir PROGRA~1 /x

Volume in drive C has no label.

Volume Serial Number is F41D-1F50

Directory of C:\PROGRA~1

02/18/2008 03:08 AM .

02/18/2008 03:08 AM ..

06/22/2007 08:53 AM Adobe

02/15/2008 03:00 PM COMMON~1 Common Files

07/05/2005 01:15 PM COMPLU~1 ComPlus Applications

01/28/2008 03:02 PM INTERN~1 Internet Explorer

11/12/2007 01:02 PM MICROS~2 Microsoft Office

04/09/2007 01:13 PM MICROS~4 Microsoft SQL Server

07/09/2007 04:12 PM SQLIO

1 File(s) 245 bytes

7 Dir(s) 21,217,181,696 bytes free

AVB SSChampion Points: 11418 More actions · Answer 7

Ziminski,

After I read your suggestion I did try the > (redirection) symbol.

Not sure if my code is correct:

EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table"-o >\\MachineName\c$\To Do\output.txt'

When I used the > symbol I did not get the recordset with all of the OSQL switches/flags so that was good... but it did not create a txt file. When I saw null in the output recordset I thought it had worked.

AVB SSChampion Points: 11418 More actions · Answer 8

Mr. Jones,

Thanks for the reply.

I tried the DOS shortname and it didn't work.

EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table"-o \\MachineName\c$\ToDo~1\output.txt'

I got the Usage Flags in the OutPut recordset. So I tried using the Redirection Symbol with the Shortname:

EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table"-o >\\MachineName\c$\ToDo~1\output.txt'

This time a TXT file was created but it did not contain the data from the query. It contained an error message:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '-'.

ziminski SSC Veteran Points: 221 More actions · Answer 9

Looks like you're getting closer but the syntax is wrong, when you use the redirection symbol you do not specify the -o option for out put. so you should have

EXEC master..xp_cmdshell 'osql -E -Q"select top 1 * from t_Table" >\\MachineName\c$\ToDo~1\output.txt'

Rick

AVB SSChampion Points: 11418 More actions · Answer 10

Hallelujah!!!!

That worked!!!

Thanks to all that helped.