April 14, 2012 at 3:09 pm
I've searched but I'm not sure of the correct term for what I want to do.
I have a database that gets customer details from a program. I cannot alter the program. I wish to have a table on another database that will run on the same server that is a duplicate of the customer table. It will need to automatically add, delete and update records as the original does.
Is this possible and if so where do I start.
Your help is appreciated as I have googled until my eyes hurt.
April 14, 2012 at 4:35 pm
look into transactional replication.
the table will have to have a primary key.
---------------------------------------------------------------------
April 14, 2012 at 7:13 pm
Thanks for the pointer. I will go Googling in the morning.
April 15, 2012 at 8:13 am
OK, back from Googling betwixt wallpapering. Would I be correct in thinking that I am unable to do this with 2005 express (which is what I am using) as I cannot find 'Local Publications' folder under 'Replication'?
April 15, 2012 at 2:49 pm
cc 49739 (4/15/2012)
OK, back from Googling betwixt wallpapering. Would I be correct in thinking that I am unable to do this with 2005 express (which is what I am using) as I cannot find 'Local Publications' folder under 'Replication'?
Express edition may only act as a subscriber, not a publisher
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 3:57 am
Thanks for the info Perry. The only reason I started down this route was that the caller ID software I am using would not connect to my sql database.
I started to investigate and found that if I select the target database as Databases>System Databases>master it picks up the tables in System Tables and I can select them with no issues.
If I select the database that contains my customers I get a runtime error 3265 'item cannot be found in the collection corresponding to the name or ordinal'. Googling reveals that this may be a VB error but I cannot rewrite the commercial software so I am looking for a solution.
It is almost as though it looks in the system tables folder inside the tables folder but not at the actual tables.
I'm sure it must be a permissions thing. I have tried using both windows and sql authentication. The fact that it connects ok to both databases but just fails to get the table from my one. Just before it crashes I see sysallocunits displayed in the table selector drop down box of my caller ID prog.
As a foot note I set up an access database to populate from the customers table in my sql database and connected my caller ID program to the access database and all works?
April 16, 2012 at 4:17 am
Are the objects in a separate schema, i.e. not dbo?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 5:17 am
Perry, firstly thank you for taking the time to help.
Bot objects (tables?) are prefixed with dbo
April 16, 2012 at 5:55 am
further to the last nugget, even when I connect successfully to the 'master' database all is good if I select the tables that are visible in Databases>System Databases>master>Tables>System Tables folder eg dbo.spt_values but the dropdown box in my software also includes the sysallocunit table that appears when my customers database crashes hand if selected under the master database it crashes with the same error.
Why is the sysallocunit table not visible with sql server management studio express? Is this relevant?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply