Can't start transactional replication SQL 2005. System.OutOfMemory Exception during start

We currently use log shipping to 2 other servers for reporting. We
want to use transactional replication instead, so that our reporting
databases are always available (when the restore agent runs the
database is unavailable for a few seconds) and to reduce the latency
between changes in the main database appearing in our reporting
database.
I've successfully got replication to work on a test scenario (with
only a few thousand rows) but when I try and get transactional
replication going on our production site I get a System.OutOfMemory
exception when performing the snapshot. It fails at about 40 percent
of one of our larger tables (157 million rows.) The database backup
is about 27GB.
The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
Server 2003.
We are running SQL2005 Standard edtion SP2, but I had the same problem
on SP1.
I've tried the /3GB switch.
I've tried putting the distrubutor on the main server, and on a
seperate server, and on the reporting box.
I've tried pull and push subscriptions.
I'm going to try a row filter to pull only the most recent data (to
drop the number of rows) and see at what point it fails, but this is a
fairly long interative process. And we do need all the data in the
end.
I'm looking for advice, has anybody else had similar problems?
Is there a way to start replication from a restored database?
I'm considering installing more Memory - but am unsure how to estimate
how much more memory we need. Also our machines run fine with 4GB of
memory normally; I can't really afford to buy more memory just to
"start" the replication process going (but of course I guess this
depends on how much more I would need!)
Daniel Bryars
This is something that I would really like to have a closer look so let me
ask you a few questions:
1) Is snapshot.exe consuming a large amount of memory according to taskmgr?
2) Do you have a pagefile configured on the distributor machine? I have
heard of one case where disabling the pagefile seemed to be the cause of OOM
error in the snapshot agent. I suspect that this may have something to do
with the use of memory mapped file in the underlying BCP api but I was never
able to prove one way or another.
3) Do you have a large amount of blob column data?
If possible, you may also want to consider upgrading your software stack at
your distributor (OS + SQL Server) to 64bit as it is quite possible that you
are running out of address space range. Most dual-core processors should
already be capable of running in 64bit mode.
-Raymond
<bryars@.hotmail.com> wrote in message
news:1174821642.773195.15500@.e65g2000hsc.googlegro ups.com...
> We currently use log shipping to 2 other servers for reporting. We
> want to use transactional replication instead, so that our reporting
> databases are always available (when the restore agent runs the
> database is unavailable for a few seconds) and to reduce the latency
> between changes in the main database appearing in our reporting
> database.
> I've successfully got replication to work on a test scenario (with
> only a few thousand rows) but when I try and get transactional
> replication going on our production site I get a System.OutOfMemory
> exception when performing the snapshot. It fails at about 40 percent
> of one of our larger tables (157 million rows.) The database backup
> is about 27GB.
> The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
> Server 2003.
> We are running SQL2005 Standard edtion SP2, but I had the same problem
> on SP1.
> I've tried the /3GB switch.
> I've tried putting the distrubutor on the main server, and on a
> seperate server, and on the reporting box.
> I've tried pull and push subscriptions.
> I'm going to try a row filter to pull only the most recent data (to
> drop the number of rows) and see at what point it fails, but this is a
> fairly long interative process. And we do need all the data in the
> end.
> I'm looking for advice, has anybody else had similar problems?
> Is there a way to start replication from a restored database?
> I'm considering installing more Memory - but am unsure how to estimate
> how much more memory we need. Also our machines run fine with 4GB of
> memory normally; I can't really afford to buy more memory just to
> "start" the replication process going (but of course I guess this
> depends on how much more I would need!)
> Daniel Bryars
>

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0

|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks
|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent
sql

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Cant Handle Dts Error - Transaction was deadlocked...

Hi, i get this error while imanuallyexecute dts. But when i execute dts on my .aspx page, i can't handle this error on "... catch(Exception ex) {... }" part.

catch (Exception ex)
 {return ex.Message ;
//DtsPackage.OnError += new PackageEvents_OnErrorEventHandler(DtsPackage_OnError); }

Here is dts message in a text file.

Step 'DTSStep_DTSDataPumpTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Any idea?

A deadlock happens when two users are trying to get each other's resources.

Here's an example:

Bob is running a process that is updating the Employee table and, in the middle of it, his code tries to update the Customer table.

Sally is running a process that is updating the Customer table on the same Customer that Bob is ABOUT TO deal with and, in the middle of it, her code tries to update the Employee that Bob has already got locked.

Neither can get the other's resource until the other one lets go.

|||

Of course it is. I know what a deadlock is. My question is i can't handle error from my web page. Dts does not return error code. It says "Dts succeeded...".

And i handle it with getting output and get the rows which start with "Error....".

Thanks for your reply..