March 10, 2009 at 2:47 pm
Can some one please provide me the script or the advance process for the following sql job-
Currently we follow the steps manually and I would like to automate the process-
1. Import the table [message]from server a to server b
2. If the job takes more then 20 seconds then manually disable the job
3. rename the old table with new name [message10]
4. Create new table [message]
5. there is one view for webreporting uses multiple message tables so update the view to add the renamed table
6. enable the job to import the table to new table.
PLEASE PLEASE PLEASE help me- Thanks
March 10, 2009 at 3:17 pm
Hello
I don't really know what's your specific problem now.
Maybe I'm wrong, but in my opinion this is a forum to find help for a specific problem like
* How to end a script after 20 seconds?
* Which ways are available to transfer data from one to another server?
* How can I rename a table?
I don't think you want the people here to write a complete application for you, do you?
Greets
Flo
March 11, 2009 at 7:18 am
I am sorry if you are confused with the steps. Let's go with your first suggested question-
How to end a script after 20 seconds?
March 11, 2009 at 8:27 am
one of Florian's many points is this:
SQL server does not natively have a rollback after x seconds functionality. because a database needs to preserve ATOM-ocity, it's an all-or-nothing scenario, whether it takes sub-one second or 24 hours to execute.
Only an outside application could rollback after x seconds, and it would need to spawn a separate thread to do the update, while the main thread does the timing....even then, the thread would continue tio execution, adn just rollback if the main thread decided it took too long. You would have to develop that applicaiton.
Further, I think you have a hole in your logic. why rollback after 20 seconds? why not 24 seconds? 30 seconds? most likely, this is an arbitry time limit based on a person being impatient, and has nothing to do with makeing sure the data goes into the system based on any business rules.
why don't you explain in more detail what you are trying to do and we can offer an optimal solution
;
for example, why do you need to rename the table? why not just keep track of what you migrated?(your [message] table DOES have a PK in it, right?
Lowell
March 11, 2009 at 8:36 am
Lowell (3/11/2009)
SQL server does not natively have a rollback after x seconds functionality. because a database needs to preserve ATOM-ocity, it's an all-or-nothing scenario, whether it takes sub-one second or 24 hours to execute.
Only an outside application could rollback after x seconds, and it would need to spawn a separate thread to do the update, while the main thread does the timing....even then, the thread would continue tio execution, adn just rollback if the main thread decided it took too long. You would have to develop that applicaiton.
Hello Lowell
Sure you are right but there may be some ways to handle this. It would be possible to work in batches and check a timestamp for exiting the statement.
As Lowell wrote, what exactly is the table structure to be exported what is the destination structure?
Greets
Flo
March 11, 2009 at 9:37 am
Thanks guys for the response. Ok the logic behind this process is the import job ususally takes 5 seconds to finish, but some times this job can take 15 to 20 minutes and the reason is if the server b table has more then 2 million records then it takes time to insert the additional data. When the query takes longer then what we usually do is go through the steps I provided in my previous request to stop the job and rename the table then create a newtable with oldname then restart the job to run it faster. I am trying to automate that manual process to make my life easier. I hope my explanation help you guys to come up with some smart idea-
March 11, 2009 at 10:11 am
psuedo code wastes your time and mine...give us the specifics so we can help with specific answers.
1. Import the table [message]from server a to server b
--are you doing this to get SERVERA's DATA??
SELECT *
INTO MYLOCALTABLE
FROM SERVERA.DBO.SOMEDATABASE.MESSAGE --just get them ALL! 1mesge or 2 million.
2. If the job takes more then 20 seconds then manually disable the job
--WHAT , specifically does the job do? does it do it to SERVERA's data, or a copy of it on SERVERB?
--does it copy the messages? use a cursor? email them? truncate SERVERA's message table? WHAT?????
--if this is run regularly, how did it change from a managable number of messages to more than 2 million messages getting built up? are these "new" messages or all messages since the beginning of time?
3. rename the old table with new name [message10]
--which server is this occurring on? SERVERA or SERVERB
4. Create new table [message]
--again,which server is this occurring on? SERVERA or SERVERB
5. there is one view for webreporting uses multiple message tables so update the view to add the renamed table
--avoid renaming and we can skip this step...fixes to teh job will fix this work around
6. enable the job to import the table to new table.
--fixing the core issue will avoid having to disable/enable any job.
Lowell
March 11, 2009 at 10:27 am
PHEW- Ok
1. Import the table [message]from server a to server b
--Trying to get [server a].[message table] DATA via ssis package to [server b]. [message table]
2. If the job takes more then 20 seconds then manually disable the job
-- on SERVERB message table data takes more then 20 second to get inserted then step three?
3. rename the old table with new name [message10]
--which server is this occurring on?occuring on as I said 'SERVERB'
4. Create new table [message]
--again,which server is this occurring on?SERVERB
5. there is one view for webreporting uses multiple message tables so update the view to add the renamed table
--avoid renaming and we can skip this step...fixes to teh job will fix this work around
6. enable the job to import the table to new table.
March 11, 2009 at 11:39 am
sorry...
my point is we need to fix the job, not continue to work around a time constriant and try to stop/disable/continue.
lots of issues can be killing your job:
out of date statistics, cursor performance, job design, the sql server "tipping point" where it will slow down when manipulating a lot fo rows.
what is it that is happening where you suddenly jump from a managable number of rows to 2 million plus?
if you paste the code the job is performing, we could probably help.
without developing an application, there is no way to stop/disable/continue like you want.
Lowell
March 11, 2009 at 12:09 pm
hydbadrose (3/11/2009)
Thanks guys for the response. Ok the logic behind this process is the import job ususally takes 5 seconds to finish, but some times this job can take 15 to 20 minutes and the reason is if the server b table has more then 2 million records then it takes time to insert the additional data. When the query takes longer then what we usually do is go through the steps I provided in my previous request to stop the job and rename the table then create a newtable with oldname then restart the job to run it faster. I am trying to automate that manual process to make my life easier. I hope my explanation help you guys to come up with some smart idea-
Why not check the number of records before trying the insert?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 12, 2009 at 7:28 am
Yes, that will be first option. My main concern is on view that how should I automate the view process. Instead of adding a new table in view manually, I want to automate this process.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply