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/
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.
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.
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,.
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.
Online tool: http://www.dpriver.com/pp/sqlformat.htm
Really easy and fast!
There is a desktop version of this sql formatter, the desktop version has more features, and it’s faster and earier to use. There is also an Add-On version for Visual Studio and an Add-On for SQL Server Management Studio.
// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.