Query/View: The 2 newest periods for each indicator

Hi,

I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.

The problem is, that I can't find a simple way to extract the 2 latest
results.

The Table (Table1) looks like this:
kpiIDperiodIDActual
Acceleration23
Acceleration54
Speed1100
Speed4200
Speed7220
Speed9180
Weight122
Weight332
Weight721
Weight1033

If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):

SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);

BUT - how how do I get the second-newest period as well?

Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest

Alternatively a View with 2 posts for each performace-indicator.

Thanks in advance
RyanOn Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:

>Hi,
>I'm working on a simple performance-program, where I need to extract
>information from the 2 newest periods for every performance-indicator
>- And from there calculate a trend between these results.
>The problem is, that I can't find a simple way to extract the 2 latest
>results.
>The Table (Table1) looks like this:
>kpiIDperiodIDActual
>Acceleration23
>Acceleration54
>Speed1100
>Speed4200
>Speed7220
>Speed9180
>Weight122
>Weight332
>Weight721
>Weight1033
>If I want to extract the newest I use something like this (made it in
>MS Access, so the syntax might differ slightly from SQLServer):
>SELECT table1.kpiID, table1.periodID, table1.Actual
>FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
>table1 as t WHERE t.kpiID=table1.kpiID);
>BUT - how how do I get the second-newest period as well?

Hi Ryan,

SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2

>Preferably I would like the final result to be a View with the
>following fields:
>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>Actual_sec_newest

In that case, try this instead:

SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)

(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks a lot. I got them both working without any hassle.

>SELECT a.kpiID, a.periodID, a.Actual
>FROM table1 AS a
>WHERE (SELECT COUNT(*)
> FROM table1 AS b
> WHERE b.kpiID = a.kpiID
> AND b.periodID >= a.periodID) <= 2

I find this to be quite clever - had to look at it some time to figure
out how it works.

>>
>>Preferably I would like the final result to be a View with the
>>following fields:
>>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>>Actual_sec_newest
>In that case, try this instead:
>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>FROM table1 AS a
>LEFT JOIN table1 AS b
> ON b.kpiID = a.kpiID
> AND b.periodID = (SELECT MAX(c.periodID)
> FROM table1 AS c
> WHERE c.kpiID = a.kpiID
> AND c.periodID < a.periodID)
>WHERE a.periodID = (SELECT MAX(t.periodID)
> FROM table1 AS t
> WHERE t.kpiID = a.kpiID)
Works as well - minor adjustment needed: Move lines 5-8 to the end.

Regards
Ryan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.|||celko, have you actually ever HELPED anyone on this list???

i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.

thx,
doug|||On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:

>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>FROM table1 AS a
>>LEFT JOIN table1 AS b
>> ON b.kpiID = a.kpiID
>> AND b.periodID = (SELECT MAX(c.periodID)
>> FROM table1 AS c
>> WHERE c.kpiID = a.kpiID
>> AND c.periodID < a.periodID)
>>WHERE a.periodID = (SELECT MAX(t.periodID)
>> FROM table1 AS t
>> WHERE t.kpiID = a.kpiID)
>>
>Works as well - minor adjustment needed: Move lines 5-8 to the end.

Hi Ryan,

That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.

This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.

Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.

--
Hugo Kornelis, SQL Server MVP|||On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>>FROM table1 AS a
>>>LEFT JOIN table1 AS b
>>> ON b.kpiID = a.kpiID
>>> AND b.periodID = (SELECT MAX(c.periodID)
>>> FROM table1 AS c
>>> WHERE c.kpiID = a.kpiID
>>> AND c.periodID < a.periodID)
>>>WHERE a.periodID = (SELECT MAX(t.periodID)
>>> FROM table1 AS t
>>> WHERE t.kpiID = a.kpiID)
>>>
>>Works as well - minor adjustment needed: Move lines 5-8 to the end.
>Hi Ryan,
>That changes the meaning of the query - the place where you put those
>lines dictates what will happen for a kpiID that has only one row.
>This one row is by definition the latest - but there's no second latest.
>If you use the query I suggested, you'll get this kpiID in your result,
>with it's only row as last measurement and NULLs as it's second latest
>measurement.
>Your version (after moving those rows) will exclude any kpiID with only
>one row. Only kpiIDs with two or more measurements will be displayed. If
>that is indeed your requirement, then you can safely move these lines.
>And you can change the LEFT JOIN in an INNER JOIN as well, to get some
>performance gain.

Hi Hugo,

thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.

Thanks again.
Ryan|||I've read 2 books written by joe celko, and and both have really helped
me. I frequently profile my code to see what it is doing. So he's
helped me.|||Go to the CMP archives for DBMS, DATABASE PROGRAMMING & DESIGN, and
INTELLIGENT ENTERPRISE magazines to go back over ten years. I have
written over 750 columns in the computer trade and academic press,
mostly dealing with data and databases. I currently write for BMC's
DBAzine.com e-magazine.

My six books: SQL FOR SMARTIES (Morgan-Kaufmann, 1995, second edition
1999, third edition 2005), SQL PUZZLES & ANSWERS (Morgan-Kaufmann,
1997), DATA & DATABASES (Morgan-Kaufmann, 1999) and TREES & HIERARCHIES
IN SQL (Morgan-Kaufmann, 2004) and SQL PROGRAMMING STYLE
Morgan-Kaufmann, 2005).
Past magazine columns include: "SQL Explorer" in DBMS (Miller-Freeman);
"Celko on SQL" in DATABASE PROGRAMMING & DESIGN (Miller-Freeman);
"WATCOM SQL Corner" in POWERBUILDER DEVELOPERS' JOURNAL (SysCon); "SQL
Puzzle" in BOXES & ARROWS (Frank Sweet Publishing); "DBMS/Report" in
SYSTEMS INTEGRATION (Cahner-Ziff); "Data Desk" in TECH SPECIALIST
(R&D); "Data Points" in PC TECHNIQUES (Coriolis Group); "Celko on
Software" in COMPUTING (VNC Publications, UK), "SELECT * FROM Austin"
(Array Publications, The Netherlands), and he was editor for the
"Puzzles & Problems" section of ABACUS (Springer-Verlag) and I ran the
CASEFORUM section 18, "Celko on SQL", on CompuServe.

So, what have you done?|||if it is SQL Server 2005, use row_number() *untested*:

select * from(
SELECT table1.kpiID, table1.periodID, table1.Actual,
row_number() over(partition by kpId order by periodId desc) rn
FROM table1
) t
where rn<3|||That's Joe Celko, the demi-god of SQL for pete's sake.sql

Query/View Question

I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:

TABLE 1

PrimaryKey

Textfield1

Textfield2

Textfield3

TABLE 2

PrimaryKey

Table1ForeignKey

Table3ForeignKey

Textfield1

TABLE 3

PrimaryKey

Textfield1

Textfield2

Textfield3

Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:

Table 1

PrimaryKey

Table1

Textfield1

Table2

Textfield

Table3

Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

Hi,

some more questions:

how do you define "the latest" in table2 ?

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||

Perhaps my question will make more sense explained like this:

I will use an analogy of checking out books from the library.

Table 1 is a table of books, with a primary key of bookid.

Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.

Table 3 is a table of library card holders contact info with a primary key of CardholderID.

All of the primary keys are auto-incrementing.

I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.

I hope that makes more sense.

|||

OK, keeping your analogy in mind, the query should be like:

Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK

untested....

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Query XML type with values from another XML type

Hi - I'm using SQL Server 2005, and I have a view with an XML data type column. I would like to write a stored proc that takes an XML data type as a parameter and return the rows from the view that match the columns in the parameter.

At this point, I'm just trying to get the syntax correct, so I started with some simple queries. Below are the queries:

declare @.params xml

declare @.view xml

set @.params = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" />

<HierarchyType hierarchyTypeId="5" />

</HierarchyTypes>'

set @.view = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" otherProp = "1"/>

<HierarchyType hierarchyTypeId="5" otherProp = "2"/>

<HierarchyType hierarchyTypeId="6" otherProp = "3"/>

<HierarchyType hierarchyTypeId="7" otherProp = "4"/>

</HierarchyTypes>'

SELECT T.c.query('.') AS result

FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c)

I would like to get the nodes from @.view where @.view.hierarchyTypeId = @.params.hierarchyTypeId. This should be pretty simple, but I'm missing it...

Any thoughts are appreciated!

Thanks,

Phil

It sounds like you are trying to join view and params to get a results set which has one matching node per row. If that is the case, the below query should be doing what you are looking for. If you want to return a single xml fragment, a different technique would have to be used.

In the query below, we iterate over the HierarchyType nodes of both @.view and @.params, project the values of the hierarchyTypeId attributes as integers using the the value() function, verify that they match, and the return the @.param node that matches.

declare @.params xml
declare @.view xml

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'

set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'

SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c),

@.params.nodes('/HierarchyTypes/HierarchyType') P(c)
WHERE T.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

|||

Hi Todd - this is very close to what I need. The only other wrinkle is that I'm trying to select all of the columns from the view (not just the hierarchyTypeId). The view has an XML column called HierarchyTypes, and I want to use that column in my 'join criteria'. And I'd like to do this in a stored proc.

CREATE PROCEDURE [dbo].[CodeHierarchy_SearchHierarchy]
@.codeTypes xml = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT v.nodeId,
v.parentNodeId,
v.HierarchyTypes
FROM VLinkedCodeHierarchies as v
WHERE /* hierarchyTypes in @.codeTypes are also in v.HierarchyTypes */

At this point, I also require that the return is NOT XML.

Thanks,

Phil

|||

So it sounds like you have a table or a view that has a few relational columns, and 1 xml column. And then you want to join it with an XML fragment.

Is that correct?

If so, then you can modify the query to use CROSS APPLY. In the example I create a test table which has an xml column, fill it with data and then join it with the fragment, projecting the relevant relational columns and xml data. You could then wrap it in a stored procedure or user defined function as needed.

(I CROSS APPLY the vTest table with nodes() function so that I get the nodes for the current row.)

declare @.params xml

drop table vTest
Create table vTest(
id int,
val xml
)

insert into vTest (id, val) values (1,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>')

insert into vTest (id, val) values (2,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="8" otherProp = "1"/>
<HierarchyType hierarchyTypeId="9" otherProp = "2"/>
<HierarchyType hierarchyTypeId="10" otherProp = "3"/>
<HierarchyType hierarchyTypeId="11" otherProp = "4"/>
</HierarchyTypes>')

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
<HierarchyType hierarchyTypeId="11" />
</HierarchyTypes>'

SELECT vTable.Id,
xVal.c.value('@.hierarchyTypeId', 'int') as hierarchyTypeId,
xVal.c.value('@.otherProp', 'int') as otherProp,
xVal.c.query('.') as MatchingFragment

FROM @.params.nodes('/HierarchyTypes/HierarchyType') P(c),
vTest as vTable CROSS APPLY vTable.Val.nodes('/HierarchyTypes/HierarchyType') xVal(c)
WHERE xVal.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

If you dont want to Join the parameter fragment and the table, but just want to check that for each row it's xml column has some data in common with the parameter fragment, then you could change the query to use an EXISTS.

|||

Hi Todd - thanks again for the excellent reply. I actually want to make sure that the rows returned have data in common with the XML fragment.

Ideally, I would like to have 3 options:

1) View column has some data from XML fragement

2) View column has all data from XML fragment, but could have more.

3) View column has ONLY data from XML fragement.

I can accomplish #1 above using a SELECT DISTINCT, but I don't think that's the most optimum.

Where are some good resources to educate myself on this?

Thanks!

Phil

|||

Here is where you can find the the basics of XQuery and the T-SQL functions that support it:
http://msdn2.microsoft.com/en-us/library/ms190262.aspx

The current w3c resources:
http://www.w3.org/TR/xquery/

