Tuesday, January 31, 2012

SSIS Error: Error: ForEach Variable Mapping number n to variable "User:: n1 " cannot be applied.

The exact error I got was
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of  variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable  User::i_HospitalCode  in the variable declaration window and it worked!

Friday, January 13, 2012

Restore database

use master
GO

Alter
Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE


GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO

Alter
Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC
sp_updatestats

I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE




Then after successfully restoring it I changed it to the multi user mode.
GO

Friday, January 6, 2012

Guidelines for index-fragmetnation


Some starting points to keep in mind for index fragmentation
  •  If an index has less than 1000 pages and is in memory, don't bother removing fragmentation
  • if the index has:
    • less than 5% logical fragmentation, don't do anything
    • between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)
    • more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)
 The guidelines are taken from 
http://sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx