How to avoid this message : 'Database name 'tempdb' ignored, referencing object in tempdb.'

Question

How to avoid this message : 'Database name 'tempdb' ignored, referencing object in tempdb.'

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 21, 2016 at 11:08 am

#321591

Hi,
How to avoid this message? I tried to fix this but no luck.
----------------------- Warning ---------------
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
-------------------------------------------------------------
SET NOCOUNT on
IF OBJECT_ID('tempdb..#members_roles') IS NOT NULL
--IF 0 < OBJECT_ID('tempdb..#members_roles')
DROP TABLE tempdb..#members_roles
DECLARE @cmd AS NVARCHAR(MAX) = N'';
DECLARE @login_name AS NVARCHAR(1000) = 'XYC';
DECLARE @RoleName AS NVARCHAR(1000)
CREATE TABLE tempdb..#members_roles
(
RoleName NVARCHAR(1000),
MemberName NVARCHAR(4000),
)
INSERT INTO tempdb..#members_roles(RoleName, MemberName)
SELECT
--a.role_principal_id,
role.name AS RoleName,
--a.member_principal_id,
member.name AS MemberName
FROM sys.server_role_members a
JOIN sys.server_principals AS role
ON a.role_principal_id = role.principal_id
JOIN sys.server_principals AS member
ON a.member_principal_id = member.principal_id
WHERE member.name = @login_name;
SELECT * FROM #members_roles
declare c cursor for
SELECT RoleName FROM tempdb..#members_roles
open c
fetch next from c into @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RoleName
--SELECT @cmd = @cmd + '
SET @cmd ='
ALTER SERVER ROLE '+@RoleName+' DROP MEMBER '+ QUOTENAME(LTRIM(RTRIM(@login_name))) +';'
SELECT @cmd
--exec sp_executesql @cmd
EXEC(@cmd);
fetch next from c into @RoleName
END
close c
deallocate c
SET NOCOUNT OFF
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7686 More actions · Answer 1

I would replace this: INSERT INTO tempdb..#members_roles(RoleName, MemberName)

with INSERT INTO tempdb..members_roles(RoleName, MemberName) - taking out the # because it is not needed but implicitly implied. Also take it out of the select at the bottom. See if you still get this error.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

When referencing a temporary table, there's no need to specify that tempdb is the database.

You'll get that message anytime you attempt to do so (so all the references to tempdb..#members_roles).

You can either live with the message since it's benign, or just stop specifying the database for temporary tables.

Cheers!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

RVSC48 (9/21/2016)
I would replace this: INSERT INTO tempdb..#members_roles(RoleName, MemberName)
with INSERT INTO tempdb..members_roles(RoleName, MemberName) - taking out the # because it is not needed but implicitly implied. Also take it out of the select at the bottom. See if you still get this error.

Careful there. Those are two very different things. With the '#' it creates a local (to that session) temporary table. Without the '#' that will create an ordinary "permanent" table in tempdb that is visible to all sessions.

If the latter is what is intended, that's fine. If his intention is to create a temporary table, not an ordinary table in tempdb, then it's actually the other way around. Remove the tempdb specification and just use #members_roles.

Cheers!

rvsc48 SSCertifiable Points: 7686 More actions · Answer 4

rvsc48

SSCertifiable

Points: 7686

September 21, 2016 at 11:37 am

#1902251

Thanks Jacob, yeah, depends on what his needs are.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

I tried removing #temp..but getting this error.

Msg 2714, Level 16, State 6, Line 10

There is already an object named '#members_roles' in the database.

Thanks.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

That would likely be because your drop did not get triggered.

If you are not executing in the context of tempdb, then scoping the object reference to tempdb is actually required for the OBJECT_ID not to return NULL when passed a temp table name.

EDIT: For clarity, basically you'll need the tempdb specification in OBJECT_ID, but nowhere else.

Cheers!