October 28, 2013 at 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
October 28, 2013 at 7:26 am
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/
October 28, 2013 at 7:28 am
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
October 28, 2013 at 7:29 am
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.
October 28, 2013 at 7:54 am
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.
October 28, 2013 at 8:49 am
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].
October 28, 2013 at 8:52 am
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
October 28, 2013 at 9:02 am
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 !
December 11, 2017 at 4:11 am
vba317 - Monday, October 28, 2013 7:22 AMI 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply