August 3, 2009 at 7:54 pm
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,
.../...
August 3, 2009 at 8:20 pm
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
August 3, 2009 at 9:33 pm
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 !
August 4, 2009 at 2:12 am
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