Need to execute a very time consuming Stored procedure on a remote machine.
-
Hi, I need to execute a Stored proc on the online database. The problem is that there are about 0.8 million records and this will take lots of time like 6-8 hrs atleast. If I execute it by cnnecting from here it has a chance of geting cancel b4 completion. I'm using SQL 2005 Express. Is it possible to just execute the SP from here and then it carries on untill its complete. regardless of whther I'm connected or not. Thanks in advance
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
Hi, I need to execute a Stored proc on the online database. The problem is that there are about 0.8 million records and this will take lots of time like 6-8 hrs atleast. If I execute it by cnnecting from here it has a chance of geting cancel b4 completion. I'm using SQL 2005 Express. Is it possible to just execute the SP from here and then it carries on untill its complete. regardless of whther I'm connected or not. Thanks in advance
Rocky Success is a ladder which you can't climb with your hands in your pockets.
Stored procedure should executed from the server. Is there anyway to reduce the number of records it must chew through?
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
-
Stored procedure should executed from the server. Is there anyway to reduce the number of records it must chew through?
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
you didnt understand my question. I know SPs are supposed to be executed on Server, offcourse that's what they're there for. My question is that when you execute an SP on an online server, it gets stopped when you get disconnected from the internet. How can we avoid this problem?
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
Hi, I need to execute a Stored proc on the online database. The problem is that there are about 0.8 million records and this will take lots of time like 6-8 hrs atleast. If I execute it by cnnecting from here it has a chance of geting cancel b4 completion. I'm using SQL 2005 Express. Is it possible to just execute the SP from here and then it carries on untill its complete. regardless of whther I'm connected or not. Thanks in advance
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
Try creating a job. so that the same will be executed at server and need not have an active connection from client.
Regards KP
-
I think SQL 2005 EXPRESS doesnt allow you to create a job. Plz tell me If I'm wrong... Many thanks
Rocky Success is a ladder which you can't climb with your hands in your pockets.
Is the remote database also SQL 2005 Express? Also, you mentioned that there was a lot of data involved, is that data already located on the remote SQL installation or is it being copied from your local machine to the remote machine?
Mark's blog: developMENTALmadness.blogspot.com
-
Is the remote database also SQL 2005 Express? Also, you mentioned that there was a lot of data involved, is that data already located on the remote SQL installation or is it being copied from your local machine to the remote machine?
Mark's blog: developMENTALmadness.blogspot.com
yes the remote server is also SQL Express 2005, and the data is on it as well. Actually i mistakenly curropted the data in a field so I wrote the SP for that. I've found a useful tool on CP and I've used it to run the SP on the remote Server, Now I hope this gets done as its supposed to be
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
yes the remote server is also SQL Express 2005, and the data is on it as well. Actually i mistakenly curropted the data in a field so I wrote the SP for that. I've found a useful tool on CP and I've used it to run the SP on the remote Server, Now I hope this gets done as its supposed to be
Rocky Success is a ladder which you can't climb with your hands in your pockets.
Happy to hear you've found your solution.
Mark's blog: developMENTALmadness.blogspot.com
-
Happy to hear you've found your solution.
Mark's blog: developMENTALmadness.blogspot.com
-
well if it doesnt go too well, can u give me some other alternative?
Rocky Success is a ladder which you can't climb with your hands in your pockets.
I'm assuming your reference to a tool you found on CP implies you can create executables on the server and run them. Knowing exactly the amount of control you have over the the remote machine would make it easier to point to a single possible solution. Can you setup a windows task? If so I would use the sqlcmd tool: http://msdn2.microsoft.com/en-us/library/ms165702.aspx[^]. You could then create a .bat or .cmd file and tell it to execute the stored procedure with a single line of code and reference the file from windows task scheduler and tell it when to execute. Then it will execute without requiring you to be logged into the remote server. The command would look something like this:
sqlcmd -S (local) -d databaseName -E -Q "exec storedProcname 'value1', 2, 'value3'"And the task would run as you or a windows account with permissions to the server/database. Or if you're using SQL Authentication:sqlcmd -S (local) -d databaseName -u username -p myp@ssw0rd -Q "exec storedProcname 'value1', 2, 'value3'"NOTE: the parameter flags are case sensitive (-Q is not the same as -q). This would be the best solution because any other solution would probably require you to be logged into the machine somehow (terminal services, etc.). This would still work as long as you can disconnect your session w/o it logging you off the machine which would allow any processes you start to continue to run. But setting up a task would be better, especially if for tasks which need to be run more than one time.Mark's blog: developMENTALmadness.blogspot.com
-
I'm assuming your reference to a tool you found on CP implies you can create executables on the server and run them. Knowing exactly the amount of control you have over the the remote machine would make it easier to point to a single possible solution. Can you setup a windows task? If so I would use the sqlcmd tool: http://msdn2.microsoft.com/en-us/library/ms165702.aspx[^]. You could then create a .bat or .cmd file and tell it to execute the stored procedure with a single line of code and reference the file from windows task scheduler and tell it when to execute. Then it will execute without requiring you to be logged into the remote server. The command would look something like this:
sqlcmd -S (local) -d databaseName -E -Q "exec storedProcname 'value1', 2, 'value3'"And the task would run as you or a windows account with permissions to the server/database. Or if you're using SQL Authentication:sqlcmd -S (local) -d databaseName -u username -p myp@ssw0rd -Q "exec storedProcname 'value1', 2, 'value3'"NOTE: the parameter flags are case sensitive (-Q is not the same as -q). This would be the best solution because any other solution would probably require you to be logged into the machine somehow (terminal services, etc.). This would still work as long as you can disconnect your session w/o it logging you off the machine which would allow any processes you start to continue to run. But setting up a task would be better, especially if for tasks which need to be run more than one time.Mark's blog: developMENTALmadness.blogspot.com
Mark J. Miller wrote:
Can you setup a windows task?
oh no I'm afraid I can't. and the worse thing is that I can't even chk the status of the job I executed it coz I dont have any permission on the system table that is used to get the status of the job.
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
Mark J. Miller wrote:
Can you setup a windows task?
oh no I'm afraid I can't. and the worse thing is that I can't even chk the status of the job I executed it coz I dont have any permission on the system table that is used to get the status of the job.
Rocky Success is a ladder which you can't climb with your hands in your pockets.
What kind of access rights do you have on the remote machine? Can you use some sort of remote desktop tool? In order to do what you describe you will need to somehow start a process on the remote machine from which you can disconnect w/o killing the process which is running on the remote machine. Otherwise, if you don't have any access other than file copy and dbo access to the database then you'll have to ask the administrator of the machine to run the sqlcmd script for you I mentioned in my last post.
Mark's blog: developMENTALmadness.blogspot.com
-
What kind of access rights do you have on the remote machine? Can you use some sort of remote desktop tool? In order to do what you describe you will need to somehow start a process on the remote machine from which you can disconnect w/o killing the process which is running on the remote machine. Otherwise, if you don't have any access other than file copy and dbo access to the database then you'll have to ask the administrator of the machine to run the sqlcmd script for you I mentioned in my last post.
Mark's blog: developMENTALmadness.blogspot.com
Mark J. Miller wrote:
you'll have to ask the administrator of the machine to run the sqlcmd script for you
:doh: I think thats what I need to do. ur right! its a shared server so they wont take the risk of allowing us to run windows services
Rocky Success is a ladder which you can't climb with your hands in your pockets.