Unable to insert database name in sql string
am 03.04.2008 16:28:47 von Chris HI'm trying to create a procedure that will insert items from multiple
(identically structured) databases into a master database table. The
first field "Project" in the select string causes an error when the
procedure is executed (the procedure itself can be created withouth
error). If I hard code a value for "Project" (ie: ...1 AS Project...)
the query runs fine for all the other columns (but the results aren't
what I need). I think the issue is the punctuation around
"+'['+rtrim(@daba_name)+']'+" in the first line of @sql but don't know
how to correct. Any thoughts would be apprecitated!
I also have a WHERE clause that drops off older values by date that is
remmed out since I couldn't get that punctuation straight either.
Thanks, Chris.
The error generated when the procedure is run states:
....
Msg 207, Level 16, State 1, Line 1
Invalid column name 'BroadReach'.
....
This error repeats with the different database names.
Procedure:
....
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE
FROM [BroadReach].[dbo].[Activity_MasterII]
DECLARE @db_row_counter AS INT
DECLARE @daba_name AS CHAR(50)
DECLARE @db_row_max AS INT
DECLARE @sql AS CHAR(1500)
SET @db_row_counter = 7
SELECT name, dbid FROM [master].[dbo].[sysdatabases]
SET @db_row_max = @@rowcount
start_point:
IF @db_row_counter <= @db_row_max
BEGIN
SET @daba_name = (SELECT name
FROM (SELECT name,
ROW_NUMBER()OVER(ORDER BY dbid) AS db_row
FROM master.dbo.sysdatabases) AS names_from_master
WHERE db_row = @db_row_counter)
IF @daba_name like 'oncontact%' GOTO skip_defaults
IF @daba_name like 'TestVersion%' GOTO skip_defaults
IF @daba_name like 'Training%' GOTO skip_defaults
/* --Next Line is the problem-- */
SET @sql = 'SELECT TOP (100) PERCENT '+'['+rtrim(@daba_name)+']'+'
AS Project,
COUNT('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].activity_id) AS CountAct,
'+'['+rtrim(@daba_name)+']'+'.[dbo].[onca_user].full_name,
'+'['+rtrim(@daba_name)+']'+'.[dbo].[oncd_activity].action_c ode,
YEAR('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].creation_date) AS ActYear,
MONTH('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].creation_date) AS ActMonth
FROM '+'['+rtrim(@daba_name)+']'+'.[dbo].[oncd_activity]
LEFT OUTER JOIN '+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity_company] ON '+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].activity_id = '+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity_company].activity_id
LEFT OUTER JOIN '+'['+rtrim(@daba_name)+']'+'.[dbo].[onca_user] ON
'+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].created_by_user_code = '+'['+rtrim(@daba_name)+']'+'.
[dbo].[onca_user].user_code
/* - also interested in proper punctuation around the 2006... date
string on following line */
/* WHERE ('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].creation_date > CONVERT(DATETIME, '2006-01-01
00:00:00', 102))*/
GROUP BY '+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].action_code,
'+'['+rtrim(@daba_name)+']'+'.[dbo].[onca_user].full_name,
YEAR('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].creation_date),
MONTH('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].creation_date)
HAVING('+'['+rtrim(@daba_name)+']'+'.[dbo].
[oncd_activity].action_code IS NOT NULL)'
INSERT INTO [BroadReach].[dbo].[Activity_MasterII]
EXEC (@sql)
skip_defaults:
SET @db_row_counter = @db_row_counter + 1
GOTO start_point
END
END