General network error. Check your network documentation ADO.Net error

Hi
I'm getting the General Network error, though not consistently. Stack
trace is below. Any fix for this?
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
TdsParserState state)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
col, Boolean& isNull)
at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
Object[] buffer, Int32[] map, Boolean useSQLTypes)
at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"RJN" <rjn@.yahoo.com> wrote in message
news:Oj67JKhZGHA.3832@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
>
1. Don't multipost.
2. This is probably one of
-a network error
-SQL Server box Crashing
-A severity 20+ error in SQL Server.
So check the SQL Server logs.
David|||Hi David
Sorry for the multipost. Since they are different forums, I thought
chances of getting an answer are more.
Which log file should I check? The one under "Microsoft SQL
Server\MSSQL\LOG" ?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***|||Read this KB Article
http://support.microsoft.com/defaul...Ben-us%3B821289 after
you are finished with the error logs
Nathan H. Omukwenyi
"RJN" <rjn@.yahoo.com> wrote in message
news:Oj67JKhZGHA.3832@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
>
> *** Sent via Developersdex http://www.examnotes.net ***

General network error. Check your network documentation - ADO.Net

Hi
I'm getting the General Network error, though not consistently. Stack
trace is below. Any fix for this?
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
TdsParserState state)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
col, Boolean& isNull)
at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
Object[] buffer, Int32[] map, Boolean useSQLTypes)
at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***Could you provide more information and maybe the code that throws that
exception because the error could occur for many reasons like:
When this happens, what is the state of your Sqlconnection object, your
physical network or the SQL Server computer?
You are trying to read the value of a column return by the Sqldatareader.
Does the column exist in the underlying datasource? What is the data type of
the column?
Nathan H. Omukwenyi
"RJN" <rjn@.yahoo.com> wrote in message
news:Ov80uEhZGHA.3480@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns
,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
> *** Sent via Developersdex http://www.codecomments.com ***|||RJN,
If you have selected "Force Protocol Encryption" on your SQL Server "Server
Network Utility" (meaning you are using an SSL connection) and you are
connecting from the SqlClient in the .NET Framework version 1.0 or 1.1 then
this KB will be of help:
http://support.microsoft.com/defaul...Ben-us%3B821289
Nathan H. Omukwenyi
"RJN" <rjn@.yahoo.com> wrote in message
news:Ov80uEhZGHA.3480@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns
,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
> *** Sent via Developersdex http://www.codecomments.com ***

General network error. Check your network documentation - ADO.Net

Hi
I'm getting the General Network error, though not consistently. Stack
trace is below. Any fix for this?
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
TdsParserState state)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
col, Boolean& isNull)
at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
Object[] buffer, Int32[] map, Boolean useSQLTypes)
at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
Thanks
*** Sent via Developersdex http://www.developersdex.com ***Could you provide more information and maybe the code that throws that
exception because the error could occur for many reasons like:
When this happens, what is the state of your Sqlconnection object, your
physical network or the SQL Server computer?
You are trying to read the value of a column return by the Sqldatareader.
Does the column exist in the underlying datasource? What is the data type of
the column?
Nathan H. Omukwenyi
"RJN" <rjn@.yahoo.com> wrote in message
news:Ov80uEhZGHA.3480@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***|||RJN,
If you have selected "Force Protocol Encryption" on your SQL Server "Server
Network Utility" (meaning you are using an SSL connection) and you are
connecting from the SqlClient in the .NET Framework version 1.0 or 1.1 then
this KB will be of help:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B821289
Nathan H. Omukwenyi
"RJN" <rjn@.yahoo.com> wrote in message
news:Ov80uEhZGHA.3480@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm getting the General Network error, though not consistently. Stack
> trace is below. Any fix for this?
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, TdsParserState state)
> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
> at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
> at System.Data.SqlClient.TdsParser.ReadBuffer()
> at System.Data.SqlClient.TdsParser.ReadByte()
> at System.Data.SqlClient.TdsParser.GetTokenLength(Byte token)
> at System.Data.SqlClient.TdsParser.ProcessColumnHeader(_SqlMetaData
> col, Boolean& isNull)
> at System.Data.SqlClient.TdsParser.ProcessRow(_SqlMetaData[] columns,
> Object[] buffer, Int32[] map, Boolean useSQLTypes)
> at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
> at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
> at System.Data.SqlClient.SqlDataReader.get_Item(String name)
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***

General network error. Check your network documentation

Hi We have a written a error log in Global.asax. which capture below mentioned error in the prodcution server. Same applications is working fine in some locations and some loactions we are getting the error.Any body knows why this error is occurs.Any body knows why this error is occurs. Error Message:General network error. Check your network documentation.Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin() at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, SqlConnectionString connectionOptions) at System.Data.SqlClient.SqlConnection.Open() at datalayer.sqldb.Execute(String ProcedureName, SqlParameter[] Parameters) at lms.User.GetUserDetails(String strUserid)ITs at LMS.Login.w_btn_Login_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

Hello my friend,

Are you using forms authentication? If so, are you using a SQL Server Express database to handle the users and roles? If so, you need to change this so it uses a full proper SQL Server, not the Express version. I made a guess that you may be doing this because the error involves OpenAndLogin(). The Express version is just for development, not recommended for production. If I have guessed incorrectly, then I am sorry.

Kind regards

Scotty

|||

Hi Scotty,

Thanks for your information. But We are using SQL Server Enterprise edition. its full version. Please let me know if you have any information.

sql

Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server
7.0 database?
select a.name from sysobjects a where a.type = 'V' and a.status > 0
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||This is actually what I was looking for. Many thanks

Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server 7.0 database
select a.name from sysobjects a where a.type = 'V' and a.status >
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
--
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||This is actually what I was looking for. Many thanks

Gather Meta Data

Hi,

I would like to prepare a data dictionary for my database (northwind).

I have framed the below SQL

SELECT
'NAME ' = a.name,
'DESCRIPTION' = b.value,
'Type ' = type_name(a.xusertype),
' ' AS 'Values',
'NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,
' ' AS 'PK',
' ' AS 'FK'
FROM
syscolumns a,
sysproperties b

WHERE
a.id = 2073058421 AND -- Customers Table
a.number = 0 AND
b.id = a.id AND
b.smallid = a.colid

ORDER BY a.colid

and the output would be:

NAME DESCRIPTION Type Values NULL PK FK
----- ----- ---- -- -- -- --
CustomerID Customer ID nchar
CompanyName Company Name nvarchar
ContactName Contact Name nvarchar X
ContactTitle Contact Title nvarchar X
Address Address nvarchar X
City City nvarchar X
Region Region nvarchar X
PostalCode Postal Code nvarchar X
Country Country nvarchar X
Phone Phone # nvarchar X
Fax Fax # nvarchar X

PK and FK is where I need to print whether the column is Primary Key or
Foreign Key.

If CustomerId is defined as Primary Key then PK should have X printed.
Thats the objective.

How will I accomplish this ?

Thanks in advance,
AnuThe following query is close to your requirements:

SELECTc.name AS ColumnName,
p.value AS ColumnDescription,
TYPE_NAME(c.xusertype) AS Type,
CASE WHEN c.isnullable=0 THEN ' ' ELSE 'X' END AS Nullable,
CASE WHEN EXISTS (
SELECT * FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid
WHERE o.xtype='PK' AND k.id=c.id AND k.colid=c.colid
) THEN 'X' ELSE ' ' END AS PK,
CASE WHEN EXISTS (
SELECT * FROM sysforeignkeys f
WHERE c.id=f.fkeyid AND c.colid=f.fkey
) THEN 'X' ELSE ' ' END AS FK
FROM syscolumns c
LEFT JOIN sysproperties p on p.id=c.id AND p.smallid = c.colid AND
p.name='MS_Description'
WHERE c.id = OBJECT_ID('Orders')
ORDER BY c.colid

with the following observations:

1. A column is not "defined as" primary key. It is "a part of" the
primary key (see the "Order Details" table in Northwind, which has a
compound primary key). The same goes for foreign keys (it is possible
to have a compound foreign key, although this is rarely encountered).

2. The sysproperties table should be LEFT JOIN-ed to the other tables
(because if you use an INNER JOIN, the columns that do not have a
description will be omitted). We should also filter for
p.name='MS_Description' because there may be also other properties in
the sysproperties table. Moreover, it should be noted that this table
is undocumented and we should use the fn_listextendedproperty function
(because using undocumented tables may cause problems in later versions
of SQL Server).

3. Use the OBJECT_ID function instead of using directly the ID of an
object.

4. Use ANSI-style joins, where the join condition is clearly placed in
the ON clause (instead of putting it in the WHERE clause, with the
other conditions).

5. I have no idea what you will put in the "Values" column (this column
seems very odd to me).

Razvan|||Razvan, nice query. Perhaps for Anuu's value column you could have
provided the Default Value when one existed. That is the only "value"
that would seem to make any sense.

IMHO -- Mark D Powell --|||Thanks, Razvan. The query is pretty simple and delivers what I need.sql

Functions and Execution Plan

The cost of query with usage of functions is as same as that of without
functions

In the below code, the query cost of insert is 0.02% and two select
statements costs same 0.04%

Declare @.t table(mydate datetime)
Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into @.t values(getdate())
set @.i=@.i+1EndSelect mydate from @.t
Select convert(varchar,mydate,112) from @.t

But I thought usage of convert function will take more query cost
What do you think of this?

MadhivananAdding a CONVERT() to the output is very little extra work, but I
suspect that you're referring to the fact that using a function on a
column in the WHERE clause can prevent MSSQL from using an index. That
can have a significant impact on the query plan, eg:

create table dbo.m (mydate datetime primary key)

Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into m values(getdate() + @.i)
set @.i=@.i+1
End

-- now run these two queries in the same batch

select *
from m
where mydate between '20100815' and '20100917'

select *
from m
where convert(char(8), mydate, 112) between '20100815' and '20100916'

On my test server, the first query takes 11% of the batch, the second
is 89% - although they are functionally equivalent, the first one can
do a seek in the clustered index, but the second must scan it. That's
not to say that functions in the SELECT will never affect the query
plan or cost, but when reviewing code it's probably more important to
look at the WHERE clause first.

Simon|||Thanks Simon

So only in Where condition it affects the performance and not in select
isnt it?

Madhivanan|||I'm sure that functions in the SELECT clause can affect the query cost
- nested string functions, nested CASE expressions, a scalar UDF which
looks up other tables etc. And all other things being equal, "SELECT
col1" will be more efficient than "SELECT somefunc(col1)", simply
because MSSQL has less work to do.

But the difference may be extremely small (as in your example), and I
guess that in most cases, any really big differences in performance
would come from functions in the WHERE clause, not the SELECT clause.
Of course there are many other reasons why a query might run slowly -
missing indexes, out-of-date statistics and so on - which have nothing
to do with functions at all, so if you have a performance problem with
a specific query, then it's best to start by looking at the query plan
before you think about how to re-write the code.

Simon|||Madhivanan (madhivanan2001@.gmail.com) writes:
> So only in Where condition it affects the performance and not in select
> isnt it?

What matters is that if you put an indexed column into an expression,
the index can no longer be used for searches.

If you have

where mydate between '20100815' and '20100917'

and there is an index on mydate, SQL Server can use that index to
find the matching rows. But if you say:

convert(char(8), mydate, 112) between '20100815' and '20100916'

that index can no longer be used, because that index holds datetime
values, and this is a string expression.

Note that if mydate is not indexed, the only cost for the function
call is the function call itself. In this case, it's not more expensive
that having it in the SELECT list. (Except that if it's in a WHERE
clause, it may be applied to more values.)

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

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

Madhivanan

Function with CASE Statements

Hi,

Uses: SQL Server 2000 + Winxp PRO;

I Created a Function as below:

<CODE>
CREATE FUNCTION [GetDestinationOperator] (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)

CASE LEN(@.Dest)
WHEN 13 THEN SET @.Op = 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN SET @.Op = 'MOBITEL'
WHEN '072' THEN SET @.Op = 'CELTEL'
WHEN '077' THEN SET @.Op = 'DIALOG'
WHEN '078' THEN SET @.Op = 'HUTCH'
ELSE SET @.Op = 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN SET @.Op = 'SLT'
WHEN '4' THEN SET @.Op = 'SUNTEL'
WHEN '5' THEN SET @.Op = 'LANKA BELL'
ELSE SET @.Op = 'LOCAL'
END
WHEN 3 THEN SET @.Op = 'INTERNAL'
ELSE SET @.Op = 'UNKNOWN'
END

RETRUN @.Op

END
<\CODE>

However when I checked the Code I written I get error Message as saying:

Error = 156:
Incorrect Syntax near the Keyword CASE
Incorrect Syntax near the Keyword WHEN
.
.
.

However when I executed the Query which I used with my database mentioned below, I get no errors:

<CODE>

SELECT CASE LEN(CalledNo)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (CalledNo , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (CalledNo , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END, CalledNo
FROM PABX
WHERE ExtNo = 204

<\CODE>

What might be the problem here?

Regards,

Hifni

Can you try this
CREATE FUNCTION GetDestinationOperator (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)
select @.op=
CASE LEN(@.Dest)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END

RETURN @.Op

END|||The case statement cannot stand alone even within a fuction. Create a variable called @.result and modify your fuction to Select @.result = <your case statement> then return the result.|||

Replace the CASE statment with a series of

IF condition

BEGIN

...

END

That should work

|||CASE is an expression not a control of flow statement. So you need to either assign the variable @.op using the value of the CASE expression or use IF...ELSE statements. Also, you should avoid writing scalar UDFs like this which perform lookup operations and use it in SELECT statements. You will get sub-optimal performance. It is based to model the lookup in a table and then perform a simple SELECT operation. This also gives added flexibility and performs better. Or you can inline the expression in the UDF in a view or TVF and use that instead.|||

Hi Everyone,
Thanks for all of your responses. For Eisa, I tried your sql and it did work wery well. Thanks for it and Umarchandra who gave a technical detail of it, thanks as well and for the rest.

Regards,

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
> > Below is the function that I am trying to create. I am failing in trying to
> > select from a random table. So I pass the table name in the function but it
> > won't let me select from that. Can someone help?
> >
> >
> > CREATE FUNCTION dbo.GetZoneID
> > (
> > @.ID int,
> > @.tableName varchar(50)
> > )
> > RETURNS money AS
> > BEGIN
> > DECLARE @.MasterZoneID INT,
> > @.Flag INT,
> > @.ZoneID money,
> > @.TempZoneID money,
> > @.TempTableName varchar(50)
> >
> > SET @.Flag = 0
> > SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> > SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
> >
> >
> > WHILE(@.Flag = 0)
> > BEGIN
> > --Failing on the @.tableName--
> > SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> > --Failing there--
> > IF(@.ZoneID = 0)
> > BEGIN
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> > END
> > ELSE
> > BEGIN
> > SET @.TempZoneID = @.TempZoneID + 0.0001
> > END
> > END
> >
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> >
> > END
> >
> >
> >
> >
> >|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID
.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
>|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
You can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>
|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
|||...or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

Function Help

I am using SQL 2000.
I have a table with Shipping Transactions in it (below).
Each record listed is a package, but the Amount shown is the total shipping
cost for all packages within a Shipment (Tracking)
I am trying to write a FUNCTION that will return the Total Shipment Amount
for a given PT (462714). Any Amount associated with a record that is voided
would not be included.
So, using the data below the function should return:
61.73
(61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
354925898 was voided so the 46.04 associated with it is excluded.
Recorded PT Order Tracking Total Weight Amount Void
1 462714 1589651 352414201 247 46.04 NO
2 462714 1589651 352414201 247 46.04 NO
3 462714 1589651 352414201 247 46.04 NO
19 462714 1589651 354925898 247 46.04 NO
20 462714 1589651 354925898 247 46.04 NO
21 462714 1589651 354925898 247 46.04 NO
22 462714 1589651 354925898 247 46.04 NO
23 462714 1589651 354925898 247 46.04 NO
24 462714 1589651 354925898 247 46.04 NO
25 462714 1589651 354925898 247 46.04 NO
26 462714 1589651 354925898 247 46.04 NO
27 462714 1589651 354925898 247 46.04 NO
28 462714 1589651 354925898 247 46.04 Y
29 462714 1589651 354925898 247 46.04 Y
30 462714 1589651 354925898 247 46.04 Y
31 462714 1589651 354925898 247 46.04 Y
32 462714 1589651 354925898 247 46.04 Y
33 462714 1589651 354925898 247 46.04 Y
34 462714 1589651 354925898 247 46.04 Y
35 462714 1589651 354925898 247 46.04 Y
36 462714 1589651 354925898 247 46.04 Y
37 462714 1589651 354111785 56 15.69 N
38 462714 1589651 354111785 56 15.69 NO
Any help would be greatly appreciated.
Thank youA number of ways, here is one (untested):
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
WHERE Void = 'N'
AND PT = 462714
GROUP BY order
) x
(Try and get the query itself working before incorporating it into a
function. Much easier to debug in QA that way, imho.)
A
"Kevin L" <no_spam@.not_real_email.com> wrote in message
news:%23yxDhihPGHA.720@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total
> shipping cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is
> voided would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>|||There is some duplicate data (as far as the fields you are looking at for
returning the total) - the following function weeds through them to return
the sum. I might suggest finding some relation to this table to filter
rather than having to use the distincts in the function. This should get yo
u
started:
CREATE FUNCTION [dbo].[GetPTTotal] (
@.PT int
)
RETURNS money AS
BEGIN
declare @.amount money
declare @.trackingamounts table (tracking int, amount money, void bit default
0)
--grab all the records which might indicate a valid amount
insert @.trackingamounts (tracking, amount)
select distinct tracking, amount
from dbo.shippingtransaction
where PT = @.pt
and void like 'n%'
--go back and void any tracking number which has at least one void
update ta
set void = 1
FROM @.trackingamounts ta
join ( select distinct tracking, void
from dbo.shippingtransaction
where pt = @.pt
and void = 'Y'
) tab on ta.tracking = tab.tracking
-- output the sum of any remaining valid amounts
select @.amount = sum(amount) from @.trackingamounts where void = 0
RETURN @.amount
END
GO
"Kevin L" wrote:

> I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total shippin
g
> cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is voide
d
> would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>
>|||On Thu, 2 Mar 2006 12:51:27 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>A number of ways, here is one (untested):
>SELECT SUM(amount)
>FROM
>(
> SELECT order,amount = MIN(amount)
> FROM tablename
> WHERE Void = 'N'
> AND PT = 462714
> GROUP BY order
> ) x
>(Try and get the query itself working before incorporating it into a
>function. Much easier to debug in QA that way, imho.)
Hi Aaron,
Looking at the sample data Kevin posted, I suspect that it should be
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
AND PT = 462714
GROUP BY order
HAVING MAX(Void) < 'Y'
) x
One of the orders had 'NO' for voided in some rows and 'Y' in some
others, and he wanted to exclude the whole order.
BTW, Kevin: Please add a CHCEK constraint to the Void column so that
you'll have to deal with only one value for yes and one for no - this
mess with NO, N and Y (end maybe YES as well) all interspersed just
gives you unneeded problems.
Hugo Kornelis, SQL Server MVP|||> One of the orders had 'NO' for voided in some rows and 'Y' in some
> others, and he wanted to exclude the whole order.
Ah, good catch.

function

hi..
i have one holiday master where all holiday are defined..now in below function i want to add payoutday to my trade_day.and this date will insert into my funding table.but before that i have to check after adding payoutday to the trade_day, this date not fall in holiday list.if that day will holidaty than i have to take next working day.and if again next day also holiday than i want to skip that day also and take next working day...and again check for holiday so on.
how to do this?

[code]

ALTER FUNCTION dbo.NewPayOutDate(@.id numeric(9))
Returns datetime
as
Begin

DECLARE @.CALDATE int
DECLARE @.ACTDATE DATETIME
Declare @.adddate DATETIME
declare @.moreCALDATE int
Begin
SELECT @.CALDATE=payoutday ,@.ACTDATE=trade_date
FROM pruamc.Tbl_GroupMst GM
INNER JOIN pruamc.Tbl_Redemption_UploadDetails (NOLOCK)
ON
GM.Group_Name = pruamc.Tbl_Redemption_UploadDetails.scheme_group
WHERE pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.id
SET @.adddate = dateadd(dd,@.CALDATE,@.ACTDATE)
SELECT @.moreCALDATE=count(*) from pruamc.Tbl_holidaymst where holiday_date between @.ACTDATE and @.adddate
END
set @.adddate = dateadd(dd,@.moreCALDATE,@.adddate)
RETURN @.adddate
END

[/code]

Moving to the Transact-SQL forum.|||

i m sorry ...thanx for

Moving to the Transact-SQL forum.

but any solution about my problem?

thanx a lot

|||

The following function will be help you to get the next working day...

Code Snippet

Create Function GetWorkingDay(@.Date as DateTime)

Returns DateTime

as

Begin
Declare @.Holiday as DateTime;
Select @.Holiday = holiday_date From Tbl_holidaymst Where holiday_date = @.Date;

If @.Holiday is Null
return @.Date;

return dbo.GetWorkingDay(DateAdd(DD, 1, @.Date));

End

Output:

if 1/1/2007 & 1/2/2007 is holiday

Select dbo.GetWorkingDay('1/1/2007') => 1/3/2007

Select dbo.GetWorkingDay('12/31/2006') => 12/31/2007

|||great thanx a lot..manid

Full-text Search with CONTAINSTABLE is slow for first time

I have a Full-text Search query as mentioned below.
SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
The table, TABLE_NAME contains around 800 rows and the column COL_Name is
data type of nvarchar(255).
The above query taking around 60 seconds when i execute it very first time.
And in next sub-sequent executions it is taking less than 1 second.
If i execute the same query after 30 minutes (without performing any thing),
in the first time it is taking again 60 seconds.
What whould be the problem here?
Thanks,
Naveen
does this apply?
http://support.microsoft.com/kb/915850
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Naveen Kumar" <Naveen Kumar@.discussions.microsoft.com> wrote in message
news:87A1EE89-C7EC-4290-9DBA-F383396AB7A0@.microsoft.com...
>I have a Full-text Search query as mentioned below.
> SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
> N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
> The table, TABLE_NAME contains around 800 rows and the column COL_Name is
> data type of nvarchar(255).
> The above query taking around 60 seconds when i execute it very first
> time.
> And in next sub-sequent executions it is taking less than 1 second.
> If i execute the same query after 30 minutes (without performing any
> thing),
> in the first time it is taking again 60 seconds.
> What whould be the problem here?
> Thanks,
> Naveen
>