anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
|||it works! thanks
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it i
s
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it i
s
Null in all 3 tables I will just use 0 for the value. So use maxi from tabl
e
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day fr
om
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if n
ot
> null, then if null use value from table c for maxi.
> tableA
> ****************************************
**
> pri key * color_id * size_id * date* maxi *
> ****************************************
**
> * 1 * 2 * 5 * 2/1/07* 7 *
> ****************************************
**
> * 2 * 4 * 6 * 2/2/07* NULL *
> ****************************************
**
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

Any way to put a query timeout on the datasource behind a report model?

Folks, we're wanting to put a timeout of 2 minutes on the queries which get generated by an ad-hoc report built in Report Builder. This particular report model has been generated on top of a cube by clicking the Generate Model button after creating an Analysis Services datasource in Report Manager. The connection string for that datasource reads:

Data Source=(local);Initial Catalog=MyCubeDatabase

I expected there to be a connection string property I could set (such as "Timeout=120") but I can't seem to find that in the documentation anywhere... And Timeout=120 doesn't appear to do it. Do you know of one? If not, do you know of another way to put a timeout limit on a Report Builder generated query or a Report Builder report itself?

I tried setting the "Limit report execution to the following number of seconds" at /Reports/Pages/Settings.aspx in Report Manager to 120, but that does not appear to have an effect on Report Builder reports.

See the following bug I reported for more details:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=232694

This is quite an urgent issue for us as we go live in a week. Any help would be appreciated!

Time out is this a joke , this tool can't even get the rigth result's .... Maybe in version 11B pacth 12334 you have the time out feature

Any load testing tool to test queries

Guys,

Is there any tool that can test the scalability of Analysis Services?

Basically we want to fire multiple queries to the Analysis Services and check if there is any performance degradation.

Is there any utility or tool that I can use?

Rgds

Hari

SQL Performance monitor works with SSAS too.|||

The Community samples site has an ASLoadSim tool listed which might be worth a look http://www.codeplex.com/SQLSrvAnalysisSrvcs

any good books on SQL queries?

can anyone recommend a good book to teach myself SQL queries? I have a basic understanding but want to improve

You might start out with Bob Beauchemin's excellent book: A Developer's Guide to SQL Server 2005.

Eventually, you will graduate to Itzik Ben-Gan's (and others) two excellent books: T-SQL Programming and T-SQL Querying.

There are several other excellent books, those come to the top of my list.

|||

Mandy1,

I suggest to add this book to Arnie's list.

Pro SQL Server 2005 Database Design and Optimization

http://www.amazon.com/Server-2005-Database-Design-Optimization/dp/1590595297/ref=sr_1_1/105-0322821-1919616?ie=UTF8&s=books&qid=1180705710&sr=1-1

AMB

|||

Ken Henderson is also worth to read...

The Guru's Guide to Transact-SQL

ISBN 0-201-61576-2

regards

Andreas

any good books on SQL queries?

can anyone recommend a good book to teach myself SQL queries? I have a basic understanding but want to improve

You might start out with Bob Beauchemin's excellent book: A Developer's Guide to SQL Server 2005.

Eventually, you will graduate to Itzik Ben-Gan's (and others) two excellent books: T-SQL Programming and T-SQL Querying.

There are several other excellent books, those come to the top of my list.

|||

Mandy1,

I suggest to add this book to Arnie's list.

Pro SQL Server 2005 Database Design and Optimization

http://www.amazon.com/Server-2005-Database-Design-Optimization/dp/1590595297/ref=sr_1_1/105-0322821-1919616?ie=UTF8&s=books&qid=1180705710&sr=1-1

AMB

|||

Ken Henderson is also worth to read...

The Guru's Guide to Transact-SQL

ISBN 0-201-61576-2

regards

Andreas

Any functions to replace NZ in SQL Server?

I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
Is there anything equivalent to this in SQL Server? Right now I'm using
CASE WHEN ... but it seems like an awful lot of script to write just to
replace null with a zero.

Any help would be greatly appreciated.

Thanks!use coalesce or isnull

declare @.v int
select coalesce(@.v,0),isnull(@.v,0)

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>I'm moving some queries out of an Access front end and creating views out of
>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
>Is there anything equivalent to this in SQL Server? Right now I'm using
>CASE WHEN ... but it seems like an awful lot of script to write just to
>replace null with a zero.
>Any help would be greatly appreciated.
>Thanks!

Hi Rico,

Use COALESCE:

COALESCE (arg1, arg2, arg3, arg4, ...)

returns the first non-NULL of the supplied arguments. You need at least
two arguments, but you can add as many as you like.

--
Hugo Kornelis, SQL Server MVP|||Thanks Guys,

I wound up finding ISNULL before I had a chance to post back. (why do I
always find the solution right after I post).

Is there an argument for using Coalesce over IsNull?

Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:j7sf42hg4b78p8u1v5nj283av4kovqivur@.4ax.com...
> On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>I'm moving some queries out of an Access front end and creating views out
>>of
>>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is
>>null.
>>Is there anything equivalent to this in SQL Server? Right now I'm using
>>CASE WHEN ... but it seems like an awful lot of script to write just to
>>replace null with a zero.
>>
>>Any help would be greatly appreciated.
>>
>>Thanks!
>>
> Hi Rico,
> Use COALESCE:
> COALESCE (arg1, arg2, arg3, arg4, ...)
> returns the first non-NULL of the supplied arguments. You need at least
> two arguments, but you can add as many as you like.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>Thanks Guys,
>I wound up finding ISNULL before I had a chance to post back. (why do I
>always find the solution right after I post).
>Is there an argument for using Coalesce over IsNull?

Hi Rico,

Three!

1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
on SQL Server.

2. COALESCE takes more than two arguments. If you have to find the first
non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
COALESCE.

3. Data conversion weirdness. The datatype of a COALESCE is the datatype
with highest precedence of all datatypes used in the COALESCE (same as
with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
the same as the first argument. This is extremely non-standard and can
cause very nasty and hard-to-track-down bugs.

--
Hugo Kornelis, SQL Server MVP|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
> Is there an argument for using Coalesce over IsNull?

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Null is not zero. Null is not a zero length string.

I believe that nulls were not designed to be placeholders for these
values.
We should be extremely careful when we convert nulls to values. Such
conversion could lead to error. Often it is persons without strong
grounding in mathematics and logic who make these conversions,
increasing the likelihood of such error. The best practice is likely to
be the exclusion of records with nulls in the columns we are processing
and to enter values in those where a value is appropriate. There may be
some cases where it's a good idea to substitute a zls for a null value,
but none comes to my mind at this time.

IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
corresponding Coalesce function.

[Yes, I've probably posted IsNull(Field,Value) solutions here; that was
then; this is now.]|||Excellent! Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vttf42107btt07jbk21cvb6953kediiarp@.4ax.com...
> On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>Thanks Guys,
>>
>>I wound up finding ISNULL before I had a chance to post back. (why do I
>>always find the solution right after I post).
>>
>>Is there an argument for using Coalesce over IsNull?
> Hi Rico,
> Three!
> 1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
> on SQL Server.
> 2. COALESCE takes more than two arguments. If you have to find the first
> non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
> COALESCE.
> 3. Data conversion weirdness. The datatype of a COALESCE is the datatype
> with highest precedence of all datatypes used in the COALESCE (same as
> with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
> the same as the first argument. This is extremely non-standard and can
> cause very nasty and hard-to-track-down bugs.
> --
> Hugo Kornelis, SQL Server MVP|||Read about IsNull Vs Coalesce
http://www.sqlservercentral.com/col...tweenisnull.asp

Madhivanan|||On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote:

>Null is not zero. Null is not a zero length string.
>I believe that nulls were not designed to be placeholders for these
>values.
(snip)

Hi Lyle,

Thus far, I agree with yoour post.

(snip)
> There may be
>some cases where it's a good idea to substitute a zls for a null value,
>but none comes to my mind at this time.

First, you should be awarer that COALESCE and ISNULL on SQL Server, or
Nz on Access, can not just be used to replace NULL with 0 or zero length
string - you can replace them with anything you like. Common uses are
COALESCE (SomeColumn, 'n/a') in a report. Or
COALESCE (UserSpecifiedColumn, DefaultValue) in any query or view.

>IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
>corresponding Coalesce function.

I disagree with this statement. As I've shown above, COALESCE and ISNULL
can be used in very useful ways. That they might also be abused by
people who fail to think their solutions through is sad, but no reason
to abolish them. That's like forbidding cars because someone might cause
an accident while drinking and driving.

Besides, since COALESCE is just a shorthand for a specific CASE
expression, removing COALESCE from the language would have no effect;
people would just use the equivalent CASE expression.

--
Hugo Kornelis, SQL Server MVP|||Lyle Fairfield wrote:
> We should be extremely careful when we convert nulls to values. Such
> conversion could lead to error. Often it is persons without strong
> grounding in mathematics and logic who make these conversions,
> increasing the likelihood of such error.

You think so? Nulls as formulated in SQL totally defy any standard
mathematics or logic. Any system that permits the predicate (x=x) to
evaluate to anything other than true isn't likely to win many votes
from persons with a strong grounding in mathematics. It is precisely
because nulls are so counter-intuitive that they lead to so many
mistakes in SQL. However, I do agree with your basic point that if you
regularly need to convert nulls like this it may indicate weakness in
your design or requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I probably shouldn't open my mouth in the presence of some posters, but with
regard to converting nulls being bad design; I have a bunch of reports that
show loans and payments (just to make things simple). If I have no payment
record (a null) then I have zero payments applied to the loan. By
converting these null payment records to zero payments, is this considered
in theory bad design? Or is this an exception to that rule. Is there a
definition between what would be considered bad design and what is
considered an exception?

Not trying to raise a debate really, just asking for clarification.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145653630.430902.73680@.z34g2000cwc.googlegro ups.com...
> Lyle Fairfield wrote:
>> We should be extremely careful when we convert nulls to values. Such
>> conversion could lead to error. Often it is persons without strong
>> grounding in mathematics and logic who make these conversions,
>> increasing the likelihood of such error.
> You think so? Nulls as formulated in SQL totally defy any standard
> mathematics or logic. Any system that permits the predicate (x=x) to
> evaluate to anything other than true isn't likely to win many votes
> from persons with a strong grounding in mathematics. It is precisely
> because nulls are so counter-intuitive that they lead to so many
> mistakes in SQL. However, I do agree with your basic point that if you
> regularly need to convert nulls like this it may indicate weakness in
> your design or requirements.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||Rico wrote:
> I probably shouldn't open my mouth in the presence of some posters, but with
> regard to converting nulls being bad design; I have a bunch of reports that
> show loans and payments (just to make things simple). If I have no payment
> record (a null) then I have zero payments applied to the loan. By
> converting these null payment records to zero payments, is this considered
> in theory bad design? Or is this an exception to that rule. Is there a
> definition between what would be considered bad design and what is
> considered an exception?
> Not trying to raise a debate really, just asking for clarification.

If you have no payment record then why do you have a null?

Nulls are a source of complexity and error. On the other hand, avoiding
them can lead to complexity of a different kind - often requiring the
creation of additional tables for example. Whether to use nulls at all
is a controversial topic about which a huge amount has been written and
argued over. In practice, SQL database systems tend to make it very
hard to avoid them altogether.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Rico (me@.you.com) writes:
> I probably shouldn't open my mouth in the presence of some posters, but
> with regard to converting nulls being bad design; I have a bunch of
> reports that show loans and payments (just to make things simple). If I
> have no payment record (a null) then I have zero payments applied to the
> loan. By converting these null payment records to zero payments, is
> this considered in theory bad design? Or is this an exception to that
> rule. Is there a definition between what would be considered bad design
> and what is considered an exception?

In practice there are many cases where NULL and 0 or the empty string
are more or less the same thing.

Of course, if we have a table:

CREATE TABLE loans (loanno char(11) NOT NULL,
...
no_of_payments int NULL,
...

A NULL in no_of_payments taken to the letter would mean "we don't
know how many payments that has not been done on this loan, if any
at all" or "this is a loan on which you do not make payments at all,
so it is not applicable".

But I don't believe for a second that this is how your table design looks
like. And with a more complex design, there could easily appear a NULL in
a query.

There are many cases were isnull or coalesce comes in handy. For some
computations, equating NULL with 0 makes sense. But coalesce can
also be used to get a value from multiple places. Assume, for instance,
that a customer can have a fixed discount, or he can be part of a
group that can have a common rebate. Assuming that an individual
discount overrides the group discount, that would be:

coalesce(Customers.discount, Groups.discount, 0)

The 0 at the end is really needed here, if we assume that a customer
may not belong to any group. That is, the Groups table comes in with
a left join, so it does not help if Groups.discount is not nullable.
And Customers.discount needs to be NULL, so we can have some logic
to get the group instead. It would not be good to have 0 to mean
"use group instead", because we may actually want to deprive the
customer of the group rebate.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||FROM BOL:
A value of NULL indicates that the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.

Null values generally indicate data that is unknown, not applicable, or
that the data will be added later. For example, a customer's middle
initial may not be known at the time the customer places an order.

Following is information about nulls:

To test for null values in a query, use IS NULL or IS NOT NULL in the
WHERE clause.

When query results are viewed in SQL Server Management Studio Code
editor, null values are shown as (null) in the result set.

Null values can be inserted into a column by explicitly stating NULL in
an INSERT or UPDATE statement, by leaving a column out of an INSERT
statement, or when adding a new column to an existing table by using
the ALTER TABLE statement.

Null values cannot be used for information that is required to
distinguish one row in a table from another row in a table, for
example, foreign or primary keys.

In program code, you can check for null values so that certain
calculations are performed only on rows with valid, or not NULL, data.
For example, a report can print the social security column only if
there is data that is not NULL in the column. Removing null values when
you are performing calculations can be important, because certain
calculations, such as an average, can be inaccurate if NULL columns are
included.

If it is likely that null values are stored in your data and you do not
want null values appearing in your data, you should create queries and
data-modification statements that either remove NULLs or transform them
into some other value.

Important:
To minimize maintenance and possible effects on existing queries or
reports, you should minimize the use of null values. Plan your queries
and data-modification statements so that null values have minimal
effect.

When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors. These tables outline the effect of introducing null
comparisons.

---
I think that null should not be referred to as a value, in the same way
that celibacy should not be referred to as sex.

In addition, the statements:
"A value of NULL is different from an empty or zero value."
and
"you should create queries and data-modification statements that
either ... or transform them into some other value."
conflict.|||This is just crap!|||Lyle Fairfield (lylefairfield@.aim.com) writes:
> This is just crap!

At least that was a concise comment.

Nevertheless, exactly what you think is crap? How would you model
discounts that can be applied on several levels?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any code for automatically inserting queries of first table into the second?

Any code for automatically inserting queries of first table into the second?
Is there a way of inserting the query of records from the first table into the second? Because I want to use the second db by deleting records that might be unnecessary.

I want to copy the searches of one table to another. Let's say I search a keyword I want the search to go through the first table and copy the records where the keyword is to be found to the second table. I find that would be easy to study. Whatever I don't want from that search I can easily delete it. And at the same time I have the original table not tampered.could u just

INSERT INTO Table2 (field1)
SELECT * FROM Table1;

i believe u can just use the INSERT cmd|||Is it before or after (I'd guess it's before) the <%rs.Open SQL%> statement?|||What does this mean?

INSERT INTO results(id, book, book_spoke, recordType, book_title, chap, chapter, chapter_spoke, vers, verse, verse_spoke, text_data) VALUES (, , , '', '', , , , , , , '')
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'execute'

/kjvresp.asp, line 280|||the problem is here: VALUES (, , , '', '', , , , , , , '')

you have to put something for each column

at worst, you could use nulls (assuming the table allows nulls)

VALUES (null, null, null, '', '', null, null, null, null, null, null, '')|||I had put && around each fieldname. But I made some changes. But this is what I got now:

INSERT INTO results SELECT * FROM bible WHERE text_data LIKE '%ezra%' AND text_data LIKE '%%' AND text_data LIKE '%%'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

/kjvresp.asp, line 272

The insertion was found in the table but the output to the ASP page is somehow blocked.|||text_data LIKE '%%' is true for every row and should be dropped

if you have an ASP problem, you should post in the ASP forum

this forum is for generic (non-proprietary) SQL

Ansi Nulls

I am get the following erro message when accessing the linked server table.
Any Suggetions ??

"HETROGENOUS QUERIES REQUIRE THE ANSI NULLS ON AND ASI WARNINGS TO BE SET FOR THE CONNETION. THIS ENSURES CONSISTENT QUERY SEMANTICS. ENABLE THESE OPTIONS AND THEN USE THE YOUR QUERY"

If Enable what go will go wrong because these are production servers..

Rajessentially you need to issue two statments:

set ANSI_NULLS on
set ANSI_WARNINGS on

I doubt setting either of these to on will give you any problems with the possability of NULL comparisons, see below.

as for what will go wrong, from Books Online:

ANSI_NULLS

When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULLS.

SET ANSI_NULLS also must be set to ON when you create or manipulate indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

and

ANSI_WARNINGS

When set to ON, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF.

SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.

The status of this option can be determined by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.