toddw07


I am working with SQL Server 2005, here is what I am doing:

declare @cx as varchar(100)

set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

exec ('CREATE SYNONYM tblsynonym FOR ' + @cx + '..TableName')

---Procedure

drop synonym tblsynonym

The application i'm working on uses stored procedures that will at some point be called by more than one user at a time. At the start of the stored procedure the synonym is created and then it is dropped when the procedure completes, the issue is this: if two users access the same stored procedure at the same time then the first procedure will create the synonym and the second will fail because the syonym already exists.

Here is what I would like to do:

declare @cx as varchar(100)

declare @timestamp as datetime

set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

exec ('CREATE SYNONYM tblsynonym' + @timestamp + ' FOR ' + @cx + '..TableName')

---Procedure

drop synonym tblsynonym

Any ideas




Re: Is it possible to timestamp a synonym name to avoid conflicts

Arnie Rowland


I fail to understand why you need to use synonyms in this situation.

Could you give more information about what you are attempting to accomplish







Re: Is it possible to timestamp a synonym name to avoid conflicts

toddw07

There are multiple databases, different users belong to different databases, one of the databases contains tables that must be accessible from any of the other databases. Example:

Say I have 4 databases DB1, DB2, DB3 and DB4. DB 4 contains a table that has user rights information that is accessed to determine accessibility of data contained in DB 1, 2 and 3. When a user logs in they log in to DB 1, 2 or 3. In order to access the user rights table in DB4 the user must create a synonym to that table, otherwise if the stored procedure attempts to access the table in DB4 by name the table will not be found because SQL will search the database the user is currently logged in to, which is DB 1, 2 or 3.






Re: Is it possible to timestamp a synonym name to avoid conflicts

Mike Wachal - MSFT

This doesn't seem like the desired use of synonyms based on what I've read.

First off, why are you trying to control user rights by creating your own database to store these Wouldn't it be more appropriate to use SQL Servers built in security to manage user rights Why re-invent the wheel

I'm also not sure I buy that your stored procedure can access the tables in DB4 without a synonym as you describe. The fact that you are able to create a synonym in the first place proves you can access the table, you simply need to three or four part naming in your stored procedure to reference the tables in common database. (SELECT User, Permission FROM DB4.DBO.UserPermissions WHERE UserID = 24) Taking the extra step to create a synonym just seems like you're needlessly flogging the server.

Maybe I don't fully understand your solution, this is just what I see based on the information you've provided.

Mike






Re: Is it possible to timestamp a synonym name to avoid conflicts

toddw07

Mike,

I forgot about this post, thanks for your input, the reason we are using synonyms is because DB4 is going to be renamed by customers who purchase the server, therefore we can't hard code the path DB4..TableName.

I have found a solution for this issue since I originally posted so for the sake of anyone else who reads this here it is:

By using dynamic SQL I've created a scalar function with the following code:
(NOTE: Both the following stored procedure and function are not in DB4)

ALTER function [dbo].[GetDynSynName]
(
@TableName as varchar(100) = ''
)
returns varchar(200)
AS
BEGIN
declare @DySynName as varchar(200)
declare @intI as int

SELECT @DySynName = @TableName + '_' + system_user + '_' + CONVERT(char(12), GETDATE(), 14)

---- The following while loop strips off the colon
SELECT @intI = charindex(':',@DySynName)
WHILE @intI > 0
BEGIN
SELECT @DySynName = substring(@DySynName,1,@intI - 1) +
substring(@DySynName,@intI + 1,30-@intI )
SELECT @intI = charindex(':',@DySynName)
END

IF @TableName = '' BEGIN
SET @DySynName = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))
END

RETURN @DySynName

END


This generates a unique synonym name in the following format: TableName_UserName_Time
and then returns the name as a string to the calling procedure. Then in the procedure the synonym name is saved in a varchar variable which is then used to create, access, and delete the synonym.

DECLARE @SynName as varchar(200)
DECLARE @TblName as varchar(50)
DECLARE @cx as varchar(100)

SET @TblName = 'Table'
SET @SynName = dbo.GetDynSynName(@TblName)
SET @cx = dbo.GetDynSynName('')

EXEC ('CREATE SYNONYM ' + @SynName + ' FOR ' + @cx + '..' + @TblName)

EXEC ('SELECT * FROM ' + @SynName)

EXEC ('DROP SYNONYM ' + @SynName)






Re: Is it possible to timestamp a synonym name to avoid conflicts

Andrea Montanari

hi,

it's horrible, really horrible, but you can write

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.TestTB (id int);
GO
INSERT INTO dbo.TestTB VALUES (1);
GO
CREATE PROCEDURE dbo.uspXX
AS BEGIN
	declare @cx as varchar(128);
	declare @timestamp as varchar(23);
	set @timestamp = REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(
							CONVERT(varchar, GETDATE(), 121)
								, ':', '_')
							, '-', '_')
						, ' ', '_')
					, '.', '_');

	set @cx = db_name();

	SELECT @timestamp, @cx;
	exec ('CREATE SYNONYM tblsynonym' + @timestamp + ' FOR ' + @cx + '.dbo.TestTB')

	SET @cx = 'SELECT * FROM tblsynonym' + @timestamp ;
	EXEC (@cx);
	SET @cx = 'DROP SYNONYM tblsynonym' + @timestamp ;
	EXEC (@cx);
END;
GO
EXEC dbo.uspXX;
GO
DROP PROCEDURE dbo.uspXX;
DROP TABLE dbo.TestTB;
--<-------------
----------------------- --------
2007_04_19_13_06_34_340 tempdb

id
-----------
1

regards






Re: Is it possible to timestamp a synonym name to avoid conflicts

Andrea Montanari

oopss..

Mike and Arnie already stated that's "horrible" before i wrote that "horrible code"

regards