OutOfMemoryException with SQL Server Mobile

Hi,

I'm getting always an OutOfMemoryException after my program runs for a while. It's a smartphone application running on .NET 2.0 CF and using the SQL Server Mobile 2005. Sometimes it's even not a OutOfMemoryException but a SqlCeException. But it's always when the phone is having little free memory left.

I have also created a few custom controls (I even implemented the Disposable interface) to dispose all resources (Fonts, Bitmaps). I'm using all Pens and Brushes always in "using" blocks, to have them disposed immediately after having used them. The program does not have any static objects or lists of objects that are hold in memory for a long time.

I really don't understand why I get this exception. I see it always happen after doing a lot queries on the SQL Server Mobile. Is this tool using a lot memory? Can I flush somehow the memory of the SQL Server? Should I run also manually sometimes the GarbageCollector? Does this thing not run automatically?

Can anybody, perhaps of MSFT help me? I'm also willing to share the full source code with MSFT, if required.

You will probably get a faster answer on the Sql Server Mobile forum.

Moving this thread to that forum.

|||

Here are a few suggestions (from an earlier thread)

1) Try to use SqlCeResultSet than DataSet

2) Please dispose the managed objects explicitly instead of waiting for garbage collector to pick them up. Please also note that SqlCeCommand object can be reused and they need to be disposed explicitly

In addtion you can email me your project and I'll be glad to look into this issue for you.

Mark dot Ihimoyan at microsoft dot com

Outloook 2002 & SQL Server 2000

Hi ,
I am running SQL server 2000 sp3a and MS Outlook 2002. I have created the
profile and tested it within mail from the ontrol panel which i can received
the email.
From SQL Server , i assigned the profile "Test" to SQL Mail & SQL Mail Agent
and click on the test which said "Successfully started(stopped) a mail
session with this profile".
Then i went ahead to create the operator and put in a valid email addr and i
click on the "test" which gave me a return message that said "the message wa
s
successfully sent" but somehow i did not recieve that email
How can i fix this ?
appreciate ur advice
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1Hi ,
to add on further i used Query Analyzer and i got the "mail sent" using
xp_sendmail @.recipient = 'eee' , @.subject = 'oooo'
tks & rdgs
maxzsim wrote:
>Hi ,
> I am running SQL server 2000 sp3a and MS Outlook 2002. I have created the
>profile and tested it within mail from the ontrol panel which i can receive
d
>the email.
>From SQL Server , i assigned the profile "Test" to SQL Mail & SQL Mail Agen
t
>and click on the test which said "Successfully started(stopped) a mail
>session with this profile".
>Then i went ahead to create the operator and put in a valid email addr and
i
>click on the "test" which gave me a return message that said "the message w
as
>successfully sent" but somehow i did not recieve that email
>How can i fix this ?
>appreciate ur advice
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
Upon further checking i see that all those mails were "stucked" in the MS
Outlook.
I'll have to manually click on the "send/receive" to sent out those emails .
furthermore , i can only send to those company email addr and not the
hotmail/gmail address.
i have set up the SMTP server but not the POP3 server i do not intend to
receive any incoming email
How can i prevent the emails from being saved to MS Outlook and i don't have
to manually click on the "send/receive" and how can i send to all valid emai
l
addresses ?
appreciate ur advice
tks & rdgs
maxzsim wrote:
>Hi ,
> to add on further i used Query Analyzer and i got the "mail sent" using
>xp_sendmail @.recipient = 'eee' , @.subject = 'oooo'
>tks & rdgs
>
>
>[quoted text clipped - 15 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||I am pretty sure you cannot because that is the latest outlook security
feature to prevent software sending out junk emails. Software could be
downloaded and take over your email service to send out emails without you
knowing it.
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5c2572e6256ac@.uwe...
> Hi ,
> Upon further checking i see that all those mails were "stucked" in the MS
> Outlook.
> I'll have to manually click on the "send/receive" to sent out those emails
> .
> furthermore , i can only send to those company email addr and not the
> hotmail/gmail address.
> i have set up the SMTP server but not the POP3 server i do not intend to
> receive any incoming email
> How can i prevent the emails from being saved to MS Outlook and i don't
> have
> to manually click on the "send/receive" and how can i send to all valid
> email
> addresses ?
> appreciate ur advice
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
I want to configure something with SQL Mail & SQL Agent Mail to auto send
out alert email so what software could help me acheive this ?
tks & rdgs
Grant wrote:
>I am pretty sure you cannot because that is the latest outlook security
>feature to prevent software sending out junk emails. Software could be
>downloaded and take over your email service to send out emails without you
>knowing it.
>
>[quoted text clipped - 29 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
Actually i am as to why i ccan only send to company123@..xxx.sss
but not xxx@.hotmail/gmail.com as i thought if you could send via SMTP then i
t
shld send as long as its a valid email addr ?
i got this error message " 554 Relay rejected for policy reasons."
appreciate ur advice
tks & rdgs
Grant wrote:
>I am pretty sure you cannot because that is the latest outlook security
>feature to prevent software sending out junk emails. Software could be
>downloaded and take over your email service to send out emails without you
>knowing it.
>
>[quoted text clipped - 29 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1

Outlook needs to be open for SMTP.

sql2k sp3a
Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
for SQL Alerts. I can get everything running with no problems. I set up an
operator and can test it 10 times in a row with success... while Outlook is
opened. But if I close Outlook, I will be able to test with success no more
than 2 times. (Though it does say sent successfull.) I will hit the test
button and the email will never arrive.
I will then log onto the box and attempt to open Outlook and it won't allow
me too. It complains about the Outlook.pst file being in use. The only way
to free this file up is to restart the SQL Agent. Only then does my test
email possibly arrive.
Has anyone else experienced this? Fix?
TIA, ChrisR
Maybe one of the articles linked here:
http://www.aspfaq.com/2403
(Or you might consider switching to XP_SMTP_SendMail.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"ChrisR" <noemail@.bla.com> wrote in message
news:OZ7ldn3PFHA.2348@.tk2msftngp13.phx.gbl...
> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook
> is opened. But if I close Outlook, I will be able to test with success no
> more than 2 times. (Though it does say sent successfull.) I will hit the
> test button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't
> allow me too. It complains about the Outlook.pst file being in use. The
> only way to free this file up is to restart the SQL Agent. Only then does
> my test email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>
|||ChrisR
your not alone, i had the same issue. NEVER found an answer ((HUGS))
"ChrisR" wrote:

> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook is
> opened. But if I close Outlook, I will be able to test with success no more
> than 2 times. (Though it does say sent successfull.) I will hit the test
> button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't allow
> me too. It complains about the Outlook.pst file being in use. The only way
> to free this file up is to restart the SQL Agent. Only then does my test
> email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>

Outlook needs to be open for SMTP.

sql2k sp3a
Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
for SQL Alerts. I can get everything running with no problems. I set up an
operator and can test it 10 times in a row with success... while Outlook is
opened. But if I close Outlook, I will be able to test with success no more
than 2 times. (Though it does say sent successfull.) I will hit the test
button and the email will never arrive.
I will then log onto the box and attempt to open Outlook and it won't allow
me too. It complains about the Outlook.pst file being in use. The only way
to free this file up is to restart the SQL Agent. Only then does my test
email possibly arrive.
Has anyone else experienced this? Fix?
TIA, ChrisRMaybe one of the articles linked here:
http://www.aspfaq.com/2403
(Or you might consider switching to XP_SMTP_SendMail.)
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"ChrisR" <noemail@.bla.com> wrote in message
news:OZ7ldn3PFHA.2348@.tk2msftngp13.phx.gbl...
> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook
> is opened. But if I close Outlook, I will be able to test with success no
> more than 2 times. (Though it does say sent successfull.) I will hit the
> test button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't
> allow me too. It complains about the Outlook.pst file being in use. The
> only way to free this file up is to restart the SQL Agent. Only then does
> my test email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>|||ChrisR
your not alone, i had the same issue. NEVER found an answer ((HUGS))
"ChrisR" wrote:
> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook is
> opened. But if I close Outlook, I will be able to test with success no more
> than 2 times. (Though it does say sent successfull.) I will hit the test
> button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't allow
> me too. It complains about the Outlook.pst file being in use. The only way
> to free this file up is to restart the SQL Agent. Only then does my test
> email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>

Outlook needs to be open for SMTP.

sql2k sp3a
Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
for SQL Alerts. I can get everything running with no problems. I set up an
operator and can test it 10 times in a row with success... while Outlook is
opened. But if I close Outlook, I will be able to test with success no more
than 2 times. (Though it does say sent successfull.) I will hit the test
button and the email will never arrive.
I will then log onto the box and attempt to open Outlook and it won't allow
me too. It complains about the Outlook.pst file being in use. The only way
to free this file up is to restart the SQL Agent. Only then does my test
email possibly arrive.
Has anyone else experienced this? Fix?
TIA, ChrisRMaybe one of the articles linked here:
http://www.aspfaq.com/2403
(Or you might consider switching to XP_SMTP_SendMail.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"ChrisR" <noemail@.bla.com> wrote in message
news:OZ7ldn3PFHA.2348@.tk2msftngp13.phx.gbl...
> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook
> is opened. But if I close Outlook, I will be able to test with success no
> more than 2 times. (Though it does say sent successfull.) I will hit the
> test button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't
> allow me too. It complains about the Outlook.pst file being in use. The
> only way to free this file up is to restart the SQL Agent. Only then does
> my test email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>|||ChrisR
your not alone, i had the same issue. NEVER found an answer ((HUGS))
"ChrisR" wrote:

> sql2k sp3a
> Ive been testing using the Pop3/ SMTP option as an alternative to Exchange
> for SQL Alerts. I can get everything running with no problems. I set up an
> operator and can test it 10 times in a row with success... while Outlook i
s
> opened. But if I close Outlook, I will be able to test with success no mor
e
> than 2 times. (Though it does say sent successfull.) I will hit the test
> button and the email will never arrive.
> I will then log onto the box and attempt to open Outlook and it won't allo
w
> me too. It complains about the Outlook.pst file being in use. The only way
> to free this file up is to restart the SQL Agent. Only then does my test
> email possibly arrive.
> Has anyone else experienced this? Fix?
> TIA, ChrisR
>
>sql

Outlook Mail on Cluster

Hi All
I have Sql2k virtual Server running in Active passive mode on Win2k3
Cluster.
Now my question is how do i configure the Outlook client on those nodes, is
it just a straight install on both the nodes and then confiture it with Sql
Virtual server?
Can i make the Oulook client to be a cluster resource
thanks in advance
Outlook is not cluster aware. You can create identical Outlook profiles on
all cluster nodes but any messages 'in process' during a cluster failover
will be lost. Outlook is also not a service so there can be some stability
issues as well. I prefer using xp_smtp from www.sqldev.net. It is not a
drop-in replacement for SQL Mail, but it can be adapted or extended to cover
most of your needs. I use it on a cluster and it is extremely stable.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:uT$Ro692EHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I have Sql2k virtual Server running in Active passive mode on Win2k3
> Cluster.
> Now my question is how do i configure the Outlook client on those nodes,
is
> it just a straight install on both the nodes and then confiture it with
Sql
> Virtual server?
> Can i make the Oulook client to be a cluster resource
> thanks in advance
>
|||Hi Nik,
In addition to the great advice/comments that Geoff had, I wanted to add
that SQL Mail is not supported on a cluster.
http://support.microsoft.com/default...B;EN-US;298723
BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster
Virtual SQL Servers (298723)
Thanks!
Donna Lambert
Microsoft PSS
SQL Server Support
|||not supported, but works to some degree... well, thats what i am hearing
from others.
"Donna Lambert [MS]" <dlambert@.online.microsoft.com> wrote in message
news:ke4CL9rGFHA.1140@.TK2MSFTNGXA02.phx.gbl...
> Hi Nik,
> In addition to the great advice/comments that Geoff had, I wanted to add
> that SQL Mail is not supported on a cluster.
> http://support.microsoft.com/default...B;EN-US;298723
> BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster
> Virtual SQL Servers (298723)
> Thanks!
> Donna Lambert
> Microsoft PSS
> SQL Server Support
>

Outlook Client on SQL Cluster

Hi,
We currently have a two node sql cluster running SQL Mail.
At present we have to manually start up outlook so the mail client works.
I have been looking at the resource options and wondered if it was possible to setup outlook client as a resource and if so, what reg keys would I need to tell the resource to access?
Cheers
Adam
Hi Adam,
If I get your question right, you are wondering if you could make Outlook client as a cluster resource.
Answer is Yes. I am not well aware of what Outlook clients need( like regkeys e.t.c.). But say if you want to make a simple application like Notepad.exe or Cmd.exe you could host them as cluster resources by making a resource of type "Generic Application".
Here is a little more on Generic Application resouce type: http://msdn.microsoft.com/library/de...pplication.asp
If this does not help then you might need to write a Resource dll that could make the interact with the OutLook client.
More on creating new Resource Types: http://msdn.microsoft.com/library/de...urce_types.asp
Thanks,
Sandeep Sutari
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
"Adam Maltby" <adamm@.frfl.co.uk> wrote in message news:enSXGsoLEHA.3324@.TK2MSFTNGP10.phx.gbl...
Hi,
We currently have a two node sql cluster running SQL Mail.
At present we have to manually start up outlook so the mail client works.
I have been looking at the resource options and wondered if it was possible to setup outlook client as a resource and if so, what reg keys would I need to tell the resource to access?
Cheers
Adam
|||Thanks for your response..... I got it working (in a fashion)...
My problem now is that although outlook will start and receive email as a cluster resource, sql email cannot access it, reason being it is now a cluster service it has to start using cluster services credentials, not the logged in user credentials....
Does anyone know of any way round that?
Cheers
Adam
|||MSCS doesn't support SQL Mail properly...
Try to use non-clustering server instead.
sql

Outlook + SQL 2000 SP4 on Windows 2003 2node Active/Passive Cluste

Hi
I have only "heard" that installing Outlook on Windows 2003 cluster running
SQL 2000 might create problems.
Has anyone actually experienced this ? Or am I just hearing rumours.
I would like to install Outlook provided there is no down side to it. What
are the alternatives to Outlook or MAPI all together.
Thanks in advance.Hi
I have not heard of any Outlook/Cluster issue as such but..
it may be the only way of getting Extended MAPI installed on the system
without breaking any licencing agreement or installing exchange server. You
would need to set up exactly the same profile on both side of the cluster.
If you are using a SMTP server (such as excahnge) then you could use XP_SMTP
instead http://www.sqldev.net/xp/xpsmtp.htm
John
"kunalap" wrote:
> Hi
> I have only "heard" that installing Outlook on Windows 2003 cluster running
> SQL 2000 might create problems.
> Has anyone actually experienced this ? Or am I just hearing rumours.
> I would like to install Outlook provided there is no down side to it. What
> are the alternatives to Outlook or MAPI all together.
> Thanks in advance.
>|||"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:22DB6194-4E10-4D29-A0D6-4C15B5C5D21D@.microsoft.com...
> Hi
> I have only "heard" that installing Outlook on Windows 2003 cluster
running
> SQL 2000 might create problems.
> Has anyone actually experienced this ? Or am I just hearing rumours.
>
We have done this. The best advice as others have said is make sure the
profile is exactly the same on both servers.
If you're not connecting to an Exchange server,but say just using SMTP to
SEND message from SQL Server, I'd recommend installing a local SMTP instance
on each node and using that to rely the messages.
Have SQL Mail send to the local copy of the SMTP server. That eliminates a
lot of situations where the MAPI client will hang.
> I would like to install Outlook provided there is no down side to it. What
> are the alternatives to Outlook or MAPI all together.
> Thanks in advance.
>

Outlook

Folks,
Can you point me to where I can look for more info to complete this task.
There is a proprietary app running with SQL 2000 as backend. Everynight the
app put DB in single user mode and runs DBCC itself to check data. That
runs well except few times it failed to get exclusive login in order to
clear single user mode. It causes the app fails to open. I think I may
want to run a script at my computer querying the DB and notify someone by
using Outlook on my computer. Is there any info that can tell me how to
open Outlook from in inside sql script?
Thanks,Hi,
try to use xp_sendmail. but you don't need the database in
single user to run dbcc checkdb, you only need single user
mode if you use the repair option.
CMLC
>--Original Message--
>Folks,
>Can you point me to where I can look for more info to
complete this task.
>There is a proprietary app running with SQL 2000 as
backend. Everynight the
>app put DB in single user mode and runs DBCC itself to
check data. That
>runs well except few times it failed to get exclusive
login in order to
>clear single user mode. It causes the app fails to
open. I think I may
>want to run a script at my computer querying the DB and
notify someone by
>using Outlook on my computer. Is there any info that can
tell me how to
>open Outlook from in inside sql script?
>Thanks,
>
>.
>|||hi,
to use xp_sendmail we must have SQL mail configured on the SQL box. This is
not my case because I can't touch the box. What I wanted is to use Outlook
on my pc to send email out. Is it possible?
> you don't need the database in single user to run dbcc checkdb, you only
> need single user mode if you use the >repair option
the app is proprietary so ... no touching. It seems that it would repair
data if possible.
"CMLC" <anonymous@.discussions.microsoft.com> wrote in message
news:196d01c4a188$bc87d820$a401280a@.phx.gbl...
> Hi,
> try to use xp_sendmail. but you don't need the database in
> single user to run dbcc checkdb, you only need single user
> mode if you use the repair option.
> CMLC
>
>>--Original Message--
>>Folks,
>>Can you point me to where I can look for more info to
> complete this task.
>>There is a proprietary app running with SQL 2000 as
> backend. Everynight the
>>app put DB in single user mode and runs DBCC itself to
> check data. That
>>runs well except few times it failed to get exclusive
> login in order to
>>clear single user mode. It causes the app fails to
> open. I think I may
>>want to run a script at my computer querying the DB and
> notify someone by
>>using Outlook on my computer. Is there any info that can
> tell me how to
>>open Outlook from in inside sql script?
>>Thanks,
>>
>>.|||Hi,
Thanks for your post.
From your descriptions, I understood that you would like to notify the
logon customer by email but you cannot configure your SQL Profile. Correct
me if I was wrong.
Based on my scope, You can use other methods to send Simple Mail Transfer
Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
OLE Automation stored procedures. See the following documents for more
detailed information
HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
http://support.microsoft.com/?kbid=312839
Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
for sending MIME based email over SMTP, implemented as an Extended Stored
Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.|||Thank you. It's what I wanted. I was thinking of a much more complicated
Outlook approach.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:$y5iBpeoEHA.740@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that you would like to notify the
> logon customer by email but you cannot configure your SQL Profile. Correct
> me if I was wrong.
> Based on my scope, You can use other methods to send Simple Mail Transfer
> Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
> Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
> Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
> OLE Automation stored procedures. See the following documents for more
> detailed information
> HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
> http://support.microsoft.com/?kbid=312839
> Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
> for sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
> This document contains references to a third party World Wide Web site.
> Microsoft is providing this information as a convenience to you. Microsoft
> does not control these sites and has not tested any software or
> information
> found on these sites; therefore, Microsoft cannot make any representations
> regarding the quality, safety, or suitability of any software or
> information found there. There are inherent dangers in the use of any
> software found on the Internet, and Microsoft cautions you to make sure
> that you completely understand the risk before retrieving any software
> from
> the Internet.
>|||It works great for me. However is there any pros or cons of sending mail
out using CDOSYS such as security?
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:$y5iBpeoEHA.740@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that you would like to notify the
> logon customer by email but you cannot configure your SQL Profile. Correct
> me if I was wrong.
> Based on my scope, You can use other methods to send Simple Mail Transfer
> Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
> Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
> Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
> OLE Automation stored procedures. See the following documents for more
> detailed information
> HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
> http://support.microsoft.com/?kbid=312839
> Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
> for sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
> This document contains references to a third party World Wide Web site.
> Microsoft is providing this information as a convenience to you. Microsoft
> does not control these sites and has not tested any software or
> information
> found on these sites; therefore, Microsoft cannot make any representations
> regarding the quality, safety, or suitability of any software or
> information found there. There are inherent dangers in the use of any
> software found on the Internet, and Microsoft cautions you to make sure
> that you completely understand the risk before retrieving any software
> from
> the Internet.
>|||Hi,
It's great to hear that my suggestion is suitable for you.
Collaboration Data Objects (CDO) for Microsoft? Windows? 2000 (Cdosys.dll),
which implements the 2.0 version of the Collaboration Data Objects API
specification, is a Component Object Model (COM) component designed to
simplify writing programs that create or manipulate Internet messages. You
could ask this exchange newsgroup, where I think will be more professional
answers there.
IMO, CDOSYS requires relatively high level of development instead of
xp_sendmail do. I would like to suggest upgrading to the lastest updates,
Windows 2000 SP4 and Office 2000 SP3 (if you had that Office installed).
See the documents below for detailed information about why these updates
are important
OL2000: Information About the CDO E-mail Security Update
http://support.microsoft.com/default.aspx?scid=kb;en-us;268279
OFF2000: How to Update an Administrative Installation with the Outlook CDO
Security Update
http://support.microsoft.com/default.aspx?scid=kb;en-us;268719
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!sql

Outlook

Folks,
Can you point me to where I can look for more info to complete this task.
There is a proprietary app running with SQL 2000 as backend. Everynight the
app put DB in single user mode and runs DBCC itself to check data. That
runs well except few times it failed to get exclusive login in order to
clear single user mode. It causes the app fails to open. I think I may
want to run a script at my computer querying the DB and notify someone by
using Outlook on my computer. Is there any info that can tell me how to
open Outlook from in inside sql script?
Thanks,
Hi,
Thanks for your post.
From your descriptions, I understood that you would like to notify the
logon customer by email but you cannot configure your SQL Profile. Correct
me if I was wrong.
Based on my scope, You can use other methods to send Simple Mail Transfer
Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
OLE Automation stored procedures. See the following documents for more
detailed information
HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
http://support.microsoft.com/?kbid=312839
Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
for sending MIME based email over SMTP, implemented as an Extended Stored
Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
|||Thank you. It's what I wanted. I was thinking of a much more complicated
Outlook approach.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:$y5iBpeoEHA.740@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that you would like to notify the
> logon customer by email but you cannot configure your SQL Profile. Correct
> me if I was wrong.
> Based on my scope, You can use other methods to send Simple Mail Transfer
> Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
> Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
> Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
> OLE Automation stored procedures. See the following documents for more
> detailed information
> HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
> http://support.microsoft.com/?kbid=312839
> Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
> for sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
> This document contains references to a third party World Wide Web site.
> Microsoft is providing this information as a convenience to you. Microsoft
> does not control these sites and has not tested any software or
> information
> found on these sites; therefore, Microsoft cannot make any representations
> regarding the quality, safety, or suitability of any software or
> information found there. There are inherent dangers in the use of any
> software found on the Internet, and Microsoft cautions you to make sure
> that you completely understand the risk before retrieving any software
> from
> the Internet.
>
|||It works great for me. However is there any pros or cons of sending mail
out using CDOSYS such as security?
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:$y5iBpeoEHA.740@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that you would like to notify the
> logon customer by email but you cannot configure your SQL Profile. Correct
> me if I was wrong.
> Based on my scope, You can use other methods to send Simple Mail Transfer
> Protocol (SMTP) e-mail directly from SQL Server. For example, you can use
> Collaboration Data Objects for NT Server (CDONTS) or Collaboration Data
> Objects for Windows 2000 (CDOSYS) in combination with the sp_OA SQL Server
> OLE Automation stored procedures. See the following documents for more
> detailed information
> HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
> http://support.microsoft.com/?kbid=312839
> Secondly, http://www.sqldev.net/ provides a SMTP based SQL Mail solution
> for sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
> This document contains references to a third party World Wide Web site.
> Microsoft is providing this information as a convenience to you. Microsoft
> does not control these sites and has not tested any software or
> information
> found on these sites; therefore, Microsoft cannot make any representations
> regarding the quality, safety, or suitability of any software or
> information found there. There are inherent dangers in the use of any
> software found on the Internet, and Microsoft cautions you to make sure
> that you completely understand the risk before retrieving any software
> from
> the Internet.
>
|||Hi,
It's great to hear that my suggestion is suitable for you.
Collaboration Data Objects (CDO) for Microsoft? Windows? 2000 (Cdosys.dll),
which implements the 2.0 version of the Collaboration Data Objects API
specification, is a Component Object Model (COM) component designed to
simplify writing programs that create or manipulate Internet messages. You
could ask this exchange newsgroup, where I think will be more professional
answers there.
IMO, CDOSYS requires relatively high level of development instead of
xp_sendmail do. I would like to suggest upgrading to the lastest updates,
Windows 2000 SP4 and Office 2000 SP3 (if you had that Office installed).
See the documents below for detailed information about why these updates
are important
OL2000: Information About the CDO E-mail Security Update
http://support.microsoft.com/default...b;en-us;268279
OFF2000: How to Update an Administrative Installation with the Outlook CDO
Security Update
http://support.microsoft.com/default...b;en-us;268719
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Outer Join in MS Access

Anyone has the idea of how to running outer join query in MS Access?Looks like MS Access does not support Outer Join query. Please correct me if this is not ture.i do outer joins in access all the time

show us yours, and the error message you got

rudysql

Out of virtual memory

I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline]

Information: The buffer manager detected that the system was low on

virtual memory, but was unable to swap out any buffers. 4 buffers were

considered and 4 were locked. Either not enough memory is available to

the pipeline because not enough is installed, other processes are using

it, or too many buffers are locked

Thank you for your assistance!write a sql command like "select * from destination_table where 1=0",otherwise SSIS will retrieve all data from your destination table.|||Hi icewill,

Not quite sure I understand why I would do that, I do want all the records. Would you please elaborate? We are loading a data warehouse. Thanks.|||Hello there,

I got such problem a while ago. By reducing "DefaultBufferMaxRow" to certain amount (My package was set to 5000) and keep "DefaultBufferSize" as small as possible (default is 10MB, Max is 100MB), your package should run succefully. Please keep in mind that Test and messure could take little bit of time for reaching the best performance.

Good luck|||Hi

Steglitz,

I have tried numerous settings but none of

them successful. I have tried DefaultBufferMaxRow at the following

values 2000, 5000, 7500, 10000 along with DefaultBufferSize set to the

default, 25Mb, 50Mb and 75Mb.

When running

performance monitor the only difference I noticed was the number of

buffers went up as the values were increased. Here

is an article I found explaining buffer

sizing:

Understanding How the Data

Flow Task Sizes the Buffers

The

data flow engine begins the task of sizing its buffers by calculating

the estimated size of a single row of data. Then it multiplies the

estimated size of a row by the value of

DefaultBufferMaxRows to obtain a preliminary working

value for the buffer size.

If the result is more than

the value of DefaultBufferSize, the engine reduces

the number of rows.

|||Here is another issue to add on top of the previous... Once the package runs Sql Server is left holding all the memory to the system. We watched it both through Performance Monitor and through task manager and after an hour it had not released the memory... 1.4 Gb and holding. The only way we could get it to reduce was to restart the Sql Server service. Is there anyone else out there that seems to be having any of these issues?|||

Bagles,

Actually i dont have any idea about why you need data from destination table, i met the problem that you mentioned before, and at last i found if you select a table as your destination directly or write sql like "select * from destination_table", SSIS will retrieve all data from the database, if there is great number of records in your destination table,that will induce your SSIS out of memory, that is not what i want to see, my mainly purpose is to get the fields to map for, so i modified the sql like "select * from destination_table where 1=0",and the problem has been solved perfectly.

GL~

|||Hi Icewill,

Using a query like that to obtain the metadata makes sense, and I suppose this is probably a silly question, but are you then using an expression to inject the actual sql statement you want at runtime?

I am currently using the DataReader source component which does not allow that, should I be using the OLEDB Source instead? Overall is the DataReader component a bad choice?

Thank you all in advance!|||There are a variety of reasons your server could run out of virtual memory, with both SSIS and SQL2k5 running it, so a couple of questions first.

1. What OS? is it 32 bit or 64 bit?
2. What is the recovery model of your target database?
3. What destination type are you using? SQL Server destination or OLE DB Destination.
4. If you're using an OLE DB destination, what settings are being used, including DataAccessMode, and fast-load settings if applicable?
5. What is the volume of data your moving through the pipeline?

As for icewill's notion that SSIS via SQL is retriving all the data from the destination table, which may be relived by adding "1=0", that assertion is patently untrue and can be observed not to be the case using profiler.

We have partioned tables with billions of rows as target tables and in no case was all the data selected merely by chosing the table as a target, either during design or run-time.|||Greetings Jaegd,

1) We are using Windows Server 2003 Standard Edition with SP1, 32bit.
2) The recovery model is Simple
3) We are using the OLEDB destination with the data access mode of Table Name variable with Fast load.
4) For any package that is running into problems it occurs if there are more than several hundred thousand rows. I currently don't have a definitive threshold but usually if we are moving more than 400,000 rows we run into a problem.

So far here is what we have discovered through several days of testing;

1) It doesn't appear that SSIS is spooling (writing the stream to disk) as it should. The default setting is to the user's temp directory, we have currently set spooling to go to our E: drive due to a lack of space on our C: drive. We have been testing for a solid week and performance monitor has not reported 1 spooled buffer to date. We currently are profiling all of the SqlServer:SSIS Pipeline variables available in Performance monitor as well as the following general categories; SQLServer:Memory Manager, Processor, Physical Disk (all 3 drives), Memory (Available MBytes), and the .NET Data Provider for SqlServer.

2) We finally found the event handler for the logging of BufferResizing and from that we have gathered that SSIS will try to fill the buffer with the MAX amount of memory first (100Mb) and not the default buffer size of 10Mb. Please correct me if I am wrong but that is what we are witnessing.

One thing that would be great to see in the future is the amount of bytes Sql Server is calculating per row and the amount of bytes it is putting in the stream. This would allow people to gain a better understanding of how to use the setting DefaultBufferMaxRows, rather than just guessing at numbers, which is essentially what we have been doing.

3) Finally the biggest adjustment we have made, and which seems to be working, is to limit the amount of memory that Sql Server itself will use. As I stated in a previous post, Sql Server was using all the memory it could get and then not releasing it, which was killing the capabilities of Integration Services. We changed the setting in Sql Server on this particular box to allow Sql Server only half of the available memory which seems to have fixed the problem. Sql Server now will give back the memory that it has used over that setting which frees up enough for SSIS to run properly.

I would still love to hear from you and others that have experienced this problem or have advice on how to avoid it. The above is just what we have witnessed through our own trials and tribulations.

Thank you for your assistance and experience!|||I have taken an alternative route (which may or may not be viable in your implementation). That route is always running SSIS on separate hardware than SQL2k5 (there are licensing issues here naturally). Note, I'm not recommending SSIS/SQL separation for the general case, by the way, since separation adds substantial unnecessary network roundtrips, as well as eliminates the use of the high speed SQL Server destination.

However, SQL/SSIS separation does allow SQL Server "domination" over the memory/CPU on its hardware. Furthermore, and most importantly, separation allows the construction of an SSIS farm, that is, processing over N number of SSIS only machines.

As for x64 vs x86, generally speaking, running x64 SSIS gives me about a 30% boost (from a wall-clock perspective) over running x86 SSIS on the exact same job, an advantage which becomes greater at larger volume levels.

I know this doesn't speak to the spooling issue, or the optimization of the DefaultBufferMaxRows, since virtual memory has become an non-issue since moving to a hardware separation approach.|||Hi jaegd,

I really appreciate your input! Unfortunately I don't have any control over the hardware that is being used; however I do find it beneficial to know how others are using the product and found your use of it a nugget to hold onto for the future! Thanks again!|||

I have been thinking about creating a SSIS farm have you seen any documention on how to set this up and best practises?

I also installed x64 bit version of SSIS, any ideas how I can confirm this?

cheers

Marcus

Out of virtual memory

I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline]

Information: The buffer manager detected that the system was low on

virtual memory, but was unable to swap out any buffers. 4 buffers were

considered and 4 were locked. Either not enough memory is available to

the pipeline because not enough is installed, other processes are using

it, or too many buffers are locked

Thank you for your assistance!write a sql command like "select * from destination_table where 1=0",otherwise SSIS will retrieve all data from your destination table.|||Hi icewill,

Not quite sure I understand why I would do that, I do want all the records. Would you please elaborate? We are loading a data warehouse. Thanks.|||Hello there,

I got such problem a while ago. By reducing "DefaultBufferMaxRow" to certain amount (My package was set to 5000) and keep "DefaultBufferSize" as small as possible (default is 10MB, Max is 100MB), your package should run succefully. Please keep in mind that Test and messure could take little bit of time for reaching the best performance.

Good luck|||Hi

Steglitz,

I have tried numerous settings but none of

them successful. I have tried DefaultBufferMaxRow at the following

values 2000, 5000, 7500, 10000 along with DefaultBufferSize set to the

default, 25Mb, 50Mb and 75Mb.

When running

performance monitor the only difference I noticed was the number of

buffers went up as the values were increased. Here

is an article I found explaining buffer

sizing:

Understanding How the Data

Flow Task Sizes the Buffers

The

data flow engine begins the task of sizing its buffers by calculating

the estimated size of a single row of data. Then it multiplies the

estimated size of a row by the value of

DefaultBufferMaxRows to obtain a preliminary working

value for the buffer size.

If the result is more than

the value of DefaultBufferSize, the engine reduces

the number of rows.

|||Here is another issue to add on top of the previous... Once the package runs Sql Server is left holding all the memory to the system. We watched it both through Performance Monitor and through task manager and after an hour it had not released the memory... 1.4 Gb and holding. The only way we could get it to reduce was to restart the Sql Server service. Is there anyone else out there that seems to be having any of these issues?|||

Bagles,

Actually i dont have any idea about why you need data from destination table, i met the problem that you mentioned before, and at last i found if you select a table as your destination directly or write sql like "select * from destination_table", SSIS will retrieve all data from the database, if there is great number of records in your destination table,that will induce your SSIS out of memory, that is not what i want to see, my mainly purpose is to get the fields to map for, so i modified the sql like "select * from destination_table where 1=0",and the problem has been solved perfectly.

GL~

|||Hi Icewill,

Using a query like that to obtain the metadata makes sense, and I suppose this is probably a silly question, but are you then using an expression to inject the actual sql statement you want at runtime?

I am currently using the DataReader source component which does not allow that, should I be using the OLEDB Source instead? Overall is the DataReader component a bad choice?

Thank you all in advance!|||There are a variety of reasons your server could run out of virtual memory, with both SSIS and SQL2k5 running it, so a couple of questions first.

1. What OS? is it 32 bit or 64 bit?
2. What is the recovery model of your target database?
3. What destination type are you using? SQL Server destination or OLE DB Destination.
4. If you're using an OLE DB destination, what settings are being used, including DataAccessMode, and fast-load settings if applicable?
5. What is the volume of data your moving through the pipeline?

As for icewill's notion that SSIS via SQL is retriving all the data from the destination table, which may be relived by adding "1=0", that assertion is patently untrue and can be observed not to be the case using profiler.

We have partioned tables with billions of rows as target tables and in no case was all the data selected merely by chosing the table as a target, either during design or run-time.|||Greetings Jaegd,

1) We are using Windows Server 2003 Standard Edition with SP1, 32bit.
2) The recovery model is Simple
3) We are using the OLEDB destination with the data access mode of Table Name variable with Fast load.
4) For any package that is running into problems it occurs if there are more than several hundred thousand rows. I currently don't have a definitive threshold but usually if we are moving more than 400,000 rows we run into a problem.

So far here is what we have discovered through several days of testing;

1) It doesn't appear that SSIS is spooling (writing the stream to disk) as it should. The default setting is to the user's temp directory, we have currently set spooling to go to our E: drive due to a lack of space on our C: drive. We have been testing for a solid week and performance monitor has not reported 1 spooled buffer to date. We currently are profiling all of the SqlServer:SSIS Pipeline variables available in Performance monitor as well as the following general categories; SQLServer:Memory Manager, Processor, Physical Disk (all 3 drives), Memory (Available MBytes), and the .NET Data Provider for SqlServer.

2) We finally found the event handler for the logging of BufferResizing and from that we have gathered that SSIS will try to fill the buffer with the MAX amount of memory first (100Mb) and not the default buffer size of 10Mb. Please correct me if I am wrong but that is what we are witnessing.

One thing that would be great to see in the future is the amount of bytes Sql Server is calculating per row and the amount of bytes it is putting in the stream. This would allow people to gain a better understanding of how to use the setting DefaultBufferMaxRows, rather than just guessing at numbers, which is essentially what we have been doing.

3) Finally the biggest adjustment we have made, and which seems to be working, is to limit the amount of memory that Sql Server itself will use. As I stated in a previous post, Sql Server was using all the memory it could get and then not releasing it, which was killing the capabilities of Integration Services. We changed the setting in Sql Server on this particular box to allow Sql Server only half of the available memory which seems to have fixed the problem. Sql Server now will give back the memory that it has used over that setting which frees up enough for SSIS to run properly.

I would still love to hear from you and others that have experienced this problem or have advice on how to avoid it. The above is just what we have witnessed through our own trials and tribulations.

Thank you for your assistance and experience!|||I have taken an alternative route (which may or may not be viable in your implementation). That route is always running SSIS on separate hardware than SQL2k5 (there are licensing issues here naturally). Note, I'm not recommending SSIS/SQL separation for the general case, by the way, since separation adds substantial unnecessary network roundtrips, as well as eliminates the use of the high speed SQL Server destination.

However, SQL/SSIS separation does allow SQL Server "domination" over the memory/CPU on its hardware. Furthermore, and most importantly, separation allows the construction of an SSIS farm, that is, processing over N number of SSIS only machines.

As for x64 vs x86, generally speaking, running x64 SSIS gives me about a 30% boost (from a wall-clock perspective) over running x86 SSIS on the exact same job, an advantage which becomes greater at larger volume levels.

I know this doesn't speak to the spooling issue, or the optimization of the DefaultBufferMaxRows, since virtual memory has become an non-issue since moving to a hardware separation approach.|||Hi jaegd,

I really appreciate your input! Unfortunately I don't have any control over the hardware that is being used; however I do find it beneficial to know how others are using the product and found your use of it a nugget to hold onto for the future! Thanks again!|||

I have been thinking about creating a SSIS farm have you seen any documention on how to set this up and best practises?

I also installed x64 bit version of SSIS, any ideas how I can confirm this?

cheers

Marcus

Out of memory while querying causes wrong data to be returned

Running AS 2000, SP4.
A couple of times while querying a cube, one member has got the wrong data displayad (the value are to low).
No matter if I reload the query afterwards, restarting the query application etc, the same faulty value still gets displayed for that member.

After I restart analysis services the value is correct again. No other changes, exact the same query etc.
While analysing the event viewer I see that analysises services has run out of memory before this strange behaviour (MSSQLServerOLAPService - "The server is out of memory").

Could this out-of-memory error has caused the wrong value to be returned, and the somehow the value has got cached on the analysis server so it's not fixed until the server has restarted?
This is the only theory I have, and it's important to figure out the cause. Users are getting worried if they can't trust the data.

The server is dedicated to analysis services and has 3GB memory and /3GB switch enabled. Memory conservation treshold is 2700mb and Minimum allocated memory is set to 1536mb.
The query tool is an asp-application using ADOMD, but the same faulty value was returned while trying the query in MDX Sample App.

First of all try installing SP4 http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5

Second, if problem persists contact customer support and have them looking into your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Sorry, misprint, I am already running sp 4.|||

There isn't much to recommed in this case.

Have your problem looked at by the customer support ( the doctors) :)

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

Out of memory error when running large report

I have built a large report but when I try to execute inside of the Report
Designer, I get the following error and then an abort:
[An error has occurred during report processing
Exception of type System.OutOfMemoryException was thrown]
I have applied SRS SP1 and here is the MSD info:
Microsoft Visual Basic .NET 69457-005-1826837-18410
Microsoft SQL Server Report Designer Version 8.00.878.00
Running on XP SP2 with 1.25 gig RAM
Is there a work-around for this?
Thanks for any help!http://support.microsoft.com/?kbid=840709
This article may help you.
| From: "Mike Harbinger" <MikeH@.Cybervillage.net>
| Subject: Out of memory error when running large report
| Date: Thu, 6 Jan 2005 18:47:55 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69.106.50.114
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10|||Thanks for the reponse Brad, but I am not getting the error from the report
server but from inside of the .Net Visual Studio Reporting Services
designer.
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:g7L9ZjO9EHA.3200@.cpmsftngxa10.phx.gbl...
> http://support.microsoft.com/?kbid=840709
> This article may help you.
>
> --
> | From: "Mike Harbinger" <MikeH@.Cybervillage.net>
> | Subject: Out of memory error when running large report
> | Date: Thu, 6 Jan 2005 18:47:55 -0800
> | Lines: 18
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 69.106.50.114
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38909
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have built a large report but when I try to execute inside of the
> Report
> | Designer, I get the following error and then an abort:
> |
> | [An error has occurred during report processing
> | Exception of type System.OutOfMemoryException was thrown]
> |
> |
> | I have applied SRS SP1 and here is the MSD info:
> | Microsoft Visual Basic .NET 69457-005-1826837-18410
> | Microsoft SQL Server Report Designer Version 8.00.878.00
> |
> | Running on XP SP2 with 1.25 gig RAM
> |
> | Is there a work-around for this?
> |
> | Thanks for any help!
> |
> |
> |
>

Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks

other programs won't start while SQL is running

First of all I'm not a sql guru. I'm just doing some troubleshooting on the server.
I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints from the DBA about sql performance.
The problem is while sql is running other programs won't start.
IIS
v2i protector
They time out. If I close sql and restart the other services they start fine but then sql won't start.
The event id's I get for all programs are
26-Application popup:'program.exe' application error: The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.
7009- Timeout (30000 milliseconds) waiting for the 'program' service to connect.
7000-The 'program' service failed to start due to the following error" the service did not respond to the start or control request in a timely fashion.
Has anyone seen a situation like this before?
ThanksHi,
Can you make the SQL Server Memory to Static and Restart the SQL Server and
other services. It seems
SQL Server meory set to dynamic and it utilize all the memory available in
the system. This might be a
problem.
Using task manager check the memory utilized by SQLSERVR.EXE.
--
Thanks
Hari
MCDBA
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||Rich,
Sounds like you may be lacking enough memory to operate properly or someone
has turned off the Dynamic Memory allocation in SQL Server by setting the
SET Working Set size to On. Make sure the Memory in SQL Server is still set
to Dynamic but lower the Max Memory setting enough to leave memory for the
other applications.
--
Andrew J. Kelly SQL MVP
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||"Andrew J. Kelly" wrote:
> Rich,
> Sounds like you may be lacking enough memory to operate properly or someone
> has turned off the Dynamic Memory allocation in SQL Server by setting the
> SET Working Set size to On. Make sure the Memory in SQL Server is still set
> to Dynamic but lower the Max Memory setting enough to leave memory for the
> other applications.
> --
> Andrew J. Kelly SQL MVP
>
> "Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
> news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> > First of all I'm not a sql guru. I'm just doing some troubleshooting on
> the server.
> >
> > I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
> from the DBA about sql performance.
> >
> > The problem is while sql is running other programs won't start.
> > IIS
> > v2i protector
> >
> > They time out. If I close sql and restart the other services they start
> fine but then sql won't start.
> >
> > The event id's I get for all programs are
> > 26-Application popup:'program.exe' application error: The application
> failed to initialize properly (0xc0000142). Click on OK to terminate the
> application.
> >
> > 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
> connect.
> >
> > 7000-The 'program' service failed to start due to the following error" the
> service did not respond to the start or control request in a timely fashion.
> >
> > Has anyone seen a situation like this before?
> >
> > Thanks
> >
> After you guys replied to my post I checked out the server after the boot. Between the MSMDSRV.EXE and the SQLSERVR.EXE they were using up 1.6 gig of memory!!! I'm still not quite sure of the fix yet but the I found a MS knowledgebase article concerning a memory leak in MSMDSRV.EXE. We're looking into it and are keeping your posts in mind. Thank you both very much.
>

other programs won't start while SQL is running

First of all I'm not a sql guru. I'm just doing some troubleshooting on the server.
I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints from the DBA about sql performance.
The problem is while sql is running other programs won't start.
IIS
v2i protector
They time out. If I close sql and restart the other services they start fine but then sql won't start.
The event id's I get for all programs are
26-Application popup:'program.exe' application error: The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.
7009- Timeout (30000 milliseconds) waiting for the 'program' service to connect.
7000-The 'program' service failed to start due to the following error" the service did not respond to the start or control request in a timely fashion.
Has anyone seen a situation like this before?
Thanks
Hi,
Can you make the SQL Server Memory to Static and Restart the SQL Server and
other services. It seems
SQL Server meory set to dynamic and it utilize all the memory available in
the system. This might be a
problem.
Using task manager check the memory utilized by SQLSERVR.EXE.
Thanks
Hari
MCDBA
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>
|||Rich,
Sounds like you may be lacking enough memory to operate properly or someone
has turned off the Dynamic Memory allocation in SQL Server by setting the
SET Working Set size to On. Make sure the Memory in SQL Server is still set
to Dynamic but lower the Max Memory setting enough to leave memory for the
other applications.
Andrew J. Kelly SQL MVP
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>
|||"Andrew J. Kelly" wrote:

> Rich,
> Sounds like you may be lacking enough memory to operate properly or someone
> has turned off the Dynamic Memory allocation in SQL Server by setting the
> SET Working Set size to On. Make sure the Memory in SQL Server is still set
> to Dynamic but lower the Max Memory setting enough to leave memory for the
> other applications.
> --
> Andrew J. Kelly SQL MVP
>
> "Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
> news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> the server.
> from the DBA about sql performance.
> fine but then sql won't start.
> failed to initialize properly (0xc0000142). Click on OK to terminate the
> application.
> connect.
> service did not respond to the start or control request in a timely fashion.
> After you guys replied to my post I checked out the server after the boot. Between the MSMDSRV.EXE and the SQLSERVR.EXE they were using up 1.6 gig of memory!!! I'm still not quite sure of the fix yet but the I found a MS knowledgebase article concerni
ng a memory leak in MSMDSRV.EXE. We're looking into it and are keeping your posts in mind. Thank you both very much.
>