Answered by:
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
- Edited by 1991Prashanth Tuesday, November 1, 2016 4:38 PM
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 -
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