Disable Jobs With TSQL
Dec 9, 2005Does anyone know how to disable and re-enable jobs using tsql?
View 1 Replies
ADVERTISEMENT
How To Disable/enable Jobs For Database That Has Failed Over
Nov 22, 2006Hi,
I currently have serverA importing data from an AS400 oledb connection and serverB on hot-standby as a mirror. The data import is being performed via an SSIS scheduled job.
I've yet to implement any solution in the following case:
If the database on serverA fails, it will failover to the mirror. But the jobs will still be on serverA. Ideally, I'm assuming the jobs should not run on serverB since it will be attempting to update the mirrored database. So, I am wondering if both servers can have identical copies of SSIS packages and jobs and simply (?) enable or disable them based on which server will be hosting the principal database.
Is there any solution where the jobs can automatically be disabled on the server with the mirrored database and enabled on the principal database depending on its status?
A Simple TSQL To Find The Last N RUN Status Of The SQL Jobs / Job History
Feb 22, 2008Replace the <<job name>> with the actual job name
---Last n RUN status of the job------
SELECT TOP 20
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
WHERE SJ.name LIKE '%<<job name>>%'
AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job
ORDER BY run_date DESC, run_time DESC, step_ID DESC
---Last n RUN status of the job---------
Newbie In Tsql - Finding Latest Failed Jobs
Mar 16, 2008I am a newbie to tsql. I am trying to find if a job has failed, if so I only want to pickup the lastest job. I want to see name,message and run date. I know I need to find the max run_date and run_time for each job_id. I know that I need to join the sysjobhistory table and the sysjobs tables. I also know that I need status not equal to one. I have tried thousands of different things and nothing seems to work. If I ever get this to work then I only want to look at jobs in the last 24 hours. I am not going to include any of my job since I have tried so many things and I have been all over the map.
SQLAgent - TSQL Jobs Hangs (SQL Server 2005)
Aug 15, 2007
I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR]
,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME]
,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM])
SELECT
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Nov 19, 2007Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
Can We Disable A Trigger In SQL 7.0
Jun 14, 2002Is there a way to disable trigger for a time window and enable it when desired.
View 1 Replies View RelatedDisable Trigger
Jul 12, 2001Is it possible to disable a trigger to manually manipulate data without completely removing it? Thank you.
View 2 Replies View RelatedDisable Identity
Oct 26, 2000A quick question, how can I disable identity for the identity column
without dropping that column? Thanks!
Xiao
Disable Logs
Aug 28, 2000Is there a way to disable logging ?
I'm about to split a 1000000 rows table into three other tables , and would prefere not to log this action ...
Does anyone know how to do it in a SQL 7.0 statement ?
Disable Triggers
Nov 21, 2001I don't seem to be able to find a command to disable triggers. Can this
be done in SQL7?
Thanks.
Disable Trigger
May 11, 2001Is it possible to disable a trigger in SQL Server 6.5?
Is so, how can i do that?
How To Disable Locking?!?
Apr 2, 1999read-only option
dbo-use option
...
Anyone any ideas ?!?
Disable Trigger
Oct 25, 2004Hi,
can I disable a trigger in Sqlserver 2000??? When i run a store procedure who works with one table i want that the trigger doesn´t work it. After that the trigger would be enabled again.
I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.
Thanks.
Disable Trigger
Mar 23, 2004Sorry, may be it is very simple, but
how can I disable all trigger on a tabled under sql 2000
SQL 2012 :: CDC Is Disable
May 20, 2014CDC is disabling when we do bulkinsertsupdateschange schema .how to findout why CDC disabled and how to prevent it ?
View 3 Replies View RelatedIs There Any Way To Disable The Primary Key ..?
Feb 24, 2007Hi,
Is there any way to disable the primary key constraint?.I want to move the records from one table to another.say column 'x' in the source table doesnt contain a primay key.But my target table column 'x' contains primay key.
How to ovecome this problem?
Thanks in advance
Disable Indexes
May 30, 2007Hello all.
Could anyone advise me how to disable indexes on a specific table at the start of a script, then re-enable them at the end?
Thanks all.
Disable DatabaseMail90.exe
Feb 28, 2008I am looking for a way to disable DatabaseMail90.exe (temporarily) while still allowing emails to queue up.
At first, I thought the solution was sysmail_stop_sp... but if I use this... calls to sp_send_DBMail fail with the following message:
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.
I'd like the emails to be queued... I just want to postpone sending them. Anyone know how to do this?
- Alan D. Nelson
Is It Possible To Disable All Restrictions On A Ms Sql DB?
Jul 20, 2005In meen. primary keys, NOT NULL, IDENTETIES...et.cI have to do a maunally, one time, building of a database. Sometables has tostay an some are to be exchanged. The foreignkey inforcemnt ill do for myself so everything is correct. I just need to be allowed to de thede taskfor a while. Is it impossible?RegardsAnders
View 1 Replies View RelatedNot Able To Disable Users
May 6, 2008I am trying to disable certain users, but it is giving me this error. I am able to disable some accounts, but not all of them. It seems to happen with user that have been recently created.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Disable Login. failed for Login 'domainuser'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Disable+Login.+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain information about Windows NT group/user 'domainuser', error code 0x5. (Microsoft SQL Server, Error: 15404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15404&LinkId=20476
The links lead to a page that is no longer available.
I read somewhere that it had to do with the SQL Server service account and that if you change it to a Domain Administrator it would and it does, but I can not believe the solution. I would say that this even started happening after we applied SQL 2005 Service Pack 2.
Has anyone else seen this problem. Do I really need to make it a Domain Administrator?
Thanks
Disable Sql Authentication
Apr 29, 2008what is the disadvantage to disable sql authentication, what is it different from window authentication
View 4 Replies View RelatedTrigger Disable
Aug 9, 2007Sorry to reopen a post, but I'm having the same problem. Worse, I can't change the trigger code right now.
My concerns with disabling triggers are:
1) DISABLE TRIGGER affects all sessions, not just the session doing the mass load
2) DISABLE TRIGGER is permanant until re-enabled, so if the mass load process fails (and if our TRY/CATCH blocks aren't perfect), then the triggers would remain disabled for normal OLTP use.
Any ideas on ways around this? I can identify all the work the triggers would have done and do it on my own. The problem is getting them not to do it!
Disable Grouping
Aug 20, 2007I would like to be able to disable grouping depending on the value of parameter. At the moment I have the following expression for grouping:
=IIf(Parameters!Group2.Value.equals(""), false, Fields(Parameters!Group2.Value).Value)
What I am trying to do is if the value of the Group2 parameter = "" turn off grouping. Group2 is assigned a value from the following custom code:
Function Group2Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = ""
Case "2"
Group = "CategoryID"
Case "3"
Group = "StockFamilyID"
Case "4"
Group = "GroupID"
Case "5"
Group = ""
Case "6"
Group = "SaleableGLCodeID"
End Select
Return Group
End Function
However this is not working.
Can grouping be disabled and if so what am I doing wrong?
Thanks in advance.
Disable Filter
Nov 11, 2006i am having a query in front end like this..
func()
select * from table where cond1 and cond2 and col.port in <string> ... goes
end
i passing this string from other function, i may be not be needing this filter(underlined) for some functionality, but i will be using the same function for both the functionalities
i need a string that should tell the sql server to allow all the possible values for that column, so by disabling the filter which i am using...
pls tell me URGENT
Thanks in advance
How To Disable Export
Feb 1, 2006Hello, I am making a web application and I will show reports with the reportviewer, however the user can manage some parameters of the applicacion, the administrator can decide if he wants to allow the user to export or not export to PDF or XLS.
Anyway, I want to enable or disable those options in the reportview dinamically!
Is that possible??
Disable Trans. Replication - HELP!
Mar 20, 2000Hi,
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
Can anyone help or offer some advice?
Thanks in advance...
Disable SQL Guest Account
Jan 17, 2001How do you disable Guest account in SQL Server.
can you do this thru registry?
Tahnk You,
Piyush Patel
Can Not Disable Replication In SQL7
Oct 24, 2000Hi, guys
I have an urgent problem in replication.
Yesterday I set up a Transaction replication with 1 publication
Server A (publisher)(push)(SQL 7)
Server B (distributor and subscriber)(SQL 7)
The replication was succesful and the suscriber can get correct data.
This morning I found the Red sigh in replication mornitor/miscellaneous agents.The error message is:
"The job failed. Unable to determine if the owner (domain nameuser ID) of job Agent history clean up: distribution has server access (reason: Could not obtain information about Windows NT group/user 'domain name'. [SQLSTATE 42000] (Error 8198))."
I tried to use the 'disable publishing and distribution wizard' to disable this replication, but it did not finish and the screen jusr freeze. I used the task manager to end the task.
then I check the replication monitor, the publication was gone but the RED sigh still there.
I tried to use sp_dropdistpublisher to drop a Publisher
but it said "the server A is not a distributor"
I tried to use sp_dropdistributiondb to delete the distribution database.
but it said "Could not drop the distribution database 'distribution'.
This distributor database is associated with a Publisher."
I tried to use sp_dropdistributor to remove the Distributor .
but it said "Could not drop the Distributor 'Server B'.
This Distributor has associated distribution databases."
Any suggestion? Thank you in advance.
Theresa
Disable Specific Trigger
Jul 26, 2004I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?
View 1 Replies View RelatedWindows Policy (disable)
May 23, 2007What is the best way to disable using the Windows policy requirements when creating a new login?
example: I'm trying to create a login and the password doesn't meet the windows policy requirements. The easy thing to do would be to create a strong password. This isn't an option at the moment. I need to disable this so I can create new users that dont have strong passwords.
Thanks!
How To Disable A Trigger In Duration?
Feb 21, 2005Hello, everyone:
There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.
Any help will be appreciated.
Thanks
ZYT
How To Enable And Disable The Identity
Nov 17, 2006Hello there :-)
Kindly help me find the answer on how to enable and disable the identity of a field on a table via SQL Script?
Thanks :-)