Sp_RefreshView

Problem:

Sometimes metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Solution:

Since the metadata for the referenced table columns is persisted in the database, changes to the referenced objects will not get reflected until the view is refreshed using the system stored procedure sp_refreshview.

Syntax:

USE database_name

EXEC sp_refreshview [view_name]

GO

The following script refreshes the metadata for all the views that have dependencies on a changed object

SELECT DISTINCT 'EXEC sp_refreshview ''' + a.name + ''''

FROM sys.objects a INNER JOIN sys.sql_dependencies b

ON a.object_id = b.object_id

WHERE type = 'V'

AND b.referenced_major_id = object_id('tablename')

SQL Server Import and Export - Issue

When we import data from excel sheet to SQL Server tables, we come across some issues related to data type mismatch and constraints. Recently I have faced such issue with the error message saying,

Error:

"The value violated the integrity constraints for the column.” (SQL Server Import and Export Wizard)

Cause:

This error will come if the source table has not NULL column and the excel sheet column with NULL values.

In my case there is no NULL values in the excel sheets. The field has complete data, but it contains the data with string, integer, Boolean, Float and number data types

Workaround:

Sort the field by the type of the data it contains. For e.g. here the column 2 values have different types of data

Column 1	Column 2
FLOAT	2
STRING	MONTH
NUMBER	0
STRING	B01
BOOLEAN	TRUE
DATETIME	4/20/2011
NUMBER	3411

Sorting them in an order to make the data with the text come up on the top will solve the problem.  If you have thousands of records with the values of different format in a single column, you can just filter the values by the format and copy the results into a new sheet then try importing into the SQL server table. It will work