Could not locate entry in sysdatabases for databas

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

SQL Server 2000 Forums

Transact-SQL (2000)

Could not locate entry in sysdatabases for databas

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-12 : 03:55:43

hi there
I have SP as

CREATE PROCEDURE [dbo].[sp_BCPBackup](@DataBase varchar(20))
AS
set nocount on
declare @id int, @sSysObj varchar(100)
create table #a (name varchar(128), id int identity)
SET @sSysObj = 'insert #a (name) select name from ' + @DataBase + '..sysobjects where xtype =''U'''
EXEC @sSysObj
drop table #a

the SP is in MASTER database & compile ok.

When i execute this SP as (Connected to MASTER or other Database)
sp_BCPBackup 'HSE0607'
I got following error

Msg 911, Level 16, State 1, Procedure sp_BCPBackup, Line 8
Could not locate entry in sysdatabases for database 'insert #a (name) select name from HSE0607'. No entry found with that name. Make sure that the name is entered correctly.

I too check the
SELECT * FROM sysdatabases --connect to MASTER
and also
sp_helpdb
it return the database detail(OK)

I just want to write this stored procedure, which should be Executable in all the Databases & #a contain the table of the database, which send as a perameter in the SP.

Regards,
Thanks.
Gurpreet S. Gill

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 04:03:11

Simple and common mistake !!

When you execute a statement using EXEC, you should wrap it inside parenthesis like this:

EXEC(@sSysObj)

Only SPs/functions can be executed without parenthesis.

Harsh Athalye
India.
"Nothing is Impossible"

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-12 : 04:07:18

Harsh Athalye
Thanks...Thanks...Thanks...
I sorry to say i am really new to this SPs, i use to write the SQLz only
A man reads form its mistakes
Regards,
Thanks again
Gurpreet S. Gill

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 04:10:46

Nothing to be sorry about!

We all learn from our mistakes.

Harsh Athalye
India.
"Nothing is Impossible"

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-12 : 05:13:26

ok right, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources