Materialized reference dimensions

I have a cube that has a chain ofreferenced dimensions in it. Analysis Services says all the dimensions in the chain must be "materialized". In fact, it demands it. But when I set them to "materialized", the cube won't process. I get this error message:

Errors in the OLAP storage engine: An error occurred while processing the '2004Trx' partition of the 'Membership Counts' measure group for the 'Memberships' cube from the DimensionsOnly database.

I'm stuck. Anyone have any ideas on how to get around this?

Thanks.

Are there more messages you are getting along with one you mention above?

Is there missing key or something like that?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

matching users profiles using sql

Hi folks

I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile.

I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease.

My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet.

If anyone could give me some advice on where to start i would be very grateful

I am develping in vb, with an sql server 2005 back-end

Hello my friend,

You are right about the SQL LIKE clause not being so useful when dealing with large amounts of text. I have worked on something similar before. The best thing to do is to have as many closed questions as possible regarding the profiles. And when they want to be more specific, add sub categories, and sub sub categories and so on. Then the SQL is easy. Most of the big guys do this. If you ever try and create a new hotmail account for example, they throw loads of checkboxes at you and throw the appropriate adverts at you every so often.

Kind regards

Scotty

|||

Hi Scotty

Thanks for the advice. I'm going to begin the development of this feature very soon so i will document what i have come up with here.

Matching Transactions and Duplicates

Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith

Matching Strings In Different Tables Of Same Database

I have a situation where I want to pull strings from one table of a SQL 2000
database and find matches for it in other tables of the same database and
have those values returned. i.e. In one table I have prospects and I want
to match their names to a table that stores the names of prospects turned
into customers. I want to write a query that looks through every entry and
returns a match for each corresponding value (from prospects to customers).
So if "Smith" is found in prospects I want SQL to return "Smith" in
customers with full contact info.

Any pointers on getting started on this is greatly appreciated. Or if you
could just point me to a reference. Obviously, I need to do some kind of
parsing. I just need to be pointed in the right direction.

Thx."Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon|||"Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon

matching saved searches to newly inserted record

Hello SQL wizards,
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job sers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job sers with matching searches
right now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job ser. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.

Matching records within same table

Hi all,

I've got a table named tLogonInfo with the following fields: fLogType, fDeskID, fUsername, fEventDate.

This table contains the logon and logoff history of an application. So for example the table could contain the following records:

1. 'Logon', '1', 'Jack', '2007-7-15 13:00:00'

2. 'Logon', '3', 'Paul', '2007-7-15 13:22:00'

3. 'Logoff', '1', 'Jack', '2007-7-15 14:00:00'

4. 'Logon', '2', 'Jack', '2007-7-16 10:30:00'

5. 'Logoff', '3', 'Paul', '2007-7-16 11:12:00'

6. 'Logoff', '2', 'Jack', '2007-7-16 17:10:00'

I want to make a query that has the fields Desk, User, Logon, Logoff and has the following result:

1. '1', 'Jack', '2007-7-15 13:00:00', '2007-7-15 14:00:00'

2. '3', 'Paul', '2007-7-15 13:22:00', '2007-7-16 11:12:00'

3. '2', 'Jack', '2007-7-16 10:30:00', '2007-7-16 17:10:00'

How can I do this?

if you use sql server 2005,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

;With Ordered as

(

Select

*

, Row_Number() Over(Partition By [User],[Mac] Order By [User],[Mac], [OperDateTime]) OpId

From

#logindata

)

Select

Logon.[User], LogOn.Mac, Logon.OperDateTime [Logon Time], Logoff.OperDateTime [Logoff Time]

from Ordered Logon

Left Outer Join Ordered Logoff On

Logon.[User] = Logoff.[User]

and Logon.[Mac] = Logoff.[Mac]

and Logon.OpId = Logoff.OpId -1

Where

Logon.Operation='Logon'

and Logoff.Operation='Logoff'

Drop table #logindata

|||

For SQL Server 2000,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

Select

[user],

[mac],

[OperDateTime] as [Logon Time],

(Select Min([OperDateTime])

From #logindata Sub Where

[Operation]='Logoff'

And Sub.[user]=Main.[user]

And Sub.[mac]=Main.[mac]) as [Logoff Time]

from #logindata Main

Where

[Operation]='Logon'

Drop table #logindata

Matching records using Business Intelligence Studio

Hello,

I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.

I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.

So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.

TIA,

Kweri

One solution is to start by creating an Integration Services project.

In the project, define a Data Flow task and add the following transforms:

- a Data Source transform, which reads from your database

- a Fuzzy Matching transform

- a Data Destination transform

The Fuzzy Matching Integration Services transform is intended to resolve the kind of problem you describe (matching records based on similarity)

Matching on null?

I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one of
the columns being null. For instance, here's one of the problem rows from the
transactions...
item id shelf owner group bank sum
15214 NULL300220MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214NULL300220MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
|||"Mike C#" wrote:

> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury

Matching on null?

I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one o
f
the columns being null. For instance, here's one of the problem rows from th
e
transactions...
item id shelf owner group bank sum
15214 NULL 300 220 MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214 NULL 300 220 MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))|||"Mike C#" wrote:

> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury

Matching on from a list

HI,

say I have a list from an sql statement (results list)
this list contains 10 items

In another table, in one particular column - there is a match for one of these items from the initial list.

SO... this may be the list
_____________________
itemnumber
1
2
3
4
5
6
7
8
9
10
----------

in the other table there is a match...
but just for one item on that list.
____________________
othertablefield
11
13
14
3 <-- match
99
78
---------

How do I find that match with my sql statement?SELECT [Othertablefield] FROM Table2 WHERE [Othertablefield] NOT IN (SELECT [itemnumber] FROM Table1)

HTH

Matching Names

Hi All,
I have a table with two columns that I want to match but am unsure of
how to.
The Columns are called "User_Name" and Managed_By" the user_name is
entered as "Fred Flintstone" while the Managed_By is entered are
"Flintstone, Fred".
To the human eye you can see that they are the same person but how can
i do that match in SQL?
I am using a SQL 2000 server
Here are 5 rows of data that I am trying to match from my table, there
are other columns in the table such as Row_Date, Acc_No
What I want to do is bring back all of the rows where the managed_by is
equal to the user_name
Thanks
Mark
Sample Data>>>>>>>>>>>>>>>>>>>>>>>
Managed_By User_Name
Ward, Kimberley Kimberley Ward
Pinder, Louise Rachel Brooks
Services, Credit Rob Mackey
Hatfield, Rebecca Joanne Fixter
Hatfield, Rebecca Rebecca HatfieldTry,
use northwind
go
declare @.t table (
Managed_By varchar(50),
[User_Name] varchar(50)
)
insert into @.t values('Ward, Kimberley', 'Kimberley Ward')
insert into @.t values('Pinder, Louise', 'Rachel Brooks')
insert into @.t values('Services, Credit', 'Rob Mackey')
insert into @.t values('Hatfield, Rebecca', 'Joanne Fixter')
insert into @.t values('Hatfield, Rebecca', 'Rebecca Hatfield')
select
*
from
@.t as a
where
[User_Name] = parsename(replace(Managed_By, ', ', '.'), 1) + ' ' +
parsename(replace(Managed_By, ', ', '.'), 2)
go
AMB
"Sh0t2bts" wrote:

> Hi All,
> I have a table with two columns that I want to match but am unsure of
> how to.
> The Columns are called "User_Name" and Managed_By" the user_name is
> entered as "Fred Flintstone" while the Managed_By is entered are
> "Flintstone, Fred".
> To the human eye you can see that they are the same person but how can
> i do that match in SQL?
> I am using a SQL 2000 server
> Here are 5 rows of data that I am trying to match from my table, there
> are other columns in the table such as Row_Date, Acc_No
> What I want to do is bring back all of the rows where the managed_by is
> equal to the user_name
> Thanks
> Mark
> Sample Data>>>>>>>>>>>>>>>>>>>>>>>
> Managed_By User_Name
> Ward, Kimberley Kimberley Ward
> Pinder, Louise Rachel Brooks
> Services, Credit Rob Mackey
> Hatfield, Rebecca Joanne Fixter
> Hatfield, Rebecca Rebecca Hatfield
>

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...Kuttan
SELECT cisco .*, ciscocom.*
FROM cisco
FULL OUTER JOIN
ciscocom
ON cisco .c1 = ciscocom.c1
AND cisco .c2 = ciscocom.c2
..
AND cisco .cn = ciscocom.cn
WHERE cisco .key IS NULL OR ciscocom.key IS NULL
"Kuttan" <vvyshak@.gmail.com> wrote in message
news:1141210889.041213.70320@.v46g2000cwv.googlegroups.com...
> Hi all..
> I have two tables such as cisco and ciscocom. and i wan to compare each
> row of ciscocom with cisco having same column values. i wan to get the
> count of matching columns for each row in cisco...
> eg:
> Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
> columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
> for each row in ciscocom. please provide me with the procedure...
> Waiting for your response...
>|||thanks a lot..
But it doesnt worked.
Actually i wan the columns that matches with each rows of the cisco
from ciscocom...
Anyway thanx a lot for your response...
thank u so much..|||Hope this is what you're looking for
SELECT COUNT (cisco.Products)
FROM cisco INNER JOIN ciscocom
ON
cisco.Products=cisco.Products AND
cisco.fw=cisco.fw AND
cisco.ports=cisco.ports
/*
* If you want to specify a value for a column
*
*/
WHERE cisco.Products='your_value'

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:01:21 -0800, Kuttan wrote:

>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
Not sure if I fully understand your requirements. If the answer below is
not what you're looking for, then please check www.aspfaq.com/5006 to
find out how to post CREATE TABLE statements, INSERT statements and
required results in order to get better help.
SELECT a.KeyColumn,
CASE WHEN a.DataColumn1 = b.DataColumn1 THEN 1 ELSE 0 END
+ CASE WHEN a.DataColumn2 = b.DataColumn2 THEN 1 ELSE 0 END
.....
+ CASE WHEN a.DataColumnN = b.DataColumnN THEN 1 ELSE 0 END
AS MatchCount
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.KeyColumn = b.KeyColumn
Hugo Kornelis, SQL Server MVP

matching inbound and outbound messages

When you begin a dialog, you do so between source and target. A conversation is begun on both source and target, and messages flow between the two. What would cause messages to build up on the source in sys.transmission_queue and not get transmitted to the target, with no error in the transmission_status column of sys.transmission_queues? The dialogs on the source have a state of 'co', indicating that messages should be flowing....

I checked the status of the queue I am having a problem with in sys.service_queues; all my queues have the is_receive_enabled and is_enqueue_enabled set to 1. I altered the state of the endpoint on each server to started, even though they were already started. I visited remus's blog and went down the checklist of things to look for when troubleshooting dialogs.

I attached the profiler to both source and target. Didnt see anything on the source to indicate an error (textdata simply said conversing for EventClass Broker:Conversation, and nothing for EventClass Broker:MessageClassify)

On the target I see stuff related to service broker, but no errors.

I created new dialogs between the source and the target and they are working fine.

What else do I need to check? Is there any way to see what sender conversation maps to which target conversation? Is there anyway to move messages from one conversation to another conversation (not to another conversation group)?

Thanks

Do you have the security events turned on in the profiler as well as the broker events? I suspect you are failing a security check of some kind. You will have to enable the security events in the profiler to see this.

-Gerald

|||

The dialog_id in sys.conversation_endpoints is the same on both the initiator and the target conversation endpoints.

The event Gerald is talking about is 'Security Security/Audit Broker Conversation' (i.e. it is in the 'Seucrity Audit' section).

HTH,
~ Remus

|||

Thanks.

This information was useful.

For clarification purposed, when I end the target end of a conversation, shouldnt the status of the source end of the conversation change? to 'do'? Should I still be able to send messages on the source end, and see nothing in the transmission_status column of sys.transmission_queue?

|||

When one ends one conversation endpoint, an 'end dialog' message is sent to the other conversation endpoint. As soon as this message reaches the other conversation endpoint that endpoint will change status to 'DO'.

After one ends a conversation, it cannot send messages or receive messages from/to it anymore.
After one receives an 'end dialog' message there is no reason to send anymore messages, since the peer has already announced that the conversation is ended from his point of views. Typically applications should react to an 'end dialog' message by ending the local endpoint of the conversation (using END CONVERSATION). Message still in transmission_queue will be cleaned up.

Whether it is valid for an application to end a conversation while there are still messages to be delivered, this depends on the application semantics. But we must guarantee that after one issued an END CONVERSATION it will never receive a message from this conversation. W/o this guarantee it would be very difficult to write Service Broker apps.

HTH,
~ Remus

Matching fileds between two tables

I hope I can explain this a little better. I have two tables that I need
information from. The first table has all but two fields that I need. I am
having two problems. First all I want to do is read the first table take the
part number and check the second table. If it is not there I want print the
information from the table and continue on reading the first table and if it
is there I want to take the invoice and print it with the information from
the first table. The second problem is the part number in the second table
is part of a large field(132 bytes). It is always at the same postion 10
bytes in the field
It sounds like you'll want an outer join. Example:
SELECT T1.col1, T1.col2, T1.part_num, T2.col1, T2.col2
FROM Table1 AS T1
LEFT OUTER JOIN Table2 AS T2
ON T1.part_num = SUBSTRING(T2.large_col, 10, 5) ;
David Portas
SQL Server MVP
"Daniell" <Daniell@.discussions.microsoft.com> wrote in message
news:E1187FC7-9EA8-4EEE-B142-ECFA9807991A@.microsoft.com...
>I hope I can explain this a little better. I have two tables that I need
> information from. The first table has all but two fields that I need. I
> am
> having two problems. First all I want to do is read the first table take
> the
> part number and check the second table. If it is not there I want print
> the
> information from the table and continue on reading the first table and if
> it
> is there I want to take the invoice and print it with the information from
> the first table. The second problem is the part number in the second
> table
> is part of a large field(132 bytes). It is always at the same postion 10
> bytes in the field
|||Thanks I will give that a try.
"David Portas" wrote:

> It sounds like you'll want an outer join. Example:
> SELECT T1.col1, T1.col2, T1.part_num, T2.col1, T2.col2
> FROM Table1 AS T1
> LEFT OUTER JOIN Table2 AS T2
> ON T1.part_num = SUBSTRING(T2.large_col, 10, 5) ;
> --
> David Portas
> SQL Server MVP
> --
> "Daniell" <Daniell@.discussions.microsoft.com> wrote in message
> news:E1187FC7-9EA8-4EEE-B142-ECFA9807991A@.microsoft.com...
>
>

Matching Debits and Credits query

I have a SQL statement that matches credit amounts to debit amounts which works fine except in cases when there is more than one debit amount for the same value, the code then applies the 1 credit to all the debits of the same amount.

eg. if I have the following

Amount Inv. No.
$200.00 345
$300.00 567
$200.00 129

In the above example using the code below, if I have a credit of $200.00 it will be applied to both inv nos. 345 and 129. I would just like it applied to either 345 OR 129.

SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
FROM Pos LEFT JOIN Neg ON (Pos.osamt = abs(Neg.osamt)) AND (Pos.ClientNo = Neg.ClientNo)
WHERE (((Neg.osamt) Is Null));

Any help would be appreciated.Your design is extremely flawed, matching debits to credits by amount only can result in applying the incorrect credit/debit combination even if the client# is the same.

:rolleyes:|||That's only part of it. I just need it resolved.|||I would just like it applied to either 345 OR 129.
Well, it is YOU who has to know whether it will be 345 or 129. What happens when there's another Inv. no with the same credit ($200)? And another?

There, of course, is a way - you could, for example, choose MIN(inv_no) or MAX(inv_no) or ...

Data model is awful; you'd better change it (if you can), because this promises you only sweat, blood and tears.|||...just out of curiosity how do you propose to match a payment against an invoice where the payer is disputing part of the invoice (ie they are paying some, but not all of the invoice), or where the payer makes a single payment covering many invoices?

Matching Columns

All

I have a table which contains 4 columns each of which are NULL or contain a 6 digit code.
Here is a sample of the table content:

COL1 COL2 COL3 COL4
-- -- -- --
452359 NULL NULL 347406
NULL NULL 347406 347406
592319 NULL 347406 347406
592319 150009 347406 347406
592319 150010 347406 347406

Through out the table any number of the columns can be null.

I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4.
So, the only rows I want from the sample data above are rows 1, 4 and 5.

I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)

Regards,
KatherineYou'll have to test this carefully, but I'd use something like:DELETE FROM foo
WHERE (Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL OR Col4 IS NULL)
AND EXISTS (SELECT *
FROM foo AS b
WHERE b.Col1 = Coalesce(foo.Col1, b.Col1)
AND b.Col2 = Coalesce(foo.Col2, b.Col2)
AND b.Col3 = Coalesce(foo.Col3, b.Col3)
AND b.Col4 = Coalesce(foo.Col4, b.Col4))-PatP|||pat: sorry: :)
insert...
select 123, null, null, null
union
select null, 123, null, null
union
select 123, 123, null, null

gives:
col1 col2 col3 col4
---- ---- ---- ----
NULL 123 NULL NULL
123 NULL NULL NULL
123 123 NULL NULL

Turns out it's a nice challenge, at least for me...|||I know there *has* to be a better way to do this... but here goes.

create table test (a int null, b int null, c int null, d int null)

insert into test(a,b,c,d)
select 123, null, null, null
union
select null, 123, null, null
union
select 123, 123, null, null

delete from t1
from test t1
inner join test t2
on (t1.a is null or t1.a=t2.a)
and (t1.b is null or t1.b=t2.b)
and (t1.c is null or t1.c=t2.c)
and (t1.d is null or t1.d=t2.d)
where
(
((t1.a!=t2.a and (t1.a is not null and t2.a is not null)) or (t1.a is null and t2.a is not null) or (t1.a is not null and t2.a is null))
or ((t1.b!=t2.b and (t1.b is not null and t2.b is not null)) or (t1.b is null and t2.b is not null) or (t1.b is not null and t2.b is null))
or ((t1.c!=t2.c and (t1.c is not null and t2.c is not null)) or (t1.c is null and t2.c is not null) or (t1.c is not null and t2.c is null))
or ((t1.d!=t2.d and (t1.d is not null and t2.d is not null)) or (t1.d is null and t2.d is not null) or (t1.d is not null and t2.d is null))
)

You'd have to run a dedup after this if you've got duplicate rows.
That ugly WHERE is just killin' me, but dealing with the NULLs is a real PITA.|||I tried it by creating a new table, inserting those with the least nulls then winding up to the most nulls skipping those with matching values. The way I have it now is a real pain and doesn't work properly.. so...|||Like a lot of tricky SQL problems, the issue here is not the question you are asking, but the fact that your schema is not normalized. Fix it, if at all possible, or you are sure to run into more problems in the future.

This method solves your problem but normalizing your data into a temporary table, and then searching the temporary table for record groups that are a subset of other record groups. It presupposes that you have a unique primary key in your original table.

DECLARE @.TEMPNORMAL TABLE
(PKEY INT,
COLVALUE VARCHAR(6))

DECLARE @.PKEYITEMCOUNT TABLE
(PKEY INT,
ITEMCOUNT INT)

INSERT INTO @.TEMPNORMAL
(PKEY,
COLVALUE)
SELECT DISTINCT
PKEY,
COLVALUE
FROM
(Select PKEY, COL1 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL2 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL3 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL4 as COLVALUE from YOURTABLE) NORMALTRANSFORM

INSERT INTO @.PKEYITEMCOUNT
(PKEY,
ITEMCOUNT)
SELECT PKEY,
COUNT(*)
FROM @.TEMPNORMAL
GROUP BY PKEY

DELETE
FROM YOURTABLE
INNER JOIN
(SELECT PKEY
FROM @.PKEYITEMCOUNT PKEYITEMCOUNT1
INNER JOIN
(SELECT PKEY1,
PKEY2,
COUNT(*) MATCHCOUNT
FROM (SELECT DISTINCT
TEMPNORMAL1.PKEY PKEY1,
TEMPNORMAL2.PKEY PKEY2,
TEMPNORMAL1.COLVALUE
FROM @.TEMPNORMAL TEMPNORMAL1
INNER JOIN @.TEMPNORMAL TEMPNORMAL2
ON TEMPNORMAL1.COLVALUE = TEMPNORMAL2.COLVALUE
AND TEMPNORMAL1.PKEY <> TEMPNORMAL2.PKEY) MATCHQUERY
GROUP BY PKEY1,
PKEY2) PKEYMATCHES
ON PKEYITEMCOUNT.PKEY = PKEYMATCHES.PKEY
AND PKEYITEMCOUNT.ITEMCOUNT = PKEYMATCHES.MATCHCOUNT
INNER JOIN @.PKEYITEMCOUNT PKEYITEMCOUNT2
ON PKEYMATCHES.PKEY2 = PKEYITEMCOUNT2.PKEY
AND PKEYITEMCOUNT1.ITEMCOUNT < PKEYITEMCOUNT2.ITEMCOUNT) SUPERFLUOUSRECORDS
ON YOURTABLE.PKEY = SUPERFLUOUSRECORDS.PKEY

blindman|||Oops...insert statement should read like this:

INSERT INTO @.TEMPNORMAL
(PKEY,
COLVALUE)
SELECT DISTINCT
PKEY,
COLVALUE
FROM
(Select PKEY, COL1 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL2 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL3 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL4 as COLVALUE from YOURTABLE where COL1 is not null) NORMALTRANSFORM

Matching Collation and Sort

I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>>
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server
>>that produced the info below (by running sp_helpsort) is a SQL 2000 SP3
>>server as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>>I just installed a named instance and the output of sp_helpsort matches
>>your current production server and the collation I chose was
>>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>>The descriptive collation description during install was (under the SQL
>>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>>Character set"
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code
>> Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code
>> Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get
>> the database collation. Have you moved a database from one server to
>> another with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot
>>seem to make the right choices to prevent collation conflicts in my
>>stored procedures. Can ANYONE please give me some insight on what
>>options to select in order to match this in SQL 2000 Service pack 3?
>>The server that produced the info below (by running sp_helpsort) is a
>>SQL 2000 SP3 server as well.
>> The closest I've been able to come at this point is to match
>> everything except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>>
>

Matching Collation and Sort

I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
You can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>
|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>
|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>
|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>

Matching Collation and Sort

I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation')
to get the database
collation. Have you moved a database from one server to another with a
different collation ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation')
to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>

Matching algorithm

I have a db full of names and identifiers. Was wondering if anyone could direct me towards a good dupe eliminating algorithm or bit of logic to look at?
Thanks!What will be your basis for eliminating duplicates ?|||Originally posted by rnealejr
What will be your basis for eliminating duplicates ?

Name dupes and identifiers, some names may be similar, some addresses may be simialr. Each row will have approximately 10 identifiers, but some will be nnull. It is fairly dirty data.|||But how do you determine which rows to delete (like keying off the most recent timestamp) or will it be a visual comparison (so will you need to route these duplicates to a holding table) ?|||Originally posted by rnealejr
But how do you determine which rows to delete (like keying off the most recent timestamp) or will it be a visual comparison (so will you need to route these duplicates to a holding table) ?

Yes I wil need to move them to a holding table. It will be compared visually using all.|||You should post more info about your table of duplicities.
Generic query looks like this

select d1.*
into ClearTable
from DuplTable d1
join
(
select IdCol1,IdCol2,...
from DuplTable d2
group by IdCol1,IdCol2,...
having count(*)=1
) x on (d1.IdCol1=x.IdCol1 or (d1.IdCol1 is null and x.IdCol1 is null))
and (d1.IdCol2=x.IdCol2 or (d1.IdCol2 is null and x.IdCol2 is null))
.
.
.
select d1.*
into HoldingTable
from DuplTable d1
join
(
select IdCol1,IdCol2,...
from DuplTable d2
group by IdCol1,IdCol2,...
having count(*)>1
) x on (d1.IdCol1=x.IdCol1 or (d1.IdCol1 is null and x.IdCol1 is null))
and (d1.IdCol2=x.IdCol2 or (d1.IdCol2 is null and x.IdCol2 is null))
.
.
.
select * from ClearTable
select * from HoldingTable|||Originally posted by ispaleny
You should post more info about your table of duplicities.


Awesome, I am going to try that format. My original table is pretty dirty. It is Lname, fname, SSN, address, city, state, country, phone number, etc., about 5 more identifiers. It is around 3 million records, some complete, some not. Spellings are different in some cases, middle names are there in other cases.

Thanks for your help.
:)

Matching a Views columns to its underlying tables columns

Hello,

Using SQL Server 2000, I'm trying to put together a query that will
tell me the following information about a view:
The View Name
The names of the View's columns
The names of the source tables used in the view
The names of the columns that are used from the source tables

Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
below, which gives me the View Name, Source Table Name, and Source
Column Name. And I can easily enough get the View columns from the
syscolumns table. The problem is that I haven't figured out how to
link a source column name to a view column name. Any help would be
appreciated.

Gary

select
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
from
sysobjects t_obj,
sysobjects v_obj,
sysdepends dep,
syscolumns t_col
where
v_obj.xtype = 'V'
and dep.id = v_obj.id
and dep.depid = t_obj.id
and t_obj.id = t_col.id
and dep.depnumber = t_col.colid
order by
v_obj.name,
t_obj.name,
t_col.namegaryderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> Hello,
> Using SQL Server 2000, I'm trying to put together a query that will
> tell me the following information about a view:
> The View Name
> The names of the View's columns
> The names of the source tables used in the view
> The names of the columns that are used from the source tables
> Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> below, which gives me the View Name, Source Table Name, and Source
> Column Name. And I can easily enough get the View columns from the
> syscolumns table. The problem is that I haven't figured out how to
> link a source column name to a view column name. Any help would be
> appreciated.
> Gary
>
> select
> v_obj.name as ViewName,
> t_obj.name as SourceTable,
> t_col.name as SourceColumn
> from
> sysobjects t_obj,
> sysobjects v_obj,
> sysdepends dep,
> syscolumns t_col
> where
> v_obj.xtype = 'V'
> and dep.id = v_obj.id
> and dep.depid = t_obj.id
> and t_obj.id = t_col.id
> and dep.depnumber = t_col.colid
> order by
> v_obj.name,
> t_obj.name,
> t_col.name

I don't believe that this information is available - sysdepends
records that the dependency exists, but not exactly what the
dependency is. The mapping of view to table columns could be 1:N or
M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
worth the effort to try and capture the detailed column mapping.

Simon|||Simon,

Thanks for the information, even though it wasn't what I wanted to hear.

Gary

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec@.posting.google.com>...
> garyderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> > Hello,
> > Using SQL Server 2000, I'm trying to put together a query that will
> > tell me the following information about a view:
> > The View Name
> > The names of the View's columns
> > The names of the source tables used in the view
> > The names of the columns that are used from the source tables
> > Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> > below, which gives me the View Name, Source Table Name, and Source
> > Column Name. And I can easily enough get the View columns from the
> > syscolumns table. The problem is that I haven't figured out how to
> > link a source column name to a view column name. Any help would be
> > appreciated.
> > Gary
> > select
> > v_obj.name as ViewName,
> > t_obj.name as SourceTable,
> > t_col.name as SourceColumn
> > from
> > sysobjects t_obj,
> > sysobjects v_obj,
> > sysdepends dep,
> > syscolumns t_col
> > where
> > v_obj.xtype = 'V'
> > and dep.id = v_obj.id
> > and dep.depid = t_obj.id
> > and t_obj.id = t_col.id
> > and dep.depnumber = t_col.colid
> > order by
> > v_obj.name,
> > t_obj.name,
> > t_col.name
> I don't believe that this information is available - sysdepends
> records that the dependency exists, but not exactly what the
> dependency is. The mapping of view to table columns could be 1:N or
> M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
> worth the effort to try and capture the detailed column mapping.
> Simon

Match timed events

/*
Anyone?
An Object is. An object has an owner.
Events happen to an object at given times
I would like to list out all events for an object
and show who was the owner of it at that time.
However my mind is a blank!
My legacy data structure looks something like this
*/
CREATE TABLE EVENT (
EVENTID Int IDENTITY(1,1) PRIMARY KEY,
OBJECTID CHAR(4),
EVENTDATE CHAR(8)
)
CREATE TABLE OBJECTOWNER (
OBJECTID CHAR(4) NOT NULL,
OWNERID CHAR(4) NOT NULL,
OWNERFROM CHAR(8) NOT NULL
)
ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
(
[OBJECTID],
[OWNERID],
[OWNERFROM]
)
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A111', '19801201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A692', '19921201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'B386', '20011201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F279', '20041201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F111', '20060310')
/*I presume I need the MAX OWNERFROM value that less then or equal to the
EventDate, along the lines of*/
SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
recent Owner' from ObjectOwner
WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
/*Which gives the owner of the object for a given date,
and I am sure I need to join in a select returning the max <=...
I could do it by inserting into a temp table and updating
but I though there must be a more elegant solution
The listing I'd like to retrive is
EVENTID, OBJECTID, EVENTDATE, OWNERID,
EVENTID OBJECTID EVENTDATE OWNERID
-- -- -- --
1 1234 19920101 A111
2 1234 20050601 B386
3 1234 20060122 F279
Any help would be greatly appreciated.
Cheers!
Simon
*/Different ways...
One with correlated subqueries in SELECT clause :
SELECT EVENTID, OBJECTID, EVENTDATE,
(SELECT OWNERID
FROM OBJECTOWNER O
WHERE O.OBJECTID = E.OBJECTID
AND OWNERFROM = (SELECT MAX(OWNERFROM)
FROM OBJECTOWNER O2
WHERE OWNERFROM <= E.EVENTDATE
AND O2.OBJECTID = E.OBJECTID)) AS
OWNERID_AT_TIME_EVENT
FROM EVENT E
A +
Simon a crit :
> /*
> Anyone?
> An Object is. An object has an owner.
> Events happen to an object at given times
> I would like to list out all events for an object
> and show who was the owner of it at that time.
> However my mind is a blank!
> My legacy data structure looks something like this
> */
>
> CREATE TABLE EVENT (
> EVENTID Int IDENTITY(1,1) PRIMARY KEY,
> OBJECTID CHAR(4),
> EVENTDATE CHAR(8)
> )
> CREATE TABLE OBJECTOWNER (
> OBJECTID CHAR(4) NOT NULL,
> OWNERID CHAR(4) NOT NULL,
> OWNERFROM CHAR(8) NOT NULL
> )
> ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
> CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
> (
> [OBJECTID],
> [OWNERID],
> [OWNERFROM]
> )
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A111', '19801201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A692', '19921201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'B386', '20011201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F279', '20041201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F111', '20060310')
>
> /*I presume I need the MAX OWNERFROM value that less then or equal to the
> EventDate, along the lines of*/
> SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
> recent Owner' from ObjectOwner
> WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
> /*Which gives the owner of the object for a given date,
> and I am sure I need to join in a select returning the max <=...
> I could do it by inserting into a temp table and updating
> but I though there must be a more elegant solution
>
> The listing I'd like to retrive is
> EVENTID, OBJECTID, EVENTDATE, OWNERID,
> EVENTID OBJECTID EVENTDATE OWNERID
> -- -- -- --
> 1 1234 19920101 A111
> 2 1234 20050601 B386
> 3 1234 20060122 F279
> Any help would be greatly appreciated.
> Cheers!
> Simon
> */
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro [MVP] wrote:
> Different ways...
> One with correlated subqueries in SELECT clause :
>
> SELECT EVENTID, OBJECTID, EVENTDATE,
> (SELECT OWNERID
> FROM OBJECTOWNER O
> WHERE O.OBJECTID = E.OBJECTID
> AND OWNERFROM = (SELECT MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID)) AS
> OWNERID_AT_TIME_EVENT
> FROM EVENT E
> A +
Much appreciated, that is exactly what I needed. I ahd just the (SELECT
MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID))
JOINed as was getting nowhere fast.
Cheers!