0

I am using synonym in stored procedure (SP) to run select on the linked databases. The database name is calculated based on the input of the SP. The SP works fine most of the time, but if the same SP is called multiple times via a program, I get error as -

Invalid Object Name Synonym_Name

Restarting the application fixed the error - but this being triggered via a service call, it's not acceptable.

In starting of the SP, I have already taken care of checking if the object exists, and if yes drop it, then create it - 

IF EXISTS(select * from sys.synonyms s where name = 'Synonym_Name' )
BEGIN
    DROP SYNONYM [dbo].[Synonym_Name]
END

EXEC ('CREATE SYNONYM [dbo].[Synonym_Name]  FOR ' +  @DB  + '.[dbo].[TableName]')

How can I avoid this Invalid Object name error?

From what I read on all the forums, there is no specific ID and synonym can be deleted by anybody. Is there any other way of handling the situation as the table name is later referred at multiple locations - for inserting from select from the table, variable assignment etc.

I am not sure if this question is already covered - I did search and was not able to find any.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
user3525199
  • 11
  • 1
  • 2
  • Well, it is possible that if two users call the procedure at the same time, you'll have a race condition. Have you considered wrapping the check / drop / create in an explicit, serializable transaction? Have you ensured that the selects from the synonym are also handled in dynamic SQL, so that in cases where the synonym doesn't already exist, the parser doesn't barf on that before the create happens in another scope? Have you considered just using dynamic SQL instead of these temporary synonyms at all? – Aaron Bertrand May 30 '14 at 18:19
  • Could you post more SQL of how you're using the synonym later? If possible, an example 'call' from the program (is it just one program calling it multiple times, multiple programs calling it once?). There are a lot of variables here, including (as @AaronBertrand mentioned) transactions, dynamic SQL, transaction isolation level, application connections, and much more. As it's a specific error, a more specific picture of the scenario will help us answer. – brazilianldsjaguar May 30 '14 at 20:26
  • Thanks Aaron and brazilianldsjaguar for responding. Yes I have actually tried using dynamic SQL but the complexity increases too much because of the way I am using this dynamic DB reference. – user3525199 Jun 02 '14 at 14:46
  • Thanks Aaron and brazilianldsjaguar for responding. Yes I have actually tried using dynamic SQL but the complexity increases too much because of the way I am using this dynamic DB reference. there are 4 different tables that are being referred to the dynamic calculated DB server. Then these are used in inserting data in temp table / calculating value of a variable. I have not used transactions. Let me try that and see how it goes. Thanks Again. – user3525199 Jun 02 '14 at 14:52
  • Finally ended up using dynamic sql! – user3525199 Aug 26 '14 at 20:31

0 Answers0