Analysis Server 2000 memory vs. SQL Server 2000 memory

Here is the situation:
Server is installed with both Analysis Services(OLAP
Cubes) and SQL Server 2000. O/S is Windows Advanced
Server 2000. Server currently has 6 GB of RAM. AWE
extension is used with 5 GB of RAM allocated to SQL
Server.
Does this mean only 1 GB of RAM is left for Analysis
Services and O/S? What is the max RAM can be allocated to
Analysis Services in a Windows 2000 Advanced Server?
Need to understand why at times (once a month) we
encounter "running low on virtual memory" on this server
causing DTS packages to stall occasionally. Realize that
best practice is to separate OLAP server from SQL Server,
but budget is a limitation at this time.Steven,
Analysis Services 32-bit edition uses up to 2GB memory. It can be exapanded
up to 3GB (check the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;295443 how to do
it). So in your case, AS and SQL are competing for the same memory. Maybe
you could limit SQL Server on 4GB, so you have 2GB for AS.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Steven Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:02e501c48af8$b4cc31e0$a601280a@.phx.gbl...
> Here is the situation:
> Server is installed with both Analysis Services(OLAP
> Cubes) and SQL Server 2000. O/S is Windows Advanced
> Server 2000. Server currently has 6 GB of RAM. AWE
> extension is used with 5 GB of RAM allocated to SQL
> Server.
> Does this mean only 1 GB of RAM is left for Analysis
> Services and O/S? What is the max RAM can be allocated to
> Analysis Services in a Windows 2000 Advanced Server?
> Need to understand why at times (once a month) we
> encounter "running low on virtual memory" on this server
> causing DTS packages to stall occasionally. Realize that
> best practice is to separate OLAP server from SQL Server,
> but budget is a limitation at this time.
>|||Hi Dejan:
Thanks for your response. Just want to clarify....
Is AS and SQL competing for the memory OR AS only has up
to 1 GB of RAM to use since AWE is enabled with 5 GB
allocated to SQL in this case?
How about the option to add 2 GB of RAM to server, and
allocate 6 GB to SQL and leave 2 GB to AS? What do you
think?
Thanks in advance.
>--Original Message--
>Steven,
>Analysis Services 32-bit edition uses up to 2GB memory.
It can be exapanded
>up to 3GB (check the article at
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;295443 how to do
>it). So in your case, AS and SQL are competing for the
same memory. Maybe
>you could limit SQL Server on 4GB, so you have 2GB for AS.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Steven Wong" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02e501c48af8$b4cc31e0$a601280a@.phx.gbl...
>> Here is the situation:
>> Server is installed with both Analysis Services(OLAP
>> Cubes) and SQL Server 2000. O/S is Windows Advanced
>> Server 2000. Server currently has 6 GB of RAM. AWE
>> extension is used with 5 GB of RAM allocated to SQL
>> Server.
>> Does this mean only 1 GB of RAM is left for Analysis
>> Services and O/S? What is the max RAM can be allocated
to
>> Analysis Services in a Windows 2000 Advanced Server?
>> Need to understand why at times (once a month) we
>> encounter "running low on virtual memory" on this server
>> causing DTS packages to stall occasionally. Realize
that
>> best practice is to separate OLAP server from SQL
Server,
>> but budget is a limitation at this time.
>
>.
>|||> Is AS and SQL competing for the memory OR AS only has up
> to 1 GB of RAM to use since AWE is enabled with 5 GB
> allocated to SQL in this case?
I think they are competing for the first 4 GB.
> How about the option to add 2 GB of RAM to server, and
> allocate 6 GB to SQL and leave 2 GB to AS? What do you
> think?
This would be even better than limiting SQL Server :-)
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.eduPete
First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
to see what will not be able to work on SQL Server 2005. Then install SQL
Server 2005 and just restore a database . Make sure that you have
compatibily level of the database =90 and try to coonect thjrou the
application to see how is going on
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
> The situation is this. I have a "production test" system, which we are
> about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> test fixes, code changes and the like, but it is effectively "pseudo
> production" so I have to be careful with it. In effect the user needs
> access to the current test data until the upgrade process is complete
> and accepted. Since this is my first major upgrade project I am a bit
> nervous. Now the issue --
> The current database is totally mis-sized, especially the Logfile which
> has been allowed to grow to well over 6G of which 3% to 5% is typically
> in use. So as a part of this process I would like to resize the target
> databases.
> My plan is to install a new instance of Sql2005 along-side the existing
> 2000 instance, on the same server and set of disks. But now I need to
> get a copy of the 2000 data over to it. If I back up and restore to an
> empty disk location w/ move, I get the existing structure. If I pre-
> create the physical structure and restore over it with a move, it will
> still overwrite the files and leave me with a copy of the old bad
> structure. I know I can go ahead with the copy with move, and then run
> the appropriate shrinks and resizing against the copy, but I'd like to
> do it in a single process in the interest of time and safety. I've
> considered the copy database wizard, but I'm not sure of all of the
> ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> of the afternoon researching this in BOL and in 3rd party books. But I
> would really appreciate any insights the group might offer and any
> advance warnings about "gotcha's" and "show-stoppers."
> Thanks in advance.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu|||In article <#MAeoOMsHHA.4676@.TK2MSFTNGP03.phx.gbl>, urid@.iscar.co.il
says...
> Pete
> First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
> to see what will not be able to work on SQL Server 2005. Then install SQL
> Server 2005 and just restore a database . Make sure that you have
> compatibily level of the database =90 and try to coonect thjrou the
> application to see how is going on
>
>
>
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
> > The situation is this. I have a "production test" system, which we are
> > about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> > test fixes, code changes and the like, but it is effectively "pseudo
> > production" so I have to be careful with it. In effect the user needs
> > access to the current test data until the upgrade process is complete
> > and accepted. Since this is my first major upgrade project I am a bit
> > nervous. Now the issue --
> >
> > The current database is totally mis-sized, especially the Logfile which
> > has been allowed to grow to well over 6G of which 3% to 5% is typically
> > in use. So as a part of this process I would like to resize the target
> > databases.
> >
> > My plan is to install a new instance of Sql2005 along-side the existing
> > 2000 instance, on the same server and set of disks. But now I need to
> > get a copy of the 2000 data over to it. If I back up and restore to an
> > empty disk location w/ move, I get the existing structure. If I pre-
> > create the physical structure and restore over it with a move, it will
> > still overwrite the files and leave me with a copy of the old bad
> > structure. I know I can go ahead with the copy with move, and then run
> > the appropriate shrinks and resizing against the copy, but I'd like to
> > do it in a single process in the interest of time and safety. I've
> > considered the copy database wizard, but I'm not sure of all of the
> > ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> > of the afternoon researching this in BOL and in 3rd party books. But I
> > would really appreciate any insights the group might offer and any
> > advance warnings about "gotcha's" and "show-stoppers."
> >
> > Thanks in advance.
> > --
> > Graham (Pete) Berry
> > PeteBerry@.Caltech.edu
>
>
Thanks Uri -- you are right on and that is already a part of the plan --
the open question remains about resizing and restoring.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edusorry for the duplicate post -- it took over 12 hours for the first one
to appear and I got impatient.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
>
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
sorry for the duplicate post -- it took over 12 hours for the first one
to appear and I got impatient.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
>
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Pete
First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
to see what will not be able to work on SQL Server 2005. Then install SQL
Server 2005 and just restore a database . Make sure that you have
compatibily level of the database =90 and try to coonect thjrou the
application to see how is going on
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.c om...
> The situation is this. I have a "production test" system, which we are
> about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> test fixes, code changes and the like, but it is effectively "pseudo
> production" so I have to be careful with it. In effect the user needs
> access to the current test data until the upgrade process is complete
> and accepted. Since this is my first major upgrade project I am a bit
> nervous. Now the issue --
> The current database is totally mis-sized, especially the Logfile which
> has been allowed to grow to well over 6G of which 3% to 5% is typically
> in use. So as a part of this process I would like to resize the target
> databases.
> My plan is to install a new instance of Sql2005 along-side the existing
> 2000 instance, on the same server and set of disks. But now I need to
> get a copy of the 2000 data over to it. If I back up and restore to an
> empty disk location w/ move, I get the existing structure. If I pre-
> create the physical structure and restore over it with a move, it will
> still overwrite the files and leave me with a copy of the old bad
> structure. I know I can go ahead with the copy with move, and then run
> the appropriate shrinks and resizing against the copy, but I'd like to
> do it in a single process in the interest of time and safety. I've
> considered the copy database wizard, but I'm not sure of all of the
> ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> of the afternoon researching this in BOL and in 3rd party books. But I
> would really appreciate any insights the group might offer and any
> advance warnings about "gotcha's" and "show-stoppers."
> Thanks in advance.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
|||In article <#MAeoOMsHHA.4676@.TK2MSFTNGP03.phx.gbl>, urid@.iscar.co.il
says...
> Pete
> First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
> to see what will not be able to work on SQL Server 2005. Then install SQL
> Server 2005 and just restore a database . Make sure that you have
> compatibily level of the database =90 and try to coonect thjrou the
> application to see how is going on
>
>
>
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.c om...
>
>
Thanks Uri -- you are right on and that is already a part of the plan --
the open question remains about resizing and restoring.
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.eduPete
First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
to see what will not be able to work on SQL Server 2005. Then install SQL
Server 2005 and just restore a database . Make sure that you have
compatibily level of the database =90 and try to coonect thjrou the
application to see how is going on
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
> The situation is this. I have a "production test" system, which we are
> about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> test fixes, code changes and the like, but it is effectively "pseudo
> production" so I have to be careful with it. In effect the user needs
> access to the current test data until the upgrade process is complete
> and accepted. Since this is my first major upgrade project I am a bit
> nervous. Now the issue --
> The current database is totally mis-sized, especially the Logfile which
> has been allowed to grow to well over 6G of which 3% to 5% is typically
> in use. So as a part of this process I would like to resize the target
> databases.
> My plan is to install a new instance of Sql2005 along-side the existing
> 2000 instance, on the same server and set of disks. But now I need to
> get a copy of the 2000 data over to it. If I back up and restore to an
> empty disk location w/ move, I get the existing structure. If I pre-
> create the physical structure and restore over it with a move, it will
> still overwrite the files and leave me with a copy of the old bad
> structure. I know I can go ahead with the copy with move, and then run
> the appropriate shrinks and resizing against the copy, but I'd like to
> do it in a single process in the interest of time and safety. I've
> considered the copy database wizard, but I'm not sure of all of the
> ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> of the afternoon researching this in BOL and in 3rd party books. But I
> would really appreciate any insights the group might offer and any
> advance warnings about "gotcha's" and "show-stoppers."
> Thanks in advance.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu|||In article <#MAeoOMsHHA.4676@.TK2MSFTNGP03.phx.gbl>, urid@.iscar.co.il
says...
> Pete
> First of all run Upgrade Adviser (could be downloaded) on SQL Server 200
0
> to see what will not be able to work on SQL Server 2005. Then install SQL
> Server 2005 and just restore a database . Make sure that you have
> compatibily level of the database =90 and try to coonect thjrou the
> application to see how is going on
>
>
>
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
>
>
Thanks Uri -- you are right on and that is already a part of the plan --
the open question remains about resizing and restoring.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edusorry for the duplicate post -- it took over 12 hours for the first one
to appear and I got impatient.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
>
Graham (Pete) Berry
PeteBerry@.Caltech.edu

amount manipulation in crystal report

I am developing a project in vb.net .
I am displaying the amount in my detail section.
There is a situation in which , if the amount is negative it has to be displayed as receipts and if else it has to be showed as payments .

like

Description Payments Receipts
monthly instalment 6000.23 0.00
finance charges 0.00 6000.23(this is -ve value)

I have struck here . Can any one help me to come out of this situation.

I am attaching two jpeg files in which

1 vbcode_rpt : will show the present look of the report.

2 PBcode_rpt : will show how i want the report to be presented.

thanks in advance

vijiPlz explain one thing to me that do have single Field in the database for Payments and Receipts or individual Field.

in ur case u can apply formula as (if its single Field)

if {Reportname.Field}<0 then
Payments
elseif {Reportname.Field} then
Receipts|||hai silly star

i got the solution for that situation.

Both the columns are one column in the database ie. the amount column for that description.

if the amount column value is < 0 then it has to be displayed as receipts and if it is > 0 then it has to be displayed as payments .

i added two unbound currency columns in the details section and in the formula dialog I added this formula to get my requirement done.

This is in the unbound_currency_column1 ie. Payments
if {tmp_ter_pool.Amount} > 0
then {tmp_ter_pool.Amount}
else
0.00

This is in the unbound_currency_column2 ie. Receipts
if {tmp_ter_pool.Amount} < 0
then {tmp_ter_pool.Amount}
else
0.00|||Its great u find the solution but did u try the one i told u?? :cool:

Am having script Problems with Duplicates and Inserts

Ok, here is the situation, I have a view in one database and I want to insert all the data into a table on the same server but in a different database. With a no duplicate insert, cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

So I first wrote a script that looked for duplicates, this worked.

FROM Coffee.dbo.vueProductCase a
JOIN (SELECT ProductCode, COUNT(*) AS cnt
FROM coffee.dbo.vueProductCase
GROUP BY ProductCode
HAVING COUNT(*) > 1) b
ON a.ProductCode = b.ProductCode

It displayed a list of Duplicates, so I then tried to enter this script which doesnt seem to work at all, but it could be that it is because I dont know how to combine the scripts to insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

insert dbo.tblInItem
(ItemId,Descr,ProductLine,SalesCat,UomBase,UomDflt )
select
t1.ProductCode,
t1.[Description],
t1.'COFFEE',
t1.'CS',
t1.WeightMeasurement,
t1.'EACH'
from COFFEE.dbo.vueProductCase t1 left join dbo.tblInItem t2 on t1.ProductCode = t2.itemid
where t2.itemid is null

Can I get some help please??cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

Doesn't make sense.

insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

Nope...this one doesn't make sense either.

Give us a sample table create statement with insert statements for the data. Then, show us what you want the data to look like when complete. We should be able to help you pretty quickly then. Right now, someone else might be able to help you if they understand you. I'm not getting it though. [:)]|||My question is "What error message or incorrect results are you getting".

Because I don't see anything syntactically wrong with your insert statement, and "It don't work fer nuffin at all" doesn't give us a lot of clues...

Alternatives to SQLMail

Hi,

We encountered a situation a few days where our SQLMail stopped working. We are using SQL Server 2000, SP4 and an Exchange Server. Prior to the SQLMail stopping we had experienced problems with our Exchange Server for a period of time.

Researching on the Internet, I've found several explanations that all read:

"The most common cause of this problem is a temporary loss of the network connection to the e-mail server. This problem most often occurs when SQL Mail is configured to use Internet protocols for message delivery, although it can also occur when communicating with a Microsoft Exchange host.

When SQL Mail loses a connection to the e-mail server, the Extended MAPI components display a message that requests user action as to whether or not to continue. This message does not appear on the server because SQL Mail is run from the SQL Server service. Because the message does not appear, there is no way to respond to the message to allow SQL Mail to continue. Even if the network connection becomes available, SQL Mail does not retry the connection.

To work around the problem, you must stop and restart SQL Server."

I believe this is the situation that we encountered. However stopping and restarting SQL Server did not solve the problem. We had to reboot the machine before the SQL Mail started working again. This is a production machine that we don't just want to "reboot at will", so I'm a little concerned about this issue.

My question is - are there any alternatives to using SQLMail? I have stored procedures that call xp_sendmail (and then xp_stopmail) to send email notifications in certain instances. Is there something else that I can/should use in its place? I found a reference that stated "To resolve the problem, update your applications to use Database Mail.", but this was on a SQL Server 2005 page, and apparently Database Mail is not part of SQL Server 2000.

Thanks,
Beth

YES!

The preferred alternative is xp_smtpmail. You 'should' NOT be using MAPI mail with SQL 2000 -for several reasons

Read about it and download from:

Email for SQL Server 2000
http://www.sqldev.net/xp/xpsmtp.htm

|||Is there any article/link that explains why "You 'should' NOT be using MAPI mail with SQL 2000"?

Thanks for the info on xp_smtpmail - I'll take a look at it.
Beth

|||

This first link is from one of the SQL Dev Team members.

http://sqljunkies.com/WebLog/euang/archive/2004/03/16/1617.aspx

http://www.windowsitpro.com/Articles/ArticleID/14116/14116.html?Ad=1

http://www.developersdex.com/sql/message.asp?p=1925&ID=%3C943EC1D1-1E2A-413A-8DF5-A5F919125DD9%40microsoft.com%3E

Top Causes of SQL Server Downtime (Item #2)
http://www.sqlmag.com/Article/ArticleID/40011/sql_server_40011.html

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3bQ315886

Google? for: sql and server and mapi and problems. You'll probably notice a couple hundred thousand hits...

'nuf said.