STORED PROCEDURE ERROR

Question

Post reply

STORED PROCEDURE ERROR

eseosaoregie

SSCertifiable

Points: 6889
More actions
March 30, 2010 at 8:35 am

#142219

I am currently trying to create a stored procedure as part of an ETL process that I am working on. I use a temporary table within the stroed procedure. When I try and create the procedure i get the following error:
Server: msg 208, Level 16, State 1, Line 3
Invalid object name '#F1'
This is the script for the procedure
CREATE PROCEDURE sp_LoadLog_Fact
AS
CREATE TABLE #F1 (
[Date] VARCHAR (80) ,
[Application] [varchar] (6),
[Database] [varchar] (8),
[User] [varchar] (8)
)
GO
INSERT INTO #F1([Date],[Application],[Database],[User])
(
SELECT
SUBSTRING(LineRead,2,24)
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead)+1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)-1 - CHARINDEX('/',LineRead))
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)-1
- CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1))
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1) +1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)+1)- 1 -
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1) +1))
FROM dbo.stg_LogFiles
WHERE CHARINDEX('[',LineRead) = 1
AND NOT SUBSTRING(LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)
,2) = '//' -- ONLY SELECT records where user id is present
)
INSERT INTO [ESSBASE_TEST_DW].[dbo].[fct_Logs]([LogDate], [Application], [Database], [User], [Retrieval])
(select
convert(datetime,substring([Date],5,7)+ right([Date],4) + substring([Date],11,9),108),
A.ApplicationID,
A.DatabaseID,
U.UserID,
1
FROM #f1 B
LEFT JOIN dbo.dimApplication A ON A.Application = B.Application
LEFT JOIN dbo.dimUser U ON U.UserName = B.[User]
)
DROP TABLE #F1
Can someone point out where I am going wrong

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Get rid of the 'GO' after the table creation.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

Thanks!! Very silly from me. I have been staring at this thing for 40 mins.

Cheers

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Sometimes it's the little things ;-).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 4

Hi,

Use can use this statement to stop occuring of temp table creation/deletion

select * from tempdb.dbo.sysobjects where name=<Table Name>

Paul White SSC Guru Points: 150467 More actions · Answer 5

Trouble Shooter (4/9/2010)
Hi,
Use can use this statement to stop occuring of temp table creation/deletion
select * from tempdb.dbo.sysobjects where name=<Table Name>

Irrelevant nonsense.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 6

Hi,

How can u say it irrelevant .

don`t be in over confidence and smart Mr. SSCertifiable

Paul White SSC Guru Points: 150467 More actions · Answer 7

Trouble Shooter (4/12/2010)
How can u say it irrelevant .
don`t be in over confidence and smart Mr. SSCertifiable

The name is Paul. And 'you' is spelt with three letters.

Your post:

"Use can use this statement to stop occuring of temp table creation/deletion

select * from tempdb.dbo.sysobjects where name=<Table Name>"

...makes absolutely no sense at all. Can you explain how your SELECT statement stops the creation or deletion of temporary tables? Or what that has to do with the question in post #1?

No? That's why I said it was irrelevant nonsense. It was irrelevant and nonsense. See? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 8

Hi,

select * from tempdb.dbo.sysobjects where name=<Table Name>"

That statement will check the existance of temp table in temp database if it is exists in temp db it will not create the temp table else it will create.

With the use of that select statement ,u can can rid of that error

"Invalid object name '#F1'"

Paul White SSC Guru Points: 150467 More actions · Answer 9

Trouble Shooter (4/12/2010)
select * from tempdb.dbo.sysobjects where name=<Table Name>"
That statement will check the existance of temp table in temp database if it is exists in temp db it will not create the temp table else it will create.

1. There is no such object: tempdb.dbo.sysobjects. Either you mean tempdb.sys.objects, or the tempdb.sys.sysobjects compatibility view.

2. Even if you got the name of the object correct, it would never return a row for a temporary table, since the full name of the table in tempdb is not #F1 (for example) it is a system generated name like #temp_______________________________________________________________________________________________________________000000000020.

3. It would match with any object with the name given, not necessarily a table. Perhaps you meant tempdb.sys.tables?

With the use of that select statement ,u can can rid of that error
"Invalid object name '#F1'"

4. By the time you posted, the answer had already been given: there was an unintentional 'GO' batch separator which ended the stored procedure definition too soon.

Have a nice day 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi