article row filter - 2 parameters

hello,

i need to filter an article based on a user-supplied datetime filter (the datetime parameter is specified by the subscriber just before replication). at the same time i need to filter again by user (different subscribers get different rows).

i already did the user-based filter using HOST_NAME( ). but the difficulty here (al least i think so) lies in passing 2 parameters to the filter. i cannot rely on using SUSER_SNAME to pass the user filter, because no one will want to create 500 user accounts. so i guess the only solution here is to pass both parameters using only HOST_NAME( ) and then write 2 splitting functions which uses HOST_NAME( ) as its parameter. am i right ?

publisher/distributor is sql server 2005, all subscribers use sql mobile.

TIA, kamil nowicki

Using datetime would not be recommended since it is not deterministic.

For eg:

lets say you have a filter to get rows being touched only in the last 7 days.

Initially you get all rows.

After 7 days having not touched any rows, you would expect the merge agent deletes the 7 rows which will not be the case.

I would advise you to use SUSER_SNAME() or HOST_NAME() to get specific rows and in addition use another column 'status' or something that you can set/reset according to your business needs.

|||

thanks for replying.

i think that i have to use a datetime filter because the parameter to this filter has to be dynamic (worst case scenario: each subscriber uses different filter parameter for each of his replication sessions). is there another way to accomplish this ?

also, is there a way to store something in SUSER_SNAME() like using SqlCeReplication.HostName to store something into HOSTNAME() ?

|||

If each session of the subscriber uses a different filter, you will get an error with mismatched partitions. You would need to reinitialize the subscriber in that case. Are you ready to reinitialize the subscribers for every sync?

But note that as I mentioned previously, you will not be able to rely on datetime filter. Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

|||

>> Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

but that would mean that i have to use a "fixed filter" (same "replicate from ..." date for every subscriber), wouldn't it ?

|||How about: login=SUSER_SNAME and status='Y'|||

We have a best practice article for time-based filtering, you may want to reference it, it's what Mahesh is talking about:

Best Practices for Time-Based Row Filters

http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

and how does that solve my scenario ? i want each subscriber to be able to choose a date ( "replicate from..." ) before each of his replication sessions. SP on the server will not know those dates when executed, so how am i supposed to update the "status" column ?

thanks for the link, i have read that before starting the thread.

|||

Are you saying that every time the subscriber syncs, it sends a new date and expects only releant rows? You cannot achieve this using the non-deterministic filter functions. If you are ready to reinitialize your subscriptions every time you sync, you may do that. Do a reinit on the publisher/subscriber, then send in the appropriate date as the hostname to get the relevant rows. However you wont be able to upload in this session because your filters may not match data that you want to upload.

|||

i took a different approach and now everything is working as it should. i wrote a SP on the backend server which updates every row in the filtered article for a given user (SP is parametrized with @.user nvarchar and @.filter datetime), changing rep_status tinyint column. the SP is called from subscriber on the distributor just before each replication session, so now the SP has all the info to update the filtered article (@.user and @.filter). article is filtered by rep_status.

the drawback is that the backend has to be put out of LAN to the internet (public IP), but later i will write a WebService so that all the data will pass through IIS and then the backend will be again NATed.

edit: and of course this works without subscription reinitialization :)

Arithmetic overflow error converting expression to data type datetime.

Ya ? am taking this error message in Asp.Net and i am not inserting a new row my database.

i have a databese and my fields

EmailID-->int

EmailAdress-->varchar(100)

DateTime-->datetime

IPAdress-->char(15)

Please help me,thanks

Most likely a disparity between the application date format and the date format SQL Server 'expects'. (Application passing the date parameter in the form of dd/mm/yyyy, and SQL Server accepting that as mm/dd/yyyy -and the ensuing date is invalid.

Have the application pass the date parameter in the ISO form of: yyyy/mm/dd OR yyyymmdd.

Arithmetic overflow error converting expression to data type datetime

I have a sql server 2000 database...using vb.net 2005... I have a form which allow user to find outstanding transaction by month selected in dropdownlist... If it matches the month it will populate out in datagrid, this date is store as dd/MM/yyyy... below is the following code:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf ddl_payment.SelectedItem.Text ="Outstanding"Then ddl_months.Visible =True datagrid1.Visible =False If ddl_months.Visible =True Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(mm,dateOfPurchase)='" + ddl_months.SelectedValue + "'AND balance > 0") bindData() datagrid1.Visible = False datagrid.Visible = True End If ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0") bindData1() datagrid.Visible =False datagrid1.Visible =True ddl_months.Visible =False Else datagrid.Visible =False datagrid1.Visible =False ddl_months.Visible =False End If End Sub

Below is the error:

Arithmetic overflow error converting expression to data type datetime.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.

Source Error:

Line 76: cmd.CommandText = sqlStatementLine 77: cmd.Connection = connLine 78: cmd.ExecuteNonQuery()Line 79: Line 80: reader = cmd.ExecuteReader

Source File:c:\inetpub\wwwroot\TAKA\App_Code\config.vb Line:78

Stack Trace:

