xp_cmdshell not creating a file

[croiva25]

DECLARE @query NVARCHAR(2000)
SELECT @query = 'osql -n -w 5000 -E -s "," -d master -Q "
SELECT
a.HospitalName,
a.HospitalCode,
c.ProductName,
b.UnitsDiscarded,
b.DateEntered,
b.DateCompleted,
b.CompiledBy
FROM
Ivana_test.dbo.Units b
INNER JOIN Hospitals a ON (a.HospitalID = b.HospitalID)
INNER JOIN Products c ON (b.ProductID = c.ProductID)
INNER JOIN FateOfProducts d ON (d.FateID = b.FateID)
ORDER BY
a.HospitalID" -o "c:\test.txt"'
EXEC Ivana_test.dbo.xp_cmdshell @query

I am trying to run this query that will hopefully create me comma delimited results in a file,but this file never gets created. It runs and saying 1 row affected but no file there, could somebody have a look at it please

 

[rsinj]

first, have you tried what the command evaluates to interactively?

also, though I'm still using v6.5, I assume when you have -d master, you want the database to be master? the option is a -D (lower and upper do make a difference). this will cause isql to generate an error.

lastly, your command says osql - if you just copied/pasted here, then that is the first mistake - it should be isql.

as I said, first thing, manually run the query you have @query set to above.

 

[rsinj]

additionally, you have not supplied a username/password to log in with? are you running with standard security requiring username/password?

again, run it manually first.

 

[hmckillop]

You can use isql or osql, one uses odbc and the other uses the DB-library to connect to your database.
If you are using osql, either specify username and passwords, use -d and then your database name, replace last line with "EXEC master.dbo.xp_cmdshell @query"

Alternatively yf you do get more problems, you could try using bcp
(obviously replace the query with yours)

DECLARE @query NVARCHAR(2000)
SELECT @query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @query


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[croiva25]

DECLARE @query NVARCHAR(2000)
SELECT @query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

What am I doing wrong, and in regards to my my cmdshell question, my query runs on its own brings results fine....
Could somebody please help me

 

[hmckillop]

The same bcp command for me created a file, obviously with a different select.
Do you have write permissions to the folder you specify in the BCP?
You shouldnt need to put the server name, login and password in quotes unless there is a space in any of them

Your query should be

DECLARE @query NVARCHAR(2000)
SELECT @query = 'bcp "SELECT a.HospitalName, a.HospitalCode, c.ProductName, b.UnitsDiscarded, b.DateEntered, b.DateCompleted, b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID) INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID) INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) ORDER BY a.HospitalID" queryout c:\test.txt -c -SmachineName -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @query



I dont understand why this shouldnt work with your machine especially as you see the correct output in query analyser.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[jms8222]

Are you looking for the file on your local drive, or on the server's C drive? I know that if I run the code against a test table on our server, it creates the file on the server's C drive.

Just a thought.