MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old September 27th, 2003, 09:53 AM
madFlasher madFlasher is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 5 madFlasher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Trigger on a timer

Can anyone explain how to fire a trigger based on a timer (say...every 30 seconds)? Can this be done completely within SQL code?

TIA

Reply With Quote
  #2  
Old September 27th, 2003, 11:16 AM
Scorpions4ever's Avatar
Scorpions4ever Scorpions4ever is offline
Banned ;)
Dev Shed God 5th Plane (7000 - 7499 posts)
 
Join Date: Nov 2001
Location: Glendale, Los Angeles County, California, USA
Posts: 7,334 Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 4 Weeks 12 h 1 m 53 sec
Reputation Power: 662
If it is ok to execute every minute, then look into creating a job (minimum interval is 1 minute for jobs). Search your Books Online for "jobs, creating", "jobs, defining" and "jobs, scheduling". Hope this helps
__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne

Puzzle of the Month solved by sizeablegrin, etienne141 and L7Sqr, superior C/C++ programmers of the month

Reply With Quote
  #3  
Old September 27th, 2003, 02:39 PM
madFlasher madFlasher is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 5 madFlasher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
ahhh-hah!

Great point, man. I'm not sure a job will handle what I need, tho. I need to copy data from two tables in one database to a table in another database. Can jobs operate across databases (both are MS SQL)?

BTW, thanks for the help!

Reply With Quote
  #4  
Old September 27th, 2003, 02:45 PM
Scorpions4ever's Avatar
Scorpions4ever Scorpions4ever is offline
Banned ;)
Dev Shed God 5th Plane (7000 - 7499 posts)
 
Join Date: Nov 2001
Location: Glendale, Los Angeles County, California, USA
Posts: 7,334 Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 4 Weeks 12 h 1 m 53 sec
Reputation Power: 662
Heck yeah, you can access data from databases on two different servers if you had to. From database1, you can do

SELECT * FROM database2.dbo.table2
or
SELECT * FROM database2..table2

If database2 is on a different server, you can do something like
SELECT * FROM server2.database2.dbo.table2

Only thing is you have to tell server1 about server2 first with sp_addlinkedserver.

However, if you just want to copy data from one server to another, consult your Books Online for "replication". HTH

Reply With Quote
  #5  
Old September 27th, 2003, 03:11 PM
madFlasher madFlasher is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 5 madFlasher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Wonderful!

Thanks for the help, man!

I think I'll create a job instead of replication. Looking at the books online, it looks like a few additional management things need to happen to setup replication properly. It seems a job would be less invasive. Also, I can set the job up to notify the operator if the job fails, right?

I don't mean to take advantage of your willingness to help but what would the syntax for such a query look like? Can you perform a SELECT on database1.dbo.table1 and then, in the same query do an UPDATE on database2.dbo.table2? Thus, moving the data from one database to another.

<Not to get too greedy but> what if I only wanted to update the data that had populated the table since my last job ran. This way, I'm not selecting every row in the table and moving it each time.

Once again, thanks!

Reply With Quote
  #6  
Old September 27th, 2003, 03:28 PM
Scorpions4ever's Avatar
Scorpions4ever Scorpions4ever is offline
Banned ;)
Dev Shed God 5th Plane (7000 - 7499 posts)
 
Join Date: Nov 2001
Location: Glendale, Los Angeles County, California, USA
Posts: 7,334 Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 4 Weeks 12 h 1 m 53 sec
Reputation Power: 662
>> Can you perform a SELECT on database1.dbo.table1 and then, in the same query do an UPDATE on database2.dbo.table2?
Code:
UPDATE t1
SET       t1.field1 = t2.field1,
             t1.field2 = t2.field2
FROM    database1.dbo.table1 AS t1
INNER JOIN database2.dbo.table2 AS t2
ON       t1.key_field = t2.key_field


You can also write a stored proc to do the transformation or whatever and then hook the stored proc to the job.

Reply With Quote
  #7  
Old September 27th, 2003, 03:47 PM
madFlasher madFlasher is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 5 madFlasher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Okay...okay....

I gotcha! I've gotta look-up more info on "INNER JOIN". That's great stuff, man.

Correct me if I'm wrong tho - your code would return all data in the respective columns regardless of age, right. For example, if in your code, "field1" were usernames and "field2" were passwords: your example would update database2 with EVERY username and password from database1 each time that the job ran, right? If the databases are large and this job runs every 5 minutes, would that create some latency? If so, is it much more complicated to only update the usernames and passwords that were added to database1 since the last job ran?

In any case, I REALLY appreciate the help.

Reply With Quote
  #8  
Old September 27th, 2003, 03:53 PM
Scorpions4ever's Avatar
Scorpions4ever Scorpions4ever is offline
Banned ;)
Dev Shed God 5th Plane (7000 - 7499 posts)
 
Join Date: Nov 2001
Location: Glendale, Los Angeles County, California, USA
Posts: 7,334 Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level)Scorpions4ever User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 4 Weeks 12 h 1 m 53 sec
Reputation Power: 662
There are quite a few ways to do this:
1. Modify the join clause to only select from table1 where fields are not in table2
2. Create a stored proc to select stuff from table1 into a temp table. Then delete all the rows from the temp table that exist in table 2. Then insert the remaining rows from temp table into table 2

Seriously though, I think you should really look into replication, because it is designed to do precisely what you need (i.e.) insert rows that don't exist in one database from another database.

Reply With Quote
  #9  
Old September 27th, 2003, 04:10 PM
madFlasher madFlasher is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 5 madFlasher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
U R Da MAN!

MAN! The help is MUCH appreciated! I think I have enough info to tackle this beast.

I will do more research on replication but I have always thought of it as something that is "bulky" and cumbersome - not something you have run every 5 minutes. I know that replication has come a long way over the last few SQL versions but I still view it as a way to backup data or update a remote database once every couple hours.

Sincerely man, thanks for the help!

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Trigger on a timer


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

 Free IT White Papers!
 
Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

 
Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

 
Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

 
Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

 
Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway