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 Timeout - ExecuteNonQuery On Stored Procedure

Hi All,
I'll do my best to try and describe the issue I'm having with my
application.
I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.
Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).
Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.
When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.
After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.
What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables?
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.
I'm a bit lost really.
Any help is appreciated.
Thanks
Alex
******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn
@.UserID int,
@.LoggedIn bit = 0
AS
SET NOCOUNT ON
UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @.LoggedIn
WHERE UserID = @.UserID
*********************************
Hi Alex,
Are you ending properly transactions?
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to
> the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login
> class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the
> mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables?
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
|||Hi
Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Regards
Mike
"Alex Stevens" wrote:

> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables?
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
>
|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
ExecuteNonQuery[vbcol=seagreen]
to[vbcol=seagreen]
Fill[vbcol=seagreen]
stored[vbcol=seagreen]
in[vbcol=seagreen]
again[vbcol=seagreen]
I
>
|||As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.
The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.
In query analyzer, Select statements work fine, but updates don't.

> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).
I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.
How can I check to see if a process is blocking the UPDATE process?
Thanks
Alex
******Stored Proc*******
ALTER PROC proc_Get_User
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
-- Used in Classes:
@.UserID int = Null,
@.UserName varChar(20) = Null
AS
BEGIN
SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@.UserID IS NULL OR UserID = @.UserID) OR
(@.UserName IS NULL OR UserName = @.UserName)
ORDER BY UserName
END
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> Hi
> Timeouts are caused by SQL not getting it's work finished in time. This
> indicates a blocking or performance issue.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query[vbcol=seagreen]
> through your VB code or Query Analyser.
> Regards
> Mike
> "Alex Stevens" wrote:
the[vbcol=seagreen]
class,[vbcol=seagreen]
mail -[vbcol=seagreen]
ExecuteNonQuery[vbcol=seagreen]
to[vbcol=seagreen]
Fill[vbcol=seagreen]
stored[vbcol=seagreen]
in[vbcol=seagreen]
will[vbcol=seagreen]
again[vbcol=seagreen]
I[vbcol=seagreen]

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Hi All,
I'll do my best to try and describe the issue I'm having with my
application.
I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.
Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).
Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.
When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.
After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.
What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables'
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.
I'm a bit lost really.
Any help is appreciated.
Thanks
Alex
******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn
@.UserID int,
@.LoggedIn bit = 0
AS
SET NOCOUNT ON
UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @.LoggedIn
WHERE UserID = @.UserID
*********************************Hi Alex,
Are you ending properly transactions?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to
> the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login
> class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the
> mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>|||Hi
Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Regards
Mike
"Alex Stevens" wrote:
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
>|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
> > the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
> > class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
> > mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> > will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
>|||As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.
The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.
In query analyzer, Select statements work fine, but updates don't.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).
I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.
How can I check to see if a process is blocking the UPDATE process'
Thanks
Alex
******Stored Proc*******
ALTER PROC proc_Get_User
---
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
--
-- Used in Classes:
--
---
@.UserID int = Null,
@.UserName varChar(20) = Null
AS
BEGIN
SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@.UserID IS NULL OR UserID = @.UserID) OR
(@.UserName IS NULL OR UserName = @.UserName)
ORDER BY UserName
END
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> Hi
> Timeouts are caused by SQL not getting it's work finished in time. This
> indicates a blocking or performance issue.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
> Regards
> Mike
> "Alex Stevens" wrote:
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
> >sql

Intermittent slow performing SP

Hello,
I have a stored proc that has started behaving strangely a few ws
after upgrading to SQL Server 2005. This problem cannot be replicated
in the test environment.
The stored proc responds within 1 second in the Production environment
until it slows down (not sure why) and takes about 2 minutes to run
when called by the application. The same proc in production contines
to run in less than a second when called from SQL Server Query
Analyzer. When I run an Alter Proc and run the script for altering
the proc (without ANY change in it), response gets back to what it
used to be - <1 sec when called by the application. Does this have
something to do with the optimizer and query plan reuse? If yes - what
causes the sudden change - for SP performance to be great part of the
day and then degrade at some point (and that too only when called by
the app, when it works fine in Query Analyzer - and only this SP -
since the rest of the app works fine)?
Indexes are defragged every night, statistics are updated with full
scan every night.
I plan to investigate this further but thought to check first so I
needn't reinvent a wheel that already exists. Thanks much.This is not that easy to answer. You've already ruled out the obvious one
of stat's not up to date.
Things to check next are....
1. what about the inputs? do they vary wildly?
2. the content of the procedure. Does it have several different execution
paths?
Regards
Colin Dawson
www.cjdawson.com
<smithabreddy@.gmail.com> wrote in message
news:1146338367.532365.141660@.g10g2000cwb.googlegroups.com...
> Hello,
> I have a stored proc that has started behaving strangely a few ws
> after upgrading to SQL Server 2005. This problem cannot be replicated
> in the test environment.
> The stored proc responds within 1 second in the Production environment
> until it slows down (not sure why) and takes about 2 minutes to run
> when called by the application. The same proc in production contines
> to run in less than a second when called from SQL Server Query
> Analyzer. When I run an Alter Proc and run the script for altering
> the proc (without ANY change in it), response gets back to what it
> used to be - <1 sec when called by the application. Does this have
> something to do with the optimizer and query plan reuse? If yes - what
> causes the sudden change - for SP performance to be great part of the
> day and then degrade at some point (and that too only when called by
> the app, when it works fine in Query Analyzer - and only this SP -
> since the rest of the app works fine)?
> Indexes are defragged every night, statistics are updated with full
> scan every night.
> I plan to investigate this further but thought to check first so I
> needn't reinvent a wheel that already exists. Thanks much.
>|||More than likely the plan for the sp is getting recompiled at times during
the day. This can be for many reasons. But the first time the sp is run
after it is marked for recompile it will create the plan based on the actual
values of the parameters passed in. If at some point you execute the sp at
one of those times when it recompiles and pass in values such that a table
scan is best it will continue to use a table scan until the next time it
recompiles. So if the selectivity of that value passed in was such that it
would return lots of rows you may not get the optimal plan for the majority
of the values you pass in after that. Running the sp in QA will generate a
new plan since it is most likely a different batch match than how it is
called from the app. If it is slow again try running sp_recompile for that
sp and see if that helps. If so then you are pretty much assured this is
the cause.
Andrew J. Kelly SQL MVP
<smithabreddy@.gmail.com> wrote in message
news:1146338367.532365.141660@.g10g2000cwb.googlegroups.com...
> Hello,
> I have a stored proc that has started behaving strangely a few ws
> after upgrading to SQL Server 2005. This problem cannot be replicated
> in the test environment.
> The stored proc responds within 1 second in the Production environment
> until it slows down (not sure why) and takes about 2 minutes to run
> when called by the application. The same proc in production contines
> to run in less than a second when called from SQL Server Query
> Analyzer. When I run an Alter Proc and run the script for altering
> the proc (without ANY change in it), response gets back to what it
> used to be - <1 sec when called by the application. Does this have
> something to do with the optimizer and query plan reuse? If yes - what
> causes the sudden change - for SP performance to be great part of the
> day and then degrade at some point (and that too only when called by
> the app, when it works fine in Query Analyzer - and only this SP -
> since the rest of the app works fine)?
> Indexes are defragged every night, statistics are updated with full
> scan every night.
> I plan to investigate this further but thought to check first so I
> needn't reinvent a wheel that already exists. Thanks much.
>|||I will try sp_recompile the next time this happens and update this
post. Thank you for your prompt responses.|||(smithabreddy@.gmail.com) writes:
> I will try sp_recompile the next time this happens and update this
> post. Thank you for your prompt responses.
Also, compare query plan before and after.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by "Also, compare query plan before and after. "
I ran profiler while the problem was occuring and although it captured
stats and showplantext for some of the other things happening at that
time, I can't find that information for the SPID which was executintg
this SP.
--
All I could see for this SP:
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
select PROCEDURE_CATALOG...
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
SET NO_BROWSETABLE ON
declare @.p1 int set @.p1=25 exec sp_prepare @.p1 output,N'@.P1 int
OUTPUT,@.P2 int,@.P3 varchar(25),@.P4 bit',N' EXEC @.P1=app.usp_proc
@.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4 ',1 select @.p1
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
if @.ModifyType = "create"
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
SET FMTONLY ON exec sp_execute 25,0,0,' ',0 SET FMTONLY OFF
set fmtonly off
exec sp_unprepare 25
SET NO_BROWSETABLE OFF
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
SET NOCOUNT ON
CREATE TABLE #tmpPTID (ProjectTemplateID int)
if @.ModifyType = "create"
NULL
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
Also when I ran the SP from QA today, I ran DBCC FreeProccache and ran
it as exec app.usp_proc. Also ran it using the verbage captured via
Profiler (again after DBCC FreeProccache):
exec sp_prepare...
EXEC app.usp_proc...
They generated exactly the same stats and execution plan...|||NNTP-Posting-Host: gw-uu-virtual.levonline.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: black.telenor.se 1146509970 22605 217.70.32.2 (1 May 2006 18:59:30
GMT)
X-Complaints-To: postmaster@.telenordia.se
NNTP-Posting-Date: Mon, 1 May 2006 18:59:30 +0000 (UTC)
User-Agent: Xnews/2006.03.07 Mime-proxy/2.1.c.0 (Win32)
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed00.sul.t-online.de
!t-online.de!130.59.10.21.MISMATCH!kanaga.switch.ch!switch.ch!newsfeed2.fune
t.fi!newsfeed1.funet.fi!newsfeeds.funet.fi!newsfeed1.telenor.se!news.telenor
.se!not-for-mail
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:600229
(smithabreddy@.gmail.com) writes:
> Not sure what you mean by "Also, compare query plan before and after. "
I meant that you should compare the query plan when the procedure appears
to be slow, and after you have used sp_recompile or whatever to force a
new plan.

