Feasibility of Filtering in this Scenario...

Background:
I'm a young developer working on a project that involves merge replication between SQL Server 2005 Mobile Edition and SQL Server 2005 and I've been having a hard time wrapping my head around exactly how to implement the best filters for the subscription(s) and publication(s).

Users of our application are Auction goers who collect data about AuctionItems in a mobile db and sync that information with a central db once he or she is finished. The central db is, obviously, pre-populated with most of the information about any/all AuctionItems. Central db information is available to view/change via web access.

Multiple users can change the same Auction data so there will be overlapping partitions.

What I would like to do is:
Present the user with a list of all available Auctions for the next 2 weeks (select * from Auctions where blah blah blah). This is a simple publication to create. Now, I'd like to be able allow the user to select AuctionID 4, AuctionID 3, AuctionID 12 and then, via a seperate AuctionListing publication only download AuctionItems that apply to those IDs.

Obviously, the publication has to be created and include the AuctionItem table and all necessary related tables/columns. But how do I create a parameterized filter based on that AuctionID?

Can a subscription be "dynamically created" once those AuctionIDs are known? Obviously, I don't want to bog my mobile devices down with tens of thousands of auction listings to auctions the user is not planning to attend.

Basically, my question is: am I wasting my time or is there some clever manipulation of HOST_NAME(), SUSER_NAME(), both, or some other method that I've missed that can get only those Auctions where the the ID matches one selected by the user at runtime?

You can do it with either one publication or 2 publications.

With one publication, I can think of dynamically populating a table with the IDs from what the user chooses. The filter will be based on IDs present in this table. However note that populating the table and synchronization need to be in 2 different transactions because if they are in the same one, enumerations will be looking at not correct data.

Let me know if you have trouble setting the filter.

|||Could you elaborate a little more on some of the parameters/steps involved involved with the filtering process?

How would we do it, exactly, in one publication? Do I need to use a

mix of RDA and merge replication (or is that even possible)?

In my head I think I know how it needs to work but, as a SQL Server beginner (just started using it this year), I don't know if I can describe the problem and solution eloquently with the available tools.|||Mahesh:

I've decided to go with one publication. How can I separate the population of the database dynamically from the sync call that I will need to make to pull the results down? How do split this action into separate transactions.|||One more thing to consider: I can see the feasibility of using two publications to accomplish this tasks and in some ways it might be easier. Nevertheless, consider this scenario.

We use a table called 'AuctionDownload' that is populated with the AuctionIDs and some string value that we can filter on using HOST_NAME(). This table will be included in the publication along with the main 'Auction' table.

User A subscribes, calls sync, gets a list of auctions and chooses IDs 1 and 2 that he wants listings for. He populates the rows in 'AuctionDownload' on his mobile device and syncs. That's fine.

However, imagine User B is also using the system at the same time as User A. The first time he calls sync to get the list of available auctions, his EMPTY AuctionDownload table will need to be merged with the central AuctionDownload table. Thus User A's values are erased before he or she could call sync on a different publication to get the actual listings!

Is there a work around or am I imagining this syncing scenario incorrectly.

Fastest Bulk Load

Hi All,

Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?

This is my current sql statment to load data.

EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'

SET ANSI_WARNINGS OFF

BULK INSERT mystuff.dbo.[v1]

FROM 'c:\myfile.txt'

WITH

(

FIRSTROW = 1,

FORMATFILE = 'c:\scripts\v1.fmt',

MAXERRORS=2000,

ROWS_PER_BATCH=100000

)

Thanks,

Mike

There should not be any significant difference in what gets logged for this between SQL 2000 and SQL 2005. How are you measuring the discrepency?

|||

I used to load this data on a windows 2000 server running sql server 2000. And I could load all of the data into 7-8 hours. Im running 22 scripts that load from 8 files a piece and specfic start and end points in the fmt files. The fmt also jump around through the data. The data files are 25 GB a piece. But now with the New version on a more powerful box the time esimates are going to be days. I guess I need to start looking for other variables if its not the logging.

I should also mention that the data all resides on a NetApp san and we are connected over 4GB fibre.

Thanks,

Mike

Failure installing sql server 2005 express on vista ultimate x64, error 29506

Whenever I try to install SQL Server 2005 Express Edition on my Vista Ultimate x64 platform I get error 29506. All I could find on the web was that this is probably due to an access control problem. I have tried installing it as the local administrator, as a member of domain admins, and as the domain administrator. All get the same error. My domain is a Small Business Server domain. This may be part of the problem, but I can't imagine how.

When trying to force access to the domain administrator, I also seem to get an access problem.

Any suggestions?

First off I would try the sp1 version, but other then that the SP2 is the next supported release on Vista, there is a ctp release but the final release has not been made...

When installing VS Express you need to run the install under elevated permissions, I do this by running a command prompt as an administrator (Right clicking on the icon and selelcting run as admin), then run the exec by using the command prompt.

|||

Steve:

Were you ever able to get this issue resolved?

Thanks!

Rory O. Cahill

Failure installing sql server 2005 express on vista ultimate x64, error 29506

Whenever I try to install SQL Server 2005 Express Edition on my Vista Ultimate x64 platform I get error 29506. All I could find on the web was that this is probably due to an access control problem. I have tried installing it as the local administrator, as a member of domain admins, and as the domain administrator. All get the same error. My domain is a Small Business Server domain. This may be part of the problem, but I can't imagine how.

When trying to force access to the domain administrator, I also seem to get an access problem.

Any suggestions?

First off I would try the sp1 version, but other then that the SP2 is the next supported release on Vista, there is a ctp release but the final release has not been made...

When installing VS Express you need to run the install under elevated permissions, I do this by running a command prompt as an administrator (Right clicking on the icon and selelcting run as admin), then run the exec by using the command prompt.