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
Is it possible to set a filter in the SELECT-statementpart?
Normaly you the filter is set in the WHERE-statementpart, but that is too late for me in my statement. Right after the SELECTing the field I want to set a filter to the query? Is it possible?
My statement looks like this now:
SELECT [DatabaseName$Item].No_,
[DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price],
[DatabaseName$Sales Price HAG].[Starting Date],
[DatabaseName$Sales Price HAG].[Starting Time],
[DatabaseName$Sales Price HAG].[Sales Code] (<== here on this item I want to set a filter)
FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
INNER JOIN (SELECT [Item No_], MAX([Starting Date]) AS MaxStartingDate, MAX([Starting Time]) AS MaxStartingTime
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]) AS SubQuery1
ON [DatabaseName$Sales Price HAG].[Item No_] = SubQuery1.[Item No_] AND
[DatabaseName$Sales Price HAG].[Starting Date] = SubQuery1.MaxStartingDate AND
[DatabaseName$Sales Price HAG].[Starting Time] = SubQuery1.MaxStartingTime
Thx for helping!
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?
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!!
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 RelatedBrand 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.
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
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
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…
Hi,
View 1 Replies View RelatedTrying to set up replication between two machines (Developer Edition) and receiving an unitialized pull subscription. Error message states the publisher cannot be accessed with an agent message of 20084.
Any ideas?
Thanks!
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
We have occasional problems with transactional replication between 2 databases, and the problem is we dont notice right away. Usually the customer notices a few days after replication has stopped, and thats no good. We want to be notified as soon as there is any problem related to replication so we can go in and figure it out.
Ive been playing with the alerts, and it LOOKS like I should be able to do what I want, but after several hours I still cannot make any of the alerts work.
In my test environment, I'm forcing the following error, in order to try and test an alert: error 20598 "The row was not found at the Subscriber when applying the replicated command."
This seems to be one of our more common errors, and also its really easy to produce, so it seemed like a good candidate for testing the alert.
First off, I noticed that the agent profile was set to "Continue on data consistancy errors", which ignores error 20598. So I changed the profile to "Default", which doesnt skip any errors.
Next I set up a simple Job that simply writes a string to a file. I tested the job by calling it manually and it works. Once i can prove that this job is actually being called, I will change it to email me and the team.
Then I created a new alert in the publisher database which watches for error code 20598. Its response is to call the job I mentioned above. So when the error occurs, a string should be written to that file.
Then I forced the 20598 error to occur. I deleted a row in the subscriber DB, and then changed the corresponding row in the publisher. This causes the error to occur. In my replication monitor, I see new entries saying "Skipped 1 error(s) when applying transactions at the Subscriber." I'm not sure why it says skipped, but I know that these entries are the 20598 error that I'm trying to produce.
Now, there was nothing written to my text file, and I can see from the job history that my job was never called. If I look at the history of my test alert, it says "never occured".
I forced the 20598 error to occur, and I have an alert that is watching for the 20598 error, but it never fires.
I also created an alert in the subscriber database, watching for the same error. This alert was not called either.
Hello,
I am trying to setup merge replication for a sqlmobile database. I was able to set it up on a test environment which the sql instance and the iis were on the same box. I am trying now to go live. Our sql instance is on another box than the iis. I am now getting a 404 error when trying to test in the url https://www.domain.com/sqlmobile/sqlcesa30.dll?diag Is there a walk through for iis running on a different box, or does someone know of a solution?
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)
I am using the Configure Publishing and Distribution Wizard in the SQL Server Enterprise Manager to configure a server as a Distribution and Publishing server. The security configuration on the server in question for authentication is SQL Server and Windows NT.
When I get to the first page in the wizard I select that I want to use the server as the Distributor. The other option is to use another server for the Distribution server - and this isn't what I want to do. The next page presents the option to use the default settings to configure the distribution databases or to configure the setup. If I select the default options I get the following error message when the arrow is pointing to Configuring Distributor:
----------------------------------------------
SQL Server Enterprise Manager could not configure 'SERVER1' as the Distributor for
'SERVER1'.
Error 18456: Login failed for user 'sa'.
Remote logins for remote server 'repl_distributor' have been dropped.
Server dropped.
Server added.
Server network name set.
----------------------------------------------
Something I have tried to change in order to fix this issue is the 'Replication agents on the Distributor log into the Publisher:' option from its default (By impersontating the SQL Agent account on 'SERVER1' (trusted connection)) to 'Using SQL Server Authentication of this account:', and in those fields I have put in 'sa' (which is a valid admin account to the SQL server) and the correct password.
In all cases I get the message above. Can anyone help me to see what I am doing wrong?
Thanks for your time,
Eric Gross
Hi there
im trying to set up a replication from a local server to a remote server.
when starting up and selecting the Distribution / publisher, i get the following errors --->
Details as followed...
TITLE: New Publication Wizard
------------------------------
SQL Server is unable to connect to server 'SERVERX'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
------------------------------
and...
===================================
SQL Server is unable to connect to server 'SERVERX'. (New Publication Wizard)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
===================================
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.PrepareToShow()
Hi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
Hi I have a problem. I have a replicated database. Now I have a couple of tables within the database where I delete the content row by row and caluted the new record and insert them. Now when I do this on the development system that is unreplicate it is fine. However on production that is replicated I get this error:
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
Now I persume this is caused by the foreign key being contrained by the replicated database. I had though about unticking the box 'Enforce Relationship for replication' but I am not sure what problems this may cause.
Woudl this fix my problem? If not do you have any idea what would.
Thanks Ed
A user inadvertantly deleted a record in a table that maintains an identity seed. I attempted to insert the record using SET IDENTITY_INSERT ON, but forgot that this table is an article in merge replication. I am attempting the insert on the publisher and I get the following message:
Msg 548, Level 16, State 2, Line 2
The insert failed. It conflicted with an identity range check constraint in database 'cad', replicated table 'dbo.emmain', column 'empl_id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.
How can I re-insert this record??Thanks in advance for any help
Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.
SQL1MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2MSSQL01 as a subscriber the connection fails to the remote server.
The error message is:
TITLE: Connect to Server
------------------------------
Cannot connect to SQL2MSSQL01.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server SQL2MSSQL01. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.
When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.
TITLE: Connect to Server
------------------------------
Cannot connect to TFMHQARNDE07.
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ' SQL2MSSQL01'. (Replication.Utilities)
I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.
When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.
Any one have any idea what it can be?
Hi,
I am trying to set up Web Synchronization using Merge Replication. The Distributor and Publisher are on the same machine. Subscriber is a windows mobile application which syncs the data using web synchronization. I have configured IIS on web server for the sync. However there is no domain user on database and web server. Is it possible set up web sync without a domain user? Which user can I then use to give permissions on the snapshot share folder.
Any help is appreciated.
Thanks
I have created a publication on SQL 2014 SP1/CU1 it is going to a distribution server also a SQL 2014 database. The subscriber is also SQL 2014 SP1/CU1. When I set it up there are no errors. I fire up Replication MGR and it looks good after the snapshot and distribution agent have been started. Again, no errors. When I do a tracer token the information gets to the distributer in 1 - 4 seconds but it never gets to the subscriber, the tracer token monitoring screen only shows pending for distributor to subscriber.
I can upload the scripts used to create the publication and/or the subscription but am a little hesitant because of server names in the scripts.
Hi All;
I am trying to set up Merge Replication on a database and want to set the IdentityRange Management to Auto for all my tables which use a Identity column.
In the wizard, on Article Properties Page, I can do this by selecting a Table, and going for its properties, but this is a tedious task as I have ~300 tables to set this property on.
Is there another way or a global location where I can set the property to true and even mention the ranges and the threshold, so that I dont have to pick each table and set it individually.
I am also aware of the fact that I can Generate a Script and modify it and run that, but I was looking for some way to do this in the wizard.
Thanks!
Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property.
If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki
Replicate ddl setting is not working if multiple subscribers are used...
View 2 Replies View RelatedSetting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
Hi
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
3) Any thing that I need to look out for.
Thanks for any inputs on this.
Regards
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
View 1 Replies View RelatedHello I am working in an advertising company and I have to find all records who have advertised 7 days back but if one person has advertised more than once means for different products, his name should come only once. How can I filter this. I am totally new to this language I can write simple query but don’t know how to filter it.
Name IndividualID Product Date
A 1 x 2007-01-27.23.59.59.777
A 1 y 2007-01-28.00.00.00.000
b 2 xx 2007-01-28.00.00.00.000
A 1 z 2007-01-28.00.00.00.000
b 2 yy 2007-01-28.00.00.00.000
A 1 p 2007-01-28.00.00.00.000
c 3 m 2007-01-28.00.00.00.000
d 1 n 2007-01-28.00.00.00.000
A 1 s 2007-01-28.00.00.00.000
Hi,
I have a supllier named K O'Neill and i want to filter on his name in my supplier table . Is this possible ?
When I try Vend Name = 'K O%Neill' it does not work.
Can anyone help?
Thanks.