September 13, 2010 at 7:28 am
Hello guys
I was trying to run a script which calls sp_msforeachdb proc, but the system (2005 express edition) gave the following error message:
Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'sp_msforeachdb'.
If I run "select object_id('sp_msforeachdb')", it returns null. But "select object_id('sp_who')" does return a negative integer.
Also, the sp_msforeachdb does not exist in the sysobject table.
However, from the system stored procedure list in master datbase, I can still see this procedure there.
I tried to use its definition script generated from the "modify" option and modify/recreate this proc again, but only bumped into another error message:
for modify: alter proc [sys].[sp_MSforeachdb]
Msg 208, Level 16, State 6, Procedure sp_MSforeachdb, Line 46
Invalid object name 'sys.sp_MSforeachdb'.
for create: create proc [sys].[sp_MSforeachdb]
Msg 2760, Level 16, State 1, Procedure sp_MSforeachdb, Line 46
The specified schema name "sys" either does not exist or you do not have permission to use it.
BTW, I am in the sysadmin role on this server instance.
How can I receover this import system stored procedure then?
Thanks in advance for your help!
regards,
Ning
Bazinga!
September 14, 2010 at 7:35 am
You've tried sp_MSforeachdb, correct?
I had a similar problem and it was due to the fact that I was using a case sensitive collation.
After changing the name of the stored proc to the exact case, my query worked fine.
Also, I don't think you are allowed to edit that system stored procedure. Hence, the invalid object errors.
September 14, 2010 at 7:49 am
When I want to modify the system SP, I make a new version, with my own naming convention so as not to be confused with the systm version.
September 14, 2010 at 8:25 am
@nick-2 Tran
Yes, i did use both lower cased and proper cased versions of this stored proc.
Thanks for you reply. But my issue is that this system proc is not recognized by my T-SQL coammnd in the SSMS query window and it doesn't hold a record in the sysobject table.
I really wanna recreate it with the same name as it should be as a system stored proc.
Can you two or others provide more input to help?
Thanks again!
Bazinga!
September 14, 2010 at 10:24 am
Are you using a login that has DBO to master?
It sounds like you just can't see the Sys schema..
September 14, 2010 at 11:20 am
I am the sysadmin.
Bazinga!
September 14, 2010 at 12:49 pm
--create stored procedure sp_MSforeachdb
/*
* The following table definition will be created by SQLDMO at start of each connection.
* We don't create it here temporarily because we need it in Exec() or upgrade won't work.
*/
create proc sys.sp_MSforeachdb
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
set deadlock_priority low
/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
/* Create the select */
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
SELECT @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retval
October 4, 2010 at 2:20 am
I am using 2005 epress edition and this sp_msforeachdb actually exists at master.sys.sp_msforeachdb, strangely, when I run just 'sp_msforeachdb' the system doesn't recognize. But when I use 'master.sys.msforeachdb', this is OK. And i in both the above situations, I was using master database as the current one.
Bazinga!
February 9, 2012 at 6:45 am
Thanks man! I had forgotten about this! I was wondering why it was working on some servers but not others!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply