resource limit was reached

Error:
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
--
Regards,
JamieAs a workaround, can you try using TCP\IP provider for linking?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> > Error:
> > The OLE DB provider "SQLNCLI" for linked server reported an error.
> > Execution
> > terminated by the provider because a resource limit was reached.
> > Situation:
> > Large query is pulled from one server to another (data warehouse).
> > Suspect:
> > DTC on the Windows server 2003 where the source server is located (SQL 2k)
> > does not have DTC enabled:
> > Looking for:
> > A work-around as we are in a situation where the server cannot yet be
> > rebooted.
> >
> > Question:
> > Is there a way to configure named pipes to effectively handle the resource
> > limitation on linked servers?
> > --
> > Regards,
> > Jamie
>
>|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either. :(
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
>> As a workaround, can you try using TCP\IP provider for linking?
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>> > Error:
>> > The OLE DB provider "SQLNCLI" for linked server reported an error.
>> > Execution
>> > terminated by the provider because a resource limit was reached.
>> > Situation:
>> > Large query is pulled from one server to another (data warehouse).
>> > Suspect:
>> > DTC on the Windows server 2003 where the source server is located (SQL
>> > 2k)
>> > does not have DTC enabled:
>> > Looking for:
>> > A work-around as we are in a situation where the server cannot yet be
>> > rebooted.
>> >
>> > Question:
>> > Is there a way to configure named pipes to effectively handle the
>> > resource
>> > limitation on linked servers?
>> > --
>> > Regards,
>> > Jamie
>>