bcp IN and OUT

EXEC xp_cmdshell 'bcp "select * from Reporting..FactMTDPrior with (nolock) where DataDt = ''2010-10-31'' " queryout "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
GO

GO


EXEC xp_cmdshell 'bcp Reporting..bcpTest in "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
go

SQL Partitions

/** Table Name + Schema Name + Function Details. **/
SELECT OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.index_id IN(0,1);

GO

/** Function Insight Details. **/
SELECT
PF.function_id
, PF.name
, PF.fanout AS NumPartitions
, CASE WHEN PF.boundary_value_on_right = 0
THEN 'LEFT' ELSE 'RIGHT' END AS RangeType
, PP.parameter_id
, CASE WHEN PP.system_type_id = PP.user_type_id
THEN T1.name ELSE T2.name END AS ParameterDataType
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
order by PF.name,PRV.Value

go

/** switch out the parition **/

alter table switch partition @partitionnr to partition @partitionnr

go

/** Adding a Partition on the existing Table **/


CREATE CLUSTERED INDEX [ix_DataDt_tab1] ON [dbo].[tab1]([DataDt] ASC)ON [SCHEME_Name]([PartitionKey])
CREATE NONCLUSTERED INDEX [ix_LoanId_tab1] ON [dbo].[tab1]([LoanId] ASC) ON [SCHEME_Name]([PartitionKey])

SQL SERVER 2005 Alert System

Declare @i int
select @i = count(*) from dbname.dbo.tab1
if @i = 0
begin
exec msdb.dbo.sp_send_dbmail @recipients = 'vpeddireddy@alert.com',@subject = 'test email'
end

Understanding SQL 2005 DMV and DMF

sys.dm_exec_query_stats -

Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here.

sys.dm_exec_sql_text -

Returns the text of the sql statement based on the SQL handle

sys.dm_exec_query_plan -

Returns the showplan in XML format for a batch or module based on the plan handle.


dm_db_index_usage_stats –

displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans

dm_db_index_physical_stats –

replace DBCC SHOWCONTIG used to check index fragmentation.

dm_db_index_operational_stats –

use to check for index contention and blocking

dm_os_wait_stats –

Used to determine the types of waits (signal or resource that are occurring)

dm_os_waiting_tasks –

Used to help identify blocking

dm_tran_locks –

Used to help indentify blocking

dm_os_performance_counters –

Query Access to SQL Server Performance counters

List of SQL 2005 DMVs

-- OS Related
select * from sys.system_objects
where name like 'DM_OS%'
GO

-- Exec Related
select * from sys.system_objects
where name like 'DM_exec%'
GO

-- Transaction Related
select * from sys.system_objects
where name like 'DM_tran%'
GO

-- Replication related
select * from sys.system_objects
where name like 'DM_repl%'
GO

-- IO Releated
select * from sys.system_objects
where name like 'DM_IO%'
GO

-- clr Releated
select * from sys.system_objects
where name like 'DM_CLR%'
GO

-- db index related

select * from sys.system_objects
where name like 'DM_db%'
GO


-- broker related

select * from sys.system_objects
where name like 'DM_broker%'
GO

-- fts related
select * from sys.system_objects
where name like 'DM_fts%'
GO

-- qn related
select * from sys.system_objects
where name like 'DM_qn%'
GO

SQL 2005

DSQuery

Following command gives OU details of a server
c:\winnt\system32>dsquery computer -name xxxxxxxxx

SQL 2005 Transaction Logs

The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared).




A log record is no longer needed in the transaction log if all of the following are true:
 The transaction of which it is part has committed.
 The database pages it changed have all been written to disk by a checkpoint.
 The log record is not needed for a backup (full, differential, or log).
 The log record is not needed for any feature that reads the log (such as database mirroring or replication).

A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.
This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not.



The check whether log truncation can take place under either of the following circumstances:

 When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
 When a log backup completes.



There are two common issues that can prevent log truncation:
 A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
 Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.

Changing Database COLLATION

1. Litespeed uninstall.

2. set Password and collation execute the following.

D:\Installs\SQL2005ENTSELX64dvd\Servers>start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=******* SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

3. APPLY CU3

SQL 2005 execute permissions

SQL2005

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Good Website

http://www.jenunderwood.com/resources.htm

Not able to Add Windows Global Group to SQL SERVER 2005

Problem : Not able to Add Windows Global Group to SQL SERVER 2005
Solution : Make sure that net logon service is running

Locks,Blocks and Latches.

Locks ensure logical consistency
o Example: A row cannot exist in table A unless a corresponding row exists in table B (a foreign-key constraint). When inserting a row into table B,
a shared lock will be obtained on the corresponding row in table B until the insert in table A completes, to ensure that nobody deletes the
corresponding row from table B and thereby ensure that the foreign key constraint (a logical consistency rule) is enforced when the transaction completes.

Latches ensure physical consistency
o To say that data is "physically consistent" is to state that the structure of the data on disk or in memory follows the defined patterns
that SQL Server expects (i.e. is not corrupt) and that the right data is stored in the right place (e.g. the value for column Y of row X is not
inadvertently stored in column W of row Z).

Sp_lock shows all outstanding locks, the sessions owning those locks, the resource being locked, the type of lock, and the state of the lock (granted or waiting).
Sys.dm_tran_locks is a data management view in SQL Server 2005 and SQL Server 2008 that shows additional detail about locks held by transactions in SQL Server.
When a process is blocked because it is waiting on a lock, you will see summary detail about the lock it is awaiting in sys.dm_os_waiting_tasks. And sys.dm_os_wait_stats provides a high level summary of time spent waiting on the various types of locks (regardless of the resource being locked).

-------------------------------------------------------------


-- Code and scripts from Chapter 6:
-- Concurrency Problems


-- You can detect table locks at a given point in time using the
-- sys.dm_tran_locks DMV. The following query shows an example:

SELECT
request_session_id,
resource_type,
DB_NAME(resource_database_id) AS DatabaseName,
OBJECT_NAME(resource_associated_entity_id) AS TableName,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks AS TL
JOIN sys.all_objects AS AO
ON TL.resource_associated_entity_id = AO.object_id
WHERE request_type = 'LOCK'
AND request_status = 'GRANT'
AND request_mode IN ('X','S')
AND AO.type = 'U'
AND resource_type = 'OBJECT'
AND TL.resource_database_id = DB_ID();




-- For example, the following code will prevent lock escalation
-- on the table for one hour.

BEGIN TRAN
SELECT *
FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)
WHERE 1=0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN




-- For example, the following query shows just those waits that
-- have been occurring for more than 5 seconds:

SELECT
WT.session_id AS waiting_session_id,
WT.waiting_task_address,
WT.wait_duration_ms,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
WHERE WT.wait_duration_ms > 5000;





-- The following query returns a subset of the columns and shows
-- all those locks that are in a WAIT state:

SELECT
TL.resource_type,
DB_NAME(TL.resource_database_id) as DatabaseName,
TL.resource_associated_entity_id,
TL.request_session_id,
TL.request_mode,
TL.request_status
FROM sys.dm_tran_locks AS TL
WHERE TL.request_status = 'WAIT'
ORDER BY DatabaseName, TL.request_session_id ASC;




-- The sys.dm_tran_locks DMV uses both the
-- resource_associated_entity_id along with the
-- resource_description to identify the locked resource, so we
-- just need to use that as the method for joining the view with
-- itself to pick out each waiting and granted lock for each
-- given resource. The following query shows how you can do it:

SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
TL1.resource_associated_entity_id,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks as TL1
JOIN sys.dm_tran_locks as TL2
ON TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
AND TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL
AND TL2.resource_description IS NULL))
ORDER BY TL1.request_status ASC;




-- You might want to also query the view and return the actual
-- object represented by the resource_associated_entity_id,
-- whether it be a RID, key, page or table.

SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
CASE TL1.resource_type
WHEN 'OBJECT'
THEN OBJECT_NAME(TL1.resource_associated_entity_id,
TL1.resource_database_id)
WHEN 'DATABASE' THEN 'DATABASE'
ELSE
CASE
WHEN TL1.resource_database_id = DB_ID() THEN
(SELECT OBJECT_NAME
(object_id, TL1.resource_database_id)
FROM sys.partitions
WHERE hobt_id = TL1.resource_associated_entity_id)
ELSE NULL
END
END AS ObjectName,
TL1.resource_description,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks AS TL1
JOIN sys.dm_tran_locks AS TL2
ON TL1.resource_associated_entity_id =
TL2.resource_associated_entity_id
WHERE TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL
AND TL2.resource_description IS NULL))
ORDER BY TL1.resource_database_id,
TL1.resource_associated_entity_id,
TL1.request_status ASC;





-- First you use the sp_configure command to set the advanced
-- option blocked process threshold to a value, let's say 60
-- seconds:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold', 60;
RECONFIGURE;






--You can join on the sys.dm_tran_locks. Here's an example:

SELECT
WT.session_id AS waiting_session_id,
DB_NAME(TL.resource_database_id) AS DatabaseName,
WT.wait_duration_ms,
WT.waiting_task_address,
TL.request_mode,
TL.resource_type,
TL.resource_associated_entity_id,
TL.resource_description AS lock_resource_description,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description AS blocking_resource_description
FROM sys.dm_os_waiting_tasks AS WT
JOIN sys.dm_tran_locks AS TL
ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
AND WT.session_id > 50;




-- By adding a subquery that joins the sys.dm_exec_requests DMV
-- and the sys.dm_exec_sql_text() DMF, correlating back to the
-- joining on the waiting task's session_id, you can extract the
-- query text. Here's an example, adapted from a similar query
-- in A Troubleshooting Methodology, Chapter 1 of this volume.

SELECT
WT.session_id AS waiting_session_id,
DB_NAME(TL.resource_database_id) AS DatabaseName,
WT.wait_duration_ms,
WT.waiting_task_address,
TL.request_mode,
(SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests AS ER
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE ER.session_id = TL.request_session_id)
AS waiting_query_text,
TL.resource_type,
TL.resource_associated_entity_id,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description AS blocking_resource_description,
CASE
WHEN WT.blocking_session_id > 0 THEN
(SELECT ST2.text
FROM sys.sysprocesses AS SP
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
WHERE SP.spid = WT.blocking_session_id)
ELSE NULL
END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS WT
JOIN sys.dm_tran_locks AS TL
ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
AND WT.session_id > 50;




-- Here's a skeletal example of how you can rewrite your code to
-- retry the transaction using Transact-SQL:

DECLARE @Tries tinyint, @Error int;
SET @Tries = 1;
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
--
IF XACT_STATE() = 1 COMMIT;
BREAK;
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER();
IF @Error = 1205
BEGIN
IF XACT_STATE() = -1 ROLLBACK;
END
SET @Tries = @Tries + 1;
CONTINUE;
END CATCH;
END;




-- Assume you have two tables, a header table called H1 and a
-- detail table called D1, and you have declared a foreign key
-- from D1 to H1 referencing H1's primary key. Here's the code
-- to create that scenario:

USE Scratch;
GO
ALTER DATABASE Scratch
SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch
SET READ_COMMITTED_SNAPSHOT OFF;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
GO
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
ALTER TABLE D1 ADD CONSTRAINT FK_D1ID_D1
FOREIGN KEY (H1ID) REFERENCES H1(H1ID);
GO
INSERT H1 VALUES (1,'Test');
GO





-- Now let's change the database to READ COMMITTED SNAPSHOT
-- (sometimes abbreviated to RCSI):

ALTER DATABASE Scratch
SET READ_COMMITTED_SNAPSHOT ON;

-- You'll have to make sure for a moment that you only have one
-- connection to the database so that this change will take
-- affect. You also need to reinsert the row into the H1 table.

INSERT H1 VALUES (1,'Test');




-- To reproduce this, alter the database to disable RCSI and
-- enable SNAPSHOT isolation (momentarily taking all other
-- connections out of the database):

ALTER DATABASE Scratch
SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch
SET ALLOW_SNAPSHOT_ISOLATION ON;


-- Now reinsert the row into the H1 table one more time.

INSERT H1 VALUES (1,'Test');




-- For this example, you'll need to recreate the same header and
-- detail tables but this time create a simple trigger that will
-- ensure referential integrity for single-row INSERTs.
-- (Using foreign key constraints is the recommended way to
-- enforce referential integrity, but many databases still use
-- triggers.)

USE Scratch;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
CREATE TRIGGER tr_D1_H1
ON D1
FOR INSERT
AS
BEGIN
-- Single-row inserts only
IF (SELECT H1ID FROM inserted) NOT IN (SELECT H1ID FROM H1)
BEGIN
PRINT 'Rolling back insert'
ROLLBACK
END
END;
GO
INSERT H1 VALUES (1, 'Test')
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
GO




-- Remove all other users in the database and issue the following
-- commands, putting the database into the
-- READ COMMITTED SNAPSHOT state:

ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT ON;
GO




-- To clean up, empty out the D1 detail table and reinsert the
-- header row into H1:

DELETE FROM D1;
INSERT H1 VALUES (1,'Test');





-- For this example, remove all other users in the database, set
-- READ COMMITTED SNAPSHOT to OFF in the database, set the
-- database to allow SNAPSHOT isolation:

ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION ON;
GO




-- For example, you could rewrite the initial RI trigger as
-- follows, using the READCOMMITTEDLOCK hint:

CREATE TRIGGER tr_D1_H1
ON D1
FOR INSERT
AS
BEGIN
-- Single-row inserts only
IF (SELECT H1ID FROM inserted) NOT IN
(SELECT H1ID FROM H1 WITH (READCOMMITTEDLOCK))
BEGIN
PRINT 'Rolling back insert'
ROLLBACK
END
END;




-- In all the examples so far of lost updates, you can apply the
-- UPDLOCK hint to the SELECT statement that reads the value
-- from Sales.SalesOrderDetail:

SET @OrderQty =
(SELECT OrderQty
FROM Sales.SalesOrderDetail WITH (UPDLOCK)
WHERE SalesOrderID = 43659
AND SalesOrderDetailID = 1);



--Finally the UPDLOCK hint will also prevent the lost update
-- shown in Table 6-12. Just add the UPDLOCK hint to the SELECT
-- statement in step 2 as follows:

SELECT OrderQty
FROM Sales.SalesOrderDetail WITH (UPDLOCK)
WHERE SalesOrderID = 43659
AND SalesOrderDetailID = 1;




-- Using the example from the transaction shown in Table 6-11,
-- you could rewrite the SNAPSHOT transaction as:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
WAITFOR TIME '00:15:00.0';

DECLARE @Tries tinyint, @Error int;
DECLARE @OrderQty smallint;
SET @Tries = 1;

WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SELECT OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
AND SalesOrderDetailID = 1;
WAITFOR DELAY '00:00:00.2';
UPDATE Sales.SalesOrderDetail
SET OrderQty = OrderQty + 2
WHERE SalesOrderID = 43659
AND SalesOrderDetailID = 1;
IF XACT_STATE() = 1 COMMIT;
BREAK;
END TRY;
BEGIN CATCH
SET @Error = ERROR_NUMBER();
IF @Error = 3960
BEGIN
PRINT '3960 encountered';
IF XACT_STATE() = -1 ROLLBACK;
END
SET @Tries = @Tries + 1;
CONTINUE
END CATCH;
END;




-- For monitoring Full SNAPSHOT isolation level transactions in
-- particular, you can use sys.dm_tran_active_snapshot_database_transactions.
-- The following query shows one example:

SELECT
transaction_id,
session_id,
transaction_sequence_num,
is_snapshot,
max_version_chain_traversed,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;




-- The following query shows one way of using this DMV to inspect
-- the free space in tempdb:

SELECT
SUM(user_object_reserved_page_count) * 8.192 AS UserObjectsKB,
SUM(internal_object_reserved_page_count) * 8.192 AS InternalObjectsKB,
SUM(version_store_reserved_page_count) * 8.192 AS VersionStoreKB,
SUM(unallocated_extent_page_count) * 8.192 AS FreeSpaceKB
FROM sys.dm_db_file_space_usage;

Maintainance Plan -- Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

When editing a maintenance plan (that I am newly creating) I got:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
The issue is the client tools installed in %ProgramFiles(x86)% instead of %ProgramFiles% but instead of reinstalling client tools, I ran:

%windir%\syswow64\regsvr32 "D:\MSSQL (x86)\90\DTS\Binn\dts.dll"
which resolved the issue.

SQL SERVER 2005 Permissions to View Stored Procedures

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master
GO
REVOKE VIEW ANY DEFINITION TO User1

-- or

USE AdventureWorks
GO
REVOKE VIEW Definition TO User1


If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Linked Server >>> Unable to begin a distributed transaction

Error Message:

OLE DB provider "SQLNCLI" for linked server "agosqltqtest01_link" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "test01_link" was unable to begin a distributed transaction.

Resolution: Change to No Authetication Required as follows

Multiple SSIS SQL Server Instances

Multiple SSIS installs of SQL server on the same machine.

There is a configuration change that needs to be made to the SSIS file MsDtsSrvr.exe. You will need to add an entry for each of the installed instances on the server so that it can look at all of the msdb’s available. Once you have made the changes, you will need to stop and restart the ssis service.