Commandline exec SQL Agent job

1.SQLCMD -SBKCOM\INSTANCE1 -E -Q"EXEC msdb.dbo.sp_start_job @job_name ='TEST'"

Permissions--On MSDB Database "SqlAgentOperator" role required to run the sql agent jobs the through command line.

2.isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

3.osql -E -d MSDB -Q "sp_start_job 'MySQLJob'"
4.CREATE PROCEDURE sp_RunJob
AS
BEGIN
EXEC msdb..sp_start_job @job_name = 'The job name'
END

SSIS Pacakge Execute

1. http://msdn.microsoft.com/en-us/library/ms162810.aspx
2. http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx

--Using Stored procedure
http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
http://geekswithblogs.net/stun/archive/2010/02/24/execute-ssis-package-from-stored-procedure-with-parameters-using-dtexec-utility.aspx


Syntax

dtexec /option [value] [/option [value]]...

Using dtexec from the xp_cmdshell
You can run dtexec from the xp_cmdshell prompt. The following example shows how to run a package called

UpsertData.dtsx and ignore the return code:
EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'

The following example shows how to run the same package and capture the return code:
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'

SSIS Pacakge - Exit codes returned from dtexec utility

Value        Description

0           The package executed successfully.

1           The package failed.

3          The package was canceled by the user.

4          The utility was unable to locate the requested package. The package could not be found.

5          The utility was unable to load the requested package. The package could not be loaded.

6          The utility encountered an internal error of syntactic or semantic errors in the command line.

ASCMD Command-line Utility -- SSAS

http://msftasprodsamples.codeplex.com/wikipage?title=SS2005%21Readme%20For%20Command-line%20Utility%20Sample&referringTitle=Home
http://msdn.microsoft.com/en-us/library/ms365187(SQL.90).aspx

ascmd -S myserver -d "Adventure Works DW" -i query.mdx -o result.xml -v cube="Adventure Works"

SQL Server 2008 Notes

http://blogs.technet.com/b/josebda/archive/2009/04/12/sql-server-2008-summary-of-training-sessions.aspx

How to receive Deadlock information automatically via email.

http://www.sqlservercentral.com/articles/deadlock/71374/


Requirement number one:
The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.
3605 = write what we want to the error log.
1204 = Capture Deadlock Events.
1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Using SQL Profiler also can find the dead lock.

Grant all permissions on all tables,views

Grant select,insert,update,delete on all tables,views to Dev_Role on database

select 'grant select,insert,update,delete on '+ name + ' to Dev_Role' from sysobjects where type = 'U' or Type='V' GO

or
SELECT 'GRANT SELECT,insert,update,delete ON ' + TABLE_NAME + ' TO BATCH_ROLE' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Go

--Grant exec permissions for SP's
Grant execute privileges to all Stored procedures to Dev_Role role
select 'grant exec on '+ name + ' to Dev_Role' from sysobjects where type = 'P' AND category =0
Go