Need to some help with this error: Cannot find either column "T0" or the user defined function or aggregate "T0", or the name is ambiguous

• pauled2109

  Old Hand

  Points: 392

  More actions

  February 16, 2011 at 4:12 am

  #234547

  I have a stored procedure which needs to dump data from an xml file into a temporary table. This is the code:

  /*** temporary table for shredded XML records ***/

  DECLARE @Temp TABLE

  (

  orderid INT,

  cargoready SMALLDATETIME,

  estpallets INT,

  estweight INT,

  estvolume REAL,

  updguid VARCHAR(30),

  companyid INT,

  userid INT,

  dtupdated SMALLDATETIME

  )

  /*** parse XML file and dump into temp ***/

  INSERT INTO @Temp

  (orderid, cargoready, estpallets, estweight, estvolume, updguid, companyid, userid, dtupdated)

  SELECT

  T0.Item.value('@orderid', 'INT') orderid,

  T0.Item.value('@cargoready', 'SMALLDATETIME') cargoready,

  T0.Item.value('@estpallets', 'INT') estpallets,

  T0.Item.value('@estweight', 'INT') estweight,

  T0.Item.value('@estvolume', 'REAL') estvolume,

  T0.Item.value('@updguid', 'VARCHAR(30)') estvolume,

  T0.Item.value('@companyid', 'INT') companyid,

  T0.Item.value('@userid', 'INT') userid,

  @dateupdated dtupdated

  FROM @XMLCargo.nodes('/root/row') as T0(Item);

  This works fine everywhere EXCEPT my production server, where the script has the follwoing error message:

  Cannot find either column "T0" or the user defined function or aggregate "T0", or the name is ambiguous

  Any ideas, anyone?

  Thnaks for your help in advance

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  February 16, 2011 at 5:00 am

  #1287558

  What version is production? What's the compatibility level of the DB on there?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• pauled2109

  Old Hand

  Points: 392

  More actions

  February 16, 2011 at 5:30 am

  #1287575

  Hi

  It's a SQL server 2008 database, compatibility level = 80

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  February 16, 2011 at 5:45 am

  #1287583

  There's your problem. It;s set to compatibility for SQL 2000. Check the databases where it does work, my bet is the compat will be 90 or higher.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• pauled2109

  Old Hand

  Points: 392

  More actions

  February 16, 2011 at 5:55 am

  #1287587

  It doesn't make any difference, I've tried setting the compatibility level to 90 and 100 and the problem is still there

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  February 16, 2011 at 5:57 am

  #1287591

  Can you post a sample piece of XML?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• pauled2109

  Old Hand

  Points: 392

  More actions

  February 16, 2011 at 6:03 am

  #1287594

  Here's some sample XML:

  <root><row orderid='178661' cargoready='2010/11/20' estpallets='1' estweight='2' estvolume='3' updguid='WUaJK1_FTkmu29vJk361_A' companyid='7' userid='2'/><row orderid='190430' cargoready='' estpallets='3' estweight='2' estvolume='1' updguid='WUaJK1_FTkmu29vJk361_A' companyid='7' userid='2'/></root>

• pauled2109

  Old Hand

  Points: 392

  More actions

  February 16, 2011 at 8:19 am

  #1287709

  Hi

  Thanks for your help, but it appears that even with the scripting error message the stored procedure is actually working as expected.

  A cause for concern though.