SQLTeam.com | Weblogs | Forums

SQLCMD output text file "/n"

sql-server-2008

#1

I have the following SQLCMD command which I am using to export data to a text file from a Microsoft SQL Server and then import the text file to a SQL Anywhere database.

SQL Query to create Insert statement
SET NOCOUNT ON SELECT CONCAT('INSERT INTO dba.proditem (opco_code, prodref, proddesc, prodcateg, prodsell, obsolete, prodcost, qtytype, qtydecimal, selldecimals, costdecimals) VALUES (''C'',''',a1.Part_Number,''',''',a1.description,''',''',a1.Product_category,''',',ROUND(a1.base_price,2)*100,',',0,',',round(a1.standard_cost,2)*100,',''D'',',2,',',2,',',2,');') FROM DVerto.dbo.Stock_Header as a1 LEFT JOIN dbo.Stock_Locations as a2 ON a1.Part_Number = a2.Part_Number WHERE ((a1.Product_Category = 11) OR (a1.Product_Category >= 50)) AND a1.discontinued_Y_or_N = 'N' AND a1.Restriction_Type = '00' AND a2.Loc_Created_Date >= GETDATE()-5 GROUP BY CONCAT('INSERT INTO dba.proditem (opco_code, prodref, proddesc, prodcateg, prodsell, obsolete, prodcost, qtytype, qtydecimal, selldecimals, costdecimals) VALUES (''C'',''',a1.Part_Number,''',''',a1.description,''',''',a1.Product_category,''',',ROUND(a1.base_price,2)*100,',',0,',',round(a1.standard_cost,2)*100,',''D'',',2,',',2,',',2,');')

SQLCMD Command
sqlcmd -S server\sqlexpress -i c:\query.sql -o c:\import.sql -h-1 -w 65535

The query creates an insert statement for the SQL Anywhere forum. When I try and import the import.sql file via DBISQL I am told that there is an illegal character on line 2, \n

When I view the file using notepad or view it in ISQL, everything appears fine, and it will run perfectly in ISQL, but if I try and use DBISQL I get the error.


#2

Can you post your query that creates the INSERT query?


#3

I have added this into the question.


#4

The GROUP BY looks very strange (I think it will have the same effect as SELECT DISTINCT without GROUP BY, which would be a lot easier to read.)


#5

Thanks. I have tweaked my SQL. Still getting the same error though.


#6

If you run the resulting command in SSMS,does it work? in SQLCMD? If not, can you post a sample of the query you get?

If so, and the error is only in the SQL Anywhere db, then you might want to ask in those forums.


#7

If I run the command in DBISQL (SQL Anywhere's version of SQLCMD).

This is the error I get https://campaigns.cmd-ltd.com/help/DBISQL.PNG

If I run this through Interactive SQL (similar to SSMS) it works fine.

I just would like the automation.


#8

OK -- then you're going to have to work with the DBISQL folks, I'm afraid


#9

Longshot: This isn't the difference between Unix linebreaks (\n) and Windows ones (\r\n) is it?


#10

Is there a way of seeing what line breaks are being used?


#11

Thanks. They have told me that there are no line breaks and the tool is generating a false error.