Running sql through osql to produce a text file

Question

Post reply

Running sql through osql to produce a text file

Paula-196779

SSCrazy

Points: 2575
More actions
September 15, 2005 at 5:49 am

#187592

Hi,
I'm trying to run the following sql using osql from the command line to produce a file of data. However I get the error "Syntax error converting the nvarchar value '"Chief Executive","' to a
column of data type int." in the output file. I'm not trying to convert it at all so I don't know why this is happening. Any ideas? P.S I know it shouldn't be using nvarchar, it's just been designed badly and I will be re-designing the database soon!
Many thanks,
Paula.

USE FOILIVE
SET NOCOUNT ON
SELECT '"'+ isnull(Departments.Department, " ")+'","'+
Requests.RequestID +'","'+
isnull(Requests.Details," ") +'","'+
isnull(Requests.Surname," ") +'","'+
isnull(Requests.FirstName," ") +'","'+
isnull(convert(varchar(10), Requests.DateLogged,103)," ") +'","'+
isnull(convert(varchar(10), Requests.TargetDate,103)," ") +'","'+
isnull(convert(varchar(10),Requests.CompletionDate,103)," ") +'","'+
isnull(RequestType.RequestType," ") +'","'+
isnull(Subject1.Subject, " ") +'","'+
isnull(Subject2.Subcategory," ")+'"'
FROM Subject2 RIGHT JOIN
(Subject1 RIGHT JOIN (Departments INNER JOIN
((password INNER JOIN Requests ON password.OfficerID = Requests.OfficerID)
INNER JOIN RequestType
ON Requests.RequestTypeID = RequestType.RequestTypeID)
ON Departments.DeptID = password.DepartmentID)
ON Subject1.SubjectID = Requests.Subject1ID)
ON Subject2.SubcategoryID = Requests.Subject2ID
ORDER BY Departments.Department, Requests.RequestID;

Viewing 9 posts - 1 through 8 (of 8 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

Can you post the schema.

Example password.OfficerID = Requests.OfficerID

If the OfficerID in any one of the tables is NVARCHAR and other INT, Then the NVARCHAR Column has a value 'Chief Executive' it will raise an error while comparing.

Regards,
gova

Paula-196779 SSCrazy Points: 2575 More actions · Answer 2

Sorry, I should have explained that the sql runs fine in Query Analyser, without the commas and speech marks, so the error must be something to do with the concatenation but I just can't see what.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

What is the datatype of Requests.RequestID and why don't you use isnull for this?

Paula-196779 SSCrazy Points: 2575 More actions · Answer 4

The datatype for RequestId is int, and it does not need isnull because it cannot be null, it is the key to the table.

Thanks,

Paula.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

Use convert(nvarchar, Requests.RequestID) instead. Does it change anything?

Paula-196779 SSCrazy Points: 2575 More actions · Answer 6

Thanks for this. I can now get the data into the file. Formatting is terrible but I can work on that!

Is it me or is the error message really deceptive? It doesn't even point to the right field. In future I will check for any int fields in the whole query!

Thank you!

peterhe SSChampion Points: 11469 More actions · Answer 7

Are you using double quotes in isnull function, like isnull(Departments.Department, " ") ?

osql uses ODBC connects to SQL server and QUOTED_IDENTIFIER is set ON by default.

Change them to single quotes should work.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 8

Chief Executive comes from Departments.Department, which cannot be (pre)added to an int. But I agree, the error message could be better.