Determine what procedure/function called a stored procedure

Is there a way within a proc to determine what object called it?
ex:
DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means to
get the context (name or id) of the procedure or function that called it?
Thanks!I don't think so. I think all you get is @.@.NESTLEVEL
However you can set sysprocesses.CONTEXT_INFO
I've used it to bypass triggers before.
http://www.sqlservercentral.com/col...ingtriggers.asp
In this case it's much the same as updating an @.@.spid based table.
Paul
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
> Is there a way within a proc to determine what object called it?
> ex:
> DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means
> to
> get the context (name or id) of the procedure or function that called it?
> Thanks!|||nrgh...that is what I was afraid of. :)
I have to modify some existing procedures and the original implementation
has led to some issues in logging information. I was hoping to avoid
changing about 1000 procs to pass in the DBID that it was executed on. *sig
h*
Thanks for the confirmation.
"Paul Cahill" wrote:

> I don't think so. I think all you get is @.@.NESTLEVEL
> However you can set sysprocesses.CONTEXT_INFO
> I've used it to bypass triggers before.
> http://www.sqlservercentral.com/col...ingtriggers.asp
> In this case it's much the same as updating an @.@.spid based table.
> Paul
>
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
>
>|||The @.@.procid variable returns the procedure identifier of the current
procedure. Perhaps you can add a parameter to each SP @.called_by and call
procedures like so:
exec @.proc2 @.called_by = @.@.procid
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
> Is there a way within a proc to determine what object called it?
> ex:
> DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means
> to
> get the context (name or id) of the procedure or function that called it?
> Thanks!|||There's been a few times I'd like to be able to get at the stack too.
Anyone out there know a trick?
If these were remote calls I think it could be done by setting up the linked
servers such that rpc's always connect as a particular user.
If each link is given a different username can you then tell which server
remotely called the proc.
But you are executing all on the same machine just from different databases.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DA1631A4-10C6-4037-97F1-C5D58327B16A@.microsoft.com...
> nrgh...that is what I was afraid of. :)
> I have to modify some existing procedures and the original implementation
> has led to some issues in logging information. I was hoping to avoid
> changing about 1000 procs to pass in the DBID that it was executed on.
> *sigh*
> Thanks for the confirmation.
> "Paul Cahill" wrote:
>|||The procID is being passed into the procedure, but it will need to pass in
the DBID as well. There are multiple DBs on the server and it recently came
to light that the logic in the proc to determine which DB the proc was
executed on is incorrect (as the procIds can be the same in multiple dbs).
: )
Thx
"JT" wrote:

> The @.@.procid variable returns the procedure identifier of the current
> procedure. Perhaps you can add a parameter to each SP @.called_by and call
> procedures like so:
> exec @.proc2 @.called_by = @.@.procid
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
>
>|||If you are wanting to peform procedure call stack tracing for debugging
purposes, then look into using SQL Profiler for this. It would require no
programming changes.
http://msdn.microsoft.com/library/d...nEventClues.asp
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:4C79AB11-F7D1-40DD-BB52-0063E138D8C4@.microsoft.com...
> The procID is being passed into the procedure, but it will need to pass in
> the DBID as well. There are multiple DBs on the server and it recently
> came
> to light that the logic in the proc to determine which DB the proc was
> executed on is incorrect (as the procIds can be the same in multiple dbs).
> : )
> Thx
> "JT" wrote:
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
Randy
Randy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com ...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy
|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com ...
>
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
RandyRandy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> > Hi,
> >
> > I am writing a stored proc that has to change to a different database
> > inline. Example:
> >
> >
> > use database2
> > go
> >
> >
> > What I need to do first is find out what the name of the current
> > database is before I switch to database2 so I can switch back.
> >
> >
> > How do I determine what the current database is?
> >
> >
> > Thanks
> >
> > Randy
>
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
RandyRandy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
>
>

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine slow running reports

Take a look at the Execution Log topics in Books Online. All of the informaiton you want is stored in the ReportServer database. There is even a DTS package to extract the data and a set of reports which show you statistics.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
--Original Message--
From: Randy Knight
Posted At: Thursday, October 06, 2005 2:53 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Determine slow running reports
Subject: Re: Determine slow running reports
That would be great if I had control of the reports but as I said, the reports are published by analysts from all over the company. I just wondered if RS put some statistics or anything like that in the ReportServer database. It would seem to be useful ... how many times report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)Just what I was looking for. Thanks.

Determine security access to stored procedure through ASP

