How can I retrieve Description and other column information?

Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).

Is there any sp for this or any query for this?

Thank you all...type

sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...

You need to look up

fn_listextendedproperty

Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.

Check this table:

select * from sysproperties|||Yup... Works fine.

Thanks Brett..|||select * from sysproperties

Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.

Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.

declare @.tbid integer
declare @.colid integer

Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'

Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering

Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'

-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description

You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object

How Can I remove Table Scan?

I have the following query and just cannot seem to remove the table scan on
Table2. I have posted the indices from all tables in the join. I think Ive
covered everything according to rule. but is it overkill?
select count(a.Vh_SummVeh_id)
from Table1 a (nolock),
Table2 b (nolock),
Table3 c (nolock),
Table4 d (nolock)
where a.VH_Group_ID = b.Vh_group_id
and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
and c.vh_value_id = b.vh_value_id
and c.Vh_band_low_id = a.Vh_band_low_id
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
sp_help Table1
nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
Vh_Rating_Class_ID, Vh_band_low_id
sp_help Table2
nonclustered, unique located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Vh_reg_area_id
nonclustered located on PRIMARY Vh_VhAll_group_id
nonclustered located on PRIMARY Vh_Motor_group_id
nonclustered located on PRIMARY Vh_mfg_yr_id
nonclustered located on PRIMARY Vh_value_cv_id
nonclustered located on PRIMARY Vh_category_id
nonclustered located on PRIMARY Vh_mfg_age_id
nonclustered located on PRIMARY Vh_group_id
nonclustered located on PRIMARY Vh_rating_class_id
nonclustered located on PRIMARY Vh_model_id
nonclustered located on PRIMARY Vh_make_id
nonclustered located on PRIMARY Vh_value_id
nonclustered located on PRIMARY Vh_reg_year_id
sp_help Table3
nonclustered, unique located on PRIMARY Vh_value_id
clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper,
Vh_value_id
nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_id
sp_help Table4
nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
Tr_seq_id
nonclustered located on PRIMARY Product_id
nonclustered located on PRIMARY Inception_date_id
nonclustered located on PRIMARY Client_ver_id
nonclustered located on PRIMARY Tr_sub_type_id
nonclustered located on PRIMARY Rated_driver_id
nonclustered located on PRIMARY f_Ren_Flag
nonclustered located on PRIMARY Cp_cover_class_ind_id
nonclustered located on PRIMARY cp_attribute_id
nonclustered located on PRIMARY Cp_gen_ind_id
nonclustered located on PRIMARY Policy_id
nonclustered located on PRIMARY dim_keys_id
nonclustered located on PRIMARY Po_tr_pay_method_id
nonclustered located on PRIMARY Vh_SummVeh_id
nonclustered located on PRIMARY Po_tr_status_id
nonclustered located on PRIMARY Po_tr_bus_type_id
nonclustered located on PRIMARY Po_rd_postcode_id
nonclustered located on PRIMARY Cover_id
nonclustered located on PRIMARY Cur_trn_dt
nonclustered located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Cover_idWhat exactly is this test supposed to achieve:
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
?
This one also makes no sense to me:
and isnull(d.Vh_SummVeh_id,0) <> 0
Try replacing these two with valid tests first.
E.g.:
and (b.vehicle_id = d.vehicle_id or b.vehicle_id is null) -- just guessing
here
and (d.Vh_SummVeh_id is null)
ML|||try
Updating statistics
--
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> I have the following query and just cannot seem to remove the table scan o
n
> Table2. I have posted the indices from all tables in the join. I think Ive
> covered everything according to rule. but is it overkill?
> select count(a.Vh_SummVeh_id)
> from Table1 a (nolock),
> Table2 b (nolock),
> Table3 c (nolock),
> Table4 d (nolock)
> where a.VH_Group_ID = b.Vh_group_id
> and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
> and c.vh_value_id = b.vh_value_id
> and c.Vh_band_low_id = a.Vh_band_low_id
> and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
> and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
> sp_help Table1
> nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
> Vh_Rating_Class_ID, Vh_band_low_id
> sp_help Table2
> nonclustered, unique located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Vh_reg_area_id
> nonclustered located on PRIMARY Vh_VhAll_group_id
> nonclustered located on PRIMARY Vh_Motor_group_id
> nonclustered located on PRIMARY Vh_mfg_yr_id
> nonclustered located on PRIMARY Vh_value_cv_id
> nonclustered located on PRIMARY Vh_category_id
> nonclustered located on PRIMARY Vh_mfg_age_id
> nonclustered located on PRIMARY Vh_group_id
> nonclustered located on PRIMARY Vh_rating_class_id
> nonclustered located on PRIMARY Vh_model_id
> nonclustered located on PRIMARY Vh_make_id
> nonclustered located on PRIMARY Vh_value_id
> nonclustered located on PRIMARY Vh_reg_year_id
> sp_help Table3
> nonclustered, unique located on PRIMARY Vh_value_id
> clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper
,
> Vh_value_id
> nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_
id
> sp_help Table4
> nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
> Tr_seq_id
> nonclustered located on PRIMARY Product_id
> nonclustered located on PRIMARY Inception_date_id
> nonclustered located on PRIMARY Client_ver_id
> nonclustered located on PRIMARY Tr_sub_type_id
> nonclustered located on PRIMARY Rated_driver_id
> nonclustered located on PRIMARY f_Ren_Flag
> nonclustered located on PRIMARY Cp_cover_class_ind_id
> nonclustered located on PRIMARY cp_attribute_id
> nonclustered located on PRIMARY Cp_gen_ind_id
> nonclustered located on PRIMARY Policy_id
> nonclustered located on PRIMARY dim_keys_id
> nonclustered located on PRIMARY Po_tr_pay_method_id
> nonclustered located on PRIMARY Vh_SummVeh_id
> nonclustered located on PRIMARY Po_tr_status_id
> nonclustered located on PRIMARY Po_tr_bus_type_id
> nonclustered located on PRIMARY Po_rd_postcode_id
> nonclustered located on PRIMARY Cover_id
> nonclustered located on PRIMARY Cur_trn_dt
> nonclustered located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Cover_id
>|||ps: i would prefer not to use index hint
also: http://techrepublic.com.com/5100-22_11-1050429.html
which explains the problem with counting nulls|||Have you tried any of my suggestions? How did they affect the execution plan
?
ML|||Thanks but statistics are updated,
the coalesce reduces the amount of disk IO by 5%.
The isNull gets away from a SQL "feature" [IS NOT NULL] that ensures the
number of IS NOT NULLs counted is correct(see thread 'Are Null counts
reliable' from yesterday).
So any other ideas on why when the selected column to update from is indexed
as well as the columns in the join, a table scan still exists? And how can I
overcome this?|||Hey ML, I have.
"and (d.Vh_SummVeh_id is null)" I cannot used because it does not give me
the correct number of ISNULL records.
The other uses a table scan also.|||there are 4million records in Table2, is it possible the optimizer thinks th
e
tablescan is the best way to run the query?
"ML" wrote:

> Have you tried any of my suggestions? How did they affect the execution pl
an?
>
> ML|||I have evaluated the performance on using a hint which drops the table scan
and creates a 95% bookmark. performance did not increase.
I think the optimizer may be right to do its table scan.
Thanks all round|||There's also no clustered index on Table4. Try making the one on the
vehicle_id column in Table4 a clustered index.
MLsql

How can I remove duplicate entries in a sql query?

I have a database being populated by hits to a program on a server.
The problem is each client connection may require a few hits in a 1-2
second time frame. This is resulting in multiple database entries -
all exactly the same, except the event_id field, which is
auto-numbered.

I need a way to query the record w/out duplicates. That is, any
records exactly the same except event_id should only return one record.

Is this possible??

Thank you,

Barrytry this, not tested :-)

SELECT *
FROM table1 a,
(SELECT min(id) FROM table1) AS b
WHERE a.id = b.id

BarrySDCA wrote:
> I have a database being populated by hits to a program on a server.
> The problem is each client connection may require a few hits in a 1-2
> second time frame. This is resulting in multiple database entries -
> all exactly the same, except the event_id field, which is
> auto-numbered.
> I need a way to query the record w/out duplicates. That is, any
> records exactly the same except event_id should only return one record.
> Is this possible??
> Thank you,
> Barry|||hrm...no luck. I ran this:

SELECT * from `playback_log` a,(SELECT min(EVENT_ID) FROM
`playback_log` ) AS b WHERE a.EVENT_ID = b.EVENT_ID

Can you see anything? I appreciate the help a bunch. thank you|||Haven't tested this:

SELECT *
FROM playback_log a
WHERE a.event_id = (select min(event_id) from playback_log b
where a.field1 = b.field1)
;

You will need to list as many fields as need to match the identical rows in the
where clause of the sub-select.

Thomas

BarrySDCA wrote on 03.03.2006 00:33:
> hrm...no luck. I ran this:
>
> SELECT * from `playback_log` a,(SELECT min(EVENT_ID) FROM
> `playback_log` ) AS b WHERE a.EVENT_ID = b.EVENT_ID
>
> Can you see anything? I appreciate the help a bunch. thank you|||"Thomas Kellerer" <WVIJEVPANEHT@.spammotel.com> wrote in message
news:46petmFc69qnU1@.individual.net...
> SELECT *
> FROM playback_log a
> WHERE a.event_id = (select min(event_id) from playback_log b
> where a.field1 = b.field1)

Here's a similar possibility, without using a correlated subquery:

SELECT a.*
FROM playback_log AS a
WHERE a.event_id IN (
SELECT MIN(b.event_id)
FROM playpack_log AS b
GROUP BY b.field1, b.field2, b.field3, ...)

What I've seen missing in the several solutions proposed is any use of GROUP
BY. You'll need to GROUP BY all the fields of the table _except_ for
event_id.

Regards,
Bill K.|||Bill Karwin (bill@.karwin.com) writes:
> "Thomas Kellerer" <WVIJEVPANEHT@.spammotel.com> wrote in message
> news:46petmFc69qnU1@.individual.net...
>> SELECT *
>> FROM playback_log a
>> WHERE a.event_id = (select min(event_id) from playback_log b
>> where a.field1 = b.field1)
> Here's a similar possibility, without using a correlated subquery:
> SELECT a.*
> FROM playback_log AS a
> WHERE a.event_id IN (
> SELECT MIN(b.event_id)
> FROM playpack_log AS b
> GROUP BY b.field1, b.field2, b.field3, ...)
> What I've seen missing in the several solutions proposed is any use of
> GROUP BY. You'll need to GROUP BY all the fields of the table _except_
> for event_id.

One more variation, using a derived table:

SELECT a.*
FROM playback_log AS a
JOIN (SELECT MIN(event_id)
FROM playback_log
GROUP BY field1, field2, ...) AS b ON a.event_id = b.event_id

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not sure why you need a nested query as others have suggested.

I see two options:

Select Distinct blah1, blah2, [all fields except event_id], blah99
>From LoginTable

or

Select blah1, blah2, [all fields except event_id], blah99
>From LoginTable
Group by blah1, blah2, [all fields except event_id], blah99

if you want an event_id, just for kicks, you could add an aggregator:

Select blah1, blah2, max(event_id), blah99
>From LoginTable
Group by blah1, blah2, [all fields except event_id], blah99|||BarrySDCA wrote:
> I have a database being populated by hits to a program on a server.
> The problem is each client connection may require a few hits in a 1-2
> second time frame. This is resulting in multiple database entries -
> all exactly the same, except the event_id field, which is
> auto-numbered.
> I need a way to query the record w/out duplicates. That is, any
> records exactly the same except event_id should only return one record.
> Is this possible??
> Thank you,
> Barry

