SQL Script - trying to output text to file

[alexhu]

If I do "select * from suppliers" I get a valid list of details from the table.

If however I do:

declare @str varchar(255)
select @str = 'isql -Q "select * from Suppliers" -E -oc:\file.txt'
exec master..xp_cmdshell @str

to get the output to write to a file, the contents of the file are :

Msg 208, Level 16, State 1, Server UKHUDGHTONA5274, Line 1
Invalid object name 'Suppliers'.


Why ?????

Alex

 

[RickCole]

You need to put the database name in which the Suppliers table resides in front of Suppliers i.e.

Select * from TestDB..Suppliers.

 

[alexhu]

Ok thanks - thats changed it a bit now I get:

Msg 4004, Level 16, State 1, Server UKHUDGHTONA5274, Line 1
ntext data cannot be sent to clients using DB-Library (such as ISQL)
or ODBC version 3.7 or earlier.

Any ideas ?

Alex

 

[tlbroadbent]

Use OSQL instead of ISQL. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Try selecting the column names within the query (omitting any text datatype column names) and see if it works that way. i.e.

Select Col_1, Col_2, Col_3, etc from TestDB..Suppliers

Of course if you want the text data type columns returning then your going to have to work around this in some other way.

Rick.

 

[tlbroadbent]

OSQL handles ntext columns. It is the suggested replacement for ISQL in SQL 7 and 2000. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[alexhu]

Thansk a lot - the osql fixed it, but it only outputs one line. How do I get it to scan the whole table ?

Sorry to be a pain here but I'm an Oracle DBA and can't understand why Microsoft doesn't a similar facility for doing this as in Oracle (Spool filename / command / spool off)

Having to program somewhat to do this is really not endearing me to this product ))

Alex

 

[tlbroadbent]

I don't know of any reason why it should output only one line. Is the one record complete? How large is the record?

A feature similar to SPOOL would be nice. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[alexhu]

My mistake! I opened in up in notepad and just saw the header row. However on scrolling down 2 pages found the first record. Ditto for the rest.

Thanks for all the help, I'm sure I'll be back !

Alex