Should We See Replication Filter Where Clause Text In Profiler TextData On SQL Server 2005?

Sep 25, 2007


We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.

Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.

When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.

But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().

We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?

FYI, here's the text of the article filter statement:


SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

end

)

)

And here's the statement we ran from Query Analyzer:


declare @id varchar(10)

select @id = 'aultnc'

SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

end

)

)

Thanks

View 4 Replies


ADVERTISEMENT

Profiler TextData Truncation

Jul 20, 2005

I'm running SQL Profiler to analyze the queries being run against mySQL server. The problem is, that in the Profiler results, theTextData column, which contains the SQL statements executed, istruncated so I don't get to see the entire SQL statements. I've gonethrough all the documentation and menu options but I can't seem tofind a way to make it show me the entire SQL statement regardless oflength. Can anybody help?Thanks,Huey

View 1 Replies View Related

How Can The Profiler Textdata Field Be Enlarged?

Jul 20, 2005

When running Profiler to save a trace to a table the field 'TextData' getstrunciated. The datatype for that field is as shown:Column name | Data Type | Length | Allow NullTextData ntext 16 yesI can not find a way to set the trace up to create a tablewith TextData oflength greater than 16. This really causes a problem when trying to capturelong running queries for tuning as the query itself maybe truncated. Isthere a way around this?

View 2 Replies View Related

Profiler's TextData Column Has 'NULL' And Longest Durations

Jul 20, 2005

Is there a way to determine why the Textdata field has "NULL" for a value.The trace shows multiple "NULL" value records with the longest durations;many greater than 50,000ms. The {} indicate the TextData and Duration onthe line below:rownumber | textdata | Eventclass | ......| Duration |...966 | 15 | {NULL} | 24 smm5413 | 808 | Microsoft Office XP | misview012 | 78| {604636} | 2004-02-11 15:25:12.010 | 4747 | 0 | 469

View 1 Replies View Related

Profiler In SQL 2005 Filter On Host Name

Oct 25, 2007

How do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.

View 1 Replies View Related

Profiler In SQL 2005 Filter On Host Name

Oct 25, 2007

How do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.

View 1 Replies View Related

PDF Filter For MS SQL Server 2005 Full Text Search

Jan 23, 2008

Hello, I have read on the multiple places that filter for full text search of PDF files using FTS2005 is included in the Reader 8 etc. However, I have not found any document or instruction etc on adobe documents, microsoft documents or web that details on how to actually configure the filter. Please help. thanks
Kumud

View 4 Replies View Related

Profiler Filter

Sep 15, 2004

I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?

Thanks,
Ken Nicholson

View 2 Replies View Related

SQl Profiler Filter

Apr 21, 2008

Hi every one,
I want to filter application name in sql profiler, so I write SQL% (for every one who connects by sql qery analyzer) in "application name" filter box but it doesn't work correctly,and shows all applications. Why it works incorrect?
Thank You
Kini

View 1 Replies View Related

SQL Server 2005 - Transactional Replication Involving Depricated Text And Ntext Data Types

Dec 2, 2005

I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.

View 3 Replies View Related

SQL 2012 :: Complex Filter For Server Merge Replication

Jul 26, 2015

We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:

Id int identity,
ParentId int,
GroupId int

Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.

A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child

The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.

Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child

And the filter would look like this:

WHERE GroupId = 200

However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…

View 2 Replies View Related

SQL Server 2012 :: Full-text Filter Daemon Service Error Log

Sep 24, 2014

is there a way to turn off or reduce the logfiles of the MSSQL Full-text Filter Daemon Service?

The entries are very useless and on server with many databases and many fullindex tables it is very inefficient to write all these files (no SSD).

(SQL Server 2012 Express)

View 0 Replies View Related

Where Clause Filter

Apr 14, 2008

I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:

Select
DisplayColumns...
From
InterestingTables
Where
(Filter1) --W1
AND --W2
NOT --W3
(Filter2) --W4

Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.

TIA.

View 1 Replies View Related

Sql Server 2005 Profiler

Apr 17, 2008

Dear friends,
I got script for creation trace file on server drive.
and it's work fine,when we migrate to sql server 2005 i take the same script for this trace and he create it successfully but the problem when i try to open this file for example today file:
SQL-Server1 SQL Trace20080417000000847.trc
I got the following message:
Sql server profiler :failed to open a file ,access denied.

old files created by sql server 2000 ,i can open it until now but the file created after migrate to sql 2005 ,i got that message above.

View 4 Replies View Related

Sql Server 2005 Profiler

Jul 11, 2007

Guys/Girls

Have a problem am currently running a sql trace on a database, have the normal options setup.

Stored Procedures
RPC:Completed
RPC:Starting
TSQL
SQL:BatchCompleted
SQL:BatchStarting

But am also using the text data field but am trying to only bring back items that have a certain string like. Does anybody know how to use wild cards in the text data colounm

Lee

View 2 Replies View Related

About The SQL Server Profiler 2005

Sep 13, 2007

Hi everyone,I am steven.

I have some questions about the SQL Server Profiler 2005.

1,haoI am using TransactionScope of .Net Frameworks 2.0 for DB query and update.I want to know the lock level(table,page,row,etc) and status of lock status(u,s,x,etc) by profiler tool when I run the query and update statement.How can I do?

2,I had got some traces when I run the DB query and update.But I cann't understand the meaning of the trace.such as Binary Data,ObjectID,Type,etc.How can I know the meaning of these?

Whould anyone can help me?

View 1 Replies View Related

Errors In SQL Server 2005 Profiler

Oct 27, 2006

I notice this running the SQL Server Profiler under the application name Report Server. Every time it runs, it reports an error (in the Error field of Profiler). Does anyone know what this is?declare @p1 nvarchar(64)set @p1=NULLexec GetDBVersion @DBVersion=@p1 outputselect @p1

View 1 Replies View Related

Profiler For SQL Server Express 2005

Aug 7, 2006

Dear All,

I need a tool to see what transactions and in what order are being issued
against the SQL Server Express 2005 DB.

Because of the SQL Server tools don't come with SQL Server Express 2005,
then i run SQL Server 2000's Profiler with SQL Server Express 2005. But It
prompt error "Failed to start a new trace".


FYI, i don't want to use the backend sql trace .in command prompt..


Pls advise me on how to run the sql profiler to trace the transaction being executed in SQL Express 2005?


Thank you.

View 4 Replies View Related

SQL Server 2005 Profiler Error

May 14, 2007

I SQL Server 2005 Profiler when ran the T- SQL template against some Stored Procedures, in the error column i get 2 - Abort. Can some one help me on this issue.



Thanks

View 1 Replies View Related

How To Include Variable In CURSOR SQL Filter Clause?

Jul 23, 2005

After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View 1 Replies View Related

Profiler In SQl Server 2005 Express Edition

Apr 6, 2008

Is it possible to install SQL server profiler in Express Edition.  I know it can be installed in SQL server 2005.  Once I tried installing in Express edition and it did not work. 
What is the main diofferenve between SQL Server 2005 and Express edition?
Thanks

View 1 Replies View Related

Profiler Trace With SSE 2005 (SQL Server Express)

Aug 15, 2007

Someone Please Help!

How on earth can a Profile Trace be run where SSE 2005 is installed??? In the past, with MSDE, we always installed the 'tools' on a local workstation, so that we had EnterPrise Manager and its suite of tools...no problem. Yet, with Management Studio Express (err...Distress?), there's no way to do this! I've scoured the net, and I see threads where people have done it, yet, no one seems to be clear...including Microsoft...on how to obtain this MOST IMPORTANT of all tools for an SQL deployment.

Please Help! Someone...Anyone!...Thank You!...Michael

View 6 Replies View Related

Filter Criteria - Temp Table Join Or Where Clause?

Jul 20, 2005

I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G

View 4 Replies View Related

Stmt Text From Profiler

Nov 2, 2005

does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written.
I saw this a few months ago and havent had any luck in finding it.

anyone?

View 1 Replies View Related

Transact SQL :: Case In Where Clause With Column Comparison With Greater Than Or No Filter

Sep 24, 2015

For Below example when @x=1 to retrieve col>0 rows or all rows.

With out another if else blocks or Dynamic sql to solve only in where clause.

select 0 col into #x
union
select 1 col
union
select 2 col

declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END

--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter

View 5 Replies View Related

SQL Server 2005 - Filter For This Month Only

Feb 7, 2007

Hi, I have done a quick search for a similar topic, but I don;t think they are what I need. What I want to do is (when creating a view in the database) I want to filter entries in my table that have only been created this month (the field I am looking at is called DateDB), I'm not sure how to do this, I have tried =MONTH(GETDATE()), but this doesn't return any values at all. Is there a way of doing this? 

View 2 Replies View Related

SQL Server 2005, Parameterized Filter: Snapshot Could Not Be Applied

Sep 28, 2005

Hi

View 8 Replies View Related

Replication Filter Bug?

Feb 20, 2008

Im using SQL 2005 SP2 as the publisher and SQL Mobile subscribers and Im convinced I have found a replication bug .....but maybe Im just being stupid!

I have an account manager table which links to companies and users.
The organisation table use a nasty @subset_filterclause filter against the account management table to decide what records to retrieve for each subscriber.

This works fine and updates either way are replicated as you would expect.

However when an account manager is changed and the organisation no longer fulfils the filter criteria, it is not removed from the subscriber (unless I delete the db and download it from scratch)
Surely this is a bug?

Just to clarify the filter clause was ok, I added the account management table to the filtered tables using the exact same @subset_filterclause.

Now when I change an account manager, the account management reocrd is correctly removed from the subscriber but the organisation record remains on the subscriber? Have I missed something obvious?

View 1 Replies View Related

Replication With Filter

Apr 13, 2006

Hi All,

I've done my replication successfully between SQL Server 2000 and SQL Server CE. But how if I want to filter my replication?

For example :- At Table1 have 5 field which is UserID,Month,Year,Price,ItemCode

I want to filter replication data with UserID and Month and Year. How can I do it?

Thank you!!

View 5 Replies View Related

Setting Up Filter For RDA Replication

Nov 20, 2007

Hi!
I have a WM5 device with Sql Server CE 3.0 and Sql Server 2005 on the serverside. I set up RDA and it works fine until I try do pull a little more advanced query. Instead of pulling a simple table with where clause I created a query that joined two tables returning values from only one. It doesn't seem to be possible, is it? If it's not, is it someway I work around this?

/Magnus

View 4 Replies View Related

Filter Records During Transactional Replication.

Apr 13, 2007

Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.

View 3 Replies View Related

Merge Replication With Dynamic Filter

Jun 14, 2006

Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database.

I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc.

I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great.

Thanks in advance.

View 8 Replies View Related

Merge Replication Using A Guid As A Dynamic Filter

Aug 12, 2006

Hi ...

I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx

My next step would be to implement dynamic filtering using the guid of the entity as a parameter.

I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)

My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.

RMO-Code:

subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"

Filter:

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()

Function:

create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @host nvarchar(50)
set @host = host_name()
declare @entity uniqueidentifier
set @entity = cast( @host as uniqueidentifier)
return @entity
End







When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@entity, where @entity is a guid

Thanks for your support

Alex









View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved