Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cannot find the object

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-17 : 06:53:17
Hey Guys

This is query below, and i am keep receiving the following error message and i don’t know why ,


“Msg 4701, Level 16, State 1, Line 4
Cannot find the object "#502" because it does not exist or you do not have permissions.”

Would appreciate any help possible , regards


-- Selecting Internal & External Where confirmation = 'y'---
--if exists
truncate table #502



CREATE UNIQUE NONCLUSTERED INDEX [pk] ON #502
(
[FDMSAccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


insert into #502
([FDMSAccountNo]
,[External_ID])


select
[FDMSAccountNo]
,[External_ID]
from [FDMS].[dbo].[stg_LMPAB502]
where Client_Conf ='y'




--- Insert into xref Link ---
insert into #502
([FDMSAccountNo]
,[External_ID])

--- Selecting x ref table--
Select
[FDMSAccountNo]
,left([ExternalAccountNo],15)
from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]
where [Correspondence_Flag] ='y'


--External Mid join --
SELECT
[Account_ID]
,[FDMSAccountNo]
Into #Update
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_External],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[External_ID],18)

--select * from #Update
insert into #Update
([Account_ID]
,[FDMSAccountNo])

--Internal Mid join --
SELECT
[Account_ID]
,[FDMSAccountNo]
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18)

--select distinct* from #update


Select distinct
Account_id,
#Update.FDMSAccountNo,
External_ID
into #sf
from #Update
inner join #502 on #502.FDMSAccountNo = #Update.FDMSAccountNo


select * from #Update
--where FDMSAccountNo = '878202467881'



----- Upload File -----
---need to write sql query ----

------------------- Kick Back File eg Incorrect Mids -------------------------

--SELECT
-- [#sf].*, SalesForce.DailyAccounts.ID,
--SalesForce.DailyAccounts.Account_ID,
--SalesForce.DailyAccounts.Account_Name,
--SalesForce.DailyAccounts.MID_External,
--SalesForce.DailyAccounts.MID_Internal
--FROM [#sf] RIGHT OUTER JOIN
-- SalesForce.DailyAccounts ON [#sf].account_id = SalesForce.DailyAccounts.Account_ID
-- where #sf.account_id is null
-- and (MID_External is not null and MID_Internal is not null)







--- linking Address to correct Sf account id ---



SELECT [FDMSAccountNo]
,right(substring(Parentsfid,0,14),12)as Parentsfid
,[MM3-DBA-NAME] as [DBA Name]
,[LEGAL-NAME] as [Legal Name]
,[MM3-DBA-ADDR1] as 'Street'
,[MM3-DBA-ADDR2]
,[MM3-DBA-ADDR4]
,[MM3-DBA-CITY] as 'City'
,[MM3-DBA-COUNTY] as 'County'
,[MM3-DBA-POSTAL-ZIP] as 'Postalcode'
,[MM3-DBA-COUNTRY]as 'Country'
,[MM3-DBA-TELEPHONE]as 'Phone'
--,[INSTALLATION_DATE],
,left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[INSTALLATION_DATE],112)),126),16)as Open_Date,
CASE WHEN LEFT (CANCELLATION_DATE , 4) >= '1970' AND LEFT (CANCELLATION_DATE , 4) < '9999' AND CANCELLATION_DATE <> '20' AND CANCELLATION_DATE <> '20000000' THEN CONVERT(varchar(20),convert(date,CANCELLATION_DATE,109))+'T23:01' end as Cancel_Date,
CASE WHEN LEFT (last_posting_date , 4) >= '1980' AND LEFT (last_posting_date , 4) < '9999' AND last_posting_date <> '20' AND last_posting_date <> '20000000' THEN CONVERT(varchar(20),convert(date,last_posting_date,109))+'T23:01' end as Last_Post_Date,
CASE WHEN LEFT (First_Post_Date , 4) >= '1980' AND LEFT (First_Post_Date , 4) < '9999' AND First_Post_Date <> '20' AND First_Post_Date <> '20000000' THEN CONVERT(varchar(20),convert(date,First_Post_Date,109))+'T23:01' end as First_Post_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime2,[CANCELLATION_DATE],112)),126),16)as Cancel_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[LAST_POSTING_DATE],112)),126),16)as First_Post_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[FIRST_POST_DATE],112)),126),16) as Last_Post_Date,
Case when [FDMSAccountNo]IS NOT NULL THEN '00530000007Yscj' ELSE '00530000007Yscj' END [BoS Owner],
Case when [FDMSAccountNo]IS NOT NULL THEN '01230000000LUxt' ELSE '01230000000LUxt' END [RecordTypeId]
Into #Address
FROM [FDMS].[dbo].[stg_LMPAB501]


--- Building ParentSf Hierarchy --

inner join (

--- Hierarchy Table Content--

--SELECT [FDMSAccountNo]
--,[MM-CHN-AGENT]
--,[MM-CHN-CORP]
--,[MM-CHN-CHAIN]
--FROM [FDMS].[dbo].[stg_LMPAB501]

--- Hierarchy --

--Outlet to chain --
select distinct [MM-CHN-CHAIN] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CHAIN] <> '000000000000'
and FDMSAccountNo <> [MM-CHN-CHAIN]


-- Outlet to Corp--
union all
select distinct [MM-CHN-CORP] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000'
and [MM-CHN-CHAIN] = '000000000000'
and FDMSAccountNo <> [MM-CHN-CORP]


-- Outlet to Agent --
union all
select distinct[MM-CHN-AGENT] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] = '000000000000'
and [MM-CHN-CHAIN] = '000000000000'
and FDMSAccountNo <> [MM-CHN-AGENT]

-- Chain to Corp--
union all
select distinct [MM-CHN-CORP] as Parentsfid
,[MM-CHN-CHAIN] as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000'
and [MM-CHN-CHAIN] <> '000000000000'

-- Chain to agent--
union all
select distinct [MM-CHN-AGENT] as Parentsfid
,[MM-CHN-CHAIN] as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CHAIN] <> '000000000000'
and [MM-CHN-CORP] = '000000000000'

-- Corp to agent --
union all
select Distinct[MM-CHN-AGENT] as Parentsfid
,[MM-CHN-CORP] as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000')x
on x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]


group by
[FDMSAccountNo],
Parentsfid,
[MM3-DBA-NAME] ,
[LEGAL-NAME],
[MM3-DBA-ADDR1],
[MM3-DBA-ADDR2],
[MM3-DBA-ADDR4],
[MM3-DBA-CITY] ,
[MM3-DBA-COUNTY] ,
[MM3-DBA-POSTAL-ZIP] ,
[MM3-DBA-COUNTRY],
[MM3-DBA-TELEPHONE],
[INSTALLATION_DATE],
[CANCELLATION_DATE],
LAST_POSTING_DATE,
FIRST_POST_DATE



-----
select distinct *
from #Address
inner join #Update on #Address.FDMSAccountNo = #Update.FDMSAccountNo
order by Open_Date desc


-- Getting the parent_Id--
Select distinct
#Address.FDMSAccountNo,
#Address.[DBA Name],
#Address.[Legal Name],
#Address.Street,
#Address.[MM3-DBA-ADDR2],
#Address.[MM3-DBA-ADDR4],
#Address.City,
#Address.County,
#Address.Postalcode,
#Address.Country,
#Address.Phone,
#Address.Open_Date,
#Address.Cancel_Date,
#Address.Last_Post_Date,
#Address.[BoS Owner],
#Address.RecordTypeId,
u.FDMSAccountNo,
#Address.Parentsfid,
u.account_id,
u1.account_id as parentid
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno


----There are duplicate mids in the table above --
-- need to develop code below to counter this --
-- Finding Duplicates Mids on fdms account no Num--


--SELECT *
--From #mason
--fdmsaccountno, COUNT(*) TotalCount

--FROM #accounts
--GROUP BY ExternalAccountNo
--HAVING COUNT(*) > 0
--ORDER BY COUNT(*) DESC





--drop table #Address,#Update,#sf





visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 06:57:27
where are you creating #502 ?
As of now first statement I see is a trucate so if you dont have any statements before it it wont work as table is not created yet

Also please keep in mind that # tables have scope only within connection and will get automatically destroyed once connection terminates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-17 : 07:31:30
HI Visakh16

Thank you for your swift response

Re-looking at the code it doesnt look like have created table #501
Where do you suggest creating the #501 table ?
NB : I have never used the trucate function before, and it was suggested by another anlayst , and its confused me with my query
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 09:13:59
it should be on top

create table #502
(
...
)

-- Selecting Internal & External Where confirmation = 'y'---
--if exists
truncate table #502



CREATE UNIQUE NONCLUSTERED INDEX [pk] ON #502
(
[FDMSAccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


insert into #502
([FDMSAccountNo]
,[External_ID])


select
[FDMSAccountNo]
,[External_ID]
from [FDMS].[dbo].[stg_LMPAB502]
where Client_Conf ='y'




--- Insert into xref Link ---
insert into #502
([FDMSAccountNo]
,[External_ID])

--- Selecting x ref table--
Select
[FDMSAccountNo]
,left([ExternalAccountNo],15)
from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]
where [Correspondence_Flag] ='y'


--External Mid join --
SELECT
[Account_ID]
,[FDMSAccountNo]
Into #Update
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_External],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[External_ID],18)

--select * from #Update
insert into #Update
([Account_ID]
,[FDMSAccountNo])

--Internal Mid join --
SELECT
[Account_ID]
,[FDMSAccountNo]
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18)

--select distinct* from #update


Select distinct
Account_id,
#Update.FDMSAccountNo,
External_ID
into #sf
from #Update
inner join #502 on #502.FDMSAccountNo = #Update.FDMSAccountNo


select * from #Update
--where FDMSAccountNo = '878202467881'



----- Upload File -----
---need to write sql query ----

------------------- Kick Back File eg Incorrect Mids -------------------------

--SELECT
-- [#sf].*, SalesForce.DailyAccounts.ID,
--SalesForce.DailyAccounts.Account_ID,
--SalesForce.DailyAccounts.Account_Name,
--SalesForce.DailyAccounts.MID_External,
--SalesForce.DailyAccounts.MID_Internal
--FROM [#sf] RIGHT OUTER JOIN
-- SalesForce.DailyAccounts ON [#sf].account_id = SalesForce.DailyAccounts.Account_ID
-- where #sf.account_id is null
-- and (MID_External is not null and MID_Internal is not null)







--- linking Address to correct Sf account id ---



SELECT [FDMSAccountNo]
,right(substring(Parentsfid,0,14),12)as Parentsfid
,[MM3-DBA-NAME] as [DBA Name]
,[LEGAL-NAME] as [Legal Name]
,[MM3-DBA-ADDR1] as 'Street'
,[MM3-DBA-ADDR2]
,[MM3-DBA-ADDR4]
,[MM3-DBA-CITY] as 'City'
,[MM3-DBA-COUNTY] as 'County'
,[MM3-DBA-POSTAL-ZIP] as 'Postalcode'
,[MM3-DBA-COUNTRY]as 'Country'
,[MM3-DBA-TELEPHONE]as 'Phone'
--,[INSTALLATION_DATE],
,left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[INSTALLATION_DATE],112)),126),16)as Open_Date,
CASE WHEN LEFT (CANCELLATION_DATE , 4) >= '1970' AND LEFT (CANCELLATION_DATE , 4) < '9999' AND CANCELLATION_DATE <> '20' AND CANCELLATION_DATE <> '20000000' THEN CONVERT(varchar(20),convert(date,CANCELLATION_DATE,109))+'T23:01' end as Cancel_Date,
CASE WHEN LEFT (last_posting_date , 4) >= '1980' AND LEFT (last_posting_date , 4) < '9999' AND last_posting_date <> '20' AND last_posting_date <> '20000000' THEN CONVERT(varchar(20),convert(date,last_posting_date,109))+'T23:01' end as Last_Post_Date,
CASE WHEN LEFT (First_Post_Date , 4) >= '1980' AND LEFT (First_Post_Date , 4) < '9999' AND First_Post_Date <> '20' AND First_Post_Date <> '20000000' THEN CONVERT(varchar(20),convert(date,First_Post_Date,109))+'T23:01' end as First_Post_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime2,[CANCELLATION_DATE],112)),126),16)as Cancel_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[LAST_POSTING_DATE],112)),126),16)as First_Post_Date,
--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[FIRST_POST_DATE],112)),126),16) as Last_Post_Date,
Case when [FDMSAccountNo]IS NOT NULL THEN '00530000007Yscj' ELSE '00530000007Yscj' END [BoS Owner],
Case when [FDMSAccountNo]IS NOT NULL THEN '01230000000LUxt' ELSE '01230000000LUxt' END [RecordTypeId]
Into #Address
FROM [FDMS].[dbo].[stg_LMPAB501]


--- Building ParentSf Hierarchy --

inner join (

--- Hierarchy Table Content--

--SELECT [FDMSAccountNo]
--,[MM-CHN-AGENT]
--,[MM-CHN-CORP]
--,[MM-CHN-CHAIN]
--FROM [FDMS].[dbo].[stg_LMPAB501]

--- Hierarchy --

--Outlet to chain --
select distinct [MM-CHN-CHAIN] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CHAIN] <> '000000000000'
and FDMSAccountNo <> [MM-CHN-CHAIN]


-- Outlet to Corp--
union all
select distinct [MM-CHN-CORP] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000'
and [MM-CHN-CHAIN] = '000000000000'
and FDMSAccountNo <> [MM-CHN-CORP]


-- Outlet to Agent --
union all
select distinct[MM-CHN-AGENT] as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] = '000000000000'
and [MM-CHN-CHAIN] = '000000000000'
and FDMSAccountNo <> [MM-CHN-AGENT]

-- Chain to Corp--
union all
select distinct [MM-CHN-CORP] as Parentsfid
,[MM-CHN-CHAIN] as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000'
and [MM-CHN-CHAIN] <> '000000000000'

-- Chain to agent--
union all
select distinct [MM-CHN-AGENT] as Parentsfid
,[MM-CHN-CHAIN] as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CHAIN] <> '000000000000'
and [MM-CHN-CORP] = '000000000000'

-- Corp to agent --
union all
select Distinct[MM-CHN-AGENT] as Parentsfid
,[MM-CHN-CORP] as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where [MM-CHN-CORP] <> '000000000000')x
on x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]


group by
[FDMSAccountNo],
Parentsfid,
[MM3-DBA-NAME] ,
[LEGAL-NAME],
[MM3-DBA-ADDR1],
[MM3-DBA-ADDR2],
[MM3-DBA-ADDR4],
[MM3-DBA-CITY] ,
[MM3-DBA-COUNTY] ,
[MM3-DBA-POSTAL-ZIP] ,
[MM3-DBA-COUNTRY],
[MM3-DBA-TELEPHONE],
[INSTALLATION_DATE],
[CANCELLATION_DATE],
LAST_POSTING_DATE,
FIRST_POST_DATE



-----
select distinct *
from #Address
inner join #Update on #Address.FDMSAccountNo = #Update.FDMSAccountNo
order by Open_Date desc


-- Getting the parent_Id--
Select distinct
#Address.FDMSAccountNo,
#Address.[DBA Name],
#Address.[Legal Name],
#Address.Street,
#Address.[MM3-DBA-ADDR2],
#Address.[MM3-DBA-ADDR4],
#Address.City,
#Address.County,
#Address.Postalcode,
#Address.Country,
#Address.Phone,
#Address.Open_Date,
#Address.Cancel_Date,
#Address.Last_Post_Date,
#Address.[BoS Owner],
#Address.RecordTypeId,
u.FDMSAccountNo,
#Address.Parentsfid,
u.account_id,
u1.account_id as parentid
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno


----There are duplicate mids in the table above --
-- need to develop code below to counter this --
-- Finding Duplicates Mids on fdms account no Num--


--SELECT *
--From #mason
--fdmsaccountno, COUNT(*) TotalCount

--FROM #accounts
--GROUP BY ExternalAccountNo
--HAVING COUNT(*) > 0
--ORDER BY COUNT(*) DESC





--drop table #Address,#Update,#sf


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -