December 29, 2008 at 2:17 pm
I.e
REPORTDB i change the synonym to be pointing to MAINDB if i did an udpate statement
update table statement (this would infact update MAINDB).
So i just making sure that any REPLICATION udpates do not go back and udpate the MAINDB which is what the synonym is pointing to.
Sorry kind of hard to write up.
December 29, 2008 at 3:17 pm
Okay, I am still not clear what you are trying to say. If you have replication enabled between two databases and these objects which you have created synonyms for are involved - then, all you need to realize is that synonyms are just another name (reference) for those objects.
Another way to think of it is, whatever you would do to the actual object would happen exactly the same way when using the synonym.
So, if our synonym references an object in DBA and that object is replicated to DBB - any changes using the synonym will be replicated to DBB just as if you had accessed the object directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2008 at 5:16 pm
Thanks i am working on it and implementing ....cheers
January 13, 2009 at 12:27 pm
One last thing,
REPORTDB
has TABLE DELT.ACCT
I couldn't do
CREATE SYNONYM DELT.ACCT AS REPORTDB.DELT.ACCT;
because the object exists as a table DELT.ACCT
If i have DATAWAREHOUSE where most of the custom views are
i.e select * from DELT.ACCT
I could create the following:
CREATE SYNONYM DELT.ACCT AS REPORTDB.DELT.ACCT;
(This worked because the physical DELT.ACCT object does not exists it is in the DATAWAREHOUSE)
Question: If i want to use DATAWAREHOUSE basically as my pointer database - how will
users beable to see the table definition i.e if using MS ACCESS, rather than them having any access to REPORTDB, because i want to control all the pointing from one db.
Any advice.
January 13, 2009 at 1:44 pm
Sorry - but I don't understand what you are trying to do.
If you want users to access to access db1 - but the objects exist in db2, then you create the synonyms and grant access to the synonym.
If db2 actually exists on serverB - then, you created the linked server, then create the synonym using four-part naming through the linked server.
Example:
CREATE SYNONYM delt.Object AS linkedserver.db2.delt.Object;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2009 at 2:24 pm
Yes i got everything nailed that you listed to the T.
Now i have some users who still use the good old Microsoft Access and use Linked Databases,
which would be pointing to the db1 ......but but the objects exist in db2.
But they will not be able see these objects (tables) when using MS Access.
unless they point to db2 (which is not what i want).
I guess they have to start using Visual Basic and code in what they require.
Hope that now makes sense 🙂
January 13, 2009 at 3:30 pm
I don't use Access - so, was not aware that you cannot see synonyms from Access. As far as I know, they should be.
However, what you can do is create a view that wraps the synonym - that way, you can modify the synonym and the view does not need to be updated. Be aware, if the tables are not exactly the same - or, the definition of the view is not fixed this could be a problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2009 at 4:15 pm
Tracey - I'd actually look at an Access startup script to drop and re-add your linked tables based on which server is "up" at the time. There were several floating around in Google to do just that. Look up "msAccess RelinkTables". I have one that's been running for years, but it's not mine to give out at this point.....
This way you can attempt a connection to the main server on startup. If it happens to fail, pop an information screen up, and relink the tables.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 13, 2009 at 4:18 pm
Oh i startup script to add linked tables in MS Access hmmm let me look around for that.
That is phase 2 of this hugh project but im getting there.
Thanks guys.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply