Excluding weekends in queries

Hi
How can I exclude wenddays from a query?
I have found "SQLDMOW_WEnds" which seems to be made for that purpose?
Otherwise I guess the only way is to use something like
where day(datefield) <> 6 and day(datefield) <> 7
Any other suggestions?
regards
HenryBuild a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
w_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
..);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html|||hi henry
you can do it as
SELECT datepart(w,datefield) NOT IN (1,7)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Henry" wrote:

> Hi
> How can I exclude wenddays from a query?
> I have found "SQLDMOW_WEnds" which seems to be made for that purpose
?
> Otherwise I guess the only way is to use something like
> where day(datefield) <> 6 and day(datefield) <> 7
> Any other suggestions?
>
>
> regards
> Henry
>
>|||That should be "dw" or "wday" in the DATEPART function; also look up
@.@.DATEFIRST and SET DATEFIRST before hard-coding the IN(1,7) condition.
"Chandra" wrote:
> hi henry
> you can do it as
> SELECT datepart(w,datefield) NOT IN (1,7)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Henry" wrote:
>sql

Excluding filter via report parameter

Hi,

How to exclude a filter on a dataset such that I may either apply the filter or not?

I would like to control that through a boolean report parameter.

Edmund

try creating a stored procedure as your dataset. in the sp write an If statement. this lets you set up logic so thet a different query can be run e.g if "true" is selected query A is run filter applied

if "false" is selected query b can be run with no filter

excluding fields in SELECT statement

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks

No. It isn't difficult to list just the columns you want. In Query Analyzer
you can drag the column list from the Object Browser pane so no typing is
required.

Note that it is also good practice to avoid using SELECT * in production
code. Using * instead of listing just the required columns is not only
inefficient, it also impacts the reliability and maintainability of your
code.

--
David Portas
SQL Server MVP
--|||No, there is no Transact-SQL syntax for this. However, you can use Query
Analyzer to generate a SELECT statement for the desired view or table and
then remove the unneeded columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks|||>> Is there a way to exclude fields [sic] in a query other than just including the ones you want. If there are 20 fields [sic] and you want to see all but 3, it would be a lot easier to exclude the 3. <<

Short answer: No. Have you ever seen such a thing in any other
programming language? The closest thing I know is FILLER in Cobol.

Rows are not records; fields are not columns; tables are not files. It
is not easier; it is dangerous. If the base table is altered, your
syntax would not see the changes and would screw up. A list of column
name is easy to generate from the schema information tables with a
tool.|||Great!

Forgot to mention I was looking for a faster way to work in the Query
Analyzer.

>>you can use Query Analyzer to generate a SELECT statement for the desired view...

Is this something other than dragging over columns into the SELECT
statement you're building?

Thanks,
Ric|||ric (rtavert@.yahoo.com) writes:
>>>you can use Query Analyzer to generate a SELECT statement for the desired
>>>view...
> Is this something other than dragging over columns into the SELECT
> statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I guess this is what Dan had in mind.

I used to use that method in the old days (pre- SQL 2000) but I was
referring to the following technique:

1) right-click on the desired table in the QA object browser

2) select Script Object to New Window as--> Select

3) remove unwanted columns

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F9F4031F9D6Yazorman@.127.0.0.1...
> ric (rtavert@.yahoo.com) writes:
>>>>you can use Query Analyzer to generate a SELECT statement for the
>>>>desired
>>>>view...
>>
>> Is this something other than dragging over columns into the SELECT
>> statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Expanding '*' is something people have asked for, and its on the list,
but not there yet -
http://www.promptsql.com/known_bugs...nhancements.htm

Regards,
Damian (PromptSQL developer)

Erland Sommarskog wrote:
> ric (rtavert@.yahoo.com) writes:
> >>>you can use Query Analyzer to generate a SELECT statement for the desired
> >>>view...
> > Is this something other than dragging over columns into the SELECT
> > statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I said expanding '*' was on the list of things to be implemented --
we've just released PromptSQL 1.2 Beta one, and this was one of the new
features.

You can now type
SELECT *[TAB] FROM Orders, Customers
and the * will be expanded to insert all the columns in Orders and
Customers, prefixed by appropriate aliases. The separator is
customizable.

Or you can type:
SELECT o.*[TAB] FROM Orders o, Customers and the o.* will be replaced
by a list of all Orders columns, prefixed by "o."

Regards,
Damian (PromptSQL developer)

http://www.promptsql.com

Excluding empty parameters

I would like to exclude any parameter that is empty from the SELECTcommand? How do I do this? This is part of a storedprocedure.
SELECT PersonID FROM Persons WHERE
(FirstName = @.firstname) AND
(LastName = @.lastname) AND
(SSN = @.ssn) AND
(AddressID = @.addressid) AND
(DOB = @.dob) AND
(Middle = @.middle)
THanks
One way to do that is to pass a NULL value for the parameters to be excluded, and then structure your query like this:

SELECT PersonID FROM Persons WHERE

(FirstName = ISNULL(@.firstname,FirstName)) AND

(LastName = ISNULL(@.lastname,LastName)) AND

(SSN = ISNULL(@.ssn,ssn)) AND

(AddressID = ISNULL(@.addressid,AddressID)) AND

(DOB = ISNULL(@.dob,DOB)) AND

(Middle = ISNULL(@.middle,Middle))

|||Thank you Teri - As always you answer me SQL questions!

excluding a package from the build process

There is some way to exclude a package from the build process of a SSIS project ?

Cosimo

cosimog wrote:

There is some way to exclude a package from the build process of a SSIS project ?

Cosimo

I don't think so. it would be nice though wouldn't it? Try submitting the request at http://connect.microsoft.com/sqlserver/feedback

-Jamie

Exclude Words

I have built a functioning full text search tool on my companies
extranet site. I use the containstable function. I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.
Thanks,
Daniel Hirsch
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the BOL
title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.
Below are two examples CONTAINSTABLE:
use pubs
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
-- another example of using Mutiple columns (same table) with a NOT
condition:
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> I have built a functioning full text search tool on my companies
> extranet site. I use the containstable function. I was curious what
> the syntax or process would be to do a "without these words" box to
> allow users to filter their search results better.
> Thanks,
> Daniel Hirsch
>
|||Thanks Allot John. the NOT is exactly what I was looking for. I
figured there was syntax like that, but I wasn't sure exactly.
Thanks Again,
Daniel Hirsch
sql

Exclude weekends

Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
and I want to exclude wends from the Duration field
any suggestions much appreciatedGary
What is a datatype of Duration column?
Is it datetime or int/smallint/tinyint/varchar/char/text/ntext?
Why not to post DDL?
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||You could use the DATEPART function to determine if the day you are quering
is a wday.
...
WHERE DATEPART(dw, yourcolumn) IN (7,1) (Saturday and Sunday in English
settings)
The Values (7,1) depends on your language settings or the settings of your
SET @.@.DATEFIRST
Look in the help of BOL to gather more information about that.
HTH, Jens Suessmeyer.
"Gary Spence" <GarySpence@.discussions.microsoft.com> schrieb im Newsbeitrag
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||check out the datename function so you can exclude saturdays and sundays.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||set datefirst 1 -- monday = 1st day of the w
add following to the WHERE filter, to exclude both StartDateTime and
EndDateTime that falls on Sat. & Sun.
(datepart(dw, StartDateTime) in (6,7) or datepart(dw, EndDateTime) in
(6,7) )
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Gary
Here is the query:
Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
AND DATEPART(dw, StartDateTime) NOT IN (1,7)
AND DATEPART(dw, EndDateTime) NOT IN (1,7)
Please let me know your comments
best Regards,
Chandra
---
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Thanks Chandra
It works great
"Chandra" wrote:
> Hi Gary
> Here is the query:
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> AND DATEPART(dw, StartDateTime) NOT IN (1,7)
> AND DATEPART(dw, EndDateTime) NOT IN (1,7)
> Please let me know your comments
> --
> best Regards,
> Chandra
> ---
>
> "Gary Spence" wrote:
>|||Hi Gary,
I have seen many replies for your question but those would not help if the
START DATE TIME and END DATE TIME doesn’t belong to same day.
Example:-
START DATE TIME = ’29-Apr-2005 23:50’
END DATE TIME = ’02-May-2005 00:01’
I would prefer to have a function that would calculate duration in minutes
excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
Here is the function:
create function dbo.GetWDayMinutes
(
@.dtStart datetime,
@.dtEnd datetime
)
returns int
as
begin
declare @.dtTemp datetime
declare @.intMinutes int
select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
set @.intMinutes = 0
while ( @.dtTemp <= @.dtEnd )
begin
if datepart(dw, @.dtTemp) not in (1, 7)
begin
set @.intMinutes = @.intMinutes
+ datediff(mi,
case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
1, @.dtTemp) end)
end
set @.dtTemp = @.dtTemp + 1
end
return @.intMinutes
end
Sample:
select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
this will return 11 minutes.
So you have to rewrite you statement as:
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
Hope this helps you.
Best Regards,
Lakshman.
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Lakshman,
Your solution is to deduct the wend time computation from the duration.
But if to exclude all duration computation that falls on the wend, I've
suggested the following:
SELECT * FROM
( -- testing data
select '29-Apr-2005 23:50' AS d_start, '02-May-2005 00:01' AS d_end
UNION
select '14-Apr-2005 23:50' AS d_start, '15-Apr-2005 00:01' AS d_end -- only
this will record to be returned
UNION
select '11-Jun-2005 23:50' AS d_start, '12-Jun-2005 00:01' AS d_end
) a
WHERE ((datepart(dw,d_start) NOT IN (6,7) AND datepart(dw,d_end) NOT IN
(6,7)))
AND
(6 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end)) OR
7 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end))
)
"Lakshman" <Lakshman@.discussions.microsoft.com> wrote in message
news:DF45EB13-032D-435C-BBAD-7E51B6C0CDF3@.microsoft.com...
> Hi Gary,
> I have seen many replies for your question but those would not help if the
> START DATE TIME and END DATE TIME doesn't belong to same day.
> Example:-
> START DATE TIME = '29-Apr-2005 23:50'
> END DATE TIME = '02-May-2005 00:01'
> I would prefer to have a function that would calculate duration in minutes
> excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
> Here is the function:
> create function dbo.GetWDayMinutes
> (
> @.dtStart datetime,
> @.dtEnd datetime
> )
> returns int
> as
> begin
> declare @.dtTemp datetime
> declare @.intMinutes int
> select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
> select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
> set @.intMinutes = 0
> while ( @.dtTemp <= @.dtEnd )
> begin
> if datepart(dw, @.dtTemp) not in (1, 7)
> begin
> set @.intMinutes = @.intMinutes
> + datediff(mi,
> case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
> case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
> 1, @.dtTemp) end)
> end
> set @.dtTemp = @.dtTemp + 1
> end
> return @.intMinutes
> end
>
> Sample:
> select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
> this will return 11 minutes.
> So you have to rewrite you statement as:
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
> Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> Hope this helps you.
> Best Regards,
> Lakshman.
>
> "Gary Spence" wrote:
>

Exclude User Defined Data Type owner from script

I need to exclude the User Defined Data Type owner when I script out my tables. I have set the ScriptingOptions.SchemaQualify = false which removes the owner from the table declaration but not from the User Defined Data Type.

I currently have to do a search and replace in order to remove all the [dbo].[MyDataType] from the CREATE TABLE scripts.

Any ideas how to fix this when creating my table script in SMO?

Given that the schema is part of the security framework of the database, why would you want to remove the schema from the create script? It would make the script unusable as a recovery tool.

Exclude User Defined Data Type owner from script

I need to exclude the User Defined Data Type owner when I script out my tables. I have set the ScriptingOptions.SchemaQualify = false which removes the owner from the table declaration but not from the User Defined Data Type.

I currently have to do a search and replace in order to remove all the [dbo].[MyDataType] from the CREATE TABLE scripts.

Any ideas how to fix this when creating my table script in SMO?

Given that the schema is part of the security framework of the database, why would you want to remove the schema from the create script? It would make the script unusable as a recovery tool.

Exclude table from transaction log

Hi
Does anyone know if it is possible that changes to a specific table are not
recorded in the transaction log while changes to other tables are ?
If yes how can this be done?
Thanks
Daniel
Message posted via http://www.droptable.comThat's not possible. Why do you want to do this?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"DANR via droptable.com" <u26179@.uwe> wrote in message
news:662d81586fb67@.uwe...
Hi
Does anyone know if it is possible that changes to a specific table are not
recorded in the transaction log while changes to other tables are ?
If yes how can this be done?
Thanks
Daniel
Message posted via http://www.droptable.com|||Hi,
No. We can not do that. SQL Server engine by itself will log all the
transactions in to transaction log file.
Thanks
Hari
SQL Server MVP
"DANR via droptable.com" <u26179@.uwe> wrote in message
news:662d81586fb67@.uwe...
> Hi
> Does anyone know if it is possible that changes to a specific table are
> not
> recorded in the transaction log while changes to other tables are ?
> If yes how can this be done?
> Thanks
> Daniel
> --
> Message posted via http://www.droptable.com
>|||Hi Tom
Because I have a table were I register several informations with thousands o
f
records but the information is not really critical and if something goes
wrong I don't need to recover that information.
This information is increasing the size of the transaction log without being
really necessary.
Thanks
Daniel
Tom Moreau wrote:
>That's not possible. Why do you want to do this?
>Hi
>Does anyone know if it is possible that changes to a specific table are not
>recorded in the transaction log while changes to other tables are ?
>If yes how can this be done?
>Thanks
>Daniel
Message posted via http://www.droptable.com|||Ok Hari
Thanks
Daniel
Hari Prasad wrote:[vbcol=seagreen]
>Hi,
>No. We can not do that. SQL Server engine by itself will log all the
>transactions in to transaction log file.
>Thanks
>Hari
>SQL Server MVP
>
>[quoted text clipped - 5 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||If you don't need that this table is time-correlated with the rest of the ta
ble in your backups,
consider putting it in its own database. Also, this database can be in simpl
e recovery mode and you
can investigate if it is possible to input the data using a minimally logged
operation (bulk loading
is probably the best bet).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DANR via droptable.com" <u26179@.uwe> wrote in message news:662ee8ef93f81@.uwe...reen">
> Hi Tom
> Because I have a table were I register several informations with thousands
of
> records but the information is not really critical and if something goes
> wrong I don't need to recover that information.
> This information is increasing the size of the transaction log without bei
ng
> really necessary.
> Thanks
> Daniel
>
> Tom Moreau wrote:
> --
> Message posted via http://www.droptable.com
>|||Hi Tibor
Thanks for your sugestion. I already thought using that approach.
I just asked because maybe someone know other way.
Thanks to all
Regards
Daniel
Tibor Karaszi wrote:[vbcol=seagreen]
>If you don't need that this table is time-correlated with the rest of the t
able in your backups,
>consider putting it in its own database. Also, this database can be in simp
le recovery mode and you
>can investigate if it is possible to input the data using a minimally logge
d operation (bulk loading
>is probably the best bet).
>
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com

Exclude table from transaction log

Hi
Does anyone know if it is possible that changes to a specific table are not
recorded in the transaction log while changes to other tables are ?
If yes how can this be done?
Thanks
Daniel
--
Message posted via http://www.sqlmonster.comThat's not possible. Why do you want to do this?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"DANR via SQLMonster.com" <u26179@.uwe> wrote in message
news:662d81586fb67@.uwe...
Hi
Does anyone know if it is possible that changes to a specific table are not
recorded in the transaction log while changes to other tables are ?
If yes how can this be done?
Thanks
Daniel
--
Message posted via http://www.sqlmonster.com|||Hi,
No. We can not do that. SQL Server engine by itself will log all the
transactions in to transaction log file.
Thanks
Hari
SQL Server MVP
"DANR via SQLMonster.com" <u26179@.uwe> wrote in message
news:662d81586fb67@.uwe...
> Hi
> Does anyone know if it is possible that changes to a specific table are
> not
> recorded in the transaction log while changes to other tables are ?
> If yes how can this be done?
> Thanks
> Daniel
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Tom
Because I have a table were I register several informations with thousands of
records but the information is not really critical and if something goes
wrong I don't need to recover that information.
This information is increasing the size of the transaction log without being
really necessary.
Thanks
Daniel
Tom Moreau wrote:
>That's not possible. Why do you want to do this?
>Hi
>Does anyone know if it is possible that changes to a specific table are not
>recorded in the transaction log while changes to other tables are ?
>If yes how can this be done?
>Thanks
>Daniel
--
Message posted via http://www.sqlmonster.com|||Ok Hari
Thanks
Daniel
Hari Prasad wrote:
>Hi,
>No. We can not do that. SQL Server engine by itself will log all the
>transactions in to transaction log file.
>Thanks
>Hari
>SQL Server MVP
>> Hi
>[quoted text clipped - 5 lines]
>> Thanks
>> Daniel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1|||If you don't need that this table is time-correlated with the rest of the table in your backups,
consider putting it in its own database. Also, this database can be in simple recovery mode and you
can investigate if it is possible to input the data using a minimally logged operation (bulk loading
is probably the best bet).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DANR via SQLMonster.com" <u26179@.uwe> wrote in message news:662ee8ef93f81@.uwe...
> Hi Tom
> Because I have a table were I register several informations with thousands of
> records but the information is not really critical and if something goes
> wrong I don't need to recover that information.
> This information is increasing the size of the transaction log without being
> really necessary.
> Thanks
> Daniel
>
> Tom Moreau wrote:
>>That's not possible. Why do you want to do this?
>>Hi
>>Does anyone know if it is possible that changes to a specific table are not
>>recorded in the transaction log while changes to other tables are ?
>>If yes how can this be done?
>>Thanks
>>Daniel
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Tibor
Thanks for your sugestion. I already thought using that approach.
I just asked because maybe someone know other way.
Thanks to all
Regards
Daniel
Tibor Karaszi wrote:
>If you don't need that this table is time-correlated with the rest of the table in your backups,
>consider putting it in its own database. Also, this database can be in simple recovery mode and you
>can investigate if it is possible to input the data using a minimally logged operation (bulk loading
>is probably the best bet).
>> Hi Tom
>[quoted text clipped - 16 lines]
>>Thanks
>>Daniel
--
Message posted via http://www.sqlmonster.comsql

exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?
Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:

> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
> and sysdiagrams as user tables, and there are no columns in either of these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studio
> do it?
|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?
|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...[vbcol=seagreen]
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:

exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:
> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
> and sysdiagrams as user tables, and there are no columns in either of these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studio
> do it?|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:
>> I want to get a list of user tables that excludes dtproperties and
>> sysdiagrams. In Management Studio these two tables show up in their own
>> Tables > System Tables folder.
>> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
>> dtproperties
>> and sysdiagrams as user tables, and there are no columns in either of
>> these
>> views to differentiate these tables from true user tables.
>> Is there any way to exclude these two annoying tables using selection
>> criteria in any of the standard metadata views? How does Management
>> Studio
>> do it?

exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:

> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtpropertie
s
> and sysdiagrams as user tables, and there are no columns in either of thes
e
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studi
o
> do it?|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...[vbcol=seagreen]
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:
>

Exclude system IDs in 2005?

Im trying out Profiler in 2005 for my first time, but don't see a way to
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
>

Exclude system IDs in 2005?

Im trying out Profiler in 2005 for my first time, but don't see a way to
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> > Im trying out Profiler in 2005 for my first time, but don't see a way to
> > exclude system IDs as was possible in 2000 on the Filters tab. Is this
> > possible?
> >
> > TIA, ChrisR
> >
> >
>sql

Exclude Single User From Single Report

Here is my situation. I have a group set up in Active Directory for a group
of people that will share similar reports. The problem is that there is one
report that one particular user in that group doesn't need to see, but
everybody else in the group does need to see the report. Right now I have
the group added so everyone sees the report.
How can I exclude that one user from the report, but allow everyone else to
still see. This of course is without creating a new group of the same users,
but without this particular user. That would work, but seems as if there
should be another way.
Thanks!
BJbjkaledas,
I don't know how well you will like this answer, but I don't see many
other alternatives...
That report is currently inheriting its permissions from the folder it
is in. You can override that inheritance and specify item-level
permissions for that report (without affecting the other reports in
that folder).
Once you override the inheritance, you will have to delete the AD group
and add all of the group members EXCEPT for the user you want to
exclude. Unfortunately, that also means that you will have to manually
maintain those permissions when new members are added to the AD group.
Not optimal, but all I can think of.
Microsoft could solve this problem by allowing administrators to
explicity grant AND explicitly deny permissions to objects. All
permissions would stack, and in case of a conflict, it could default to
the most conservative (or limited) access level.
For example, if a user was in a group that could view and another group
that could execute, then the user could view and execute. However, if
the user was in a group that could execute and another group that could
"not execute", then it could default to the "not execute".
Maybe they will include that in the next release...
-Josh
bjkaledas wrote:
> Here is my situation. I have a group set up in Active Directory for a group
> of people that will share similar reports. The problem is that there is one
> report that one particular user in that group doesn't need to see, but
> everybody else in the group does need to see the report. Right now I have
> the group added so everyone sees the report.
> How can I exclude that one user from the report, but allow everyone else to
> still see. This of course is without creating a new group of the same users,
> but without this particular user. That would work, but seems as if there
> should be another way.
> Thanks!
> BJ|||Josh,
Thank you for your response. It seems easier to get one here than on the
forums. I had feared that was the only way around this issue. That is ok
though. Hopefully they will have exclusions in the next version. Hopefully
they will have exceptions in the Subscription schedules also!
Thanks again!
BJ
"Josh" wrote:
> bjkaledas,
> I don't know how well you will like this answer, but I don't see many
> other alternatives...
> That report is currently inheriting its permissions from the folder it
> is in. You can override that inheritance and specify item-level
> permissions for that report (without affecting the other reports in
> that folder).
> Once you override the inheritance, you will have to delete the AD group
> and add all of the group members EXCEPT for the user you want to
> exclude. Unfortunately, that also means that you will have to manually
> maintain those permissions when new members are added to the AD group.
> Not optimal, but all I can think of.
> Microsoft could solve this problem by allowing administrators to
> explicity grant AND explicitly deny permissions to objects. All
> permissions would stack, and in case of a conflict, it could default to
> the most conservative (or limited) access level.
> For example, if a user was in a group that could view and another group
> that could execute, then the user could view and execute. However, if
> the user was in a group that could execute and another group that could
> "not execute", then it could default to the "not execute".
> Maybe they will include that in the next release...
> -Josh
>
> bjkaledas wrote:
> > Here is my situation. I have a group set up in Active Directory for a group
> > of people that will share similar reports. The problem is that there is one
> > report that one particular user in that group doesn't need to see, but
> > everybody else in the group does need to see the report. Right now I have
> > the group added so everyone sees the report.
> >
> > How can I exclude that one user from the report, but allow everyone else to
> > still see. This of course is without creating a new group of the same users,
> > but without this particular user. That would work, but seems as if there
> > should be another way.
> >
> > Thanks!
> >
> > BJ
>

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

exclude rows

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:

table 1
----
id
----
a
b
c
d
e
f
g

table 2
----
id / id2
----
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4

need results
----
id
----
a
e
g

any suggestions?

thanksSELECT * FROM tblOne WHERE id_field IN (SELECT id_field FROM tblTwo WHERE other_id = 1)

exclude query

I have 16,000 rows in tblClient and 3000 rows in NewTable.
SELECT tblClient.*
FROM tblClient INNER
JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier
if I use = (equal) instead of <> (exclude), the query returns 3000 rows
when I use <> it returns 160000 rows,
if I try group by, the query bugs
what is my problem
Hi
This will depend on the values of NoDossier that you have in the two tables!
You have 3000 matches but this could be (say) 2 distinct values for NoDossier.
John
"Fernand St-Georges" wrote:

> I have 16,000 rows in tblClient and 3000 rows in NewTable.
> SELECT tblClient.*
> FROM tblClient INNER
> JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier
> if I use = (equal) instead of <> (exclude), the query returns 3000 rows
> when I use <> it returns 160000 rows,
> if I try group by, the query bugs
> what is my problem
>
>
|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)
|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)