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 weekend dates from calculated data pull

I have a business need to display some date specific data to a selcect group
of users 3 bussiness days before everyone else is allowed to see it.
THere are many fields I will be displaying across many tables. The date I am
using is ONLY in the primary table (meaning I don't have to include or
exclude data based on a date match from the other tables).
What I need to know is if there is a way in the SQL Query to not count the
weekend days as part of the 3 bussiness days. My query right now uses "WHERE
datafield < (GetDate + 5)". But this lets the users see data for the week's
Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
seen on the previous Friday. Also Not Acceptable.
Any ideas?
Thanks.
Carrie E. Wells
Web Architect/Developer
Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
move it.
On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
(snip)
>What I need to know is if there is a way in the SQL Query to not count the
>weekend days as part of the 3 bussiness days. My query right now uses "WHERE
>datafield < (GetDate + 5)". But this lets the users see data for the week's
>Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
>(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
>seen on the previous Friday. Also Not Acceptable.
Hi Carrie,
The simple, quick and dirty solution would be to use a CASE expression
to add either 3 or 5 to the current date, depending on the result of
DATEPART(day, GetDate()). But that would not take public holidays and
company holidays into account.
The best solution is to use a calendar table. You'll have to create it
once; after that, you can use it in this and many other situations. How
to make a calendar table, and many possible usees, is described at this
site: http://www.aspfaq.com/show.asp?id=2519

>Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
>move it.
I don't think you can move posts on Usenet :-)
Anyway, this group is actually intended for MSEQ (Microsoft English
Query). In practice, nobody ever posts anything about MSEQ, but the
group does catch some stray questions about SQL Server queries.
A better place for this kind of questions is the programming group at
microsoft.public.sqlserver.programming. That group is frequented by many
more experts than this one.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Since the site is 24/7 then that should be ok. The whole reason for 3 days
was to work around the schedules of our foriegn customers, who of course
don't have the same holidays. This issue was about certian Key Customers
getting the data first but not before the company rules said they could.
Thanks for the solution. I never even thought about using a Case statement
and this has been driving me crazy for two weeks. :-)
Thanks Again,
Carrie Wells
Carrie E. Wells
Web Architect/Developer
"Hugo Kornelis" wrote:

> On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
> (snip)
> Hi Carrie,
> The simple, quick and dirty solution would be to use a CASE expression
> to add either 3 or 5 to the current date, depending on the result of
> DATEPART(day, GetDate()). But that would not take public holidays and
> company holidays into account.
> The best solution is to use a calendar table. You'll have to create it
> once; after that, you can use it in this and many other situations. How
> to make a calendar table, and many possible usees, is described at this
> site: http://www.aspfaq.com/show.asp?id=2519
>
> I don't think you can move posts on Usenet :-)
> Anyway, this group is actually intended for MSEQ (Microsoft English
> Query). In practice, nobody ever posts anything about MSEQ, but the
> group does catch some stray questions about SQL Server queries.
> A better place for this kind of questions is the programming group at
> microsoft.public.sqlserver.programming. That group is frequented by many
> more experts than this one.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.

Excluding rows on a table while importing

I'm using DTSWizard to import a table from my main database to Temp.

This is the SQL statement...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)

What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?

This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.

Thanks,

Bill

Do you have to use the DTSWizard?

If not, BULK INSERT has a FIRSTROW parameter.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley,

I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.

Thanks,

Bill

|||

It is indeed a TSQL statement. The Books Online has excellent documentation and examples.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the

Create Table Query?

Thanks,

Bill

|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?

Thanks,

Bill

|||

Hi Jens,

What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.

Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.

Thanks,

Bill

|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[custom1] varchar(255),
[custom2] varchar(255),
[custom3] varchar(255),
[custom4] varchar(255),
[custom5] varchar(255),
[bounces] int
)

I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?

Thanks,

Bill

|||

You can use this as the source of the table (instead of using the table direct)

SELECT

*

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM SomeTable

) SubQuery

WHERE ROWCOUNTER>5000

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.

Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

|||Well just a comma missing

SELECT

*

FROM

(

Code Snippet

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM xlaANLsubscribers

) SubQuery

WHERE ROWCOUNTER>90091

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for getting back with me Jens.

I ran your code in DTS Wizard.

This is the error I received...

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(

SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret

excluding records that are already present in a table...

Hello, I'm stucked with trying to identify records that are already present
in a table when queried from another table...
For example, if I have 2 tables (Table A and Table B), Table B has 3 fields
(a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
into Table B from Table A if they are not already present... here's my sql
statement
insert into TableB
(a,b,c)
select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)
its returning me no records as inserted because
[select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)]
is not identifying the records that are not in. If I have only 1 primary key
then there's no problem.
Can anyone point out what's wrong with this statement? Thanks a lot in
advance.I think this will work:
select * from TableA
where not exists(select * from TableB
where TableB.a = TableA.a
and
TableB.b = TableA.b)
Bryce|||try
insert into TableB
(a,b,c)
select a,b,c from TableA
where
not exists (select 1 from TableB where TableB.a = TableA.a and TableB.b and
TableA.b)
"Nestor" wrote:

> Hello, I'm stucked with trying to identify records that are already presen
t
> in a table when queried from another table...
> For example, if I have 2 tables (Table A and Table B), Table B has 3 field
s
> (a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
> into Table B from Table A if they are not already present... here's my sql
> statement
> insert into TableB
> (a,b,c)
> select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)
> its returning me no records as inserted because
> [select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)]
> is not identifying the records that are not in. If I have only 1 primary k
ey
> then there's no problem.
> Can anyone point out what's wrong with this statement? Thanks a lot in
> advance.
>
>

Excluding part of select statement if no data is returned in results

I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.

Here is my select statement:

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:

SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!

TIA

BUMP... Somebody...|||

Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.

Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!

|||

Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

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 Export Options on Report

In the rsreportserver.config there is a <ExcludedRenderFormats> element, which allows you to list the rendering formats that are excluded from the export option for all reports.

Is this the only way you can change this? i.e. can you change this through Management Studio or Report Manager, and also can you exclude the export options for a single report rather than having to do it for all reports?

Thank you

Hi,

no this is a serverwide property which can only be administered through the "XML-Interface" <:-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||To confirm the "XML-Interface" being the rsReportServer.config file|||Yes.sql

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 duplicates after Multicast

I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.

How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?

I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.

See if the aggregate transformation can help you...

This paper has also some suggestions:

http://technet.microsoft.com/en-us/library/aa964137.aspx|||

Can you avoid creating duplicates in the first place? Perhpas the Conditional Split could be used instead of the Multicast?

I have found Sort to be the best de-duplication option, generally faster than the aggregate, but test it with your data if performance is an issue.

Excluding duplicates after Multicast

I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.

How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?

I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.

See if the aggregate transformation can help you...

This paper has also some suggestions:

http://technet.microsoft.com/en-us/library/aa964137.aspx|||

Can you avoid creating duplicates in the first place? Perhpas the Conditional Split could be used instead of the Multicast?

I have found Sort to be the best de-duplication option, generally faster than the aggregate, but test it with your data if performance is an issue.

Excluding databases from a maintenance plan

Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van Brakel
Hi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>
|||John,
Thanks for the quick respons.

> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).

> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel
|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John

Excluding databases from a maintenance plan

Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.

> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).

> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
Johnsql

Excluding databases from a maintenance plan

Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John

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

Excluding a column with snapshot replication

I have a database that I'm trying to replicate to allow users to run MI
type queries. This would remove the impact on the operational database
of long running expensive queries. The database records details of
emails including the text and is about 40 GB in size. The database is
part of a package system. One table contains all the text of all emails
in an ntext column and accounts for 25 GB. I would like to exclude the
column from replication as with it, replication takes over 12 hours and
the MI queries do not use the column. However, the queries do use views
that reference the column.
I'm using snapshot replication that runs once a day. In the
"Publication Properties" in the "Filter Columns" tab, I de-selected the
column. The target database has an identical schema to the source
database. When the distribution job runs it fails with a message
indicating it couldn't bulk load the table in question and the error
message indicates that an "Unexpected EOF encountered in BCP data-file".
I've assumed that the BCP data file has a structure that is at
variance with the table. I tried dropping the column from the target
table but replication then fails during the application of a number of
*View.sch scripts because the views reference the column. This is
despite the fact that the publication property for all database objects
is not to drop them.
Can anyone suggest a way in which I can get replication to work without
including the data in one column but to retain the complete schema.
TIA
Laurence Breeze
Laurence,
you could replicate the table (minus the problem column) to a table of
another name. Create a view which has the old tablename and queries the new
table, with an additional column containing a hardcoded null.
Rgds,
Paul Ibison
|||Thanks Paul,
This has done the trick.
Laurence
Paul Ibison wrote:
> Laurence,
> you could replicate the table (minus the problem column) to a table of
> another name. Create a view which has the old tablename and queries the new
> table, with an additional column containing a hardcoded null.
> Rgds,
> Paul Ibison
>