Changing the report parameters or data sources to the values you specified is not allowed

I have a report I want to modify and when I go to deploy it I get:

Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report

Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.

However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.

How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....

Thanks!

Hello,

It seems that other modifications might have been made to the report. Try to get the latest version from the report server, modify the header column's color, and try to re-deploy.

Go to your report in Report Manager and click on the Properties tab. In the General section, click on the Edit link, it will ask you to save the RDL file. This is the report file as it is on the RS server. Navigate to where your RDL files are stored on your development box and replace the report with the one you got from the RS server. Open your VS project and make your change.

Hope this helps.

Jarret

|||

I'll have to try that thanks.

Question for you as well, when I do that will it keep all the settings that report has like execution schedules, history, etc..?

Or does all that get lost when I re-deploy the report to the server?

Also what about security settings for the report?

If I set the report to be viewable by HR Management only, will I have to redo that once I overwrite the report by re-deploying that or is that all preserved?

(If it IS preserved is it preserved in the report itself or just on the report server where those properties point to the report by the name of the report?)

Thanks!

|||

Another option is to create a specific HR-only shared data source, specify the credentials -there-, and build reports off of it, then you don't need to supply credentials for each report.

Then limit access to that datasource to only HR personnel.

Then in your SSRS Project Properties, change 'OverwriteDataSources' to False. Then you can specify credentials on the server once, and each time you deploy your project, they don't get erased by the deployment process.

The execution schedules, permissions, and history settings remain the same after deployment. The only thing that will get wiped out is the credentials.

Hope this helps,

Geof

|||

That does make sense, I'll have to do that.

Thanks for the info.

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity range values with database name keeping the same.

Hello,
What are the steps for changing the identity range values?
We have backup the database that contained initial publication with
identity range set to 1000. Now when I delete the existing database with
replication, and then create a new DB with same name and restore the
database it maintains the publication and does not allow me to change
the identity range values. Also constraints are not modified. If I
create a new publication two identity range constraints are generated.
However when I create a new DB with a new name, restore the database and
then create another publication it correctly modifies the identity range
constraints with new value say 20000.
I want database with same name to be created. Please help.
Neha Magia
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Replication does not remove the constraints it uses to maintain the identity
ranges on the subscriber tables. You will have to remove these manually.
It may be possible that these constraints are also in place on the
publisher. You will have to drop the publication, remove the constraints is
they still exists, and then rebuild it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neha Magia" <nehamagia9@.yahoo.com> wrote in message
news:ussPRbZ7EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> What are the steps for changing the identity range values?
> We have backup the database that contained initial publication with
> identity range set to 1000. Now when I delete the existing database with
> replication, and then create a new DB with same name and restore the
> database it maintains the publication and does not allow me to change
> the identity range values. Also constraints are not modified. If I
> create a new publication two identity range constraints are generated.
> However when I create a new DB with a new name, restore the database and
> then create another publication it correctly modifies the identity range
> constraints with new value say 20000.
> I want database with same name to be created. Please help.
> Neha Magia
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CS
scholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegro ups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>
|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!
|||Why stuff and why not the replace function?
|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.sql

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character of a string?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

TIA,
-CSscholzie (scholzie@.gmail.com) writes:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?

Have you looked at substring()?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE VIEW Parts (.., generic-part, ..)
AS
SELECT .. SUBSTRING part_id,1,5) + '_'[ + SUBSTRING part_id, 7, 1) ,
...
FROM Inventory;|||Thanks. I thought substring() was for finding the instance of a
character, but I guess I read wrong.

Appreciate the help!

Changing table value being field name

Deal all,
I have a table with values like :
ProductID ProductName ProductPrice
1 Product01 1000
2 Product02 1100
3 Product03 2100
4
5...
about 113 prodcut on line (could be around 150 in the future).
I want to make a report which needs data in a row like :
Product01 Product02 Product03 ... Product113
1000 1100 2100 .. the price of Product113
Could anybody tell me how to achieve this?
TIAHI
Read this.
But let front end do front end work
http://www.aspfaq.com/show.asp?id=2462
--
Regards
R.D
--Knowledge gets doubled when shared
"Hendrick" wrote:

