Running dynamic SQL in a different database

10

May 19, 2022 by Kenneth Fisher

This is one of those things that on hindsight was a stupid problem, but still cost me hours and a major headache. So I thought I would share the story of my headache with you. 😁

A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.

DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
GO
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘GO’.

Completion time: 2022-05-18T16:53:57.5145414-05:00

Hu? I can’t use GO in my dynamic SQL? Well then how am I going to switch databases before I run my code?

Two mistakes here. I was going to say they were stupid mistakes, but let’s face it, everyone forgets things and gets stuck on something simple. So mistakes, but not stupid. Anyway.

I banged my head against my desk for probably 3 hours, doing all kinds of weird searches, trying out all kinds of strange pieces of code, for something I knew was really really simple.

DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;

I ended up with a few takeaways from all of this.

  • Habits can really mess you up if you aren’t careful.
  • You don’t actually need a GO after a USE.
  • Everyone makes mistakes. And frequently those mistakes are from some really simple stuff. No one is immune.

FYI I already knew that last one, but it’s one of those things that everyone should be reminded of periodically.

10 thoughts on “Running dynamic SQL in a different database

  1. aaronbertrand says:

    I find this a lot easier:

    DECLARE @db sysname = N'otherDB';
    DECLARE @exec nvarchar(1000) = QUOTENAME(@db) + N'.sys.sp_executesql';

    EXEC @exec @sql;

  2. […] Kenneth Fisher doesn’t like this database and wants to move to a new one: […]

  3. Jen McCown says:

    Is there any reason not to do this?

    DECLARE @sql nvarchar(max);
    SET @sql = N’ /* Do stuff here, always with DBName.” . */

    SELECT * FROM [OtherDB].sys.tables;’

    EXEC sp_executesql @sql;

  4. CHRISTOPHER G says:

    Some commands have to be the first statement in a batch, so you can’t always use a USE statement to change DBs first. To get around that, I went Inception-style on the problem and created this little stored procedure:

    CREATE PROCEDURE dmc.ExecuteInDB
    @p_SQLToExecute nvarchar(max)
    , @p_RunInDB sysname = NULL
    AS

    ——————————————————————————–
    — CONSTANTS AND VARIABLES —
    —————————–
    DECLARE @PARMDEF nvarchar(max) = ‘@SQLIn nvarchar(max)’;
    DECLARE @SQL nvarchar(max);

    ——————————————————————————–
    — EXECUTE —
    ————-
    SET @SQL = N’USE [‘ + @p_RunInDB + N’];’ + Char(13) + Char(10);
    SET @SQL = IsNull (@SQL, N”) + N’EXEC sp_executesql @SQLIn;’;

    EXEC sp_executesql @SQL, @PARMDEF, @SQLIn = @p_SQLToExecute;

  5. Robert S Conover says:

    My job requires the exection of SQL code blocks across 1000’s of dbs on dozens of servers to collect and warehouse data, sometimes the code is a static/common proc/snipet, but many times it is a custom request.. “Find me all of {this condition….}, and search ONLY these customers/DBs”

    As a result I/We have create many “Code Generation” methods to run SQL across multiple servers/db’s.

    The most comman is an LOOP based [Site list] method, not the fastest, but VERY stable/dependable, when time is not a factor, as in run off hours/overnight(s).

    Here is a simplified version:
    Note: This method depends on the use of [Linked Servers] from the calling Server/DB, and proper exection permission, as well as REMOTE EXECUTION… {Argue the good/bad of all that if you will -:)}

    /* ========================================================================
    History
    ———-
    YYYY-MM-DD WHO [Comment]
    ======================================================================== */

    SET NOCOUNT ON;
    — ================ COMMON VARS
    Declare @RowCount Int — Output/Log
    Declare @Dump Varchar(200) — Output/Log
    — ================ Customer/Site Info variables
    Declare @SiteKey Int — Unique Prime Key
    Declare @Nickname Varchar(100) — Customer Nickname (Display)
    Declare @DBName Varchar(100) — Database Name
    Declare @ExecuteServer Varchar(200) — Remote Exec string
    — ================ SQL Parts
    Declare @SQLExec nVarchar(Max) — Runable SQL string
    Declare @SQLTemplate Varchar(Max) = ‘
    USE [{DB NAME}]
    /* =======================================================
    Customer: {NICKNAME}

    CODE COMMENTS IN GENERATED SQL
    ======================================================= */
    BEGIN
    — [SQL COMMAND(S) HERE].. LIKE W/REPLACE
    Select {SITEKEY} [SiteKey] — PK
    , ”{NICKANME}” [Customer]
    , [FIELD1]
    , [FIELD2]
    FROM [dbo].[Tablename]
    JOIN — ??? ON [] = []
    WHERE ??????????
    END

    /* ========== POSTING COMMENT
    Master list of Customers DBs – Common location all DB’s can access
    – Common.dbo.Sites {Linked Server}
    – Key/Cust Nickname(Short)/Customer Type(Filters)/
    – DB Server/DB Name – USE/Remote Exec
    – Other info (+30 attributes)
    =========================== */

    Declare @Sites dbo.typSiteList_Table
    Insert Into @Sites
    EXEC [dbo].[Master_Get_SiteList_Table] ‘{**FILTER ARG**}’
    — ADD Additional FILTER/DELETEs of SITE(s) HERE

    WHILE Exists(Select Top 1 [SiteKey] From @Sites) — Loop on Site List
    BEGIN
    — ==================== Get values for SQL Generation & Display
    Select TOP 1 @SiteKey = [Site].[SiteKey],
    @Nickname = [Site].[Nickname],
    @DBName = [Site].[DBName],
    @ExecuteServer = ‘[‘ + [Site].[DBServer] + ‘].[master].dbo.sp_executesql’
    From @Sites [Site]
    Order By [Site].[SiteKey]

    — LOG
    Set @Dump = ‘– ‘ + CONVERT(Varchar(50),GetDate(),121)
    + ‘ – ‘ + Right(‘ ‘ + CONVERT(Varchar(4),@SiteKey),5) + ‘ ‘
    + Convert(Char(30),@Nickname)
    Print @Dump

    /* ========== POSTING COMMENT
    Token based replacement in @SQLTemplate
    =========================== */
    Set @SQLExec = Replace(Replace(Replace(
    @SQLTemplate
    , ‘{SITEKEY}’ , @SiteKey)
    , ‘{NICKNAME}’ , @Nickname)
    , ‘{DB NAME}’ , @DBName)

    Print @SQLExec — TESTING

    EXEC @ExecuteServer @SQLExec
    Set @RowCount = @@ROWCOUNT
    Set @Dump = ‘ – Output: ‘ + CONVERT(VARCHAR(20),@RowCount)
    Print @Dump — LOGGING

    NEXT_SITE:

    Set @Dump = ‘– ‘ + CONVERT(Varchar(50),GetDate(),121) + ‘ – ‘ + Right(‘ ‘ + CONVERT(Varchar(4),@SiteKey),5) + ‘ ‘ + Convert(Char(30),@Nickname) + ‘ Complete’
    Print @Dump

    Delete From @Sites Where [SiteKey] = @SiteKey — Remove This Site From List as Complete

    END

    *** Pasted poorly, not sure how to make that better..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,738 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: