Error: Could not locate entry in sysdatabases for database dbo

Question

Error: Could not locate entry in sysdatabases for database dbo

vba317

SSC Eights!

Points: 838
More actions
October 28, 2013 at 7:22 am

#299663

I get this error when I am running one of my jobs. If I run the steps manually everything works well.
This is step 3 in a process. It is running a stored procedure called exec dbo.sproc.Infmtx_003_MonthlyAR.
After reviewing the different sql books I have I have seen examples of detaching and reattaching databases. If this is what I need to do how do I do it?
Tom

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

vba317 (10/28/2013)
I get this error when I am running one of my jobs. If I run the steps manually everything works well.
This is step 3 in a process. It is running a stored procedure called exec dbo.sproc.Infmtx_003_MonthlyAR.
After reviewing the different sql books I have I have seen examples of detaching and reattaching databases. If this is what I need to do how do I do it?
Tom

Not much in the way of details here but I suspect you have 3 part naming for this sproc that is incorrect. 3 part naming is [Database].[Schema].[ObjectName]

Probably you just need 2 part naming for this. [schema].[procName]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

exec dbo.sproc.Infmtx_003_MonthlyAR.

And there is your problem, by specifying 3-part naming, you've said the database name is dbo, the schema is sproc and the procedure name is Infmtx_003_MonthlyAR. Since you're getting errors, that's obviously not the case, so specify the correct database and schema names for the procedure

Didn't you post exactly this problem about a month ago and get an answer then?

Edit, yes, you did. Thread posted on 25/9/2013 http://www.sqlservercentral.com/Forums/Topic1498396-1291-1.aspx

Your last post from that thread:

vba317 (9/25/2013)
Thanks for your patience. I thought I had copied and pasted to avoid errors but I was wrong. The command needs to be exec dbo.sproc_Infmtx_003_MonthlyAR with an underscore after sproc not a . Thanks a million!!

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 3

I'm not sure how this is even working when you run it manually. A 4 part object name is ServerName.DatabaseName.Schema.Object so in your example dbo is in the database name section. That is why you are getting the error when you run it as a job.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

vba317 SSC Eights! Points: 838 More actions · Answer 4

A thousand apologies. I have not had a chance to fix it yet. I am fixing it today using the previous posting from last month. I am processing right now.

vba317 SSC Eights! Points: 838 More actions · Answer 5

I changed the procedure to the following: exec sproc.Infmtx_003_MonthlyAR. I now get the error Error could not find the stored procedure 'sproc.Infmtx_003_MonthlyAR' [SQLSTATE 42000] [Error 2812].

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Is there a schema called sproc and does it have a procedure named Infmtx_003_MonthlyAR?

According to what you said a month ago, it does not. I will re-quote

vba317 (9/25/2013)
Thanks for your patience. I thought I had copied and pasted to avoid errors but I was wrong. The command needs to be exec dbo.sproc_Infmtx_003_MonthlyAR with an underscore after sproc not a . Thanks a million!!

Check the database, check the schema, see what the actual name of the procedure is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vba317 SSC Eights! Points: 838 More actions · Answer 7

For some reason I had to do the change twice in order for the system to save the change from a period to a dash. Once again thanks for your patience. The job is currently running past the point it was giving me an error for. So thanks !

varunchopra.apj Grasshopper Points: 14 More actions · Answer 8

vba317 - Monday, October 28, 2013 7:22 AM
I get this error when I am running one of my jobs. If I run the steps manually everything works well. This is step 3 in a process. It is running a stored procedure called exec dbo.sproc.Infmtx_003_MonthlyAR.After reviewing the different sql books I have I have seen examples of detaching and reattaching databases. If this is what I need to do how do I do it?Tom

Hi All

I faced the same issue.

We need to use Square brackets for database name

ie

use [database]

instead of

use database

Hope it helps.