> Deal all,
> I have a table with values like :
> ProductID ProductName ProductPrice
> 1 Product01 1000
> 2 Product02 1100
> 3 Product03 2100
> 4
> 5...
> about 113 prodcut on line (could be around 150 in the future).
> I want to make a report which needs data in a row like :
> Product01 Product02 Product03 ... Product113
> 1000 1100 2100 .. the price of Product113
> Could anybody tell me how to achieve this?
> TIA
>
>|||Thanks, it works as intended.. However, I thought there's a solution from
SQL languange without hardcoding the 'Q' part (which is ProductName in my
case). since it could be 150 rows :(
In access I could use TRANSFORM ... PIVOT, why this nice feature not
supported in SQL Server?, Even in SQL Server 2005 is not so matching this
PIVOT easiness. SQL compliance, I guess.
Thanks anyway.
Hendrik
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:748C930B-EF50-4F48-BA2E-8C4E08C7604D@.microsoft.com...
> HI
> Read this.
> But let front end do front end work
> http://www.aspfaq.com/show.asp?id=2462
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Hendrick" wrote:
>
Product113|||> In access I could use TRANSFORM ... PIVOT, why this nice feature not
> supported in SQL Server?, Even in SQL Server 2005 is not so matching this
> PIVOT easiness.
Because the smart solution is to do it in your reporting app or
presentation tier, not in the database.
David Portas
SQL Server MVP
--|||Hi Hendrick,
Could you please check your system clock.
I have you 9 hours ahead of everybody else.
...or you may have set your time zone wrong.
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:%23W5M6tE4FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Deal all,
> I have a table with values like :
> ProductID ProductName ProductPrice
> 1 Product01 1000
> 2 Product02 1100
> 3 Product03 2100
> 4
> 5...
> about 113 prodcut on line (could be around 150 in the future).
> I want to make a report which needs data in a row like :
> Product01 Product02 Product03 ... Product113
> 1000 1100 2100 .. the price of Product113
> Could anybody tell me how to achieve this?
> TIA
>|||It's the time zone.
Thanks
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:ersbj7H4FHA.3592@.TK2MSFTNGP12.phx.gbl...
> Hi Hendrick,
> Could you please check your system clock.
> I have you 9 hours ahead of everybody else.
> ...or you may have set your time zone wrong.
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:%23W5M6tE4FHA.1420@.TK2MSFTNGP09.phx.gbl...
Product113
>|||Hmmm, and now one day ahead. Fixed.
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:#xafTcI4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> It's the time zone.
> Thanks
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:ersbj7H4FHA.3592@.TK2MSFTNGP12.phx.gbl...
> Product113
>

Changing smtp and from values

During the install wizard prompts where presented for a
SMTP address and a FROM value. I want to change these
values now, How do I do this?Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config
Be sure to make a copy first :)
Bruce L-C
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:268801c47030$1efc9f70$a501280a@.phx.gbl...
> During the install wizard prompts where presented for a
> SMTP address and a FROM value. I want to change these
> values now, How do I do this?|||Edit the RSReportServer.config file found in %PROGRAMFILES%\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\ folder:
<RSEmailDPConfiguration>
<SMTPServer></SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From></From>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:268801c47030$1efc9f70$a501280a@.phx.gbl...
> During the install wizard prompts where presented for a
> SMTP address and a FROM value. I want to change these
> values now, How do I do this?

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

Changing label of y-axis

For my report i have to change the label of the y-axis. My dataset is
returning integer values "0", "1", "2". These values are now shown as the
label. However, i need to translate these ID's to string values, such as "0"
= "no value available", "1" = "service down", "2" = "service up".
I have tied to insert a code snippet for conditinal names in the Label
Format Code field
=IIF(Fields!Parameter.Value=0, "no values available", Fields!Parameter.Value)
but this does not solve the problem, because it seems i can only use
formatting code in the label field.
Is there a way to do this or do i have to label my y-axis as is and insert a
legend to let the reader of the report know, what that values are meaning?For the series or category (I forget which one is the y-axis), specify the
following instead of just "Fields!myvalue.value"
=IIF(Fields!myValue.value = 0, "No value available",
IIF(Fields!myValue.Value = 1, "Service Down", "Service Up"))
Replace "myvalue" with the actual name of your column.
"Ben" <yogiben_at_gmx_dot_net> wrote in message
news:2B86E61F-186C-4A74-AE0B-EAF85F1DF9C7@.microsoft.com...
> For my report i have to change the label of the y-axis. My dataset is
> returning integer values "0", "1", "2". These values are now shown as the
> label. However, i need to translate these ID's to string values, such as
> "0"
> = "no value available", "1" = "service down", "2" = "service up".
> I have tied to insert a code snippet for conditinal names in the Label
> Format Code field
> =IIF(Fields!Parameter.Value=0, "no values available",
> Fields!Parameter.Value)
> but this does not solve the problem, because it seems i can only use
> formatting code in the label field.
> Is there a way to do this or do i have to label my y-axis as is and insert
> a
> legend to let the reader of the report know, what that values are meaning?

Changing Index Question

Hi All,
I'm trying to desgin a table that will reset the index depending on field
values. Example:
INDX Date1 NETID JunkField
-- -- -- --
1 05/05/05 USER1 1
2 05/05/05 USER1 1
3 05/05/05 USER1 1
1 05/05/05 USER2 1
1 05/06/05 USER2 1
Every time there is a new user NETID or the DATE1 values changes, the INDX
value should reset back to 1. Could someone provide me with a simple SQL
script. I'm new to this.
Thanks.Triggers may help you acheive this. Have a look at :-
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_create2_7eeq.htm
--
HTH
Ryan Waight, MCDBA, MCSE
"JackV" <vituja@.consumer.org> wrote in message
news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I'm trying to desgin a table that will reset the index depending on field
> values. Example:
> INDX Date1 NETID JunkField
> -- -- -- --
> 1 05/05/05 USER1 1
> 2 05/05/05 USER1 1
> 3 05/05/05 USER1 1
> 1 05/05/05 USER2 1
> 1 05/06/05 USER2 1
> Every time there is a new user NETID or the DATE1 values changes, the INDX
> value should reset back to 1. Could someone provide me with a simple SQL
> script. I'm new to this.
> Thanks.
>|||Hi Ryan,
The link doesn't seem to work. Can you resend it. Thanks.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eO0gI9RgDHA.2292@.TK2MSFTNGP10.phx.gbl...
> Triggers may help you acheive this. Have a look at :-
>
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
> lref.chm::/ts_create2_7eeq.htm
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "JackV" <vituja@.consumer.org> wrote in message
> news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > I'm trying to desgin a table that will reset the index depending on
field
> > values. Example:
> >
> > INDX Date1 NETID JunkField
> > -- -- -- --
> > 1 05/05/05 USER1 1
> > 2 05/05/05 USER1 1
> > 3 05/05/05 USER1 1
> > 1 05/05/05 USER2 1
> > 1 05/06/05 USER2 1
> >
> > Every time there is a new user NETID or the DATE1 values changes, the
INDX
> > value should reset back to 1. Could someone provide me with a simple
SQL
> > script. I'm new to this.
> >
> > Thanks.
> >
> >
>

Changing identity column value

Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.

Normally, this is not a good idea. There are typically a number of problems that you add to your list of problems whenever you try to "solve this kind of problem." Yes, this can be done, however, some problems that you will have to address include:

Taking the table "offline"

Resolving referential integrity problems.

Dropping and recreating the table

verifying that the change is "correct"

|||Programatically you can do that using :

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

(see Books Online)

if you have the order of identity:
1,2,5,6

with command above you can insert the records 3 and 4

Changing Group BackgroundColor from Field Values

Hello Everybody,

I'am working with "Reporting Services 2005".
I would like to have specific Colors for a Group.

e.g.

+Apel: (Group, f.e. "green")

+peach: (Group, f.e. "red")
....

Apel and peach have each a color assigned in the database.
Each Report can have different Fruits on it, but they are defined by their color.

Could change the color statically and and toggle Details Colors, but nut this!

Thanks very much for any kind of help!

Have your query return the background color. THen add that color field to your group. Set the rowdetail background to the color from the dataset.|||Thanks very much, it works!

changing default values

How can i change the default value of a column? I already have a column
named DateOfRental but I want to alter it so that it has default value
getdate()

Thanks
David

--
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious"David Wright" <David@.prodigiousuk.com> wrote in message
news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> How can i change the default value of a column? I already have a column
> named DateOfRental but I want to alter it so that it has default value
> getdate()
> Thanks
> David
> --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious

1. Use sp_help to get the current name of the constraint

exec sp_help MyTable

2. Drop the constraint

alter table MyTable drop constraint <Constraint name goes here
3. Add a new constraint

alter table t1 add constraint DFT_DateOfRental default getdate() for
DateOfRental

Simon|||Will try that, thanks Simon

David

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||How can i drop a constraint i never made though? its not in the list when i
try the SP_help :(

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||David Wright (David@.prodigiousuk.com) writes:
> How can i drop a constraint i never made though? its not in the list
> when i try the SP_help :(

If there is no constraint, you should not have to drop it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Changing default parameters cause postback\refresh

I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.

Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?

Anyone know of a solution to this?|||

Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:

Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

Hi John,

I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.

So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?

Thanks!,

|||

John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.

Changing default parameters cause postback\refresh

I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.

Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?

Anyone know of a solution to this?|||

Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:

Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx
|||

Hi John,

I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.

So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?

Thanks!,

|||

John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.