|
Very easy to do. Like this:
USE[master]
EXEC sp_addlinkedserver N'{computer}\{database},
N'SQL Server'
Literally ...
|
|
|
|
|
I have a database that receives weather information. The main table is (somewhat simplified)
LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float
Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement)
All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc.
If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running.
What is the best approach to take to allow merging of the data?
|
|
|
|
|
Why are you using merge instead of an insert?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The routine uses the MERGE statement to determine if a record for the particular observation exists. If it does not, it inserts a new record. If a record does exist, it updates the appropriate field.
Problem comes in that there are three raw import records for the particular observation. So the first merge may try to insert something for a high temperature while at the same time the system is trying to insert for the low temperature.
I can use a cursor to cycle through the imported data row by row but want to avoid the speed hit.
I also can't change the main table to hold a record for each individual type of temperature because of the huge number of records (~17 million per observation cycle)
|
|
|
|
|
Have only one thread perform the MERGE portion?
|
|
|
|
|
Here's an idea:
Set up a table that is a queue table.
Just insert all the data into this table - no merge needed.
Then you have one process that reads from this table performing merges and deleting the row from the queue once the merge has been performed.
As you are only performing inserts then deletes on the queue I can't see a deadlock occurring on the queue - as the process will be issuing one merge at a time you should avoids deadlocks on your merge table too.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I used a mutex to control the calls to the MERGE sections and it is running. If performance becomes and issue, I will split the program in two so a multi threaded section pulls data and inserts to a temporary table and a single threaded program calls for merging the data.
|
|
|
|
|
Sounds good - I always revert to the old carving code in stone method when all else fails so I hope the mutex works
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
how did i add calender to my site
|
|
|
|
|
kalula wrote: how did i add calender to my site
How would you think that we would know how you added a calendar to your site? We cannot see your project, your code or read your mind. Besides that, what does your calendar on your site have to do with a database?
|
|
|
|
|
ZZzaaaappp take a look on your desk you will find a brand new calendar in the burnt bit on the corner - just take it out to your site and nail it to the entrance.
Your question makes as much sense as my response. Read the guide lines [^] and ask a sensible question in the correct forum (probably the ASP forum)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how did i add calender to my site
|
|
|
|
|
Wrong forum. You could find many HTML/javascript calendars in web. Please use Google.
thatrajaNobody remains a virgin, Life screws everyone 
|
|
|
|
|
Dude's,
want to insert the values in one table(say bookingtable),
the remaining seats column in another table must be suitably updated.
Trying to concept using of Triggers. Can I go with the following way? but my table is not updated. Pls help on this.
CREATE TRIGGER Trgupdtsts ON Table_FB2
FOR INSERT
AS
DECLARE @FNo INT,
@DOJRNY DATETIME,
@RMNGSTS INT
SELECT @FNO = i.FNO,
@DOJRNY = i.DOJRNY
FROM inserted i;
set @RMNGSTS = ("need to insert logic here?")--'Record Inserted'
INSERT INTO TBL_FSTS
(FNO , DOJRNY , RMNGSTS)
VALUES (@FNO , @DOJRNY , @RMNGSTS)
--select @RMNGSTS from inserted
|
|
|
|
|
What is the logic for finding @RMNGSTS Values?
|
|
|
|
|
Hi prabu,
Want to update the Remaining seats in the another table. So I used @RMNGSTS as variable.
How to frame the entire queries? Is the above is correct?
|
|
|
|
|
Hi,
You need to insert same details to another table right, Use same Same logic. No need to assign a values in variable.
|
|
|
|
|
dude, didnt get your logic. Kindly send me the codes.
|
|
|
|
|
Another one with a crappy design, available seats should be an enquiry on the Bookingtable! while using a trigger spit may fix this issue the design should be fixed to remove the need for a trigger spit
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
One could get the opinion that you don't like triggers spit 
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
One of my earliest sql jobs was to track down a bug, it was in a trigger spit and it took me ages to find the bloody thing, that was over 25 years ago and I have not forgiven them their sins!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I don't like them either, I'm considering triggers and distinct as a sign that there is something wrong in the design and query respectively. Cannot always be without them, but I try hard.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Friends,,
Atlast what is the exact solution? Everybody posting ur concerns, but I want answer
|
|
|
|
|
Create a stored procedure.
Pass the update information into the stored procedure via parameters then update the relevant tables via the stored procedure.
I would tend to agree with Mycroft spit that triggers can hide things from you when you are trying to debug a problem later on.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
One could get the impression that you don't like Mycroft. 
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