I have an ASP application running in a MS Windows Server 2003 computer
joined to a Windows 2000 Active Directory domain.
Different users have different roles, and the security in the SQL
database is based on Active Directory security groups (SQL server is
configured for Windows security and not SQL server security).
I would like to make certain ASP page show an icon depending on whether
the user has EXECUTE permission on a particular stored procedure.
Example: User X@.domain.com is a member of the Active Directory
security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
in of a SQL user called "App Admins", and App Admins have been granted
EXECUTE permission to the stored procedure spDeleteSomething. I want
the ASP to determine if X@.domain.com has the permission on
spDeleteSomething so an icon is displayed; in this case it should be
displayed.
I hope I made this clear, but if not, feel free to ask for more details.You can use the IS_MEMBER function to check to see if the current login is a
member of the App Admins group:
SELECT IS_MEMBER('domain.com\App Admins')
returns 1 if the current login is a member of the App Admins security group,
0 if they aren't.
"webJose" wrote:

> I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||In ASP.NET, you can determine if a web user is a member of an Active
Directory group or role without going through SQL Server.
How To: Use Role Manager in ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000013.asp
For example:
if (Roles.IsUserInRole("TestRole"))
{
Label1.Text = User.Identity.Name + " is in role TestRole";
}
else
{
Label1.Text = User.Identity.Name + " is NOT in role TestRole";
}
"webJose" <webJose@.gmail.com> wrote in message
news:1140194004.035980.226530@.g14g2000cwa.googlegroups.com...
>I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||JT: Thank you for your response. Although highly enlighting, I am not
using .NET (I know! I should be). :-)
Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
create user-defined functions to encapsulate this functionality. Now,
out of curiosity, is there a way to test for EXECUTE permissions on any
stored procedure like on the fly? For example, something like:
If CanExecute("spSomeSP") Then
Response.Write "You got it!"
End If
And CanExecute() would test somehow the permissions for the user ID on
the sp name passed as argument.|||You could query it from the sysprotects system table.
For SQL 2000:
IF EXISTS (
SELECT 1 FROM sysprotects
WHERE [id] = OBJECT_ID('yourproc')
AND [uid] = USER_ID()
AND [action] = 224
AND [protecttype] IN (204,205)
)
BEGIN
PRINT 'You have access'
END
For SQL 2005
IF EXISTS (
SELECT 1 FROM sys.database_permissions
WHERE [class] = 1
AND [major_id] = OBJECT_ID('yourproc')
AND [grantee_principal_id] = USER_ID()
AND [type] = 'EX'
AND [state] IN ('G','W')
)
BEGIN
PRINT 'You have access'
END
"webJose" wrote:

> JT: Thank you for your response. Although highly enlighting, I am not
> using .NET (I know! I should be). :-)
> Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> create user-defined functions to encapsulate this functionality. Now,
> out of curiosity, is there a way to test for EXECUTE permissions on any
> stored procedure like on the fly? For example, something like:
> If CanExecute("spSomeSP") Then
> Response.Write "You got it!"
> End If
> And CanExecute() would test somehow the permissions for the user ID on
> the sp name passed as argument.
>|||One caveat to this: the will only return that the user has access if the use
r
they map too was explicity given access to execute procedure. If their
permission is inherited from membership in a server or database role, my
script will not show them as having access.
If you are explicitly giving execute permission to each of the users, then
it will work.
"Mark Williams" wrote:
> You could query it from the sysprotects system table.
> For SQL 2000:
> IF EXISTS (
> SELECT 1 FROM sysprotects
> WHERE [id] = OBJECT_ID('yourproc')
> AND [uid] = USER_ID()
> AND [action] = 224
> AND [protecttype] IN (204,205)
> )
> BEGIN
> PRINT 'You have access'
> END
> For SQL 2005
> IF EXISTS (
> SELECT 1 FROM sys.database_permissions
> WHERE [class] = 1
> AND [major_id] = OBJECT_ID('yourproc')
> AND [grantee_principal_id] = USER_ID()
> AND [type] = 'EX'
> AND [state] IN ('G','W')
> )
> BEGIN
> PRINT 'You have access'
> END
> --
> "webJose" wrote:
>

Determine fastest query in Query Analyzer

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @.param or @.param = Null) filters in the WHERE
statement.

One query populates a #Temp table with the UniqueIDs from the results
of the SELECT query in the above example, then joins that #Temp table
to get the desired rows.

One query users EXEC sp_executesql @.sql, @.paramlist, @.param
in which the @.param has the dozen filters.

What I'm trying to determine is which is the fastest.

Each time I run the query in Query Analyzer it returns the same
recordset (duh!) but with much different Time Statistics.

Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

Any illumination on this is appreciated.
lqHi

You are looking at the client statistic! The topic "Query Window Statistics
Pane" in books online explains their values.

Time is a good indicator of performance, for instance if there are more
network round trips this should be noticed in the time taken. You may also
want to consider the number of reads/writes which may give some indication
of how well it will perform when the system in under a load. These can be
viewed using SQL profiler.

Expect the first time you run a query to take longer than subsequent times,
if your query is cached subsequent executions may be significantly faster.
Use DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to clear the procedure
cache and buffer pool.

John

"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1126976566.998344.13050@.g44g2000cwa.googlegro ups.com...
>I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.
> One query populates a #Temp table with the UniqueIDs from the results
> of the SELECT query in the above example, then joins that #Temp table
> to get the desired rows.
> One query users EXEC sp_executesql @.sql, @.paramlist, @.param
> in which the @.param has the dozen filters.
> What I'm trying to determine is which is the fastest.
> Each time I run the query in Query Analyzer it returns the same
> recordset (duh!) but with much different Time Statistics.
> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.
> Any illumination on this is appreciated.
> lq|||Have a look at showplan whick give you an idea what the database is
doing to resolve your queries. Determining the fastest method can be
difficult especially with changing volumes of data, add or remove an
index will effect the results (faster or slower) so experiment a bit.

Sorry I cannot be more help

duncan|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.

@.param = Null?

Remember that NULL is never equal to anything, not an even another NULL.
NULL is an unknown value, and two nulls may be two different values.
Use "@.param IS NULL" instead.

> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.

When I need to benchmark queries I usually do:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- Run query
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'It took ' + ltrim(str(@.tookms)) + ' ms.'

As John mentioned it is important to have the cache in mind. You can
do DBCC DROPCLEANBUFFERS to flush the cache, but don't this on a
production box! Often I'm lazy and run the queries several times, and
forget the first run, since that may include time reading from disk.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks. Yes, I use "Is Null". I wrote the question on the fly. I will
insert your @.tookms into my sprocs to see how they perform. That's a
great hint.
LQ

Detecting the network UserID

Hey guys,
I would like to determine the network id of the current user so that I can
pass that value to a stored procedure that the report references.
I tried add the following code in the Code tab of the Report Properties tab:
Function GetUserID() AS String
Return System.Environment.UserName
End Function
I then referenced this function as the default value for the @.vcUserId
argument that the stored procedure wants by assigning =Code.GetUserID() as
the Non-queried default value for the report parameter.
This approach works well when I preview the report in Visual Studio.
Unfortunately, it fails when I deploy the report to the server.
Can anyone offer some suggestions on how to approach this issue?
Thanks in advance,
-JimI'd guess that the reason this doesn't work is because when you run it
in VS.net the application is running as you or some other acceptable
local user
(http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
When it runs online it probably can't execute or it returns the
ASP.NET worker process's user or something similar and not useful. I
know for ASP.NET you can call HttpContext.User.Idenity.Name (or
something similar, I'm working from memory mostly) to see who the
logged in user is, this might work better in report services. Or there
might be a separate way to get the current user from the report manager
that I'm not aware of.|||Use the global variable User!UserID.value
You can get to this with the expression builder. It returns domain\username,
if you don't want the domain then you will need to strip it off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terence Tirella" <ttirella@.literate.com> wrote in message
news:1141851303.988200.237700@.u72g2000cwu.googlegroups.com...
> I'd guess that the reason this doesn't work is because when you run it
> in VS.net the application is running as you or some other acceptable
> local user
> (http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
> When it runs online it probably can't execute or it returns the
> ASP.NET worker process's user or something similar and not useful. I
> know for ASP.NET you can call HttpContext.User.Idenity.Name (or
> something similar, I'm working from memory mostly) to see who the
> logged in user is, this might work better in report services. Or there
> might be a separate way to get the current user from the report manager
> that I'm not aware of.
>sql

Detecting maximum database size

I need a way of programmatically detecting the maximum size of a database
when being used under MSDE. The stored procedure sp_helpdb reports the
maxsize value as being 'unlimited', which in the case of MSDE is untrue.
I could look at the product name and infer the max size *but* this would be
my last alternative since I would like it also to work for say the upcoming
2005 version, that I understand has had its max db size increased to 4gigs.
Regards
Lee
There is no function for that, if you want to know the limit for the MSDE
you have to use that via the Selection of the prduct name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need a way of programmatically detecting the maximum size of a database
> when being used under MSDE. The stored procedure sp_helpdb reports the
> maxsize value as being 'unlimited', which in the case of MSDE is untrue.
> I could look at the product name and infer the max size *but* this would
> be
> my last alternative since I would like it also to work for say the
> upcoming
> 2005 version, that I understand has had its max db size increased to
> 4gigs.
> Regards
> Lee
>
|||Thanks for the response, i thought that might be the case.
Regards
Lee
"Jens Smeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
message news:%23F68UpigFHA.1612@.TK2MSFTNGP12.phx.gbl...
> There is no function for that, if you want to know the limit for the MSDE
> you have to use that via the Selection of the prduct name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
> news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>

Detect Stored Procedure that refer to tables that do not exist

It is possible to construct a query that will list all stored procedures
that refer to tables that no longer exist in the database?
Thanks,
ChrisHi
Execute each SP with "SET NOEXEC ON" set.
From BOL
"The execution of statements in SQL Server consists of two phases:
compilation and execution. This setting is useful for having SQL Server
validate the syntax and object names in Transact-SQL code when executing. It
is also useful for debugging statements that would usually be part of a
larger batch of statements"
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:eFzSEi3lFHA.3900@.TK2MSFTNGP09.phx.gbl...
> It is possible to construct a query that will list all stored procedures
> that refer to tables that no longer exist in the database?
>
> Thanks,
> Chris
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Execute each SP with "SET NOEXEC ON" set.
> From BOL
> "The execution of statements in SQL Server consists of two phases:
> compilation and execution. This setting is useful for having SQL
> Server validate the syntax and object names in Transact-SQL code when
> executing. It is also useful for debugging statements that would
> usually be part of a larger batch of statements"
> Regards
Mike,
NOEXEC doesn't seem to work for me. For some reason, it doesn't detect
the drop of the table in my example. However, when I tried using SET
FMTONLY ON, it worked and does not depend on any branching in the
procedure itself that would prevent access to the underlying missing
table.
create table abc123 (co1l int)
go
drop proc abc123test
go
create proc abc123test @.b bit
as
begin
if @.b = 0
Select co1l from abc123
else
Select id from sysobjects
end
go
exec abc123test 1
exec abc123test 0
drop table abc123
go
set fmtonly on
exec abc123test 1
exec abc123test 0
set fmtonly off
drop proc abc123test
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks,
Is it possible to perform the checking at the time the stored procedure is
created. The problem I have got is that it would take a long time to test a
thousand+ number of stored procedures using exec, supplying different
parameters to each one etc. So I thought it would be easier to generate a
script that attempts to drop and then recreate the stored procedures and
stop when it fails because the table does not exist.
So is it possible to perform the check when I actually do the CREATE PROC...
create proc abc123test @.b bit
as
Select co1l from abc123
go
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
> NOEXEC doesn't seem to work for me. For some reason, it doesn't detect the
> drop of the table in my example. However, when I tried using SET FMTONLY
> ON, it worked and does not depend on any branching in the procedure itself
> that would prevent access to the underlying missing table.
> create table abc123 (co1l int)
> go
> drop proc abc123test
> go
> create proc abc123test @.b bit
> as
> begin
> if @.b = 0
> Select co1l from abc123
> else
> Select id from sysobjects
> end
> go
> exec abc123test 1
> exec abc123test 0
> drop table abc123
> go
> set fmtonly on
> exec abc123test 1
> exec abc123test 0
> set fmtonly off
> drop proc abc123test
> go
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks,
> Is it possible to perform the checking at the time the stored
> procedure is created. The problem I have got is that it would take a
> long time to test a thousand+ number of stored procedures using exec,
> supplying different parameters to each one etc. So I thought it
> would be easier to generate a script that attempts to drop and then
> recreate the stored procedures and stop when it fails because the
> table does not exist.
> So is it possible to perform the check when I actually do the CREATE
> PROC...
> create proc abc123test @.b bit
> as
> Select co1l from abc123
> go
>
> Thanks,
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
No. You can't do that at creation time. You could probably write
something pretty quickly to iterate through the syscolumns table for
each procedure and generate a dummy set of parameters. There is also
likely sample code out there to do the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
> No. You can't do that at creation time. You could probably write something
> pretty quickly to iterate through the syscolumns table for each procedure
> and generate a dummy set of parameters. There is also likely sample code
> out there to do the same.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks David
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
Ok. I whipped something together that you can use directly in a T-SQL
script. The script is mildly tested, so additional testing is required
at your end before using on a production system. The script creates the
necessary dynamic SQL to execute each procedure with NULL parameters and
then executes using SET FMTONLY ON and stored the results of the
execution in the temp table.
Good luck.
-- Stores the objects, execution SQL, and result
Create Table #ExecProcs (
ID INT IDENTITY NOT NULL,
Owner nvarchar(128) NOT NULL,
SPName nvarchar(128) NOT NULL,
ParameterCount INT NOT NULL,
ExecResult INT NULL,
ExecSQL nvarchar(2000) NOT NULL,
PRIMARY KEY (ID))
go
-- Get a list of procedures and create the execution SQL
Insert into #ExecProcs (
Owner,
SPName,
ParameterCount,
ExecSQL )
Select
USER_NAME(o.uid) as "Owner",
o.name as "SPName",
COUNT(p.name) as "ParameterCount",
'SET FMTONLY ON;' +
'EXEC [' +
USER_NAME(o.uid) + '].[' + o.name + '] ' +
CASE SIGN(COUNT(p.name))
WHEN 0
THEN ''
WHEN 1
THEN
REPLICATE('NULL,', COUNT(p.name) - 1) +
REPLICATE('NULL', COUNT(p.name) - (COUNT(p.name) - 1))
END + ';'
from
dbo.sysobjects o
left outer join
dbo.syscolumns p
on
o.id = p.id
where
o.type = 'P'
Group By
o.uid,
o.id,
o.name
Order By
o.uid,
o.id,
o.name
go
-- check the results
Select * from #ExecProcs
go
-- script to execute each procedure and store result in temp table
Declare @.Result INT
Declare @.SQL nvarchar(2000)
Declare @.ID INT
Declare @.Loop BIT
Set @.Loop = 1
While (@.Loop = 1)
Begin
SET @.ID = NULL
Select TOP 1
@.ID = ID,
@.SQL = ExecSQL
From
#ExecProcs
Where
ExecResult IS NULL
If @.ID IS NULL
SET @.Loop = 0
Else Begin
EXEC (@.SQL)
Update
#ExecProcs
Set
ExecResult = @.@.ERROR
Where
ID = @.ID
End
End
go
-- check results
Select * from #ExecProcs
go
-- drop the temp table
drop Table #ExecProcs
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David - this works great.
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OuJKWnEmFHA.2904@.TK2MSFTNGP14.phx.gbl...
> C-W wrote:
> Ok. I whipped something together that you can use directly in a T-SQL
> script. The script is mildly tested, so additional testing is required at
> your end before using on a production system. The script creates the
> necessary dynamic SQL to execute each procedure with NULL parameters and
> then executes using SET FMTONLY ON and stored the results of the execution
> in the temp table.
> Good luck.

Detaching and Re-Attaching Loses Stored Procedures

Hi, I've just detached using QA and sp_detach_db a database that I have been
working on for a client and taken it to the clients site and using QA and
sp_attach_db attached it to their SQL 2000 Server.
The problem is that a) there are none of my stored procedures for that db on
their server, b) when re-attaching it back to my SQL Server there are none
of my stored procedures and worst still c) who forgot to backit up first!!
I thought all user stored procedures were stored within the mdf files so why
have they not gone with the db, tables, etc. The user sps did NOT start with
sp_ by the way.
Any help would be much appreciated.
Thanks
David Smith
MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)Look at that issues:
http://groups.google.de/groups?q=at...c.rr.com&rnum=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David Smith" <david.smith@.lonsdale.co.uk> schrieb im Newsbeitrag
news:uGfYA8bSFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi, I've just detached using QA and sp_detach_db a database that I have
> been working on for a client and taken it to the clients site and using QA
> and sp_attach_db attached it to their SQL 2000 Server.
> The problem is that a) there are none of my stored procedures for that db
> on their server, b) when re-attaching it back to my SQL Server there are
> none of my stored procedures and worst still c) who forgot to backit up
> first!!
> I thought all user stored procedures were stored within the mdf files so
> why have they not gone with the db, tables, etc. The user sps did NOT
> start with sp_ by the way.
> Any help would be much appreciated.
> Thanks
> David Smith
> MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)
>

Designing Primary Key and Clustered Index and Performance

I have several tables where the clustered index (the
physical way the data is stored) is different from the
Primary Key (which is just a unique number). It seems to
me that this will help me the most, as the clustered
index supports my SELECT statements, and the Primary Key
column will support my UPDATE and DELETE statements. I
am very new to SQL Server. My question is this. Is
designing the tables this way ok to do, or is this not
how it should be done?
Second part of my question is, If what I am doing is
fine, then does it make a difference (performance wise)
if I have the Primary Key column the first column table
or about the seventh column in?
Thanks so much for your help.Depends on who you ask. Zealots will say that you should never, ever use an
arbitrary unique integer as a row identifier. I disagree. However,
whenever possible, even if you are using a unique identifier you should
attempt to set a 'natural' primary key based on uniqueness in your data, and
use this for your primary key rather than the row identifier. You can still
use the ID to make life simpler (e.g. passing back lists of rows to client
applications for singleton selection), but the primary key will help
maintain and validate the table's data.
As for location within the column list, it makes no difference where
anything is. Don't rely on the ordering of your column list. Always
specify explicit column lists in the order you want them for selects and
inserts.
"Nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:052b01c3aee5$09ea4e00$a301280a@.phx.gbl...
> I have several tables where the clustered index (the
> physical way the data is stored) is different from the
> Primary Key (which is just a unique number). It seems to
> me that this will help me the most, as the clustered
> index supports my SELECT statements, and the Primary Key
> column will support my UPDATE and DELETE statements. I
> am very new to SQL Server. My question is this. Is
> designing the tables this way ok to do, or is this not
> how it should be done?
> Second part of my question is, If what I am doing is
> fine, then does it make a difference (performance wise)
> if I have the Primary Key column the first column table
> or about the seventh column in?
> Thanks so much for your help.|||Placing the clustered index on a column other than the primary key is often
wise.
You only get ONE clustered index per table so use it wisely (Which is what I
believe you are doing).
If you are new to SQL Server, welcome to the community.
I recommend starting with these 3 books:
1. Inside SQL Server 2000 by Kalen Delaney
2. Professional SQL Server 2000 Programming By Robert Vieira
3. Transact-SQL Programming by Kline
Cheers
Greg Jackson
Portland, OR

Design Table; Columns/Description get with SP??

Hi,

I'm using stored procedures to create an online data dictionary for
all of our dbs and I thought it would be very cool if there was a way
that I could somehow pull the columns description entered in the table
design view (lower pane) of EM. I have poked around, but have not
found a way. It is my suspcion that this may be impossible. Does any
smart person out there know?

Also want to pull pks and fks and other constraints, which I think I
can figure out how to do, but if anyone has any pointers, that would
also be appreciated.

Many Thanks,

Georgiavesta (vesta0424@.gmail.com) writes:

Quote:

Originally Posted by

It looks to me like you create your extended properties with this
function. Are there any default functions to get the information that
has been manually entered in the description field?

No, functions don't create anything, they can only retrieve data. To
add an extended property from T-SQL, you use sp_addextendedproperty.

I cannot really give any examples of using either, because I have not
used extended properties myself very much. Or rather not at 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

Design question from data mining newbie

Hi,

We currently have about 900 stored procedures which have logic to group healthcare claims into different 'Edit Groups' depending on the logic within each 'Edit' stored procedure.

Examples of the logic for the Edit stored procedures would be something like:

Edit1: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P1, P2, P3....P345) and a diagnosis code in (D1, D2,...D123) and does NOT have a modifer code in (M1, M2, M3)

Edit2: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P7, P8, P9....Pxxx) and a diagnosis code in (D1, D2,...Dyyy) and has a modifer code in (M3, M4, M7), which are dated within 120 days of each other.

Do you think one of the SQL Server 2005 Data mining algorithms (Clustering or Classification or Association Rules) could play some part in this? Most of the 900 stored procs can be grouped based on logic, I mean the logic is similar for each group and only the parameters (in brackets above) vary for each stored proc within the same group.

We're totally new to data mining, although we do have some moderately complex cubes running. Which algorithm (if any) would be the most appropriate for our needs?

Thanks for any help,

JGP

Hi,

From you examples, you data is of multimensional nature. It is definitely helpful to use Microsoft Analysis Services to create OLAP cube(s) to efficiently support browse and explore your edit groups. If you are only interested in querying your edit groups, you don't need data mining.

Data mining is good at generalizing knowledge(such as patterns) from data. It can then use learned knowldege to analyze your (new) data. For example, if you have a given set of claims described by the following table:

ClaimID Income HaveInurance City Fraud

1 x0,000 Yes FairyLand No

2 y0,000 No FraudLand Yes

.......

Suppose you can collect the above data from your claim database. Now let's say that you are interested in predict whether or not a new claim is fraud. You can train a model with one of Microsoft data mining algorithms (such as Microsoft Decision Trees). After training, you can use your model to predict new claims like this:

ClaimID Income HaveInurance City

10001 a0,000 Yes FairyLand

1002 b0,000 No FraudLand

.......

Depends on the query you use, you can get some result like this:

ClaimID Income HaveInurance City Predicted Result of Fraud

10001 a0,000 Yes FairyLand No

1002 b0,000 No FraudLand Yes

.......

In general, data mining can play a role when you need to learn patterns from your data, and then apply the patterns to analyze (new) data.

|||

Thanks for the example.

Are you saying that Data mining is relevant only in predicting stuff and not in finding relationships based on pre-defined rules, like what I initially explained?

I did see come cases where classification was done, where based on a bunch of parameters, historic claims can be classified into Edit1(cluster1) , Edit2(cluster2) etc.

Isn't this possible?

Thanks,

JGP

|||

My post is just using predicting as an example. Data mining is relavant to predicting as well as finding relation among data. For example, you can use clustering algorithm to cluster you data, and check whether there is a natural mapping between your edit groups and the cluster1 you found (as you mentioned above). This process can help you understand you data better. For example, if an Edit group can be naturally mapped to some cluster, this Edit group can be considered as well defined, since it really maps to some existing grouping (or cluster) of your data. On the other hand, you might consider merge a few groups if they belong to the same cluster, etc.

On the other hand, data mining works on the basis of probability. In other words, it can not be 100% correct most of the times. Say, for a problem with a set of rules, you can already classify each case (such as each claim) into each target group 100% correct. You don't want to use data mining, because you can not do any better than 100% correct, and data mining does not come free. But, if you need to find something unknown about your data, such as a claim is/isn't fraud or if it belongs to some unknown group, you should resort to data mining.

Good luck,

|||

Thanks, I think I'm getting the picture now.

Just to get some hands on, would you happen to know of any good tutorials\books for clustering\classification that is available for newbies?

Preferably one that works with 'well-defined' groups....

|||

A good book is Data Mining Techniques by Berry and Linhoff.

I think in your situation you can use data mining for data discovery to learn quite a bit about your data sets. It sounds like each of your "edits" are a fairly complicated set of rules and it may be difficult to determine which rules end up being more important, or which "edits" are related (if I am correct, a single record could have multiple "edits", no?)

With this is mind, you could "reverse engineer" the edits with a simple classification model -e.g. trees - to predict what factors are the "most important" in determining an edit. If all of your edits are "and" conditions, this won't do much, but if you have any with "or" conditions, you may find that a majority of records recieve an edit for only a few of the possible conditions. Using this to classify old records, as Yimin notes, is not going to be 100% accurate though, since you already have an encoding of the 100% accurate rules.

If you created a table that had for each record all of the record data and all of the possible "edits" for that record (you would likely need a nested table) you could predict "edits" based on the record data plus other "edits". This would end up in a relationship diagram describing how record data and edits are related. If you made such a model including only the edits, you would see how they are interrelated independent of record data. You could perform a similar operation using Clustering, to see if groups of edits cluster together.

In the end you could end up with a greater understanding of your data - potentially removing redundant code or streamlining in other ways. The good thing about data mining, it that it's painless - it's kind of fun to play with and you can get some good insights, but it doesn't cause any harm in the meantime....

Enjoy, and feel free to post any follow up questions.

-Jamie

Design question - stored prosedures vs. components

It is a design question which affects SQL Server.
Scenario A. Business logic in SQL Server stored procedures. No middle tier
components is used. As a result the stored procedures are very long, up to
5,000 lines.
Scenario B. Business logic is in components. Stored procedures are used for
elementary operations, insert, update, delete and therefore are very short.
Components have code that glues everything together. Components can run on a
separate server.
In my opinion, scenario A doesn't scale well and causes very high cpu
utilization on SQL Server but I would like to listen somebody else's opinion
on that ...
Thanks,
-Stan"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
Stan,
Let me give you my opinion. We have a similar set up at our location.
We developed a system where all business logic laid in SQLServer SPs,
triggers, etc, and no middle tier. Only light validations were performed at
the UI and the heavy stuff took place at the database server. Pros: Any
change in business rules affected 80%-100% the database layer (SPs) and
0%-20% on the client side. This made it a lot easier for us to modify or
correct code, save and go, without having too much impact on the client
layer. Also, having all database objects at hand (tables, views, other SPs,
etc.) is always a great advantage if you place your business logic in SPs.
Cons: As you said, it causes very high CPU utilization, but that will always
depend on the hardware you are using, and how intense will be used your
application. This drawback may be compensated with a good state-of-the-art
server, if feasible.
Having business logic in a middle tier also has its great advantages, and
may free your database server machine of hardwork. But then, in my opinion,
you may have to deal with C++/VB (or whatever language your are using) code,
that definitely is more difficult to maintain than SQL statements.
In conclusion, there is actually no better approach between these two. That
will depend on the requirements specifications for your system (how
complicate it will be, what would fit better based on your requirements) and
the expected usage.
C.|||I agree entirely with Carlitos, but would like to add... for MAXIMUM
scalability you must go to a middle tier... Like Carlitos, I find loading
the back end, easy and quick to create and maintain... However, if you need
HIGHEST scalability, when you have already purchased the largest SQL Server
box that money can buy, what do you do?... you have to move work OFF of the
SQL Server onto another box - middle tier...
There is no best answer - except the one that meets your needs
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
>|||I concur with Wayne in what he added.
C.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||I agree - it all depends on situation.
I am trying to feel the magnitude of a difference between these two
scenarios.
It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
server box
with stored procedures that are long, run often and use a lot of temp tables
...
Despite that, there are a lot of vendors and developers who opt for keeping
business logic on SQL Server...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||You have to get rid of the temp tables then, they are expensive resource
wise. A lot of cases I have seen where temporary tables were used in stored
procedures they could be rewritten into one SQL statement, usually with
derived tables, and performance went up by what I think is the rule-of-thumb
for these kind of scenarios: 50 times. Yes, you read that correct, writing
proper SQL code instead of using a procedural approach and temp tables (and
even worse cursors) makes your code run 50 times as fast.
Jacco Schalkwijk
SQL Server MVP
"Stan" <nospam@.yahoo.com> wrote in message
news:O8Nbr9pjDHA.3688@.TK2MSFTNGP11.phx.gbl...
> I agree - it all depends on situation.
> I am trying to feel the magnitude of a difference between these two
> scenarios.
> It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
> server box
> with stored procedures that are long, run often and use a lot of temp
tables
> ...
> Despite that, there are a lot of vendors and developers who opt for
keeping
> business logic on SQL Server...
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > I agree entirely with Carlitos, but would like to add... for MAXIMUM
> > scalability you must go to a middle tier... Like Carlitos, I find
loading
> > the back end, easy and quick to create and maintain... However, if you
> need
> > HIGHEST scalability, when you have already purchased the largest SQL
> Server
> > box that money can buy, what do you do?... you have to move work OFF of
> the
> > SQL Server onto another box - middle tier...
> >
> > There is no best answer - except the one that meets your needs
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > > It is a design question which affects SQL Server.
> > >
> > > Scenario A. Business logic in SQL Server stored procedures. No middle
> tier
> > > components is used. As a result the stored procedures are very long,
up
> to
> > > 5,000 lines.
> > >
> > > Scenario B. Business logic is in components. Stored procedures are
used
> > for
> > > elementary operations, insert, update, delete and therefore are very
> > short.
> > > Components have code that glues everything together. Components can
run
> on
> > a
> > > separate server.
> > >
> > > In my opinion, scenario A doesn't scale well and causes very high cpu
> > > utilization on SQL Server but I would like to listen somebody else's
> > opinion
> > > on that ...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||On Thu, 9 Oct 2003 21:57:14 +0100, "Jacco Schalkwijk"
<NOSPAMjaccos@.eurostop.co.uk> wrote:
>You have to get rid of the temp tables then, they are expensive resource
>wise. A lot of cases I have seen where temporary tables were used in stored
>procedures they could be rewritten into one SQL statement, usually with
>derived tables, and performance went up by what I think is the rule-of-thumb
>for these kind of scenarios: 50 times. Yes, you read that correct, writing
>proper SQL code instead of using a procedural approach and temp tables (and
>even worse cursors) makes your code run 50 times as fast.
I just want to concur with what so far is an amazingly consistent
thread!
Putting everything in the back end is probably extreme, as is putting
nothing in the back end. I will share this wisdom with you, that what
the back end is all about is not tables, but a data model. If all
your middle tier asks of the database is simple stuff, it is extreme
in another dimension, and doing things the hard way.
5,000 lines is way too long for a single SP, I'd guestimate from just
that, that something is majorly wrong. Lots of cursors, too, I'll
bet, unnecessary and horribly slow cursors, is what I mean. Clean up
the database tier, and the entire problem might just vanish. I've
heard that it's easy to load up an 8-way system with SQLServer because
neither Wintel nor SQLServer is very good at it, but the only case
I've actually seen had such crude code running on it, that it was
really inappropriate to bitch about the hardware.
No easy answers.
Joshua Stern|||Hi Stan,
Yes, there is no easy answer. I'm including some supplemental information
below:
Where Should Business Logic Go?
http://www.4guysfromrolla.com/webtech/121501-1.shtml
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

design help

hey all,
you know in an invoice sometimes you have past due amounts and credited
amounts. are these items normally stored or are they calculated on the fly?
thanks,
rodcharThose two are quite a bit different, and the answer is "it depends". What
are you trying to do with them?
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"rodchar" wrote:

> hey all,
> you know in an invoice sometimes you have past due amounts and credited
> amounts. are these items normally stored or are they calculated on the fly
?
> thanks,
> rodchar|||i'm just trying the best way to make them appear on an invoice.
"MeanOldDBA" wrote:
> Those two are quite a bit different, and the answer is "it depends". What
> are you trying to do with them?
>
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "rodchar" wrote:
>