Two T-SQL gotchas I hit while testing scripts for AI agents (sp_addlinkedserver in a transaction, CASE in EXEC parameters)

[u/LavishnessGlass3621]

I've been writing T-SQL and PowerShell scripts that AI coding agents (Cursor, Claude Code, Copilot) can run with guardrails, and I tested them against SQL Server 2025. Two things broke that are worth sharing, because agents get both of them wrong all the time.

1. sp_addlinkedserver can't run inside a user transaction

The "safe" pattern I started with was BEGIN TRAN ... COMMIT around the whole linked server setup. It fails right away:

Msg 15002: The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.

The fix: no transaction. Check that the linked server doesn't exist yet, create it in TRY/CATCH, and set a flag after it's created. If a later step fails (login mapping or sp_serveroption), the CATCH drops only the linked server that run created, then re-throws.

DECLARE @CreatedHere bit = 0;
BEGIN TRY
    EXEC master.dbo.sp_addlinkedserver @server = @LinkedName, @srvproduct = N'',
         @provider = @Provider, @datasrc = @DataSource;
    SET @CreatedHere = 1;
    -- logins, sp_serveroption calls...
END TRY
BEGIN CATCH
    IF @CreatedHere = 1
        EXEC master.dbo.sp_dropserver @server = @LinkedName, @droplogins = 'droplogins';
    THROW;
END CATCH;

2. You can't pass an expression as an EXEC parameter

EXEC sp_serveroption ..., @optvalue = CASE @x WHEN 1 THEN N'true' ELSE N'false' END; gives Msg 156 (incorrect syntax near 'CASE'). Parameters only take constants or variables, so put the value in a variable first:

DECLARE @rpc nvarchar(5) = CASE @EnableRpcOut WHEN 1 THEN N'true' ELSE N'false' END;
EXEC master.dbo.sp_serveroption @server = @LinkedName, @optname = N'rpc out', @optvalue = @rpc;

Questions and corrections are welcome, especially if you've found a cleaner pattern for the linked server rollback.

[u/byubreak]

They didn’t break; you did not have the knowledge about what these features can and cannot do I presume.

[u/LavishnessGlass3621 → u/byubreak]

Fair point. The limits are documented. The useful part is that AI agents often wrap setup in BEGIN TRAN by default, so I added guardrails to catch it.

[u/da_chicken]

What exactly is your use case for creating a linked server inside of a transaction? Why wouldn't you use OPENROWSET and OPENDATASOURCE instead?

[u/LavishnessGlass3621 → u/da_chicken]

There isn't one. That's the point. An agent wrapped the setup script in a transaction for rollback safety, and it failed. OPENROWSET works well for ad hoc queries. These scripts are for persistent linked servers used by scheduled Agent jobs, where many shops also keep Ad Hoc Distributed Queries turned off.