Create Table within an IF statement causes error

This doesn't make any sense to me. I am trying to create a stored procedure that creates a temp table using T-SQL. The table will be created differently depending on the arguments passed. Here is an example of what I am trying to do:

DECLARE @.Switch bit

SET @.Switch = 0

IF @.Switch = 0

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (Zero int)

END

ELSE

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (One int)

END

SELECT * FROM #DontWork

If you run this as is, it fails stating that "There is already an object named '#DontWork' in the database." However if you comment out one of the CREATE TABLE statements (either one of them), it works fine. The PRINT @.Switch line will prove that the IF ELSE statement is evaluating properly if you change the value of @.Switch. My guess is that the parsing engine is throwing the error before it even tries to run the code. Is there any way to make this work the way it should? Or do I have to resort to creating 2 different tables and modifying the rest of my code to compensate for the change?

This is usually caused because you have ran a CREATE TABLE statement in a previous development iteration. Try appending this to the end of your code:

Code Snippet

go

drop table #DontWork

The temp table stays in scope after you run through one time so the next time through you get the error. Try hiliting the code I've given you and execute just the DROP TABLE. Then un-hilite the code and rerun query. It should run correctly once you have dropped the table.

OK, I'm all wet... Hang on.

You can alter it:

Code Snippet

create table #what (one int)

alter table #what
add two int

alter table #what
drop column one

select * from #what

go

drop table #what

/*
two
--
*/

|||

The code doesn't execute.

The parsing engine is attempting to resolve the objects, and (incorrectly, in my opinion) assumes that the second instance of the create table is attempting to make a second object with the same name. The parsing engine is resolving objects, not checking logic and code flow.

To test, comment out EITHER CREATE statement and the code executes.

Your options include creating the #Temp table before the IF statement, or using a different #Table name in the second instance.

Or you could have both switched locations call out to another procedure that creates the #Temp table.

|||My code already involves altering the table. I was just trying to use an IF statement because one scenario creates a predictable table structure, and the other side requires that the field names be calculated at run time. I was trying to save myself some effort by simply having that CREATE TABLE command in there twice, but it seems that because of what Arnie said about the parsing engine resolving objects, not checking logic and code flow, I'm going to have to do things the complicated way. I wish there was a way to communicate things like this to the powers that be at Microsoft. Any idea how to do that, if at all possible?|||

They do pay attention to the suggestions.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||MS SQL has always had a problem with this construct. The solution, as mentioned, is to create the table once, and then use alter table to change the table to what you want. Or just create 2 tables of different names.

|||

SQL Server compiles the entire batch (SP, trigger, function or ad-hoc) and compilation doesn't take into account run-time information (variable values, control of flow etc). This gets tricky for temporary tables because of the way they are scoped. For best performance and manageability, you should put the creation logic for the different conditions in their own SPs and the execution logic too. This provides better reusability. You could use the ALTER TABLE approach but that will give bad performance in SQL Server 2005 since it negates the caching that we do automatically on temporary tables (metadata & 1 page of allocation which can get reused). Of course, if you can remove the temporary tables altogether.

Btw, your code will work if you were creating a permanent table conditionally.

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 permission denied

Hello,
I hope this is something simple to fix, but so far I had no luck...
Situation:
I created sql login, let's say 'test' and allowed access to database
db1. Actually, I made the account as the db_owner of db1. The test
account was not added to any of the server roles. Now, everything is
working fine until I try to create table or view or proc, etc. in the
db1 while logged in as the test user.
When creating table I get that CREATE TABLE permission denied in
database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
sysadmin to that test account. That didn't help. IS there some DENY
somewhere that I don't see? How can I check what is preventing me from
creating table using that account?
Any comments?
Thanksgot it ... database role 'public' had DENY on creating tables. So even
if my test login had GRANT, the DENY on public denied for test as well
because test is public and db_owner.
laimis wrote:
> Hello,
> I hope this is something simple to fix, but so far I had no luck...
> Situation:
> I created sql login, let's say 'test' and allowed access to database
> db1. Actually, I made the account as the db_owner of db1. The test
> account was not added to any of the server roles. Now, everything is
> working fine until I try to create table or view or proc, etc. in the
> db1 while logged in as the test user.
> When creating table I get that CREATE TABLE permission denied in
> database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
> sysadmin to that test account. That didn't help. IS there some DENY
> somewhere that I don't see? How can I check what is preventing me from
> creating table using that account?
> Any comments?
> Thanks

Create table in schema with Enterprise Manager

How can I specify the schema I want the table to be created in with
Enterprise Manager 2005?
It justs asks for a table name and if you put a schema name in it just
treats it as part of the table name
Paul
Paul Hatcher (PaulHatcher@.discussions.microsoft.com) writes:
> How can I specify the schema I want the table to be created in with
> Enterprise Manager 2005?
> It justs asks for a table name and if you put a schema name in it just
> treats it as part of the table name
CREATE TABLE schemaname.tbl
Let the graphic tools be. They are some funny toys that have ended up in
the wrong place. And, whatever, never use them to change your tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Create table in schema

Hi, I'm trying to create a schema, and have the userid assigned to a role
have the ability to create tables just in this schema. I have created a rol
e
A_Role and want to assign all the permissions to the role. So I tried the
commands:
grant alter on schema::dds to A_Role
grant create table to dds_pco_role
Then I've added the userid to this role. Logged in as the user, but when I
try to create a table
create table dds.T1 (col1 int, col2 char(3))
I get the message:
The specified schema name "dds" either does not exist or you do not have
permission to use it.
And yes, the schema does exist. Am I missing another command?
Thanks,
MitcheYou granted the CREATE TABLE to dds_pco_role - not A_role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
Hi, I'm trying to create a schema, and have the userid assigned to a role
have the ability to create tables just in this schema. I have created a
role
A_Role and want to assign all the permissions to the role. So I tried the
commands:
grant alter on schema::dds to A_Role
grant create table to dds_pco_role
Then I've added the userid to this role. Logged in as the user, but when I
try to create a table
create table dds.T1 (col1 int, col2 char(3))
I get the message:
The specified schema name "dds" either does not exist or you do not have
permission to use it.
And yes, the schema does exist. Am I missing another command?
Thanks,
Mitche|||Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role.
"Tom Moreau" wrote:

> You granted the CREATE TABLE to dds_pco_role - not A_role.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> Hi, I'm trying to create a schema, and have the userid assigned to a role
> have the ability to create tables just in this schema. I have created a
> role
> A_Role and want to assign all the permissions to the role. So I tried the
> commands:
> grant alter on schema::dds to A_Role
> grant create table to dds_pco_role
> Then I've added the userid to this role. Logged in as the user, but when
I
> try to create a table
> create table dds.T1 (col1 int, col2 char(3))
> I get the message:
> The specified schema name "dds" either does not exist or you do not have
> permission to use it.
> And yes, the schema does exist. Am I missing another command?
> Thanks,
> Mitche
>
>|||Just trying to narrow things down. Try:
grant CONTROL on schema::dds to A_Role
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role.
"Tom Moreau" wrote:

> You granted the CREATE TABLE to dds_pco_role - not A_role.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> Hi, I'm trying to create a schema, and have the userid assigned to a role
> have the ability to create tables just in this schema. I have created a
> role
> A_Role and want to assign all the permissions to the role. So I tried the
> commands:
> grant alter on schema::dds to A_Role
> grant create table to dds_pco_role
> Then I've added the userid to this role. Logged in as the user, but when
> I
> try to create a table
> create table dds.T1 (col1 int, col2 char(3))
> I get the message:
> The specified schema name "dds" either does not exist or you do not have
> permission to use it.
> And yes, the schema does exist. Am I missing another command?
> Thanks,
> Mitche
>
>|||I'm getting:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
I also got that when I ran grant alter on schema. What does it mean?
"Tom Moreau" wrote:

> Just trying to narrow things down. Try:
> grant CONTROL on schema::dds to A_Role
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role
.
> "Tom Moreau" wrote:
>
>|||I think you maybe had run EXECUTE AS and didn't run REVERT. Thus, it thinks
you are the user you're pretending to be.
Run:
SELECT CURRENT_USER
and see what it says.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
I'm getting:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
I also got that when I ran grant alter on schema. What does it mean?
"Tom Moreau" wrote:

> Just trying to narrow things down. Try:
> grant CONTROL on schema::dds to A_Role
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not
> A_role.
> "Tom Moreau" wrote:
>
>|||OK, I'd just log out and back in as sa, just to be sure we're starting
clean. Then, run:
grant CONTROL on schema::dds to A_Role
After that, start a brand new window and log in as dds_user. Try creating
the table then.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
No, there's no EXECUTE AS in my script. And when I run select current_user
in the window that I'm trying to grant the rights (logged in as SA), I get
"dbo."
When I run select current_user in the window I'm trying to create the table,
logged in as dds_user, I get "dds_user."
"Tom Moreau" wrote:

> I think you maybe had run EXECUTE AS and didn't run REVERT. Thus, it
> thinks
> you are the user you're pretending to be.
> Run:
> SELECT CURRENT_USER
> and see what it says.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
> I'm getting:
> Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
> information_schema, sys, or yourself.
> I also got that when I ran grant alter on schema. What does it mean?
> "Tom Moreau" wrote:
>
>|||Same thing.
Have I set up the role incorrectly? Here's my script
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dds_user
')
CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OFF
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
'dds_user' and type = 'S')
CREATE USER [dds_user] FOR LOGIN [dds_user]
IF EXISTS(select name from sys.database_principals where name =
'dds_pco_role' and type = 'R')
DROP ROLE dds_pco_role
CREATE ROLE dds_pco_role AUTHORIZATION dds_user
IF NOT EXISTS(select name from sys.schemas where name = 'dds')
EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pco_
role]'
EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
-- grants for dds schema
grant control on schema::dds to dds_pco_role
grant create table to dds_pco_role
Thanks!
"Tom Moreau" wrote:

> OK, I'd just log out and back in as sa, just to be sure we're starting
> clean. Then, run:
> grant CONTROL on schema::dds to A_Role
> After that, start a brand new window and log in as dds_user. Try creating
> the table then.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> No, there's no EXECUTE AS in my script. And when I run select current_use
r
> in the window that I'm trying to grant the rights (logged in as SA), I get
> "dbo."
> When I run select current_user in the window I'm trying to create the tabl
e,
> logged in as dds_user, I get "dds_user."
> "Tom Moreau" wrote:
>
>|||This all worked for me. What I did was run the entire script in
AdventureWorks. I then opened a new window and then ran:
execute as user = 'dds_user'
Then, I ran:
create table dds.T1 (col1 int, col2 char(3))
It executed OK.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
Same thing.
Have I set up the role incorrectly? Here's my script
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name =
'dds_user')
CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OFF
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
'dds_user' and type = 'S')
CREATE USER [dds_user] FOR LOGIN [dds_user]
IF EXISTS(select name from sys.database_principals where name =
'dds_pco_role' and type = 'R')
DROP ROLE dds_pco_role
CREATE ROLE dds_pco_role AUTHORIZATION dds_user
IF NOT EXISTS(select name from sys.schemas where name = 'dds')
EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pco_
role]'
EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
-- grants for dds schema
grant control on schema::dds to dds_pco_role
grant create table to dds_pco_role
Thanks!
"Tom Moreau" wrote:

> OK, I'd just log out and back in as sa, just to be sure we're starting
> clean. Then, run:
> grant CONTROL on schema::dds to A_Role
> After that, start a brand new window and log in as dds_user. Try creating
> the table then.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> No, there's no EXECUTE AS in my script. And when I run select
> current_user
> in the window that I'm trying to grant the rights (logged in as SA), I get
> "dbo."
> When I run select current_user in the window I'm trying to create the
> table,
> logged in as dds_user, I get "dds_user."
> "Tom Moreau" wrote:
>
>|||I don't get it. What version of sql are you running?
Every time I try to grant control to the dds_pco_role, I get the message:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
And then I check the permissions in the sys.database_permissions table, and
that permission is not there. I don't get why it's not working?!?!?!
"Tom Moreau" wrote:

> This all worked for me. What I did was run the entire script in
> AdventureWorks. I then opened a new window and then ran:
> execute as user = 'dds_user'
> Then, I ran:
> create table dds.T1 (col1 int, col2 char(3))
> It executed OK.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mitch" <Mitch@.discussions.microsoft.com> wrote in message
> news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
> Same thing.
> Have I set up the role incorrectly? Here's my script
> IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name =
> 'dds_user')
> CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@.st3r', CHECK_EXPIRATION=OF
F
> IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
> 'dds_user' and type = 'S')
> CREATE USER [dds_user] FOR LOGIN [dds_user]
> IF EXISTS(select name from sys.database_principals where name =
> 'dds_pco_role' and type = 'R')
> DROP ROLE dds_pco_role
> CREATE ROLE dds_pco_role AUTHORIZATION dds_user
> IF NOT EXISTS(select name from sys.schemas where name = 'dds')
> EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pc
o_role]'
> EXEC sp_addrolemember 'dds_pco_role', 'dds_user'
> -- grants for dds schema
> grant control on schema::dds to dds_pco_role
> grant create table to dds_pco_role
> Thanks!
> "Tom Moreau" wrote:
>
>sql

Create table from text file, extract data, create new table from extracted data.

Hello all,

Please help...

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem...

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.select *
INTO ExtractedDataFile
from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%'|||Thanks so much Brett!

I have more question though......

I will be needing to do the importation of text file & extraction of data at least once a month. Then after I import & extract data I will need to append the extracted data into the table ExtractedDataFile. But I will only need to append the data if there is no duplicate DF_SC_Case_Nbr.

Can all this be done in just one stored procedure? How will I do this?

Thanks again.|||What's a duplicate?

Do you have books online?

Open it up, and leave it open...

Look into bcp

Create Table

I have created a table already but I want to create another table inside it. Is it even possible?You can't create tables within tables. You should create subtables with foreign key references to the primary key of the parent table instead.|||As blindman said, with the SQL Standards currently in use, you cannot create a table within another table. I've been told that the new SQL:2006 standard (which no database engine yet implements afaik) describes a new feature which accomplishes this task. Unfortunately, I've had to little time to study the SQL:2006 standard so I don't have any details.|||Do you get copies of the standard roac?|||Currently Google is my friend. But I will have my own copies, yes. They can be bought from http://webstore.ansi.org . As many of the upoming standards are drafts, they have a bit nicer price, $30.

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.
On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:

>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:
>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create project/stored procedure for SQLCRL

I installed SQLServer 2005 Standard Edition and tried to created a
stored procedure in VB. From START/PROGRAMS/MICROSOFT VISUAL STUDIO
2005, I created a blank solution. What type of project shall I create
for creating a stored procedure in VB ?

I tried to install SQL Server again in case I left back some parts, but
I got a message that all parts were installed.On 7 Jun 2006 00:37:56 -0700, Chris wrote:

>I installed SQLServer 2005 Standard Edition and tried to created a
>stored procedure in VB. From START/PROGRAMS/MICROSOFT VISUAL STUDIO
>2005, I created a blank solution. What type of project shall I create
>for creating a stored procedure in VB ?
>I tried to install SQL Server again in case I left back some parts, but
>I got a message that all parts were installed.

Hi Chris,

Create a "database" project, using the "SQL Server Project" template.
After that, you can choose the "Project" / "Add Stored Procedure" menu
choice to add a CLR stored procedure to your project.

--
Hugo Kornelis, SQL Server MVP|||Thanks a lot, Hugo.

Create Proc Parameter Issue

Hi All,
I have created a stored proc that is set to accept @.username =
varchar(40)...it fails when the username is FULLY qualified with Domain name
...ex: 'MyDomain\Username'...how do I get my procedure to except this FULL
name?
Thanks...M.Please post some sample code on how you execute that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michelle" <smiley2211@.yahoo.com> schrieb im Newsbeitrag
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||The datatype for usernames in SQL (as used in the system tables) is sysname,
which is equivalent to nvarchar(128). Use that instead of varchar(40).
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||Yes, I tried sysname as well...still errors: "Associated statement is not
prepared"
************snippet********
CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
set nocount on
declare @.dbn varchar(30)
declare test cursor for
select name from master..sysdatabases
etc....
*****************
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
> The datatype for usernames in SQL (as used in the system tables) is
> sysname, which is equivalent to nvarchar(128). Use that instead of
> varchar(40).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Sorry...I execute this as such...
sp_getprivs 'MyDomain\Username'
Thanks...M
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
> Yes, I tried sysname as well...still errors: "Associated statement is not
> prepared"
> ************snippet********
> CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
> set nocount on
> declare @.dbn varchar(30)
> declare test cursor for
> select name from master..sysdatabases
> etc....
> *****************
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
>|||Try using delimiters:
sp_getprivs '[MyDomain\Username]'
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
> Sorry...I execute this as such...
> sp_getprivs 'MyDomain\Username'
> Thanks...M
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
>|||Thanks, that worked...
...M
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ui3WLisZFHA.2884@.tk2msftngp13.phx.gbl...
> Try using delimiters:
> sp_getprivs '[MyDomain\Username]'
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
>

Create Percentage Calculated Measure... Getting the current Dimension

Hello,

I created a cube for surveys and would like to create a measure for the percentage of people (surveys) that answered Q1, Q2, Q3, etc. I have the following calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered Q2]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Surveys Count]

)

Then Null

Else ( [Q2].[Units].CurrentMember,

[Measures].[Surveys Count])

/

(

// The Root function returns the (All) value for the target dimension.

Root (), [Measures].[Surveys Count])

End ,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

Do I have to specify the dimension in the script (like [Q2].[Units].CurrentMember) ? That means I have to create one percentage calculation member for each of the questions (dimensions). Is there a way to get the current dimension?

I hope I explain my question clearly, please let me know if I can explain further.

Thank you very much,

Sincerely,

Annie

There is no such concept as "the current dimension". Any given cell in a cube is always defined by a coordinate of all attributes of all cube dimensions. From your description, it seems like you created one dimension per survey question. Then a coordinate in your cube space would consist of members from all question dimensions. You might have multiple measure groups in the cube, in which case a cube dimension may or may not relate to a given measure group.

In any case, the CurrentMember component in the tuple expression in your example is redundant and can be safely removed.

|||

Thank you so much for the prompt reply.

As I'm fairly new to MDX. Do you mean change the script to:

-

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered]

AS Case

// Test to avoid division by zero.

When IsEmpty([Measures].[Surveys Count]) Then 0

Else [Measures].[Surveys Count] /(Root (),[Measures].[Surveys Count])

End,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

-

This then gives an "#value" error.... Could you be a bit more specific?

Thank you so much,

Sincerely,

Annie

|||

The Root() function without argument returns a tuple containing members from all attribute hierarchies, including the measures hierarchy. The [Measures].[Surveys Count] component in your tuple will cause a duplicate hierarchy error.

Create or modify MSDE Database

Hi,
On Administrator session, I have created an CUSTOMER MSDE database. That's OK.

On user session, I want to access on my database CUSTOMER but I have on error : "Unable to connect to the database". And, in French : "Echec de la connexion de l'utilisateur Machine_Name/User_Name".

What is the error ? I don't know what to do any more.
How to give right to the user ? Have you an idea ?

Thank's.

Patrice A. BONNEFOY.Did you create a username to use to gain access to the MSDE database? If you're using Windows authentication then you need to add a user like Machine_Name/ASPNET. If you are using Sql authentication then you have to add some username or use an existing one.

This link may helpful for doing this|||Hi,
Thank you for your assistance. Now, I think of being able to repair me.

Best regards.
Patrice BONNEFOY.
www.pabonnefoy.net/

create only sp's during schema initialization

Hey, I've created the indexes and tables and stuff manually. I just need SQL
to create the 3 sp's on each table during Trans Repl. Does it do that or it's
all or nothing kind of situation?
Right now, I manually edited about 20 schema files from snapshot to not drop
and recreate the table. And, it takes time.
Tejas,
for a nosync initialization, if you run sp_scriptpublicationcustomprocs
'publicationname' at the publisher, the results (in text format) are 3
stored procedure creation scripts. These are then run on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||see, when you say, 'no, the subscriber already has the schema and data' even
the data doesn't get transferred over. How would I be able to do it through
replication process? or i have to sue bcp or dts or something EXTERNALLY?
Thank you.
|||Tejas,
now I'm confused I thought you were trying to achieve a nosync
initialization? If not, then the normal initialization process will take
care of the data transfer. Please can you clarify a bit more for me exactly
what you want to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
What had happened is this.
I was asked to script the tables and indexes from the publisher and run them
on the sub. Now, I cannot use the 'yes,initialize the schema' option as it
would overwrite all that. But at the same time, I could not use 'no, the sub
already has the schema and data' option cuz that would not transfer over the
data. That's what I was asking you about.
|||Tejas,
what I don't understand is the point of putting just the schema on the
subscriber. It's standard practice to do a full initialization (schema and
data) or a nosync one (neither). So, if there is no reason for creating the
shema on the subscriber, then I'd do a standard initialization and let it
drop the articles on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I do it the way you asked me to, the replication finishes doing the
schema and most of the data and when it starts doing the indexes, the log
file grows like crazy an i dont have that much space to fulfill the logspace
need. Is there a work around this? And i think it evetually times out. I read
somewhere to increase the querytimeout for this. But still, how about the log
file space?
thank you.
by the way, I tried it the way you had said first
|||Tejas,
if you're struggling for space to host the log file, there's no simple fix
Options include creating a separate log file on another disk, trying
simple recovery mode etc
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Create new table with fields from 4 other tables

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

Quote:

Originally Posted by tenchyz

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

i'm no expert but i guess all you have to do is create a procedure for this|||

Quote:

Originally Posted by tenchyz

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

how are these tables related? do you need a physical table or maybe you just need a view

Create New Table

hi i have one question,

i have created a table, let's say CallerList:

CREATE TABLE MyDB.[dbo].[CallerList]
(
[pid] [int] NOT NULL,
[Name] [varchar] NULL,
[Surname] [varchar] NULL,
[Phone] [int] NULL,
[Date] [datetime] NOT NULL

)

now i'd like to create table CallerList1, and i want it to have same names of columns: pid, Name, Surname, Phone, Date, but i don't want to create it in the way as i wrote up, is it possible to somehow COPY these column names from CallerList and not to wirte the whole code again? Like just the names of columns and their properties to have the same...

thanx

Hi,

this should help:

SELECT * INTO CallerList1 FROM CallerList WHERE 0 = 1

So you copy only the structure to the new table CallerList1

--Andreas