having example.

hi can anyone explain to me the diff between having and where?
set1
a b
1 2
1 NULL
1 3
2 NULL
2 1
1 NULL
select a,b from set1
where b =2
group by a,b
and
select a,b from set1
group by a,b
having b =2
both the above querries give me teh same result.
so i cant tell teh difference.WHERE applies before the grouping and aggregation, HAVING applies
afterwards the aggregation.
HTH, Jens Suessmeyer.|||You can use HAVING to apply a filter on grouped sets like
select a,b from set1
group by a,b
having COUNT(8) >2
Otherwise HAVING servers the same purpose as WHERE
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:uiO5W9PPGHA.3528@.TK2MSFTNGP10.phx.gbl...
> hi can anyone explain to me the diff between having and where?
> set1
> a b
> 1 2
> 1 NULL
> 1 3
> 2 NULL
> 2 1
> 1 NULL
>
> select a,b from set1
> where b =2
> group by a,b
> and
> select a,b from set1
> group by a,b
> having b =2
>
> both the above querries give me teh same result.
> so i cant tell teh difference.
>

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 optional parameters

On many reports I have optional parameters, for example if the employee is filled in it will run for that employee but if it's null it runs for all employees. Which is the best way to handle this?

The two options I am currently looking at are:

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

select * from employee where isnull(@.empID, employee.id) = employee.id

Anyone else have a different solution?

Currently we use the OR approach and I wanted to see if anyone had any thoughts before switching to using IsNull, which seems a bit better.

OR can be slow. I always use the ISNULL approach.

|||

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

|||

The problem with using the if (@.EmpID is null) is that then you need to maintain two queries. And for some reports that have 3 or more optional parameters this could mean having 8 or more queries to maintain just for optional parameters. We also thought about using an expression for the query but some of the clients like to use the generic query view if they edit the report so this makes that option unavailable.

I think we will be using the isnull option for our solution.

Thanks

|||

I thought this blog post was useful for this topic. You can combine approaches if you want.

http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

|||

Techquest wrote:

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

Not everyone uses stored procs to serve reports.

|||

Found an issue using the IsNull approach. If the optional parameter can be null in the table it will not return the null values.

EX. Middle name is a nullable field

select * from Employee where MiddleName = IsNull(@.MiddleName, MiddleName)

This won't return all values when the parameter is null, will return all values that have any non null MiddleName.

Think it may be safer to use the OR approach now because other people that use modify reports not familiar with this may use IsNull is cases it should not be used.

Anyone have any suggestions around this? I know most of the time the optional parameter does not go against a nullable field but could cause a lot of problems if you did it by mistake.

Thanks,

Kevin

|||

Yeah, avoid nullable fields

In case you do have them though you could use:

select * from Employee where COALESCE(MiddleName, '') = COALESCE(@.MiddleName, MiddleName, '')

COALESCE is basibcally like ISNULL but it takes any number of parameters rather than just 2 and returns the first non-null value.

|||

COALESCE functions differently than IsNull though. If you look at the execution plan, these both are the same:

select * from employee where employee.id = COALESCE(@.EmpID, employee.id)

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

This defeats the purpose of using IsNull for the better exectution with some indexes.

Handling Events in Report Builder

Is there any way that I can handle events using a custom library in the
Report Builder? For example, I have written my stored procs to specifically
handle the "All" case of multi-select parameters without the necessity of a
huge comma-separated list. I'd like to post-process the parameters before the
report is rendered.
Any suggestions?
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com"Event driven" is not possible in ReportBuilder. Will check from my side,
whether any API's can be used to handle events. But I dont think this is
possible.
Amarnath
"Helen Warn" wrote:
> Is there any way that I can handle events using a custom library in the
> Report Builder? For example, I have written my stored procs to specifically
> handle the "All" case of multi-select parameters without the necessity of a
> huge comma-separated list. I'd like to post-process the parameters before the
> report is rendered.
> Any suggestions?
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com

Handle TEXT FIELDS in Recordset Using ASP

hi there,

I have a problem when i tried to get data from mssql server using recordset in asp language. For example, i have table containing 5 fields. One of them is text field and the rest are varchar.

here i list the table structure (table name: Info):

Field Name Field Type Length

---

Name varchar 50

Gender varchar 50

Address text 16

Status varchar 50

Age varchar 50

---

Sample data inside table info.

Name Gender Address Status Age

---

Ali male MAL S 26

this is my code:

set rs = server.createobject ("adodb.recordset")

rs.open "select * from info", connectionstring

if not rs.eof then

name = rs("name")

gender = rs("gender")

address = rs("address")

status = rs("status")

age = rs("age")

end if

rs.close

what happend here, i managed to get name = Ali, gender = male but for address, status and age is nothing, just empty string.What was really happened, does anyone knows this problem.

If I change

rs.open "select * from info", connectionstring

to

rs.open "select * from info", connectionstring, 3

i managed to get all the info but i have to do a lot of code change for my program. Is there any solution to this problem?

i'm using win server 2003 and mssql server 2000

thanks for cooperation from you guys.

please accept my apologize for my bad language.

regards

kharulli

Firts you need to move all your BLOB-type fields to the end of the list of your fields in a selection list of the SELECT statement. It means your SELECT should look like

SELECT [Name], Gender , Status, Age, Address FROM ....

Now, after you query data, you need to get GetChunk method of the field to read the data in a case if it is longer than 255 characters. Otherwise you could use Value property

|||

thanks VMazur for quick replies,

I tried and its works. But it seems i must do a lot code conversion to overcome this problem. Its this MSSQL weaknesses or blob-type just work like this? Does MSSQL have a solution to this problem. Sorry to cause trouble to your guys in answering my post.

best regards

kharulli.

|||I believe this is how provider handles BLOB fields. They have to be at the end of your selection. Reason for this is, probably, memory allocation|||thanks for the info. it helped me a lot.

Handle error in t-sql

Hi,

I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).

If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?

Thanks

Yes, I believe that feature was added in SQL Express/2005, but I'm not familiar enough with it to give examples. Normally, I would not create a SQL query that would ERROR, but it might return an empty resultset, or other indicator that it failed. If I need to catch a true error, then catch it in a try/catch block in your code.