locked
Incorrect syntax near 'refresh_util' RRS feed

  • Question

  • set @stmt= 'create or replace  synonym '+isnull('q_sym_object', '') +' for '+ isnull('q_new_object', '');
    			PRINT  '...'+isnull(stmt, '');
    			execute sp_executesql @stmt;
    						
    			-- ...redefine synonym pointing to the non-active object.
    			set @stmt= 'create or replace  synonym '+ isnull('q_sym_nact_object', '') +' for '+ isnull('q_cur_object', '');
    			PRINT  '...'+isnull(stmt, '');
    			execute sp_executesql @stmt;
    			
    									
    			set @log_comment = 'SUCCESS';
    		   PRINT  'Refresh of: '+ isnull(q_new_object, '') +' failed.';
    			set @log_comment = 'FAILURE - if in between semesters, this could be alright - or maybe the record count has changed;';
    		end
    		
    		refresh_util.log_refresh_action( p_obj_owner, p_object, q_new_object, q_cur_object, rec_cnt, job_started, log_comment );
    
    		
    		RAISERROR(N'
    		when others then
    		   rollback;
    		   print  ISNULL(SQLERRM, '') +": "+ ISNULL(SQLERRM, '');
    		   refresh_util.log_refresh_action( p_obj_owner, p_object, q_new_object, q_cur_object, rec_cnt, job_started,
    		    RAISERROR (ISNULL(SQLCODE, " ") "+"isnull(substring(SQLERRM, 1,200), '') );'
    		   	
    	end refresh_object;

     ERRORS:

    refresh_util it says Incorrect syntax near 'refresh_util';

    p_obj_owner- Error is Incorrect syntax near'p_obj_owner'.Expecting '(', or SELECT

    end- Error saying Incorrect syntax near 'end'.Expecting ','.

    'refresh_object'-Error saying Incorrect syntax near 'refresh_object'.Expecting CONVERSATION.

    Kindly help me solve this issue.

    Thanks



    Prashanth


    Tuesday, November 1, 2016 4:36 PM

Answers

  • Hi 1991Prashanth,

    In your case, you may take a look at Control-of-Flow Language (Transact-SQL) first. In SQL Server, because there is no ALTER SYNONYM statement, you have to drop the synonym, and then re-create the synonym with the same name, but point the synonym to the new location. See more at https://msdn.microsoft.com/en-us/library/ms187552.aspx.

    IF EXISTS (SELECT * FROM sys.synonyms WHERE name = N'synonym_name' AND schema_id = SCHEMA_ID(N'dbo'))
    	DROP SYNONYM [dbo].[synonym_name]

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Riaon Wednesday, November 23, 2016 9:15 AM
    • Marked as answer by Kalman Toth Monday, December 19, 2016 3:03 PM
    Tuesday, November 22, 2016 5:19 AM

All replies

  • I have no clue what you mean by "end refresh_object'" and SQL Server doesn't either.

    What do you think that statement should do?


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, November 1, 2016 4:46 PM
  • This looks like Oracle PL/SQL.  Are you trying to migrate this to SQL Server?

    David


    David

    My Blog

     

    Tuesday, November 1, 2016 5:37 PM
  • Yes Exactly!

    Prashanth

    Monday, November 21, 2016 10:27 PM
  • Hi 1991Prashanth,

    In your case, you may take a look at Control-of-Flow Language (Transact-SQL) first. In SQL Server, because there is no ALTER SYNONYM statement, you have to drop the synonym, and then re-create the synonym with the same name, but point the synonym to the new location. See more at https://msdn.microsoft.com/en-us/library/ms187552.aspx.

    IF EXISTS (SELECT * FROM sys.synonyms WHERE name = N'synonym_name' AND schema_id = SCHEMA_ID(N'dbo'))
    	DROP SYNONYM [dbo].[synonym_name]

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Riaon Wednesday, November 23, 2016 9:15 AM
    • Marked as answer by Kalman Toth Monday, December 19, 2016 3:03 PM
    Tuesday, November 22, 2016 5:19 AM