Urgent - Replacement Of Number Function In Sql Server
Apr 10, 2001
Hey folks,
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ
View 1 Replies
ADVERTISEMENT
Sep 23, 2004
I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
View 1 Replies
View Related
Oct 10, 2006
Hi everyone, I have some code that we need to migrate to SQL Server2005 from 2000, and I have a webpage that upon viewing, fires a queryto the SQL server using ISS and ISAPI. The result set is formatted fordisplay using XSLT. But since ISAPI is deprecated in SQL 2005, I waswondering how to migrate this.Thanks,
View 5 Replies
View Related
Sep 16, 2015
I have a table with the following data;
CREATE TABLE #Tab (Data Varchar(100))
INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab
How do I replace every value before the '=' but leave the comma.
Here is what the final output should look like
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'
Select * from #TabFinal
View 9 Replies
View Related
Jan 7, 2008
Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.
View 2 Replies
View Related
May 23, 2006
Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
Thanks,
Dick Campbell
View 1 Replies
View Related
Feb 27, 2008
I have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.
Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.
Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.
Thanks in advance for your time.
-Mark
View 3 Replies
View Related
Apr 10, 2001
Hey folks,
Please give me the correct function name, otherwise please ignore in sending reply. I had used all the functions like identity, ident and so on. I need while selecting a querry, i need to generate serial numbers. I dont have identity column in my table. But, i need to generate serial numbers..
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ
View 1 Replies
View Related
Oct 10, 2007
Here is what I have
CDec(Sum(Val(Fields!Test1.Value))/(Val(CountRows("Group1"))*SomeNumber)*100%)
This works fine
but the problem is the number can be 25.325641. I only want to get 25 so I did this
Int(Sum(Val(Fields!Test1.Value))/(Val(CountRows("Group1"))*SomeNumber)*100%)
This did the trick but if the number is .255667 it will display 0.
here is what i want:
1-if the number is bigger than 0 i don't want to display the decimals for example:
25.01245 = 25
2-if the number is less than 0. I only want to display 2 decimal places
.256415 = .25
Thanks
View 4 Replies
View Related
Oct 16, 2007
Ok here is what I have
Department:A
Line 1 Name: George
Addresss date raise
125254 Test 10
125254 Test 10
125254 Test 10
125254 Test 10
Total: 80% 40
Name: Mik
Addresss date raise
125254 Test 10
125254 Test 10
125254 Test 10
Total: 70% 30
Name: Jonathan
Addresss date raise
125254 Test 10
125254 Test 10
125254 Test 10
Total: 70% 30
Line 10
Line 15 Total for this division: 33% ((100 )/3) 100
Department:B
¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦..
Here is what I did I have a list control that is grouped by department. this will get me the info from Line 1 to Line 10 then this list control is actually under another list control that is grouped by department to get the info from Line 15. I used this example for simplicity and what I am doing is fairly similar to this situation. My problem is, I need to find away to know how many totals I have to count the total for the entire department. In other words : from the example above how can I know I have to divide by 3 to get 33%. I can get the 100 without any problem but I cant tell what number I need to divided by. I tried to use Count(Name.Field) I was hoping this will give me 3 which indicates the divided number. But I was getting 7 for some reason.
How can I get the number of times the list control was implemented for one department which is in this case 3?
thanks
View 2 Replies
View Related
Dec 11, 2007
how can I write decode function in sql
such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource
such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
View 1 Replies
View Related
Mar 22, 2002
HI there,
Is there a function on transact SQL which test if a character belong to expression,I want to select from a table only the address that contain '@'.
Thanks in advance
Jalal
View 1 Replies
View Related
Jun 2, 2000
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS
IF UPDATE(colA)
select "updating col A"
IF UPDATE(colB)
select "updating col B"
go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go
insert into TestTrigger
(colA)
values
(1)
go
insert into TestTrigger
(colA, colB)
values
(2,3)
go
View 1 Replies
View Related
Sep 14, 2007
CAN SQL GURUS(PESO,MADHAVAN,VOLTER,KRISTEN) PROVIDE ME EXAMPLE OF CONVERT AND CAST FUNCTION CLEARLY WITH EXAMPLE
View 9 Replies
View Related
May 22, 2008
Hi all,
I AM USING ROW_NUMBER() FUNCTION IN MY QUREY IN SQL 2005 BUT IT IS GIVING ME PROBLEM IN SQL 2000.
SO CAN U TELL ME WHAT IS THE ALTERNATE WAY TO ACHIVE SAME RESULT.
MY QUREY IS:
select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.SalesPerson
CAN U TELL ME HOW CAN I REPLACE ROW_NUMBER() FUNCTION.
THANKS IN ADVANCE
-JOHN
View 3 Replies
View Related
Mar 29, 2005
This script is for an in-line table function, F_TABLE_NUMBER_RANGE, that generates a number table. The input parameters are the @START_NUMBER and @END_NUMBER. It returns a sorted result set containing all intergers from @START_NUMBER to @END_NUMBER inclusive.
This is an improved version of a script that I posted on a topic a few weeks ago. I modified it to cross join fewer tables based on powers of 16, instead of powers of 2, because I found that this compiled and ran much faster for small result sets (less than 10,000 rows).
This is the link to the other post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46252&whichpage=5
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists
(select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_NUMBER_RANGE]
GO
create function dbo.F_TABLE_NUMBER_RANGE
(
@START_NUMBERint,
@END_NUMBERint
)
/*
This function returns an integer table containing all integers
in the range of@START_NUMBER through @END_NUMBER, inclusive.
The maximum number of rows that this function can return
is 16777216.
*/
returns table
as
return
(
selecttop 100 percent
NUMBER = (a.NUMBER+b.NUMBER)+
-- Add the starting number for the final result set
-- The case is needed, because the start and end
-- numbers can be passed in any order
case
when @START_NUMBER <= @END_NUMBER
then @START_NUMBER
else @END_NUMBER
end
from
(
Selecttop 100 percent
NUMBER = convert(int,N01+N02+N03)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) a
cross join
(
Selecttop 100 percent
NUMBER =
convert(int,
(N01+N02+N03) *
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) b
where
a.NUMBER+b.NUMBER <
-- Total number of rows
abs(@START_NUMBER-@END_NUMBER)+1and
-- Check that the number of rows to be returned
-- is less than or equal to the maximum of 16777216
case
when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
then 1
else 0
end = 1
order by
1
)
GO
GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]
GO
-- Demo using the function to ruturn numbers 1 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)
-- Demo using the function to ruturn numbers -1500 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000)
CODO ERGO SUM
View 13 Replies
View Related
Nov 14, 2001
I have the following code in a stored procedure which needs modification as a result of the change in the input format for display_cur_price_tx field(price range).
------------------------------------------------------------------------------
old format for display_cur_price_tx is: $69 - $99
Old code for sp:
case charindex ('-', p.display_cur_price_tx)
when 0 then rtrim(convert (char(12), Convert (Numeric(7, 2), rtrim (substring (p.display_cur_price_tx, 2, len(p.display_cur_price_tx))))))
else rtrim (Convert(Char(12), Convert(Numeric(7 ,2), substring (p.display_cur_price_tx, 2, convert (int, charindex ('-', p.display_cur_price_tx) - 2))))) + ' - ' +rtrim(Convert (Char(12), convert(Numeric(7, 2), substring (p.display_cur_price_tx, charindex ('-', p.display_cur_price_tx) + 3, len(p.display_cur_price_tx)))))
endas price
------------------------------------------------------------------------------
New format for display_cur_price_tx is: $69-$99 9without the spaces inbetween).
What needs to be new code for sp?
If I still run the old code mentioned above now, I am getting the following error:
Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.
Any help on how to modify the above code to suit the new format for price range?? Any help is appreciated.
Thanks.
Sheila.
View 1 Replies
View Related
Jul 26, 2006
Creates a table of prime numbers, starting at 2 up to a maximum number.
Makes use of dbo.F_TABLE_NUMBER_RANGE, which is found here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
create function dbo.F_TABLE_PRIME(@MaxNumber bigint)
returns @t table (i bigint primary key) as
begin
insert @t select NUMBER from dbo.F_TABLE_NUMBER_RANGE(2, @MaxNumber)
declare @i bigint
set @i = 1
while 1 = 1
begin
select @i = min(i) from @t where i > @i
if @i is null or @i * @i > @MaxNumber break
delete @t where i > @i and i % @i = 0
end
return
end
go
select * from dbo.F_TABLE_PRIME(100000) order by iCan we improve on the speed?
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part.
View 20 Replies
View Related
Mar 27, 2008
Hi all
I have select statement which is using Function and I am passing arguments to the query
select * from funactin_name(@a,@b,@c)
It is giving an error saying is that "An insufficient number of arguments to function..."
some body help how to find out required arguments to the function by using QA.
View 4 Replies
View Related
Jan 23, 2008
Hi all,
I have a search function in my website, which is fully functionable.
But currently, this is the issue that I am having problem with.
For example:
I have only the name 'John' in my database.
When the user search for 'John Tan', no results is shown as there was no such name in the database
But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee"
So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in.
Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!
View 21 Replies
View Related
Sep 10, 2001
I have a phone number field.
I would like to remove the hypens and brackets.
Do anyone know of any functions, I can use to accomplish this??
Thanks in advance!
--Vic
View 1 Replies
View Related
Jul 18, 2002
hi and thanks for your help. Is there such a code that return whether or not a field is either positive or negative. If I have a field that contain numeric value. lets say -500
can I run a code that check the sign ?
thanks for your help
Ali
View 2 Replies
View Related
Jan 14, 2008
Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')
Thanks for all your help in advance!!!
View 6 Replies
View Related
Mar 10, 2008
I wrote a Scalar UDF in SQL2005 that returns an integer. I want to be able to display this integer in a ASP.Net 2.0 web page. I typically use a DAL for all data so I added an ObjectDataSource as a Qeury that contains only the UDF. How do I easily display the value in a Label Control or?
I have tried to use a Repeater with a label, a Formview with a Label, all to no avail. Any suggestions?
View 12 Replies
View Related
Jan 9, 2015
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table
tbl_1
(txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
[Code] ....
View 6 Replies
View Related
Aug 5, 2014
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
View 1 Replies
View Related
Apr 14, 2004
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:
SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2
I really appreciate any help anyone can provide.
Thanks,
DC Ross
View 5 Replies
View Related
Aug 14, 2014
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
View 0 Replies
View Related
Nov 26, 2013
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey
[code]....
View 3 Replies
View Related
Jun 22, 2015
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50
View 8 Replies
View Related
Nov 16, 2015
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
View 6 Replies
View Related
Aug 3, 2005
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
View 4 Replies
View Related
Oct 26, 2000
hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
View 1 Replies
View Related
