Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fabianus76
Posting Yak Master
191 Posts |
 Posted - 2009-02-28 : 18:14:42
|
| Hello all !I have this problem, which shouldn't exist :-)There is this query SET IDENTITY_INSERT Customer ON INSERT INTO Customer (ID, CLICOCL) SELECT SUBSTRING(DCOCOCL,2,99), DCOCOCL FROM Customer RIGHT OUTER JOIN RecPlusTemp ON Customer.CLICOCL COLLATE Latin1_General_CI_AI = RecPlusTemp.DCOCOCL where Customer.ID is null and the Select doesn't give any rows. But still the insert part seems to check for all possible results and throws an exception : Conversion failed when converting the varchar value 'ÜLLER' to data type int.In fact normally SUBSTRING(DCOCOCL,2,99) sends back an integer, but for some records, the substring isn't an integer. So as I said, the Select :SELECT SUBSTRING(DCOCOCL,2,99), DCOCOCL FROM Customer RIGHT OUTER JOIN RecPlusTemp ON Customer.CLICOCL COLLATE Latin1_General_CI_AI = RecPlusTemp.DCOCOCL where Customer.ID is null does not send ANYTHING in that perticular case, so there shouldn't be any conversion problem. But still the insert checks the thing ... Don't understand why. I hope I was clear engough! Thanks for any feedback !Regards, Fabianusmy favorit hoster is ASPnix : www.aspnix.com ! |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-02-28 : 23:46:40
|
| Is your column that you are using in substring is of int datatype? That might be the issue. |
 |
|
|
fabianus76
Posting Yak Master
191 Posts |
Posted - 2009-03-01 : 04:49:45
|
| Hello GhantaBro, thanks for your suggestion, but the column is of datatype varchar. I finally found out the syntax that works : SET IDENTITY_INSERT Customer ON INSERT INTO Customer (ID, CLICOCL) Select SUBSTRING(DCOCOCL,2,99), DCOCOCL FROM RecPlusTemp where DCOCOCL not in (SELECT CLICOCL collate Latin1_General_CI_AS FROM Customer)Don't know why, but it works.Regards, Fabianusmy favorit hoster is ASPnix : www.aspnix.com ! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-01 : 04:52:35
|
If this "SUBSTRING(DCOCOCL,2,99)" returns "üller" you can't insert that into an INT column.Is there a specific reason you force your own identity values?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-01 : 04:53:53
|
What you have done, is to check that value does not already exist in the table.But that should have given you the "DUPLICATE KEY" error, not a "CONVERSION" error.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
fabianus76
Posting Yak Master
191 Posts |
Posted - 2009-03-01 : 05:19:27
|
| Hello Peso, thanks for your feedback. You are right, I check that the value does not already exist, but in the first query I do the same (Customer.ID is null). The whole problem comes form the fact that I have ClientIDs (DCOCOCL or CLICOCL depending in which table I am) which are build lik this : F000000 (Where F is a letter and 0000 is a incremented number). But for very view clients the client ID is FFFFFF. What I did is to convert this Client ID into 00000 (droping the F). But as for some clients the client ID is FFFFF it throws an error because not able to convert. Now, these view clients I treated them in advance, added them by hand to the Customers table and gave them free IDs (00000). And now I simply want to add new clients (that are created in an old system and I translate them into the new system), and as the horrible old clients that have an ID of type FFFFF are in the old db (represented in the table RecPlusTemp), I don't have to try to insert them. The second query doesn't try, but for some reason that I do not understand, the first does try dispite the fact that I filter those which are already in the Customer table. Anyway, it's solved. Thank you guys for your kind support !Regards, Fabianusmy favorit hoster is ASPnix : www.aspnix.com ! |
|
|
|
|
|
|
|
|
conversion problem that should not happen ...