Question about returning a smalldatetime from a Function

I've been working this for a while. Kind of new to SQL Server
functions and not seeing what I am doing wrong. I have this function

CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS smalldatetime AS
BEGIN
Declare @.retVal varchar(10)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

The error I get is
Server: Msg 296, Level 16, State 3, Procedure test, Line 6
The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value.

1) I tried declaring @.retVal as a smalldatetime and get the error "Must
declare the variable '@.retVal'.'
2) If I run that same query in query analyzer (manually inserting the
parm) it returns 11/14/2006. That's what I want.

If I change the function to this and run it
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS varchar(50) AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

It now works but the return value is Nov 14 2006 12:00AM

What am I doing wrong?

TIASQL Server (alderran666@.gmail.com) writes:
> I've been working this for a while. Kind of new to SQL Server
> functions and not seeing what I am doing wrong. I have this function
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS smalldatetime AS
> BEGIN
> Declare @.retVal varchar(10)
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
> The error I get is
> Server: Msg 296, Level 16, State 3, Procedure test, Line 6
> The conversion of char data type to smalldatetime data type resulted in
> an out-of-range smalldatetime value.
> 1) I tried declaring @.retVal as a smalldatetime and get the error "Must
> declare the variable '@.retVal'.'
> 2) If I run that same query in query analyzer (manually inserting the
> parm) it returns 11/14/2006. That's what I want.

What data type is t_master_schedules.date? If it is varchar(10), and
it returns 11/14/2006, the query looks, eh, funny to me. First,
11/14/2006 does not look like a date to me. :-) But even if I assume
that 11 is supposed to be a month, it seems strange that you consider
2006-11-14 to be less than 2004-12-12. Shouldn't your query read
MIN(convert(smalldatetime, [date], 101) in such case?

Alternatively, the column is datetime or smalldatetime, but in such
there is no need to incolve varchar at all.

Anyway, when I try:

select convert(smalldatetime, '11/14/2006', 1)

I get:

Server: Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

Whereas

select convert(smalldatetime, '11/14/2006', 101)

returns 2006-11-14.

> If I change the function to this and run it
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS varchar(50) AS
> BEGIN
> Declare @.retVal varchar(50)
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
> It now works but the return value is Nov 14 2006 12:00AM

Here you are first converting to smalldatetime, and then convert
back to varchar without any format specification, why you get this
default format.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 6 Jun 2006 01:50:03 -0700, SQL Server wrote:

(snip)
>1) I tried declaring @.retVal as a smalldatetime and get the error "Must
>declare the variable '@.retVal'.'

Hi SQL Server,

And yet, that is exactly what you should do. Never convert unless you
have to.

The error message you got is not a result of declaring @.retVal as a
smalldatetime, but a result of "something" that was off in the code when
you tried that. Unfortunately, you didn't post that version of the code,
so I can't tell you what went wrong. Maybe, if you still have tat
version archived, you could post it here?

Meanwhile, try if this works:

CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS smalldatetime
AS
BEGIN
DECLARE @.retVal smalldatetime
SELECT @.retVal = MIN([date])
FROM dbo.t_master_schedules
WHERE event_id = 13
AND group_ = @.Group
RETURN @.retVal
END

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> The error message you got is not a result of declaring @.retVal as a
> smalldatetime, but a result of "something" that was off in the code when
> you tried that. Unfortunately, you didn't post that version of the code,
> so I can't tell you what went wrong. Maybe, if you still have tat
> version archived, you could post it here?
> --
> Hugo Kornelis, SQL Server MVP

This is okay
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS varchar(50) AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

This is okay too (change Returns from varchar(50) to datetime)
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS datetime AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

But change it to this
This is okay too (change Returns from varchar(50) to datetime)
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS datetime AS
BEGIN
Declare @.retVal datetime
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

Here is a link to a screen capture of the error.
http://i12.photobucket.com/albums/a...erran/error.jpg

the column [date] in the table t_master_schedules is a datetime.

I actually do want @.retVal to be a varchar because the end result
should be a string that shows the first date for a particular group and
the last date in a particular group. So I would be running a select
with a Max([date]) and returning a string

11/14/2006 and 02/03/2007

The problem is that I am not able to get the date formated into the
mm/dd/yyyy format that I want.|||SQL Server (alderran666@.gmail.com) writes:
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS datetime AS
> BEGIN
> Declare @.retVal datetime
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
>...
> the column [date] in the table t_master_schedules is a datetime.
> I actually do want @.retVal to be a varchar because the end result
> should be a string that shows the first date for a particular group and
> the last date in a particular group. So I would be running a select
> with a Max([date]) and returning a string
> 11/14/2006 and 02/03/2007
> The problem is that I am not able to get the date formated into the
> mm/dd/yyyy format that I want.

