I'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( [at] daba_name)+']'+" in the first line of [at] 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 [at] db_row_counter AS INT
DECLARE [at] daba_name AS CHAR(50)
DECLARE [at] db_row_max AS INT
DECLARE [at] sql AS CHAR(1500)
SET [at] db_row_counter = 7
SELECT name, dbid FROM [master].[dbo].[sysdatabases]
SET [at] db_row_max = [at] [at] rowcount
start_point:
IF [at] db_row_counter <= [at] db_row_max
BEGIN
SET [at] 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 = [at] db_row_counter)
IF [at] daba_name like 'oncontact%' GOTO skip_defaults
IF [at] daba_name like 'TestVersion%' GOTO skip_defaults
IF [at] daba_name like 'Training%' GOTO skip_defaults
/* --Next Line is the problem-- */
SET [at] sql = 'SELECT TOP (100) PERCENT '+'['+rtrim( [at] daba_name)+']'+'
AS Project,
COUNT('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].activity_id) AS CountAct,
'+'['+rtrim( [at] daba_name)+']'+'.[dbo].[onca_user].full_name,
'+'['+rtrim( [at] daba_name)+']'+'.[dbo].[oncd_activity].action_c ode,
YEAR('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].creation_date) AS ActYear,
MONTH('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].creation_date) AS ActMonth
FROM '+'['+rtrim( [at] daba_name)+']'+'.[dbo].[oncd_activity]
LEFT OUTER JOIN '+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity_company] ON '+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].activity_id = '+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity_company].activity_id
LEFT OUTER JOIN '+'['+rtrim( [at] daba_name)+']'+'.[dbo].[onca_user] ON
'+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].created_by_user_code = '+'['+rtrim( [at] daba_name)+']'+'.
[dbo].[onca_user].user_code
/* - also interested in proper punctuation around the 2006... date
string on following line */
/* WHERE ('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].creation_date > CONVERT(DATETIME, '2006-01-01
00:00:00', 102))*/
GROUP BY '+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].action_code,
'+'['+rtrim( [at] daba_name)+']'+'.[dbo].[onca_user].full_name,
YEAR('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].creation_date),
MONTH('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].creation_date)
HAVING('+'['+rtrim( [at] daba_name)+']'+'.[dbo].
[oncd_activity].action_code IS NOT NULL)'
INSERT INTO [BroadReach].[dbo].[Activity_MasterII]
EXEC ( [at] sql)
skip_defaults:
SET [at] db_row_counter = [at] db_row_counter + 1
GOTO start_point
END
END
