Sql Task Editor in SSIS

• Mark_1820

  Old Hand

  Points: 315

  More actions

  January 31, 2012 at 10:54 am

  #254595

  Can anyone help me the code below. It used to execute perfectly fine in my DTS package on SQL SERVER 2000, but recreating this task in SSIS 2008 gives me errors.

  I verified the both the live and the temp table exist before it hits this code.

  IF (SELECT COUNT(1) FROM myLiveTable_TEMP) > 100000

  Begin

  DROP TABLE myLiveTable

  sp_rename myLiveTable_TEMP,myLiveTable

  end

  Error:

  Msg 102, Level 15, State 1, Line 4

  Incorrect syntax near 'sp_rename'.

  Any ideas?

  Thanks in advance.

• patrickmcginnis59

  SSCertifiable

  Points: 6483

  More actions

  January 31, 2012 at 11:04 am

  #1440837

  Mark-545947 (1/31/2012)

  ---

  Can anyone help me the code below. It used to execute perfectly fine in my DTS package on SQL SERVER 2000, but recreating this task in SSIS 2008 gives me errors.

  I verified the both the live and the temp table exist before it hits this code.

  IF (SELECT COUNT(1) FROM myLiveTable_TEMP) > 100000

  Begin

  DROP TABLE myLiveTable

  sp_rename myLiveTable_TEMP,myLiveTable

  end

  Error:

  Msg 102, Level 15, State 1, Line 4

  Incorrect syntax near 'sp_rename'.

  Any ideas?

  Thanks in advance.

  try putting the arguments inside singe quotes?

  IF (SELECT COUNT(1) FROM myLiveTable_TEMP) > 100000

  Begin

  DROP TABLE myLiveTable

  sp_rename 'myLiveTable_TEMP','myLiveTable'

  end

• Mark_1820

  Old Hand

  Points: 315

  More actions

  January 31, 2012 at 11:23 am

  #1440847

  No change. The same error.

  I get the same error executing it in SSMS.

• patrickmcginnis59

  SSCertifiable

  Points: 6483

  More actions

  January 31, 2012 at 11:39 am

  #1440860

  patrickmcginnis59 (1/31/2012)

  ---

  Mark-545947 (1/31/2012)

  ---

  Can anyone help me the code below. It used to execute perfectly fine in my DTS package on SQL SERVER 2000, but recreating this task in SSIS 2008 gives me errors.

  I verified the both the live and the temp table exist before it hits this code.

  IF (SELECT COUNT(1) FROM myLiveTable_TEMP) > 100000

  Begin

  DROP TABLE myLiveTable

  sp_rename myLiveTable_TEMP,myLiveTable

  end

  Error:

  Msg 102, Level 15, State 1, Line 4

  Incorrect syntax near 'sp_rename'.

  Any ideas?

  Thanks in advance.

  try putting the arguments inside singe quotes?

  IF (SELECT COUNT(1) FROM myLiveTable_TEMP) > 100000

  Begin

  DROP TABLE myLiveTable

  sp_rename 'myLiveTable_TEMP','myLiveTable'

  end

  Wierd, I got the same thing. When I changed

  sp_rename 'myLiveTable_TEMP','myLiveTable'

  to

  exec sp_rename 'myLiveTable_TEMP','myLiveTable'

  it worked ok. I'd initially thought that the bare invokation would be ok, but then again what do I know LOL

  Interestingly enough, looking back at the jobs I had that used sp_rename, quotes weren't needed either, 2000 level db though, and I DID use the 'EXEC' keyword in those procedures.

• Mark_1820

  Old Hand

  Points: 315

  More actions

  January 31, 2012 at 12:13 pm

  #1440889

  The exec sp.. worked like a charm!

  I removed the quotes around the tables, so I guess bare invokation was OK after all.

  Thanks a lot.:-)

• patrickmcginnis59

  SSCertifiable

  Points: 6483

  More actions

  January 31, 2012 at 12:17 pm

  #1440899

  glad it worked!