Category Archives: MS SQL Stored Procedures

Entity Framework Function Import not returning Columns data

While working with Entity Framework (EF), sometimes the EF doesn’t recognize the columns returned by the Stored Procedure.


Reason

1) If the db procedure is using temporary table (e.g. #tempTable), EF don’t recognize the MetaData from the procedure.

2) If multiple select … statements exists in db procedure.


Solutions

Before executing procedure or beginning of db procedure set the FMTONLY to ON like below line:

SET FMTONLY ON

This will force EF to read the metadata from Temp tables


Disadvantage

There would be performance issues with approach because the way EF read the metadata from temp tables is to execute them multiple times.


Workaround

Instead of using the temp tables, create table variables and use them to store data temporarily. While using this option you don’t need to set the FMTONLY setting.


Performance

Using the table variables will be faster than FMTONLY option.


Find more detailed information at 

http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/


Thanks


Rename a table Column in MS SQL

How to rename a MS SQL server database table column name after the table creation??
You can rename a column regardless of it is null or contains any data.

We need to use a System stored procedure to rename. Any command with alter keywords won’t work.

Code
EXEC sp_rename ‘tableName.[oldColumnName]’, ‘newColumnName’, ‘COLUMN’

Example:
EXEC sp_rename ‘Ultra_tblPressRelease.[uShowStatus]’, ‘uShowInA2B’, ‘COLUMN’

The above line will rename the existing column named uShowStatus of table tblPressRelease to uShowInA2B.

There will be no adverse effect of this procedure on your table data.
But you have to update all your SQL Cursors and Stored Procedures with the new column name (if any).