Hopefully you found the suggestions on how to filter out the duplicates
helpful.

I just want to point out that this shows the weakness of using a
pseudokey as the primary key of a table. If they really are the same
events, they shouldn't be duplicated.

Actually, I don't think you have duplicate events, since at least some
of the connection hits must have failed or timed out. Otherwise why
were there retries? So they are not really the same. Are you maybe
missing a status or result attribute?

(but the ID field is still a poor crutch for a possibly weak design.)

just some food for thought.
Ed.|||>(but the ID field is still a poor crutch for a possibly weak design.)

hmmmm. i'd prefer to think of it as the table stores ALL hits to the
table, but this particular data consumer only wants to see ONE. The
way I think about it, your natural key would be the composite of all
fields in the table; perhaps "natural", but certainly awkword.

I like figital's solution.|||It's exactly what I'm doing....I'm recording hits to an advertising
engine, before they get to the media server. Some client players hit
more than once before they connect, causing a duplicate entry. I just
want to see it sorted out w/out the duplicates. I have the
distribution server logs for accurate playback reporting, I'm only
interested in seeing cleaned up hits.

I setup code to remove the duplicates, but it's not fool proof. I will
try the suggestions here and let the group know how it goes. Here is a
link to what I'm doing:

http://www.sundiegolive.com/advertising.htm notice if two duplicates
come in now and are staggered between eachother, it shows twice still.
I will try and let you all know...

I would like to say that I didn't expect such great help from the
group. I've thrown a few questions out to different groups but usually
don't have so many great replies. Thank you everyone!|||Have you looked at Kx and Stonebreaker's StreamBase? They are databaes
designed for a high te,mproal flow of data. SQL is not always thje
right tool, in spite of what Bill Gates tells you.|||On Sat, 04 Mar 2006 11:01:08 -0800, BarrySDCA wrote:

> It's exactly what I'm doing....I'm recording hits to an advertising
> engine, before they get to the media server. Some client players hit
> more than once before they connect, causing a duplicate entry. I just
> want to see it sorted out w/out the duplicates. I have the
> distribution server logs for accurate playback reporting, I'm only
> interested in seeing cleaned up hits.

If everything is the same except the timestamp, you can round that up to
the nearest second and use a SELECT DISTINCT.

Check your SQL server's date conversion options to find out how to get
different values from the timestamp field.|||Doug wrote:
> >(but the ID field is still a poor crutch for a possibly weak design.)
> hmmmm. i'd prefer to think of it as the table stores ALL hits to the
> table, but this particular data consumer only wants to see ONE.

It isn't clear that this was ONE of several data consumers.. Even so
the ID pseudokey might not be the best choice.

> The
> way I think about it, your natural key would be the composite of all
> fields in the table; perhaps "natural", but certainly awkword.
> I like figital's solution.

It is not clear from the previous posts that the natural key would
require ALL the columns of that table. If the table is really storing
events, then the key might be as simple as a timestamp, perhaps with an
event type. It doesn't have to be ALL of the columns.

My point is too many people immediately jump to ID fields as the PK.
Many times before they even sit down to think about what entity the
table represents. If you never look for the natural key, you are sure
not to find it.

HTH
ed|||"Ed Prochak" <edprochak@.gmail.com> wrote in message
news:1141752316.933161.102260@.i39g2000cwa.googlegr oups.com...
> My point is too many people immediately jump to ID fields as the PK.

That's certainly a good point. However, I've worked on projects in which
the decision-makers wouldn't commit to _any_ combination of attributes that
would uniquely identify the entity. There were always cases where the value
in any column could be either non-unique, or else have no value specified
(i.e. NULL). Neither would they commit to any attributes that could be
reasonably stable and unchanging (though I understand that this is not
strictly necessary for a key).

So in those kinds of situations, I felt I had to create pseudokeys to have
any chance of the application working. Even if we know the best practices
for database modeling, the project on which we are working may have
constraints that don't allow us to follow those best practices.

Regards,
Bill K.|||Bill Karwin wrote:
> "Ed Prochak" <edprochak@.gmail.com> wrote in message
> news:1141752316.933161.102260@.i39g2000cwa.googlegr oups.com...
> > My point is too many people immediately jump to ID fields as the PK.
> That's certainly a good point. However, I've worked on projects in which
> the decision-makers wouldn't commit to _any_ combination of attributes that
> would uniquely identify the entity. There were always cases where the value
> in any column could be either non-unique, or else have no value specified
> (i.e. NULL). Neither would they commit to any attributes that could be
> reasonably stable and unchanging (though I understand that this is not
> strictly necessary for a key).

It's not necessarily something the End users or even managers should
make the final decision upon. I one really big project I had to good
fortune of working with a real Data Modeler. No she did not
programming, only modelling. She interviewed the users to find out what
data they had, what data they wanted and how they used it. She
organized it and created the ER model. The model included enough
information to design the DB model, which is when the PK's were
determined. Almost all of the cases you mentioned were encountered.
Each was handled by the DB designers. There are times when you create a
pseudokey and times when you split the logical table
maybe to model a more generic entity you create a pair of
parent/child tables where the parent lacks one of the fields that might
be NULL if only the Child table existed, or
maybe the entity really was 2 entities that looked similar, as an
address that represents the customer's location and the adddress that
represents the billing address. A DB that application that needed both
would be poorly served by one ADDRESS table.
What happens internally to the DB does not matter as long as you can
present the right data in the right combinations.

It is our job to engineer the software, not the end users and managers.

> So in those kinds of situations, I felt I had to create pseudokeys to have
> any chance of the application working. Even if we know the best practices
> for database modeling, the project on which we are working may have
> constraints that don't allow us to follow those best practices.
> Regards,
> Bill K.

Sounds like you take pseudokeys as the last resort, which IMO is
exactly what they are. That approach is best practices. Keep it up.

Ed

How can I read type of all fields in joined query?

How can I read type of all fields in joined query?

Is there a stored procedure method to use for this purpose?

Thanks

What language needs to know this? c#/VB? T-Sql?

If c#/vb, and you are loading things into a DataTable, the meta-data is available in the DataTable object. I suspect it's availabe in DataReaders, etc.

|||

Hi David,

I need T-SQL stored procedure that gave me type of all fields in a joined query.

Thanks for your help

|||

create procedure get_stuff

as

begin

select col_1, col_2, col_3 from some_table
union
select col_a, col_b, col_c from some_other_table

end

That's the basic syntax. Check the manual for how to add parameters if needed.

How can I query this on linked servers...

For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.

The only difference would be the way you do your SELECT. you need to include the 4-part naming convention: server.Database.schemaowner.objectname

How can I query the node in XML with XQuery?

1. I store the xml info below in the XML field Demographics in SQL 2005, I hope to query all the node info with XQuery.

The result just like
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

but I can't get the correct result using
select Demographics.query('/Folder') from store

2. Furthermore, How can I query the Name attribute of the Folder node by ID of Folder using XQuery,
If so, I can get the Name (such as "Root") after I know the ID "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"

3. Can I get the result below using XQuery? (Notice: the sub Name="Card" have no content )

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
</Bookmark>

<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>

</Folder>

Could you help me? Thanks!

=========================XML Info=====================================

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
</Folder>

======================XML Info=====================================

Before I get to the questions: There are many examples in our MSDN whitepapers. Two in particular will be very informative -

1) XML feature overview: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
2) XQuery: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp

Now for your questions:

1) I ran your code and got back the expected result - the full XML. What was your expection?

2) Try
SELECT Demographics.value ('(//Folder[@.Id = "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"]/@.Name)[1]', 'nvarchar(64)')
FROM store

When your table contains more than one row, you will get a NULL value from the rows that do not contain a <Folder> with Id "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d". Eliminate those rows using the exist() method in a (T-SQL) WHERE clause or write an outer SELECT statement. There are examples in the whitepapers above.

3) Here is one way of writing your query:
SELECT Demographics.query ('
for $f in /Folder
return
<Folder Name="{$f/@.Name}" Id="{$f/@.Id}">
{for $b in $f/Bookmark
return <Bookmark> {$b/Title}{$b/Url}</Bookmark>}
<Folder Name="{$f/Folder/@.Name}" Id="{$f/Folder/@.Id}">
{$f/Folder [@.Name ne "Card"]/Bookmark}
</Folder>
</Folder>')
FROM store

Not sure what you are trying to do - if you can tell us more, we might be able to suggest other solutions.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||

When I run "select Demographics.query('/Folder') from store", I get the Result 1, but what I expect is Result 2 ! How can I get Result 2 ？

//-- Result 1 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
//--

//-- Result 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

Your expectation is not inline with what the XPath or XQuery specification defines should be the result. The children of the folder node are part of that node, and thus they are returned. You can get the results you are looking for with an XQuery statement like this:

select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')

However, this will only work for "Folder" elements which are nested at two levels deep. You will find difficulty in supporting more generic scenarios since user defined functions are not supported in our XQuery implementation.

Another option would be to use our DML language to remove elements which are not named "Folder":

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x

This works by removing all of the nodes which are not named "Folder", along with any text content.

-John

|||

Thank you very much!

The following code you wrote is not OK
select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')
The following code is OK!!!

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x
but what does the "local-name" mean?

//--Code-
declare @.my xml

set @.my='
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark BId="f8dce8hj-846c-4e38-ab2f-6d03d9711b80">
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark BId="fgdce3ak-846c-4e38-ab2f-8i03d9711b23">
<Title>We ll e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-11-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>

<Folder Name="Card1" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark BId="ghdce3ak-456c-4e38-ab2f-5h02d9711b67">
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-12</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark BId="fkdfh3a8-456c-6y38-jk2f-5h0gh9711b45">
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-25</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>

<Folder Name="Card1in1" Id="l9dcf8fe-689c-0935-fghj-7u03d9711b5t">
<Bookmark BId="ghdfh3a8-896c-6y40-jkfg-5h0gh9711b89">
<Title>The News of CNN</Title>
<Url>https://www.cnn.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-23</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>

</Folder>

<Folder Name="Card2" Id="67dcf8fe-734c-4e56-ab2f-6d03d9711bfg">
</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

|||local-name returns the "local name" of a node. All element names are made up of two parts (this is what is called a qualified name, or QName): a) the namespace uri, and b) the local name. The namespace uri is usually indicated by a prefix which is bound to the actual namespace uri. For example, take this node:

<a:foo xmlns:a="bar" />. It has "bar" as its namespace uri, and "foo" as its local name.

So what the query does that I provided is it checks every element in the document, and if the local name does not equal "Folder", it deletes it from the document.

How can I query the node in XML with XQuery?

1. I store the xml info below in the XML field Demographics in SQL 2005, I hope to query all the node info with XQuery.

The result just like
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

but I can't get the correct result using
select Demographics.query('/Folder') from store

2. Furthermore, How can I query the Name attribute of the Folder node by ID of Folder using XQuery,
If so, I can get the Name (such as "Root") after I know the ID "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"

3. Can I get the result below using XQuery? (Notice: the sub Name="Card" have no content )

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
</Bookmark>

<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>

</Folder>

Could you help me? Thanks!

=========================XML Info=====================================

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
</Folder>

======================XML Info=====================================

Before I get to the questions: There are many examples in our MSDN whitepapers. Two in particular will be very informative -

1) XML feature overview: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
2) XQuery: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp

Now for your questions:

1) I ran your code and got back the expected result - the full XML. What was your expection?

2) Try
SELECT Demographics.value ('(//Folder[@.Id = "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"]/@.Name)[1]', 'nvarchar(64)')
FROM store

When your table contains more than one row, you will get a NULL value from the rows that do not contain a <Folder> with Id "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d". Eliminate those rows using the exist() method in a (T-SQL) WHERE clause or write an outer SELECT statement. There are examples in the whitepapers above.

3) Here is one way of writing your query:
SELECT Demographics.query ('
for $f in /Folder
return
<Folder Name="{$f/@.Name}" Id="{$f/@.Id}">
{for $b in $f/Bookmark
return <Bookmark> {$b/Title}{$b/Url}</Bookmark>}
<Folder Name="{$f/Folder/@.Name}" Id="{$f/Folder/@.Id}">
{$f/Folder [@.Name ne "Card"]/Bookmark}
</Folder>
</Folder>')
FROM store

Not sure what you are trying to do - if you can tell us more, we might be able to suggest other solutions.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||

When I run "select Demographics.query('/Folder') from store", I get the Result 1, but what I expect is Result 2 ! How can I get Result 2 ？

//-- Result 1 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
//--

//-- Result 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

Your expectation is not inline with what the XPath or XQuery specification defines should be the result. The children of the folder node are part of that node, and thus they are returned. You can get the results you are looking for with an XQuery statement like this:

select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')

However, this will only work for "Folder" elements which are nested at two levels deep. You will find difficulty in supporting more generic scenarios since user defined functions are not supported in our XQuery implementation.

Another option would be to use our DML language to remove elements which are not named "Folder":

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x

This works by removing all of the nodes which are not named "Folder", along with any text content.

-John

|||

Thank you very much!

The following code you wrote is not OK
select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')
The following code is OK!!!

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x
but what does the "local-name" mean?

//--Code-
declare @.my xml

set @.my='
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark BId="f8dce8hj-846c-4e38-ab2f-6d03d9711b80">
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark BId="fgdce3ak-846c-4e38-ab2f-8i03d9711b23">
<Title>We ll e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-11-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>

<Folder Name="Card1" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark BId="ghdce3ak-456c-4e38-ab2f-5h02d9711b67">
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-12</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark BId="fkdfh3a8-456c-6y38-jk2f-5h0gh9711b45">
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-25</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>

<Folder Name="Card1in1" Id="l9dcf8fe-689c-0935-fghj-7u03d9711b5t">
<Bookmark BId="ghdfh3a8-896c-6y40-jkfg-5h0gh9711b89">
<Title>The News of CNN</Title>
<Url>https://www.cnn.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-23</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>

</Folder>

<Folder Name="Card2" Id="67dcf8fe-734c-4e56-ab2f-6d03d9711bfg">
</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

|||local-name returns the "local name" of a node. All element names are made up of two parts (this is what is called a qualified name, or QName): a) the namespace uri, and b) the local name. The namespace uri is usually indicated by a prefix which is bound to the actual namespace uri. For example, take this node:

<a:foo xmlns:a="bar" />. It has "bar" as its namespace uri, and "foo" as its local name.

So what the query does that I provided is it checks every element in the document, and if the local name does not equal "Folder", it deletes it from the document.

how can i query the date one week back from now?

for example today is May,2 2006: my query should return all data where date = April 24, 2006.

Try this RDL expression for calculating the previous week:
=Today.AddDays(-7)

If you want to do this directly in the query, you will need to look for date related functions for the particular database you are working with. For SQL Server queries you would use the DateAdd function: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp

-- Robert

|||thanks a lot!!! sql

How can I query the appointed node in XML with XQuery quickly?

I store the xml info below in the XML field in SQL 2005, I hope to query all the Folder node with XQuery.

The result I hope to get

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

================ Stored In XML field==========================

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
</Folder>

=====================================================

Some pepole give me a anwser, It work well, but when the size of the XML field is little big, it run very slowly, could you give a code which can run quickly!

//--Code -
declare @.my xml

set @.my='
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark BId="f8dce8hj-846c-4e38-ab2f-6d03d9711b80">
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark BId="fgdce3ak-846c-4e38-ab2f-8i03d9711b23">
<Title>We ll e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-11-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>

<Folder Name="Card1" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark BId="ghdce3ak-456c-4e38-ab2f-5h02d9711b67">
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-12</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark BId="fkdfh3a8-456c-6y38-jk2f-5h0gh9711b45">
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-25</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>

<Folder Name="Card1in1" Id="l9dcf8fe-689c-0935-fghj-7u03d9711b5t">
<Bookmark BId="ghdfh3a8-896c-6y40-jkfg-5h0gh9711b89">
<Title>The News of CNN</Title>
<Url>https://www.cnn.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-23</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>

</Folder>

<Folder Name="Card2" Id="67dcf8fe-734c-4e56-ab2f-6d03d9711bfg">
</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

That ususally is done using recursion. But provided SQL Server doesn't support user-defined functions in XQuery it seems to be unfeasible to implement. Another workaround would be using XSLT.

Are you sure you need such kind of filtering? You said you only need to query some node, not filtering XML tree.

|||

Another way to write it would be to use the nodes() method to generate a relational rowset that contains Name, Id and ParentID (see the other posting for code samples), then write a recursive relational user-defined function that recomposes the hierarchy (see the FOR XML whitepaper for a code sample).

However, what I assume in your case, is that the logging of the changes is slowing you down. Have you checked what your data and IO disk load is? Do you have your log file on a different disk drive/disk spindle than your data?

Best regards

Michael

How can I Query Analyze DB Greater than 128MG

I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...

What is the work around?

Thanks in advance

JEK

If you have sql server 2005 you can use the sql server management studio to connect to your sql ce (.sdf) database.
|||In SQL Server Management Studio, in the Connect To Server dialog, click "Options >>", and you will be able to increase the max db size from the default of 128 MB.|||I need to use Query Analyzer 3.0 on the Handheld|||There is no workaround this "bug". Await the next version or move the SDF file to your desktop and use one of the tools mentioned above.

How can I Query Analyze DB Greater than 128MG

I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...

What is the work around?

Thanks in advance

JEK

If you have sql server 2005 you can use the sql server management studio to connect to your sql ce (.sdf) database.
|||In SQL Server Management Studio, in the Connect To Server dialog, click "Options >>", and you will be able to increase the max db size from the default of 128 MB.|||I need to use Query Analyzer 3.0 on the Handheld|||There is no workaround this "bug". Await the next version or move the SDF file to your desktop and use one of the tools mentioned above.

How can I query a trusted domain (from MS SQL)?

I've defined a linked ADSI server and I seem to be able to query the
local domain ( localdomain.com ) with:

DBCC TRACEON(7300)
GO
SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
''LDAP://DC=localdomain,DC=com'' ')

But I also have a trusted domain ( trusteddomain.com ) which I would
also like to query from the same SQL-enviroment but this does not work
:( :

DBCC TRACEON(7300)
GO
SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
''LDAP://DC=trusteddomain,DC=com'' ')

Error returned:
"OLE DB error trace [Non-interface error: OLE DB provider
ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IRowset::GetNextRows returned 0x8007202b]."

What am I doing wrong? From within windows-explorer I have no problem
obtaining the userlist from trusted domain when I want to assign
file-permissions.
Any idea?Write a VB script with the following:

Set objRoot = GetObject(LDAP://dc=trusteddomain,DC=com)
WScript.Echo objRoot.Name

and run it on the SQL server. IF this does not work, then a linked ADSI
server won't work either. I've never used trusted domains in W2K, but check
DNS settings. The client needs to find a domaincontroller for the given
domain. Also, you could try adding the domaincontroller of the trusted
domain to your query:

LDAP://nameofdcintrusteddomain/dc=trusteddomain,dc=com

Arild

"Ammar" <ammar_fake@.vip.hr> wrote in message
news:647d9cb9.0404211523.d8941c7@.posting.google.co m...
> I've defined a linked ADSI server and I seem to be able to query the
> local domain ( localdomain.com ) with:
> DBCC TRACEON(7300)
> GO
> SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> ''LDAP://DC=localdomain,DC=com'' ')
> But I also have a trusted domain ( trusteddomain.com ) which I would
> also like to query from the same SQL-enviroment but this does not work
> :( :
> DBCC TRACEON(7300)
> GO
> SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> ''LDAP://DC=trusteddomain,DC=com'' ')
> Error returned:
> "OLE DB error trace [Non-interface error: OLE DB provider
> ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
> DBPROPVAL_OO_BLOB being supported].
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> IRowset::GetNextRows returned 0x8007202b]."
> What am I doing wrong? From within windows-explorer I have no problem
> obtaining the userlist from trusted domain when I want to assign
> file-permissions.
> Any idea?|||If should of course be:

Set objRoot.GetObject("LDAP://dc=trusteddomain,DC=com")

(Outlook Express is a bit too _smart_ )

Arild

"Arild Bakken" <arildb_@.hotmail.com> wrote in message
news:O2N%23mZDKEHA.208@.tk2msftngp13.phx.gbl...
> Write a VB script with the following:
> Set objRoot = GetObject(LDAP://dc=trusteddomain,DC=com)
> WScript.Echo objRoot.Name
> and run it on the SQL server. IF this does not work, then a linked ADSI
> server won't work either. I've never used trusted domains in W2K, but
check
> DNS settings. The client needs to find a domaincontroller for the given
> domain. Also, you could try adding the domaincontroller of the trusted
> domain to your query:
> LDAP://nameofdcintrusteddomain/dc=trusteddomain,dc=com
>
> Arild
> "Ammar" <ammar_fake@.vip.hr> wrote in message
> news:647d9cb9.0404211523.d8941c7@.posting.google.co m...
> > I've defined a linked ADSI server and I seem to be able to query the
> > local domain ( localdomain.com ) with:
> > DBCC TRACEON(7300)
> > GO
> > SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> > ''LDAP://DC=localdomain,DC=com'' ')
> > But I also have a trusted domain ( trusteddomain.com ) which I would
> > also like to query from the same SQL-enviroment but this does not work
> > :( :
> > DBCC TRACEON(7300)
> > GO
> > SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> > ''LDAP://DC=trusteddomain,DC=com'' ')
> > Error returned:
> > "OLE DB error trace [Non-interface error: OLE DB provider
> > ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
> > DBPROPVAL_OO_BLOB being supported].
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > IRowset::GetNextRows returned 0x8007202b]."
> > What am I doing wrong? From within windows-explorer I have no problem
> > obtaining the userlist from trusted domain when I want to assign
> > file-permissions.
> > Any idea?

how can I put this into one query

I have a table (tblJobs) that has a delivery date - to this date I want to
add a set amount of 'working' days
I have a table of dates with columns that specify whether the date is a
wday and a holiday.
for each record in the tblJobs table I want the calculated date to appear in
the record.
Eg
tblJobs
JobNo DeliveryDate
G123 03/01/2006
tblHols
dt isWday isHoliday
01/01/2006 0 1
02/01/2006 1 1
03/01/2006 1 0
04/01/2006 1 0
05/01/2006 1 0
06/01/2006 1 0
07/01/2006 0 0
08/01/2006 0 0
09/01/2006 1 0
10/01/2006 1 1
11/01/2006 1 0
ResultSet
Job DeliveryDate CalcDate (i.e. DeliveryDate + 5
WorkingDays)
G123 03/01/2006 11/01/2006
I have the query to calculate the date but I don't know how to pass the
DeliveryDate for each record to the subquery
Select *,(SELECT c.dt
FROM dbo.tblHols c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte) = = = @.dte needs to equal
DeliveryDate for each record
AND 5 = (
SELECT COUNT(*)
FROM dbo.tblHols c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM tblJobs
Here are the scripts to create the tables etc
CREATE TABLE [dbo].[tblHols] (
[dt] [datetime] NOT NULL ,
[isHoliday] [bit] NULL ,
[isWday] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblHols] ADD
CONSTRAINT [PK_tblHols] PRIMARY KEY CLUSTERED
(
[dt]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblJobs] (
[Job] [nvarchar] (8) COLLATE Latin1_General_BIN NOT NULL ,
[DeliveryDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblJobs] ADD
CONSTRAINT [PK_tblJobs] PRIMARY KEY CLUSTERED
(
[Job]
) ON [PRIMARY]
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20070101'
BEGIN
INSERT dbo.tblHols(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.tblHols SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE tblHols
SET
isHoliday = 1
WHERE datepart(d,dt) IN (1,2,10)
INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G1234' AS Expr1, '20060102' AS Expr2
INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G2234' AS Expr1, '20060105' AS Expr2On Sat, 17 Dec 2005 23:14:16 -0000, Al Newbie wrote:
(snip)
>I have the query to calculate the date but I don't know how to pass the
>DeliveryDate for each record to the subquery
>Select *,(SELECT c.dt
> FROM dbo.tblHols c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte) = = = @.dte needs to equal
>DeliveryDate for each record
> AND 5 = (
> SELECT COUNT(*)
> FROM dbo.tblHols c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> ) as CalcDate
>FROM tblJobs
Hi Al,
Not sure if you still need this after my previous reply, but you can
simply replace "@.dte" with "tblJobs.DeliveryDate".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||i think it is better to be later than to be never :)
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Jobs') DROP TABLE Jobs;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Calendar') DROP TABLE Calendar;
CREATE TABLE Jobs(
job_id CHAR(4) NOT NULL PRIMARY KEY,
dlvr_dt DATETIME NOT NULL);
INSERT INTO Jobs VALUES('G123', '2006-01-03');
INSERT INTO Jobs VALUES('G234', '2005-01-01');
CREATE TABLE Calendar( -- Hols(
cal_dt DATETIME NOT NULL PRIMARY KEY,
is_wday INTEGER NOT NULL CHECK(is_wday IN(0,1)),
is_hday INTEGER NOT NULL CHECK(is_hday IN(0,1)))
INSERT INTO Calendar
SELECT '2006-01-01', 0, 1 UNION ALL
SELECT '2006-01-02', 1, 1 UNION ALL
SELECT '2006-01-03', 1, 0 UNION ALL
SELECT '2006-01-04', 1, 0 UNION ALL
SELECT '2006-01-05', 1, 0 UNION ALL
SELECT '2006-01-06', 1, 0 UNION ALL
SELECT '2006-01-07', 0, 0 UNION ALL
SELECT '2006-01-08', 0, 0 UNION ALL
SELECT '2006-01-09', 1, 0 UNION ALL
SELECT '2006-01-10', 1, 1 UNION ALL
SELECT '2006-01-11', 1, 0;
SELECT J.job_id, J.dlvr_dt, C.cal_dt
FROM Jobs as J, Calendar as C
WHERE C.cal_dt >= J.dlvr_dt
AND C.is_wday = 1 AND C.is_hday = 0
AND 6 = (SELECT COUNT(*)
FROM Calendar as C2
WHERE C2.cal_dt between J.dlvr_dt AND C.cal_dt
AND C2.is_wday = 1 AND C2.is_hday = 0)

How can I process each row in result set to access properties on another package o

Take a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>Thanks, Dan -- but I can't seem to get my head around your suggestion. Basi
cally, what I
want is to be able to specify a Source Query that would return a bunch of ro
ws. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a
"Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specif
y
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> want to invoke
> I want to set
> the best
> even though I
> "Source" record
> execute that
> User").
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrot
e:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of r
ows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
DTS[vbcol=seagreen]
specify[vbcol=seagreen]
that I[vbcol=seagreen]
row,[vbcol=seagreen]
would be[vbcol=seagreen]
Task",[vbcol=seagreen]
each[vbcol=seagreen]
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of exist
ing DTS
objects, rather than having to write a lot of code. Alas, it seems like it'
s not quite
the case, even though DTS seems uniquely qualified to do this type of thing
(almost).
Since it can use a Connection to issue a query on that remote server and pro
cess the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Que
ry Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX t
ransformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> Sorry, but I don't understand what you mean by <withOUT doing anything to
a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataS
ou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> DTS
> specify
> that I
> row,
> would be
> Task",
> each
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -
- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you c
an
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
_2_1/202-5145180-8774263" target="_blank">http://www.amazon.co.uk/exec/obidos...5145180-8774263
Regards,
Paul Ibisonsql

How can I output subset of a table to a flat file in SSIS?

Hi All,

I have a table A. I need output subset of a table A to a flat file using query, like:

select A.* from A inner join B on... ..... inner join C......where left(A.id, 3) = B.sid.... AND B.num between 100 and 200).

How can I do this in SSIS? Which data flow item I may need?

Thanks

Well there are several ways you could approach this.

One possibility is to use a "Data Reader Source" item. In the Advanced Editor of this item, under the Component Properties tab, you may specify a SqlCommand.

Another possible way is to use the "OLE DB Source" item. Double click the item to view the "OLE DB Source editor". Change the data access mode to "SQL command". Some controls will appear that can assist you with entering and validating your SQL statement.

|||

SSISy Boy wrote:

Well there are several ways you could approach this.

One possibility is to use a "Data Reader Source" item. In the Advanced Editor of this item, under the Component Properties tab, you may specify a SqlCommand.

Another possible way is to use the "OLE DB Source" item. Double click the item to view the "OLE DB Source editor". Change the data access mode to "SQL command". Some controls will appear that can assist you with entering and validating your SQL statement.

Thanks, it works!

How can i output result to a log file?

Hi all,
In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that?
Thanks first.qa has this capability. Options -> Results -> Defaul results to: FILE.

if you want to do it from command line, you can use OSQL. See book online for details.

How can I optmize this query

Hi,
How can I optmize this query
Select prodid,poid,po_date from po_sales
WHERE poid = 123 and po_sale <> (select
max(po_sale) from po_sales where poid = 123 ) and
po_sale <>(select min(po_sale) from po_sales where poid = 123)
ThanksHi
Just check this query:
Select prodid,poid,po_date from po_sales
FROM po_sales
INNER JOIN (Select poid from po_sales
WHERE poid = 123
GROUP BY poid
HAVING max(po_sale) <> po_sale AND
min(po_sale) <> po_sale) AS
Derive ON Derive.poid = po_sales.poid
is this the one that u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chris" wrote:

> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||
I would use a correlated query rather than named the Paramteres three times:
Select prodid,poid,po_date from po_sales AS po
WHERE poid = 123 and po_sale <(select
max(po_sale) from po_sales where poid = po.poid ) and
po_sale <>(select min(po_sale) from po_sales where poid = po.poid)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:762CD1A6-AD5C-4F57-837A-22E6788674EA@.microsoft.com...
> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales AS po
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = po.poid ) and
> po_sale <>(select min(po_sale) from po_sales where poid = po.poid)
> Thanks|||Chris
Untested
Select prodid,poid,po_date from po_sales
WHERE poid = 123 and po_sale not in
(
select max(po_sale)as po_sale from po_sales where poid = 123
union all
select max(po_sale) from po_sales where poid = 123
)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:762CD1A6-AD5C-4F57-837A-22E6788674EA@.microsoft.com...
> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||Depending on what's in the po_sales column, you could try this:
DROP TABLE #po_sales
CREATE TABLE #po_sales ( prodid INT, poid INT, po_sale INT, po_date DATETIME
)
INSERT INTO #po_sales SELECT 1, 123, 10, '1 Apr 2005'
INSERT INTO #po_sales SELECT 2, 123, 20, '2 Apr 2005'
INSERT INTO #po_sales SELECT 3, 123, 30, '3 Apr 2005'
INSERT INTO #po_sales SELECT 4, 123, 40, '4 Apr 2005'
-- SELECT * FROM #po_sales
-- Your query
SELECT prodid, poid, po_date
FROM #po_sales
WHERE poid = 123
AND po_sale <> ( SELECT MAX( po_sale ) FROM #po_sales WHERE poid = 123 )
AND po_sale <> ( SELECT MIN( po_sale ) FROM #po_sales WHERE poid = 123 )
-- My query
SELECT a.prodid, a.poid, a.po_date, a.po_sale
FROM #po_sales a
INNER JOIN #po_sales b ON a.poid = b.poid
WHERE a.poid = 123
GROUP BY a.prodid, a.poid, a.po_date, a.po_sale
HAVING a.po_sale > MIN( b.po_sale )
AND a.po_sale < MAX( b.po_sale )
Select them both in QA, and press Ctrl + L to compare the Execution plan
costs. Let me know how you get on.
Damien
"Chris" wrote:

> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||Chris,
It is highly unlikely that you will get any measurable performance gain
by rewriting the query. Optimizing the indexes for this query will help
(if you haven't done so already).
The query will benefit from an (nonclustered) index on
po_sales(poid,po_sale)
HTH,
Gert-Jan
Chris wrote:
> Hi,
> How can I optmize this query
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks

How can I obtain the tine via Query Analyzer?

I know that that is a stupid thing but I can't remember it.
thanks thousandsIf you meant, time, then:
SELECT CURRENT_TIMESTAMP
or
SELECT GETDATE()
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0B99C351-23F4-42CC-8B7B-F7B671B84569@.microsoft.com...
I know that that is a stupid thing but I can't remember it.
thanks thousands|||Cheers,
"Narayana Vyas Kondreddi" wrote:

> If you meant, time, then:
> SELECT CURRENT_TIMESTAMP
> or
> SELECT GETDATE()
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:0B99C351-23F4-42CC-8B7B-F7B671B84569@.microsoft.com...
> I know that that is a stupid thing but I can't remember it.
> thanks thousands
>
>|||Hi Enric
Vyas answered the question, but this is just an extension for the answer you
might be interested in it:
SELECT CONVERT(varchar(8),getdate(),108)
this will give you present time
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Enric" wrote:
> Cheers,
> "Narayana Vyas Kondreddi" wrote:
>

How can I obtain a report of users and roles in SQL Server?

I need a query (script) or a tool to obtain a report (or audit report) for
all databases instanced into SQL Server, just for an entittlement review aun
audit reports.
The fields are:
User, role, permissions, last date access
Best regards to everyone.
JosSee:-
SYSPROTECT system table and system procedure SP_HELPROTECT
Thanks
Hari
SQL Server MVP
"Jos Archondo" <jarchondo@.spvs.gov.bo> wrote in message
news:uMv%23UlC1FHA.664@.tk2msftngp13.phx.gbl...
>I need a query (script) or a tool to obtain a report (or audit report) for
> all databases instanced into SQL Server, just for an entittlement review
> aun
> audit reports.
> The fields are:
> User, role, permissions, last date access
> Best regards to everyone.
> Jos
>|||You should also run sp_helplogins. That will help define where
sp_helprotect should be run.
RLF
"Jos Archondo" <jarchondo@.spvs.gov.bo> wrote in message
news:uMv%23UlC1FHA.664@.tk2msftngp13.phx.gbl...
>I need a query (script) or a tool to obtain a report (or audit report) for
> all databases instanced into SQL Server, just for an entittlement review
> aun
> audit reports.
> The fields are:
> User, role, permissions, last date access
> Best regards to everyone.
> Jos
>

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanks
Hi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>
|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote: