Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do I need to verify that NewID() returns a unique GUID?

I'm migrating a web based system to SQL server. I'm planning on using the SQL server function NewID() to create unique keys for many of my records in many different tables. I'm just wondering if NewID() is guaranteed to return a value that does not already exist in my database. I mean obviously once you have a certain number of records (a hell of a lot) you'd be breaking the odds to never come up with a duplicate.

Do I need to make sure the result of NEWID() doesn't already exist?

Thanks

No this algorithm is guaranteed to always return a unique value. At least for a really, really long time,

Hope this helps,

Do I need SSIS to process cube?

There is a function called "proactive caching" in Analysis services. It can:
-Automatic synchronization with the relational database
-No more explicit "cube processing

But I cannot have the latest data in the cube even I set the proactive mode as "real time"

Do I need SSIS to process cube in this case?

Following is the procedures I have done:
1. test the data
1.1 use the bi dev studio to browser the cube, ensure no new data are there
1.2 process the the cube and browser the data, ensure new data are there
1.3 delete new data from source database and reprocess the cube, ensure no new data are there
1.4 add new data again

2. configure the proactive setting of cube
2.1 use sql server management studio to open the cube and open the properties window
2.2 in the option of "proactive caching" select "low-latency MOLAP" (even real-time ROLAP later), then click ok

3. configure the proactive setting of cube
3.1 open the patitions view properties window

3.2 in the option of "proactive caching" select "low-latency MOLAP" (even real-time ROLAP later), then click ok

3.3 in the notification tab, select "sql server " and specifiy tracking tables to the "fact table", which is a view to get data from real fact table.

4. wait a period of time...

5. test the data again
5.1 use the bi dev studio to browser the cube, but no new data are there (even I selected real-time ROLAP later). I even tried the reconnect and refresh options in the tool bar

So my questions are :
1. Did I do the right thing to achieve the target "Automatic synchronization with the relational database "

2. Can I monitor the procedure of synchronization, such as monitoring the log of processing, viewing the schedule setting and status of the process?



Thanks a lot!

You'd be better putting this on the SSAS forum.

-Jamie

|||

Sorry to post to the wrong place. But I did post it to the SSAS yesterday but nobody answered. Since it is urgent for me to solve the problem, I tried to post here.

I just wanted to know whether I should use "proactive caching" or use SSIS to solve the problem. Even a simple answer yes or no will be mostly appreciated.

Thanks!

|||

Well it depends on your requirements.

Using ProActive caching means there is an unknown latency between putting data into the warehouse and it appearing in the cube. But the good thing is you don't have to build extra functionality to do it.

The opposite is true of using SSIS. You process the cube when you need it to but you have to build, maintain and control that extra fucntionality.

Its your choice.

-Jamie

|||

Thanks Jamie.

That means I can use proactive caching to synchronize the data from database? Now the problem is how to make it work. At least I think I am in the right track.So I will put more time on it.

Thanks again.

|||

Sorry, I still cannot work it out. Can anybody help?

Thanks in advance!

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

sql

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

dm_db_index_physical_stats

Is this function as well as alter index reorganazi or rebuild backward
compatible to sql2k?
Thanks,
KOni.None of the new functions, views etc from SQL2005 will run on SQL2000 since
they don't exist there.
Andrew J. Kelly SQL MVP
"Koni Kogan" <kkogan@.haiint.com> wrote in message
news:%23crVjos7FHA.4076@.tk2msftngp13.phx.gbl...
> Is this function as well as alter index reorganazi or rebuild backward
> compatible to sql2k?
> Thanks,
> KOni.|||No, but if you really need it, then try scripting it from 2005 and
re-creating in the 2000 master database; it's just a function. This sounds
like it probably references system tables, which as subject to change from
one version of SQL Server to the next, so use at your own risk.
"Koni Kogan" <kkogan@.haiint.com> wrote in message
news:%23crVjos7FHA.4076@.tk2msftngp13.phx.gbl...
> Is this function as well as alter index reorganazi or rebuild backward
> compatible to sql2k?
> Thanks,
> KOni.

Division operator

I'm trying to perform a simple mathematical operation on an integer. The integer is the result of the COUNT() function, I need to divide this by a number, say 5, and then round up that number.

For example, if the result of the operation was 52.4, it would become 53, if it were 52.6 it would be 53 and If it were 52, it would remain 52.

Is this kind of functionality built in to T-SQL ?

The main problem I'm having is that the result of COUNT(blah)/5 returns an integer, and ignores the remainder.

Thanks,
PaulIf you look up "division" in BOL you will get a better understanding of what is happenig.

you might try "cast(count(blah) as float)/5"

Divide by Zero Error

I have a function that compiles a number of different aspects of a Select query and then creates some high-bred fields based on the result. The problem I have is that some of these values contain a zero or Null and therefore I am getting a divide by zero error. The three sums I have are as follows;

Number 1:-

SELECT........, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, BLAH BLAH FROM

Number 2 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc, BLAH BLAH FROM

Number 3 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, BLAH BLAH FROM

I have been informed that I need to use something like a CASE statement. What I want it to do is that if a ZERO or NULL is detected in any element of the source of the sum, then I want it to ignore the sum and just place the value of zero in whatever the AS xxxxxxxx dictates.

Could someone point me in a direction here or provide me with a little sample of how to go about doing this in a SELECT statement.

Thanks in advance

you can use case like this

select case when fnWTRalldata.floortotal = 0 then 0 else fnWTRalldata.floortotocc / fnWTRalldata.floortotal end as floorspaceperc from table

This should help you avoid division by zero errors.

|||

Thanks for your suggestion Andreas.

In addition to my previous post, would I be better to evaluate these conditions using a CASE statment in a second,third and fourth function and then deliver the results to the afore mention function, or could I use a nested statement within the existing SELECT statements?

Regards

|||

you can also use the iif in reporting services if that helps but it is a pain.

=iif(denominator > 0, numerator/iif(denominator >0, denominator, 1), 0)

|||

This is what I did to solve it. Found the answer elsewhere on the net

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal
else 0 end AS floorspaceperc,
case when FinalRtLsincSC <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1
else 0 end AS rentrolldiscperc,
case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal
else 0 end AS netrentpersqft, BLAH BLAH
FROM fnWTRalldata

Thanks for all your suggestions, the solution I found was down the road you were all heading in.

Thanks

Divide by zero

I'm using Toolman's Public function and it's working as far as the divide by
zero, however, I'm trying to get a percentage. When I pass the two
parameters to it, if the result is 100.% it shows up. If it's anything else,
it shows 0.0%. Here is the function and the code.
Public Function DivideBy(ByVal exp1, ByVal exp2)
If exp1 = 0 Then
DivideBy = 0
Else
DivideBy = exp1 / exp2
End If
End Function
=code.DivideBy
( Fields!Under120_1.Value, Fields!TotalRecs.Value )
Any help is appreciated.
--
Thanks!Sounds like a datatype issue, maybe there is a conversion to int in your
expression?
I tried the expression:
=Code.DivideBy(5, 100) * 100
and it doesn't return zero or 100.
I wrapped a CInt() around it and I get that behavior.:
=CInt(Code.DivideBy(5, 100))
The CInt forces either a 0 or 1, then it's converted to percent.
"Candy" <Candy@.discussions.microsoft.com> wrote in message
news:3BBFBF2E-EAEB-4103-BC70-E81C14AE537F@.microsoft.com...
> I'm using Toolman's Public function and it's working as far as the divide
> by
> zero, however, I'm trying to get a percentage. When I pass the two
> parameters to it, if the result is 100.% it shows up. If it's anything
> else,
> it shows 0.0%. Here is the function and the code.
> Public Function DivideBy(ByVal exp1, ByVal exp2)
> If exp1 = 0 Then
> DivideBy = 0
> Else
> DivideBy = exp1 / exp2
> End If
> End Function
> =code.DivideBy
> ( Fields!Under120_1.Value, Fields!TotalRecs.Value )
> Any help is appreciated.
> --
> Thanks!|||Try CDbl() instead of CInt()
Also, make sure the values being passed are not integer values

Divide by number of days in the year.

How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advanceHere's how to get the number of days in the current year:
select
case
when year (getdate()) / 100 % 4 = 0 then 365
when year (getdate()) % 4 = 0 then 366
else 365
end
However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly. However, you could feed a date to
the function.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advance|||For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>|||scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)
SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+
1
Regards
R.D
"scuba79" wrote:

> How can I create a function that will divide a parameter passed into the
> stored procedure by the number of days in the current year? I know that I
> can not just use 365 since it will not take into account leap years.
> Thanks in advance|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>

Distributed Transaction Coordinator

Hi,
I understand if I use Immediate update from Subscriber to Pulisher, i will
use the function of DTC, how about if I make a schedule e.g. every 1 hour,
when it runs (Push/Pull), does that use DTC also?
Second ? is do i have to use Remove Server to run or I can use Linked
Server?
Thanks so much
Ed
Ed,
the schedule refers to the distribution agent and applies to commands going
from the publisher to the subscriber. if you want to 'postpone' the relay of
commands from the subscriber to the publisher, you can use queued updating
subscribers. Replication uses remote servers, but linked servers can also be
used for the DTC (have a look at :
http://support.microsoft.com/default...b;en-us;274098 )
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)