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 Incorrect syntax near 'Collate'

HI.

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

Scripts is

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

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

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

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

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

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

create table script

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 if not exists syntax error

CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);

For some reason the above statements are giving me syntax errors?

I want to create table only if it does not already exist.

Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?

thx in advance .

IF NOT EXISTS
( SELECT [name]
FROM sys.tables
WHERE [name] = MyTable
)
CREATE TABLE MyTable (Col1 int IDENITY, etc. )

Use 'IF EXISTS' test to TRUNCATE or DROP.

create table from another table

Hello,

under MSSQL8 doing this "create table tblA as ( select Name from tblB );"
results in error 156 (wrong syntax near AS keyword). What is my mistake?

thanks and regards
MarkSee SELECT in Books Online:

select Name
into dbo.tblA
from dbo.tblB

Simon

Create Table Error

While Creating the Table in SqlServer200 It is givin the Error like

Server: Msg 2749, Level 16, State 2, Line 1
Identity column 'CustID' must be of data type int, bigint, smallint,
tinyint, or decimal or numeric with a scale of 0, and constrained to
be nonnullable.

Can't we create a Identity for character Field?
my Code is follows

CREATE TABLE Rtest (CustID Varchar(10) NOT NULL IDENTITY,
CustName VARCHAR(40) NOT NULL)NO, You cant have an varchar or char field as an identity column

Identity column must be of type numeric ,or the types specified in the error u have given.|||Identity property is to ensure the column value to increase by the defined step. How would a varchar increase?

Create Table Error

I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.
That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>
|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com

Create Table Error

I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.comsql

Create Table Error

I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
following syntax.
create table employee_pay_tbl
(date_hire date);
I'm getting this error and need help. IIt does not like the date data type?
I'm a beginner...
Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.That is not Transact-SQL for SQL Server, because SQL Server does not have a
data type called date (you can try DATETIME or SMALLDATETIME).
You should check out Books Online, where the syntax examples were actually
written for SQL Server. From Erland:
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
--
Aaron Bertrand
SQL Server MVP
"mrndnjn" <u35966@.uwe> wrote in message news:75628a19d0224@.uwe...
> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
> following syntax.
> create table employee_pay_tbl
> (date_hire date);
> I'm getting this error and need help. IIt does not like the date data
> type?
> I'm a beginner...
> Server: Msg 2715, Level 16, State 7, Line 1
> Column or parameter #1: Cannot find data type date.
>|||Aaron you're absolutely correct. Thank you very much for the feedback!
Aaron Bertrand [SQL Server MVP] wrote:
>That is not Transact-SQL for SQL Server, because SQL Server does not have a
>data type called date (you can try DATETIME or SMALLDATETIME).
>You should check out Books Online, where the syntax examples were actually
>written for SQL Server. From Erland:
>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
>> I'm attempting to do an exercise from Sam's Learn SQL in 24 hours with the
>> following syntax.
>[quoted text clipped - 8 lines]
>> Server: Msg 2715, Level 16, State 7, Line 1
>> Column or parameter #1: Cannot find data type date.
--
Message posted via http://www.sqlmonster.com

Create table eror

I am able to connect to a database set up on SQL windows 2003 under
IIS. However I get an error message when I try to create a table. I am
running from php.
I am sure I am doing something stupid but I can't see it or could it
be a permissions issue?
Error -
Connection to Database members Successful.
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near
the keyword 'IF'.
Code -
//create a table in that dbase
$sql ="CREATE TABLE IF NOT EXISTS $_SESSION[table_name]
(
firstname VARCHAR(20),
lastname VARCHAR(20),
username VARCHAR(20),
password VARCHAR(50),
group1 VARCHAR(20),
group2 VARCHAR(20),
group3 VARCHAR(20),
pchange VARCHAR(1),
email VARCHAR(100),
redirect VARCHAR(100),
verified VARCHAR(1),
last_login DATE
)";
$result = odbc_exec($connection,$sql) or die(odbc_errormsg());
Any ideas or places to look gratefully received.
JohnThere is no CREATE TABLE IF NOT EXISTS statement in T-SQL.
You would need to use something along the lines of:
IF NOT EXISTS (select * from dbo.sysobjects
WHERE id = object_id('dbo.TableName')
AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
CREATE TABLE dbo.TableName
(ColumnName varchar(50) not null,
etc.....)
-Sue
On Thu, 10 Aug 2006 19:45:59 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>I am able to connect to a database set up on SQL windows 2003 under
>IIS. However I get an error message when I try to create a table. I am
>running from php.
>I am sure I am doing something stupid but I can't see it or could it
>be a permissions issue?
>Error -
>Connection to Database members Successful.
>[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near
>the keyword 'IF'.
>Code -
>//create a table in that dbase
>$sql ="CREATE TABLE IF NOT EXISTS $_SESSION[table_name]
>(
> firstname VARCHAR(20),
> lastname VARCHAR(20),
> username VARCHAR(20),
> password VARCHAR(50),
> group1 VARCHAR(20),
> group2 VARCHAR(20),
> group3 VARCHAR(20),
> pchange VARCHAR(1),
> email VARCHAR(100),
> redirect VARCHAR(100),
> verified VARCHAR(1),
> last_login DATE
> )";
>$result = odbc_exec($connection,$sql) or die(odbc_errormsg());
>Any ideas or places to look gratefully received.
>John|||On Thu, 10 Aug 2006 13:09:11 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>There is no CREATE TABLE IF NOT EXISTS statement in T-SQL.
>You would need to use something along the lines of:
>IF NOT EXISTS (select * from dbo.sysobjects
> WHERE id = object_id('dbo.TableName')
> AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
> CREATE TABLE dbo.TableName
> (ColumnName varchar(50) not null,
> etc.....)
>-Sue
Thanks Sue
Would I be using T-SQL?
How can I find out?
I have never heard of that.
John
[vbcol=seagreen]
>On Thu, 10 Aug 2006 19:45:59 +0100, John
><yjdyhugo@.d8hujsrfu> wrote:
>|||Yes that's T-SQL. T-SQL (Transact SQL) is SQL Server's
implementation or "flavor" of SQL.
-Sue
On Thu, 10 Aug 2006 21:04:35 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>On Thu, 10 Aug 2006 13:09:11 -0600, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>
>Thanks Sue
>Would I be using T-SQL?
>How can I find out?
>I have never heard of that.|||On Thu, 10 Aug 2006 19:17:41 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>Yes that's T-SQL. T-SQL (Transact SQL) is SQL Server's
>implementation or "flavor" of SQL. Thanks again Sue
Having had a quick look round TSQL seems to be an extension of SQL.
Are you saying that the code I used would not work on a server/SQL
implementation.
John|||On Fri, 11 Aug 2006 09:31:28 +0100, John <yjdyhugo@.d8hujsrfu> wrote:

>On Thu, 10 Aug 2006 19:17:41 -0600, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>
>Having had a quick look round TSQL seems to be an extension of SQL.
>Are you saying that the code I used would not work on a server/SQL
>implementation.
Further info
SQL seems to be objecting to
IF NOT EXISTS
&
last_login DATE
If I remove these then it runs through OK
Any ideas whu this is the case much appreciated.
John|||On Fri, 11 Aug 2006 13:07:27 +0100, John <yjdyhugo@.d8hujsrfu> wrote:

>On Fri, 11 Aug 2006 09:31:28 +0100, John <yjdyhugo@.d8hujsrfu> wrote:
>
>Further info
>SQL seems to be objecting to
>IF NOT EXISTS
>&
>last_login DATE
>If I remove these then it runs through OK
>Any ideas whu this is the case much appreciated.
>John
Sue already gave you the answer. In SQL Server's implementation of
SQL, "CREATE TABLE IF NOT EXISTS" is not legal syntax. Neither is
"DATE" a valid data type.
T-SQL (Transact SQL) defines what is legal syntax for SQL Server.|||On Fri, 11 Aug 2006 07:01:18 -0700, Jack Jackson
<jacknospam@.pebbleridge.com> wrote:

>On Fri, 11 Aug 2006 13:07:27 +0100, John <yjdyhugo@.d8hujsrfu> wrote:
>
>Sue already gave you the answer. In SQL Server's implementation of
>SQL, "CREATE TABLE IF NOT EXISTS" is not legal syntax. Neither is
>"DATE" a valid data type.
>T-SQL (Transact SQL) defines what is legal syntax for SQL Server.
Sorry you are quite correct.
I thought T-SQL was an extension to SQL but it appears some things
have been taken out as well.
Is that the case?
Thanks for your help Sue and Jack, I think I am beginning to get the
picture now.
John|||It is an extension but it is also the "flavor" used by SQL
Server and impacts how some of the SQL and related standards
are implemented. So it's not just a matter of "additional
functionality" type of thing. Not all database vendors
implement all of the same ANSI SQL.And not everything
implemented by a database vendor is a SQL standard. You will
find plenty of variation in syntax, functionality among the
different database platforms.
-Sue
On Sat, 12 Aug 2006 10:26:30 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>Sorry you are quite correct.
>I thought T-SQL was an extension to SQL but it appears some things
>have been taken out as well.
>Is that the case?
>Thanks for your help Sue and Jack, I think I am beginning to get the
>picture now.
>John|||On Sun, 13 Aug 2006 15:26:03 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>It is an extension but it is also the "flavor" used by SQL
>Server and impacts how some of the SQL and related standards
>are implemented. So it's not just a matter of "additional
>functionality" type of thing. Not all database vendors
>implement all of the same ANSI SQL.And not everything
>implemented by a database vendor is a SQL standard. You will
>find plenty of variation in syntax, functionality among the
>different database platforms.
Looks like I am going to have to wade through the mysql and spot the
differences as I go.
Thanks again for your help on this, I appreciate it.
John

Create Subscription Without Administrator Priveleges

Hi,
I have an application that attempts to create a subscription through the
Reporting Services API. I'm currently getting an error that states the
"'System.Web.Services.Protocols.SoapException: A subscription delivery error
has occurred. --> A subscription delivery error has occurred. --> The value
of parameter ''extensionSettings'' is not valid. Check the documentation for
information about valid values. --> The account you are using does not have
administrator privileges. A subscription cannot be created for
testuser@.company.com"
I did a bit more poking around and found that if I added TestUser, the
currently logged in account, to the administrator group of the machine that
has the Reporting Services webserver, I no longer had this problem and I was
able to create the subscription through the API. My domain users are all set
to Browsers through the Report Manager web interface, but I doubt this
matters since it is separate from the API.
I'm devising a work around for this problem now that doesn't use the default
credentials for the Reporting Service web service, but uses credentials of a
user who is an administrator of that machine. I believe this will work, but
I was wondering how else I might give users the privelege of creating
subscriptions without being administrators of the machine. Also, what other
methods (i.e. delete subscription) are priveleged to only administrators of
the machine?
Thanks, JoelTurns out they just can't create subscriptions for anybody else but
themselves.
Bugger! And I built a great little address book tool too!
-Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:e$OO684xEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have an application that attempts to create a subscription through the
> Reporting Services API. I'm currently getting an error that states the
> "'System.Web.Services.Protocols.SoapException: A subscription delivery
> error has occurred. --> A subscription delivery error has occurred. -->
> The value of parameter ''extensionSettings'' is not valid. Check the
> documentation for information about valid values. --> The account you are
> using does not have administrator privileges. A subscription cannot be
> created for testuser@.company.com"
> I did a bit more poking around and found that if I added TestUser, the
> currently logged in account, to the administrator group of the machine
> that has the Reporting Services webserver, I no longer had this problem
> and I was able to create the subscription through the API. My domain users
> are all set to Browsers through the Report Manager web interface, but I
> doubt this matters since it is separate from the API.
> I'm devising a work around for this problem now that doesn't use the
> default credentials for the Reporting Service web service, but uses
> credentials of a user who is an administrator of that machine. I believe
> this will work, but I was wondering how else I might give users the
> privelege of creating subscriptions without being administrators of the
> machine. Also, what other methods (i.e. delete subscription) are
> priveleged to only administrators of the machine?
> Thanks, Joel
>|||Hi Joel,
I am looking into this issue and will update you as soon as possible when I
find any valueable things to add.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||All,
Following a lead, I checked the output of a GetPermissions call and this is
what I, an adminstrator of the webserver and a content manager Reporting
Services received for output.
Create Folder
Delete
Read Properties
Update Properties
Create Report
Create Resource
Create data source
Read Security Policies
Update Security Policies
The example in RS BOL states that I should see more permissions :
Delete
Execute and View
Read Properties
Update Properties
Update Parameters
Read Data Sources
Update Data Sources
Read Report Definition
Update Report Definition
Create Subscription
Delete Subscription
Read Subscription
Delete Report History
Update Subscription
Create Any Subscription
Delete Any Subscription
Read Any Subscription
Read Security Policies
Update Security Policies
Update Any Subscription
Read Policy
Update Policy
List Report History
Create Report History
Execute
Create Link
I ran it again with a user that had Browser permissions only and who wasn't
an administrator and it returned
Read Properties
My main concern is where are the
Create Subscription
Delete Subscription
Read Subscription
permissions'? If only for me, but also for TestUser.
Thx, Joel
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
> Hi Joel,
> I am looking into this issue and will update you as soon as possible when
> I
> find any valueable things to add.
>
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||So it turns out that I had my path set to the root directory in the sample
application
Dim permissions As [String]() = rs.GetPermissions("/")
If change it to point directly to a report I get back the expected
permissions.
Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
Activity Detail")
Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> All,
> Following a lead, I checked the output of a GetPermissions call and this
> is what I, an adminstrator of the webserver and a content manager
> Reporting Services received for output.
> Create Folder
> Delete
> Read Properties
> Update Properties
> Create Report
> Create Resource
> Create data source
> Read Security Policies
> Update Security Policies
> The example in RS BOL states that I should see more permissions :
> Delete
> Execute and View
> Read Properties
> Update Properties
> Update Parameters
> Read Data Sources
> Update Data Sources
> Read Report Definition
> Update Report Definition
> Create Subscription
> Delete Subscription
> Read Subscription
> Delete Report History
> Update Subscription
> Create Any Subscription
> Delete Any Subscription
> Read Any Subscription
> Read Security Policies
> Update Security Policies
> Update Any Subscription
> Read Policy
> Update Policy
> List Report History
> Create Report History
> Execute
> Create Link
> I ran it again with a user that had Browser permissions only and who
> wasn't an administrator and it returned
> Read Properties
> My main concern is where are the
> Create Subscription
> Delete Subscription
> Read Subscription
>
> permissions'? If only for me, but also for TestUser.
> Thx, Joel
>
> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible when
>> I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>|||For all to learn from ...
The permissions on subscriptions don't make sense. It seems the user is
blocked from creating a subscription for anybody only at the Report Manager
web site level, not at the API level if SendEmailToUserAlias=False. This is
evident by the CC and BCC text boxes not being visible when the user is not
an administrator of the machine RS is running on, and by RS NOT throwing an
error when the CC and BCC fields are used in the extensionParameters array
through the API call to CreateSubscription. However, the comments field is
not allowed through an API call or shown on the website and throws an error
if its use is attempted. It seems MSFT has implemented most security only at
the presentation tier (the web site), not the middle-tier. (However,
documentation on the all of the security is lacking.)
Thx, Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eZRrC7AyEHA.2348@.TK2MSFTNGP12.phx.gbl...
> So it turns out that I had my path set to the root directory in the sample
> application
> Dim permissions As [String]() = rs.GetPermissions("/")
> If change it to point directly to a report I get back the expected
> permissions.
> Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
> Activity Detail")
> Joel
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
>> All,
>> Following a lead, I checked the output of a GetPermissions call and this
>> is what I, an adminstrator of the webserver and a content manager
>> Reporting Services received for output.
>> Create Folder
>> Delete
>> Read Properties
>> Update Properties
>> Create Report
>> Create Resource
>> Create data source
>> Read Security Policies
>> Update Security Policies
>> The example in RS BOL states that I should see more permissions :
>> Delete
>> Execute and View
>> Read Properties
>> Update Properties
>> Update Parameters
>> Read Data Sources
>> Update Data Sources
>> Read Report Definition
>> Update Report Definition
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>> Delete Report History
>> Update Subscription
>> Create Any Subscription
>> Delete Any Subscription
>> Read Any Subscription
>> Read Security Policies
>> Update Security Policies
>> Update Any Subscription
>> Read Policy
>> Update Policy
>> List Report History
>> Create Report History
>> Execute
>> Create Link
>> I ran it again with a user that had Browser permissions only and who
>> wasn't an administrator and it returned
>> Read Properties
>> My main concern is where are the
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>>
>> permissions'? If only for me, but also for TestUser.
>> Thx, Joel
>>
>> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
>> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible
>> when I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions
>> or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>>
>

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

sql

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create subscription failed.

Hi
I am using merge replication to sync sqlserver 2000 sp3 database and sql
server ce 2.0 sp3 via PPC 2003. I received the following error.
"Sql CE Exception: Create subscription failed:
system.Data.Sqlserverce.sqlceException"
"Create subscription failed (27750 - 8004005)"
Please help.
80004005 is a generic access denied. Are you sure the account you are using
to pull the subscription is in the PAL of your merge publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pcherlop" <Pcherlop@.discussions.microsoft.com> wrote in message
news:930ED762-1874-4513-B675-0FFC27A418E0@.microsoft.com...
> Hi
> I am using merge replication to sync sqlserver 2000 sp3 database and sql
> server ce 2.0 sp3 via PPC 2003. I received the following error.
> "Sql CE Exception: Create subscription failed:
> system.Data.Sqlserverce.sqlceException"
> "Create subscription failed (27750 - 8004005)"
> Please help.

CREATE SUBCUBE in Microsoft Visual Studio for SQL2005

Hi,

I am trying to create a SUBCUBE in Microsoft Visual Studio (Analysis Services) but

It keeps on giving me an error when trying to build the Subcube.

The MDX statement is correct because I have tested it in SQL2005 Management Studio and it works.

My question is that “am I doing it in the right place?”:

This is under the Cubes Folder > Calculations Tab> New Script Command

My understanding is that under the “New Script Command” I can type any MDX statement and it should work? Or should this be done in another place?

When processing the cube the error I am getting back is:

Parser: The script contains the statement, which is not allowed

What I am trying to do is to create a slice of a cube by e.g. Brand which one of my departments need to use and should not see the other Brands. All the other dimensions and measures should still apply.

I would appreciate it if someone can please assist me with this.

Thank you in advance

Pieter Nelson

im no expert in olap cubes and am only tinkering with them for the last few months, but ive never heard of sub cubes. in that suituation, id either use a front end to create a report that filters out all but the brand your interested in, or create a seperate cube, based on the exact same data source view, but just import the brand you require. there may be a more elegant way, but those ways are pretty simple.

Regards,

Winston.

|||

Hi Pieter,

It sounds like you need to be using dimension security, not subcubes here. See
http://msdn2.microsoft.com/en-us/library/ms175366.aspx

for details on how to do this.

HTH,

Chris

|||

Unfortuanately I can not use Security or Another Measure Group.

I went into the Calculations Tab in Visual Studio and edited the Calculate Script that look like this:

-

CALCULATE;

CREATE SUBCUBE [Cube_AcqMgmtGF_Mthly] AS SELECT

{[All Brands].[All Brands].[Brand].&[Goldfishka B]} ON 0

FROM [Cube_AcqMgmtGF_Mthly];

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ave first Purchase]

AS '[Measures].[First Purch]/[Measures].[Usaccountno]',

FORMAT_STRING = "Currency",

VISIBLE = 1;

-

I am still trying to filter this cube to one specific brand, but the deployment keeps on failing with the following Description:

"An MDX Statement was expected. An MDX expression was specified" "The script contains the statement, which is not allowed"

To be honest here, I am now getting really confused.

SUMMARY:

Is my code in the right place "Create Subcube" or is there an alternative way of doing this.

PLEASE HELP ANYONE?

|||

Hi Pieter,

To be honest, I can't see anything wrong with your MDX (which service pack are you runnning?) but even if you could create a subcube in your MDX Script I don't think it's going to do what you want - even though I'm able to deploy a CREATE SUBCUBE command in an MDX Script it doesn't look like it does anything useful.

Why can't you use security or another measure group? Can you explain what you want to do in more detail?

Chris

|||

Hi,

I am trying to create subcubes(by Brands) from a Master cube (by only processing one cube), but the situation is that if I create a cube for every brand then it will be different processing for every cube. Unfortuanately these fact tables contains millions of records and processing all these records for every cube will take much longer. The other problem is that if there is changes in the logic of one fact table then I will have to apply these changes to all the other cubes individually.

Hope this helps?

|||

This sounds exactly what dimension security is intended for. Why do you say you can't use it?

Chris

Create Procedure syntax error

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

Create Procedure Error?

Hello All,
I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
Access to SQL Server" published by Apress. Unfortunately the code for
the chapter is not included in the download file from
http://www.apress.com/book/suppleme...?bID=70&sID=309 as
stated in the second paragraph of chapter 16. I've attempted to contact
the publisher without luck. I am attempting to create the procedure in
the box quote below. Unfortunately when attempting to save the
procedure I receive an error message that reads "ADO error: Incorrect
syntax near the keyword 'IF'".
I believe I've found one of the problems which was a space between '@.'
and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
correction eliminated several other errors which left me with the above
error message. I'm searching through 'Books On-Line' per
http://msdn.microsoft.com/library/d...r />
_4fht.asp
without luck. I've found a few other errors in the books code which
also relates to the 'IF' statement. It could be that I'm using SP4
while the book was probably written with the first release. I don't
know, but I'm just guessing.
Any ideals? Thanks!
,-- [ ]
| CREATE PROCEDURE dbo.usp_InsertProd
| (@.ProductName [varchar](40), @.Unit_Price[Money])
| AS
| BEGIN
| If @.Unit_Price > 100
| RAISERROR(50001,16,1)
| ELSE
| BEGIN
| BEGIN TRANSACTION
| INSERT INTO dbo.Products
| (ProductName, UnitPrice)
| IF @.@.ERROR <> 0
| Rollback TRANSACTION
| ELSE
| COMMIT TRANSACTION
| END
| END
`--
Regards,
Greg StrongThe syntax problem is that the INSERT
| INSERT INTO dbo.Products
| (ProductName, UnitPrice)
has neither a VALUES clause nor a SELECT to provide WHAT is being
inserted.
I expect it should read:
INSERT INTO dbo.Products
(ProductName, UnitPrice)
(@.ProductName, @.Unit_Price)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 20:08:53 GMT, Greg Strong <NoJunk@.NoJunk4U.com>
wrote:

>Hello All,
>I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
>Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
>Access to SQL Server" published by Apress. Unfortunately the code for
>the chapter is not included in the download file from
>http://www.apress.com/book/suppleme...?bID=70&sID=309 as
>stated in the second paragraph of chapter 16. I've attempted to contact
>the publisher without luck. I am attempting to create the procedure in
>the box quote below. Unfortunately when attempting to save the
>procedure I receive an error message that reads "ADO error: Incorrect
>syntax near the keyword 'IF'".
>I believe I've found one of the problems which was a space between '@.'
>and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
>correction eliminated several other errors which left me with the above
>error message. I'm searching through 'Books On-Line' per
>http://msdn.microsoft.com/library/d... />
t_4fht.asp
>without luck. I've found a few other errors in the books code which
>also relates to the 'IF' statement. It could be that I'm using SP4
>while the book was probably written with the first release. I don't
>know, but I'm just guessing.
>Any ideals? Thanks!
>,-- [ ]
>| CREATE PROCEDURE dbo.usp_InsertProd
>| (@.ProductName [varchar](40), @.Unit_Price[Money])
>| AS
>| BEGIN
>| If @.Unit_Price > 100
>| RAISERROR(50001,16,1)
>| ELSE
>| BEGIN
>| BEGIN TRANSACTION
>| INSERT INTO dbo.Products
>| (ProductName, UnitPrice)
>| IF @.@.ERROR <> 0
>| Rollback TRANSACTION
>| ELSE
>| COMMIT TRANSACTION
>| END
>| END
>`--|||On Wed, 19 Apr 2006 20:08:53 GMT, Greg Strong wrote:
(snip)
> I am attempting to create the procedure in
>the box quote below. Unfortunately when attempting to save the
>procedure I receive an error message that reads "ADO error: Incorrect
>syntax near the keyword 'IF'".
Hi Greg,
I see several problems in the stored procedure. Comments inline.

>,-- [ ]
>| CREATE PROCEDURE dbo.usp_InsertProd
Style preference - I've never seen the use of those silly prefixes.
Surely, if the name comes directly after CREATE PROCEDURE or EXECUTE,
you don't need the usp_ prefix to know it's a stored procedure, do you?

>| (@.ProductName [varchar](40), @.Unit_Price[Money])
Add a space between "@.Unit_Price" and "[Money]". Maybe not an error, but
definitely confusing!
Also, no need to escape the datatypes with brackets. And using lower
case on one datatype and mixed case on another is confusing too. Same
goes for using PascalCase for one variable name and Under_Scores for the
other - choose one style and stick to it!

>| AS
>| BEGIN
This BEGIN (and the corresponding END) is not strictly necessary. But it
doesn't hurt either.

>| If @.Unit_Price > 100
>| RAISERROR(50001,16,1)
A single statement in an IF or ELSE clause is permitted, but can be
confusing. I tend to prefer to always use a BEGIN / END block, unless
BOTH branches of the IF statement are one statement only (like the IF
@.@.ERROR <> 0 below)

>| ELSE
>| BEGIN
>| BEGIN TRANSACTION
>| INSERT INTO dbo.Products
>| (ProductName, UnitPrice)
Here's the source of your error. This needs either a VALUES or a SELECT
clause to become a complete statement. In this case, I'd hazard a guess
and use VALUES.

>| IF @.@.ERROR <> 0
>| Rollback TRANSACTION
>| ELSE
>| COMMIT TRANSACTION
>| END
>| END
>`--
Finally, the formatting is very bad. Matching BEGIN and END statements
should line up; statements between BEGIN and END should be indented by
the same amount.
Here's how I would write it:
CREATE PROCEDURE dbo.InsertProd
(@.ProductName varchar(40),
@.UnitPrice money)
AS
BEGIN
IF @.UnitPrice < 100
BEGIN
RAISERROR (50001, 16, 1)
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO dbo.Products (ProductName, UnitPrice)
VALUES (@.ProductName, @.UnitPrice)
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END
END
Hugo Kornelis, SQL Server MVP|||On Wed, 19 Apr 2006 17:42:20 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>has neither a VALUES clause nor a SELECT to provide WHAT is being
>inserted.
>I expect it should read:
>INSERT INTO dbo.Products
>(ProductName, UnitPrice)
>(@.ProductName, @.Unit_Price)
Yes, I see what your saying. So shouldn't it really be:
INSERT INTO dbo.Products (ProductName, UnitPrice)
VALUES (@.ProductName, @.Unit_Price)
Unless I'm missing something here, I believe so. Anyhow I've changed
the procedure to what is in the box quote below. On save I now receive
the following error:
ADO error: Must declare the variable ".
Incorrect syntax near the keyword 'ELSE'
I'm not sure, but I believe the second line may have something to do
with the number of 'BEGIN' and 'END' keywords. There are 4 'BEGIN'
keywords, and only 3 'END' keywords. I've made some changes, but no
luck. I do not have a clue as to why the first line exists in the error
message.
Any ideals? Thanks!
,-- [ ]
| CREATE PROCEDURE dbo.usp_InsertProd
| (@.ProductName [varchar](40), @.Unit_Price[Money])
| AS
| BEGIN
| If @. Unit_Price > 100
| BEGIN
| RAISERROR(50001,16,1)
| END
| ELSE
| BEGIN
| BEGIN TRANSACTION
| INSERT INTO dbo.Products (Pr
oductName, UnitPrice)
| VALUES (@.ProductName, @.Unit_
Price)
| IF @.@.ERROR <> 0
| Rollback TRANSACTION
| ELSE
| COMMIT TRANSACTION
| END
| END
`--
Regards,
Greg Strong|||On Wed, 19 Apr 2006 22:29:21 GMT, Greg Strong wrote:
(snip)
> Anyhow I've changed
>the procedure to what is in the box quote below. On save I now receive
>the following error:
>ADO error: Must declare the variable ".
>Incorrect syntax near the keyword 'ELSE'
(snip)
>| If @. Unit_Price > 100
Hi Greg,
There's a space between @. and Unit_Price. Remove it.

>I'm not sure, but I believe the second line may have something to do
>with the number of 'BEGIN' and 'END' keywords. There are 4 'BEGIN'
>keywords, and only 3 'END' keywords.
Wrong. There are three BEGIN and three END keywords.
BEGIN TRANSACTION is not a BEGIN keyword. BEGIN TRANSACTION starts a
transaction, which is later ended with either COMMIT TRANSACTION or
ROLLBACK TRANSACTION.
BEGIN is the start of a block of statements that can be used where the
syntax allows a single statement; the block should be ended with END.
Hugo Kornelis, SQL Server MVP|||If Seems that th e"INSERT INTO..." is not completed. It should be:
BEGIN
If @.Unit_Price > 100
RAISERROR(50001,16,1)
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO dbo.Products
(ProductName, UnitPrice)
/*-- You missed following part--*/
VALUES
(@.ProductName,@.Unit_Price)
IF @.@.ERROR <> 0
Rollback TRANSACTION
ELSE
COMMIT TRANSACTION
END
END
"Greg Strong" <NoJunk@.NoJunk4U.com> wrote in message
news:du4d425nta4uguv347e55mt6tve8kfh3lh@.
4ax.com...
> Hello All,
> I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
> Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
> Access to SQL Server" published by Apress. Unfortunately the code for
> the chapter is not included in the download file from
> http://www.apress.com/book/suppleme...?bID=70&sID=309 as
> stated in the second paragraph of chapter 16. I've attempted to contact
> the publisher without luck. I am attempting to create the procedure in
> the box quote below. Unfortunately when attempting to save the
> procedure I receive an error message that reads "ADO error: Incorrect
> syntax near the keyword 'IF'".
> I believe I've found one of the problems which was a space between '@.'
> and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
> correction eliminated several other errors which left me with the above
> error message. I'm searching through 'Books On-Line' per
> http://msdn.microsoft.com/library/d.../>
rt_4fht.asp
> without luck. I've found a few other errors in the books code which
> also relates to the 'IF' statement. It could be that I'm using SP4
> while the book was probably written with the first release. I don't
> know, but I'm just guessing.
> Any ideals? Thanks!
> ,-- [ ]
> | CREATE PROCEDURE dbo.usp_InsertProd
> | (@.ProductName [varchar](40), @.Unit_Price[Money])
> | AS
> | BEGIN
> | If @.Unit_Price > 100
> | RAISERROR(50001,16,1)
> | ELSE
> | BEGIN
> | BEGIN TRANSACTION
> | INSERT INTO dbo.Products
> | (ProductName, UnitPrice)
> | IF @.@.ERROR <> 0
> | Rollback TRANSACTION
> | ELSE
> | COMMIT TRANSACTION
> | END
> | END
> `--
>
> --
> Regards,
> Greg Strong|||On Thu, 20 Apr 2006 00:24:42 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>Finally, the formatting is very bad. Matching BEGIN and END statements
>should line up; statements between BEGIN and END should be indented by
>the same amount.
Yes, I agree.

>Here's how I would write it:
Thanks! Yours is much easier to follow. Thanks again!!!
Regards,
Greg Strong|||On Thu, 20 Apr 2006 00:52:08 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>Wrong. There are three BEGIN and three END keywords.
>BEGIN TRANSACTION is not a BEGIN keyword. BEGIN TRANSACTION starts a
>transaction, which is later ended with either COMMIT TRANSACTION or
>ROLLBACK TRANSACTION.
Thanks for the clarification!
Regards,
Greg Strong|||On Wed, 19 Apr 2006 17:17:57 -0700, "Norman Yuan" <NotReal@.NotReal.not>
wrote:

>If Seems that th e"INSERT INTO..." is not completed.
Thanks to all for your comments!
Regards,
Greg Strong|||hi Greg,
Greg Strong wrote:
>...
> Unfortunately when attempting to
> save the procedure I receive an error message that reads "ADO error:
> Incorrect syntax near the keyword 'IF'".
> I believe I've found one of the problems which was a space between '@.'
> and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
> correction eliminated several other errors which left me with the
> above error message. I'm searching through 'Books On-Line' per
> http://msdn.microsoft.com/library/d.../>
rt_4fht.asp
> without luck. I've found a few other errors in the books code which
> also relates to the 'IF' statement. It could be that I'm using SP4
> while the book was probably written with the first release. I don't
> know, but I'm just guessing.
the exception is raised becouse of this line,
INSERT INTO dbo.Products
(ProductName, UnitPrice) IF
as the INSERT INTO syntax requires you to provide the VALUES
(param_or_constant, ...)
column names are not mandatory, but values to be inserted of course are...
so modify it as following
-- USE tempdb;
-- GO
CREATE PROCEDURE dbo.usp_InsertProd (
@.ProductName [varchar](40),
@.Unit_Price [Money]
)
AS
BEGIN
IF @.Unit_Price > 100
RAISERROR(50001,16,1);
ELSE BEGIN
BEGIN TRANSACTION;
INSERT INTO dbo.Products
(ProductName, UnitPrice) VALUES (@.ProductName, @.Unit_Price);
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
END;
END;
GO
-- DROP PROCEDURE dbo.usp_InsertProd;
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply