Difference between SP and function

Hi All,

It may be sound weird. I want to find the difference between SQL Stored procedure and functions. I knew a couple one is for function, parameter is must where as in SP its not, second is function would return a value whereas procedure wont.

Is there anythign else?That was it! You are on the right track :)|||>> second is function would return a value whereas procedure wont.

not necessarily. even a stored proc can return values ( see OUTPUT PArameters).

Function can return only ONE value where as a stored proc can return multiple values.
performance wise there isnt any diff.

I remember having googled about this once and did come across a couple of articles that xtensively described the differences..so I would say google and you can find more definitive answers.

hth|||I guess "parameter is must where as in SP " he ment for OUTPUT param!|||one use of a function is to use it to return a table object that can be used in another sql statement|||Hi

Thanks you all for your answers. But in the recent inteview which i attended they asked for one more difference between these two apart from those i specified earlier.|||I'm sure you could Google and find all of the information you need.

The main thing about UDFs is that they need to be deterministic -- that is, the same input parameters will always return the same result. So therefore you cannot, for example, directly use GETDATE() in your UDF. Another biggie is that you cannot use either @.@.ERROR or RAISERROR. And another biggie is that dynamic SQL cannot be executed.

I've gotten burnt by all of the above, and others. Some have workarounds, some do not.

Terri

Difference between procedure and function ?

What is the difference between procedure and function

Hi Sahara,

Please read the BOL for more information about them. You can find the answer in the following sections:

Stored procedures:

Designing and Creating Databases > Stored Procedures (Database Engine) > Understanding Stored Procedures >

Functions:

Designing and Creating Databases > User-defined Functions (Database Engine) >

Regards,

Janos

|||

here my findings

Features

Procedures

Functions

Parameters

Supports in, out, in & out

Only supports in

Temp Object

Accessible – You can use the temp tables inside the procedure

Not supported

Create as Temp

Accepted –

Create Proc #MyProc..

Not supported

Select Result

Supported

Not supported

Return

Return integer value

Returns any type of value

On DML Quires

Not allowed

You can embed the function on query

Calling SPs

Allowed

Not Allowed

Calling Another Functions

Allowed

Allowed

Insert/Update/Delete

Allowed

Not Allowed

Or

Only allowed against the table variables

Recursive Operation

Allowed

Allowed

Versioning (grouped)

Allowed

Not Allowed

Schema Binding

Not Allowed

Allowed

Creating Objects

Allowed

Not Allowed

EXEC

Allowed

Not Allowed

SP_EXECUTESQL

Dynamic SQL

Allowed

Not Allowed

GETDATE() or other non-deterministic functions

Allowed

Not Allowed

SET OPTION

Not Allowed

Allowed

Setting Permission

Grant/Deny

Yes

No for scalar functions.

Yes for Table Values/Inline Functions

Difference between 2 stored procedures

Code:
ALTER PROCEDURE GetBookings
(
@.CurrentDate datetime,
@.Catergory char(10)
)
AS

SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
Class, @.CurrentDate AS Expr1
FROM tblBookings
WHERE StartDate <= @.CurrentDate and EndDate >= @.CurrentDate and
Catergory=@.Catergory

Code:
ALTER PROCEDURE GetBookings
(
@.StartDate datetime,
@.EndDate datetime,
@.Catergory smallint
)
AS

SELECT *
FROM tblBookings
WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
Catergory=@.Catergory

__________________________________________________ ________
What the difference between the to stored procedures in terms what the
date filters return??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Kieran Dutfield" <kolo83@.talk21.com> wrote in message
news:401fcc45$0$70304$75868355@.news.frii.net...
> Code:
> ALTER PROCEDURE GetBookings
> (
> @.CurrentDate datetime,
> @.Catergory char(10)
> )
> AS
> SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
> Class, @.CurrentDate AS Expr1
> FROM tblBookings
> WHERE StartDate <= @.CurrentDate and EndDate >= @.CurrentDate and
> Catergory=@.Catergory
>
> Code:
> ALTER PROCEDURE GetBookings
> (
> @.StartDate datetime,
> @.EndDate datetime,
> @.Catergory smallint
> )
> AS
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Catergory=@.Catergory
> __________________________________________________ ________
> What the difference between the to stored procedures in terms what the
> date filters return??
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

There isn't enough information in your post to answer your question
properly - what is the table structure, what are the possible range of
values in each of the date columns, and what range of values might be passed
in in the parameters? What is the result set you want to see, for a given
set of sample data?

To get a good answer, you would need to post DDL for your table, sample
data, and the result set you want. But if you set up some sample data, and
simply try the two queries, I guess you'll be able to answer your own
question. Of course, if you don't understand why you get certain results,
then posting the extra information should allow someone to explain or
suggest a solution.

Simon|||Kieran Dutfield (kolo83@.talk21.com) writes:
> SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
> Class, @.CurrentDate AS Expr1
> FROM tblBookings
> WHERE StartDate <= @.CurrentDate and EndDate >= @.CurrentDate and
> Catergory=@.Catergory
>...
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Catergory=@.Catergory
> __________________________________________________ ________
> What the difference between the to stored procedures in terms what the
> date filters return??

The first query returns all rows where the @.CurrentDate falls within
the interval of [StartDate, EndDate].

The second query returns all rows where the entire interval [StartDate,
EndDate] falls within the interval of [@.StartDate, @.EndDate].

Open intervals, where one or both value are NULL will not be included,
and if any of the variables are NULL, no row will be returned in either
query.

If there are time portions in some of the values, this can lead to
confusion, if you expect the filtering to look at the date only.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Difference between [Stored Procedure / Trigger & Function]

hi

can any give me what are the major difference between [Stored Procedure / Trigger & Function] with example

kinds regards

There's no need for examples. The distinctions are very bold.

A stored procedure generally performs query's on one or more tables. i.e. UPDATE, INSERT, DELETE

A trigger is tied to one table and fires based on the type of trigger you use.

A function generally doesn't touch any table but simple performs a task such as complex calculations or string munipulation and returns a value.

Notice I use the term generally and I mean it very loosely. All of these can overlap but the choice of use is based on the desired task.

Adamus

sql

Difference between #table and # # Table?

If i create a ##table using my stored procedure, I can do the select * from ##table

but when i created the #table using the stored procedure and tried to do the select * from #table

I am getting the error message invalid object #table

that means it is going out of scope.

But if i create the ##table, will that table going to remain in the database even until i drop the ##table or it gets dropped when i close the database connection.

Thank you very much.
-RajFrom SQL Server Books OnLine:

There are two types of temporary tables:

Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

difference among them

Hi everyone,
What is the real difference among them ?
CREATE PROCEDURE sp_devamsizlik
@.ADI CHAR(20)
AS
SELECT *
FROM OGRENCI
WHERE ADI=@.ADI

GO
--

CREATE FUNCTION sp_devamsizlik
(@.ADI

CHAR(20) )
RETURN TABLE
BEGIN
RETURN( SELECT * FROM OGRENCI WHERE ADI=@.ADI )
END

The short answer could be something like; they are both tools in your toolbox, one is a hammer and the other is a screwdriver. They are used to build with, but at different times for different purposes. Which is best depends on if you're looking at a nail or a screw.

The 'real' difference could fill many many pages, so I'd like to point you to BOL first. There's a lot in there that explains about procedures and functions. The one thing that may not be so obvious in BOL, is that functions aren't always the magic wand that one could be led to believe. UDF's should be used with caution, but used right, they can serve you very well.

/Kenneth

|||

Hello

The First one is Storde Procedure and the Second one is User Define function.

Search for Diffrence between SP and UF in detail

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

|||

Akbar Khan wrote:

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

Hi, would you mind being more clear ?|||

Hi

Sorry there is an error in the posted syntex: actually

In Stored Procedure you can call a User Define Function Like If write the follwing query in Stored Procedure then it will run with out any error.
Suppose we have a GetName User Define Function which returns Student Name and accept the Student Id as a aparameter.

Select dbo.GetName(std_id) FROM STUDENT

Here you can also see that a User Define function is called in SQL Select Query.

But you cann't call the follwing syentex in User Define function

Exec SP_DoSomething

SP_DoSomeThing is a stored procedure.

The Vest way to Understand Code it....

Diff. performance in Query Analyzer than when using stored procedure

Hi group,

I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.

Here is what I execute in Query Analyzer when bypassing the stored
procedure:

USE Verizon
GO
DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Here is what I execute in Query Analyzer when calling the stored
procedure:

DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @.phonenumber

Here is the script that created the stored procedure itself:

CREATE PROCEDURE dbo.baddebt_phonelookup @.phonenumber varchar(15)
AS

SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.

Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@.phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@.phonenumber]) ORDERED
FORWARD)
------------

Finally, here is the execution tree when calling the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
------------

Thanks for any help on my path to optimizing this query for our
production environment.

Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0308051401.2e65bb80@.posting.google.com>...
> Hi group,
> I have a select statement that if run against a 1 million record
> database directly in query analyzer takes less than 1 second.
> However, if I execute the select statement in a stored procedure
> instead, calling the stored proc from query analyzer, then it takes
> 12-17 seconds.

<snip
One possible reason is parameter sniffing - see here:

http://groups.google.com/groups?sel...7&output=gplain

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0308060118.46c12f2e@.posting.google.com>...
> One possible reason is parameter sniffing - see here:
> http://groups.google.com/groups?sel...7&output=gplain
> Simon

Wow. Thats a bit of an eye opener. It makes me wonder how best to
make sure a decent plan is chosen by SQL Server, and the answer seems
to be to make it recompile the stored procedure every time ?

or is there a way to make SQL simply use the index at all times? I'd
hate to spend a lot of time on my dev machine getting the stored
procedure to run correctly on a million record table, only to port it
to my production machine and have it take forever on the 33 million
record database because of some magically crafted execution plan :-)

Thanks,

Warren|||Here is something else that I don't understand. The stored procedure
I listed above compares a phone number that is passed in against a
Telephone_Number column that is 15 digits long, and against a computed
column (Telephone_Number_Redef), that is the left 10 digits of the
Telephone_Number column.

This is because sometimes our client passes in a 10 digit number, and
sometimes a 15 digit version that includes some check digits on the
end (Don't ask).

Anyway, in the execution plan for when the stored proc is executing, I
see the following:

----------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
----------

It appears to be recomputing the Telephone_Number_Redef column values
on the fly, instead of using the values already present. The
Telephone_Number_Redef column is indexed specifically to allow that
second comparison in the WHERE statement to be a SARG, but it seems
this is being ignored.

Is it being ignored because SQL had already decided to do a table
scan, and so though it might as well speed things up by not scanning
both columns? or is SQL doing a table scan because it thinks it needs
to re-compute the values for Telephone_Number_Redef on the fly?

Argh.

Thanks,

Warren Wright
Scorex Development Team
Dallas|||More follow-up on this issue, to help you experts analyze what's going
on here.

I've spent the day trying various things, with no success. I tried
using hints to suggest that the index on Telephone_Number_Redef be
used, which results in an error stating the stored procedure couldn't
be executed due to an unworkable hint.

I've tried declaring a new variable in the stored proc with a value
set equal to the @.phonenumber input, so SQL couldn't optimize based on
the actual value being passed in.

I've tried changing the index for the computed column to be a
clustered index.

I only wish I could simply tell SQL to use the same execution plan it
uses when I run the query from the analyzer!! All problems would be
solved!

No matter what, if I run the query from query analyzer, the response
time is a few milliseconds. If I run the stored procedure, the
response time is at least 17 seconds due to a completely suboptimal
execution plan (where the Telephone_Number_Redef's index isn't used at
all).

Introducing the new version of the stored proc with the OR statement
that checks against the computed column as well bogs down the
production server, and results in timeouts and app errors for our
client.

Frustrated,

Warren|||[posted and mailed, please reply in news]

Warren Wright (warren.wright@.us.scorex.com) writes:
> Here is something else that I don't understand. The stored procedure
> I listed above compares a phone number that is passed in against a
> Telephone_Number column that is 15 digits long, and against a computed
> column (Telephone_Number_Redef), that is the left 10 digits of the
> Telephone_Number column.

Computed column? Which you have an index on? Aha!

While Bart's article on parameter sniffing is good reading it is not
the answer here. Index on computed columns (as well on views) can
only be used if these SET options are ON: ANSI_NULLS, QUOTED_IDENTIFIER,
ANSI_WARNINGS, ARITHABORT, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL.
And NUMERIC_ROUNDABORT be OFF.

The killer here is usually QUOTED_IDENTIFIER. That option, together
with ANSI_NULLS is saved with the procedure, so that the run-time
setting does not apply, but the setting saved with the procedure.
QUOTED_IDENTIFIER is ON by default with ODBC and OLE DB, as well
with Query Analyzer. But OSQL and Enterprise Manager turns it off.
So you need to make sure that the procedure is created with
QUOTED_IDENTIFIER on.

You can review the current setting with

select objectproperty(object_id('your_sp'), 'IsQuotedIdentOn')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Diff b/w executing as Stored Procedure and Script

Hi All,
I have a peculiar problem with SQL2000. When i execute a Stored procedure in Demo & Production i get different outputs. But i copied the business logistics from the Sp and executed as a script in both the servers. Now Both the records are same.

I WANT TO KNOW " WHETHER THERE IS ANY DIFFERENCE IN EXECUTION METHODOLOGY BETWEEN STORED PROCEDURE AND QUERY".

NOTE: My stored procedure has 14 executable scripts. Upto 10 scripts no date comparisons were made. But at the 11th script the records differ.

I DOUBT WHETHER THERE WILL BE ANY DATE RELATED ISSUE WHEN EXECUTING AS STORED PROCEDURE AND SCRIPT

Hi,

There is no date related issue for a stored procedure to execute as for as I know. the diffrence between the stored proceudre and the script is the execution plan and the compiling time. remember some time in stored procedure might return the cached data. If possible try to start and stop the DB and then try to execute in both they day.

Might be some thing wrong in the quries in SP and the script which youa re running.

Take a look.

Mohan

|||

Hi Mohan,

Thanks for your reply. There is no chance for error in the script. How i say this bcoz " When i am running the script and the queries in the demo server i get the same result. So only i am very sure about that. Anyway you have given me a new Information. I shall take the necessary steps.

The DIFFERENCE occurs only in PRODUCTION server.

did debugging with SP3 ever get fixed for Visual Studio.NET?

When I installed SP3 on SQL 2000, I lost the ability to debug stored procedures
from Visual Studio - single stepping into the stored procedure.
I spent a day trying to fix it maybe 8 months ago - and gave up.
Are there any tech notes that actually work to restore the ability to debug when
SQL Server is on the same machine as Visual Studio?
If not, can I fix the problem by un-installing SQL Server and then installing
the SP2 version? I'm unclear if the database files would have any problems with
this - or have any problems when moving SP2 databases to my SP3 production
boxes?
--
Thanks in advance, Les CaudleWe can get it to single step through the procedure, but not set a breakpoint.
Was it ever possible to breakpoint? I note the documentation states it will
not "Break", but we took that to mean one could not break the execution and
re-start.
Cheers
Happy Harry
"Les Caudle" wrote:
> When I installed SP3 on SQL 2000, I lost the ability to debug stored procedures
> from Visual Studio - single stepping into the stored procedure.
> I spent a day trying to fix it maybe 8 months ago - and gave up.
> Are there any tech notes that actually work to restore the ability to debug when
> SQL Server is on the same machine as Visual Studio?
> If not, can I fix the problem by un-installing SQL Server and then installing
> the SP2 version? I'm unclear if the database files would have any problems with
> this - or have any problems when moving SP2 databases to my SP3 production
> boxes?
> --
> Thanks in advance, Les Caudle
>

Dictrionary item as parameter

I have a Dictionary(Of String, Int16) collection of items that I would like
to pass as a parameter to a stored procedure.
I want to be able to iterate thru the collection rather than call the stored
procedure for every item in the dictionary.
How can I get the collection into a format that SQL 2005 can work with?This article covers the bases on this issue:
http://sommarskog.se/arrays-in-sql-2005.html
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:E5F27C84-CE82-4FD5-8BB2-31697A7BCC68@.microsoft.com...
>I have a Dictionary(Of String, Int16) collection of items that I would like
> to pass as a parameter to a stored procedure.
> I want to be able to iterate thru the collection rather than call the
> stored
> procedure for every item in the dictionary.
> How can I get the collection into a format that SQL 2005 can work with?

Diagnose SQL Procedures.

Hi there,
Is there any way I can create one user defined stored procedure that will
let me know all the stored procedure and functions in current database have
compiled successfully, there is no compilation error in the stored
procedures ?
Basically, I have a database and there are around 90 stored procedure and I
want to check all the stored procedures compiled successfully or still they
need to compiled so I need any query or stored procedure through which I can
diagnose this...
Thanks in advance
What exactly do you mean by compile? Sp's only get compiled the first time
they are run. They get parsed and checked when you create them. There really
isn't anything such as an unsuccessful compilation.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23VA1H30MGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Is there any way I can create one user defined stored procedure that will
> let me know all the stored procedure and functions in current database
> have compiled successfully, there is no compilation error in the stored
> procedures ?
> Basically, I have a database and there are around 90 stored procedure and
> I want to check all the stored procedures compiled successfully or still
> they need to compiled so I need any query or stored procedure through
> which I can diagnose this...
> Thanks in advance
>
|||Andrew J. Kelly wrote:
> What exactly do you mean by compile? Sp's only get compiled the
> first time they are run. They get parsed and checked when you create
> them. There really isn't anything such as an unsuccessful compilation.
Maybe he wants to know how to verify existing procedures against a
schema that has changed. If that's the case, there's some information
here that might help:
http://groups.google.com/group/micro...eb685e696368c1
David Gugick - SQL Server MVP
Quest Software

Developing a Search Case

hi all,
I am developing a search case...and i got to develop a stored procedure.

the procedure will be called in the following way...

exec search_text('t1@.c1%c2%c3%t2@.c1%c2%c3%t3@.c1%c2%,'he llo')

i.e. i want to search for 'hello' in the t1,t2,t3 tables and c1,c2,c3..are the corresponding columns in respective tables..

writing select statement is easy.,..but need a best solution to divide the tables and columns and then writing the procedure...

help me out...

thanks and regards
sai sreenivasHi,

it all depends on what database server you want to run the Stored Procedure...
So, what's the database and we try again...|||hii ,
My Database server is SQLSERVER on windows 2000 platform.....

looking forward for your reply...

thanks and regards
SAI SREENIVAS JEEDIGUNTA|||I'm sorry man, I'm not that familiar with Microsoft software.
If it were DB2 or Informix I could give you the solution...|||Will you always have the pattern 1 table and 3 columns ? Can you post the sql statement you have already designed that you want emulated in the sp ? Will you be running this sp in sql server or through a front-end like visual basic ... ? Will you always be searching for "hello" or some unknown string ?|||What results are you expecting back from your stored procedure ?

Developer terminates MSSQLSERVER service

Hi.
One of the developers tried to compile a stored procedure
against the master database by mistake. The result was
that the .
In the event log the following entry appears:
The system has called a custom component and that
component has failed and generated an exception.
This indicates a problem with the custom component.
Notify the developer of this component that a failure has
occurred and provide them with the information below.
Component Prog ID: SC. Pool 61 1
Method Name: IDispenseDriver::CreateResource
Process Name: sqlserver.exe
The serious nature of this error has caused the process
to terminate.
Exception: C0000005
Address: 0x17CEA9E1
Call Stack: 0x17CEA9E2
The MSSQLSERVER service terminated unexpectedly.
Just prior to that an entry appears with the developers
user id:
Error: 0, Severity:19 State:0
SqlDumpExceptionHandler: Process 167 generated fatal
exception = C0000005
EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
process.
How can I prevent this from ever happening again?
How can I lock down my server properly enough to prevent
this from ever happening again?
Thanks,
MTAV's are generally bugs in SQL Server, and you cannot make sure that SQL Server is bug free. When
you hit an AV, search KB, make sure the db is OK (CHECKDB) and if needed open a case with MS.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> Hi.
> One of the developers tried to compile a stored procedure
> against the master database by mistake. The result was
> that the .
> In the event log the following entry appears:
> The system has called a custom component and that
> component has failed and generated an exception.
> This indicates a problem with the custom component.
> Notify the developer of this component that a failure has
> occurred and provide them with the information below.
> Component Prog ID: SC. Pool 61 1
> Method Name: IDispenseDriver::CreateResource
> Process Name: sqlserver.exe
> The serious nature of this error has caused the process
> to terminate.
> Exception: C0000005
> Address: 0x17CEA9E1
> Call Stack: 0x17CEA9E2
> The MSSQLSERVER service terminated unexpectedly.
> Just prior to that an entry appears with the developers
> user id:
> Error: 0, Severity:19 State:0
> SqlDumpExceptionHandler: Process 167 generated fatal
> exception = C0000005
> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
> process.
> How can I prevent this from ever happening again?
> How can I lock down my server properly enough to prevent
> this from ever happening again?
> Thanks,
> MT
>|||Thanks Tibor,
Good news, I ran the CHECKDB and found 0 allocation
errors and 0 consistency errors in database 'master'.
What does AV stand for? Under what criteria would I
search the KB to determine how to prevent this situation
from happening again
Thanks,
MT
>--Original Message--
>AV's are generally bugs in SQL Server, and you cannot
make sure that SQL Server is bug free. When
>you hit an AV, search KB, make sure the db is OK
(CHECKDB) and if needed open a case with MS.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"MT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
>> Hi.
>> One of the developers tried to compile a stored
procedure
>> against the master database by mistake. The result was
>> that the .
>> In the event log the following entry appears:
>> The system has called a custom component and that
>> component has failed and generated an exception.
>> This indicates a problem with the custom component.
>> Notify the developer of this component that a failure
has
>> occurred and provide them with the information below.
>> Component Prog ID: SC. Pool 61 1
>> Method Name: IDispenseDriver::CreateResource
>> Process Name: sqlserver.exe
>> The serious nature of this error has caused the process
>> to terminate.
>> Exception: C0000005
>> Address: 0x17CEA9E1
>> Call Stack: 0x17CEA9E2
>> The MSSQLSERVER service terminated unexpectedly.
>> Just prior to that an entry appears with the developers
>> user id:
>> Error: 0, Severity:19 State:0
>> SqlDumpExceptionHandler: Process 167 generated fatal
>> exception = C0000005
>> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
this
>> process.
>> How can I prevent this from ever happening again?
>> How can I lock down my server properly enough to
prevent
>> this from ever happening again?
>> Thanks,
>> MT
>>
>
>.
>|||AV is short for Access Violation (see the error messages that SQL Server returned). You would have
to try to find a relevant KB article by going in a good search string, error number etc. However,
there might not be a KB article for your problem (the bug in SQL Server might not be knows, yet),
hence the need to possibly open a case with MS support. If you find a KB article, it hopefully has
some workaround, point to some service pack that fixes this or possibly a hotfix that fixes this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:06b301c39ebc$a80b02c0$a601280a@.phx.gbl...
> Thanks Tibor,
> Good news, I ran the CHECKDB and found 0 allocation
> errors and 0 consistency errors in database 'master'.
> What does AV stand for? Under what criteria would I
> search the KB to determine how to prevent this situation
> from happening again
> Thanks,
> MT
> >--Original Message--
> >AV's are generally bugs in SQL Server, and you cannot
> make sure that SQL Server is bug free. When
> >you hit an AV, search KB, make sure the db is OK
> (CHECKDB) and if needed open a case with MS.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"MT" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> >> Hi.
> >>
> >> One of the developers tried to compile a stored
> procedure
> >> against the master database by mistake. The result was
> >> that the .
> >>
> >> In the event log the following entry appears:
> >>
> >> The system has called a custom component and that
> >> component has failed and generated an exception.
> >> This indicates a problem with the custom component.
> >> Notify the developer of this component that a failure
> has
> >> occurred and provide them with the information below.
> >>
> >> Component Prog ID: SC. Pool 61 1
> >> Method Name: IDispenseDriver::CreateResource
> >> Process Name: sqlserver.exe
> >>
> >> The serious nature of this error has caused the process
> >> to terminate.
> >>
> >> Exception: C0000005
> >> Address: 0x17CEA9E1
> >> Call Stack: 0x17CEA9E2
> >>
> >> The MSSQLSERVER service terminated unexpectedly.
> >>
> >> Just prior to that an entry appears with the developers
> >> user id:
> >>
> >> Error: 0, Severity:19 State:0
> >> SqlDumpExceptionHandler: Process 167 generated fatal
> >> exception = C0000005
> >>
> >> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
> this
> >> process.
> >>
> >> How can I prevent this from ever happening again?
> >> How can I lock down my server properly enough to
> prevent
> >> this from ever happening again?
> >>
> >> Thanks,
> >> MT
> >>
> >>
> >
> >
> >.
> >

Determining permissions through Stored Procedures

Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
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

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>