In a integration project I am moving data from A to B.
First time is fine - since table B is empty.
However next time I run the process, I would like to delete all records in B before I run the project again.
What is the best way to delete / clean up data when you re run the process ?
Cheers, T
Issue a DELETE or TRUNCATE from an Execute SQL Task.
-Jamie
Hi all
in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me.
I appreciate your response !
You don't need to check for status if you create a proxy account for the Agent, that is clone an Admin level permissions to run the Agent, run a search for SQL Server Agent Proxy account in the BOL(books online). The reason is when you run a Job manually it runs in the context of your permission as a Job it becomes the Agent's which means it needs your level of permissions to run it. Hope this helps.|||Hi Caddre
Thanks for your response. I project workd is like this:
it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?
Thank you very much !
|||
Liu_andi:
Hi Caddre
Thanks for your response. I project workd is like this:
it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?
Thank you very much !
When you start SQL Server you are starting the SQL Server service it is different from the Agent without the proxy account the Agent will not be running in an application as you expect it, threads are not relevant to the issue it is related to the Agent having enough permission to run, it takes a lot of people time to understand but fix it or your jobs will not run as you expect. Hope this helps.
|||Hi Caddre
you are so nice to give me response.
you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:
exec master..xpcmdshell 'net start'.
the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?
|||
Other way I think is more better to use:
declare @.service nvarchar(100)
select @.service = case when charindex('\',@.@.servername)>0
thenN'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end
exec master..xp_servicecontrol N'QUERYSTATE', @.service
the return is "Running.", which expresses the status of SqlAge is running.
|||
Hi Caddre
you are so nice to give me response.
you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:
exec master..xpcmdshell 'net start'.
the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?
This is a query against one database that exposes all your SQL Server that is not prudent, what I am telling you to do is a simple process running under a service clean and valid. I am telling you to do it the correct way what you actually do is your employer's business. Hope this helps.
Hi all
In my project, i need to call some jobs to finish some things. But sometimes, Sql Agent is not run. I need to check the status of it. Are there some functions or other ways to check it. if you know, please response me.
thank you !
You can use this scriptdeclare @.service nvarchar(100)
select @.service = case when charindex('\',@.@.servername)>0
then N'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end
exec master..xp_servicecontrol N'QUERYSTATE', @.service|||Great answer, thank you !
