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

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

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
>

Full-text Search Assistance

Hi there:

We've been using full-text catalogs and some SQL stored procedures to return a ranked set of search results for a while now in 2000. Now that we've moved to 2005, I wonder if anyone knows of any good resources for returning ranked search results within 2005?

Ahh, but that was but part one of my question.

We have a table that need several fields indexed and searched, for our purposes we'll just say they're two varchar(1024) fields, a date field, and a two smaller varchar(128) fields. Would it make better sense, and return better search results, if we created a new ntext field named "SearchResults" and then wrote all of the data from the fields above to that single field for each record? W're then searching against only one field, and each result can be weighted for proximity.

Perhaps I'm just looking for suggestions on the best search methodology to apply against a scenario like I outlined above, when we need to search several fields against the same search criteria.

Any assistance you can give me would be great.

Thnks!

Brad

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20

Hi,
read this

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20