> I ran profiler while the problem was occuring and although it captured
> stats and showplantext for some of the other things happening at that
> time, I can't find that information for the SPID which was executintg
> this SP.
Did you filter on this spid? If you include too many events, the
Profiler will have to throw events away. (You can see this as a special
event in Profiler.)
But I hoped it would be easier to see this from QA - or it is not.
QA runs with SET ARITHABORT ON by default, but this is not the case
for an ASP client, and you get different plans because of this setting.
So capturing plans in Profiler may be better after all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP slowed down again. I ran sp_recompile for the SP (did not have
a profiler template set up yet and it was too much of a fire-fighting
situation to do that right away) and this fixed performance
immediately. The page refreshed almost instantaneously instead of
taking the 2-4 minutes it was taking just before running sp_recompile.
So what is the solution for such a stored proc? Run Sp_Recompile
against it every so often?
There are two queries in this SP. The first one is an Insert into a
temp table (and this statement gets recompiled every time the SP is
run) and the second is a SELECT.
Should I
1) add a recompile hint to this SELECT query
2) set the SP to recompile everytime
3) run a job with sp_recompile <storedProc> every half hour
I realize I will need to test whatever solution...but would one of the
above be inherently better than the others or am I missing something
else that can be done? I suspect we have several such procs in the
database which may also need some help before too long.
P.S. I have created a Profiler template to capture events related to a
specific object ID - I will use this to perform a short capture the
next time I see this latency. This should help me figure out what
causes the bottleneck and if it can be eliminated.|||(smithabreddy@.gmail.com) writes:
> There are two queries in this SP. The first one is an Insert into a
> temp table (and this statement gets recompiled every time the SP is
> run) and the second is a SELECT.
Do you have any idea of what amount of data that goes into the temp table?
Maybe you get more consistent performance with a table variable, as table
variables do not cause recompilations?
Or would it be possible to integrate the query that fills the temp table
into the final SELECT?

> Should I
> 1) add a recompile hint to this SELECT query
> 2) set the SP to recompile everytime
> 3) run a job with sp_recompile <storedProc> every half hour
Of these I would actually lean towards the latter. Compilation can be costly
too. Then again, a recompilation hint on the SELECT query is the prettiest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP I mentioned above has been behaving well the last couple of days
- haven't had to recompile or do anything so I'll let it be until I
need to touch it again.
I have ANOTHER SP (:\) which is giving me some problems. In the PROD
database running this PROC with one set of parameters causes it finish
executing in under 1 second. Another set of parameters causes the
query to take close to 6 minutes to execute.
With the problem set of parameters, if I run against:
- Prod Server:
PROD database: 6 minutes
- Development Server:
DB1: 0 seconds
DB2: 6 Minutes
DB3 (Copy of PROD from yesterday): 0 Seconds
All of the databases have the same data. They have the same defrag,
update usage and update stats job run on them. On the DEV server, I've
run DBCC FreeProccache and recompiled the proc before running it in
each database.
--
I've narrowed down the latency to one section of the SP. This set of
parameters causes this one section of the SP to run and this section
consists of:
IF @.maxrows>0
SET ROWCOUNT @.maxrows
INSERT INTO...SELECT
.
.
.
SET ROWCOUNT 0
--
This runs fine in DB1 and DB3 but not in PROD and DB2. The execution
plan is different in for this query and two more nested loops have been
added in the inefficient query plan, plus a Table Spool/Lazy Spool step
for which rowcount = 1.6 million.
If I remove the SET Rowcount - the SP executes within 1 second.
Replacing SET Rowcount with TOP also causes the query to take between 5
and 6 minutes.
Why the different execution plans for the same set of parameters? They
are different copies of the same database.
What causes the Table Spool/Lazy Spool to come in to play?
(Adding a recompile/force order query hint does not make a change.)

intermitent connection failure

We have written a thread to monitor whether the database connection is still
alive or not.
This monitoring thread executes a stored procedure, sp_TestDB, with ADO
_CommandPtr every 3 seconds.
Most of the time we have no problems executing it. However, we receive the
following error randomly:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(WrapperWrite()). Msg:Unspecified error
We are convinced that the machine where the database server is installed is
definitely alive as we ping it all the time and there is no request time out
.
We want to know what other problems could cause this error to happen randoml
y?
The development language we use is Visual C++.
The database server we use is :
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
ScottHi Scott,
Since this is an unspecified error on the client side, In this case, please
try to use SQL profiler to start a trace to see what is actually happening
on the server. The trace log might show more information on this error.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."|||Excuse me, but I have the same problem, could you tell me how can I fix ?
I will appreciate it.
Thanks
Bruno Martinez
MCDBA
BMartinez@.cosapisoft.com.pe
"scottrm" <scottrm@.newsgroup.nospam> escribi en el mensaje
news:AD41E055-5B53-4015-B73E-88086A949CE4@.microsoft.com...
> We have written a thread to monitor whether the database connection is
> still
> alive or not.
> This monitoring thread executes a stored procedure, sp_TestDB, with ADO
> _CommandPtr every 3 seconds.
> Most of the time we have no problems executing it. However, we receive the
> following error randomly:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (WrapperWrite()). Msg:Unspecified error
> We are convinced that the machine where the database server is installed
> is
> definitely alive as we ping it all the time and there is no request time
> out.
> We want to know what other problems could cause this error to happen
> randomly?
> The development language we use is Visual C++.
> The database server we use is :
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>
> --
> Scott

intermitent connection failure

We have written a thread to monitor whether the database connection is still
alive or not.
This monitoring thread executes a stored procedure, sp_TestDB, with ADO
_CommandPtr every 3 seconds.
Most of the time we have no problems executing it. However, we receive the
following error randomly:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(WrapperWrite()). Msg:Unspecified error
We are convinced that the machine where the database server is installed is
definitely alive as we ping it all the time and there is no request time out.
We want to know what other problems could cause this error to happen randomly?
The development language we use is Visual C++.
The database server we use is :
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Scott
Hi Scott,
Since this is an unspecified error on the client side, In this case, please
try to use SQL profiler to start a trace to see what is actually happening
on the server. The trace log might show more information on this error.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Excuse me, but I have the same problem, could you tell me how can I fix ?
I will appreciate it.
Thanks
Bruno Martinez
MCDBA
BMartinez@.cosapisoft.com.pe
"scottrm" <scottrm@.newsgroup.nospam> escribi en el mensaje
news:AD41E055-5B53-4015-B73E-88086A949CE4@.microsoft.com...
> We have written a thread to monitor whether the database connection is
> still
> alive or not.
> This monitoring thread executes a stored procedure, sp_TestDB, with ADO
> _CommandPtr every 3 seconds.
> Most of the time we have no problems executing it. However, we receive the
> following error randomly:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (WrapperWrite()). Msg:Unspecified error
> We are convinced that the machine where the database server is installed
> is
> definitely alive as we ping it all the time and there is no request time
> out.
> We want to know what other problems could cause this error to happen
> randomly?
> The development language we use is Visual C++.
> The database server we use is :
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>
> --
> Scott
sql

Interfacing SQL Server with Access...

I have a web based SQL Server application that I need to modify to include data that is currently stored in an Access mdb. The mdb file and SQL Server are running on the same box. The data that is on the Access system cannot be migrated to SQL Server since it is a commercial package. But in our web application we want to query this data (and that in the SQL Server database) and present both current information to the users. We don't need to edit the Access data. Just view it and combine it with data we now have in SQL Server.

I was thinking I would build views or stored procedures that would pull info from both the SQL Server tables and the Access tables. But I'm not sure how efficient this is. One issue is that the users are remote, meaning many will not be in the same building that the SQL Server box is located at.

What is the best way to accomplish this? Any ideas? I've thought about using DTS but maybe it is overkill.

Thanks for the help.I would not query the access data from a web app. It will choke and die once you get about 20 connectins to it at a time. If you can live with the access data not being real time I would setup a job that fires daily or hourly that pulls the data into sql server daily or hourly or every 15 minutes. Or if you have the development manpower, redevelop the Access app to use sql as the datasource with an Access Data Project. Access as a backend to a web app is a recipe for disaster. Seen it before.|||Thanks for the advice Sean. Seeing as we cannot redevelop the backend for SQL Server, our only option appears to be batch updating of data into our system from Access on a scheduled basis. Would we use something like DTS for this? What can we do in SQL Server to facilitate the transfer of data from Access to SQL Server?

Thanks Sean.|||Use a SQL Server Agent job that fires a DTS job.|||Use a SQL Server Agent job that fires a DTS job.

Thanks. Will do.sql

Interface-less SMO?

One of the typical uses of DMO was to instantiate COM objects via t-sql either via stored procedures or ad-hoc submissions through Query Anaylzer/OSQL/ISQL. This allows me to construct helper scripts that have access to objects outside the SQL Server process space, and I don't have to create any application (console or gui) to do what I need.

It seems that SMO is not meant to be 'interface-less' as we could do with DMO, is this true? If this is the case, can we plan on either SMO being able to instantiate objects without an interface, or can we depend on DMO hanging around for a little while longer, while SMO "ramps up"?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

The main reason I prefer DMO is that it's much easier to encapsulate it into t-sql and have it run from within sql server (job/sp/batchfile). I don't want to have to start writing applications to do what I used to be able to do with DMO.

As an example, I have an sp that gets executed via job; the sp uses DMO in it's body to determine the drive space information on the server and the job emails the resultset as an attachment. Very easy and tidy, one job and one sp.

Now that DMO is being deprecated, I have to get rid of my sp code and create an application, or invoke a powershell script instead of just running an sp and emailing the results as a text file. Can I do this same exact operation with SMO, and not require an application interface?

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

It is "tough" to re-write in TSQL what SMO *already* have. We (DBAs with large number of server / databases) used DMO out-of-the box and yes sp_OA* to automate "EASILY" across servers. Now you are asking to deploy PowerShell (another add-on) in order to use SMO from TSQL. Not easy to deploy it all over the place.

|||

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

Neither SMO nor DMO was designed to be called inside the server, there is at least one memory leak in DMO that can not be fixed and there are lots of threading issues. I strongly encourage you not to do it until there is a version desiged to be called inside the server.

|||

Euan Garden wrote:

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I have to side with Noeld still on this. Most DBA's are aware that there are potential issues with using the sp_OA* procedures internally. However, most of us are not creating large DMO objects internally. Most of us are going after configuration values (like, say, BackupDirectory) which is extremely difficult to get to via t-sql without DMO (it can be done, but it's a LOT more code). Myself, I've been using a custom set of routines that I've written over the years on several hundred servers and only once have I had an issue with DMO causing an error on the server.

Being able to query for configuration values internally means that I only have to deploy my code to the server and it is 'self-contained' at that point. Why not use what we run (SQL Servers) to get the information we need? Why provide the sp_OA* procedures in the first place if they weren't meant to be used (just being rhetorical)?

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

sp_Oa was provided as a technology solution and it still provides a solution today, thats not to say that I would recomend it. A couple of other examples, SQLMail, in its time was a really cool feature, but on reflection calling MAPI(a non thread safe client focussed API) from inside an Extended Stored Procedure is not going to increase the reliability of your server. SQL Server still supports XPs, I would always look to do something in SQLCLR before an XP however.

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interface-less SMO?

One of the typical uses of DMO was to instantiate COM objects via t-sql either via stored procedures or ad-hoc submissions through Query Anaylzer/OSQL/ISQL. This allows me to construct helper scripts that have access to objects outside the SQL Server process space, and I don't have to create any application (console or gui) to do what I need.

It seems that SMO is not meant to be 'interface-less' as we could do with DMO, is this true? If this is the case, can we plan on either SMO being able to instantiate objects without an interface, or can we depend on DMO hanging around for a little while longer, while SMO "ramps up"?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

The main reason I prefer DMO is that it's much easier to encapsulate it into t-sql and have it run from within sql server (job/sp/batchfile). I don't want to have to start writing applications to do what I used to be able to do with DMO.

As an example, I have an sp that gets executed via job; the sp uses DMO in it's body to determine the drive space information on the server and the job emails the resultset as an attachment. Very easy and tidy, one job and one sp.

Now that DMO is being deprecated, I have to get rid of my sp code and create an application, or invoke a powershell script instead of just running an sp and emailing the results as a text file. Can I do this same exact operation with SMO, and not require an application interface?

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

It is "tough" to re-write in TSQL what SMO *already* have. We (DBAs with large number of server / databases) used DMO out-of-the box and yes sp_OA* to automate "EASILY" across servers. Now you are asking to deploy PowerShell (another add-on) in order to use SMO from TSQL. Not easy to deploy it all over the place.

|||

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

Neither SMO nor DMO was designed to be called inside the server, there is at least one memory leak in DMO that can not be fixed and there are lots of threading issues. I strongly encourage you not to do it until there is a version desiged to be called inside the server.

|||

Euan Garden wrote:

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I have to side with Noeld still on this. Most DBA's are aware that there are potential issues with using the sp_OA* procedures internally. However, most of us are not creating large DMO objects internally. Most of us are going after configuration values (like, say, BackupDirectory) which is extremely difficult to get to via t-sql without DMO (it can be done, but it's a LOT more code). Myself, I've been using a custom set of routines that I've written over the years on several hundred servers and only once have I had an issue with DMO causing an error on the server.

Being able to query for configuration values internally means that I only have to deploy my code to the server and it is 'self-contained' at that point. Why not use what we run (SQL Servers) to get the information we need? Why provide the sp_OA* procedures in the first place if they weren't meant to be used (just being rhetorical)?

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

sp_Oa was provided as a technology solution and it still provides a solution today, thats not to say that I would recomend it. A couple of other examples, SQLMail, in its time was a really cool feature, but on reflection calling MAPI(a non thread safe client focussed API) from inside an Extended Stored Procedure is not going to increase the reliability of your server. SQL Server still supports XPs, I would always look to do something in SQLCLR before an XP however.

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interface-less SMO?

One of the typical uses of DMO was to instantiate COM objects via t-sql either via stored procedures or ad-hoc submissions through Query Anaylzer/OSQL/ISQL. This allows me to construct helper scripts that have access to objects outside the SQL Server process space, and I don't have to create any application (console or gui) to do what I need.

It seems that SMO is not meant to be 'interface-less' as we could do with DMO, is this true? If this is the case, can we plan on either SMO being able to instantiate objects without an interface, or can we depend on DMO hanging around for a little while longer, while SMO "ramps up"?

Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Some tutorials on using SMO with PowerShell...

http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/

Dan

|||

The main reason I prefer DMO is that it's much easier to encapsulate it into t-sql and have it run from within sql server (job/sp/batchfile). I don't want to have to start writing applications to do what I used to be able to do with DMO.

As an example, I have an sp that gets executed via job; the sp uses DMO in it's body to determine the drive space information on the server and the job emails the resultset as an attachment. Very easy and tidy, one job and one sp.

Now that DMO is being deprecated, I have to get rid of my sp code and create an application, or invoke a powershell script instead of just running an sp and emailing the results as a text file. Can I do this same exact operation with SMO, and not require an application interface?

It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.

|||

Jens K. Suessmeyer wrote:

You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...

|||

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

|||

Euan Garden wrote:

Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.

Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.

You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.

You are using that COM interface to call a large complex COM library whos primary function is to

Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.

If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.

It is "tough" to re-write in TSQL what SMO *already* have. We (DBAs with large number of server / databases) used DMO out-of-the box and yes sp_OA* to automate "EASILY" across servers. Now you are asking to deploy PowerShell (another add-on) in order to use SMO from TSQL. Not easy to deploy it all over the place.|||

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

Neither SMO nor DMO was designed to be called inside the server, there is at least one memory leak in DMO that can not be fixed and there are lots of threading issues. I strongly encourage you not to do it until there is a version desiged to be called inside the server.

|||

Euan Garden wrote:

Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).

I have to side with Noeld still on this. Most DBA's are aware that there are potential issues with using the sp_OA* procedures internally. However, most of us are not creating large DMO objects internally. Most of us are going after configuration values (like, say, BackupDirectory) which is extremely difficult to get to via t-sql without DMO (it can be done, but it's a LOT more code). Myself, I've been using a custom set of routines that I've written over the years on several hundred servers and only once have I had an issue with DMO causing an error on the server.

Being able to query for configuration values internally means that I only have to deploy my code to the server and it is 'self-contained' at that point. Why not use what we run (SQL Servers) to get the information we need? Why provide the sp_OA* procedures in the first place if they weren't meant to be used (just being rhetorical)?

I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.

|||

sp_Oa was provided as a technology solution and it still provides a solution today, thats not to say that I would recomend it. A couple of other examples, SQLMail, in its time was a really cool feature, but on reflection calling MAPI(a non thread safe client focussed API) from inside an Extended Stored Procedure is not going to increase the reliability of your server. SQL Server still supports XPs, I would always look to do something in SQLCLR before an XP however.

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

|||

Euan Garden wrote:

Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server

That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.

Interesting conumdrum.. question about filters...

Got a dataset coming from a stored procedure.

There is a parameter I must put on the report that filters the dataset.

the choices are "exclude" and "only", based on that I have to filter my dataset like

if they select exclude then I have to filter where "debitType <> 'Intercompany'"

if they select on;y then I have to filter where "debitType = 'Intercompany'"

Is this at all possible in the filter part?

It is easy as pie in Crystal record selector.

Hello,

Try this as your filter:

Expression: =cBool((Fields!debitType.Value <> "Intercompany" AND Parameters!Param1.Value = "Exclude") OR (Fields!debitType.Value = "Intercompany" AND Parameters!Param1.Value = "Only"))

Operator: =

Value: =cBool(True)

Hope this helps.

Jarret

|||

oh, that's good. didn't knwo you could use epxressions like that in the left side of the filter table.

Thanks alot.

Inter-database stored procedures and permissions

Hello all, this is my second post to this newsgroup. It's a question
about stored procedures and permissions and how these behave between
databases.

Here's the scenario. I have a database that stores information for a
system "A", and I have a different database on the same SQL server
that stores the login and other info "LOGIN". I write a stored
procedure in the "A" database that checks some tables in the "LOGIN"
database, let's call this "SP_A".

Additionally I have a user account that accesses all appropriate
stored procedures in "A" called "USER_A", and the same for the "LOGIN"
database, "USER_LOGIN".

Here's the part that raised my curiosity. I log into the server via
Query Analyzer using the "USER_A" account. I run "SP_A" which does a
join between some table in "A" and another table in "LOGIN". I give
"USER_A" execute permission on "SP_A", then I try to run "SP_A" and
get an error:

SELECT permission denied on object '(table in "LOGIN" database)',
database '(real name of "LOGIN")', owner 'dbo'

Huh? how come I need to assign additional select permissions in this
database if I'm not doing an actual select statement? I'm not even
dynamically running a select statement through an exec function. This
just struck me as odd, seeing as how I never explicitly set SELECT
permission on any table in "A" for "USER_A", yet my stored procedure
works, but between databases I have to assign extra permissions for a
stored procedure "SP_A" access to the tables in "LOGIN".

Anyone able to explain this behavior? Because I'm at a loss and I've
only been doing this DB thing for about 2 years.

Thanks in advance, all.
-TJThe same login must own both databases in order for the ownership chain to
be unbroken for the dbo-owned objects. Additionally, if you are running SQL
2000 SP3, you need to enable cross-database chaining the both databases.

The script below illustrates how you can implement cross-database chaining
security.

USE A
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'A', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
--no object permissions are granted
GO

USE LOGIN
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'LOGIN', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
GRANT EXEC ON dbo.SP_A TO USER_A
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"TJ Olaes" <junk@.olaes.net> wrote in message
news:feab9d89.0312291525.4f1893a3@.posting.google.c om...
> Hello all, this is my second post to this newsgroup. It's a question
> about stored procedures and permissions and how these behave between
> databases.
> Here's the scenario. I have a database that stores information for a
> system "A", and I have a different database on the same SQL server
> that stores the login and other info "LOGIN". I write a stored
> procedure in the "A" database that checks some tables in the "LOGIN"
> database, let's call this "SP_A".
> Additionally I have a user account that accesses all appropriate
> stored procedures in "A" called "USER_A", and the same for the "LOGIN"
> database, "USER_LOGIN".
> Here's the part that raised my curiosity. I log into the server via
> Query Analyzer using the "USER_A" account. I run "SP_A" which does a
> join between some table in "A" and another table in "LOGIN". I give
> "USER_A" execute permission on "SP_A", then I try to run "SP_A" and
> get an error:
> SELECT permission denied on object '(table in "LOGIN" database)',
> database '(real name of "LOGIN")', owner 'dbo'
> Huh? how come I need to assign additional select permissions in this
> database if I'm not doing an actual select statement? I'm not even
> dynamically running a select statement through an exec function. This
> just struck me as odd, seeing as how I never explicitly set SELECT
> permission on any table in "A" for "USER_A", yet my stored procedure
> works, but between databases I have to assign extra permissions for a
> stored procedure "SP_A" access to the tables in "LOGIN".
> Anyone able to explain this behavior? Because I'm at a loss and I've
> only been doing this DB thing for about 2 years.
> Thanks in advance, all.
> -TJ|||Thank you for the prompt reply.

I checked the owners of all the tables, stored procedures, and the two
databases involved. All items seem to be under the ownership of "dbo", and
the owner of the two databases are the same, which is a windows account on
the system (COMPUTER\account). The dbo doesn't have a login on either
database, but I would think if the dbo of the two databases were the same
there should be no problem, right?

-TJ

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:sJ3Ib.14731$IM3.12960@.newsread3.news.atl.eart hlink.net...
> The same login must own both databases in order for the ownership chain to
> be unbroken for the dbo-owned objects. Additionally, if you are running
SQL
> 2000 SP3, you need to enable cross-database chaining the both databases.
> The script below illustrates how you can implement cross-database chaining
> security.
> USE A
> EXEC sp_changedbowner 'sa' -- or any common login
> EXEC sp_dboption 'A', 'db chaining', true --if SQL 2000 SP3
> EXEC sp_adduser 'USER_A'
> --no object permissions are granted
> GO
> USE LOGIN
> EXEC sp_changedbowner 'sa' -- or any common login
> EXEC sp_dboption 'LOGIN', 'db chaining', true --if SQL 2000 SP3
> EXEC sp_adduser 'USER_A'
> GRANT EXEC ON dbo.SP_A TO USER_A
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "TJ Olaes" <junk@.olaes.net> wrote in message
> news:feab9d89.0312291525.4f1893a3@.posting.google.c om...
> > Hello all, this is my second post to this newsgroup. It's a question
> > about stored procedures and permissions and how these behave between
> > databases.
> > Here's the scenario. I have a database that stores information for a
> > system "A", and I have a different database on the same SQL server
> > that stores the login and other info "LOGIN". I write a stored
> > procedure in the "A" database that checks some tables in the "LOGIN"
> > database, let's call this "SP_A".
> > Additionally I have a user account that accesses all appropriate
> > stored procedures in "A" called "USER_A", and the same for the "LOGIN"
> > database, "USER_LOGIN".
> > Here's the part that raised my curiosity. I log into the server via
> > Query Analyzer using the "USER_A" account. I run "SP_A" which does a
> > join between some table in "A" and another table in "LOGIN". I give
> > "USER_A" execute permission on "SP_A", then I try to run "SP_A" and
> > get an error:
> > SELECT permission denied on object '(table in "LOGIN" database)',
> > database '(real name of "LOGIN")', owner 'dbo'
> > Huh? how come I need to assign additional select permissions in this
> > database if I'm not doing an actual select statement? I'm not even
> > dynamically running a select statement through an exec function. This
> > just struck me as odd, seeing as how I never explicitly set SELECT
> > permission on any table in "A" for "USER_A", yet my stored procedure
> > works, but between databases I have to assign extra permissions for a
> > stored procedure "SP_A" access to the tables in "LOGIN".
> > Anyone able to explain this behavior? Because I'm at a loss and I've
> > only been doing this DB thing for about 2 years.
> > Thanks in advance, all.
> > -TJ|||"Thomas Joseph Olaes" <tjTAKEOUT@.ALLTHEshowfaxCAPITALLETTERS.com> wrote in
message news:1n4Ib.4695$5M.99324@.dfw-read.news.verio.net...
> Thank you for the prompt reply.
> I checked the owners of all the tables, stored procedures, and the two
> databases involved. All items seem to be under the ownership of "dbo", and
> the owner of the two databases are the same, which is a windows account on
> the system (COMPUTER\account). The dbo doesn't have a login on either
> database, but I would think if the dbo of the two databases were the same
> there should be no problem, right?
> -TJ

Yes, the 'dbo' user ownership chain is unbroken if the database owners are
the same. The following should return 'COMPUTER\account' as the
owner/login. Don't forget that cross-database chaining needs to be enabled
too.

sp_helpdb 'A'
sp_helpdb 'LOGIN'
GO
use A
sp_helpuser 'dbo'
GO
use LOGIN
sp_helpuser 'dbo'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP|||Dan Guzman wrote:
> "Thomas Joseph Olaes" <tjTAKEOUT@.ALLTHEshowfaxCAPITALLETTERS.com>
wrote in
> message news:1n4Ib.4695$5M.99324@.dfw-read.news.verio.net...
> > Thank you for the prompt reply.
> > I checked the owners of all the tables, stored procedures, and the
two
> > databases involved. All items seem to be under the ownership of
"dbo", and
> > the owner of the two databases are the same, which is a windows
account on
> > the system (COMPUTER\account). The dbo doesn't have a login on
either
> > database, but I would think if the dbo of the two databases were
the same
> > there should be no problem, right?
> > -TJ
> Yes, the 'dbo' user ownership chain is unbroken if the database
owners are
> the same. The following should return 'COMPUTER\account' as the
> owner/login. Don't forget that cross-database chaining needs to be
enabled
> too.
>
> sp_helpdb 'A'
> sp_helpdb 'LOGIN'
> GO
> use A
> sp_helpuser 'dbo'
> GO
> use LOGIN
> sp_helpuser 'dbo'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

Solved my problem. Thanks.

Intercept Result

Hi,

I am trying to write a system (Stored Proc, View, CLR Proc, ?), where if you query a specific table and it returns data from only 1 column for the submitted SELECT statement then substitute data obtained from an external web service.

Does anyone have any suggestions about how best to implement this? I have searched through the BOL but nothing directly deals with what I want to do.

Thanks,

Blair

P.S. I am using SQL SERVER 2005 ENT Edition with everything available.

Hi,

I'm not very clear with your question.

For substituting a result, you can use CASE within your SELECT statement.

Assuming Table1 table with Col1 as column, and a scalar valued function CLRFunction() which will call a web service,

SELECT CASE Col1
WHEN 'abc' THEN CLRFunction(1)
WHEN 'def' THEN CLRFunction(2)
END 'Col1'
FROM Table1

Here 'abc' and 'def' are the values stored in Col1 of Table1

Hope this answers for your query, else please explain your requirement with some more detail.

Regards

Babu

|||

Thanks that is exactly what I was looking for. I now know what to do.

blair

|||

Hello...

I think calling a webservie inside a SP is not a very good behavior. You should write your SPs so that they finish as fast as possible. If you try to access an outside resource you never know how long it will take(and a webservice is even worse). You should consider moving this logic into your application. Also you need to access external resources in that function, so you have to declare it as "unsafe" which will also have some implications on security...

Also it will be more complicated to make your app responsive while your code is waiting on the SQL Server. Calling a webservice async is very easy... Calling SQL is a little more work (but still no big deal)

Interactive Debugging of Stored Procedure

In VS 2005 with SQL Server Express: How do I debug a stored procedure that is called from a object datasource control while the asp.net code is running?

Thanks

You may need to PRINT debugging information in the stored procedure, or use SQL Profiler to capture a trace when executing the stored procedure.

interaction Stored Procedure - FTP-server

hi all,

this is my question:

can a stored procedure interact with a FTP-server that has nothing to do with the database the stored procedure is running from?

I want my stored procedure to delete records in my database AND my FTP-server in the same time..., so also uploaded files which have been uploaded to a FTP-server during the same session when data has been written to my database...?

this is my SP for deleting records in my database:

...

DELETE FROM dbo.tblUploadsIntern WHERE DATEDIFF(dd,GETDATE(), tblUploadsIntern.uplZipfileDateTimeOffline) > 7

END

GO

the code for deleting files on FTP-server works also...

thanks in advance...

I would not load my SQL Server with housekeeping like this.

However, you could create a SQL Agent Job that would take care of this for you.

But, really, if it has nothing to do with SQL Server, why not make a Windows Scheduler Job to do the clean up?

Inter Database Security

I have a stored procedure db1.dbo.sp1
this Stored Procedure grabs data from another database. Say db2.dbo.tbl1

If i call the qry1.
how does the security from db2 come in to play? if at all?

For example, If I am SQL Server Authenticated, for DB1 but not for DB2 will the SP fail. etc.If you have security to db1 but not db2, and you run it as yourself, it will fail.

HTH|||Tnx for the info. I picked up some additional tips and was able to confirm your suggestion on our sql servers over here.

Is there anyway to use NT Authentication in the first db. Then from within a stored procedure (in the 1st database), access the 2nd database using the sa account?

I.E. pass a sa login request to the 2nd database.