Red Gate forums :: View topic - Script fails as sp references column that no longer exists
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Script fails as sp references column that no longer exists

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
prw



Joined: 12 Jun 2012
Posts: 2

PostPosted: Thu Jan 24, 2013 5:23 pm    Post subject: Script fails as sp references column that no longer exists Reply with quote

Hi,

During our build process we have created a stored procedure that inserts data into a table in another database. This was created and run successfully. Since then a column was dropped from this table and the sp. updated to reflect this.

Using SQLCompare to produce the deployment script it successfully scripts the creation of the sp when it was first created followed by a script that performs an ALTER statement to change the sp.
During deployment the script fails at the part where the sp is first created. This is because the column referenced in the table no longer exists, and so the sp cannot be created.

How can this be overcome during the deployment process. I have checked previous forum posts but can't find anything. I would have thought this was a common occurrence so I am overlooking a very simple option ?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Thu Jan 24, 2013 6:40 pm    Post subject: Reply with quote

This is a SQL Server limitation. It is not possible to create a procedure that references a column that doesn't exist. Interestingly SQL Server lets you create a procedure that references a table that doesn't exist!

Why can't you simply drop the procedure since it is now "invalid" and can't run anyway?

(SQL Prompt has a "find invalid objects" feature that will help you track these down)
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group