MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
Maury
Maury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury
|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury
|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2...lesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
MauryMaury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury|||"Tracy McKibben" wrote:
> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>> Look up COALESCE in Books Online...
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury|||Maury Markowitz wrote:
> INTP56" wrote:
>> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
>> ISNULL(salesq4,0)
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because it
s
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null
":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
MauryMaury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||COALESE will only return the first non-null value. Think of it as a shorthan
d
way of doing a CASE statement that checks each value in order for a NULL, an
d
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to rol
l
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand f
or
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/...realsqlguy.com|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

min or max over multiple columns

Hi all,
A short question about min and max functions. Let's say I have the following
table:
contract sales_2005 sales_2006
1234 100 120
5678 870 560
Now, I want to build a query that gives the following result:
contract maximum_sales
1234 120
5678 870
So I want to get the maximum yearly sales per contract over the years 2005
and 2006. A normal max-function won't work because it only calculates the
maximum row in a given column. Is there a function that does what I'm lookin
g
for? Also to get the minimum yearly sales per contract.
Of course, it can be solved with a greater than/less than construction but
I'm looking for a more efficient solution.
Thanks in advance!
regards,
MaartenWhy is your data not properly normalized?
You can normalize it for the purpose of the aggregation by using a UNION
query (untested, since you haven't provided proper DDL):
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
...then use this as a derived table and apply the aggregation:
select max(Normalized.Sales) as MaxSales
,min(Normalized.Sales) as MinSales
from (
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
) Normalized
Is this what you need?
ML
http://milambda.blogspot.com/|||I would use a greater than/less than for this.
Something like (you would have to add Coalesce or IsNull function calls if
either sales_2005 or sales_2006 can be NULL):
Select contract,
Max(Case When sales_2005 > sales_2006 Then sales_2005 Else sales_2006 End)
As maximum_sales
From ...
Group By contract
Order By contract
If you absolutely want to avoid greater than/less than, you could do:
Select contract,
Max(sales) As maximum_sales
From (Select contract, sales_2005 As sales From ...
Union All Select contract, sales_2006 As sales From ...) x
Group By contract
Order By contract
but that would probably be less efficient than the first query.
Tom
"maarten_dtg" <u23468@.uwe> wrote in message news:625ad6dff69a4@.uwe...
> Hi all,
> A short question about min and max functions. Let's say I have the
> following
> table:
> contract sales_2005 sales_2006
> 1234 100 120
> 5678 870 560
> Now, I want to build a query that gives the following result:
> contract maximum_sales
> 1234 120
> 5678 870
> So I want to get the maximum yearly sales per contract over the years 2005
> and 2006. A normal max-function won't work because it only calculates the
> maximum row in a given column. Is there a function that does what I'm
> looking
> for? Also to get the minimum yearly sales per contract.
> Of course, it can be solved with a greater than/less than construction but
> I'm looking for a more efficient solution.
> Thanks in advance!
> regards,
> Maarten|||See the execution plan for the three "select" statements as a batch.
create table dbo.t1 (
contract int not null primary key,
sales_2005 money not null,
sales_2006 money not null
)
go
insert into dbo.t1
select
*
from
(
select 1234 as contract, 100 as sales_2005, 120 as sales_2006
union all
select 5678, 870, 560
) as t2
go
select
contract,
case when sales_2005 > sales_2006 then sales_2005 else sales_2006 end as
max_sales
from
dbo.t1
select
contract,
max(sales) as max_sales
from
(
select contract, sales_2005 as sales from dbo.t1
union all
select contract, sales_2006 as sales from dbo.t1
) as t2
group by
contract
select
dbo.t1.contract,
max(case when t2.c1 = 1 then dbo.t1.sales_2005 else dbo.t1.sales_2006 end)
as max_sales
from
dbo.t1
cross join
(
select 1 as c1 union all select 2
) as t2
group by
dbo.t1.contract
go
drop table dbo.t1
go
AMB
"maarten_dtg" wrote:

> Hi all,
> A short question about min and max functions. Let's say I have the followi
ng
> table:
> contract sales_2005 sales_2006
> 1234 100 120
> 5678 870 560
> Now, I want to build a query that gives the following result:
> contract maximum_sales
> 1234 120
> 5678 870
> So I want to get the maximum yearly sales per contract over the years 2005
> and 2006. A normal max-function won't work because it only calculates the
> maximum row in a given column. Is there a function that does what I'm look
ing
> for? Also to get the minimum yearly sales per contract.
> Of course, it can be solved with a greater than/less than construction but
> I'm looking for a more efficient solution.
> Thanks in advance!
> regards,
> Maarten
>|||This is a cross tabs and not a table. You have split an attribute,
time, over two columns.
In Oracle, you would use GREATEST (sales_2005, sales_2006), but we have
to do more work:
SELECT X,contract_nbr, MAX(X.sales_annual)
FROM (SELECT S1.contract_nbr, S1.sales_2005
FROM SalesCrossTab AS S1
UNION ALL
SELECT S2.contract_nbr, S2.sales_2006
FROM SalesCrossTab AS S2)
AS X(contract_nbr, sales_annual)
GROUP BY X,contract_nbr;

Min except for zero

We have a complex query with many aggregate functions.
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:

>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim

Migration from SQL 2003 to 2005

I have an SQL database that uses standard tables and stored procedures
- no triggers and functions. I have a Microsoft Excel based front-end
which uses ADO to access the DB to (1) run stored procedures (some SPs
return recordsets and some do not), (2) insert data into tables (3)
edit table data using simple linked grid based forms.
This DB was initially created in SQL 2000 and then installed on a SQL
2003 server - absolutely no issues. Now, I have to upgrade to SQL
2005. I am not concerned about DB Admin issues but only my procedures
should not stop working or ADO calls not working (note that I am NOT
using the .NET platform).
It is probably difficult to give any sure answer with this limited
information but I am mainly looking for standard issues that most SQL
2003 databases will face on upgrading to 2005.
Thanks,
Jay
P.S. I am concerned about things like...SQL 2005 has changed the
syntax for cursors; it does not support regular ADO and you must use
ADO.NET; data type Decimal is not available any more and you must use
Numeric; Basically, things like these that will require me to change
my table structures or procedures.
Jay
Download Upgrade Advisor tool from MS site and run it against SS2000
database, see report file.
"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
>

Migration from SQL 2003 to 2005

I have an SQL database that uses standard tables and stored procedures
- no triggers and functions. I have a Microsoft Excel based front-end
which uses ADO to access the DB to (1) run stored procedures (some SPs
return recordsets and some do not), (2) insert data into tables (3)
edit table data using simple linked grid based forms.
This DB was initially created in SQL 2000 and then installed on a SQL
2003 server - absolutely no issues. Now, I have to upgrade to SQL
2005. I am not concerned about DB Admin issues but only my procedures
should not stop working or ADO calls not working (note that I am NOT
using the .NET platform).
It is probably difficult to give any sure answer with this limited
information but I am mainly looking for standard issues that most SQL
2003 databases will face on upgrading to 2005.
Thanks,
Jay
P.S. I am concerned about things like...SQL 2005 has changed the
syntax for cursors; it does not support regular ADO and you must use
ADO.NET; data type Decimal is not available any more and you must use
Numeric; Basically, things like these that will require me to change
my table structures or procedures.Jay
Download Upgrade Advisor tool from MS site and run it against SS2000
database, see report file.
"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
>|||"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
Hi Jay
There was not a version SQL 2003, so I assume this is Windows 2003 and the
SQL Server did not change?
To help you prepare the upgrade to SQL 2005 look at the upgrade advisor to
see what you may need to change when upgrading the database
http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e0...
and the upgrade handbook is worth checking out for the additional steps you
will need to do such as rebuilding indexes, statistics and updating usage
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx.
John

Migrating to SQL Server 2005;

Hi all,

I have a Database in SQL Server 2000 with several objects (tables, views , stored procedures , functions ).

Is there any specific procedure or tool by which I could migrate the entire database to SQL Server 2005.

Any help would be appreciated.

Thanks,

Hari Haran ArulmozhiAs far as I have seen, backup/restore works as an upgrade path.