Alter table definition not reflected in SQL Server View

Few days ago, i got the chance to work with one of our legacy application. There was some changes in business requirement and i need to drop few columns from the table and so on and that table was used in one sql server view. After done my changes in table when i test my view, surprisingly it gives me an error! whooa!

Msg 4502, Level 16, State 1, Line 7
View or function ‘view_name’ has more column names specified than columns defined.

How is it possible as i have used “Select *” in my view. It should automatically reflect the changes in my view. Well, SQL server will not work in that way. After referring MSDN, i knew that metadata for my view is not automatically updated when i alter any dependent tables. So in today’s post we’ll see how we can resolve this type of error in SQL Server. Alright, let’s create this scenario.


CREATE TABLE [dbo].[EmployeeMaster](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](40) NOT NULL,
	[LastName] [varchar](40) NOT NULL,
	[JoiningDate] [date] NULL,
	[BloodGroup] [varchar](5) NULL,
	[PANNo] [varchar](20) NULL
 )
GO

INSERT INTO EmployeeMaster(FirstName,LastName,JoiningDate,BloodGroup)
	VALUES ('Azim', 'Premji', '2015-04-27', 'B+'),
			('Narayana', 'Moorthy', '2015-04-27', 'A+')
GO

Now let’s create the view.


CREATE VIEW view_Employee
AS
    SELECT * FROM  dbo.EmployeeMaster 

If you querying the view, everything works fine as expected. Now let us drop the “PANNo” column from the EmployeeMaster table.


ALTER TABLE EmployeeMaster
	DROP COLUMN PANNo

And let’s querying our view and see what happen.

ErrorInView

We get the error! Now to fix this error one of the way is alter our view or use the sp_refreshview stored procedure. So let’s see how we can fix the error using sp_refreshview SP.


EXEC SP_REFRESHVIEW view_Employee

Now again querying our view and as expected the error is gone now. Same way let say if you have added 2 new columns in your table then this time you won’t get any error when you querying your view but those 2 new columns not reflected in your view. So again to fix this issue you need to use “sp_refreshview” as shown above.

That’s it guys. Hope you like and enjoyed this post.

Advertisements

Execute a predefined tSql query stored in a column of table

Today’s post is inspired from the question asked here in the stackoverflow site. So the question is how to execute a predefined tsql query which is stored in a column of table and then display the result in one report. You can find the answer here i gave him. You might find it yourself in this type of situation in your application where you need to display the consolidated report over your other report in which you need to display the name of the report and its associated count or some other data.

Allright, let’s create this situation. For explanation i copied the data from that question. so here is the required table with some required records in it.


CREATE TABLE #tmpConsReport
(
	ReportName [varchar](100), 
	ReportQuery [varchar](2000)
);

INSERT INTO #tmpConsReport (ReportName, ReportQuery) 
	VALUES ('Sales Daily Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)'),
			    ('Sales Weekly Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) '),
				('HR Dept. Count', 'Select COUNT(*) FROM Users DepartmentID = 1'),
				('Todays New Joiny', 'Select TOP 1 firstname FROM Users ORDER BY EmpID DESC');

Now to execute the query which is stored in column of table we need to build dynamic query which looks something like this –


DECLARE @query VARCHAR(MAX) = ''

SELECT @query += 'SELECT ' + QUOTENAME(ReportName, '''') 
							+ ' AS ReportName, CAST((' + ReportQuery + ') AS VARCHAR) AS Result UNION ALL ' 
From #tmpConsReport

SET @query = LEFT(@query, LEN(@query) - 10)

PRINT(@query)
EXEC(@query)

If you don’t get any idea after seeing this query, no problem. Let’s print that dynamic query and it looks like this –


SELECT 'Sales Daily Report Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)) AS VARCHAR) AS Result 

UNION ALL 

SELECT 'Sales Weekly Report Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) ) AS VARCHAR) AS Result 

UNION ALL 

SELECT 'HR Dept. Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Users DepartmentID = 1) AS VARCHAR) AS Result 
			
UNION ALL 

SELECT 'Todays New Joiny' AS ReportName, 
         CAST((Select TOP 1 firstname FROM Users ORDER BY EmpID DESC) AS VARCHAR) AS Result

Hmmm Now looks better. But its easy right!!! Yes, using UNION ALL i have built the dynamic query and then execute it.

That’s it guys. Hope you like this post and learned something new today. Have a nice day.