Convert string to uniqueidentifier?
in [Programming SQL]
|
Prev: MonthName Year to YYYYMM Conversion
Next: Deploy script
From: James on 3 Jun 2010 11:18 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
From: Erland Sommarskog on 3 Jun 2010 17:39 James (james(a)klett.us) writes: > 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. I would guess that you have data in that table which are not legal guids. This query should spot them: SELECT ... FROM tbl WHERE col NOT LIKE replicate('[0-9A-F]', 8) + '-' + replicate('[0-9A-F]', 4) + '-' + replicate('[0-9A-F]', 4) + '-' + replicate('[0-9A-F]', 4) + '-' + replicate('[0-9A-F]', 12) -- 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: John Bell on 3 Jun 2010 17:44 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
From: James on 4 Jun 2010 09:20 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
From: James on 4 Jun 2010 09:21
Your query executed perfectly, no results. So, I am still at a loss as to why it wont work with the Import wizard thanks, JIM "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D8CF0B152F6DYazorman(a)127.0.0.1... > James (james(a)klett.us) writes: >> 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. > > I would guess that you have data in that table which are not legal guids. > > This query should spot them: > > > SELECT ... > FROM tbl > WHERE col NOT LIKE replicate('[0-9A-F]', 8) + '-' + > replicate('[0-9A-F]', 4) + '-' + > replicate('[0-9A-F]', 4) + '-' + > replicate('[0-9A-F]', 4) + '-' + > replicate('[0-9A-F]', 12) > > > > -- > 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 > |