how can i run exec with multiline

  • hi all,

    I have problem with exec. Please see my ex for demo

    DECLARE @newline AS NVARCHAR(2);

    SET @newline = '\CR'--CHAR(13) + CHAR(10);

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = '

    USE [CO.Individual_LinhTest] '+@newline+'

    GO'+@newline+'

    CREATE TABLE [dbo].[tbl_CO_tsk_ReminderReferenceType](

    [ID] [smallint] NOT NULL,

    [Name] [nvarchar](64) NOT NULL,

    CONSTRAINT [PK_tbl_CO_gen_] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]'+@newline+'

    GO

    '

    EXEC(@sql )

    some ones have any idea for my problem ?

    thank so much,

    .../...

  • your sql statement should not have GO statements, that's all.

    this worked fine for me.

    note that since i'm quoting the whole thing as a multiline statement, i didn't need the @newline variables:

    DECLARE @newline AS NVARCHAR(2);

    SET @newline = '\CR'--CHAR(13) + CHAR(10);

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = '

    USE [Sandbox] ;

    CREATE TABLE [dbo].[tbl_CO_tsk_ReminderReferenceType](

    [ID] [smallint] NOT NULL,

    [Name] [nvarchar](64) NOT NULL,

    CONSTRAINT [PK_tbl_CO_gen_] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    ;

    '

    EXEC(@sql )

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • yes, I know if I remove "GO' I can run it well, But you have many tables to create and you make sure all your tables are created then you can create store proc. Other side you can release you memory for sql server with GO command.

    thanks so much !

  • GO only works in Enterprise Manager, the SQL Engine doesn't know what it is.

    DECLARE @newline AS NVARCHAR(2);

    SET @newline = '\CR'--CHAR(13) + CHAR(10);

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = '

    USE [Sandbox] ;

    CREATE TABLE [dbo].[tbl_CO_tsk_ReminderReferenceType](

    [ID] [smallint] NOT NULL,

    [Name] [nvarchar](64) NOT NULL,

    CONSTRAINT [PK_tbl_CO_gen_] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    ;

    GO

    SELECT @SQL

    Leave the GO in, then select the dynamic SQL string instead. You can switch Enterprise Manager to "Results to text" and copy and paste this into a new or existing query window.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply