General network error. Check your network documentation.

DTS Gurus !
We have a SQL 2000 package which uses a data driven query
which makes use of two stored procedures.
GETNeededRecords() and ProcessNeededRecords() with
transformations defined. Each record in the
result set returned by the GETNeededRecords is used by the
ProcessNeededRecords(). The Data Driven
step has a log file which logs the error messages.
Consistently the package fails reporting error
the below listed error. There is not enough we could find
online. Any help in this regard is
appreciated.
Command Error in Data Driven Query:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DBNETLIB][ConnectionRead (recv()).]
General network error. Check your network documentation.
Error Help File:
Error Help Context ID:0
If the package is running on the same server, I dont
understand why there should be network error.
Would this have to do with the details talked about in the
article
http://support.microsoft.com/default.aspx?scid=kb;en-
us;827452&Product=sql2k
..
1. Please do a select * from this table, and preferably run a checktable on
this to make sure it's consistent.
2. If it is,hvae u tried reducing the number of rows/resultsets to be
processed? Same behaviour still?
3. Pls check the sql error logs and Event logs at the same time of DTS
failure.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

General network error. Check your network documentation.

DTS Gurus !
We have a SQL 2000 package which uses a data driven query
which makes use of two stored procedures.
GETNeededRecords() and ProcessNeededRecords() with
transformations defined. Each record in the
result set returned by the GETNeededRecords is used by the
ProcessNeededRecords(). The Data Driven
step has a log file which logs the error messages.
Consistently the package fails reporting error
the below listed error. There is not enough we could find
online. Any help in this regard is
appreciated.
Command Error in Data Driven Query:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DBNETLIB][ConnectionRead (recv()).]
General network error. Check your network documentation.
Error Help File:
Error Help Context ID:0
If the package is running on the same server, I dont
understand why there should be network error.
Would this have to do with the details talked about in the
article
http://support.microsoft.com/default.aspx?scid=kb;en-
us;827452&Product=sql2k
.1. Please do a select * from this table, and preferably run a checktable on
this to make sure it's consistent.
2. If it is,hvae u tried reducing the number of rows/resultsets to be
processed? Same behaviour still?
3. Pls check the sql error logs and Event logs at the same time of DTS
failure.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

General network error. Check your network documentation

Hello,

I am getting this error "General network error. Check your network documentation." inconsistently. It is happening in some of the stored procs but not all the time. This is always accompanied by "Procedure: ConnectionRead (recv())." in the stack.

I am wondering is this due to some network packets getting dropped or there is something else that probably may be causing this.

ThanksHave you checked the SQL Server error log? There should be something in there to help you pinpoint the problem.

Terri|||Checked the sql server log, there is nothing in there about this error

FWIW: Database Space Used (stored proc)

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO

CREATE PROCEDURE sp_dbspaceused

as

declare @.id int -- The object id of @.objname.
declare @.pages int -- Working variable for size calc.
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.logsize dec(15)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

/*
** If @.id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @.dbsize = Pages used
** @.bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @.dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @.dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @.logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @.bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @.pagesperMB = 1048576 / @.bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@.dbsize + @.logsize) / @.pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@.dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @.pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @.pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @.pages = @.pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @.pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GOI think this one is shorter ;):

select
reserved=(
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))*8|||Yes it is. No one ever accused me of having an overabundance of imagination.

Thanks for the nice re-write.

Regards,

hmscott|||With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

Regards,

hmscott

ALTER PROC sp_dbSpaceUsed

AS

CREATE TABLE #TempSpace (
[Database] varchar(255),
Reserved dec(15),
Index_Size dec(15),
Data dec(15),
Unused dec(15)
)

DECLARE @.sSQL varchar(1000)

SELECT @.sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
SELECT
''?'' as [Database],
reserved=(
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255)))*8'

EXEC sp_MSforeachdb @.command1=@.sSQL

SELECT * FROM #TempSpace

DROP TABLE #TempSpace

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--

Functions in Functions

Hi,

I have to calculate data in function with "EXEC". During runtime I get the Error:

"Only functions and extended stored procedures can be executed from within a function."

I would use a Stored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?

btw: Storing all the data in a table, would mean a lot of work, I rather not like to do. ;-)

Thx for any help

Blubb10

Wih in the function,

- You can't use dynamic SQL

- You can't call any stored proc

These are the limitation of the Function.

Post your soruce code.

|||Here is a thread describe the issue similar to yours.

F.Y.I.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457236&SiteID=1

Thanks,

Zuomin
|||Without knowing OP's logic we can't simply declare it is by design.. Let him post the logic used inside the function.. We will wait.. |||

DECLARE @.decBOM_Count INT

DECLARE @.strBSC_Formula NVARCHAR(200)

-- For demo, is a parameter of the function

SELECT @.strBSC_Formula = 'BR-(MW-25)-8'

--the next steps are, replacing all the variables in the formula by actual number-values

.

.

.

-- Here @.strBSC_Formula contains something like '1000-(70-25)-8'

SELECT @.strBSC_Formula = 'SET @.decBOM_Count =' + @.strBSC_Formula

EXEC sp_executesql @.strBSC_Formula, N'@.decBOM_Count DECIMAL(10, 4) OUTPUT', @.decBOM_Count OUTPUT

-- In @.decBOM_Count I expect a number as result of the formula.

|||

Ok..

Here you can't achive it from the function directly.

If you use SQL Server 2000,

You have to use extended stored procedure

- a com program which will evaluate the given fromula and return back the value

- need to register that com dll on the db server

If you use SQL Server 2005,

You have to use the CLR function

- a simple C#/VB.NET code which will evulate the expression.

Let me know the version of SQL Server.. I will try to help you on this.

|||

It is SQL Server 2005 Standard and the program is written in Access 2003 / VBA.

Functions don't show dependencies?

I use a user defined function in several stored procedures. However, only
tables show up when I look at the function's dependencies. How do I also
have the stored procedures that are using this function show up as
dependencies?
If that cannot be done, how else do I keep track of the stored procedures
which are using a certain function?
Thanks,
BrettThis works for me. Try recreating the sps.
use northwind
go
create function dbo.ufn_f1 ()
returns int
as
begin
return (1)
end
go
create procedure dbo.usp_p1
as
select dbo.ufn_f1()
go
exec sp_depends ufn_f1
go
drop procedure dbo.usp_p1
go
drop function dbo.ufn_f1
go
AMB
"Brett" wrote:

> I use a user defined function in several stored procedures. However, only
> tables show up when I look at the function's dependencies. How do I also
> have the stored procedures that are using this function show up as
> dependencies?
> If that cannot be done, how else do I keep track of the stored procedures
> which are using a certain function?
> Thanks,
> Brett
>
>|||Also,
How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"Alejandro Mesa" wrote:
> This works for me. Try recreating the sps.
> use northwind
> go
> create function dbo.ufn_f1 ()
> returns int
> as
> begin
> return (1)
> end
> go
> create procedure dbo.usp_p1
> as
> select dbo.ufn_f1()
> go
> exec sp_depends ufn_f1
> go
> drop procedure dbo.usp_p1
> go
> drop function dbo.ufn_f1
> go
>
> AMB
>
> "Brett" wrote:
>

functions and procedures

Dear Experts,
I'm working for aproduct based company, i need guidence from you in some respects

1) how to become expertise in functions and stored procedures?

is there any good links for me, i'm a learner.of cource google is there, but i dont know the starting point.please provide me some good links, and your esteemed guidenceHai friend

I hope this link helpful for u
http://www.informit.com/guides/cont...&seqNum=56&rl=1|||let mi start by saying im a stored procedure envangelist, lover n guru...so u can direct any problem or tots in ds direction to mi personally...

Stored Procedures are precompiled functions that encapsulate some basic fxns...pretty much like functions in regular procedural languages like vb e.t.c; but its compiled once...n runs straight(faster) when called...optimizes network traffic

Basically u pass parameters(or list of parameters) to a function; it does some work n returns a result(optionally)...it could return just about anything...a number, string or query...its hard!

lets create something very simple...a stored procedure to calculate area of a rectangle..

d basic structure or framework of a stored procedure is as follows:

create proc procedurename
{list of parameters n their data types passed to d stored procedure}
as
{body of procedure}

now...d 'area stored procedure'

create proc area
@.length int, @.breadth int
as
-- @.result is a local variable
declare @.result as int
set @.result = @.length * @.breadth
select @.result as [Area of a Rectangle]

its dat simple...copy d code above n paste unda ur database in query analyser n press f5...

on a fresh window...test d procedure using d following:
exec area 5,4

.................................
its dat simple.....let mi no if u av problems running ds or if u av questions...

i dont particularly work wit functions...but i no its ds straight to

be good...|||Hello seun
its great fro me to meet a guru.i'm very happy with your words.i've started learning stored procedures, perticularly, when i get a doubt, i'm expecting help fromyou.

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Functions

Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
PhilHow about giving us some specs first. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
Phil|||Phil,
It's my experience that scalar functions that do not access
any tables and that don't have long loops or recursion are not
going to run faster or slower if rewritten - the work they incur
is largely in the passing of parameters and returning of a result.
The only real optimization is to write the function inline. This
might be close:
convert(char(6),
dateadd(
month,
datediff(
month,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime),
cast(right(@.edition,6)+'01' as datetime)
)/@.period*@.period,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime)
),
112)
Steve Kass
Drew University
Phil wrote:

>Hi all,
>I have read of information about how to performance tune stored procedures,
>but is there any way of doing the same with functions or just some rules of
>thumb that I should adhere to, my code looks like this :-
>ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
>INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
>100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
>11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
>12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
>END
>
>Just wondering if there is a better way to write this or hints for
>performance.
>Thanks in advance
>Phil
>|||Hi Tom,
Sorry about that, getting a bit late should of really left this till the
morning where I was thinking a little better. Not really sure what you need
to know but I am using sql server 2000, the code is used for determining
contract start dates by passing in e.g. 200503 YYYY/MM dates, my code works
fine just wondering if there are any options that you can either turn on or
off that may affect how the funtion performs. The reason I ask is I know
that you can set things like SET NOCOUNT in stored procedures that does have
some affect on performance just wanting to know if there is anything similar
for funtions, I really should of made this more clear in my first post, sorr
y.
Thanks Phil
"Tom Moreau" wrote:

> How about giving us some specs first. ;-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
> Hi all,
> I have read of information about how to performance tune stored procedures
,
> but is there any way of doing the same with functions or just some rules o
f
> thumb that I should adhere to, my code looks like this :-
> ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
> INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
> 100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
> 11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
> 12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
> END
>
> Just wondering if there is a better way to write this or hints for
> performance.
> Thanks in advance
> Phil
>

function vs stored procedure

I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?i think its the ability to use the UDF inline that makes it easier.If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. not necessarily difficult but mnore coding.

heres an answer fromAdvanced SQL Server Stored Procedure Programming Chat

Q: (SRC): Are there inherent performance benefits to using Stored procedure vs. User Defined Functions?

A: There is no difference in performance. Because of the enforced prevention of side-effects in a UDF, only UDFs can be used in queries.

ofcors, UDFs have some limitations too( like you cannot use non-deterministic built-in functions..like getdate() .etc), which am assuming you are aware of.

hth|||To elaborate a bit, a UDF can also return tabular (in addition to scalar) data, and thus can be joined to.

While this might seem similar to a view, it has one substantila advantage: it can be parameterized! So for potentially enormous result sets, a UDF can improve efficiency enormously.|||You also have to be careful using UDFs in other queries cause you can end up calling the func for every row returned and that can be painful. Also there are a number of normal SQL statements you can't do in funcs.

function vs stored proc

Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIT depends on how you wish to use it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrmagoo" <-> wrote in message news:ef4%23ZWEZGHA.4688@.TK2MSFTNGP04.phx.gbl...d">
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>
>

function versus stored proc

I have to repost this, so I apologize if anyone responded. For some reason
the copy I sent is not being refreshed in my newsreader Inbox.
Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIt comes down to what kind of interface you want your clients to have to use
in order to get the data from the database. A stored procedure, to me,
"feels" more like a solid interface (from a client perspective) than does a
T-SQL function. Compare:
EXEC dbo.spr_PhraseCount @.Phrase='x'
to:
SELECT dbo.f_PhraseCount('x')
By using the function, you're exposing a little piece of SQL to the client
(i.e., the SELECT statement), whereas the stored procedure completely
encapsulates it. This is a very small issue in this case, but it might
illustrate the direction that you want to take the entire application in
from a data access point of view. Ultimately, you need to decide how much
you want to couple your client (the application tier) to the database.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
>I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Currently they give you the same result, but if you wanted to change things
slightly stored procedures have more flexibility regarding returning
recordsets
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Thanks Adam for your insightful response.
I had originally posted this but lost the thread in my newsreader. However,
I performed a groups.google.com search and found it. I had received a
response from someone named Tibor Karaszi, a SQL Server MVP, who answered my
exact same post with the very brief and useless reply of "IT depends on how
you wish to use it.". That's all he said, so I thank you for your
elaboration. I have participated in the MS newsgrouips for a long time, and
I wish MS would award the MVP status to people like you who take the time to
give people like me information I can use. Whereas Tibor gave me such
pathetically little information that I can do nothing with, and I wonder why
he even bothered to spend what must have been 10 seconds replying to such a
meaningless question to him (it seems), you have provided me information
that I can use.
Thank you.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> It comes down to what kind of interface you want your clients to have to
use
> in order to get the data from the database. A stored procedure, to me,
> "feels" more like a solid interface (from a client perspective) than does
a
> T-SQL function. Compare:
> EXEC dbo.spr_PhraseCount @.Phrase='x'
> to:
> SELECT dbo.f_PhraseCount('x')
> By using the function, you're exposing a little piece of SQL to the client
> (i.e., the SELECT statement), whereas the stored procedure completely
> encapsulates it. This is a very small issue in this case, but it might
> illustrate the direction that you want to take the entire application in
> from a data access point of view. Ultimately, you need to decide how much
> you want to couple your client (the application tier) to the database.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||Thank you.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:mbCdnaLFI9MmDtXZRVny1A@.bt.com...
> Currently they give you the same result, but if you wanted to change
things
> slightly stored procedures have more flexibility regarding returning
> recordsets
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||I'm sorry to hear that you weren't pleased with Tibor's reply -- he is
generally a great source of knowledge on all things SQL Server related, and
he's helped me many times in these groups and elsewhere.
As for MS awarding MVP to people like me, it does happen -- I've been an MVP
since 2004 ;-)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message
news:%23B59gVWZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader.
> However,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
> my
> exact same post with the very brief and useless reply of "IT depends on
> how
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time,
> and
> I wish MS would award the MVP status to people like you who take the time
> to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder
> why
> he even bothered to spend what must have been 10 seconds replying to such
> a
> meaningless question to him (it seems), you have provided me information
> that I can use.
> Thank you.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> use
> a
> news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> reason
> stored
> @.Phrase
>|||mrmagoo wrote:
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader. However
,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
my
> exact same post with the very brief and useless reply of "IT depends on ho
w
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time, an
d
> I wish MS would award the MVP status to people like you who take the time
to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder w
hy
> he even bothered to spend what must have been 10 seconds replying to such
a
> meaningless question to him (it seems), you have provided me information
> that I can use.
>
There are very few people here who are more helpful than Tibor. I
expect his reply was intended to prompt you for more information.
Please remember that any help you do get is for free. If you want a
guaranteed level of service you'd better go hire someone. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am aware that people here contribute for "free", however MVPs get MSDN,
don't they?, so contributions are not without remuneration. Non-MVPs are the
only ones who truly work for free, and I doubt that an MVP wannabe would get
to be an MVP by answering posts that way.
Your point is well taken, however. I did not mean to offend Tibor, but I did
intend to point out that the reply was not helpful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145642545.679167.247350@.t31g2000cwb.googlegroups.com...
> mrmagoo wrote:
However,
answered my
how
and
time to
why
such a
> There are very few people here who are more helpful than Tibor. I
> expect his reply was intended to prompt you for more information.
> Please remember that any help you do get is for free. If you want a
> guaranteed level of service you'd better go hire someone. :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Function to validate e-mail address

does anyone have a funciton or Stored PRoc to validate internet e-mail addresses?

I have a table of e-mail addresses, many of which are junk (the web programmer did not enforce validation on the survey form) and now i have to filter the junk out of the list.

Can any one help with this? Is there a UDF or SP floating out there I could use?These links may give you some ideas:

One that does it in the database

One that does it in C#

Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f

Function sequence error with bcp call from a stored procedure

I have a stored procedure that contains a series of bcp calls to export data
.
The bcp calls are executed by xp_cmdshell and are all the same (other than
the table name of course).
I have SET NOCOUNT ON as the first line in the stored procedure and it is
not returning any data. The bcp calls are in the format of:
bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber' queryout
SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
exec @.RC = master..xp_cmdshell @.Query
On one of the bcp shells I'm receiving the following errors but not on
others. The errors are:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL
Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
[Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation failed.
Failed to get the call stack!
ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
@.RETURN_VALUE = N/A
The stored procedure is being called from a .Net class library and is
executing on SQL Server 2000 with following version info:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Any ideas would be appreciated. Thanks.AV normally means a bug in sqlserver code. I would suggest you contact PSS
regarding this.
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>I have a stored procedure that contains a series of bcp calls to export
>data.
> The bcp calls are executed by xp_cmdshell and are all the same (other than
> the table name of course).
> I have SET NOCOUNT ON as the first line in the stored procedure and it is
> not returning any data. The bcp calls are in the format of:
> bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber'
> queryout
> SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
> exec @.RC = master..xp_cmdshell @.Query
>
> On one of the bcp shells I'm receiving the following errors but not on
> others. The errors are:
> SQLState = S1010, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
> NULL
> Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
> [Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation
> failed.
> Failed to get the call stack!
>
> ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
> Server: Msg 11, Level 16, State 1, Line 0
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
> your network documentation.
> @.RETURN_VALUE = N/A
> The stored procedure is being called from a .Net class library and is
> executing on SQL Server 2000 with following version info:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> Any ideas would be appreciated. Thanks.
>|||AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you elaborat
e?
"oj" wrote:

> AV normally means a bug in sqlserver code. I would suggest you contact PSS
> regarding this.
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>
>|||Jacob,
Sorry. I mean Access Violation and Product Support Services.
http://support.microsoft.com/oas/de...aspx?gprid=2852
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
> AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you
> elaborate?
> "oj" wrote:
>|||Thanks, I'll look into that.
"oj" wrote:

> Jacob,
> Sorry. I mean Access Violation and Product Support Services.
> http://support.microsoft.com/oas/de...aspx?gprid=2852
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
>
>

Function or SP for date conversion

i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?

SELECT CAST ( 38665 as datetime)

function in a constraint

I want to make sure that only month end dates make it into the table. I coul
d
put if conditions in my insert/update stored procs or I could do that with a
constraint.
Is it possible to put a constraint in table that checks for certain
condition using user defined functions?
for example:
CREATE TABLE t1
(
c1 int,
mydate datetime
CONSTRAINT myconstraint month_end_check_function(mydate)
)
Where month_end_check_function is a function that returns true if c2 was
month end date such as 7/31/05 and would return false if c2 was 7/3/05.
TIA...sqlster,
SQL Server does support functions within a check constraint.
HTH
Jerry
"sqlster" <nospam@.nospam.com> wrote in message
news:48C643BE-FA2E-44C3-88DE-2C6596CDEAD1@.microsoft.com...
>I want to make sure that only month end dates make it into the table. I
>could
> put if conditions in my insert/update stored procs or I could do that with
> a
> constraint.
> Is it possible to put a constraint in table that checks for certain
> condition using user defined functions?
> for example:
> CREATE TABLE t1
> (
> c1 int,
> mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
> Where month_end_check_function is a function that returns true if c2 was
> month end date such as 7/31/05 and would return false if c2 was 7/3/05.
> TIA...
>|||On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:

>I want to make sure that only month end dates make it into the table. I cou
ld
>put if conditions in my insert/update stored procs or I could do that with
a
>constraint.
>Is it possible to put a constraint in table that checks for certain
>condition using user defined functions?
>for example:
>CREATE TABLE t1
>(
>c1 int,
>mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
>Where month_end_check_function is a function that returns true if c2 was
>month end date such as 7/31/05 and would return false if c2 was 7/3/05.
>TIA...
>
Hi sqlster,
Though Jerry is right - functions are permitted in a CHECK constraint -,
you don't need one here:
CREATE TABLE T1
(c1 int NOT NULL PRIMARY KEY,
mydate datetime,
CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nice job Hugo - thinkin outside the box!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8vvdk159t9sefnbp02hn1rgsevlhqhdtu8@.
4ax.com...
> On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:
>
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Excellent (and creative) solution!
Gert-Jan
Hugo Kornelis wrote:
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten