Internal Activation - calls stored procs in other DBs

Hi all

I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".

I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?

Thanks

Ian

You are hitting the 'Extending database impersonation under EXECUTE AS context' issue. I have a series of posts in my blog tackling this problem:

http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx

The first link is posted today and is an actual example on how to call a procedure in another database under activation.

|||

Thanks for the information - it's just what I was looking for.

Ian

|||

One more question....

Is it essential that the owner of the other DB is the same as the owner of the activated stored proc?

I don't seem to be able to get this to work if they are different.

Thanks

Ian

|||You can use any user with receive permission on the queue.|||I think you must grant AUTHENTICATE permission on the 'other' DB to the user from the EXECUTE AS clause of the CREATE/ALTER procedure. If the EXECUTE AS is OWNER, then to the owner of the activated procedure)

Intermittent SQL Server does not exist or access denied.

Hi, I have a web application which is taking a fair bit of traffic.
It involves a registration process, and then a series of questions which
must be answered. The SQL server receives upwards of 1,000,000 queries a
day.
Unfortunately on a small number of these queries (~500 a day), the ASP pages
are returning that the SQL server does not exist or that access if denied.
Having explored the Microsoft Support Site, and exhausted all possible
solutions, without actually reaching a solution - I was wondering - is it
possible that the server is actually just taking so many requests that it
can't be found? Surely this should return a timeout?
The web box is a Windows 2000 IIS installation, and the SQL box is a
separate Windows 2000 / SQL Server 2000 installation.
Anyone got any ideas?
Many thanks.
MattWhat are you using to handle connections -- TCP/IP sockets, named pipes or
both? I would double check your IIS server and SQL server to make sure the
server and client network utilities match up with connection parameters.
Have you tried using Performance Monitor on the SQL server? You will take a
small performance hit, but you can get some more information on what is
happening when the connection fails. You should probably start off
monitoring a few basic SQL counters -- like for reads, writes and
connections.
When a SQL connection can't be made, the SQL server does not exist or access
denied is actually a common error. In my line of support, it usually means
that a network connection (ipc$) between the client and server can't be
made, but I imagine this is not the case for you.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Matt Pallatt" <matt.pallatt@.bnm.co.uk> wrote in message
news:%23ZMUOpzQEHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a web application which is taking a fair bit of traffic.
> It involves a registration process, and then a series of questions which
> must be answered. The SQL server receives upwards of 1,000,000 queries a
> day.
> Unfortunately on a small number of these queries (~500 a day), the ASP
pages
> are returning that the SQL server does not exist or that access if denied.
> Having explored the Microsoft Support Site, and exhausted all possible
> solutions, without actually reaching a solution - I was wondering - is it
> possible that the server is actually just taking so many requests that it
> can't be found? Surely this should return a timeout?
> The web box is a Windows 2000 IIS installation, and the SQL box is a
> separate Windows 2000 / SQL Server 2000 installation.
> Anyone got any ideas?
> Many thanks.
> Matt
>|||So connection/session pooling should be in use for large web based
applications. If you've disabled connection/session pooling then this
would put a heavier burden on the server.
What is your connection Timeout value?
What is running on the server when the timeouts happen?
Is there any blocking?
How many web servers are connecting to the SQL Server?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We were having the exact same problem. It seems that by stopping SQL Server
from listening on named pipes (ie. just leaving tcp/ip) stopped the problem|||Sorry, may have spoken too soon. Our error have come back.

Intermittent SQL Server does not exist or access denied.

Hi, I have a web application which is taking a fair bit of traffic.
It involves a registration process, and then a series of questions which
must be answered. The SQL server receives upwards of 1,000,000 queries a
day.
Unfortunately on a small number of these queries (~500 a day), the ASP pages
are returning that the SQL server does not exist or that access if denied.
Having explored the Microsoft Support Site, and exhausted all possible
solutions, without actually reaching a solution - I was wondering - is it
possible that the server is actually just taking so many requests that it
can't be found? Surely this should return a timeout?
The web box is a windows 2000 IIS installation, and the SQL box is a
separate Windows 2000 / SQL Server 2000 installation.
Anyone got any ideas?
Many thanks.
Matt
What are you using to handle connections -- TCP/IP sockets, named pipes or
both? I would double check your IIS server and SQL server to make sure the
server and client network utilities match up with connection parameters.
Have you tried using Performance Monitor on the SQL server? You will take a
small performance hit, but you can get some more information on what is
happening when the connection fails. You should probably start off
monitoring a few basic SQL counters -- like for reads, writes and
connections.
When a SQL connection can't be made, the SQL server does not exist or access
denied is actually a common error. In my line of support, it usually means
that a network connection (ipc$) between the client and server can't be
made, but I imagine this is not the case for you.
************************************************** *****************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
************************************************** *****************
"Matt Pallatt" <matt.pallatt@.bnm.co.uk> wrote in message
news:%23ZMUOpzQEHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a web application which is taking a fair bit of traffic.
> It involves a registration process, and then a series of questions which
> must be answered. The SQL server receives upwards of 1,000,000 queries a
> day.
> Unfortunately on a small number of these queries (~500 a day), the ASP
pages
> are returning that the SQL server does not exist or that access if denied.
> Having explored the Microsoft Support Site, and exhausted all possible
> solutions, without actually reaching a solution - I was wondering - is it
> possible that the server is actually just taking so many requests that it
> can't be found? Surely this should return a timeout?
> The web box is a windows 2000 IIS installation, and the SQL box is a
> separate Windows 2000 / SQL Server 2000 installation.
> Anyone got any ideas?
> Many thanks.
> Matt
>
|||So connection/session pooling should be in use for large web based
applications. If you've disabled connection/session pooling then this
would put a heavier burden on the server.
What is your connection Timeout value?
What is running on the server when the timeouts happen?
Is there any blocking?
How many web servers are connecting to the SQL Server?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||We were having the exact same problem. It seems that by stopping SQL
Server from listening on named pipes (ie. just leaving tcp/ip) stopped
the problem
chudles
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message245192.html
|||Sorry, may have spoken too soon. Our error have come back.
chudles
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message245192.html

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?
Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
--
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Interactive Resolver

Error message: "The process failed to get information about the Interactive Resolver from the registry".
I am using MSDE to subscribe to a central 2000 database using a pull subscription and the active X controls. However, the default resolver fails to operate. I have a call in with Product Support and sent them this:
Publication articles are all marked “Use the default resolver” and “Allow Subscribers to resolve conflicts interactively during on-demand synchronizations”. Note we are treating changes to the same column as being a conflict.
On the subscriber, “on demand synchronisations” are marked “Resolve Conflicts interactively…”
In my code to control the ActiveX objects, if I set the “UseInteractiveResolver” property to True, then the error message appears. If I leave this out, the default priority subscription happens.
We ARE NOT using any third party resolver, nor are we using any resolver aside from the interactive one.
However, I still cannot get the interactive resolver to display.
Is there something else. I have tried re-registering all DLLs back on the client after copying them from the 2000 CD ROM (SP3)
Hi Top,
From your descriptoin, I learnt that you the default resolver failed to operate when you subscribed to a
central 2000 database using a pull subscription and the active X controls.
Based on my experience (not 100% sure), the issue would occur if we manually or programmatically
change the Progid of conflict viewer from Wzconflict to CnfViewer. If so, the sync manager is unable to
launch the conflict viewer.
As you have call into Microsoft with Product Support, could you provide us with the Case ID so that we can
handle your post more gracefully and also for community benefits. In this way, we can address your
problem quickly and efficiently making headway to a solution.
We look forward to hearing from you.
Best regards,
Billy Yao
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||SRS040408600030
|||Hi Mark,
Thank you for your information! We have looked into the issue and found the causes of this issue as I
mentioned in my previous post.
When you tried to merge the data from your client which had MSDE installed you got following error 'The
process failed to get information about the interactive resolver component from the registry". This error was
due to missing DLL files or registry entries of the conflict viewer on your client system.
To solve the problem, we should check the following Registry Keys
------
HKCR\CLSID\{A2CA91F1-7328-4949-8611-8BEDB4AD503C}\InprocServer32 --> 'C:\Program Files
\Common Files\Microsoft Shared\Database Replication\Conflict Viewer\wzcnf.dll'
Check if the dll exists in the same location.
[INFO]Other Keys Accessed by conflict viewer:
HKCR\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32 -->'C:\Program Files\Common
Files\system\ado\msado15.dll'
HKCU\CLSID\{0BE35204-8F91-11CE-9DE3-00AA004BB851}\InprocServer32 -->'oleaut32.dll'
HKCR\CLSID\{0BE35204-8F91-11CE-9DE3-00AA004BB851}\InprocServer32 -->'oleaut32.dll'
------
and verify this steps
Steps:
======
1) The Following Files must be in the Directories mentioned for Interactive Conflict resolver to work.
\Program Files\Common Files\Microsoft Shared\Database Replication\Conflict Viewer: wzcnf.dll ,
wzcnfclt.exe
2) The following resource file should be installed in a language specific directory \Program Files\Common
Files\Microsoft Shared\Database Replication\Conflict Viewer\Resources\<LCID>: wzcnfrc.rll
The <LCID> is usually 1033.
IF these files are missing from these directories copy them from the same location on other systems where
the conflict viewer is working fine , or copy them from the Setup CD.Now go to the directory where "Wzcnf.dll
" is located and register it by typing "Regsvr32 wzcnf.dll".
NOTE: If we dont have the Resource file Wzcnfrc.rll, The Conflict viewer will still come up, but the Labels will
be missing from the Buttons on the User Interface.Once we have the Files in place and the Dll file registered,
we'll get the Interactive conflict viwer to come up normally on the MSDE Subscribers.
NOTE: The Conflict Viewer and Interactive Conflict Viewer uses the same exe and Dll.
We appreciated your time and efforts throughout the troubleshooting process. Thank you for posting in
newsgroup.
Best regards,
Billy Yao
Microsoft Online Support
Please reply to newsgroups only. Thanks.