Sql Jobs Information Query.
Oct 6, 2005does any one has a query which gives, how often the job runs in a day?
Thanks for your help.
ADVERTISEMENT
How To Capture Information Of Any Jobs That Are Hanging?
May 18, 2005How to capture information of any jobs that are hanging( for example, that are running for more than 6 hours) and send an alert in the email with job name and duration it has been running?
Thanks,
Log Information For SQL Server Agent Jobs.
Dec 12, 2007
Hi Guys,
I am not sure if this is the right place to put a question on SQL Server Agent.
I have scheduled some SSIS Jobs using the SQL Server Agent, and wanted to know how can I view detailed log information on how the Job ran (particularly Failure Information).
The information in the Log File Viewer to see the job execution log doesnt seem to help much - as it only says weather the Job Failed or Succeeded.
I need to see the reason and/or the step in a job where it failed!
Can someone help me out with some insight into this....
Thanks
Pramod
Sql Query For Getting Information From Different Database
Feb 18, 2003I want to write a sql query for information from a table in different database but on the same server.
what is the syntax.
select * from databse2.owner.table ??
i am in application1 database and i want to query from application2 database on the same server.
use application1
go
Select * from application2.dbo.tablename
go
Is this query correct?
Query Database Information
Oct 11, 2001Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Thanks,
Mary Elizabeth
NW Natural
Query To Extract The Most Recent Information - Help Please
Mar 23, 2007Hi all,
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode
1 999 Barry White BW13 8GS
2 999 <null> <null> BW13 9GS
3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
Kind regards,
macca
Information Schema Query Question
Oct 31, 2005Hello All,Could someone help provide a query that I can run todetermine the primary key and unique columns for anygiven table ?Thanks in Advance,Akintoye
View 1 Replies View RelatedLooking For Query Plan Determination Information
Jul 20, 2005Hi,I was just helping a coworker optimize a query. He had two versions:one which used UNION for each value for which he was tallying resultsand another query which used GROUP BY. Here is an aproximation of whatthey were:Query #1:---------SELECT 12 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 5UNIONSELECT 13 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 6Query #2:---------SELECT R.row_num AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1INNER JOIN Report_Rows R ON R.col2 = T.col2WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateGROUP BY ALL R.row_numORDER BY R.row_numThe Report_Rows table in this case would have had two rows mapping row12 to a column value of 5 and row 13 to a column value of 6. Thesecond query was performing horribly until I noticed the ALL keywordin the GROUP BY, which I didn't think was necessary. When I removedthat it performed more like I expected it to perform.Before I had noticed that I was scouring over the query plans andcouldn't figure out why in one instance the query optimizer chose tojoin My_Table and Another_Table, yet when the ALL keyword was there itchose to return all of the records from Another_Table (a rather largetable) and join it to the Report_Rows table before then joining toMy_Table, which had the date criteria in the WHERE clause.So, if you've read this far without giving up...1. Why would the ALL keyword cause this? I understand thefunctionality of ALL, but I still don't see why that caused thereordering of the joins.2. (more importantly) Are there any good resources that you know ofthat explain how the query optimizer choices its query paths? Do the"Inside SQL Server" books go into that much detail? Any good onlineresources?Thanks!-Tom.
View 3 Replies View RelatedMeta Information Of TSQL Query
Mar 13, 2008Hello.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
[result will be]
| id1 | id2 | col67 | .......................
and at the end of all I need something like :
id1-from t1.id; id2-from t2.id; col67-unknown;
thanks for help
I Can Not Make Query For Any Information In Arabic Language
Oct 8, 2007HI, i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank youand my code like this use todayselect CatID from dbo.tbl_Categories where CatName like '%اتصالات%'
View 11 Replies View RelatedPulling Information About Stored Procedures Query
Apr 28, 2008I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id
AND procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id
Query/Report For 'Owner' Of All SQL Jobs
Nov 3, 2006Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?
If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?
Query To Find Who Runs Jobs
Aug 29, 2007I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle
Checking Jobs Status Through Query
Oct 27, 2006I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
Thanks in Advance
Jacx
SQL Query - Duplicate Records - Different Dates - How To Get Only Latest Information?
Mar 17, 2006I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
View 24 Replies View RelatedReporting Services :: MAX Function Query - Display Most Recent Information
May 27, 2015I have a query where I am trying to display the most recent information about travelling method of customers using "t.collection_date". I am struggling to get MAX function working in the query.
SELECT P.id, P.forename, P.surname, P.dob, c.postcode, l.code_des, MAX(t.collection_date)
FROM People p,
MOT_HIST t,
lk_mode_of_travel l,
corresp_address c
WHERE p.ID = t.id (+)
AND t.mode_of_travel = l.int_code (+)
AND p.id = c.entity_id (+)
Query To Select Data Based On Alphanumeric (surname) Information.
Jul 31, 2007Hello,
I am trying to write a query that will be able to select different segments of data based on spelling of the last name.
For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'
then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...
I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?
Thanks
Query Find A List Of All The Jobs In A SQL 2005 Database
Apr 22, 2008
Hi,
I was wondering if there was a SQL query I could run that would list all the current jobs in a SQL 2005 database? I would like to check to ensure a job exists before I call it, but was having trouble finding a way to determine if it existed.
Thanks for any advice!
-Flea#
DB Engine :: Query To List Out Late Running Jobs
Nov 11, 2015Any sql script or powershell script which outputs late running jobs? Currently I am using the below script to find out currently running jobs along with duration. But my requirement is to add few more columns to the query which indicates whether jobs is running fine or running behind expected time.
-currently using query to pull running jobs
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS currently_executing_step_id,
[code]...
SQL Server 2012 :: Query Servers From A List For File Storage Information
Oct 13, 2014I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
SQL Server 2008 :: How To Find All Agent Jobs That Are Currently Running By Query
May 29, 2013When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
Edit / Update Query - Change Current Sales Information For A Specific Product
Apr 11, 2012I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
Query Execution Failed For Data Set &&<name&&> For More Information About This Error Navigate To The Report Server On The Local Serv
Jul 10, 2007I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error
Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors
Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report.
The error message was slightly different, in that it couldn't even open the Datasource.
We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.
I could not figure out how to "enable remote errors" or find an error log on the server. The C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles Log files did not appear to record any errors.
Please advise!
SecurityException Class SQLServerDatabaseMetaData's Signer Information Does Not Match Signer Information Of Other
May 23, 2007Hello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
Query To Only Display Information From One Table Where The Foreign Key Doesnt Exist In The Other Table.
Nov 28, 2006I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies View RelatedInformation About A Job After It Ran
Jul 2, 2001I have a job that runs each night that deletes all records from a table that are older than two weeks old. Is there some way I can view how many records were deleted from the table each night. Is it written to a log somewhere? Thanks in advance!
View 1 Replies View RelatedGetting Information
Aug 4, 2005Hi all,
This is my first thread here.
I got an information about
SET NO_BROWSETABLE ON/OFF
in your forum.. The replier told that this is an undocumented option.
My question is how you guys find information on such undocumented things....
Thanks in advance.
Saint
Information !
Mar 26, 2008Hello,
Can anyone clarify the nuances of How to deal with the change management environment in SQL Server.
Thanks.
Get Job Information
Sep 6, 2007I am trying to get job information and have a query set up. However, I would also like to get the owner information. However, the job only stores the sid of the owner. Does anybody know how I could change this query to get the owner name instead of sid? Don't want to use the sp_help_job command.
SELECT @@SERVERNAME AS Server, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description, msdb.dbo.sysjobs.notify_level_eventlog, msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend, msdb.dbo.sysjobs.notify_level_page, msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created, msdb.dbo.syscategories.name AS Category_Name, msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time, msdb.dbo.sysjobservers.last_run_outcome, msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date, msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator, msdb.dbo.sysoperators.email_address, msdb.dbo.sysjobs.date_modified, GETDATE()
AS Package_run_date
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
Information Being Cut Off
Mar 17, 2008I have a report where the information is getting cut off the report.
At the beginning of or at the ending of the page it is dropping the information.
My layout setting are set 8.5in wide by 11in height. .5in for either left or right margin. 1in for top and bottom.
I run the report and I'm missing a record.
I adjust the height to 10in to force diffferent page breaks and it's there.
I have the setting in one of the groups set for a page break at end. If I uncheck that it doesn't help.Still no records.
What is going on?
Looking For Information
Mar 15, 2006I'm looking for books or information on command line configuration of settings for things like activating Remote Connections under the Service Area Configuration and setting a port for that connection.
What books would be good to get or where can I download papers on this?
Any help would be appreciated.
Locking Information
Dec 23, 2005Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
Trigger Information
Jul 26, 2001Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep