Can you connect from the DTS host to the SQL2008 server using SSMS or OSQL?

100% agree. Don't use SQL to manipulate the procedure text.

IMHO, the ROLLBACK statement should be the first instruction in the CATCH block, because the RAISERROR in the error handling procedure would prevent any subsequent statement from executing, if called...

As far as I know there's no particular issue on this kind of connection.

SQL Server 2008 can be set up as an OLEDB source in your DTS package.

What error...

Proabably it's not what you wanted to hear, but I think you'b better do it manually.

If you are confident enough that it can be done with a script, you could...

Looks like LoggedDateTime is not a datetime column.

Can you post the create script for the tables involved?

You can't use IF UPDATE(column) in dynamic sql. If you don't want to type the code for each column, generate it and paste it into the trigger.

You could also check...

UPDATE: looks like there's no way to do what I'm after.

Adam Machanic opened a connect entry for that...

Any ideas?

Dynamics AX keeps the business logic inside the app code and doesn't usually move it to the database with stored procedures.

If you are concerned about AX performance, I suggest that...

You could also use a small powershell script:

function connectSQL([string]$serverName, [string]$instanceName) {

trap {

continue

}

$sqlServer = $serverName

if(!$instanceName.equals("")){

$sqlServer = $serverName + "\" + $instanceName

}

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source="+ $sqlServer +";Integrated Security=SSPI;Initial Catalog=master")

$cn2.Open()

Write-Host ...

Alvin Ramard (7/7/2010)

---

CirquedeSQLeil (7/7/2010)

---

Alvin Ramard (7/7/2010)

---

Jeff Moden (7/7/2010)

---

I'm either getting too old or just lazier in my old age. I no longer fight people who insist on being wrong....

DENY doesn't work with sysadmins, as Gail pointed out.

DENY won't prevent db_owners from dropping the DENY itself (db_owner implies CONTROL).

I experienced some strange things with DENY, so I'd be careful.

It...

You could also try using SELECT/INTO instead of INSERT/EXEC:

DECLARE @sqlRowset nvarchar(4000)

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

set @sqlRowset = 'SELECT * INTO #results FROM...

Just a guess, if you have to run this script to connect to many servers, but always from the same server, you could tweak your DTC settings only for the...

It's not very different. The SP is applied automatically to all nodes, moving the instances accordingly. You will probably have to fail over the instances to the preferred owners at...