how can i run exec with multiline

Question

Post reply

how can i run exec with multiline

linhdkl

Old Hand

Points: 329
More actions
August 3, 2009 at 7:54 pm

#140357

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,
.../...

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

linhdkl Old Hand Points: 329 More actions · Answer 2

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 !

Akeel.Mughal SSChasing Mays Points: 609 More actions · Answer 3

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