SQL Server

SSIS: Can not execute dataflow in Visual Studio

Categories: Database; Tagged with: SQL • SQL Server • SSIS; @ September 6th, 2012 19:24

This happened when we just edit the dtsx file.
If you want execute the flow in VS directly, create one Integration services project first, then you can copy your dtsx and pase it into the “SSIS Packages” in the Solution Explorer:

then you can execute the ssis dataflow directly in VS.

SQL Server: Execute SQL Agent job from T-SQL

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 5th, 2012 19:02

Run SQL Agent job by this:

EXEC msdb.dbo.sp_start_job 'testJob';

Seems sp_start_job is asynchronous, here is a test:

-- Invoke SQL Agent job from T-SQL PRINT CONVERT(VARCHAR(24),GETDATE(),113) EXEC msdb.dbo.sp_start_job 'testJob'; PRINT CONVERT(VARCHAR(24),GETDATE(),113)

The results:
05 Sep 2012 18:55:11:747
Job ‘testJob’ started successfully.
05 Sep 2012 18:55:11:753

in testJob, we have one setp which is used for record the runtime:

PRINT CONVERT(VARCHAR(24),GETDATE(),113)
This job will output to one file, and in this file we can get the runtime date:

Job ‘testJob’ : Step 3, ‘recodTime’ : Began Executing 2012-09-05 18:55:12
05 Sep 2012 18:55:12:110 [SQLSTATE 01000]

MoreInfo:
1. EXECUTE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188332.aspx
2. Different ways to execute a SQL Agent job: http://www.mssqltips.com/sqlservertip/1730/different-ways-to-execute-a-sql-agent-job/
3. sp_start_job Runs Asynchronously: http://www.daveturpin.com/2009/11/sp_start_job-runs-asynchronously/

SQLServer: Export SQL result into CSV file 导出SQL结果到CSV文件

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 5th, 2012 18:50

sqlcmd -E -iC:\test.sql -oC:\testSQLOutput.csv -s"," -h-1 -W

SQL in test.sql:

SELECT * FROM DavidTest.dbo.LiGuoliangComUsers;

All result should be export into the csv file.

SSMS(SQL Server Management Studio) can not edit SQL Agent Job steps

Categories: Database; Tagged with: SQL • SQL Server • SSMS; @ August 27th, 2012 21:39

After creating a new agent job in SSMS, I try to edit one of the steps, but got an error like this:

TITLE: Microsoft SQL Server Management Studio
——————————

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————
ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

This is a SQL2008R2 bug >link<

you can download and install the hotfix, from the bug page.

——————————————————————

or you can try this simple way, but it’s not stable for me:

Quit all SQL based applications (SSMS, BIDS etc), you dont have to stop sql service.

Go to Start -> All Programs -> Accessories and then right click on Command Prompt and choose run as administrator.

Now copy and past the following two lines in to the command window to register the DLL’s

This will register 64 bit DTS.dll
c:\windows\system32\regsvr32 "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"

This will register 32 bit which is required as SSMS is a 32-bit application.
c:\windows\syswow64\regsvr32 "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll"

Start SSMS and test that you can open and re-open jobs steps.

Job done!

from:

Cannot edit job steps in SQL Server 2008 R2 Management Studio on 64 bit windows.

The difference between DELETE and TRUNCATE

Categories: Database; Tagged with: SQL • SQL Server; @ August 21st, 2012 13:29

DELETE

DML(Data Manipulation Language), is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

If you delect one table’s data, the ID will continue in SQL 2005,.

TRUNCATE

DDL(Data Definition Language), TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.

If you truncate one table, the ID will be reset in SQL 2005.

Newer Posts <-> Older Posts