osql import inserting carriage returns?

[bobdurie]

I'm trying to run an import of some data that has unix style carriage
returns throughout (ie, 0x0A, or \n). When i use osql to import the
data, it shows up in the SQL Server 2005 database as 0x0D 0x0A or \r
\n.

I do not want this behaviour - the import file is very large and has
ddl at the beginning. Can anyone suggest a more suitable utility or
method for importing this data that will preserve the original
format? Or perhaps my database is not configured correctly?

Thanks,
Bob

[Roy Harvey (SQL Server MVP)]

On Wed, 15 Oct 2008 09:03:57 -0700 (PDT), "bo******@gmail .com"
<bo******@gmail .comwrote:

>I'm trying to run an import of some data that has unix style carriage
returns throughout (ie, 0x0A, or \n). When i use osql to import the
data, it shows up in the SQL Server 2005 database as 0x0D 0x0A or \r
\n.

I do not want this behaviour - the import file is very large and has
ddl at the beginning. Can anyone suggest a more suitable utility or
method for importing this data that will preserve the original
format? Or perhaps my database is not configured correctly?

I haven't had to work with that type of data that I can recall, but I
do wonder how you use OSQL to import data. BCP, DTS, SSIS, BULK
INSERT sure, but not OSQL, which is for executing SQL commands.

Roy Harvey
Beacon Falls, CT

[Plamen Ratchev]

The BCP utility is a good choice to import data and provides flexibility
to use format files:
http://msdn.microsoft.com/en-us/libr...2(SQL.90).aspx
http://msdn.microsoft.com/en-us/libr...5(SQL.90).aspx

--
Plamen Ratchev
http://www.SQLStudio.com

[bobdurie]

On Oct 15, 12:10*pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@sne t.netwrote:

On Wed, 15 Oct 2008 09:03:57 -0700 (PDT), "bobdu...@gmail .com"

<bobdu...@gmail .comwrote:

I'm trying to run an import of some data that has unix style carriage
returns throughout (ie, 0x0A, or \n). *When i use osql to import the
data, it shows up in the SQL Server 2005 database as 0x0D 0x0A or \r
\n.

I do not want this behaviour - the import file is very large and has
ddl at the beginning. *Can anyone suggest a more suitable utility or
method for importing this data that will preserve the original
format? *Or perhaps my database is not configured correctly?

I haven't had to work with that type of data that I can recall, but I
do wonder how you use OSQL to import data. *BCP, DTS, SSIS, BULK
INSERT sure, but not OSQL, which is for executing SQL commands.

Roy Harvey
Beacon Falls, CT

Hi Roy,

The .sql file has all the "create table" and "insert into" commands.
I just run the command like this:

c:\osql -U sa -P blahblah -d mydb -i C:\myimportfile .sql

All the data imports fine, except the simply 0x0A's in the data - they
appear in the db as 0D0A's.

If i look at the .sql file before import in a hexeditor there is
clearly no 0D's in the data itself.

Bob

[bobdurie]

On Oct 15, 12:14*pm, Plamen Ratchev <Pla...@SQLStud io.comwrote:

The BCP utility is a good choice to import data and provides flexibility
to use format files:http://msdn.microsoft.com/en-us/libr...5(SQL.90).aspx

--
Plamen Ratchevhttp://www.SQLStudio.c om

Hi Plamen,

BCP does look like a great tool, but my output format of ".sql" with
complete INSERT and CREATE TABLE statements doesn't seem like its
compatible with bcp. Please correct me if i'm wrong.

Thanks,
Bob

[Roy Harvey (SQL Server MVP)]

On Wed, 15 Oct 2008 11:11:29 -0700 (PDT), "bo******@gmail .com"
<bo******@gmail .comwrote:

>BCP does look like a great tool, but my output format of ".sql" with
complete INSERT and CREATE TABLE statements doesn't seem like its
compatible with bcp. Please correct me if i'm wrong.

No, BCP won't do that. Of course Plamen responded before it was clear
what you were doing.

Roy Harvey
Beacon Falls, CT

[Plamen Ratchev]

BCP will not work to execute scripts, it is for raw data import. Since
you are on SQL Server 2005 you can try the SQLCMD utility:
http://msdn.microsoft.com/en-us/library/ms170572.aspx
http://www.dbazine.com/sql/sql-articles/cook15

--
Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

bo******@gmail. com (bo******@gmail .com) writes:

I'm trying to run an import of some data that has unix style carriage
returns throughout (ie, 0x0A, or \n). When i use osql to import the
data, it shows up in the SQL Server 2005 database as 0x0D 0x0A or \r
\n.

I do not want this behaviour - the import file is very large and has
ddl at the beginning. Can anyone suggest a more suitable utility or
method for importing this data that will preserve the original
format? Or perhaps my database is not configured correctly?

Try using SQLCMD instead. It does not seem to add the CR.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx