Forum Discussion

Kiru_2024's avatar
Kiru_2024
Copper Contributor
Apr 15, 2024

Must declare even if I have already declared - what am i doing wrong?

Below is the script and its throwing "Must declare the scalar variable "@sql"." error.

 

EXEC sp_MSforeachdb '

IF OBJECT_ID(''tempdb..##FKConstraints'') IS NOT NULL

    DROP TABLE ##FKConstraints;

CREATE TABLE ##FKConstraints

(

    ServerName VARCHAR(40),

    DatabaseName VARCHAR(20),

    SQLText VARCHAR(MAX)

);

DECLARE @FinalSQL NVARCHAR(MAX) = N''

IF DB_ID() > 4

BEGIN

    DECLARE @sql VARCHAR(MAX) = ''

    SELECT @sql

        = @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ''ALTER TABLE ['' + s.name + '']'' + ''.'' + ''['' + o.name + '']''

          + ''WITH CHECK CHECK CONSTRAINT'' + i.name + CHAR(13) + CHAR(10)

    FROM sys.foreign_keys i

        INNER JOIN sys.objects o

            ON i.parent_object_id = o.object_id

        INNER JOIN sys.schemas s

            ON o.schema_id = s.schema_id

    WHERE i.is_not_trusted = 1

          AND is_not_for_replication = 0;

    IF @@ROWCOUNT > 0

        INSERT INTO ##FKConstraints

        (

            ServerName,

            DatabaseName,

            SQLText

        )

        VALUES

        (@@SERVERNAME, DB_NAME(), @sql);

END;

--SELECT * FROM ##FKConstraints

SELECT @FinalSQL = N''USE'' + N'' ''+ DatabaseName + N''   '' + CHAR(13) + CHAR(10) + SQLText + CHAR(13) + CHAR(10)

FROM ##FKConstraints;

EXEC sp_executesql @FinalSQL;'

  • SivertSolem's avatar
    SivertSolem
    Iron Contributor
    Apr 17, 2024
    I'm not able to debug the entirety of your script, but it would appear you have an issue with nested strings and escaping the ' character.
    • SivertSolem's avatar
      SivertSolem
      Iron Contributor
      Apr 17, 2024

      Your script

      EXEC sp_MSforeachdb '
IF OBJECT_ID(''tempdb..##FKConstraints'') IS NOT NULL
    DROP TABLE ##FKConstraints;
CREATE TABLE ##FKConstraints
(
    ServerName VARCHAR(40),
    DatabaseName VARCHAR(20),
    SQLText VARCHAR(MAX)
);
DECLARE @FinalSQL NVARCHAR(MAX) = N''
IF DB_ID() > 4
BEGIN
    DECLARE @sql VARCHAR(MAX) = ''
    SELECT @sql
        = @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ''ALTER TABLE ['' + s.name + '']'' + ''.'' + ''['' + o.name + '']''
          + ''WITH CHECK CHECK CONSTRAINT'' + i.name + CHAR(13) + CHAR(10)
    FROM sys.foreign_keys i
        INNER JOIN sys.objects o
            ON i.parent_object_id = o.object_id
        INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
    WHERE i.is_not_trusted = 1
          AND is_not_for_replication = 0;
    IF @@ROWCOUNT > 0
        INSERT INTO ##FKConstraints
        (
            ServerName,
            DatabaseName,
            SQLText
        )
        VALUES
        (@@SERVERNAME, DB_NAME(), @sql);
END;
--SELECT * FROM ##FKConstraints
SELECT @FinalSQL = N''USE'' + N'' ''+ DatabaseName + N''   '' + CHAR(13) + CHAR(10) + SQLText + CHAR(13) + CHAR(10)
FROM ##FKConstraints;
EXEC sp_executesql @FinalSQL;'

       

      What's executed by sp_MSForEachDb

      IF OBJECT_ID('tempdb..##FKConstraints') IS NOT NULL
    DROP TABLE ##FKConstraints;
CREATE TABLE ##FKConstraints
(
    ServerName VARCHAR(40),
    DatabaseName VARCHAR(20),
    SQLText VARCHAR(MAX)
);
DECLARE @FinalSQL NVARCHAR(MAX) = N'
IF DB_ID() > 4
BEGIN
    DECLARE @sql VARCHAR(MAX) = '
    SELECT @sql
        = @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + s.name + ']' + '.' + '[' + o.name + ']'
          + 'WITH CHECK CHECK CONSTRAINT' + i.name + CHAR(13) + CHAR(10)
    FROM sys.foreign_keys i
        INNER JOIN sys.objects o
            ON i.parent_object_id = o.object_id
        INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
    WHERE i.is_not_trusted = 1
          AND is_not_for_replication = 0;
    IF @@ROWCOUNT > 0
        INSERT INTO ##FKConstraints
        (
            ServerName,
            DatabaseName,
            SQLText
        )
        VALUES
        (@@SERVERNAME, DB_NAME(), @sql);
END;
--SELECT * FROM ##FKConstraints
SELECT @FinalSQL = N'USE' + N' '+ DatabaseName + N'   ' + CHAR(13) + CHAR(10) + SQLText + CHAR(13) + CHAR(10)
FROM ##FKConstraints;

      Pay particular attention to the area around "Declare @sql" on line 12 and how that breaks when '' is converted back to '.

Resources