Creating Triggers Among Multiple Database Servers

Nov 14, 2000

Hello,

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?

Any help is appreciated!!
Thanks,
Lisa

View 1 Replies


ADVERTISEMENT

Creating A Database From Multiple Databases Accross Multiple Servers

Sep 13, 2007

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a



I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

View 8 Replies View Related

Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?

Mar 7, 2008



Hey!

DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?

View 3 Replies View Related

Creating Multiple Triggers Is Same Sql Script

Jun 14, 2004

I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

View 6 Replies View Related

Triggers In Different Database Servers

Feb 16, 2004

Dear All,

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


Thanks alot in advance,
Noha

View 2 Replies View Related

Accessing One Database From Multiple SQL Servers

Dec 1, 2006

Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.

My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.

Does this scenario completely rule out the use of SQL Server Express?

Thanks

View 3 Replies View Related

SQL 2012 :: Multiple Database Servers - One Instance Name

Apr 24, 2014

Scenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.

Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.

So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.

Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.

There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.

I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.

View 1 Replies View Related

Multiple Triggers On A Table Or Encapsulated Triggers

May 12, 2008

This isn€™t an problem as such, it€™s more of a debate.

If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.

www.handleysonline.com

View 12 Replies View Related

Triggers Across Servers

Aug 6, 2002

Hi All,

Can I write a trigger on one server and if I reference the server.db.owner.table can it work on a different server?

Thanks in advance,

Brad

View 1 Replies View Related

Creating Multiple Database With T-SQL Script

May 2, 2002

Hi everyone:

I have a question about T-SQL scripting:

I want to create several databases with identical properties: size, name (database1, database2, database5, databaseN), path, etc, and I would want to automate the process

So, I downloaded a good T-SQL script from the Internet, which automates the process of creation of ONE database

The problem is I would like to "put" this script within a bucle sentence; I mean, within a FOR or WHILE sentence, so I could create, for example, thirty identicales DBs without prompting me for the database name (without desktop interaction, I mean)

Here you have the generical T-SQL script:


USE master
GO
CREATE DATABASE emc
ON
( NAME = 'emc',
FILENAME = D:MSSQL7DATAemc.mdf',
SIZE = 5 )
LOG ON
( NAME = 'emc',
FILENAME = 'DMSSQL7DATAemc.ldf',
SIZE = 1 )
GO


If any oy yoy have some similar script for doing this, I would highly appreciate.

Many thx.

View 1 Replies View Related

Transact SQL :: Powershell Script To Fetch Database Usage Details For Multiple Servers (report)

Oct 28, 2015

Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.

Details: servername, databasename, datafile usage, logfile usage, free % age...etc.

View 3 Replies View Related

Triggers And Linked Servers

Jun 17, 2004

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.

View 1 Replies View Related

Linked Servers And Triggers

Oct 21, 2004

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?

Thanks :-)

View 2 Replies View Related

Triggers On Linked Servers

Apr 19, 2004

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.

View 5 Replies View Related

Problem With Triggers Between Linked Servers

Jul 20, 2005

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

View 4 Replies View Related

SQL Server 2008 :: Creating Multiple Copies Of A Database

Mar 21, 2015

I am creating a program that will take a master database and create separate databases for class room training.creating my own app to do this since it will have other stuff to do.i will have a master database that i will need to create multiple copies of. 2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.each computer in the classroom will access its own copy of the database/windows folders.

What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.

View 5 Replies View Related

Federated Servers - Triggers And Identity Columns

May 1, 2008



Hi Folks.

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?

Thank you in advance.

View 1 Replies View Related

Creating Triggers

Oct 25, 2007

I have never work with triggers before, so I need a hand on how to create a trigger when a row is updated on a table. Lets says I have this table call "Events" and I want to send the new row to another table call "History". When a new row is inserted I want to select that row and inserted in the History table. Can someone give me a hand with this?
 
Thanks,
Erick

View 18 Replies View Related

Creating Triggers(urgent)

Jan 9, 2002

Hi Guys,
I have a requirement to create update,delete triggers in all the columns of each table in my development database. I would really appreciate if anyone could guideme or send me the script.
Thaks a lot for your consideration
Jay

View 1 Replies View Related

Creating Triggers Question

Jul 2, 2001

Hi,

I'm new to triggers, does anyone know any good resources/articles about creating triggers?

I need to create a trigger for table X, to check BEFORE the update of the table whether a double value for column [X].[Y] exists, if it does (and the value is NOT NULL), do not allow update, if it doesnt allow update/insert statement. This is different from unique keys because as I understand, SQL Server doesnt support multiple NULLs.

Thanks everyone!

Ilya Zherebetskiy
Brainlink Development
http://www.brainlink.com

View 1 Replies View Related

Creating Triggers On System Tables!!

Feb 27, 2002

Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.

View 2 Replies View Related

Creating Triggers On System Tables

Mar 1, 2002

Hi,

Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.

I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??

Thanks

Rob

View 1 Replies View Related

Creating Triggers Using Managed Code In SQL Server 2005

Mar 6, 2008

Hi all( Create a VB 2005 SQL Server Project ) 

i want to Create a Trigger using Managed Code in VB.Net (.NET CLR integration with SQL Server.)Somebody help me.Thanks

View 2 Replies View Related

Creating Triggers In Transactional Replication On The Subscriber Side

Jun 17, 2007

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?



Thanks,

Arslan.

View 2 Replies View Related

Multiple Triggers

Jul 20, 2005

When a trigger on one table updates another table, does it activatethe update trigger on the updated table?For ex:1. Table A has a trigger for update2. Table B has a trigger for update that updates table AWill the trigger no table A run when table B trigger updates table A?Thanks

View 2 Replies View Related

SQL Server 2008 :: Getting Error While Creating Linked Servers

Sep 15, 2015

Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3

/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'

This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".

How to setup the linked server........... Below are the drivers installed on the SQL server

PostgreSQL35W
PostgreSQL30

View 2 Replies View Related

Problem Creating Indexed Views With Linked Servers

Jul 20, 2005

Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!

View 1 Replies View Related

Multiple Update Triggers

Oct 17, 2001

What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire

View 1 Replies View Related

Triggers - Run For Multiple Updated

Sep 24, 2005

I am new to trigger and I have following question.

I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.

Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?

I hope I make it clear and please help me on this! :o

View 8 Replies View Related

One Trigger Vs Multiple Triggers ?

Aug 17, 2007



I have a question regarding Triggers.

Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.

Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.

Or I can create three different triggers each for Insrt, update and delete.

Which option is better for performance?

Thanks



View 2 Replies View Related

Can't Map Other Sql Servers Without Creating Alias With Port Number On Client Network

Aug 24, 2005

I can't map other sql servers without creating alias with proper port number on client network utility. Other users using same version of the client tools, MMC, SQL DMF etc. I need to map 70 sql servers on using my client tools.
Any help is appreciated.

thanks,

View 3 Replies View Related

Triggers Affecting Multiple Tables

May 4, 2004

I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".

My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.

Gazzou

View 7 Replies View Related

Triggers Prevent Updates To Multiple Records

Mar 13, 2002

I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?

Many thanks for advice.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved