Select query returns wrong values...

• U.

  SSC Enthusiast

  Points: 105

  More actions

  October 24, 2008 at 7:28 am

  #200350

  Hi all,

  i have a problem with my application. It's designed to synchronize MySQL database with MSSQL database. However every now and then table CUSTOMERS is not updated as it should be. As i've seen from log file, fetched values of ID column (primary key) in MSSQL are not correct. For example, instead of values '00000', '000001', '00014', etc... the select statement returns values '0', '1', '14',... Basically all the zeros are wiped out. Data type of the column is nvarchar(10). The customer uses SQL Server 2000 Enterprise. However i didn't encounter this problem on my SQL Server 2005 with same (cloned) database.

  This is the sql:

  SELECT TOP 1000 ID FROM CUSTOMERS WHERE ID NOT IN (SELECT TOP 0 ID FROM CUSTOMERS ORDER BY ID ) ORDER BY ID

  Thanks for any help,

  U.

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 24, 2008 at 7:55 am

  #889231

  Mathematically, it is correct to show 1 instead of 00001 for the INTEGER data type

  Maybe you can explicitly select it as NVARCHAR

  if not, use REPLICATE to fill in the 0's

  SELECT TOP 1000 ID = CAST(ID AS NVARCHAR(10))

  FROM CUSTOMERS

  WHERE ID NOT IN (SELECT TOP 0 ID FROM CUSTOMERS ORDER BY ID ) ORDER BY ID

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• U.

  SSC Enthusiast

  Points: 105

  More actions

  October 24, 2008 at 12:24 pm

  #889416

  Hi Jerry, thank you for the quick answer.

  I'll try your suggestion and report back.

  U.

• U.

  SSC Enthusiast

  Points: 105

  More actions

  November 6, 2008 at 3:25 pm

  #895253

  Huh, i guess that solved the problem. The error doesn't show up anymore. Thank you again.