Strange error in TSQL 2005

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  July 18, 2011 at 12:50 am

  #240944

  Guys,

  I'm getting strange error when executing below TSQL script on server. It works fine on mine local machine.

  DECLARE @TranTbl TABLE(TranId INT)

  INSERT @TranTbl

  SELECT T.pk_TransactionId FROM tbl_MARC_Transactions T JOIN tbl_MARC_FlexiMap F

  ON T.fk_FlexiMapId=F.pk_FlexiMapId

  WHERE F.b_Active=1 AND T.dt_Date>CONVERT(SMALLDATETIME,CONVERT(VARCHAR,GETDATE(),101))

  AND T.b_Active=1 AND T.b_CurrActive=1 AND F.pk_FlexiMapId=@pk_FlexiMapId

  UPDATE R SET str_TableName='', i_ReservationStatus=1 FROM tbl_MARC_Reservation R, @TranTbl

  WHERE R.b_IsCancelled=0 AND dt_DiningTime>=GETDATE()

  AND TranId IN(SELECT * FROM dbo.SPLIT(R.str_TableName,1,0))

  Error is: Incorrect syntax near '.'

  Curious, what's going on here. Appreciate your help!

  Thanks in advance.

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  July 18, 2011 at 8:16 am

  #1354250

  For starters, you should definitely re-write that UPDATE statement.

  People will immediately harp on the fact that you are using the old-style joins, by using a comma to separate the tables. This is definitely a flaw, but the bigger flaw is the fact that you aren't associating the two tables together, meaning you're getting a full cross join between them.

  Unless this is intentional behavior, you should always make sure your JOIN conditions are present, to prevent the cross join from occurring.

  Beyond that, I expect the likely cause of the problem is within your function, or, more to the point, the data being passed to your function. You'll need to provide more for us to help though - table definitions, the definition of that function, and the data you're using would be required.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  July 22, 2011 at 5:29 am

  #1356954

  I can't help on the original problem because there's just not enough information to go on.

  However, I see that you have a Split function and most folks rendition of such a function leaves a whole lot to be desired in the area of performance. If you post the code for the Split function, I can check that out for you and recommend a very high performance alternative if a performance problem does exist in the code. It'll be a direct replacement, too.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 22, 2011 at 6:43 am

  #1357014

  "...on server. It works fine on mine local machine..." - do you mean you have installed a local server and it runs ok there? Are the functions dbo.split the same on each server?

  Investigate the data :

  SELECT r.str_TableName

  FROM tbl_MARC_Reservation r --, @TranTbl

  WHERE r.b_IsCancelled=0 AND dt_DiningTime>=GETDATE()

  ORDER BY str_TableName

  --AND TranId IN(SELECT * FROM dbo.SPLIT(R.str_TableName,1,0))

  Do this against both servers.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden