Why is "Invalid Object" message in Stored Procedure?
-
As you can see from the image, the IDE is flagging the database object, TaxTables as Invalid. Yet when I run the stored procedure, it completes successfully. HOWEVER, it does not accomplish what it is designed to do. So there are several layers to this question. I would like to take each of them one at a time.
1. Why is the IDE flagging the TaxTables database as Invalid?
I believe it must be something I have done as I did not write the stored procedure or the view that it uses. It runs of the server from which it was copied and accomplishes what it is supposed to do.
This stored procedure exists on another server, in a database with the same name, Data_02. This other server is running SQL 2005. I backed up the database on the other server. I copied the .bak file to the new server (Server 2012 with SQL 2008 R2). I restored the database. The backup and restore were done using SSMS. The view and stored procedure appears in the database on the New server. However, you can see that the IDE is not finding the TaxTable database.
Why is that?
What do I need to do to fix this?
There are no errors in the IDE on the other server when I view the stored procedure there.
Image showing stored procedure on new server:
After executing the stored procedure on the new server:
-
Edit -> Intellisense -> Refresh local cache (Ctrl-Shift-R)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gila,
Thank you. I didn't try that.
I did find an answer that worked for all of my questions. On the new server and newer version of SQL, I found that if I added Square brackets around the Table names AND put a space in front of the right hand bracket, everything worked. Intellisense recognized the database and table name. More importantly when I ran the SQL Agent job, the values which were supposed to be updated, were now actually updating. I do not understand why the change I made fixed everything. Thanks everyone for reading and replying.
inner join TaxTables.dbo.[MOZip ] t on (s.ZipCode = t.Zip_ZipCode)
inner join TaxTables.dbo.[MORate ] r on (t.Zip_SignatureCode = r.C_SignatureCode and s.City = r.C_CityName)
-
The problem was that your table names have spaces. That's the reason that you need the square brackets. If you don't change them soon enough it will be a nightmare to change the code that references them. If you leave them that way, you might have headaches when coding.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply