Convert string to uniqueidentifier?
in [Programming SQL]
|
Prev: MonthName Year to YYYYMM Conversion
Next: Deploy script
From: John Bell on 4 Jun 2010 16:49 On Fri, 4 Jun 2010 08:20:02 -0500, "James" <james(a)klett.us> wrote: >Thanks, I executed your suggested query and all is well, no errors. So, the >implicit cast should work in the Import wizard, but doesn't > >thanks, > >JIM > >"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message >news:j58g06tp1tkfni24jsht53hhie0d0pt7hc(a)4ax.com... >> On Thu, 3 Jun 2010 10:18:35 -0500, "James" <james(a)klett.us> wrote: >> >>>I have a View with a varchar(36) which hold string guid's. I am trying to >>>import that data over to another db-table into a uniqueidentifier column. >>>The import wizard errors out : *** the column is called Customer on both >>>sides *** >>> >>>Error 0xc02020c5: Data Flow Task 1: Data conversion failed while >>>converting >>>column "Customer" (101) to column "Customer" (229). The conversion >>>returned >>>status value 2 and status text "The value could not be converted because >>>of >>>a potential loss of data.". >>> (SQL Server Import and Export Wizard) >>> >>>I thought that string guids and uniqueidentifiers were interchangeable? >>>In >>>the View I have tried CAST and CONVERT to make a uniqueidentifier out of >>>the >>>string guid, but Query builder complains and errors out: >>> >>> use of CONVERT function might be unnecessary, and then upon execute: >>> >>> SQL Execution Error. ... Error Source: .Net SqlClient Data Provider >>> Error Message: Conversion failed when converting from a character >>>string to uniqueidentifier. >>> >>>Thanks in advance >>> >>>JIM >> >> Hi JIM >> >> If the strings are all valid GUIDs then you can get an implicit >> conversion. There should not be any loss of data if it is 36 >> characters. This might be an issue with Query Builder. Try doing this >> in a query window and typing in the SQL. You could check that the >> column has valid GUIDs by doing a cast of the source data >> >> SELECT CAST(customer as uniqueidentifier) AS [GUID] >> FROM SourceTable >> >> John Hi JIM So if you now try doing an insert BEGIN TRANSASACTION INSERT DestinationTable ( Customer ) SELECT CAST(customer as uniqueidentifier) AS [GUID] FROM SourceTable ROLLBACK TRANSACTION GO It shouldn't give and error ! If you don't get and error you may want to see if SQL Profiler will indicate that is is failing where you think. If you insert other columns, of if you have triggers it is probably not the uniqueidentifier that is the problem. John John
From: Erland Sommarskog on 4 Jun 2010 18:05 James (james(a)klett.us) writes: > I have a View with a varchar(36) which hold string guid's. I am trying to > import that data over to another db-table into a uniqueidentifier column. > The import wizard errors out : *** the column is called Customer on both > sides *** > > Error 0xc02020c5: Data Flow Task 1: Data conversion failed while > converting column "Customer" (101) to column "Customer" (229). The > conversion returned status value 2 and status text "The value could not > be converted because of a potential loss of data.". > (SQL Server Import and Export Wizard) > > I thought that string guids and uniqueidentifiers were interchangeable? > In the View I have tried CAST and CONVERT to make a uniqueidentifier out > of the string guid, but Query builder complains and errors out: I was able to reproduce the problem, and to identify the cause. And I can offer a workaround. The error happens in the OLE DB prodivder. In OLE DB, you cannot convert a 36-char GUID to a binary GUID, but the GUID has to be enclosed in braces, like this: {A19CBBCF-9726-4228-8457-C09A33602671} The workaround is to instead use the .Net Provider for SQL Server which does not have this issue. You have to select upwards on the first page in the wizard to find it. Note that on the page that follows, you will need to specify server, login information and database (Initial Catalog). -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx
From: James on 7 Jun 2010 09:59
Erland, Thank you !!!!!!! I really wanted to know why the problem was occurring, not just a workaround. Thanks so much for the detailed explanation JIM "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D8EFDC604Yazorman(a)127.0.0.1... > James (james(a)klett.us) writes: >> I have a View with a varchar(36) which hold string guid's. I am trying >> to >> import that data over to another db-table into a uniqueidentifier column. >> The import wizard errors out : *** the column is called Customer on >> both >> sides *** >> >> Error 0xc02020c5: Data Flow Task 1: Data conversion failed while >> converting column "Customer" (101) to column "Customer" (229). The >> conversion returned status value 2 and status text "The value could not >> be converted because of a potential loss of data.". >> (SQL Server Import and Export Wizard) >> >> I thought that string guids and uniqueidentifiers were interchangeable? >> In the View I have tried CAST and CONVERT to make a uniqueidentifier out >> of the string guid, but Query builder complains and errors out: > > I was able to reproduce the problem, and to identify the cause. And I can > offer a workaround. > > The error happens in the OLE DB prodivder. In OLE DB, you cannot convert > a 36-char GUID to a binary GUID, but the GUID has to be enclosed in > braces, > like this: > > {A19CBBCF-9726-4228-8457-C09A33602671} > > The workaround is to instead use the .Net Provider for SQL Server > which does not have this issue. You have to select upwards on the first > page in the wizard to find it. Note that on the page that follows, > you will need to specify server, login information and database (Initial > Catalog). > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx > |