Configuration Error on Encryption Key

I came across an issue while configuring SQL Server 2005 Reporting Services. In the reporting services configuration tool, there is a step the either Backup, Restore, or Change the encryption key for the new Reporting Services instance. I backed up the key since this is the first installation of SQL Server and Reporting Services on this machine.

I had expected that the configuration would then allow me to progress to the next step in the configuration, "Initialization". But regardless of how I handle the Encryption Key step, the option to move "Initialization" does not become available.
Has anyone had this happen to them? Or better yet, does anyone have a solution to this?

The report server only needs to be manually initialized if you are connecting the server to a web farm. It is needed to ensure that all nodes in the farm are using the same encryption key. In your case, since you have backed up the key, the report server is already initialized. The option is likely disabled because you are using an edition of SQL Server that does not support web farms (it is only available in enterprise and developer editions).|||Yep, I found that to be the case yesterday while trying to troubleshoot this.
I was checking on another server trying to find where the setup went wrong and determined that the issue is actually that the Initialization step didn't become available as it should. There must have been an error in one of the preceding steps that didn't show up as it should have.
At this point I am going to try and run the command-line initialization to complete the setup. And if that doesn't work, I'll probably have to uninstall and re-install Reporting Services.

conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

concatenating sql fields and parameters

Hi,
I am trying to run a update stored procedure where one of the fields is
dynamic eg.
UPDATE table
SET field_ + @.number = @.a_value
WHERE (key = @.key_value)
@.number is chosen by the user and the field_## can be anything from field_01
to field_99.
Is there a way i can do this? The above method doesn’t work.
Any help will be greatly appreciated.
Many ThanksIn t-SQL, you will have to use each columns explcitly in the SET clause,
with commas separating each column assignments. For syntax, refer to the
topic UPDATE in SQL Server Books Online.
Perhaps with Dynamic SQL you might be able to kludge it out. For details,
refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note, it
is possible that you have a flawed design which force you to use such
meaningless constructs in your code.
Anith|||Hi Vortex
consider rewriting as:
EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
key = ' + @.key_value)
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"vortex" wrote:

> Hi,
> I am trying to run a update stored procedure where one of the fields is
> dynamic eg.
> UPDATE table
> SET field_ + @.number = @.a_value
> WHERE (key = @.key_value)
> @.number is chosen by the user and the field_## can be anything from field_
01
> to field_99.
> Is there a way i can do this? The above method doesn’t work.
> Any help will be greatly appreciated.
> Many Thanks
>|||I will give it a try,
I am creating a stored procedure with your update command, if I use the
method you suggested will SQL have to compile the sp every time a new value
is used or will it just compile the once. (Speed is required, that is why I
am using a sp)
Thanks
Khalid
"Chandra" wrote:
> Hi Vortex
> consider rewriting as:
> EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
> key = ' + @.key_value)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "vortex" wrote:
>|||If you can suggest a better way of doing it, i would be a very happy bunny a
s
i have a lot more stored procedures to write :(
thanks
"Anith Sen" wrote:

> In t-SQL, you will have to use each columns explcitly in the SET clause,
> with commas separating each column assignments. For syntax, refer to the
> topic UPDATE in SQL Server Books Online.
>
> Perhaps with Dynamic SQL you might be able to kludge it out. For details,
> refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note,
it
> is possible that you have a flawed design which force you to use such
> meaningless constructs in your code.
> --
> Anith
>
>|||It is not as ease as it seems. For example, Chandra's solution will fail if
@.number is tinyint/int/bigint because you can not those data types have
greater precedence than varchar so sql server will try to convert 'UPDATE
table SET field_' to tinyint/int/bigint and this will give an error. The sam
e
will happen @.a_value, you have to quote it between apostrophes for char /
varchar / datetime values. The same with @.key_value. You will have to use
dynamic sql and bunch on lines to accomodate the statement to the variables
data type.
I will not write about readability and maintenance of your final code, you
can guess what will be the result.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"vortex" wrote:

> I will give it a try,
> I am creating a stored procedure with your update command, if I use the
> method you suggested will SQL have to compile the sp every time a new valu
e
> is used or will it just compile the once. (Speed is required, that is why
I
> am using a sp)
> Thanks
> Khalid
> "Chandra" wrote:
>
>|||>> If you can suggest a better way of doing it,..
Better way of doing an UPDATE or changing the design? Regarding the UPDATE,
did you refer to the manual for exact syntax?
Regarding the design, with simple one-liners as in your initial post, it is
hard to suggest anything meaningful. Post some more information regarding
this table, the entity type that is being modelled and the attributes
involved. Also provide some details regarding the business model and how
this table fits into the overall schema.
Generally it is hard to provide accurate design suggestions over newsgroup
responses, however with the above requested info, you could perhaps get
started.
Anith