Having trouble distilling something down to a single query

Ok, lets say I have 2 tables, A & B.

Lets say table a has the key A.record_id, and the field emp.

Say table b has the key B.record_id, a foreign key B.a_id that links it to table A, and the field B.date. Now, I want to join these tables as so:

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN "DBA"."B" TABLE B ON
A."RECORD_ID" = B."A_ID"}

You see, I want a list of all A.record_id, whether or not I get a return from the B table.

The problem arises when I want to limit the dates via B.date. It's clear to me what the problem is here, I just don't know a way around it.

WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

So basically, now I'm not getting any a.record_id's for a's that are linked to a b that fall outside of that date range.

Summing up I want...

All A + B where there is a B.date in that range
No A+B for results that are not within the entered date range.
All A's, regardless of if there is a linked B.
All A's, even if there are linked B's outside of the date range.
All in 1 statement (due to environment limitations).

Thanks for your help. I'm pretty much self taught here, so I apologize for not having the language knowledge to make this question more concise. Of course if I knew better how to explain what I'm trying to do then I'd probably know how to do it. ;-)

Mock Sample Data

table A
A001 bill
A002 bill
A003 bill
A004 frank
A005 frank
A006 bob

table B
B001 A001 1/1/2004
B002 A001 1/15/2004
B003 A001 4/1/2004
B004 A003 5/1/2004
B005 A004 1/1/2004
B006 A005 3/3/2004

Mock Results

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A003 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL
A006 bob NULL NULL


edit: added mock data/resultsI'd use:(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))-PatP|||Sorry, that's how it's in there now, editing above to reflect.

Not the problem.|||What version of which database engine are you using?

-PatP|||Using Sybase 9.|||I just noticed some apparent inconsistancies in your query. Could you post the entire query as you are submitting it so that I can try it? I'm using version 8, but I'd expect that to be close enough.

-PatP|||SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" A LEFT OUTER JOIN "DBA"."B" B ON
A."RECORD_ID" = B."A_ID"}
WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

Mock Current Results From Earlier Mock Data

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL

Thanks for looking at this.|||Give up? ;-)|||Try a LEFT OUTER JOIN to a Subquery that restricts your table B.

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN
(Select *
FROM "DBA"."B" TABLE B where ((B.DAte <='1/31/2004' and B.Date >='1/01/2004') OR B.DAte is NULL)) as S1
on A."RECORD_ID" = S1."A_ID"}

I may have a typo with the brackets up there, but something like that should work.

The key is that you are creating a subquery with results restricted to your data range, and then naming that subquery S1. Then the results of S1 are joined to table A.|||1. All A + B where there is a B.date in that range
2. No A+B for results that are not within the entered date range.
3. All A's, regardless of if there is a linked B.
4. All A's, even if there are linked B's outside of the date range.
5. All in 1 statement (due to environment limitations).
Unless I am missing something, - there are contradicting conditions in your requirements:

If #1 is to be met Then #3 & #4 cannot be
If #2 is to be met Then #4 cannot be
If #3 is to be met Then B.date is NULL, thus #1 cannot be
If #4 is to be met...see above

Can you clarify?

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Michael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com
|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Michael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com
|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:
> Michael Tissington wrote:
> > I'm looking at creating an association between a database and the
> > hardware that it is running on - for example by creating a license
> > key that includes a hash of the mac address.
> >
> > Obviously clients will be able to query the database directly. What
> > I'm trying to do is to tie my client application to a specific
> > database/hardware combination.
> >
> > What pieces of hardware information can SQL query ?
> >
> > How would I get the mac address that SQL is listening on?
> >
> > Any other ideas that I can use to tie the database to the hardware.
> >
> > Thanks
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick