Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Script - trying to output text to file

Status
Not open for further replies.

alexhu

MIS
Joined
Sep 25, 2001
Messages
1,288
Location
GB
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
 
You need to put the database name in which the Suppliers table resides in front of Suppliers i.e.

Select * from TestDB..Suppliers.
 
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
 

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.
 
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.
 

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.
 
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
 

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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top