Create table script without drop

Hello all!
I'm currently working on a project where we have several customers with
the same application. The database is constantly being changed and it's
hard to keep track of all the changes from all the versions in the
customers' systems.
Usually I create the changes script every time I alter any of the tables
but there is always a risk of loosing them. I wonder if there is anyway
of creating a script that updates all the tables instead of dropping and
creating them all, so our customers won't loose the database records.
Thanks in advance,
Hugo MadureiraHugo,
You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
TABLE.
eg.
Alter Table MyTable
Add MyColumn varchar(10)
HTH
Barry|||You can get rid of a lot of headaches by using SQL Compare.
www.red-gate.com
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:%232216JpJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm currently working on a project where we have several customers with
> the same application. The database is constantly being changed and it's
> hard to keep track of all the changes from all the versions in the
> customers' systems.
> Usually I create the changes script every time I alter any of the tables
> but there is always a risk of loosing them. I wonder if there is anyway of
> creating a script that updates all the tables instead of dropping and
> creating them all, so our customers won't loose the database records.
>
> Thanks in advance,
> Hugo Madureira|||Of course, this gets more complex than just adding columns. Such as
adding/removing columns with check constraints, foreign key constraints,
primary key constraints, unique constraints, computed columns, changing
datatypes/scale/precision, etc. Not all table changes are adding columns.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138731694.928227.324210@.z14g2000cwz.googlegroups.com...
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>|||Ahh now I understand what he *actually* wanted... oops!
Barry|||I was looking for a possible way of doing that with Enterprise Manager
manager, in a way that it could be done automatically.
When I use Enterprise Manager to create a table script, it drops the
table and re-creates it. That causes data loss in the database.
If there is no way of doing that, is it possible to easily edit the
script generated by Enterprise Manager to do that?
Barry wrote:
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>

Create Table Script Incorrect syntax near 'Collate'

HI.

I am using SQL Server 2000. I generate a script of some table from EmpDB database when I run script in query analyzer it return error "Incorrect syntax near 'COLLATE'."

Scripts is

************************************************** ****
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp]
GO

CREATE TABLE [dbo].[Emp] (
[EmpID] [int] NOT NULL ,
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

************************************************** ****
When I remove " COLLATE SQL_Latin1_General_CP1_CI_AS" from script manually then it run successfully.

Please guide why it is happening and how to escape from error.

Thanx in advance.I have tried your code and it works on my PC.

Try creating the table without the Collation and then use entreprice manager to assign the spicfic collation to the field. Does the specific calation exist?sql

Create table script

Hi all,
Often when I post a query problem the replies ask that a create
table script is provided. Is there an easy way to do this on SQL 2000?
What if I don't want to include a whole table only certain fields?
Any pointers would be gratefully received.
M

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can generate a CREATE TABLE script automatically in Query Analyzer
(right-click on the table in the Object Browser) or in Enterprise Manager
(right-click on the database. All Tasks > Generate SQL Script).

Ideally, edit the script to just the columns relevant to your problem but
always include the primary key for each table. Include the foreign keys if
there's more than one table involved. Also include any check, unique and
nullability constraints on the relevant columns.

Often it helps if you can construct some sample data (post as INSERT
statements) to illustrate your problem and show the result you require based
on that sample data. Oh, and do test your script out before you post it :-)

See also: www.aspfaq.com/5006

--
David Portas
----
Please reply only to the newsgroup
--

create table script

I am creating table in sql server 2005, by using the below script,but it
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:

> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>
>
|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>
|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:

> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>
>
|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...[vbcol=seagreen]
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:

create table script

I am creating table in sql server 2005, by using the below script,but it
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
some tool you get that error message? If so, can you specify what tool you use to execute the script
and against what version of SQL Server. Also, make sure you do not mark any text when you execute
it, or that you do mark only the text you want to be executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>I am creating table in sql server 2005, by using the below script,but it
> showing "syntax error near (" The below script is created from generate
> script option of sql) ,I ll apprecite the solution asap,Thanx guys
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[UserRoles](
> [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> [UserID] [int] NOT NULL,
> [RoleID] [int] NOT NULL,
> [ExpiryDate] [datetime] NULL,
> [IsTrialUsed] [bit] NULL,
> [EffectiveDate] [datetime] NULL,
> CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> (
> [UserRoleID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]|||I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >I am creating table in sql server 2005, by using the below script,but it
> > showing "syntax error near (" The below script is created from generate
> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > CREATE TABLE [dbo].[UserRoles](
> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> > [UserID] [int] NOT NULL,
> > [RoleID] [int] NOT NULL,
> > [ExpiryDate] [datetime] NULL,
> > [IsTrialUsed] [bit] NULL,
> > [EffectiveDate] [datetime] NULL,
> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> > (
> > [UserRoleID] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > ) ON [PRIMARY]
>
>|||>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
Then that tool would be query analyzer (or perhaps you meant SQL Server Management Studio).
Anyhow, I executed the code you posted and it worked just fine. I'm also on SQL Server 2005. My
guess is that you by mistake maked a part of the text so that only that text were submitted to sQL
Server.-
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script
>> from
>> some tool you get that error message? If so, can you specify what tool you use to execute the
>> script
>> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but it
>> > showing "syntax error near (" The below script is created from generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> >I am not using any kind of tool, i am executing it from query analyzer of
> >sql
>> server 2005 standard ediion
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>>I am not using any kind of tool, i am executing it from query analyzer of
>>sql
>> server 2005 standard ediion
>> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when
>> you execute the script from
>> some tool you get that error message? If so, can you specify what tool
>> you use to execute the script
>> and against what version of SQL Server. Also, make sure you do not mark
>> any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but
>> >it
>> > showing "syntax error near (" The below script is created from
>> > generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>
>|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> > >I am not using any kind of tool, i am executing it from query analyzer of
> > >sql
> >> server 2005 standard ediion
> >
> > Then that tool would be query analyzer (or perhaps you meant SQL Server
> > Management Studio).
> >
> > Anyhow, I executed the code you posted and it worked just fine. I'm also
> > on SQL Server 2005. My guess is that you by mistake maked a part of the
> > text so that only that text were submitted to sQL Server.-
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
> >>I am not using any kind of tool, i am executing it from query analyzer of
> >>sql
> >> server 2005 standard ediion
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> I assume that you by "it showing "syntax error near ("" Mean that when
> >> you execute the script from
> >> some tool you get that error message? If so, can you specify what tool
> >> you use to execute the script
> >> and against what version of SQL Server. Also, make sure you do not mark
> >> any text when you execute
> >> it, or that you do mark only the text you want to be executed.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
> >> message
> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >> >I am creating table in sql server 2005, by using the below script,but
> >> >it
> >> > showing "syntax error near (" The below script is created from
> >> > generate
> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >> >
> >> > SET ANSI_NULLS ON
> >> > GO
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > CREATE TABLE [dbo].[UserRoles](
> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> >> > [UserID] [int] NOT NULL,
> >> > [RoleID] [int] NOT NULL,
> >> > [ExpiryDate] [datetime] NULL,
> >> > [IsTrialUsed] [bit] NULL,
> >> > [EffectiveDate] [datetime] NULL,
> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> >> > (
> >> > [UserRoleID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > => >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > ) ON [PRIMARY]
> >>
> >>
> >>
> >
> >
>
>|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
>> Another posibility is that the server/instance you are running this
>> against
>> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
>> 2005. Try running
>> Select ServerProperty('ProductVersion')
>> If it returns a value where the first digit is 8, like
>> 8.00.2039
>> then it is SQL 2000 and that is your problem.
>> But if it returns a value where the first digit is 8, like
>> 9.00.3042.00
>> then it is SQL2005 and my guess is incorrect.
>> But if it is SQL 2000, then the entire clause WITH (...) is not valid
>> syntax
>> and should be removed.
>> Tom
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>> > >I am not using any kind of tool, i am executing it from query analyzer
>> > >of
>> > >sql
>> >> server 2005 standard ediion
>> >
>> > Then that tool would be query analyzer (or perhaps you meant SQL Server
>> > Management Studio).
>> >
>> > Anyhow, I executed the code you posted and it worked just fine. I'm
>> > also
>> > on SQL Server 2005. My guess is that you by mistake maked a part of the
>> > text so that only that text were submitted to sQL Server.-
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> > message
>> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>> >>I am not using any kind of tool, i am executing it from query analyzer
>> >>of
>> >>sql
>> >> server 2005 standard ediion
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> I assume that you by "it showing "syntax error near ("" Mean that
>> >> when
>> >> you execute the script from
>> >> some tool you get that error message? If so, can you specify what
>> >> tool
>> >> you use to execute the script
>> >> and against what version of SQL Server. Also, make sure you do not
>> >> mark
>> >> any text when you execute
>> >> it, or that you do mark only the text you want to be executed.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >> >I am creating table in sql server 2005, by using the below
>> >> >script,but
>> >> >it
>> >> > showing "syntax error near (" The below script is created from
>> >> > generate
>> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >> >
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > CREATE TABLE [dbo].[UserRoles](
>> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> >> > [UserID] [int] NOT NULL,
>> >> > [RoleID] [int] NOT NULL,
>> >> > [ExpiryDate] [datetime] NULL,
>> >> > [IsTrialUsed] [bit] NULL,
>> >> > [EffectiveDate] [datetime] NULL,
>> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [UserRoleID] ASC
>> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
>> >> > IGNORE_DUP_KEY
>> >> > =>> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> >> > ) ON [PRIMARY]
>> >>
>> >>
>> >>
>> >
>> >
>>

create stored procedure in IF-structure

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:

Code Snippet

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@.lastname varchar(30) = 'D%',
@.firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @.firstname
AND au_lname LIKE @.lastname
GO

The thing is, I want to change and use it like this:

Code Snippet

USE pubsIF NOT EXISTS (SELECT name FROM sysobjects


CREATE PROCEDURE ...

USE pubs

GO

ALTER PROCEDURE au_info2 ...

But that does not seem to work.. I get the following error:

Code Snippet

Incorrect syntax near the keyword 'PROCEDURE'

Any idea's? Any help is appreciated!

Kind regards,

Frederik

The CREATE statement needs to be the first statement in the batch so you can't have it in after an IF clause.

I guess you could get round this by doing the following:

IF NOT EXISTS.....

EXEC('CREATE PROCEDURE au_info2 AS.....')

HTH!

|||Very dirty, but it works! I need it to avoid some errors when

replicating and such.. Thx!

Create stored procedure

Hi!
I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.
Please help me!
Mike.I'm not sure that I understand what you mean by inparameters, but if you just read the script file into a string variable, then execute that string variable as a command, then you should be "good to go".

-PatP|||Why would you want to do backend application development from access..

I would imagine it would be severe hoop jumping...

Get the sql server client side tools...

unless we're really talking about MSDE...|||Originally posted by Brett Kaiser
Why would you want to do backend application development from access.. Why do some folks like leather undies? There is no accounting for taste.

I'd suggest using OSQL or better yet Visual Studio, but that's just me!

-PatP|||Originally posted by Pat Phelan
Why do some folks like leather undies?

I have no response|||We are currently (trying) to create an application with Access forms and SQL server database with stored procedures. If you can get out of it, please do. Certainly the part with the stored procedures parameters is a hell. I would also suggest MSDE with osql.

But, probably, you can't drop the Access, so if you can supply some more info and i'll look into it.sql

create sqlexpress mdf compat. with sql2k

I have a script to create a db. it only uses features available to sql2k. I only have sqlexpress, but want to distribute a db to someone with sql2k. Can I set a setting or something to allow me to detach my db and attach it to the sql2k server, or create the db as sqk2k compat.?

thanks

Databases created against SQL Server 2005 cannot be detached and attached to SQL Server 2000. You should be able do an export/import.

Dan

Create SQL install script

I want to create a SQL script like the one for Northwind... that creates the DB with data (instnwnd.sql).

I have the DB running I just don't know how to add all the tables and content into a file.

In mySQL I would just use phpmy admin to create the scipt... just don't know how in MS SQL.

Any help is greatly appreciated.

If you got an existing database to can just right click the mouse in MS SQL 2005 and generate the sql script, you can put in in the clipboard or on a script.sql file.

Do you have MS SQL 2005? 2000 or express?

|||

I have 2005... I have tried to do that but I only get the tables and not the data.

Thanks!

|||

AH!

If you want the data, you'll have to Export and you can still select a file or another database.

|||

Doh... I was in SQL Express. I also have SQL 2005. I know see it...

Thanks!

|||

Well, it's Saturday, minds are a little confused on Saturdays

|||

Here is article that describes the way it could be made.http://www.denovations.com/articles/

Unfortunately there is no standard way to do it.

Create SQL 2000 user accounts with a script

> Is it possible to create multiple user accounts within SQL 2000 via a
> script? We have a database on a development server that is overwritten
> every night. I would like to be able to create a script to create the
> user
> accounts needed. Any help or guidance would be appreciated. Thank you.
> scott
Sure - lookup sp_addlogin and sp_grantdbaccess in BOL. It's likely that you
only need to grant existing logins access to the recreated database. I also
suggest reviewing sp_change_users_login - this will allow to synchronize the
users in the restored database with the correct login.Is it possible to create multiple user accounts within SQL 2000 via a
script? We have a database on a development server that is overwritten
every night. I would like to be able to create a script to create the user
accounts needed. Any help or guidance would be appreciated. Thank you.
scott|||> Is it possible to create multiple user accounts within SQL 2000 via a
> script? We have a database on a development server that is overwritten
> every night. I would like to be able to create a script to create the
> user
> accounts needed. Any help or guidance would be appreciated. Thank you.
> scott
Sure - lookup sp_addlogin and sp_grantdbaccess in BOL. It's likely that you
only need to grant existing logins access to the recreated database. I also
suggest reviewing sp_change_users_login - this will allow to synchronize the
users in the restored database with the correct login.|||It's very easy to write a script to do this, and then to set up a SQL Job to
run the script sortly after the database is replaced.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"spburke" <spburke@.discussions.microsoft.com> wrote in message
news:4B6B03EC-1227-4009-B7E5-EF8260BAA502@.microsoft.com...
> Is it possible to create multiple user accounts within SQL 2000 via a
> script? We have a database on a development server that is overwritten
> every night. I would like to be able to create a script to create the
> user
> accounts needed. Any help or guidance would be appreciated. Thank you.
> scott|||It's very easy to write a script to do this, and then to set up a SQL Job to
run the script sortly after the database is replaced.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"spburke" <spburke@.discussions.microsoft.com> wrote in message
news:4B6B03EC-1227-4009-B7E5-EF8260BAA502@.microsoft.com...
> Is it possible to create multiple user accounts within SQL 2000 via a
> script? We have a database on a development server that is overwritten
> every night. I would like to be able to create a script to create the
> user
> accounts needed. Any help or guidance would be appreciated. Thank you.
> scott|||scott,
email me at jrvickers@.hotmail.com (subject:msdn) and i'll email you back the
VBSCript file I use to create server databases and accounts for my network.
I
use it in conjunction with creating user accounts on our w2k3 AD Domain.
Jamie.
"spburke" wrote:

> Is it possible to create multiple user accounts within SQL 2000 via a
> script? We have a database on a development server that is overwritten
> every night. I would like to be able to create a script to create the use
r
> accounts needed. Any help or guidance would be appreciated. Thank you.
> scott

Create Script with dependent objects SQL Server 2005

Hi,

I need to script a view but with dependent objects. The view has a lot of user defined functions and manually creating the script is a pain.

There is a way to do it in SQL Server (2000) Enterprise manager where one right clicks the view --> All Tasks --> Generate Script and then Check

"Generate Scripts for all dependent objects" in the Formatting tab.

The db i am using is on SQL Server 2005.

Is there a equivalent way to do this in SQL Server 2005 Management Studio ?

regards,

Rohan Wali

Have you tried the generate script wizard in SSMS?|||You might try using SMO in this case http://www.sqlteam.com/item.asp?ItemID=23185, as I don't see using script wizard.|||

Hi,

Even i dont see a solution using the script wizard. I will try using SMO using the link provided and see how it goes from there.

Thanks for the tip.

regards,

Rohan Wali

sql

Create script to insert 200 rows into table

I have to create a script to install a database, and one of the tables
has about 200 rows of static data... I dont want to have to manually
type in 200 insert statements, so is there a better way to do this? I
thought about maybe exporting the data into a CSV file and using some
sort of procedure to insert the records that way... Any advise?I did some research and discovered the lovely BCP utility. With this
utility i was able to export the data into a basic txt file using this
as a template:

bcp "SELECT * FROM pubs..authors" queryout authors.txt -U garth -P pw -
c

However I can find any resources on how I would go about putting the
data into the table...
If anyone could please use the above example, as the export and
provide me with a proper import that would be absolutely wonderful.|||I figured out that I can just include a bulk insert statement in my
script to do this::

BULK INSERT tmpStList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR =
',')

however I can't figure out how to use a tab as the field terminator as
opposed to ,|||Well, all I did was change the bcp utility to create a CSV file
instead of the tab seperated file...

But when I try to run the bulk insert statement I get this error:

The BULK INSERT SQL construct or statement is not supported.

Error Message:

Cannont bulk load because the file "C:\scripts\attributes.txt" could
not be opened. Operating system error code 123(The filename,
directory name, or volume lable syntax is incorrect.)|||Perhaps you ran the BCP utility locally, then ran BULK INSERT on the
server?

Have you noticed yet that BCP works in both directions, IN as well as
OUT?

Roy Harvey
Beacon Falls, CT

On Wed, 15 Aug 2007 16:30:22 -0000, rhaazy <rhaazy@.gmail.comwrote:

Quote:

Originally Posted by

>Well, all I did was change the bcp utility to create a CSV file
>instead of the tab seperated file...
>
>But when I try to run the bulk insert statement I get this error:
>
>The BULK INSERT SQL construct or statement is not supported.
>
>
>Error Message:
>
>Cannont bulk load because the file "C:\scripts\attributes.txt" could
>not be opened. Operating system error code 123(The filename,
>directory name, or volume lable syntax is incorrect.)

|||My problem was that I was using the wrong instance of sql server...I
was trying to use sql server express, which doesn't support the bulk
insert. After I changed the instance I had no problem getting it to
work. Thanks for your response though.|||SQL Script Builder is a multiple platform database migration tool, it
create a database sql script (or dump file) from any ODBC data source.
Scripts are available in 5 output formats ; MySql, MS SQL, Oracle,
Pervasive and PostgreSQL. The script produced will migrate the
database (multiple tables selection) or only one table. SQL Script
Builder can be used for example to migrate your Access database to
MySql database, or MySql database to MS SQL database and vice
versa.There's no limits, all you need is the ODBC driver for the
database you wish to import from.

More Info: http://www.sqlscriptbuilder.com
Download URL: http://www.sqlscriptbuilder.com/dow...uildersetup.exe
Screenshot URL: http://www.sqlscriptbuilder.com/images/Interface.jpg
Best regards,
David

CREATE script that filters out empty columns

All:
Say I need to duplicate a table, but the CREATE script must only
include those columns of the table where the value in ALL the available
rows is not null. For an ad-hoc exercise (one or two tables), this is
easy, but for duplicating, say, 90 tables with the empty columns
filtered out, I assume I need an SP that uses each table's metadata to
test each column individually, for each table. A temp table could then
keep the name of those non-empty columns, and the script would
recreate the new table's script from the resulting set.
If someone can suggest a script to do this, I'll be more than happy...BTW, I do know that information_schema.columns is involved... I know
what the logic should be, I simply don't know how to translate that
logic into T-SQL well enough to be efficient... and maybe VB.NET should
be involved, instead should be something like:
(code to write the beginning of the CREATE TABLE statement, plus the
first bracket)
For all tables in the database
For each column in current_table
SELECT DISTINCT (current_column) , COUNT(*) FROM current_table
GROUP BY (current_column)
If (COUNT(*) >= 1 AND (individual value in the column) <> NULL
then /* This implies that the only value there is not NULL */
(write the name of current_column to a file, plus its data
type and width, and a
comma if not the last column)
end if
next column
next table
(write the closing bracket)
Any suggestions?

Create script in 2005 without the [] delimiters around the object names

Anyone know how to permanently set up SQL 2005 Server Management Studio to NOT put those silly [ and ] delimiters around object names when using the CREATE SCRIPT to... funtionality from object explorer?

Moving thread to the Tools General forum because they'll be better able to answer your question.

-Jeffrey

Create Script Database

Hi, i want to know if there is a tool like scptxfr in SQL2005, in order to generate a script of all database.

I need this , because i have to make a job that automatically generates script of all database every day for backup.

Thks.

There is no such tool with SQL Server 2005. However you could create such a tool very with SMO with not much effort, depeding on what you want to do:

http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx

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

Create Procedure syntax error

Hi,
I'm having a problem where a CREATE PROCEDURE call with syntax error in
it is not being reported when included in a large script.
If I create a script of the individual CREATE PROCEDURE call on it's
own and run it, the syntax error is reported.
I am running the scripts using Query Analyzer, SQL Server 2000. Does
this problem sound familiar to anyone?
Thanks,
NeelDoes it have a GO before and after? Do you get the error if you try to run
the script from the previous create to the next object?
<neelpunna@.hotmail.com> wrote in message
news:1126724981.896558.20590@.z14g2000cwz.googlegroups.com...
> Hi,
> I'm having a problem where a CREATE PROCEDURE call with syntax error in
> it is not being reported when included in a large script.
> If I create a script of the individual CREATE PROCEDURE call on it's
> own and run it, the syntax error is reported.
> I am running the scripts using Query Analyzer, SQL Server 2000. Does
> this problem sound familiar to anyone?
> Thanks,
> Neel
>

Create procedure error on computed column.

I have the following script that was generated using SMO:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[proc_InsertCaseNote]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Erin D. Rowley

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[proc_InsertCaseNote]

-- Add the parameters for the stored procedure here

@.ReasonCodeSubCategoryID int,

@.OrderGroupID uniqueidentifier,

@.NoteText text,

@.CustomerEmail varchar(75),

@.EmployeeFirstName varchar(255),

@.EmployeeLastName varchar(255)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)

values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY

END

GO

But when I try to run it (in SQL Management Studio) I get the following error:

Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18

The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any ideas on how to debug this problem?

Thank you.

Kevin

Please post the table DDL.|||

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

|||

Arnie Rowland wrote:

Please post the table DDL.

Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.

Kevin

|||

Louis Davidson wrote:

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.

Kevin

|||

Right click the table in SSMS, click "Script table to..."

The error is not really all that strange, it is not letting your procedure do something that won't work.

|||

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

|||

Tom Werz wrote:

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

The table looks like:

/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]

The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.

Reproduced here for reference.

USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY
END

Thank you for your suggestions.

Kevin

Create Partition using ASSL

Hi,

Is there any sample code about create partition using ASSL (Analysis Service Script Language)?

thanks,

There is no ASSL. There is a DDL - data definition language for Analysis Services objects.

It is XML-based. Try in the SQL Managment Studio to right click on your partition and script it into the XMLA editor. You will see DDL definition of the partition. You can modify it, change the partition ID and name and send back to the server. You will see new partition being created as result of that.

Thanks.
Edward

This posting is provided "AS IS" with no warranties, and confers no rights

|||thanks a lot. :)

Create objects calling other scripts.

Hello,
Can anybody tell me, how can i create one script that calls other scripts.
For example, i need to create object1, object2, object3 but for control
created releases i cant create my objects in the same script, so all that i
want its to create one 'run_all.sql' script that calls
'object1.sql','object2.sql', 'object3.sql'.
Can you give me any ideas, i've done this with isql or osql but i always
need to authenticate my self.
Thanks and best regards
A workaround would be to use a batch file to call osql for each script you
have.
Cristian Lefter, SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CC7ABEB1-AA65-468B-904A-88E42D5D2EDB@.microsoft.com...
> Hello,
> Can anybody tell me, how can i create one script that calls other scripts.
> For example, i need to create object1, object2, object3 but for control
> created releases i cant create my objects in the same script, so all that
> i
> want its to create one 'run_all.sql' script that calls
> 'object1.sql','object2.sql', 'object3.sql'.
> Can you give me any ideas, i've done this with isql or osql but i always
> need to authenticate my self.
> Thanks and best regards