I am trying to understand federated servers. So far I have been able to link 2 servers, create a distributed view, and run a select and insert query against the view.
The next thing that I need to understand is how to deal with triggers and with identity columns (ie columns that have auto-increments).
Our application is using triggers on tables in several cases to keep track of create and update dates. So there is a trigger that automatically updates the datetime field on a table whenever that row gets updated or changed or created. This way the programmer doesn't need to remember to update those date time values. Now I understand that in the distributed view architecture the triggers on the underlying tables may not work. Can any one comment on this?
As well, I have a table which has the identity enabled, so it is incrementing a counter and creating a unique integer key value for each new record. How does this work when we move to a distributed architecture? How do I assure a unique key is generated in the distributed view architecture?
Hi, I read the following article related to "Federated Database Servers" But i am not getting what is this "Federated Database Servers", May be the language of this article i think little bit difficult. So, can anyone please explain me this. Wht are thse servers and when, why and where we use these servers?????
I'm trying to do some researh on the use of SQL's DPV. I'm looking for feedback from people who've actually done this production to know more about the design challenges and level of added administration required. Any information will be much appreciated. Thanks.
I've been working on this for a couple of days now, trying different scenarious.
Problem: I created a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to collect information and insert to a linked server server2.dbs2.owner.table2. When I run with the trigger active, SQL Analyzer just hangs in there "Executing Query Batch ..." indefinately. When I don't create the trigger, but run all parts manually from SQL Analyzer, it works fine.
What did I try: 1. create a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to insert into server1.dbs1.owner.table2. This worked fine. 2. create a stored proc to execute within the trigger on server1.dbs1.owner.table1 to insert to server2.dbs2.owner.table2. This just hangs in there "Executing Query Batch ..." indefinately.
What now? I have a suspicion that something is not working correctly with the triggers and the linked servers. Has anyone encountered a similar problem and what did you do to overcome this? I greatly appreciate all responses and suggestions. Thanks all.
I have two servers one on SQL Server 2000 one on SQL Server 7 I have setup the two servers so that they are linked and have added appropriate logins. How it works is a record is inserted into a database on SQL 2000 which has a trigger on it that send the record to a stored procedure on the SQL 7 server, from there this places the record into a table, which calls a trigger. Now this all works fine when I use the query analyser however when I don't use it, the record does not get inserted anywhere. Now I have stepped through it and it works up until the last trigger, if I remove that everything works fine. However the code in this trigger works fine, as when I use the quuery analyser everything works just as it should.
Does anyone have any suggestions as to how I can get this to work?
I am trying to create a trigger on table A existing in Database A on Server A which on insert,update,delete would reflect these changes on table B in DatabaseB on Server B. I wrote the following syntax but it doesnt work
CREATE trigger trg_upd_tableA On dbo.tableA for UPDATE as --update the fields declare @cust_no varchar SELECT @cust_no = cust_no FROM inserted
update ServerB.DatabaseB.dbo.tableB set entity_type_cd = 'MFG', cust_no = 6699, name_en = 'NOVARTIS', status ='A', create_date=getdate() ,modify_date=getdate() where cust_no= @cust_no
I get the error below
Another user has modified the contents of this table or view ; the database row you are modifying no longer exists in the database
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server] MSDTC on server 'DatabaseB' is unavailable'
I would like anyone to reply to me as soon as possible please because i need the solution urgently
Can i write triggers beteen the linked servers.Here is the whole scenario. I have 3 servers. Server aaaa and server bbbb has replication in between them.Server aaaa is a publisher and server bbbb is the suscriber. I have another server cccc.So If i make any change on a table xxxx should effect the tablee xxxx on server aaaa and bbbb.So i am writing a trigger(for insert,update and delete on the table) xxxx on the server ccccc.So that trigger should take care of any DML(insert,update and delete) happend on table xxxx on server cccc and should effect on server aaaa and then the replication should take care of server bbbb.This want i am planning right now?Is it a good practice to implement in such a requirement? Please help me. Thanks.
I have two SQL Server 2000 machines (server_A and server_B). I'veused sp_addlinkedserver to link them both, the link seems to behavefine. I can execute remote queries and do all types of neat thingsfrom one while logged onto the other.I'm working on a project to keep the data in the two systemssynchronized, so I'm using triggers on both sides to update eachother. For testing, I've created a simple, one-column table on bothservers, and also created a trigger on both tables. Consider thefollowing trigger code on server_A:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONINSERT INTO server_B.myDB.dbo.myTable SELECT * FROM insertedGOAnd also the following trigger code on server_B:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONINSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOBefore you start screaming about the recursive relationship betweenthese triggers, I'm well aware of that issue, so I'm wrapping thetrigger logic with a login ID test. The servers are linked using aspecial login account, I'll call it 'trigger_bypass_login', so thetriggers look like this:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF SUSER_SNAME() <> 'trigger_bypass_login'INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOAlthough this logically works fine, there seems to be a compile issue,because I'm running into the error:The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist inthe specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].What is strange is that I CONTINUE TO GET THE ERROR if I change thetrigger code to the following:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF 1=0INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM insertedGOSo obviously, it has nothing to do with the actual inserting that theINSERT performs, but rather the fact that the trigger INSERTreferences the linked server/table.So, I moved the INSERT statement to a stored procedure, and it worksand I no longer get the error:CREATE TRIGGER myTriggerON myTableFOR INSERTASSET XACT_ABORT ONSET NOCOUNT ONIF SUSER_SNAME() <> 'trigger_bypass_login'EXEC myStoredProcedureGOIt works.. BUT, the stored procedure does not have access to the SQLServer 'inserted' trigger table. I've tried usingDECLARE CURSOR myCursor GLOBAL FOR SELECT * FROM insertedand then letting the stored procedure reference the cursor, but then Ihave to deal with the cursor data on a column-level basis, which isnot an option in this project because there are 100's of tables withmany columns, which might change over time.So it is of extreme importance that I use INSERT INTO ... SELECT tomove the row data in a generic fashion.I hope I have provided enough, yet not too much, information.I would really appreciate any suggestions anyone might have as to howI might handle this situation. Thanks.Hank
I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?
I would like to use sequences and triggers to update table identity field with int value from sequence via before insert trigger. I'm searching on google for a few days and there are no same or identical article about this subject.
Is there any sample how to create table with column Id, Name, Comment and sequence (for generate int numbers for Int field in table) and trigger which will fired before insert and check is inserted Id is NULL and update this field from sequence or nothing if id is set up.
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Hi, What I want to do is simple to explain, hard to obtain (i guess): I want to fill a dropdownlist with the names of the sqlservers detected on the system. I´ve installed a sw recently that prompts for that, and when you select that server, then it prompts you to insert username and password, and then appears a dropdownlist again with the names of the databases of that sqlserver. Is this possible to do, at least fill dropdownlists with sqlservers and database names?!?I don't see how, thanks!
Just to confirm, do identity columns and XML columns work OK with database mirroring ? That is, all data types are supported with mirroring, and identities aren't an issue ?
Transactional replication with identity columns was a right pain in the **** in SQL 2000. I'm assuming that mirroring doesn't have these issues, but want to be sure.
I have one table namely consumer with approx 50 columns.
I have created one same table with audit prefix including 2 more column for action n timestamp fields.
My question is that if user change only 10 column data at a time: i want to add only that particular column data rather to add entire row. Currently I am adding entire row in audit table but now scenario is change to update only updated column data.
Hello, I am creating triggers on th efly from stored procedure.I have table 1in db1 and table2 in db2 and i want to capture inserts ,updates and deletes in table 1 to table 2 for which triggers are getting created from SP in SQL 2000.
If i create instead of triggers then its working fine but the row will not be inserted in the main table and i need rows to be inserted in the main table.The tables is having ntext columns.Any work around for this?
Hello, I have an assignment: write a stored procedure with a parameter @location this variable will in turn choose a stored procedure from a different server based on the value. I never heard of federated db's before, or federated stored procedures, can anyone help with this problem or give me clues or details? i am using SQL 2000, and i have access to multiple db's
I am trying to implement linked servers and I am having some trouble. The setup is as follows: I have two machines with the same SA login/pwd. BOTH machines have a database called: Federated_Bridge.
On machine 1: Federated_Bridge contains a table Fed_Entity_1
On machine 2: Federated_Bridge contains a table Fed_Entity_2
Machine 1, when I look it up in SQL Enterprise Manager it says: (local)
Machine 2, when I look it up in SQL Enterprise Manager it says: REALIBM2
When I expand those, I can see the databases folder and I can then see Federated_Bridge respectively.
On machine 1 (local) I am trying to create a linked server. I use the following SP call:
---------
use master go EXEC sp_addlinkedserver @server = 'TEST3', @srvproduct = 'SQLServer OLEDB Provider', @provider = 'SQLOLEDB', @datasrc = 'REALIBM2Federated_Bridge'
The above queries seem to execute fine. Afterwards, when I check in Enterprise manager, I can see under linked servers TEST3 .. but no tables or views are showing.
When I run the following select query from query analyzer, I get an error message:
SELECT * FROM Test3.Federated_Bridge.dbo.Fed_Entity_2
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers TABLE A: [ServerA].[ProdDB].dbo.[Orders] TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
Can anyone describe how SQLServer calculates identity columns? Does it use some internal counter when generating the next identity, or something a little more mundane such as gets the highest existing identity value at the point of the insertion and increments it by the IDENT_INCR value of the identity column?I’m not worrying about reliability or gaps in values, but i am wondering if it would be less efficient for me to manually manage the identity/primary key in the form of a counter in another table used to generate the new identity, or simply let the DB do it for me. I dont mind if there are gaps in the sequence etc. so would it be less efficient for me to calculate the field than SQLServer itself? Basically, is the overhead to the DB of me doing it greater than the overhead of the app doing it...Thanks
I have a question about IDENTITY columns. I am working for a client that has an entire employee database that uses IDENTITY columns without any Primary keys defined. I have never seen this done. Is it ok or should I recommend that it be changed to use Primary keys?
The DBA that built the database is no longer with the company and the client has no DBA. Where can I get some information on how to use IDENTITY columns? I did not get much from the help file.
I have been using the following query to identify the IDENTITY columnsin a given table. (The query is inside an application.)select column_namefrom information_schema.columnswhere table_schema = 'user_a' andtable_name = 'tab_a' andcolumnproperty(object_id(table_name), column_name, 'IsIdentity') = 1This works. When "user_a" performs the query, everything is OK.Now, another user wanted to use the same application. So, "user_b"clicks on a button, and the exact same query as above is run. (Nosubstitutions are made; user_b is trying to see the identity column in[user_a].[tab_a]). However, the query returns null, instead of theidentity column name. User_b can read the table and select from itjust fine.Why am I getting two different results against the same query? Do Ineed to rewrite the query to go against different information schemaviews?
I'm seeing some weird behavior regarding identity columns in MSSQL 2000.In a specific client database we have this table: ID Name SecLevDG Flags ----------- ----------------- ----------- ----------- 1029528 xxx 0 01029529 xxx 0 01049676 xxx 0 0 While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
alter table Authority drop constraint apkAuthorityId alter table Authority drop column SecLevDg alter table Authority add new_id integer identity
This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:
ID Name Flags new_id ----------- ---------------- ----------- ----------- 1029528 xxx 0 167772201029529 xxx 0 167772201049676 xxx 0 16777220
Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?
DBCC CHECKIDENT returns: Checking identity information: current identity value '1', current column value '1'.
DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included): Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. [...] CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).
I have an app that has multiple users inserting and updating from a couple key tables using SQL Server 2005. In my previous SQL coding life, I was able to make use of sequence.NextVal to find and lock the next available sequence value for a table. Currently I am in the SQL Server 2005 world and cannot do this. I have found all sorts of help for Ident_currentand Identity columns, but nothing on how to accomplish the same as a NextVal did. I can add one to the Ident_Curr but I think I run the risk of that value being used by another user before the current user can get his update in. Is that correct? Is there a way to accomplish what I am trying to do? Basically what I need to do is when a user inserts a record in table "Loads" I need to insert behind the scenes to table "Comments" with the ID of the row created in the "Loads" table. Thank you in advance, Garth
I am using sql 2000 dts package to migrate a databse to SQL Server 2005, everything works except for identity columns, SQL Server reorder the columns, and this breaks the referential integrity. Is there a way to stop that? Your help is appreciated.
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
I have a production database with about half the tables using IDENTITY columns for PRIMARY KEYS. This system is configured as both a Publisher and a Distributor. We are using Transactional Replication without updates. The SQL Server Agent runs every hour to pick up any changes and replicate them to the Subscriber (another SQL Server machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs) under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if the primary server goes down. And hopefully, only for a short time before the primary server comes back online. During the time that the failover is actually being used, the application will not make any changes to the database.
The IDENTITY values that are replicated need to stay with their original values. The replication process CANNOT assign new values to these columns when there are inserted into the database on the Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR REPLICATION' to the IDENTITY columns and the values will be preserved. But a collegue of mine says that resets the IDENTITY sequence on the subscriber and the 2nd time a row gets inserted on the Publisher, the values get messed up. On his system, he calls a stored procedure for the tables with IDENTITY columns, and in the stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication for every table with an IDENTITY column, or can I just add 'NOT FOR REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now. Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
hi there. I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication, I am receiving errors when I run Replication with Identity Columns, Thanks in advance Fin
Hi folks! I've a merge replication setup b/w two servers. Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]). Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message: "Checking identity information: current identity value '1', current column value '999'." How do i square this away?
