Create Script

I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RMWhat error did you get exactly?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RM" <darasingha_nospam@.yahoo.com> wrote in message
news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RM|||Incorrect syntax - near primary (at the end of the first constraint
statement)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDhhMYzAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> What error did you get exactly?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "RM" <darasingha_nospam@.yahoo.com> wrote in message
> news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I am trying to use a script to create a table that already exists in one
DB
> in another DB. I used Enterprise Manager, "copied" the table and pasted
it
> into notepad which gave me the following script. However, I cannot get
SQL
> query analyzer to accept this script. Not sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Hello RM,
In your create script you have your table name as [dbo.ReportSite] which
if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
So change your script to be dbo.[ReportSite] and everything should be ok.
Aaron Weiker
http://aaronweiker.com/

> I am trying to use a script to create a table that already exists in
> one DB in another DB. I used Enterprise Manager, "copied" the table
> and pasted it into notepad which gave me the following script.
> However, I cannot get SQL query analyzer to accept this script. Not
> sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Thanks - worked.
RM.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:138326632422751463292620@.news.microsoft.com...
> Hello RM,
> In your create script you have your table name as [dbo.ReportSite] which
> if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
> So change your script to be dbo.[ReportSite] and everything should be ok.
> --
> Aaron Weiker
> http://aaronweiker.com/
>
>sql