Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
ChrisHi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:
> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
Chris
Hi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:

> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retrieving Most recent transaction date

Hi,

I have a fact table containing the transaction code and date on a daily basis. I need to find out for a particular account the most recent date when a particular transaction 'abc' was received. I need to find out most recent dates for other such transactions as well to be displayed on one single report. Basically I would need something like:

ABC Date - Most recent date when 'ABC' transaction was sent

XYZ Date - Most recent date when 'XYZ' transaction was sent. ........

I thought of using the filter available on the cube browser, but if I use filter and use the '=' operator I can only specify one transaction code. But I need dates for different transaction codes.

Any thoughts on this would be greatly appreciated.

Thanks.

Here's a sample Adventure Works query, which returns the last order date for each Promotion listed:

>>

With

Member [Measures].[LastDate] as

Tail(NonEmpty([Date].[Date].[Date].Members,

{[Measures].[Order Quantity]})).Item(0).MemberValue

select

{[Measures].[Order Quantity],

[Measures].[LastDate]} on 0,

Non Empty [Promotion].[Promotion].[Promotion].Members on 1

from [Adventure Works]

-

Order Quantity LastDate
No Discount 238,806 7/31/2004
Volume Discount 11 to 14 18,181 6/30/2004
Volume Discount 15 to 24 10,713 6/1/2004
Volume Discount 25 to 40 2,321 6/1/2004
Volume Discount 41 to 60 85 4/1/2004
Mountain-100 Clearance Sale 456 6/1/2002
Sport Helmet Discount-2002 492 7/1/2002
Road-650 Overstock 304 8/1/2002
Sport Helmet Discount-2003 680 7/1/2003
Touring-3000 Promotion 1,581 9/28/2003
Touring-1000 Promotion 775 9/23/2003
Mountain-500 Silver Clearance Sale 382 6/1/2004

>>

Retrieving lables from database

I'm creating lots of reports containing similiar lables, "Username", "Country", "Date of birth" etc. For some reason people tend to wanna change these lables over time so "Date of birth" becomes "Birthdate" or something. As I'm doing it now, I'll have to edit all my reports containing "Date of birth" when this happens.

So, instead I'll try using some kind of databasetable containing all the strings. However, I'm not sure about the best way of implementing this. A language-code could be great in a table like this, but lets skip it for this small example. Lets say the DB-table only contained two columns, "ID" and "String". Then one way of doing this would be to create one dataset for each string/lable, i.e. "Select String from StringTable where ID=23". Then use "=First(Fields!String.Value)" for one lable in the report. However, it would be a bit messy with one dataset for each lable.

Is there some way in a lable expression to, instead of "First", define a row with a given ID, i.e. "=Fields!String.Value where ID = 23" ? Or how would you solve this "lable problem"?

Regards Andreas

If you are displaying the whole table you can use the INFORMATION_SCHEMA.COLUMNS view to get the information back, simply query on the table_name and you will get all the columns of the table back.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||If I understand you correct, you reffer to having one column in the table per string/lable? This wasn't my intention. My intention was to have all strings/lables in the same column and identified by an ID. Do you get what I mean?|||Mhmm, yes I thought so, try to execute the following query (on the adventureworks database)

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_NAMe = 'Log'

which results in:

LogID
EventID
Priority
Severity
Title
Timestamp
MachineName
AppDomainName
ProcessID
ProcessName
ThreadName
Win32ThreadId
Message
FormattedMessage

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Retrieve next lowest number in mixed set

Let's say I have a result set (records) containing numbers such as:

0
1
2
3
9
16
21
45
100
101
102
103
104
105
106

How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.

Thoughts? Thanks..."Zamdrist" <zamdrist@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:
>
0
1
2
3
9
16
21
45
100
101
102
103
104
105
106
>
How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.
>
Thoughts? Thanks...
>

SELECT MIN(x)+1 x
FROM tbl t
WHERE NOT EXISTS
(SELECT x
FROM tbl
WHERE x = t.x+1);

--
David Portas|||On Oct 4, 3:31 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"Zamdrist" <zamdr...@.gmail.comwrote in message
>
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...
>
>
>

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:

>

Quote:

Originally Posted by

0
1
2
3
9
16
21
45
100
101
102
103
104
105
106

>

Quote:

Originally Posted by

How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.

>

Quote:

Originally Posted by

Thoughts? Thanks...

>
SELECT MIN(x)+1 x
FROM tbl t
WHERE NOT EXISTS
(SELECT x
FROM tbl
WHERE x = t.x+1);
>
--
David Portas

Thanks!|||"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:
>
0
1
2
3
9
16
21
45
100
101
102
103
104
105
106
>
How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.
>
Thoughts? Thanks...
>

Another method (SQL Server 2005 only):

SELECT MIN(x) x
FROM
(SELECT x+1 FROM tbl
EXCEPT
SELECT x FROM tbl) t(x);

--
David Portas|||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. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How might someone write a procedure to get the next lowest number in this sequence? In this case it would be: 4. <<

No; read your own specs! The answer is -1. Or maybe -0.0000...1 if
the column is a DECIMAL or a FLOAT. If you had posted DDL that
limited the column to non-negative integers, then Dave's answer would
work.|||On Oct 4, 2:17 pm, Zamdrist <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

In this case it would be: 4.

SELECT MIN(t2.x +1)
FROM t FULL JOIN t t2 ON t.x = t2.x +1
WHERE t.x IS NULL;

Retrieve data from text field containing XML data

I have the following data in a SQL2k data field:
<History data="<?xml version="1.0" encoding="utf-16"?>
<ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<column id="Availability" v="U" o="A" />
<column id="LastModificationAgentID" v="59930" />
</ColumnsList>"/>
I'm trying to retrieve the data with Query Analyzer using the following quer
y:
SELECT *
FROM OPENXML(Column, N'/ColumnsList/Column')
WITH (ID varchar(40), v varchar(100), o varchar(100))
Am I at all on the right track with this? This is my first attempt at
working with XML and SQL together.
Thanks in advance.I am a bit . Your XML is not a well-formed XML document.
Also, I would recommend that you look at OpenXML samples in Books Online.
Basically, OpenXML uses a handle to refer to the XML document that it gets
from sp_xml_preparedocument (which parses the XML) and needs to be released
by sp_xml_removedocument.
If your XML data in the column is larger than 8k, you will have an
implementation restriction in SQL Server 2000 on variables not allowing
NTEXT/TEXT types. This makes it harder to use OpenXML on already stored XML
data (I recommend to use OpenXML on the data when it is being passed to a
stored proc before it gets stored in a table).
I would also recommend to look at SQL Server 2005, that have much better
support for your scenario (look at the XML datatype, XQuery and the nodes()
method).
Best regards
Michael
"JAdams" <JAdams@.discussions.microsoft.com> wrote in message
news:E8F3E463-2071-41EA-91E1-49E9E5D39510@.microsoft.com...
>I have the following data in a SQL2k data field:
> <History data="<?xml version="1.0" encoding="utf-16"?>
> <ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <column id="Availability" v="U" o="A" />
> <column id="LastModificationAgentID" v="59930" />
> </ColumnsList>"/>
> I'm trying to retrieve the data with Query Analyzer using the following
> query:
> SELECT *
> FROM OPENXML(Column, N'/ColumnsList/Column')
> WITH (ID varchar(40), v varchar(100), o varchar(100))
>
> Am I at all on the right track with this? This is my first attempt at
> working with XML and SQL together.
> Thanks in advance.
>

Retrieve data from text field containing XML data

I have the following data in a SQL2k data field:
<History data="<?xml version="1.0" encoding="utf-16"?>
<ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<column id="Availability" v="U" o="A" />
<column id="LastModificationAgentID" v="59930" />
</ColumnsList>"/>
I'm trying to retrieve the data with Query Analyzer using the following query:
SELECT *
FROM OPENXML(Column, N'/ColumnsList/Column')
WITH (ID varchar(40), v varchar(100), o varchar(100))
Am I at all on the right track with this? This is my first attempt at
working with XML and SQL together.
Thanks in advance.
I am a bit confused. Your XML is not a well-formed XML document.
Also, I would recommend that you look at OpenXML samples in Books Online.
Basically, OpenXML uses a handle to refer to the XML document that it gets
from sp_xml_preparedocument (which parses the XML) and needs to be released
by sp_xml_removedocument.
If your XML data in the column is larger than 8k, you will have an
implementation restriction in SQL Server 2000 on variables not allowing
NTEXT/TEXT types. This makes it harder to use OpenXML on already stored XML
data (I recommend to use OpenXML on the data when it is being passed to a
stored proc before it gets stored in a table).
I would also recommend to look at SQL Server 2005, that have much better
support for your scenario (look at the XML datatype, XQuery and the nodes()
method).
Best regards
Michael
"JAdams" <JAdams@.discussions.microsoft.com> wrote in message
news:E8F3E463-2071-41EA-91E1-49E9E5D39510@.microsoft.com...
>I have the following data in a SQL2k data field:
> <History data="<?xml version="1.0" encoding="utf-16"?>
> <ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <column id="Availability" v="U" o="A" />
> <column id="LastModificationAgentID" v="59930" />
> </ColumnsList>"/>
> I'm trying to retrieve the data with Query Analyzer using the following
> query:
> SELECT *
> FROM OPENXML(Column, N'/ColumnsList/Column')
> WITH (ID varchar(40), v varchar(100), o varchar(100))
>
> Am I at all on the right track with this? This is my first attempt at
> working with XML and SQL together.
> Thanks in advance.
>

Retrieve Data from SQL Server in an hierarchical XML.

Hi,
I have a table containing hierarchical data and I would like to retrieve it
form SQL Server as hierarchical XML so i can use it directly for binding wit
h
e.g. the ASP.NET2 Treeview
E.g table:Nodes
Table:Nodes
id parent Name
1 0 Node1
2 1 Node11
3 1 Node12
4 2 Node111
Format to be retrieved:
<nodes>
<node value="1" text="node1">
<node value="2" text="node11">
<node value="4" text="node111"></node>
</node
<node value="3" text="node12"></node>
</node>
</nodes>
- Raoul Jacobs
The nature of developping is sharing knowledge.Raoul,
First, SQLXML will not provide you with such output. You will need to take a
standard resultset and convert it to XML.
Second, if you want to retreive heirarchical data easily, then you need to
store your heirarchy properly. As you can see from your experience, the
ID/ParentID doesn't work out so well.
Search for SQL Nested Sets on google. Also, I would also recommend getting
either SQL For Smarties or Trees and Heirarchies in SQL (both by Celko).
Otherwise, your SOL and will need to develop a horribly ugly trigger or
other equally bad middle-tier code.
-- Alex Papadimoulis
"JaRa" wrote:

> Hi,
> I have a table containing hierarchical data and I would like to retrieve i
t
> form SQL Server as hierarchical XML so i can use it directly for binding w
ith
> e.g. the ASP.NET2 Treeview
> E.g table:Nodes
> Table:Nodes
> id parent Name
> 1 0 Node1
> 2 1 Node11
> 3 1 Node12
> 4 2 Node111
> Format to be retrieved:
> <nodes>
> <node value="1" text="node1">
> <node value="2" text="node11">
> <node value="4" text="node111"></node>
> </node
> <node value="3" text="node12"></node>
> </node>
> </nodes>
> --
> - Raoul Jacobs
> The nature of developping is sharing knowledge.