Enabling xp_cmdshell in SQL Server

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

SSIS Deployment to SQL Server

SSIS Deployment to SQL Server


1) Go to SSIS project folder\bin\, double click the DeploymentManifest file
2) Choose “SQL Server deployment”.
3) Fill in the information then you are done.

SSIS - read excel column as text (string)

Case: when you use SSIS to input excel data, and one of the column contains number and letter like this:

[Column A]
700
700
800
800A

When you try to use SSIS to read the excel and import to MSSQL database, 800A will then become null value and you have no idea what the heck is going on...

To solve the problem:
In SSIS, click on your connection manager in connection manager panel. Look up ConnectionString in properties panel (on your right hand side) and fix your connection string like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Profiles\alanhui\Desktop\CMP Data Conversion\template\DataConversion-Step1.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;";

IMEX=1 is the key factor to solve this problem.

To learn more about this, you can go to the following website:
http://support.microsoft.com/kb/194124

Enjoy

SQL - How to reset identity increment value is MSSQL?

run the script below...BRAVO!

DBCC CHECKIDENT ( #table_name# ,RESEED, #number_starting_from#)

#table_name# = the table you wish to reset the identity increment
#number_starting_from# = the number you wish to reset to. For example, if you wish to start your new item at 1, then put in 0, like the example below:

DBCC CHECKIDENT ( my_table ,RESEED, 0)

Enjoy!

Enabling the table for SQL Cache Dependency

To enable the table for SQL Cache dependency

First go to c:\WINDOWS\Microsoft.NET\Framework\versionNumber
Then type in the following command:
aspnet_regsql -S [DB server] -U [user name] -P [password] -d [DB name] -ed -et -t [table]

Microsoft Visio

For Microsoft Visio

To show table column properties (like varchar(100)), click on Database, Option, Document, click on table tab, then check for Data Types - show physical.