| Author |
 Topic  |
|
|
kid_on_the_block
Posting Yak Master
172 Posts |
 Posted - 12/10/2012 : 10:00:53
|
Hi
I am trying to create a table dynamically in SQL Server & its failing . Can someone please help me resolve the issue
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);
SET @MyTableName = convert(varchar,GETDATE(),112)
SET @DynamicSQL =
'CREATE TABLE ' + @MyTableName + ' (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)
'
Exec @DynamicSQL
I am getting the following error :
' is not a valid identifier.
|
|
|
theboyholty
Posting Yak Master
United Kingdom
220 Posts |
Posted - 12/10/2012 : 10:09:55
|
Two problems with this:
You're trying to call your table 20121012. You can't begin a table name with a number, needs to be characters at the start of the table name.
Also this: Exec @DynamicSQL should be Exec (@DynamicSQL).
Give that a go and see what happens.
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/10/2012 : 10:11:01
|
quote:
Originally posted by kid_on_the_block
Hi
I am trying to create a table dynamically in SQL Server & its failing . Can someone please help me resolve the issue
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);
SET @MyTableName = convert(varchar,GETDATE(),112) + 'Table'
SET @DynamicSQL =
'CREATE TABLE ' + @MyTableName + ' (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)'
Exec (@DynamicSQL)
I am getting the following error :
' is not a valid identifier.
Do this |
|
|
|
theboyholty
Posting Yak Master
United Kingdom
220 Posts |
Posted - 12/10/2012 : 10:12:15
|
Actually you CAN call a table 20121210, but you'd need to put in in square brackets. Sorry, my bad.
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
|
kid_on_the_block
Posting Yak Master
172 Posts |
Posted - 12/10/2012 : 10:56:44
|
Thanks a ton, It worked I modified my script to add [ ] for the table name & the () for Dynamic SQL
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(2000);
SET @MyTableName = convert(varchar,GETDATE(),112)
SET @DynamicSQL =
'CREATE TABLE [' + @MyTableName + '] (
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[SPID] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[HostName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ApplicationName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](128) NULL,
[Error] [int] NULL
)
'
Exec (@DynamicSQL) |
|
|
| |
Topic |
|
|
|
Trying to create a table Dynamically
