Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
Roberto
Hi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
VB[vbcol=seagreen]
that[vbcol=seagreen]
sql
Showing posts with label order. Show all posts
Showing posts with label order. Show all posts
Thursday, March 22, 2012
Wednesday, March 21, 2012
Exclusive access to MS SQL Database
Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
RobertoHi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
>
VB[vbcol=seagreen]
that[vbcol=seagreen]
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
RobertoHi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
>
VB[vbcol=seagreen]
that[vbcol=seagreen]
Exclude rows with no data
I have a report created using MDX imported from another application. In
order to provide flexibility, this MDX carries in several fields that are not
necessarily used when the report is created. (The report prompts for
parameters which determine whether it should be created using budget or
actual data.) Because of this, I am getting rows of data where there are no
values for the type of report I am requesting. For example, if I have budget
data but no actual data for an item, this item still shows up when I request
the report for actual data. Is there any way to prevent these rows from
appearing?On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have a report created using MDX imported from another application. In
> order to provide flexibility, this MDX carries in several fields that are not
> necessarily used when the report is created. (The report prompts for
> parameters which determine whether it should be created using budget or
> actual data.) Because of this, I am getting rows of data where there are no
> values for the type of report I am requesting. For example, if I have budget
> data but no actual data for an item, this item still shows up when I request
> the report for actual data. Is there any way to prevent these rows from
> appearing?
I'm not familiar w/MDX; however, you should be able to filter the
results by something like Sum(Fields!ActualData.Value) > 0. Hope this
is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
message that says "Processing of filter can not be performed. Please check
the data type value retuend by the filter expression." Can you tell me what
this means and how to set up the statement properly. Thank you for your
help. I'm learning this tool on a "need to know" basis and I have 60 reports
to create w/o any real training.
"EMartinez" wrote:
> On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I have a report created using MDX imported from another application. In
> > order to provide flexibility, this MDX carries in several fields that are not
> > necessarily used when the report is created. (The report prompts for
> > parameters which determine whether it should be created using budget or
> > actual data.) Because of this, I am getting rows of data where there are no
> > values for the type of report I am requesting. For example, if I have budget
> > data but no actual data for an item, this item still shows up when I request
> > the report for actual data. Is there any way to prevent these rows from
> > appearing?
> I'm not familiar w/MDX; however, you should be able to filter the
> results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> message that says "Processing of filter can not be performed. Please check
> the data type value retuend by the filter expression." Can you tell me what
> this means and how to set up the statement properly. Thank you for your
> help. I'm learning this tool on a "need to know" basis and I have 60 reports
> to create w/o any real training.
> "EMartinez" wrote:
> > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I have a report created using MDX imported from another application. In
> > > order to provide flexibility, this MDX carries in several fields that are not
> > > necessarily used when the report is created. (The report prompts for
> > > parameters which determine whether it should be created using budget or
> > > actual data.) Because of this, I am getting rows of data where there are no
> > > values for the type of report I am requesting. For example, if I have budget
> > > data but no actual data for an item, this item still shows up when I request
> > > the report for actual data. Is there any way to prevent these rows from
> > > appearing?
> > I'm not familiar w/MDX; however, you should be able to filter the
> > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > is helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
It seems like you might not have set the 'Value' part of the filter to
an integer value. By default, the filter sets all numbers in the
'Value' column to strings unless you use an = sign before it. You
might want to try something like: in the 'Expression' column enter:
=Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
the 'Value' column enter: =0
If this does not correct the problem, you will most likely need to
convert the results of 'Fields!GrossSales.Value' to an integer: via
something like: CInt(Fields!GrossSales.Value).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. I had to use the "cint" in the expression column AND the "=0"
in the value column. I appreciate youe help!
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||One more question....For some reason the Table filter list has the and/or
column on the selection grayed out. I would like to be able to say "sales OR
pounds" not =0. Do you have any idea why the and/or column is not available
under the table filter properties?
Thank you,
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 5, 7:16 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> One more question....For some reason the Table filter list has the and/or
> column on the selection grayed out. I would like to be able to say "sales OR
> pounds" not =0. Do you have any idea why the and/or column is not available
> under the table filter properties?
> Thank you,
> PB
> "EMartinez" wrote:
> > On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > > message that says "Processing of filter can not be performed. Please check
> > > the data type value retuend by the filter expression." Can you tell me what
> > > this means and how to set up the statement properly. Thank you for your
> > > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > > to create w/o any real training.
> > > "EMartinez" wrote:
> > > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > > I have a report created using MDX imported from another application. In
> > > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > > necessarily used when the report is created. (The report prompts for
> > > > > parameters which determine whether it should be created using budget or
> > > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > > values for the type of report I am requesting. For example, if I have budget
> > > > > data but no actual data for an item, this item still shows up when I request
> > > > > the report for actual data. Is there any way to prevent these rows from
> > > > > appearing?
> > > > I'm not familiar w/MDX; however, you should be able to filter the
> > > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > > is helpful.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. SQL Server Developer
> > It seems like you might not have set the 'Value' part of the filter to
> > an integer value. By default, the filter sets all numbers in the
> > 'Value' column to strings unless you use an = sign before it. You
> > might want to try something like: in the 'Expression' column enter:
> > =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> > the 'Value' column enter: =0
> > If this does not correct the problem, you will most likely need to
> > convert the results of 'Fields!GrossSales.Value' to an integer: via
> > something like: CInt(Fields!GrossSales.Value).
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad to be of assistance. I have not been able to get
the filter options to allow 'or' in my experiences so far. You might
need to have the filtering in the query or stored procedure that is
sourcing the report to allow for more flexibility. Just a thought.
Regards,
Enrique Martinez
Sr. SQL Server Developer
order to provide flexibility, this MDX carries in several fields that are not
necessarily used when the report is created. (The report prompts for
parameters which determine whether it should be created using budget or
actual data.) Because of this, I am getting rows of data where there are no
values for the type of report I am requesting. For example, if I have budget
data but no actual data for an item, this item still shows up when I request
the report for actual data. Is there any way to prevent these rows from
appearing?On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have a report created using MDX imported from another application. In
> order to provide flexibility, this MDX carries in several fields that are not
> necessarily used when the report is created. (The report prompts for
> parameters which determine whether it should be created using budget or
> actual data.) Because of this, I am getting rows of data where there are no
> values for the type of report I am requesting. For example, if I have budget
> data but no actual data for an item, this item still shows up when I request
> the report for actual data. Is there any way to prevent these rows from
> appearing?
I'm not familiar w/MDX; however, you should be able to filter the
results by something like Sum(Fields!ActualData.Value) > 0. Hope this
is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
message that says "Processing of filter can not be performed. Please check
the data type value retuend by the filter expression." Can you tell me what
this means and how to set up the statement properly. Thank you for your
help. I'm learning this tool on a "need to know" basis and I have 60 reports
to create w/o any real training.
"EMartinez" wrote:
> On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I have a report created using MDX imported from another application. In
> > order to provide flexibility, this MDX carries in several fields that are not
> > necessarily used when the report is created. (The report prompts for
> > parameters which determine whether it should be created using budget or
> > actual data.) Because of this, I am getting rows of data where there are no
> > values for the type of report I am requesting. For example, if I have budget
> > data but no actual data for an item, this item still shows up when I request
> > the report for actual data. Is there any way to prevent these rows from
> > appearing?
> I'm not familiar w/MDX; however, you should be able to filter the
> results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> message that says "Processing of filter can not be performed. Please check
> the data type value retuend by the filter expression." Can you tell me what
> this means and how to set up the statement properly. Thank you for your
> help. I'm learning this tool on a "need to know" basis and I have 60 reports
> to create w/o any real training.
> "EMartinez" wrote:
> > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I have a report created using MDX imported from another application. In
> > > order to provide flexibility, this MDX carries in several fields that are not
> > > necessarily used when the report is created. (The report prompts for
> > > parameters which determine whether it should be created using budget or
> > > actual data.) Because of this, I am getting rows of data where there are no
> > > values for the type of report I am requesting. For example, if I have budget
> > > data but no actual data for an item, this item still shows up when I request
> > > the report for actual data. Is there any way to prevent these rows from
> > > appearing?
> > I'm not familiar w/MDX; however, you should be able to filter the
> > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > is helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
It seems like you might not have set the 'Value' part of the filter to
an integer value. By default, the filter sets all numbers in the
'Value' column to strings unless you use an = sign before it. You
might want to try something like: in the 'Expression' column enter:
=Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
the 'Value' column enter: =0
If this does not correct the problem, you will most likely need to
convert the results of 'Fields!GrossSales.Value' to an integer: via
something like: CInt(Fields!GrossSales.Value).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. I had to use the "cint" in the expression column AND the "=0"
in the value column. I appreciate youe help!
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||One more question....For some reason the Table filter list has the and/or
column on the selection grayed out. I would like to be able to say "sales OR
pounds" not =0. Do you have any idea why the and/or column is not available
under the table filter properties?
Thank you,
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 5, 7:16 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> One more question....For some reason the Table filter list has the and/or
> column on the selection grayed out. I would like to be able to say "sales OR
> pounds" not =0. Do you have any idea why the and/or column is not available
> under the table filter properties?
> Thank you,
> PB
> "EMartinez" wrote:
> > On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > > message that says "Processing of filter can not be performed. Please check
> > > the data type value retuend by the filter expression." Can you tell me what
> > > this means and how to set up the statement properly. Thank you for your
> > > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > > to create w/o any real training.
> > > "EMartinez" wrote:
> > > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > > I have a report created using MDX imported from another application. In
> > > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > > necessarily used when the report is created. (The report prompts for
> > > > > parameters which determine whether it should be created using budget or
> > > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > > values for the type of report I am requesting. For example, if I have budget
> > > > > data but no actual data for an item, this item still shows up when I request
> > > > > the report for actual data. Is there any way to prevent these rows from
> > > > > appearing?
> > > > I'm not familiar w/MDX; however, you should be able to filter the
> > > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > > is helpful.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. SQL Server Developer
> > It seems like you might not have set the 'Value' part of the filter to
> > an integer value. By default, the filter sets all numbers in the
> > 'Value' column to strings unless you use an = sign before it. You
> > might want to try something like: in the 'Expression' column enter:
> > =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> > the 'Value' column enter: =0
> > If this does not correct the problem, you will most likely need to
> > convert the results of 'Fields!GrossSales.Value' to an integer: via
> > something like: CInt(Fields!GrossSales.Value).
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad to be of assistance. I have not been able to get
the filter options to allow 'or' in my experiences so far. You might
need to have the filtering in the query or stored procedure that is
sourcing the report to allow for more flexibility. Just a thought.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Friday, March 9, 2012
EXCEPTION_ACCESS_VIOLATION when I want to update
Hi,
I want to update a db-column with text-format.
The order is simple: Update tblatt set textattvalue = '...' where Attid= ...
... stands for a text and an ID.
I get the exception I wrote in the subject and I don't know, what I can do.
On 3 other Databases, which I create with the same script(=they are all equal), everything works fine.
I hope anyone has an idea.if the transaction is stuck in the log the table will be locked and then you are stuck! Does this still happen if you stop and re-start the sevices?
(Of course you could always look at the table locks :rolleyes: )|||I had restarted the Services before, but it didn't change anything. I deleted the database (only this one, because there are 2 or 3 other important dbs) and recreated it, but nothing changed.
I want to update a db-column with text-format.
The order is simple: Update tblatt set textattvalue = '...' where Attid= ...
... stands for a text and an ID.
I get the exception I wrote in the subject and I don't know, what I can do.
On 3 other Databases, which I create with the same script(=they are all equal), everything works fine.
I hope anyone has an idea.if the transaction is stuck in the log the table will be locked and then you are stuck! Does this still happen if you stop and re-start the sevices?
(Of course you could always look at the table locks :rolleyes: )|||I had restarted the Services before, but it didn't change anything. I deleted the database (only this one, because there are 2 or 3 other important dbs) and recreated it, but nothing changed.
Subscribe to:
Posts (Atom)