[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 TAKA.config.dataReader2(String sqlStatement) in c:\inetpub\wwwroot\TAKA\App_Code\config.vb:78 TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\TAKA\PaymentStatus.aspx.vb:65 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I have another table which is staff table and it datefield is store in MM/dd/yyyy format

DATEPART returns an integer so wrappingddl_months.SelectedValue in quotes is probably pointless.

Seeing as it's a sql exception I would say it's extremely likely that the field "dateOfPurchase" is of the wrong type. Perhaps ur storing it as a string or an int...?

Also sql doesn't store dates in a locale specific format, the fact that u've said "I have another table whihc is staff table and it datefield is store in MM/dd/yyyy format" suggests to me ur storing ur dates as strings which will not work.

|||

Furthermore executing unparameterised queries against sql is slap-worthy offence. Use parameters... or get hacked.

|||

I waited so long for it approved zzz...ya...worldspawn thx for reply... 4get to said that my date is in nvarchar format... erm so any idea how to get the month in the database in this format? Or do I need change it to smalldatetime format? I need smth like For example my database has this date(23/10/2007, 21/09/2007,22/10/2007)

How can i retrieve by month, If ddl_months i selected '10' den that 2 records with month 10 will shown in datagrid...

Thank in adv

|||

If u want to keep it as a string u'll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.

But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text...?

If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not '10')

That's interesting about the approval. I used to get that but stopped seeing it. I assumed they'd just made everything unmoderated but it's probably just for user's that havent reached 'participant' status.

|||

Would it be safe if i jus change my database dateOfPurchase to smalldatetime format? Cos i received prompt when trying to save... It say data may be lost when trying to convert nvarchar to smalldatetime... below is a few line example of my code... I select a date from calendar and display it in a textbox then save all the data enter in database... will there be any serious problem if i change?

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

todayD.Text = Calendar1.TodaysDate().ToString("dd/MM/yyyy")

End Sub

PrivateSub Calendar1_SelectionChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Calendar1.SelectionChanged

'display selected date from calendar

tb_dop.Text = Calendar1.SelectedDate().ToString("dd/MM/yyyy")

EndSub

PublicSub AddTransaction1(ByVal dateOfPurchaseAsString,ByVal totalAmountAsString,ByVal paymentReceivedAsString,ByVal bAsString,ByVal receiptInvoiceAsString,ByVal remarksAsString,ByVal csngInvoiceAsString,ByVal transDetailsAsString,ByVal coNameAsString)

'declare the INSERT method for INSERT transaction

Dim strSQLAsString ="INSERT INTO custTransaction (dateOfPurchase, totalAmount, paymentReceived, balance, receiptInvoice, remarks, csngInvoice, transDetails, coName) VALUES ('" + dateOfPurchase +"'," + totalAmount +"," + paymentReceived +"," + b +",'" + receiptInvoice +"','" + remarks +"', '" + csngInvoice +"', '" + transDetails +"', '" + coName +"')"

con1.Open()

Dim cmdAsNew SqlCommand(strSQL, con1)

cmd.ExecuteNonQuery()

con1.Close()

EndSub

PrivateSub btn_add2_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btn_add2.Click

dateOfPurchase = tb_dop.Text

If tb_csngInvoice.Text ="None"Then

'check for duplication

If tb_receiptInvoice.Text = config.dataReader2("SELECT receiptInvoice FROM custTransaction WHERE receiptInvoice = '" + tb_receiptInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

ElseIf tb_receiptInvoice.Text ="None"Then

'check for duplication

If tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

EndIf

EndSub

Thank you for ur time

|||

Is this production database? Honestly i have no idea what would happen exactly but i think it's safe to say ur existing date fields will become incorrect and it may encounter and error trying to convert them and not even work... make sure sql server's date format is dmy and it should work without any problems, if it's still set to mdy u'll have big problems.

Also if you want to display a date in the form dd/MM/yyyy use the inbuilt ToShortDateString method which will convert the date to that format (as long as ur server is configured to use that dateformat by default).

Please read a tutorial on how to property construct a sql command using parameters and not string concatenation. String concatentation leaves u open to sql injection attacks... plus it's just crappy.

|||

Hi, worldspawn can i know how u convert the sql server to display date in dd/MM/yyyy... although i have set the my own server to UK time but it still display as MM/dd/yyyy... Thanks

|||

I'm not a sql guru but I believe u type:

SET DATEFORMAT dmy

So just connect to the sql server with query analyzer or whatever and execute that. It's 6pm, i'm off home, best of luck :)

|||

Hi,

I think the here type of the field as being smalldatetime or datetime is not the issue.

Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute

Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.

So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.

I think shifting from datetime to smalldatetime will increase the performance.

If you want to know if some data would be truncated. 

select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)

run this if you have any result than dont convert to smalldatetime.

 About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;

First be sure  ddl_months.SelectedValue is integer

then try the code as 

 config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(m,dateOfPurchase) = cast('"+ ddl_months.SelectedValue + "', as integer)AND balance > 0")

Hope this helps.

|||

Hi yvzman,

A nice infomation to note... Thank for replying... But i think is use CONVERT instead of CAST... dunno y CAST is not longer supported by my script

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Arithmetic overflow

Hi,
Every time a run a query where dates are included, I get an 'Arithmetic
overflow error converting expression to data type datetime'.
The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
AS Month' syntax is used.
I think that somehow, some of the dates are corrupted.
How can I found out which of then are corrupted? There're too many to scroll
through.
TIA,
Ana
************************************************** *****************************
"Eres dueo de lo que callas y esclavo de lo que dices"
"Judge your success by what you had to give up in order to get it"
************************************************** *****************************
On Tue, 28 Sep 2004 23:22:39 +0200, Ana wrote:

>The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
>AS Month' syntax is used.
>I think that somehow, some of the dates are corrupted.
>How can I found out which of then are corrupted?
Hi Ana,
SELECT YourDateCol
FROM YourTable
WHERE ISDATE(YourDateCol) = 0
And change your database design: use the datetime format to store dates
instead. <g>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)