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 RPC:Completed and SP:Completed

Hi,
I know that SP refers to stored procedures, and RPC to remote procedure
calls, but I don't know what that means in real life.
I can see in my trace files that each stored procedure has related
RPC:Completed event. So why would anybody include SP:Completed event too?
Any ideas or advices?
Thanks
SP:Completed events would occur if you called a sp from within another sp or
if the sp was initially called in a batch. If you only traced RPC events
you would miss these executions. Sometimes you don't care to see anything
other than the initial call to the server which would either be a RPC or a
batch depending on how the call was made. An RPC only occurs when you call
the sp as a sp. By that I mean you would use the command type of stored
procedure to properly execute a sp as a RPC. If you said the type was text
it would come across as a Batch:Completed and then it would have a
SP:Completed. This is due to the fact each batch gets a plan and each sp
execution gets it's own plan as well. Note that not all columns show up for
SP:Completed as they do for RPC and Batch such as reads, cpu etc.
Andrew J. Kelly SQL MVP
"peja" <peja@.sympatico.ca> wrote in message
news:erFcUZaKGHA.668@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I know that SP refers to stored procedures, and RPC to remote procedure
> calls, but I don't know what that means in real life.
> I can see in my trace files that each stored procedure has related
> RPC:Completed event. So why would anybody include SP:Completed event too?
> Any ideas or advices?
> Thanks
>

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.

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 Stored Procedures and Function?

Diff. b/w Stored Procedures and Function??

When any of them is appropriate to use?

Here|||

What is the difference between a Sub and a Function in VB?

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 I just trip over a golden egg?

I'v been scouring the MSDN library and asking in newsgroups, forums, etc. on a way to extract [only] the time from a DateTime field (for a stored procedure) and no one could answer my question. then I tried this in QA:

selectright(getdate(),7)

and I found an answer. I'm now looking for someone to prove this is either a golden egg, a goose egg, or a documented feature I missed and if so where, so I can learn from it.

Jon

This will work,if you know what the default locale of the Sql Server is set as. However, if you don't, then you could get a nasty shock. For instance, if the server happened to be using ISO8601 dateformats then you would get something meaningless back.

Try

SELECT Right(CONVERT(varchar,GetDate(),126),7)

to see what I mean. The safest way to do what you are doing would be:

SELECT LTRIM(Right(CONVERT(varchar,GetDate(),0),7))

That should guarantee you are getting the correct dateformat string to work with. There's nothing mysterious about the RIGHT function, either - most SQL languages have a substring function.|||

Thank you and I accept the safest way as you suggested. The RIGHT function wasn't mysterious to me but rather the tool I used to get the answer I wanted. I was looking for an easy means to retrieve the time from SQL and was frustrated nobody out there could answer a question I thought would be a standard novice question. I'm realizing the simplest things are the cause of the greatest frustration in the programming world. Thanks for your solution.

Jon

|||Actually, I would suggest this:
SELECT CONVERT(char(12),GETDATE(),114)

For this datetime value:
2005-12-17 20:55:27.060

This would be returned:
20:55:27:060

SeeCAST and CONVERT for more information on how to use these 2 functions.|||

Thank You Terri! I was not familiar with CONVERT and now it seems ALL my date problems can be solved by this new information.

Jon

|||

Hmmm... that's interesting. To tell you the truth, I would have just used a whole bunch of datepart functions myself :)

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

Device Activation Error

Hi,

I have set of stored procedures in SQL 7.0. They are all encrypted. I moved the whole set to SQL 2K. I took a backup of the SQL 7.0 version and put it in SQL 2K. Now, when I run the stored procedure in SQL 2K, I am getting an error as "Device Activation Error". This is happening when I run a stored procedure which is creating a new database and I am giving the MDF and LDF names as parameters. I have not met with any success in SQL Books. Please enlighten...

Thanks,
SrinidhiOriginally posted by srinidhiv
Hi,

I have set of stored procedures in SQL 7.0. They are all encrypted. I moved the whole set to SQL 2K. I took a backup of the SQL 7.0 version and put it in SQL 2K. Now, when I run the stored procedure in SQL 2K, I am getting an error as "Device Activation Error". This is happening when I run a stored procedure which is creating a new database and I am giving the MDF and LDF names as parameters. I have not met with any success in SQL Books. Please enlighten...

Thanks,
Srinidhi

what do you mean by encrypted stored procedures ?
if you could post the code ,it could be more helpful.|||Does the stored procedure works fine in SQLServer7.0 ?|||So your SP is encrypted. It is written with incompatible MSSQL7 syntax.
You can simply decrypt it. MSSQL2K encryption is weak.
Get permitions to rewrite SP from developer and look at I-net.

Development Environment Needs?

Trying to figure out what development enviroment we need in order to
do the following:

- develop a non-native SQL server stored procedure;
- call a web service or java program from the stored procedure;
- return static values;
- call the stored procedure from a view.

How do I get a hold of the right tools and what do I need to put the
pieces together?

Obviously, I've not used SQL server and I'm looking for the basic
starting point.

Thanks!CG (chelseagraylin@.hotmail.com) writes:
> Trying to figure out what development enviroment we need in order to
> do the following:
> - develop a non-native SQL server stored procedure;
> - call a web service or java program from the stored procedure;
> - return static values;
> - call the stored procedure from a view.

You cannot call stored procedures from views. You can call extended
stored procedures from used-defined functions though, and these you
call from views. (Or use table-valued functions which are basically
parameterized views, and which can be multi-statement.)

Typically you develop extended stored procedures in C++. If you want to
talk .Net you would need a COM interop.

Since you appear to be forward-looking, you might find interest in
the upcoming version of SQL Server, where you can program CLR directly
in SQL Server. In SQL 2005 you can develop the function directly in
CLR. To call a web service there would still be a few things to go
through, but it would certainly be easier.

Beta 2 of SQL Server is expected soon, and this will be a public beta.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So if I want to do this with the current version (yes, looks like this
will be much easier in the future!), I need a SQL Server environment
set up (hopefully just the desktop version) and then I need an
environment to be able to write a C++ program that is accessible by
SQL Server?

Sounds so easy...|||CG (chelseagraylin@.hotmail.com) writes:
> So if I want to do this with the current version (yes, looks like this
> will be much easier in the future!), I need a SQL Server environment
> set up (hopefully just the desktop version) and then I need an
> environment to be able to write a C++ program that is accessible by
> SQL Server?

For SQL Server I would recommend using Developer Edition, which is at
49 USD only. Developer Edition comes with graphic tools, and having
Query Analyzer to submit queries is invaluable.

However, once you go in production, you are better of with MSDE, since
Developer Edition is not licensed for production. (And for some strange
reason, the graphic tools can be used against MSDE according to the
license.)

You will also need a couple of include files and link libraries. They
come with Devloper Edition.

For the C++ environment I am not really the guy to ask, but Visual
Studio is of course a safe bet. If you go for GNU C++ to use freeware,
you will probably need the Platform SDK, which I have no idea how it
is available outside VS.

> Sounds so easy...

Getting the environment is indeed the easy part. The actual development
is likely to be tougher. Writing extended stored procedures is not for
the faint of heart. Keep in mind that they execute in-process, so an
access violation in your XP can crash the entire SQL Server.

--
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 Sommarskog (esquel@.sommarskog.se) writes:
> (And for some strange reason, the graphic tools can be used against MSDE
> according to the license.)

An important word disappeared here, so I take it again:

> (And for some strange reason, the graphic tools can *not* be used against
> MSDE according to the license.)

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

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

To add to Erland's post...

Don't forget source code control, such as Visual Source Safe or PVCS... If
you go the whole Microsoft suite then a MSDN subscription would be an
excellent investment.

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951B2905B452Yazorman@.127.0.0.1...
> CG (chelseagraylin@.hotmail.com) writes:
> > So if I want to do this with the current version (yes, looks like this
> > will be much easier in the future!), I need a SQL Server environment
> > set up (hopefully just the desktop version) and then I need an
> > environment to be able to write a C++ program that is accessible by
> > SQL Server?
> For SQL Server I would recommend using Developer Edition, which is at
> 49 USD only. Developer Edition comes with graphic tools, and having
> Query Analyzer to submit queries is invaluable.
> However, once you go in production, you are better of with MSDE, since
> Developer Edition is not licensed for production. (And for some strange
> reason, the graphic tools can be used against MSDE according to the
> license.)
> You will also need a couple of include files and link libraries. They
> come with Devloper Edition.
> For the C++ environment I am not really the guy to ask, but Visual
> Studio is of course a safe bet. If you go for GNU C++ to use freeware,
> you will probably need the Platform SDK, which I have no idea how it
> is available outside VS.
> > Sounds so easy...
> Getting the environment is indeed the easy part. The actual development
> is likely to be tougher. Writing extended stored procedures is not for
> the faint of heart. Keep in mind that they execute in-process, so an
> access violation in your XP can crash the entire SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Developing Stored Procedures using Management Studio Express

Hi,

I am new to SQL server 2005. I am developing stored procedures as follows in Management Studio:

1)Right click Stored Procedures folder on a database
2)Select "New Stored Procedure"
3)Write the query in a .sql file
4)Execute the query.

Now in this model, I dont seem to have a develop->test->develop and then deploy, type of development cycle. The problem is each time I execute a query it is deployed and re-executing it results in an error like: "There is already an object named 'NewEmployee' in the database." Which forces you to manually delete the SP and re-execute your query. It doesn't give you a chance to test your SP logic before deploying. Contrary to this, If I developed CLR SP's in Visual Studio, I would test/debug the procedure, and then "deploy" it later when I'm finished with it. Can I not do this in Management Studio?

Thanks,
Bahadir

right click the existing stored proc

click on "script stored procedure as">alter>"new query editor"

modify the scripts

and run

|||Doh! I think this was a bit stupid question. More clever next time :)

Thanks,
Bahadir

Developing Stored Procedures using Management Studio Express

Hi,

I am new to SQL server 2005. I am developing stored procedures as follows in Management Studio:

1)Right click Stored Procedures folder on a database
2)Select "New Stored Procedure"
3)Write the query in a .sql file
4)Execute the query.

Now in this model, I dont seem to have a develop->test->develop and then deploy, type of development cycle. The problem is each time I execute a query it is deployed and re-executing it results in an error like: "There is already an object named 'NewEmployee' in the database." Which forces you to manually delete the SP and re-execute your query. It doesn't give you a chance to test your SP logic before deploying. Contrary to this, If I developed CLR SP's in Visual Studio, I would test/debug the procedure, and then "deploy" it later when I'm finished with it. Can I not do this in Management Studio?

Thanks,
Bahadir

right click the existing stored proc

click on "script stored procedure as">alter>"new query editor"

modify the scripts

and run

|||Doh! I think this was a bit stupid question. More clever next time :)

Thanks,
Bahadir

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 Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/d.../>
ll_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/d...
tall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?
Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/de...stall_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/de...stall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:
> Bruce Thomas wrote:
> > I have been trying to determine why an stored proc that I wrote that
> > makes a single random selection from a list of about 3 million phone
> > numbers works slowly in a particular client's office when in use by
> > multiple Clients. During investigation I found that the IT
> > entrepreneur who supplied and installed my Client's software provided
> > a Developer's edition of SLQ Server. As it is illegal to use this
> > Edition commercially, my Client will be upgrading to an appropriate
> > version before the app goes into commercial use. Meanwhile I would
> > like to know if the Developer's Edition includes a concurrent
> > workload governor like the the Personal Edition or any other kind of
> > governor that would result in performance degradation?
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>