LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » BCP issue with uniqueidentifier, SQL Server 2000 to 2005

BCP issue with uniqueidentifier, SQL Server 2000 to 2005

Options

Previous Topic · Next Topic

andrej351

Posted: Monday, April 27, 2009 11:42:36 PM

Rank: Newbie

Joined: 4/27/2009
Posts: 3
Points: 9
Where do you live?: Australia

Hey everyone,

I'm having an issue trying to bulk export some data out of a SQL Server 2000 database to a text file, then import it into a SQL Server 2005 database.

My problem is i have a number of unique identifier columns which are all being exported WITHOUT the 4 dashes ie. 39A6EE1CE8CC4CA58B026969B9E45DB0 instead of 39A6EE1C-E8CC-4CA5-8B02-6969B9E45DB0. As a result they won't import straight into a uniqueidentifier column in my 2005 DB.

If i manually put in the 4 dashes IT IMPORTS FINE.

Does anyone have any idea why my export would be ommitting the dashes??

Any idea how i could import uniqueidentifiers that are valid aside from having no dashes in them??

More Details....

My export is a cmd line bcp out that looks like this: bcp table out file -c -T -S server

My import is a T-SQL script that looks like this:

BULK INSERT db.dbo.table
FROM 'C:\datafile.dat'
WITH
(
DATAFILETYPE = 'char',
KEEPIDENTITY,
TABLOCK
)

Scott Whigham

Posted: Tuesday, April 28, 2009 3:53:42 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

I think you're going to have to use a format file with this one. And make sure you use a SQLCHAR with a length of 36 or 37 (I can't remember which ATM); something akin to:

Code:

9.0
19
1 SQLCHAR 0 37 "\t" 1 MyGui
2 SQLCHAR 0 128 "\t" 2 FirstName
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 128 "\t" 3 LastName

andrej351

Posted: Wednesday, April 29, 2009 5:58:07 PM

Rank: Newbie

Joined: 4/27/2009
Posts: 3
Points: 9
Where do you live?: Australia

Hey thanks for the reply,

It turns out that the SQL 2000 database is storing the unique identifiers as varchars without the dashes, hence why they're being exported that way. The 2000 DB is our clients internal DB and therefore i had limited access to it and couldnt simply check the data type. Im guessing they've done this because of the current front end application to this database.

So my solution is to import the IDs into char(32) columns, allowing me to set up any required PKs and FKs needed.

Alternatively, i could've chained together a bunch of SUBSTRING function calls to manually insert the dashes as they are always in the same place. This could've bin nested inside a 'bcp substringSelectQuery queryout filename' bulk export.

Case closed.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.