Here is my query:
IF OBJECT_ID('NPWAS1513.dbo.usp_MSPEX_QLK_Billing_Fact_Load') IS NOT NULL
DROP PROCEDURE dbo.usp_MSPEX_QLK_Billing_Fact_Load;
GO
CREATE PROCEDURE usp_MSPEX_QLK_Billing_Fact_Load
@create_timestamp datetime,
@update_timestamp datetime,
@create_user varchar(50),
@update_user varchar(50),
@dbProdServ varchar(50)
AS
print 'dbProdServ is:'+ @dbProdServ;
print 'current_user is:' +@current_user;
DECLARE @sSQL AS VARCHAR(MAX);
SET @sSQL = ''
SET @sSQL = 'set identity_insert ' + @dbProdServ + '.mspex_qlk_billing_fact ON'
EXEC(@sSQL);
SET @sSQL = 'INSERT INTO ' + @dbProdServ +'.mspex_qlk_billing_fact
(project_id,
billing_year,
billing_month,
billing_month_desc,
billing_date_id,
projected_bill_amount,
billed_year_to_date_amount,
billed_inception_to_date_amount,
remaining_bill_amount,
actual_billed_amount,
current_billing_percent,
previous_billing_percent,
billing_pct_diff,
billing_type,
final_bill_ind,
last_in_progress_date,
current_record_ind,
load_time_stamp,
total_contract_period,
contract_period_current_year,
partial_bill,
create_timestamp,
create_user,
update_timestamp,
update_user)
SELECT project_dim.project_id,
billing_final_data.billingyear,
billing_final_data.billingmonth,
billing_final_data.billingmonthdesc,
Time_Dim.Time_ID,
billing_final_data.projected_bill_amount,
billing_final_data.billed_year_todate_amount,
billing_final_data.billed_inception_todate_amount,
billing_final_data.remaining_bill_amount,
billing_final_data.actual_billed_amount,
billing_final_data.current_billing_percent,
billing_final_data.previous_billing_percent,
billing_final_data.billing_pct_diff,
billing_final_data.billing_type,
billing_final_data.final_bill_ind,
billing_final_data.last_in_progress_date,
billing_final_data.current_record_ind,
billing_final_data.load_time_stamp,
billing_final_data.[Total Contract Period],
billing_final_data.[Contract Period Current Year],
billing_final_data.[Partial Bill],'''+
CAST(@create_timestamp as varchar(max)) + ''',''' +
@create_user + ''','''+
CAST(@update_timestamp as varchar(50)) +''','''+
@update_user + '''
FROM '+
@dbProdServ +'.mspex_qlk_project_dim project_dim,'+
@dbProdServ +'.mspex_rpt_billing_final_data billing_final_data,'+
@dbProdServ + '.MSPEX_QLK_Time_Dim Time_Dim
WHERE
project_dim.myproject_project_uid = billing_final_data.projectuid
AND'''+ convert(datetime, cast(billing_final_data.[BillingMonth] as nvarchar(2)) + '''/01/''' + cast(billing_final_data.[billingyear] as nvarchar(4)), 101) +''' + = Time_Dim.Time_Date';
BEGIN TRANSACTION
EXEC(@sSQL)
COMMIT TRANSACTION
I get the error msg:
Msg 4104, Level 16, State 1, Procedure usp_MSPEX_QLK_Billing_Fact_Load, Line 23
The multi-part identifier "mspex_rpt_billing_final_data.BillingMonth" could not be bound.
Msg 4104, Level 16, State 1, Procedure usp_MSPEX_QLK_Billing_Fact_Load, Line 23
The multi-part identifier "billing_final_data.billingyear" could not be bound.
Msg 207, Level 16, State 1, Procedure usp_MSPEX_QLK_Billing_Fact_Load, Line 83
Invalid column name 'BillingMonth'.
Msg 207, Level 16, State 1, Procedure usp_MSPEX_QLK_Billing_Fact_Load, Line 84
Invalid column name 'billingyear'.
I checked the column names, etc. and things are fine. In fact, I directly dragged the table name and column name to ensure that it is correct.
Please help ASAP. Calling me at cell at 630-338-9427 would be great. But an URGENT response is absolutely necessary.
Thanks guys.