T-SQL Reference:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx

And there are probably a number of tutorials that would discuss the differences of when to use a DISTINCT vs EXISTS vs CROSS APPLY.

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008
How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
--
Ekrem Ã?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

query with view locks database (was "Problem with Viiews")

Hi,
I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database.
Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.

Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.

Thanx in advance.

Regards,
SushmaMost of the time the ways views are implimented or used are not very efficient. They often result in slow queries because you are in effect querying a query and the result you are seeking is often more efficiently achieved by writing one well formed query. If the view was created for security purposes there are alternatives like restricting access to a table but granting access to a stored procedure that accesses a table.sql

Query to View with Many Columns

Hi All,

I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?

Does anyone have idea how to join table that can reflect both left and right join?

Table A Table B

Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5

A Jan 5 xxx A Jan 1 yyy

B Feb 3 C Mar 4

B Mar 4 C Apr 3

C Apr 2 D May 2

E Mar 1

Result Table

Column1 Column2 Column3 Column4 Column 5

A Jan 6 (= 5+1) xxx yyyy

B Feb 3

B Mar 4

C Mar 4

C Apr 5 (= 2+3)

D May 2

E Mar 1

So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).

Thanks for the help.

The VIEW will first create a virtual table containing ALL of the columns, and then you will get a resultset of just the columns requested.

That could be very inefficient. Most likely, it would be better to query against the underlaying table for just the columns required. Or create a separate VIEW for this purpose.

|||

Thanks for the answer .

The problem is this query will be executed by Report Builder, so all of those columns will be needed as user might select any combination to include in the report, so it's quite difficult to provide only certain column by creating another view. I already try to separate the View into multiple entity in Report Builder, but when it comes to the aggregate, some of the calculation is calculated wrongly in the Report Builder, and some complexity with multiple currency/exchange rate araise, that force me to add more column inside the view .

Any idea or suggestion for my situation? Thanks

|||

You haven't provided enough detail for anyone to offer cogent suggestions.

However, one thing comes to mind. You might be able to use a Stored Procedure that Creates a custom VIEW (from input parameters), and then returns the resultset from that VIEW.

query to view current executing jobs

... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>

Query to get all user tables with columns

Hi,

I tried to create a simple view as follows

CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

Combined with consistent naming conventions I will use this view to
easily find foreign keys; a la

SELECT *
FROM V_ALL_USERTABLE_COLUMNS
WHERE ColName LIKE ('%user_id')

There is something wrong with my view definition that I don't get
though; it doesn't return all the columns. I have a table with the
following definition

CREATE TABLE [dbo].[c_messages]
(
[cid] [int] IDENTITY (1, 1) NOT NULL ,
[touser_id] [int] NULL ,
[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fromuser_id] [int] NOT NULL ,
[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[message_read] [bit] NOT NULL ,
[logout] [bit] NULL
) ON [PRIMARY]
GO

The problem is that the select I used to define the view doesn't
return the touser_id column. I have sort of a sneaking suspicion that
the problem has to do with joining syscolumns.type to systypes.type,
but I don't know what to do instead (I'd really like to include the
type; it's useful if I ever changed the type of a primary key and want
to check that I also changed all the foreign keys).

Any help would be appreciated!Use the information schema rather than the system tables:

SELECT * FROM information_schema.columns

This format is much easier to use.

Your original query should work if you join on XTYPE rather than TYPE
but this is not recommended. In general you should avoid referencing
system tables directly.

--
David Portas
SQL Server MVP
--

Query Time Out

Hi,

I have a report with query timeout set to 1 sec (I want this for producing timeout exception). When I view it from Business Intelligence Dev. studio's preview tab, it gives me "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" exception.

This is fine..but when i deploy it on report server it doesn't give this exception and opens the report without giving any exception.

in DatabaseQueryTimeout in rsreportserver.config is set to 900sec.

Can someone help me out if I am doing anything wrong or missing something?

And I'will be thankfull to you in advance.

-Thanks

Sounds like more a reporting services issue. Try that group.