If you want a string back, why do you then insist on converting to
smalldatetime? Should you not convert to char(10) and return char(10)?

Anyway, I would suggest that you scrap the function entirely. I don't
know where you use this function, but data access from scalar functions
should be avoided, as it can affect performance considerably if
you stick into a query. This is because the query more or less get
converted to a cursor behind the scenes. So it is much better to
integrate the logic in the main query.

As for the date formatting, you should avoid formatting dates in
SQL Server, but format them client side, so the the client's
regional settings are respected.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

> If you want a string back, why do you then insist on converting to
> smalldatetime? Should you not convert to char(10) and return char(10)?
..
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

All I want to know is how to return
08/29/2006

from
'2006-08-29 00:00:00.000'

Looking at the SQL Server Books Online help resource it appears to me
that the convert function should be able to do this. But this doesn't
work. Why not and how can I format that date the way I want in the
output. In VB I'd just use the format function. Is there something
similar in T-SQL?
print convert(datetime, '2006-08-29 00:00:00.000', 101)|||SQL Server (alderran666@.gmail.com) writes:
> All I want to know is how to return
> 08/29/2006
> from
> '2006-08-29 00:00:00.000'
> Looking at the SQL Server Books Online help resource it appears to me
> that the convert function should be able to do this. But this doesn't
> work. Why not and how can I format that date the way I want in the
> output. In VB I'd just use the format function. Is there something
> similar in T-SQL?
> print convert(datetime, '2006-08-29 00:00:00.000', 101)

That converts a string value to datetime. You want to convert a datetime
value to a string.

A datetime value is a internally a numeric value and does not have any
format. The format code in the above example tells SQL Server how to
interpret the string.

But as I said, while you can format date values to string in your SQL code,
you should avoid doing so. This should be done client-side, so that the
client's regional settings can be respected. I can tell you that if you
give me an app that spits out strings like 08/29/2006, you will have a bug
report back in ten seconds, because that is not a date as far as I'm
concerned.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

Your XPATH expression for the value method is incorrect. You can do one of the following:

select
Col.value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

-- or

select
Col.value('(./EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(./PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

See Books Online EVENTDATA topics for more examples. And check out the XQuery documentation also.

sql

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set.

I made that code but it returned null, any help please?

createtrigger DatabaseEvents
ondatabase
for ddl_database_level_events
as
--select eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)')as'Event Type'
,Col.value('(/PostTime/text())[1]','datetime')as'Post Time'
from @.data.nodes('/EVENT_INSTANCE')as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>

Question about Max function

I have table name datagraph
dat price
08/30/2004 23
09/1/2004 100
09/1/2004 21
09/1/2004 12
09/1/2004 32

I want to write Sql that show the highest price of today. I tried

sql="select max(price)from datagraph where dat like '%"&date()&"%')"

It does not work !!Which database server?|||To echo gannet, which database engine? It would also help if you could post the DDL for the table, since it looks like your dat column might be text of some kind instead of a date column.

-PatP|||it's access, and it's a text column

see http://www.dbforums.com/t1009057.html

gop373, use a DATE/TIME column

Question about Jump to URL function

Hi !

When using the Jump to URL function, is it possible to open the URL in a new page instance of doing a redirect?

Thanks !
I've found how to do it. I didn't know we could use javacript in report but it seems that it work so i added a window.open and now it's working fine.

|||

Another way you can do it is by adding rc:TargetLink=_blank to the link leading into your report. Then all Jump to URL hyperlinks in that report will open in a new window.

I like the javascript better though so you can mix pop-up and new window links.

|||

How exactly can I add that to my report? It is exactly the function I need - I tried adding it to the report link and it doesnt work for me - can you post the exact syntax? Im new to Report Services and REALLY need your help!

Sincerest of thanks!

Gina

|||

you can add javacript code. So you can do something like:

="_javascript:void(window.open('myPage.html', 'popup','location=no,toolbar=no,resizable=1'))"

You muste remove the _ after the ". This is just to be able to display all code.

|||

I keep getting Invalid Schema errors - URL's in reports may only use http://, https://, ftp://, mailto: or news:

Grrrrr......but thanks for the response! I will keep hacking at it......

Gina

|||

Try this it should work fine:

In the Navigate to URL field paste this:

="_javascript:void(window.open('http://www.google.com/', 'popup','location=no,toolbar=no,resizable=1'))"

Don't forget to remove the _ before the java script word. I've tried it and it work fine. Each time click on the texbox i've got a popup that open on google website.

If this doesn't work make sure that you have the SP1 version for reportServer.

|||Nope - still have the error - this is SQL RS 2000 SP1- thanks for all the help tho - Onward and Upward! AGH!

Question about Jump to URL function

Hi !

When using the Jump to URL function, is it possible to open the URL in a new page instance of doing a redirect?

Thanks !I've found how to do it. I didn't know we could use javacript in report but it seems that it work so i added a window.open and now it's working fine.|||

Another way you can do it is by adding rc:TargetLink=_blank to the link leading into your report. Then all Jump to URL hyperlinks in that report will open in a new window.

I like the javascript better though so you can mix pop-up and new window links.

|||

How exactly can I add that to my report? It is exactly the function I need - I tried adding it to the report link and it doesnt work for me - can you post the exact syntax? Im new to Report Services and REALLY need your help!

Sincerest of thanks!

Gina

|||

you can add javacript code. So you can do something like:

="_javascript:void(window.open('myPage.html', 'popup','location=no,toolbar=no,resizable=1'))"

You muste remove the _ after the ". This is just to be able to display all code.

|||

I keep getting Invalid Schema errors - URL's in reports may only use http://, https://, ftp://, mailto: or news:

Grrrrr......but thanks for the response! I will keep hacking at it......

Gina

|||

Try this it should work fine:

In the Navigate to URL field paste this:

="_javascript:void(window.open('http://www.google.com/', 'popup','location=no,toolbar=no,resizable=1'))"

Don't forget to remove the _ before the java script word. I've tried it and it work fine. Each time click on the texbox i've got a popup that open on google website.

If this doesn't work make sure that you have the SP1 version for reportServer.

|||Nope - still have the error - this is SQL RS 2000 SP1- thanks for all the help tho - Onward and Upward! AGH!sql

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?
There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I think that it is a flaw. I tried casting '.' to integer and decimal and
got an error.
Russel Loski, MCSD.Net
"Izzy" wrote:

> SQL Server 2000
> SELECT ISNUMERIC('.')
> Returns 1
> Why would a single period evaluate to being numeric? Is this a flaw?
>
|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definition
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I suggest the following article:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '£', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definition
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I suggest the following article:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I think that it is a flaw. I tried casting '.' to integer and decimal and
got an error.
Russel Loski, MCSD.Net
"Izzy" wrote:

> SQL Server 2000
> SELECT ISNUMERIC('.')
> Returns 1
> Why would a single period evaluate to being numeric? Is this a flaw?
>|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definitio
n
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I suggest the following article:
http://classicasp.aspfaq.com/genera...-isnumeric.html
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/genera...-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --sql

Question about IIF() function?

I tried to replace the value on rows using the IIF() function and it did not work. The following is the mdx code.

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified"))) ON ROWS FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS FROM [AP Statistics by Patient Type])

What is wrong with the IIF() function....

IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

Thanks

The problem is that you are returning strings and the row axis needs a set of members. You would need to put your IIF in a calculated measure on the columns and also define a set of members on the rows.

At a guess it would probably need to look something like the following (my changes in red).

WITH MEMBER [Measures].[ParameterCaption]

AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[PatientCalc] AS IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

SELECT {[Measures].[ParameterCaption]

, [Measures].[ParameterValue]

, [Measures].[ParameterLevel]

, [Measures].[PatientCalc]}

ON COLUMNS ,

[DIM_Patient].[In Out Patient].Members ON ROWS

FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS

FROM [AP Statistics by Patient Type])

Question about functions

Hi,
I need to call a function in a sql query in a stored procedure to
calculate time differences between various dates. I have a function
that uses a cursor to sum up the totals of these numbers, but it runs
very, very slowly. I can accomplish the same results without a cursor
by using a temporary table and several queries, but when I try to put
this in a stored procedure and call the stored procedure from the
function, I get the following error:
Only functions and extended stored procedures can be executed from
within a function.
Any suggestions?
Thanks,
Amy Bolden>> Any suggestions?
The message clearly states what you can do with a function. So you will have
to find an alternative to calling procedures from functions. Either make the
calling routine a procedure or make the called routine a function.
Anith|||You must supply more information about that, did you try to use
datediff in a correlated query (don=B4t know where you get the data from
?).
Jens Suessmeyer.|||Sorry, I thought maybe an explanation would be enough.
Here is the top query in the stored procedure. The @.StartDate and
@.EndDate are
parameters that are supplied by a web application:
SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
POSUM.UserName,
TT.UserId,
dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
FROM vw_TTLH TT INNER JOIN
UserMaster POSUM ON TT.UserID = POSUM.UserNum
WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
Here is the function that calculates all the time spent in a truck for a
particular day:
CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
@.ActivityDate DateTime = NULL)
RETURNS int
AS
BEGIN
DECLARE @.TotalTimeInTruck INT
Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
RETURN @.TotalTimeInTruck
END
Here is the stored procedure that I am trying to call from the function
to get the total time
spent in a truck on a single day:
CREATE PROCEDURE spGetTotalTimeInTruck
@.UserID INT,
@.ActivityDate VARCHAR(10),
@.TotalTimeInTruck INT OUTPUT
AS
DECLARE @.ActivityDatePlusOne VARCHAR(10)
SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
CREATE TABLE #tempTruck
(
SeqNum int NULL,
InTruck datetime NULL,
OutTruck datetime NULL
)
INSERT INTO #tempTruck
(OrderNumber, InTruck)
SELECT OrderNumber, DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND ActID in (200)
UPDATE #tempTruck
SET OutTruck = (
SELECT DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND OrderNumber = #tempTruck.OrderNumber + 1)
SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
DROP TABLE #tempTruck
RETURN @.TotalTimeInTruck
Thanks,
Amy Bolden
*** Sent via Developersdex http://www.examnotes.net ***|||Are you talking about something similar to this?
http://www.eggheadcafe.com/articles/20030626.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
Free Source Code for ADO.NET Object Mapper To DataBase Tables And Stored
Procedures
http://www.eggheadcafe.com/articles...e_generator.asp
"Amy" <abolden@.eastridge.net> wrote in message
news:1127400856.741551.164370@.z14g2000cwz.googlegroups.com...
> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi
I think if you omitt using of CURSUR in your Function then it will be some
more faster. If you are using cursor for itration purpose than I have an Ide
a
that may help you.
There is no concept of Arrays in SQL Server I think so, but we can create
our own Psedu Arrays, and we can itrate in these arrays.
Define a local variable as varchar and insert the Primary key in the varable
COMMA seprated.
And then by a while loop get the ID and select the field(s) you want from
the orignal table and colculate it.
Run This code It may open your Mind
Declare @.var varchar(100)
SET @.var = '120,20,23,32,23234,,3,5,6,'
WHILE @.var <> ''
BEGIN
Declare @.id int
SET @.id = CAST(SUBSTRING(@.var,0, CharIndex(',', @.var,0)) as int)
SET @.var = SUBSTRING(@.var, CharIndex(',', @.var,0)+1, LEN(@.var))
PRINT @.id
END
________________________________________
__________________
"Amy" wrote:

> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>

question about excute store procedure

Hi, all

i create a function("changefilepermission") to execute a procedure ("grant_file_access") to change the file permission. i click the permission checkbox to change new permission, after that i will click the submit button to update change to the database. but it does not change to the database. this is my part of code. is anybody can give me a help?

thanks in advanced!!!!!!!!!

Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
 Dim FileTitle As New String("")
 Dim FileName As New String("")
 FileName = DGPermission.Items.Item(0).Cells(0).Text 'retrieve filename from datagrid
 FileTitle = DatabaseCommand(userid, "fa_title", filename) ' retrieve the filetitle from table
 Dim permission As Char ' set the permission value
 If (CkRead.Checked) Then
 Permission = "r"
 ElseIf (CkWrite.Checked) Then
 Permission = "w"
 ElseIf (CkExecute.Checked) Then
 Permission = "o"
 End If
 Try
 ' call the store procedure function by passing 4 value
 ChangeFileAccess(userid, FileName, FileTitle, Permission) '
 Catch ex As Exception
 lblErrorMsg.Text = ex.ToString
 End Try
End Sub
' execute store procedure function
Public Sub ChangeFileAccess(ByVal userid As String, ByVal DiskFilename As String, ByVal Title As String, ByVal Access As Char)
 Dim UpdateCommand As SqlCommand
 UpdateCommand = New SqlCommand
 With UpdateCommand
 .Connection = SqlConnection
 .CommandType = CommandType.StoredProcedure
 .CommandText = "Grant_File_Access"
 .Parameters.Add("@.vu_id", SqlDbType.VarChar, 20).Value = userid
 .Parameters.Add("@.DiskFilename", SqlDbType.VarChar, 64).Value = DiskFilename
 .Parameters.Add("@.Title", SqlDbType.VarChar, 50).Value = Title
 .Parameters.Add("@.Access", SqlDbType.Char, 1).Value = Access
 End With
 Try
 UpdateCommand.Connection.Open()
 UpdateCommand.ExecuteReader() ' call the store procedure
 UpdateCommand.Connection.Close()
 Catch ex As Exception
 lblErrorMsg.Text = ex.ToString
 End Try
 End Sub

What does your stored procedure look like?

Terri