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 table from query results

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1

ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanksare you sure you need to store the concatenation in a separate table?SELECT ID, COL1, COL2, COL1+COL2 AS COL3
INTO TABLE2
FROM TABLE1|||thanks Rudy

yes I'll be storing the concatenated field in a new table

will that procedure create automatically the table2 even if i don't use CREATE TABLE?

alex

Create table and Index

Hello,
I am using SQL server 2000 with SP4. I am running create table and index as
below
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
I am trying to create table in TEST file group and Index in TEST1 file
group. But table and index are both getting created in TEST1 file group. If I
execute just Creat Table statement alone, then the table is getting created
in TEST file group, but if I execute both statements together, the table and
Index is getting created in TEST1.
Any help on this is really appreciated.
Thank you
-mvsWhat happens if you replace it with this?
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
GO
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
http://sqlservercode.blogspot.com/
"mvs" wrote:
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. If I
> execute just Creat Table statement alone, then the table is getting created
> in TEST file group, but if I execute both statements together, the table and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||A clustered index *is* the table (the leaf level of the index are the data pages). Hence, you cannot
separate a clustered index from the data pages, by definition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. If I
> execute just Creat Table statement alone, then the table is getting created
> in TEST file group, but if I execute both statements together, the table and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||Hi,
I tried it, it is same problem.
--
mvs
"SQL" wrote:
> What happens if you replace it with this?
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> GO
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> http://sqlservercode.blogspot.com/
>
> "mvs" wrote:
> > Hello,
> > I am using SQL server 2000 with SP4. I am running create table and index as
> > below
> >
> > CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> > NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> >
> > CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> > ON TEST1
> >
> > I am trying to create table in TEST file group and Index in TEST1 file
> > group. But table and index are both getting created in TEST1 file group. If I
> > execute just Creat Table statement alone, then the table is getting created
> > in TEST file group, but if I execute both statements together, the table and
> > Index is getting created in TEST1.
> >
> > Any help on this is really appreciated.
> > Thank you
> > -mvs
> >|||My bad I didn't realize it was a clustered index
"mvs" wrote:
> Hi,
> I tried it, it is same problem.
> --
> mvs
>
> "SQL" wrote:
> > What happens if you replace it with this?
> > CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> > NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> >
> > GO
> >
> > CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> > ON TEST1
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> > "mvs" wrote:
> >
> > > Hello,
> > > I am using SQL server 2000 with SP4. I am running create table and index as
> > > below
> > >
> > > CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> > > NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> > >
> > > CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> > > ON TEST1
> > >
> > > I am trying to create table in TEST file group and Index in TEST1 file
> > > group. But table and index are both getting created in TEST1 file group. If I
> > > execute just Creat Table statement alone, then the table is getting created
> > > in TEST file group, but if I execute both statements together, the table and
> > > Index is getting created in TEST1.
> > >
> > > Any help on this is really appreciated.
> > > Thank you
> > > -mvs
> > >|||MVS,
Table data by default is stored in a heap structure (unsorted set of data
pages). When you create a clustered index on the table the data is copied
to a contiguous set of pages and physically sorted based on the clustered
index key. Hence you either have a heap structure for a table (INDID = 0
sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
your first statement does create the table ON TEST and your second statement
moves the data, sorts it ON TEST1. So if you want the data to be on TEST
use ON TEST in both statement conversely if you want the data to be on TEST1
use ON TEST! in both statements. That being said your nonclustered indexes
can exist on different filegroup than the clustered index (table).
HTH
Jerry
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index
> as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777
> (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group.
> If I
> execute just Creat Table statement alone, then the table is getting
> created
> in TEST file group, but if I execute both statements together, the table
> and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||Thank you all. Now it really helped me.
--
mvs
"Jerry Spivey" wrote:
> MVS,
> Table data by default is stored in a heap structure (unsorted set of data
> pages). When you create a clustered index on the table the data is copied
> to a contiguous set of pages and physically sorted based on the clustered
> index key. Hence you either have a heap structure for a table (INDID = 0
> sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
> your first statement does create the table ON TEST and your second statement
> moves the data, sorts it ON TEST1. So if you want the data to be on TEST
> use ON TEST in both statement conversely if you want the data to be on TEST1
> use ON TEST! in both statements. That being said your nonclustered indexes
> can exist on different filegroup than the clustered index (table).
> HTH
> Jerry
> "mvs" <mvs@.discussions.microsoft.com> wrote in message
> news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> > Hello,
> > I am using SQL server 2000 with SP4. I am running create table and index
> > as
> > below
> >
> > CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> > NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> >
> > CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777
> > (t_creg)
> > ON TEST1
> >
> > I am trying to create table in TEST file group and Index in TEST1 file
> > group. But table and index are both getting created in TEST1 file group.
> > If I
> > execute just Creat Table statement alone, then the table is getting
> > created
> > in TEST file group, but if I execute both statements together, the table
> > and
> > Index is getting created in TEST1.
> >
> > Any help on this is really appreciated.
> > Thank you
> > -mvs
> >
>
>

CREATE TABLE [database . [schema1 ] . | schema1. ] tableName

Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
as below. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
Create Schema.TableName (No Problem)
Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Create Databasename,Schema1.schema2.TableName (Error below)
Msg 117, Level 15, State 1, Line 13
The object name 'DatabasName.Schema1. Schema2.TableName' contains more than
the maximum number of prefixes. The maximum is 2.
Msg 319, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.A table can belong to only a single schema. The pipe in the syntax is a
choice, not a concatonation. Things like below are valid ways to specify
a table name
CREATE TABLE database..name
schema is the default schema for the user
CREATE TABLE database.schema.name
database is the database in use
CREATE TABLE schema.name
CREATE TABLE name
database is the database in use and schema is the default schema for the use
r
Dan

> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> as below. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
> Create Schema.TableName (No Problem)
> Create Schema1.schema2.TableName (Problem SQL see schema1 as the
> database)
> Create Databasename,Schema1.schema2.TableName (Error below)
> Msg 117, Level 15, State 1, Line 13
> The object name 'DatabasName.Schema1. Schema2.TableName' contains more
> than
> the maximum number of prefixes. The maximum is 2.
> Msg 319, Level 15, State 1, Line 84
> Incorrect syntax near the keyword 'with'. If this statement is a
> common
> table expression or an xmlnamespaces clause, the previous statement
> must be
> terminated with a semicolon.|||Hi Dan,
I like to use 2 schema names as in the syntax.
Thank you,
Rune
"Dan Sullivan" wrote:

> A table can belong to only a single schema. The pipe in the syntax is a
> choice, not a concatonation. Things like below are valid ways to specify
> a table name
>
> CREATE TABLE database..name
> schema is the default schema for the user
>
> CREATE TABLE database.schema.name
>
> database is the database in use
> CREATE TABLE schema.name
>
> CREATE TABLE name
> database is the database in use and schema is the default schema for the u
ser
>
> Dan
>
>
>|||The syntax does not support two schema names. Look up "Documentation convent
ions"
in the BOL to see how this syntax is defined. If you want to use a "." in
a schema name then the name must be enclosed in [] or ""
CREATE SCHEMA [s1.s2]
CREATE TABLE [s1.s2].table_name
Dan
> Hi Dan,
> I like to use 2 schema names as in the syntax.
> Thank you,
> Rune
> "Dan Sullivan" wrote:
>|||> I like to use 2 schema names as in the syntax.
Yeesh. That is NOT what the syntax describes! | is or, not and! Meaning,
database.schema.table or schema.table. You cannot nest schemas. A schema
cannot own a schema. Period.

Create SQL sever 2005 clustering in existing non cluster SQL 2005

Hi (I send one before but looklike it did not pass through)
I have questions as below.
1) On production server win 2003 64 bits, I can not see Cluster by
ClusterAdmin, Quarum is the folder in C:\Drive, SAN is only shared drive, SQL
logon by local system account, Sql connect as (local) or ComputerName from
Management Studio. Get info from my boss, it was SQL server 2005 Ent
clustering on this server. What else should I check?
2) Get info that I need to add just only SQL Virtual server name and IP
address to make it clustering. If item#1 is true, How? Any modification?
In the case I found single instance, I will detach databases, uninstall
SQL server, make cluster, reinstall and attached databasesto shared disk but
this scenario is beyond my knowledge.
This is new to me, please help.
Thanks
Please rephrase the question. Are you asking what to do if the cluster fails
and can't be found in cluster administrator or are you asking how to
reinstall sql.
It sounds like a little of both.
"James" wrote:

> Hi (I send one before but looklike it did not pass through)
> I have questions as below.
> 1) On production server win 2003 64 bits, I can not see Cluster by
> ClusterAdmin, Quarum is the folder in C:\Drive, SAN is only shared drive, SQL
> logon by local system account, Sql connect as (local) or ComputerName from
> Management Studio. Get info from my boss, it was SQL server 2005 Ent
> clustering on this server. What else should I check?
> 2) Get info that I need to add just only SQL Virtual server name and IP
> address to make it clustering. If item#1 is true, How? Any modification?
> In the case I found single instance, I will detach databases, uninstall
> SQL server, make cluster, reinstall and attached databasesto shared disk but
> this scenario is beyond my knowledge.
> This is new to me, please help.
> Thanks
|||Thank Burt,
I am sorry for confusion.
First: In my scenario, I open ClusterAdmin but can not found any cluster in
that server, can not found quorum disk and SQL server start up logon as Local
account.
My boss told me that this is SQL server 2005 Clustering on wins 2003 64
bits. I do not beleive this is true.
--> I want to ask, How can I confirm it was true as my boss said?
Do I have enough fact as above to tell there have no clustering in this
server?
Second: If my boss correct, he said I need to create SQL virtual name and IP
address to make it clustering no need to reinstall SQL server 2005.
-->I want to ask, What should I do?
If my boss misunderstand what is the other fact that I need to support my
idea?
Thanks you so much.
"burt_king" wrote:
[vbcol=seagreen]
> Please rephrase the question. Are you asking what to do if the cluster fails
> and can't be found in cluster administrator or are you asking how to
> reinstall sql.
> It sounds like a little of both.
>
> --
> "James" wrote:
|||The simplest thing to do is ask him what the virtual name of the server is.
Then go to a command prompt on the server and enter "hostname" as the
command. The return value should be different than the virtual server name.
If you're using cluster administrator correctly (note that you can look at
local as well as remote clusters) then it sounds like it's not a cluster.
--DatabaseAdmins.com, LLC
Remote DBA Services for SQL and Oracle.
"James" wrote:
[vbcol=seagreen]
> Thank Burt,
> I am sorry for confusion.
> First: In my scenario, I open ClusterAdmin but can not found any cluster in
> that server, can not found quorum disk and SQL server start up logon as Local
> account.
> My boss told me that this is SQL server 2005 Clustering on wins 2003 64
> bits. I do not beleive this is true.
> --> I want to ask, How can I confirm it was true as my boss said?
> Do I have enough fact as above to tell there have no clustering in this
> server?
> Second: If my boss correct, he said I need to create SQL virtual name and IP
> address to make it clustering no need to reinstall SQL server 2005.
> -->I want to ask, What should I do?
> If my boss misunderstand what is the other fact that I need to support my
> idea?
> Thanks you so much.
> "burt_king" wrote:
|||Thank ypu again Burt,
I ask her and she reply that we just delete the clustering out but the SQL
that create on clustering still run. (I have no idea, but it is working so I
think it is not install in clustering)
They will create new windows clustering again. The hard part is
"they do not allow me to reinstall or create name instance but want me to
only add SQL virtual name and IP address and make it clustering".
--> last question: Is it possible to make SQL server 2005 local(I believe it
is local) to be SQL server 2005 clustering by add SQL virtual name and IP
address without reinstall (or new install) after network guy create windows
clustering? If yes,How?
Thank you so much.
"burt_king" wrote:
[vbcol=seagreen]
> The simplest thing to do is ask him what the virtual name of the server is.
> Then go to a command prompt on the server and enter "hostname" as the
> command. The return value should be different than the virtual server name.
> If you're using cluster administrator correctly (note that you can look at
> local as well as remote clusters) then it sounds like it's not a cluster.
> --
> --DatabaseAdmins.com, LLC
> Remote DBA Services for SQL and Oracle.
>
> "James" wrote:
|||Did you run the command at the command line and see what was returned? It's
hard to give good advice here because it seems like we're not getting the
whole picture.
Additionally, it sounds like your manager is asking if an existing instance
that is not clustered can be made a cluster without reinstalling: to my
knowledge no.
HTH
"James" wrote:
[vbcol=seagreen]
> Thank ypu again Burt,
> I ask her and she reply that we just delete the clustering out but the SQL
> that create on clustering still run. (I have no idea, but it is working so I
> think it is not install in clustering)
> They will create new windows clustering again. The hard part is
> "they do not allow me to reinstall or create name instance but want me to
> only add SQL virtual name and IP address and make it clustering".
> --> last question: Is it possible to make SQL server 2005 local(I believe it
> is local) to be SQL server 2005 clustering by add SQL virtual name and IP
> address without reinstall (or new install) after network guy create windows
> clustering? If yes,How?
>
> Thank you so much.
> "burt_king" wrote:
|||Thanks
As they said they delete the windows cluster out, and do not have SQL
virtual name. the hostname command show the server name when run that command.
Thank you for your answer.
Just coming up in my head, If you install SQL server 2005 over windows
clustering and you delete windows clustering out. Can SQL server still
survive or running? I never delete windows clustering before.
Thank you so much.
James
"oracleSQLdba" wrote:
[vbcol=seagreen]
> Did you run the command at the command line and see what was returned? It's
> hard to give good advice here because it seems like we're not getting the
> whole picture.
> Additionally, it sounds like your manager is asking if an existing instance
> that is not clustered can be made a cluster without reinstalling: to my
> knowledge no.
> HTH
>
> "James" wrote:
|||Sorry, what to you mean, "delete the windows cluster out"?
If you mean, can you uninstall windows cluster services, the answer is yes.
But if you're at the virtual server and run the hostname command and receive
that same name back then you're not likely to be on a cluster.
I think you may be best served by getting a consultant in there for a day to
look at what you've got and help you.
"James" wrote:
[vbcol=seagreen]
> Thanks
> As they said they delete the windows cluster out, and do not have SQL
> virtual name. the hostname command show the server name when run that command.
> Thank you for your answer.
> Just coming up in my head, If you install SQL server 2005 over windows
> clustering and you delete windows clustering out. Can SQL server still
> survive or running? I never delete windows clustering before.
> Thank you so much.
> James
> "oracleSQLdba" wrote:

CREATE SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
Ganesh
Hi
If I understand
http://msdn.microsoft.com/library/de.../sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:
> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/d...r />
k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefiniti
on. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements o
f
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

Create related tables from XML schema

I'm new to XML.
I received a schema from one of our vendors (please see below)
I would like to create SQLserver tables required for exporting XML file
later using this schema.
Is there a way to do this in SQLserver?
Thanks
Bill
<CDData> XML Schema
<?xml version="1.0"?>
<!-- CD Schema Basic version 3.2.1 3/2/2004 -->
<!-- SubElements and Attributes for each Element must appear in order
shown -->
<Schema name="CD_Routedata_XML_Schema"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name='PRODUCT' content='eltOnly' model='closed'>
<AttributeType name='PRODNAME' dt:type='string' required='no'/>
<AttributeType name='PRODID' dt:type='string' required='no'/>
<AttributeType name='INVOICE' dt:type='string' required='no'/>
<AttributeType name='QTY' dt:type='number' required='no'/>
<Attribute type='PRODNAME'/>
<Attribute type='PRODID'/>
<Attribute type='INVOICE'/>
<Attribute type='QTY'/>
</ElementType>
<ElementType name='PRODUCTS' content='eltOnly' model='closed'>
<Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
</ElementType>
<ElementType name='ACCTNAME' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ACCTID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
model='closed'/>
<ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='DURATION' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='DEPART' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='CODE' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='STOP' content='eltOnly' model='closed'>
<AttributeType name='NUM' dt:type='ui2' required='yes'/>
<Attribute type='NUM'/>
<Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
<Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
<Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
<Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
<Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
<Element type='DURATION' minOccurs='0' maxOccurs='1'/>
<Element type='DEPART' minOccurs='0' maxOccurs='1'/>
<Element type='CODE' minOccurs='0' maxOccurs='1'/>
<Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='DRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='CODRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ROUTE' content='eltOnly' model='closed'>
<AttributeType name='DATE' dt:type='datetime' required='yes'/>
<AttributeType name='SITEID' dt:type='string' required='yes'/>
<AttributeType name='ROUTEID' dt:type='string' required='yes'/>
<Attribute type='DATE'/>
<Attribute type='ROUTEID'/>
<Attribute type='SITEID'/>
<Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
<Element type='STOP' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
<AttributeType name='ADD' dt:type='string' required='no'/>
<Attribute type='ADD'/>
<Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='CADECDATA' content='eltOnly' model='closed'>
<Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='BASE' content='eltOnly' model='open'>
<Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
</ElementType>
</Schema>
Look at the SchemaGen option of the SQLXML XML Bulkload object.
Best regards
Michael
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:eCA47z88GHA.3280@.TK2MSFTNGP02.phx.gbl...
> I'm new to XML.
> I received a schema from one of our vendors (please see below)
> I would like to create SQLserver tables required for exporting XML file
> later using this schema.
> Is there a way to do this in SQLserver?
> Thanks
> Bill
> --
> <CDData> XML Schema
> <?xml version="1.0"?>
> <!-- CD Schema Basic version 3.2.1 3/2/2004 -->
> <!-- SubElements and Attributes for each Element must appear in order
> shown -->
> <Schema name="CD_Routedata_XML_Schema"
> xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:datatypes">
> <ElementType name='PRODUCT' content='eltOnly' model='closed'>
> <AttributeType name='PRODNAME' dt:type='string' required='no'/>
> <AttributeType name='PRODID' dt:type='string' required='no'/>
> <AttributeType name='INVOICE' dt:type='string' required='no'/>
> <AttributeType name='QTY' dt:type='number' required='no'/>
> <Attribute type='PRODNAME'/>
> <Attribute type='PRODID'/>
> <Attribute type='INVOICE'/>
> <Attribute type='QTY'/>
> </ElementType>
> <ElementType name='PRODUCTS' content='eltOnly' model='closed'>
> <Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
> </ElementType>
> <ElementType name='ACCTNAME' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ACCTID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
> model='closed'/>
> <ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='DURATION' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='DEPART' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='CODE' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='STOP' content='eltOnly' model='closed'>
> <AttributeType name='NUM' dt:type='ui2' required='yes'/>
> <Attribute type='NUM'/>
> <Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
> <Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
> <Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
> <Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
> <Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
> <Element type='DURATION' minOccurs='0' maxOccurs='1'/>
> <Element type='DEPART' minOccurs='0' maxOccurs='1'/>
> <Element type='CODE' minOccurs='0' maxOccurs='1'/>
> <Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='DRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='CODRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ROUTE' content='eltOnly' model='closed'>
> <AttributeType name='DATE' dt:type='datetime' required='yes'/>
> <AttributeType name='SITEID' dt:type='string' required='yes'/>
> <AttributeType name='ROUTEID' dt:type='string' required='yes'/>
> <Attribute type='DATE'/>
> <Attribute type='ROUTEID'/>
> <Attribute type='SITEID'/>
> <Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
> <Element type='STOP' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
> <AttributeType name='ADD' dt:type='string' required='no'/>
> <Attribute type='ADD'/>
> <Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='CADECDATA' content='eltOnly' model='closed'>
> <Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='BASE' content='eltOnly' model='open'>
> <Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> </Schema>
>

CREATE FTC Failing for

Trying to create a catalog as seen below:

CREATE FULLTEXT INDEX ON [dbo].[AttachFiles](

[BinFile])

KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]

WITH CHANGE_TRACKING OFF

GO

Getting error of:

Msg 7655, Level 16, State 1, Line 1

TYPE COLUMN option must be specified with column of image or varbinary(max) type.

BinFile is an Image datatype.

What do I need to update on my CREATE statement above to make this work?

When you use an IMAGE or VARBINARY(MAX) field for a full-text index, you have to tell the service how to read the binary image. It has a set of extensions installed with which it can read these. You can see the list by executing a "SELECT * FROM sys.fulltext_document_types" query in the full-text enabled database. To tell the service what image type is stored in the field, you need another field that holds the type name.

So, let's assume Dossiers_FTC holds MS Word Document files. Let's also assume you have a field in your table named "BinType" that contains the string ".doc". Your statement would look like this:

CREATE FULLTEXT INDEX ON [dbo].[AttachFiles]

([BinFile] TYPE COLUMN [BinType]) KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]

WITH CHANGE_TRACKING OFF

GO

Create Excel Output

I successfully use the code below to output a report to a web page or to a
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>

Create Error Message and Abort

I would like to place the sql statement listed below in a SQL Server
Enterprise Manager job if this is true abort the job and send message.
If (select value from salary) < 10
Error Message 'Salary Less Than 10'
Joe,
Are you trying to create a user-defined error message and invoke it in code?
If so use sp_addmessage and the T-SQL RAISERROR statment. See SQL BOL for
more details.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9BB22A3F-2617-4A06-A23E-F216250850AF@.microsoft.com...
>
> I would like to place the sql statement listed below in a SQL Server
> Enterprise Manager job if this is true abort the job and send message.
> If (select value from salary) < 10
> Error Message 'Salary Less Than 10'
>

Create Error Message and Abort

I would like to place the sql statement listed below in a SQL Server
Enterprise Manager job if this is true abort the job and send message.
If (select value from salary) < 10
Error Message 'Salary Less Than 10'Joe,
Are you trying to create a user-defined error message and invoke it in code?
If so use sp_addmessage and the T-SQL RAISERROR statment. See SQL BOL for
more details.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9BB22A3F-2617-4A06-A23E-F216250850AF@.microsoft.com...
>
> I would like to place the sql statement listed below in a SQL Server
> Enterprise Manager job if this is true abort the job and send message.
> If (select value from salary) < 10
> Error Message 'Salary Less Than 10'
>

Create Error Message and Abort

I would like to place the sql statement listed below in a SQL Server
Enterprise Manager job if this is true abort the job and send message.
If (select value from salary) < 10
Error Message 'Salary Less Than 10'Joe,
Are you trying to create a user-defined error message and invoke it in code?
If so use sp_addmessage and the T-SQL RAISERROR statment. See SQL BOL for
more details.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9BB22A3F-2617-4A06-A23E-F216250850AF@.microsoft.com...
>
> I would like to place the sql statement listed below in a SQL Server
> Enterprise Manager job if this is true abort the job and send message.
> If (select value from salary) < 10
> Error Message 'Salary Less Than 10'
>

create endpoint fails with error 0x800704be

I am trying to create an endpoint with the code below and get the following error

An error ('0x800704be') occurred while attempting to register the endpoint 'getFormsEP'.

Anyone any ideas on what this error means?

Please help if you can.

Regards, Major (that is my Christian name ;-)

Code

create endpoint getFormsEP
state = started
as http
(
site='ictdwt10mt',
path='/EndPoints',
authentication=(integrated),
ports=(clear),
clear_port=84
)
for soap
(
webmethod 'getForms'
(
name='EForms21.dbo.getForms',
schema=standard,
format=rowsets_only
),
wsdl=default,
login_type=windows,
database='EForms21',
schema=none
)

Additionl information.

I have now setup a server with .NET 3.0 and a development PC with .NET 3.0. I have applied the lastest OS fixes to both.

I have installed SQL 2005 Service Pack 2 on the server.

I am still getting this error.

Anyone from MS watching?

Please help if you can.

Regards, Major.

|||

Hi Major,

You need to set the namespace in order for this endpoint to bind because it serves as the final location or address of the bound endpoint.

Hope this helps,

John Gordon (MSFT)

<edit>

Sorry about that, I misunderstood the requirements. I will continue to look into this for you but Matt is probably going to beat me to a solution to your problem. I hope we get this fixed for you soon.

|||Hi Major,

What operating system are you running on?

Error code: (HRESULT) 0x800704be (2147943614) - The format of the specified network name is invalid. Or the constant error code is ERROR_INVALID_NETNAME.

Note I wasn’t able to replicate it by putting all sorts of invalid names as the site name when creating the endpoint.

This will come up when SQL tries to register the endpoint using httpaddurl API and it means the network name you specified in the site='zzz' tag is not valid from http.sys perspective. Try running ipconfig /all and verifying the site name you are using is correct.

Create database fails

HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
Suchi
Are these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:

> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>
|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:

Create database fails

HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
SuchiAre these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
> > HI All,
> >
> > Environment:
> >
> > Windows2003,
> >
> > SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> >
> > When I execute below simple create database command without specifying the
> > file name, i got below error. If i explicitly specify the file ( just mdf
> > file), the database is getting created.
> >
> >
> > CREATE DATABASE TESTDB
> >
> > Error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name '\TestDB.mdf' may be
> > incorrect.
> > Server: Msg 1802, Level 16, State 1, Line 1
> > CREATE DATABASE failed. Some file names listed could not be created. Check
> > previous errors.
> >
> >
> > The
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> > and
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> > are having same value ( E:\data )......
> >
> > Can any one tell me how to resolve this?
> >
> > Thanks,
> > Suchi
> >