September 29, 2015 at 8:35 pm
Hello All,
I am trying to export data using SQLCMD. One of the columns in the table has the FLOAT data type with all the values for that column is 1.
when I select * from table in SSMS the result is correct.
Column_Name
------
1
1
1
However when I use SQLCMD it returns
Column_Name
-------
1.0
1.0
1.0
This only happens when I run SQLCMD on a SQL server 2014. I have tested it on 2008 R2 and it works fine.
I could use the convert or cast function but that would defeat the purpose of comparing data from 2 databases.
Also I am unable to change the data type for that column.
September 29, 2015 at 9:26 pm
You could try using BCP OUT with a format file...
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/
September 29, 2015 at 10:18 pm
I could do that but then I would have to create a new format file for every new table I compare.
The BCP command only lets me export data one table at a time. I use the sqlcmd as inputting the query from .sql file.
September 29, 2015 at 10:44 pm
You can format the output like
....sqlcmd ...
1> SELECT FORMAT(Column_Name,'0') FROM table
2> GO
...
1
1
1
D.Mincic
😀
MCTS Sql Server 2008, Database Development
September 29, 2015 at 10:59 pm
I was trying not to format the output as I wanted to compare the two tables. Its strange Im getting different results from the same sqlcmd on 2008 R2 and 2014.
I found a work around, I ran the query as output to text file in SSMS and it gives out the correct format for both versions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply