CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-server-data-tools
adam0101
adam0101

Reputation: 30995

How do I refresh the columns of a view in SSDT?

I'm getting this error when trying to deploy a SQL Server Data Tools (SSDT) database project:

View or function 'dbo.Employees' has more column names specified than columns defined.

I deleted a column from the underlying table and the view looks like this: 

CREATE VIEW [dbo].[Employees] AS SELECT * FROM [$(ExternalDB)].[dbo].[Employees];

Doing a Google search brings back this page which says that SQL Server keeps meta data on views which must be refreshed. They mention this command:

EXEC sp_refreshview 'Employees';

If I put that in the pre-deployment script, it'll run before the column was dropped. If I put it in the post-deployment script, the deployment will throw the error before it gets executed. So my question is where or how can I do that with an SSDT project?

Upvotes: 1

Views: 1318

Answers (1)

Ed Elliott
Ed Elliott

Reputation: 6856

This is interesting as by default ssdt will refresh any views which depend on any table that has changed as part of a deployment.

Was the column dropped as part of a normal ssdt deployment?

In your publish profile or publish options are you setting ScriptRefreshModule to false?

Aside from this select * in a view is bad practice, put the full column list and this problem disappears, you can even right click the "select *" and choose to expand to get the full column list - do that instead :) Ed

Upvotes: 1

Related Questions