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 Server Report on the fly

I would like to modify some of the properties of a ServerReport entity from a desktop app using the ReportViewer control. In particular, I would like to have the document map displayed as expanded, and make some changes to the group toggle items based on the user's parameter selection.

Can someone please point me in the right direction? How do I get access programmatically to the ServerReport layout?

Thx

In the event I'm way off base just disregard this post.

Create a parameter of your choice and name it for example "ExpandDetails" and apply the Data Type as Boolean. In the default values select “Non-Queried” and type “False”

Select the table in question, open up the Properties of the table, select the group that controls the expanded element, Edit the group you selected, change from the General tab to the Visibility tab, select Expression, add this code"=Iif(Parameters!ExpandDetails.Value = True, False, True)"

Select the textbox where the expanded element will occur, open up the Properties of the textbox. Change from the General tab to the Visibility tab. Select Expression at the bottom of the dialog box where you see “Expanded” and “Collapsed”, add this code “=Iif(Parameters!ExpandDetails.Value = True, True, False)”

This will create a parameter named ExpandDetails with a radio box selection. Click on the appropriate value and select “View Report”

Tweak the logic to work in the manner you need. by that I mean the true or false of the IIF statements.

|||

Thanks for the reply.

I've actually tried what you suggest. My situation is that I allow my users to choose an outer group, and an inner group, with the option of choosing "None" (I.e. no inner grouping) for the inner group. I've managed to get it all working (except for the +/- icons being reversed in some cases) but I have not been able to get the Expand/Collapse to work, mainly because I have no way (that I can see) of changing a ToggleItem based on an expression.

So I need to get down and dirty with the rdl before rendering the report, and am hunting around for how to proceed. I suspect I need to dump the report using LoadReport into a memory stream and cast it somehow, but some sample code with be helpful.

Cheers,

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.
Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing password

Hi Freinds,
SQL 2000 REPORTING SERVER
I changed my SA account password and Reporting service stop working.
Where should I modify the account crudentails in RSReportServer.config
Thanks,
PatUse rsconfig.exe to reset the connection information for RS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick" <patriarck@.gmail.com> wrote in message
news:u%236cNH64EHA.2288@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000 REPORTING SERVER
> I changed my SA account password and Reporting service stop working.
> Where should I modify the account crudentails in RSReportServer.config
> Thanks,
> Pat
>

changing file's filegroup

Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601
280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.

>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
belonging[vbcol=seagreen]
my[vbcol=seagreen]
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (whi
ch generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, h
owever remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501
280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
>
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.

>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact[vbcol=seagreen]
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.

>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>|||You can name the files anything you want, that doesn't affect whether a file
is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions
when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the
primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the
one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501
280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
>
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact

changing file's filegroup

Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.
Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.
|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.

>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...[vbcol=seagreen]
belonging[vbcol=seagreen]
my
>
>.
>
|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my
|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.

>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact
>
>.
>
|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.

>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>
|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact

changing file's filegroup

Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> Hi, I have a database with two data files, both
belonging
>> to the PRIMARY filegroup. I've emptied one of the files
>> and would like to modify that file's filegroup to
>> SECONDARY so that I can remove it from my database.
>> Using the alter database modify filegroup would impact
my
>> entire database and not to a specific file.
>> Any suggestions. Thanks.
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> >--Original Message--
> >Why not just remove it using ALTER DATABASE... REMOVE
> FILE...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> Hi, I have a database with two data files, both
> belonging
> >> to the PRIMARY filegroup. I've emptied one of the files
> >> and would like to modify that file's filegroup to
> >> SECONDARY so that I can remove it from my database.
> >>
> >> Using the alter database modify filegroup would impact
> my
> >> entire database and not to a specific file.
> >>
> >> Any suggestions. Thanks.
> >
> >
> >.
> >|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>>--Original Message--
>>The filegroup named "PRIMARY" is not the same thing as
>the primary file (which generally has .mdf as
>>extension).
>>The error message states that you cannot remove the
>primary file. You can, however remove secondary file
>>(.ndf) from the primary filegroup (as well as other
>filegroups).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
>attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
>files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
>impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> >--Original Message--
> >The filegroup named "PRIMARY" is not the same thing as
> the primary file (which generally has .mdf as
> >extension).
> >
> >The error message states that you cannot remove the
> primary file. You can, however remove secondary file
> >(.ndf) from the primary filegroup (as well as other
> filegroups).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> >> That doesn't work because it already belongs to in the
> >> PRIMARY filegroup and get the following msg when
> attempted
> >> to do so:
> >>
> >> The primary data or log file cannot be removed from a
> >> database.
> >>
> >> Thanks.
> >>
> >> >--Original Message--
> >> >Why not just remove it using ALTER DATABASE... REMOVE
> >> FILE...
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> >> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> >> Hi, I have a database with two data files, both
> >> belonging
> >> >> to the PRIMARY filegroup. I've emptied one of the
> files
> >> >> and would like to modify that file's filegroup to
> >> >> SECONDARY so that I can remove it from my database.
> >> >>
> >> >> Using the alter database modify filegroup would
> impact
> >> my
> >> >> entire database and not to a specific file.
> >> >>
> >> >> Any suggestions. Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
Jerry
I have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:
> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing current database during local tx or xa tx

What are the consequences of changing the current database (using "use dbnam
e")
during a transaction?
If I modify multiple databases (on the same Sql Server instance) during a
transaction, will all changes be done in the same transaction (and commited/
rollbacked
atomically)?
Does using XA tx change the answer to the question?Taras Tielkes (taras.tielkes@.gmail.com) writes:
> What are the consequences of changing the current database (using "use
> dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance) during
> a transaction, will all changes be done in the same transaction (and
> commited/rollbacked atomically)?
Yes.

> Does using XA tx change the answer to the question?
XA?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Taras Tielkes wrote:
> What are the consequences of changing the current database (using
> "use dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance)
> during a transaction, will all changes be done in the same
> transaction (and commited/rollbacked atomically)?
> Does using XA tx change the answer to the question?
Yes. Same transaction. But you cannot use USE statements in stored
procedures or triggers. There's a better way, however. USE, which
physically changes a user's context, is an expensive operation. You're
just better off fully-qualifying the objects you are modifying. Then you
don't have to switch databases:
For example:
Update MyDB.dbo.MyTable SET ...
David Gugick
Quest Software
www.imceda.com
www.quest.com