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 :)

Argument not specified for parameters error

hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc...
Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.

the code is this:

Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim report As Byte() = Nothing
'Create an instance of the Reporting Services
'Web Reference
Dim rs As localhost.ReportingService = New localhost.ReportingService
'Create the credentials that will be used when accessing
'Reporting Services. This must be a logon that has rights
'to the Axelburg Invoice-Batch Number Report.
'***Replace "LoginName", "Password", and "Domain" with
'the appropriate values. ***
rs.Credentials = New _
System.Net.NetworkCredential("Administrator", _
"password", "localhost")
rs.PreAuthenticate = True
'The Reporting Services virtual path to the report.
Dim reportPath As String = _
"/Galactic Delivery Services/Axelburg/Invoice-Batch Number"
' The rendering format for the report.
Dim format As String = "html4.0"
'The devInfo string tells the report viewer
'how to display with the report.
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"
'Create an array of the values for the report parameters
Dim parameters(1) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue _
= New localhost.ParameterValue
paramValue.Name = "StartDate"
paramValue.Value = calStartDate.SelectedDate
parameters(0) = paramValue
paramValue = New localhost.ParameterValue
paramValue.Name = "EndDate"
paramValue.Value = calEndDate.SelectedDate
parameters(1) = paramValue
'Create variables for the remainder of the parameters
Dim historyID As String = Nothing
Dim Credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As _
localhost.ParameterValue = Nothing
Dim StreamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = _
New localhost.SessionHeader
rs.SessionHeaderValue = sh
Try
'Execute the report.
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
'Flush any pending responce.
Response.Clear()
'Set the Http headers for a PDF responce.
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response().ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
' filename is the default filename displayed
'if the user does a save as.
HttpContext.Current.Response.AppendHeader( _
"Content-Disposition", _
"filename=""Invoice-BatchNumber.HTM""")
'Send he byte array containing the report
'as a binary response.
HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.End()
Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"<HTML><BODY><H1>Error</H1><br><br>" & _
ex.Message & "</BODY></HTML>")
HttpContext.Current.Response.End()
End If
End Try
End Sub
End Class

This is the area underlined as failing:
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

The errors all pretty much go like this:
c:\inetpub\wwwroot\AxelburgFrontEnd\ReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.

I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.Anybody? Is this that hard?

Argument Not Specified For parameter 'TruePart' of Public Function

why am I getting the error "Argument Not Specified For parameter 'TruePart'
of 'Public Function IIf(Expression as Boolean, TruePart as Object, FalsePart
as Object) as Object' from the following textbox expression in reporting
services 2005?
=IIF(Sum(Fields!ElapsedRunSeconds.Value)is Nothing, 0,
Sum(Fields!ElapsedRunSeconds.Value)/60.0/60.0)Stacey,
Try =IIF(Sum(Fields!ElapsedRunSeconds.Value) is Nothing, 0, Sum(Fields!
ElapsedRunSeconds.Value)/3600.0)
Inserting a space between "Sum(Fields!ElapsedRunSeconds.Value)" and
"is" or changing is to = should fix it.
Changing the /60.0/60.0 to /3600.0 is mostly for looks and
simplicity. HTH
toolman
stacey wrote:
> why am I getting the error "Argument Not Specified For parameter 'TruePart'
> of 'Public Function IIf(Expression as Boolean, TruePart as Object, FalsePart
> as Object) as Object' from the following textbox expression in reporting
> services 2005?
> =IIF(Sum(Fields!ElapsedRunSeconds.Value)is Nothing, 0,
> Sum(Fields!ElapsedRunSeconds.Value)/60.0/60.0)

Argument not specified for parameter error

I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.

What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select...Where function?

After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().

here's some of the code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 if Not Page.IsPostBack then
 dim Code as String
 Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
 label1.Text = Code
 GetPropertyCodes(Code)
 ddlPropertyCode.DataBind()
 end if
End Sub
 Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @.Code)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_code.ParameterName = "@.Code"
 dbParam_code.Value = Code
 dbParam_code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_code)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function

Thanks for your help.

ChrisWhat is the value of your Code variable when you are setting the parameter value? Can you debug that and determine that?

Alsternately, are you certain everything is spelled correctly? Can you look at the actual SQL being sent using SQL Profiler?|||I know the value in the Cookie exists because I can look at the cookie and see the values that were written to it during the login page. I have tried pulling the value out of the cookie and placing it in the Text property of a label but the label isn't changing from it's default value. That tells me two things: 1. The Request.Cookie code syntax is not correct or 2. The syntax is correct but the Request.Cookie is not finding the cookie. Since the Request.Cookie command doesn't report an error if the cookie is empty or not there, how can I check that the syntax is correct.

Everything is spelled correctly as far as I can tell. How do I look at the actual SQL being sent using SQL Profiler? If you mean using the query tool to check that my Select statement is correct, I have done that as part of the Wizard when creating the Function.

Chris|||Here is your problem:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 if Not Page.IsPostBack then
 dim Code as String
 Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
 label1.Text = Code
 GetPropertyCodes(Code)
 ddlPropertyCode.DataBind()
 end if
End Sub

Code is a variable local to your Page_Load. YOu then use Code in your GetPropertyCodes() method without declaring it. Place Option Explicit On at the top of ALL code files and this sort of error will not occur.

You need to either make Code a variable at the page level, or retrieve a local copy in GetPropertyCodes().|||Where do I place Option Explicit On in my source code?|||At the top of the code file:

Option Explicit On

or at the top of a page with code, make sure Explicit="True", like so:

<%@. Page Explicit="True"%>|||Douglas,

I made the addition of the Explicit reference as suggested but now I am getting a different error. Can you tell me what this really means?

<ERROR>
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 8: if Not Page.IsPostBack then
Line 9: dim Code as String
Line 10: Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
Line 11: GetPropertyCodes(Code)
Line 12:

</ERROR
I think the error above was because I wasn't logged in and the Cookie had not been created but that is just a guess. If I login to my site and create the cookie, I get this error:

<ERROR
Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Char' does not contain a property with the name Prop_Code.

Source Error:

Line 15: ddlPropertyCode.DataValueField = "Prop_Code"
Line 16: ddlPropertyCode.DataMember = "Table"
Line 17: ddlPropertyCode.DataBind()
Line 18: end if
Line 19: End Sub

</ERROR
Thanks for your help.

Chris|||For the first area, obviously you need to protect against the cookie being Nothing.

For the second, Are you certain that the dataset has Prop_Code as a field? Have you checked by examining the DataSet in the debugger?|||Douglas,

I have added an Is Nothing condition so I can redirect to the login page if the Cookie doesn't exist. I know the code to get the Code value out of the Cookie works because I can create a simple page that runs the same commands to populate the UserCode variable and then populate a label.

The Prop_Code column exists in the database table but I still cannot get the Property Code drop down list to populate. I have scoured the Internet and all of my ASP.Net books (including your book) and I still cannot find any references that will help me. My page comes up with the code below but the drop down list is still blank. I have tried populating the properties in the drop down list instead of doing it in the Code but that doesn't work either. I have also tried using a SQLDataSourceControl but then I can't use my function because the dscontrol parameter for the @.Code isn't set.

Do you have any idea or an Internet resource that can help me?

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 if Not Page.IsPostBack then
 if Request.Cookies("UCodeCookie") is Nothing then
 Response.Redirect("login.aspx")
 else
 dim UserCode as String
 UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
 GetPropertyCodes(UserCode)
 ddlPropertyCode.DataSource = "GetPropertyCodes"
 ddlPropertyCode.DataTextField = "Prop_Code"
 ddlPropertyCode.DataValueField = "Prop_Code"
 ddlPropertyCode.DataMember = "Table"
 ddlPropertyCode.DataBind()
 end if
 end if
End Sub
 Function GetPropertyCodes(ByVal UserCode As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @.Code)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_code.ParameterName = "@.Code"
 dbParam_code.Value = UserCode
 dbParam_code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_code)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function

Thanks
Chris|||This:

GetPropertyCodes(UserCode)
ddlPropertyCode.DataSource = "GetPropertyCodes"

should be this:

Dim ds as DataSet
ds=GetPropertyCodes(UserCode)
ddlPropertyCode.DataSource = ds.Tables[0]

|||Douglas,

I get this error:

<ERROR
Compiler Error Message: BC30203: Identifier expected.

Source Error:

Line 15: Dim ds as DataSet
Line 16: ds=GetPropertyCodes(UserCode)
Line 17: ddlPropertyCode.DataSource = ds.Tables[0]
Line 18: ddlPropertyCode.DataTextField = "Prop_Code"
Line 19: ddlPropertyCode.DataValueField = "Prop_Code"

</ERROR>|||Doh! Too many languages. This:

ddlPropertyCode.DataSource = ds.Tables[0]

must be this:

ddlPropertyCode.DataSource = ds.Tables(0)

Parens rather than square brackets...|||Douglas,

That worked. I really appreciate your help.

Could you also help me with one other thing? Now that I have the dropdownlist working, I want to retrieve some information out of another SQL Server table after I submit all of the information for this page based on the same code value that I can then place into another cookie. I am not sure how to get the data out of the dataset (read that as function) and place it into a String variable which can in turn be written to the cookie. Do you have any idea how to do this or at least point me to a resource on the web that can help.

Thanks

Chris|||Given a dataset ds that you have filled as previous examples:

Dim c as string
c=ds.Tables(0).Rows(0)("FieldName")