InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:00:27 GMT20http://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHere is the generated script I used.Take a look at it and replace the server, db names - login etc... accordingly.It only has 1 table involved in the replication but once created, you may use the SSMS to add more tables,view, stored procs, functions.The scripts are numbered in the order they should be ran.The replication is setup at the central server is the publisher and distributor.Give it a try if you have the time and still wishes to go this way.YvanFri, 21 Sep 2012 06:08:52 GMTYvan Bouchardhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxSince the touchscreens will have thier own local copy of sql express , i would suggest that you build the system to sync on demand instead of creating a replication topology that your not familair with.There are cases for and against this approach you , need to validated which one meets your requirement.Thu, 20 Sep 2012 23:55:39 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxI had to do this about 10 years ago with an Access (95!) based EPOS system in 10 retail outlets and IBM DB2 system at head office.We were sending transaction data, gift vouchers data, customer demographic data and stock request/delivery confirmations to HO and getting back Group stock positions, active vouchers and stock issue notifications.Eventually the solution we came up with was to run a windows scheduled job to extract new records into csv files and give each one a unique name.StoreIDTableName.SequenceNumber (we were limited to 8.3 names)These were then FTP'd to the head office server which ran a script to import the files (and validate that there were no missing files - sequential processing was important for us) and then move the processed file to an archive folder.The same process flow should work for you. The only challenge we had was for one of the files (stock receipt confirmation) we needed the HO system to send back an acknowlegement file that they had received our confirmation and updated our stock levels. If not, then we had to resend it.Wed, 19 Sep 2012 09:54:18 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxThe central Server can pull the data out of the subscriber. As long it uses an account with the proper permissions, everything will merge.I don`t have access to the script I used right now, but I can share it later tonight - as long as I can find it! Stay tuned. :-)Wed, 19 Sep 2012 08:39:51 GMTYvan Bouchardhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]Yvan Bouchard (9/19/2012)[/b][hr]You have many ways:Initiallly, you can run the Import Data wizard in SQL which will build and run an SSIS package to import the Access data into SQL, in the central server. This will give you your central DB to set-up the replication.The cool thing about replication is that you can set it up to push all the schema (tables), include views, stored proc down to the subscriber. So this way, after your first replication is set-up, the only thing you need in the SQL Express engine on the Touch PC is a blank database. Once you add the subscription on the remote SQL Express, the whole thing gets created by the replication process.Once you have one replication with one Touch PC set-up, use the Generate Script... feature to script all of this. Then use the Subscriber portion of the script, tweak it so it applies to another touch PC (changing the filter for the computer name), create the blank db on the remove touch PC and run the tweaked subscriber script. Repeat for all other touch PC.Pretty cool stuff once it is setup! :cool:Start by creating one replication with one PC. The rest after is pretty straight-forward.[/quote]Yvan, I don't quite understand. See the following link: [url]http://msdn.microsoft.com/en-us/library/ms165686(v=sql.105).aspx[/url]SQL Express can only be a subscriber. DJ's requirement is different. The information flows from the touch devices to the central database not the other way round.The only way I can see this working is if he goes with SQL Server compact edition 3.5 SP2 (not 4.0) and use merge replication but reconfigure it so nothing flows from central DB to touch device. I'm not even sure that's possible.DJ has some serious research to do! This isn't going to be easy either way.Wed, 19 Sep 2012 07:58:37 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxYou have many ways:Initiallly, you can run the Import Data wizard in SQL which will build and run an SSIS package to import the Access data into SQL, in the central server. This will give you your central DB to set-up the replication.The cool thing about replication is that you can set it up to push all the schema (tables), include views, stored proc down to the subscriber. So this way, after your first replication is set-up, the only thing you need in the SQL Express engine on the Touch PC is a blank database. Once you add the subscription on the remote SQL Express, the whole thing gets created by the replication process.Once you have one replication with one Touch PC set-up, use the Generate Script... feature to script all of this. Then use the Subscriber portion of the script, tweak it so it applies to another touch PC (changing the filter for the computer name), create the blank db on the remove touch PC and run the tweaked subscriber script. Repeat for all other touch PC.Pretty cool stuff once it is setup! :cool:Start by creating one replication with one PC. The rest after is pretty straight-forward.Wed, 19 Sep 2012 07:45:52 GMTYvan Bouchardhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHi YvanThanks for the info. To be honest I have never used VB.net to write to SQL only Access but its probably only a change of connection strings needed. So I will create another version of my program to write to a local SQL Database and see if I can get the merge working.The only big question I have is its easy to deploy a blank Access Database with all the tables setup with my application, how do I deploy an SQL Express database with tables ect via my VB.net application as it wont just be a simple case of copying a blank database to each touchscreen unti.CheersDJWed, 19 Sep 2012 07:10:38 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxYou can still download SQL Express 2008, you don`t have to use 2012 version.Of course, SQL Express would be one more thing running on the PC but it is not running all the time. The test stations we had were simple Dell desktop where most of them had 2G MEM. Unless there are continuously entering data on the Touch PCs, I am pretty sure it can handle the load, since you were using Access and I guess it is doing the job. However, you can configure the memory usage to restrict SQL from grabbing too much memory, if you want to. Just look in the SQL Server Properties for that.Wed, 19 Sep 2012 07:06:09 GMTYvan Bouchardhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHi Yvan / AllThanks for the replys, I have had a play with SSIS, but having problems, I have created my connections ect but had to install the Access Driver on my SQL 2008 server so it would see my Access accdb but its now complaining about the connection. Also having to create seperate flows for each table and each touchscreen will become a headache.I am thinking about moving my touch to SQL but does the latest version of Express merge with SQL 2008. Also how heavy is express for PCs as my machines are very basic for the touchscreen system so wont do well with heavy overheads.CheersDJWed, 19 Sep 2012 06:45:31 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxYou can definitely use Merge Replication if you have SQL Express on your local touch station replicate data back to the central DB. I`ve done it in the past for test equipment running continously and uploading results back to the central DB for global reporting.You also place a filter on the replication so each touch DB only merges its own data, not capturing everyone elses results. Use the machine name. Works like a charm.Wed, 19 Sep 2012 05:31:26 GMTYvan Bouchardhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxJust so you know , the access approach works fine when ur dealing with few touchscreen app, once the number of users increase the SSIS implementation is going to be a pain to manage and worse yet could cause all kinds of data management issues. Also what happens when u upgrade the application ? chances are you will have users with two or three different versions of the access database depending on whether they choose to upgrade or not.If you want to redesign the system it will be easier to do now , than have it limp around for a few years before finally moving to a scalable rdbmsWed, 19 Sep 2012 03:42:43 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxExcellent, thanks Anthony. I will dig out the SQL software and have a look.Wed, 19 Sep 2012 01:28:37 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]Douglasjbell (9/19/2012)[/b][hr]HiThanks for the replys.As the application has already been designed from the ground up for use with the Access database I would rather stick with this as the backend database.I have had a look at SSIS for SQL 2008 unfortuantly to use the Business Intelligence Development Studio I need to have SQL 2008 Professional which you get the developer edition with and unfortuantly I only have SQL 2008 Standard. I think I am going to have to look into writing my own VB program that sits as a service on the clients PC and updates the SQL Server Manually.RegardsDj[/quote]BIDS is included in Standard Enterprise and Developer editions.Just a case of installing it from the SQL installation which installs the "shell" version of Visual Studio, or if you already have VS2008 installed, adds in the references to business intelligence projects.Wed, 19 Sep 2012 01:08:31 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHiThanks for the replys.As the application has already been designed from the ground up for use with the Access database I would rather stick with this as the backend database.I have had a look at SSIS for SQL 2008 unfortuantly to use the Business Intelligence Development Studio I need to have SQL 2008 Professional which you get the developer edition with and unfortuantly I only have SQL 2008 Standard. I think I am going to have to look into writing my own VB program that sits as a service on the clients PC and updates the SQL Server Manually.RegardsDjWed, 19 Sep 2012 00:45:46 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]Jayanth_Kurup (9/18/2012)[/b][hr]http://msdn.microsoft.com/en-us/library/ms172461%28v=sql.105%29.aspxlooks like sql compact support merge replication based on the above link.[/quote]So DJ has some choices to make.1) Stick with Access and maybe use SSIS to poll the data at regular intervals.2) switch to SQL Server Compact Edition (3.5 SP2) and use merge replication. (I don't like this option - :ermm:)I don't see another way with his current setup. What do you think?Tue, 18 Sep 2012 14:13:08 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxhttp://msdn.microsoft.com/en-us/library/ms172461%28v=sql.105%29.aspxlooks like sql compact support merge replication based on the above link.Tue, 18 Sep 2012 13:26:34 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxJust to clarify on the error handling in SSIS. Because you're able to configure the workflow of the package, you can redirect the package elsewhere so you deal with failed connections.Sorry if I'm not making much sense but I hope I've said enough to get you to check out this great ETL tool.Tue, 18 Sep 2012 13:20:24 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]Douglasjbell (9/18/2012)[/b][hr]Hi AbuThanks for the reply, adding timestamps and the such to the tables will not be an issue, and it is only one way synch, Access to SQL as its only a logging system so the clients will not need any info from the main DB.As I say I am only new to SQL so just looking into your SQL scheduled job. My only concern is that I will have to create a linked server to each Access database then have the job pull the information.I have a few concerns on this though, if I have 20 databases and the job has to pull data from all these databases each minute, I think the overhead on the SQL server may be to much and effect the main application running on the server.What happens if a data pull from a database takes longer than 1 minute.What happens if the SQL job cant see a database.I would like to try and stay with the access databases on the local systems if possible.CheersDj[/quote]All your concerns are valid so forgive me for my advice if it's not appropriate!If you want to keep the Access DBs then you can create an SSIS package to loop through each touch application DB and poll the required information. You can create an app table on your repository DB whicH will hold the necessary connection details. Your SSIS package can loop through each row in this table to generate the dynamic connection string etc....As to you other concerns, you can find out more about the error handling capabilities that SSIS offers. It's a great tool and it might just do the job for you.Good luck!Tue, 18 Sep 2012 13:13:33 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHi AbuThanks for the reply, adding timestamps and the such to the tables will not be an issue, and it is only one way synch, Access to SQL as its only a logging system so the clients will not need any info from the main DB.As I say I am only new to SQL so just looking into your SQL scheduled job. My only concern is that I will have to create a linked server to each Access database then have the job pull the information.I have a few concerns on this though, if I have 20 databases and the job has to pull data from all these databases each minute, I think the overhead on the SQL server may be to much and effect the main application running on the server.What happens if a data pull from a database takes longer than 1 minute.What happens if the SQL job cant see a database.I would like to try and stay with the access databases on the local systems if possible.CheersDjTue, 18 Sep 2012 12:55:20 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]Douglasjbell (9/18/2012)[/b][hr]HiThanks for the reply, I have just been reading about SQL 2012 free LocalDB that can be deployed with an application. This looks like a very good database for my application instead of Access 2007. However as my knowledge of SQL is very limited, can I use an application written in VS 2005 to read and write to a 2012 LocalDb instance and can the information in this DB be mergerd or replicated to a full 2008 sql server as per my origional post.CheersDJ[/quote]It would be nice to get some experts to join in this discussion as I don't want to give wrong suggestions!Your touch device DBs need to publish the information and your repository db needs to be the subscriber. With SQL server compact edition 3.5 sp2 I believe you can do merge replication (so info flows both ways) but I've never tried it so not sure how it will work.Have you considered creating your own custom replication process?As I said previously, you can have a scheduled job that runs every two minutes and checks each db for records that have been inserted or updated. Is this not possible?Thanks.P.sLocalDB does not spurt replication I'm afraid. Even compact edition 4 no longer supports merge replication so it looks like Microsoft don't want this functionality available in this vision anymore!Tue, 18 Sep 2012 12:22:21 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHiThanks for the reply, I have just been reading about SQL 2012 free LocalDB that can be deployed with an application. This looks like a very good database for my application instead of Access 2007. However as my knowledge of SQL is very limited, can I use an application written in VS 2005 to read and write to a 2012 LocalDb instance and can the information in this DB be mergerd or replicated to a full 2008 sql server as per my origional post.CheersDJTue, 18 Sep 2012 11:38:32 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspx[quote][b]anthony.green (9/18/2012)[/b][hr]Can compact edition act as a publisher in replication? I know Express and Web edition are subscriber only, but not done much with compact.[/quote]Good question....See this: [url]http://msdn.microsoft.com/en-us/library/ms172417(v=sql.105).aspx[/url][b]SQL Server Compact supports data replication with SQL Server 2005 and SQL Server 2008 using merge replication and Remote Data Access (RDA).[/b]Tue, 18 Sep 2012 09:21:03 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxCan compact edition act as a publisher in replication? I know Express and Web edition are subscriber only, but not done much with compact.Tue, 18 Sep 2012 08:01:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxLet me see if I've understood this correctly:You have your touch screen devices with their own local ACCES DBs containing the three tables:ACCESS-DB-1ACCESS-DB-2and you have a SQL Server 2008 instance with a DB that has the same three tables.SQLSERVER-2K8To use SQL Server replication, your SQL Server instance will need to communicate with another SQL Server. Are you able to swap the ACCESS database with SQL Server compact edition?If this isn't an option then you will have to create your own custom replication. You will need to use timestamps on your records in the access database and have a scheduled job on the SQL Server instance that runs every 2 minutes to check for any record with a timestamp greater than the end time of the previous poll operation. Hope that makes sense.Tue, 18 Sep 2012 07:43:22 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHi allI am till struggling with this one, any ideas on the bet way to handle it?CheersDjSun, 16 Sep 2012 03:43:00 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1358795-1292-1.aspxHi AllI have created a touchscreen data collection system that stores its information on local Access 2007 databases. The reason for storing the information locally and not direct to the SQL server is to protect the data from Network Downtime. The touchscreens must still work even if the network is down.Now what I would like to do is be able to push or pull the data from the access databases to our back end SQL 2008 server say every couple of minutes, if the Network is down then the push / pull gets ignored until the database / sql server can see each other. This must be at the record level as some records may change within the 2 minutes between data push / pulls. At the moment I only have 2 touchscreen units but if successful this may be rolled out to the plant meaning I can have as many as 20 - 30 units. The database only has 3 tables and the information is linked by an id field via the Touchscreen. The tables themselves are not linked.Is it possible to create a replication on my SQL server to look at each access database, pull any new data and update any records that have been changed? I would like the SQL server to do the pull as it will reduce overhead on the touchscreen units although linking each remote database will be a pian.Hope you can help.CheersDJThu, 13 Sep 2012 12:42:49 GMTDouglasjbell