SP help

[robertfah]

I got some help a while ago from some of you on this board regarding a way to take data from multiple DB's and put into a temp table. Basically we have 35+ DB's, all the same tables and we want to list all the "Users" from each DB into 1 table. This works very well but I am looking to turn this into a Stored Procedure and am getting a few errors and hope you can help again.

Here's the SP:

ALTER PROCEDURE [dbo].[GetAllPOCsInAllDatabases]

AS

	IF object_id('tempdb..#Users') IS NOT NULL
		DROP TABLE #Users

	SELECT 'xxxx' AS DBName, 1 AS TempColumn, *
	INTO   #Users
	FROM   MyDB.dbo.Users
	WHERE  1=0

	UPDATE #Users
	SET DBName = null

	ALTER TABLE #Users DROP COLUMN UserId

	GO

	tempdb..sp_rename '#Users.TempColumn','UserId', 'COLUMN'

	DECLARE @DBName varchar(20)
	DECLARE @sSQL      varchar(2000)

	DECLARE LoopCursor CURSOR FOR
		SELECT 'SC_' + DBName AS 'DBName'
		FROM [SCMaster].dbo.Configuration c
		WHERE DBName NOT IN ('ABC', '123')
		ORDER BY 'SC_' + c.DBName

		OPEN LoopCursor

		FETCH NEXT FROM LoopCursor
		INTO @DBName

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @sSQL = '
				SELECT ''' + @DBName + ''', u.*
				FROM [' + @DBName + '].dbo.Users u

			INSERT INTO #Users 
			EXEC(@sSQL)

			FETCH NEXT FROM LoopCursor
			INTO @DBName
		END

		CLOSE LoopCursor
		DEALLOCATE LoopCursor

	SELECT * FROM #Users
	Order BY FirstName


GO

When I try to save it, I get this error:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 215
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
Msg 208, Level 16, State 0, Line 28
Invalid object name '#Users'.

Any help would be greatly appreciated!

 

[markros]

You have GO at the very top of the SP. GO is the batch separator in SSMS, so you need to remove it.

PluralSight Learning Library

 

[robertfah]

You have GO at the very top of the SP. GO is the batch separator in SSMS, so you need to remove it.

When I remove it, I get:

Msg 102, Level 15, State 1, Procedure GetAllPOCsInAllDatabases, Line 41
Incorrect syntax near 'tempdb'.

 

[markros]

I don't think you need to specify DB name for sp_rename system procedure. Also, please verify its syntax. In addition, I think you're doing something too complex. Why do you need to rename a column in the temp table? Why not create it correctly from the creation?

PluralSight Learning Library

 

[robertfah]

I believe the syntax is correct because this code runs fine in a new Query window and not in a SP. I'll give you a rundown of what I'm trying to accomplish and why the solution above was proposed.

We ahve 35+ DB's that are all identical For security reasons, we need to keep our customer's data seperate. We have an internal trouble ticket system that allows us to put tickets in for each customer. One requirement of this software is a complete list of POC's (users) from all our customers in 1 drop down list. This way the employee can choose a POC without having to know the actual Customer, location (that will all get auto populated but no need to get into that). So We have a master DB called SCMaster which holds relevant but not security specific information about our customers. Hence the 'Configuration' table. This is a list of ALL our Customers and their DB names. What we wanted was some SQL that went through our list of DB's and got the entire 'Users' table and combined them into 1 table. I didn't want to use the CREATE statement because there are times when our table structure changes and we didn't want to have to modify the SP (or even remember that we had to modify it) each time this occurred. The ALTER , DROP COLUMN statement is used because the UserID field is auto-incrementing and was throwing errors when trying to do it without it. So it was decided it was best to use it.

Here's a link to the thread:

http://www.tek-tips.com/viewthread.cfm?qid=1652663&page=3

 

[robertfah]

I figured out the issue....

this:

tempdb..sp_rename '#Users.TempColumn','UserId', 'COLUMN'

Needs to be this:

EXECUTE tempdb..sp_rename '#Users.TempColumn','UserId', 'COLUMN'

SP build properly and data is being displayed.