Incorrect syntax near 'refresh_util'

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

Answer 1

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

Answer 2

This looks like Oracle PL/SQL.  Are you trying to migrate this to SQL Server?

David

---

David

My Blog

 

Answer 3

Yes Exactly!

---

Prashanth

Answer 4

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 [e-mail].