SQL 2012 :: Retrieve Job History For All The Jobs Using Central Management Server?
Apr 2, 2015Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?
View 0 Replies
ADVERTISEMENT
Central Management Server Doesn't Support Multi Domains
Jun 4, 2015As CM doesn't support SQL authentication, and if access SQL Server on another domain with no trust, we cannot use Windows authentication as well.
CM only supports Windows authentication, but for large organizations which will have multi domains...
Central Management Server - How To Send Query Output In The Form Of Mail
Apr 29, 2015i have used CMS in SQL 2008 R2. i have added couple of servers in its group. i have executed a query & i need to send the query output in the form of email.
basically query is checking the rows count from couple of user tables in servers.
issue here is how to copy the data that is used by CMS? i need to work on automate the rows count in difft user table in db servers.
SQL Server Admin 2014 :: Central Management Server Doesn't Support Multi Domains
Jun 3, 2015As CM doesn't support SQL authentication, and if access SQL Server on another domain with no trust, we cannot use Windows authentication as well.
CM only supports Windows authentication, but for large organizations which will have multi domains.
I am thinking if there is no proper way/tool, we will develop one then.
Displaying Jobs In SQL Server Management Studio
Dec 11, 2007Is there a way to display and manage Jobs in SQL Server Management Studio at the same level of detail as existed in Enterprise Manager?
In Enterprise Manager, when I clicked on Jobs, it displayed Name, Category, Enabled, Status, Next Run Date, etc. All very useful when managing many jobs. I particularly used the ability to sort my jobs by category.
In Management Studio, I click on Jobs in Object Explorer, and all I see in Object Explorer Details is Name and Status, with an icon that has a tiny read arrow that is almost invisible that apparently tells me if the job is not enabled.
I can right-click on Jobs and Manage Job Categories, which allows me to View Jobs, but that doesn't help me much unless I do a screen print so that I can go back to Object Explorer and manage the jobs that I want.
Perhaps I am missing something, but this is just one of several areas where it feels like Microsoft has made my job more difficult with this tool rather than easier.
Thanks for any suggestions that may help make this easier. Otherwise I am to the point where I'm going to have to rename all of my jobs beginning with the category so I can use the alphabetical sort.
Cliff
Scheduled Jobs: History Info Disappears
Jul 13, 2001From time to time, history info on our scheduled jobs (SQL Server 7.0) is missing. When I try to view job history, I get a popup saying "There is no history information for this job". Sometimes the job does run, however, sometimes it doesn't... The problem seems to happen randomly on random scheduled jobs. Any tips on how to get back the consistency of info we used to enjoy in 6.5?
Also, is there a way to specify how many job runs the history should display. It only goes as far as 5 or so, I wonder if it can capture more "runs"?
Please email back the answer to lpinskaya@firstam.com - it would be GREATLY appreciated.
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---------
SQL 2012 :: Access To Central Backup Location
Dec 11, 2014SQL Server service accounts; the default (and recommended) service account is NT ServiceMSSQLServer virtual service account - which is shown as MSSQLSERVER locally - if you want to assign permissions to a local location. Fine, that's OK, works.
For network resources, as far as I understand it (or think I do, anyway), a virtual service on a box is made visible to the network using the <Domain><Computer>$ credential. Eg for box Bimble on Domain Wimble - if I want to give access to a directory on backupServer1, I simply assign rights to WimbleBimble$ and SQL Server should get those rights.
This, or so I thought, should allow SQL Server access to that location. The thing is, when trying to access that location, it's throwing an access denied error. But it works fine with LocalSystem.
Do I need to do some additional configuration to allow the Virtual Service account to be shown via <Domain><Computer>$ ?
SQL Server 2012 :: Password History Table
May 7, 2014I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
SQL Server 2012 :: Getting End Dates For History Segments?
Aug 14, 2014I have a customer history table with the follow structure and data:
CustomerID Tier StartDate RecordStatus
123 A 01/01/2013
SQL Server 2012 :: Getting End Dates For History Segments
Aug 14, 2014I have a customer history table with the follow structure and data:
CustomerID Tier StartDate RecordStatus
123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1
CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)
[Code] .....
The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.
What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:
CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL
SQL 2012 :: View Server Agent Job History ONLY
Jul 6, 2015I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
SQL Server 2012 :: RAISERROR Not Showing Custom Message In Job History
Apr 1, 2014So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.
View 2 Replies View RelatedSQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows
Feb 13, 2015comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
SQL 2012 :: Retrieve Binary File From Server
Apr 14, 2014I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
[Code] .....
SQL Server 2012 :: Retrieve Column From Nested Query
Aug 6, 2015I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
SQL 2012 :: Ability To View Server Level Logins And Agent Jobs?
Sep 16, 2014In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies View RelatedSQL Server 2012 :: Recurrences - Retrieve Only Rows Where Item Not Change For Given Value
Sep 16, 2015I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
[Code] ....
SQL Server 2012 :: Retrieve String Between Two Delimiters For Multiple Occurrences?
Oct 21, 2015How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
Status Field In Management - Jobs
Apr 20, 2007I am trying get the status field from the list of jobs in management. I have a activex script that queries system tables to find out if a job has failed and sends the report via e-mail.
Currently I use this query to get the names of jobs failed...
SELECT
name
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
ORDER BY name
I need to add some criteria around the status field to get the appropriate results.
anybody?
Cheers :beer:
SQL Server 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value
Jul 17, 2015I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
Managing Scheduled Jobs From Management Studio Express
Jul 10, 2006I'm working on a backup solution for my company. Right now we have three servers in three locations running SQL Server 2000. For testing purposes, I created a scheduled job on one of these servers. It worked fine but I'd like to tweak the job some, tinker with the timing and save locations. I'm using Management Studio Express on my laptop to remotely work with these databases but I can't seem to find a decent way to work with existing jobs. Am I missing something or does SSMSE lack the "manage jobs" functionality?
View 1 Replies View RelatedSQL 2012 :: How To Find CPU Usage From History
Jun 15, 2015I am trying to find out CPU utilization from the history using process.%processor time. I am having dual core CPU with 2 numa nodes each having 16 logical cpus bind to it.
how to calculate the CPU utilization using perfmon.I tried to use SQL query which gives CPU history using SQL DMV, but I am unable to get the exact value. Because in between I have used the same querry to capture my CPU usage on the run day, the value on run day and the query which iam tryting to pull out is different. I am using the same query to pull the history data with providing the date.
-- Get CPU Utilization History (SQL Server 2008 and above)
DECLARE @ts BIGINT
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info);
SELECT SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
[code]....
SQL 2012 :: Query For History Table?
Nov 3, 2015I have order history table that tracks who worked on a order
ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538
I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4
I have order history table that tracks who worked on a order
Another example
ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789
I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2
SQL 2012 :: Select Statement From History Table?
Jan 26, 2015Project has 2 tables process(parent) and processchild(child).
Project workflow recorddsany changes to these tables as a history.
I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).
Here is example.
Process table
PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...
ProcessChild table
PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..
I want to find out all the processes where processchildid=2 and processchild.status =1
SSIS Central Server?
Sep 20, 2007We have about 150 SQL servers and basically we're considering the pros and cons of installing SSIS on a central SSIS server - that is responsible for all DTS jobs - as opposed to installing SSIS on the local SQL instance.
On the plus side so far:
1./ Central administration, alerting, change management etc
2./ Possible performance gain on the local instance not having SSIS installed?
On the negative side:
1./ Central point of failure
2./ Possibility that it would need to be a clustered...
3./ Compatibility issues may mean having to make the central SSIS server 32-bit?
4./ Possible performance cost of remote SSIS?
5./ With multiple DTS packages running at different times, when would we take the server down for maintenace...?
Would appreciate your thoughts.
SQL 2012 :: Database Failover History In AlwaysOn Group
Jun 16, 2014If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.
View 0 Replies View RelatedCentral Store Of SQL Server Registration
Nov 7, 2000I am attempting to centrally register all of my sql server so I can go to any workstation and open enterprise manager and select to read the sql server registration from the remote server.
I followed all of the steps in books on line about going to the central server, turning off the store/user independent option, registering all of the servers and then going to a workstation and selecting the option to read from the central server.
I get error messages like registry read error: 5 and registry read error: 2
This is getting frustrating because we have a lot of servers in our environment and re-registering them everytime is getting to be quite a pain. If the central store will not work, is there a way to write a script to register all servers in an enterprise.
Thanks,
PJ
SQL 2012 :: Copy SSRS Report Snaphot History To Another Folder
Mar 17, 2015How can I make a copy of an SSRS Report history snaphot to another folder in Report Manager. I have a report that has history snapshots. But want a copy of the report snapshot history in another folder for users to view easier in Report Manager.
View 0 Replies View RelatedCentral Store For Server Registration Information
Jun 26, 2001Has anyone had any luck trying to use a central store for server registration information? I'm following the steps but it doesn't seem to work.
I receive the following error: The Server Registration Information could not be loaded - Registry read error:2
Any help is appreciated.
SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?
Apr 24, 2015I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
Unable To Perform Set Server Defaults In Sharepoint 3.0 Central Administration
Apr 12, 2007I'm not quite sure where to post this question, so I'll try here. We've been struggling getting SQL2005 reporting services integrated with Share Point Services 3.0 for several days now.
We've installed SQL Reporting Services with SP2 and run the SharePoint Reporting Services add-in. We've been able to go through the SQL Reporting Services Configuration and now were at the last step in the SharePoint Application Management Reporting Services configuration.
The first two steps of Manage Integration Settings and Grant Database Access was sucessful. The final step of "Set Server Defaults" gives us a 404 error message. There is nothing in the Event Log to suggest that there was a problem, and the SQL2005 Server is in SharePoint Integration mode. I've searched but can't find a solution. Can anyone steer me in the right direction please?
SQL 2012 :: Email For Over Running Jobs?
May 28, 2014I'm trying to find out if there is a way within sql server or via script of emailing when a particular job or jobs has over run?
View 1 Replies View Related