Msg 102, Level 15, State 1, Line 24 Incorrect syntax near '.'

Question

All... based on another question I've posted here recently, I built this stored procedure, but when executed, I receive the error in the title.

As you can see, I tried to remove any aliases, but it didn't matter. Not sure how to use PRINT to see the issue on this either. Of course the isolated SELECT statement works on its own.

I can even pull results from my temp table after the error appears. So is it a problem in this script or the results? Appreciate your assistance. I also only used the WHERE statement to limit the test results.

    ALTER PROC ap_vhdr_test AS
SET NOCOUNT ON
IF OBJECT_ID('temp.dbo.#ap_vend_det') is NOT NULL
DROP TABLE #ap_vend_det;

CREATE TABLE #ap_vend_det           
(db_name varchar(32)
, vendor_name varchar(40)
, vendor_code varchar(12)
, voucher_no varchar(16)
, invoice_num varchar(16)
, inv_date varchar(16)
, due_date varchar(16)
, apply_date varchar(16)
, total float
, line_desc varchar(40)
, company_id smallint
, gl_num varchar(32)
, acct_site varchar(32)
, sort_code varchar(32)
, nat_gl varchar(32)
, gl_desc varchar(40)
, category nvarchar(510)
, sub_category nvarchar(510)
, po_num varchar(16)
, vendor_class varchar(8)
)
INSERT INTO #ap_vend_det
EXEC sp_MSforeachdb N'IF ''?'' NOT IN ( ''model'',''tempdb'',''master'',''msdb'')
BEGIN SELECT DISTINCT db_name = ''?''
, amaster.addr1 --as vendor_name
, amaster.vendor_code --as vendor_code
, apdet.trx_ctrl_num --as voucher_no
, aphdr.doc_ctrl_num --as invoice_num
, CONVERT(varchar(16),dateadd(dd,(aphdr.date_doc - 639906),''1/1/1753''),101) --as inv_date 
, CONVERT(varchar(16),dateadd(dd,(aphdr.date_due - 639906),''1/1/1753''),101) --as due_date
, CONVERT(varchar(16),dateadd(dd,(aphdr.date_applied - 639906),''1/1/1753''),101) --as apply_date
, aphdr.amt_net --as total
, aphdr.doc_desc --as line_desc
, gldet.company_id --as company_id
, gldet.account_code --as gl_num
, gldet.seg2_code --as acct_site
, gldet.seg3_code --as sort_code
, gldet.seg1_code --as nat_gl
, gldet.description --as gl_desc
, ap_coa.group_header --as category
, ap_coa.group_label  --as sub_category
, apdet.po_ctrl_num --as po_num
, apvend.vend_class_code --as vendor_class
FROM ?.dbo.amaster --amaster
JOIN ?.dbo.aphdr --aphdr                        --**
    ON amaster.vendor_code = aphdr.vendor_code
    AND amaster.pay_to_code = aphdr.pay_to_code
JOIN ?.dbo.apdet --apdet
    ON aphdr.trx_ctrl_num = apdet.trx_ctrl_num
JOIN ?.dbo.gldet --gldet
    ON aphdr.journal_ctrl_num = gldet.journal_ctrl_num
JOIN ?.dbo.glt --glt
    ON gldet.journal_ctrl_num = glt.journal_ctrl_num
JOIN ?.dbo.apvend --apvend
    ON amaster.vendor_code = apvend.vendor_code
JOIN reps.dbo.ap_coa --ap_coa
    ON gldet.seg1_code = ap_coa.acct_code
WHERE aphdr.date_applied >= ''734785''              
END';

SELECT * FROM #ap_vend_det;

Answer 1

This will fail if you have database names that have spaces or other characters in them. You need to enclose them in square brackets, e.g.

FROM [?].dbo.amaster --amaster

Not only that, because you are using 3 part names, you also need to alias the tables in the FROM clause.

FROM [?].dbo.amaster amaster
JOIN [?].dbo.aphdr aphdr                        --**
    ON amaster.vendor_code = aphdr.vendor_code
    AND amaster.pay_to_code = aphdr.pay_to_code
JOIN [?].dbo.apdet apdet
    ON aphdr.trx_ctrl_num = apdet.trx_ctrl_num
JOIN [?].dbo.gldet gldet
    ON aphdr.journal_ctrl_num = gldet.journal_ctrl_num
JOIN [?].dbo.glt glt
    ON gldet.journal_ctrl_num = glt.journal_ctrl_num
JOIN [?].dbo.apvend apvend
    ON amaster.vendor_code = apvend.vendor_code
JOIN reps.dbo.ap_coa ap_coa
    ON gldet.seg1_code = ap_coa.acct_code
WHERE aphdr.date_applied >= ''734785''