Hi,
I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.
ie, I want to get a dataset like :
"0", "dataCol1", "dataCol2" for the first line
"1", "dataCol1", "dataCol2" for the second line
"2", "dataCol1", "dataCol2" for the third line
etc.
Anyone knows if there is a sql statement that does it ?
Thanks
Johanncheck this|||You can also create a temp table with an int identity column, insert the first table into the temp, and return it.
Showing posts with label line. Show all posts
Showing posts with label line. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
How to get query result in XML format using osql.exe (command line)
Hi.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
Sunday, February 19, 2012
How to get executing sql and pids via sql from a command line....
To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--sean
Hello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net ...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--sean
Hello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net ...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>
How to get executing sql and pids via sql from a command line....
To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
----
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--seanHello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
----
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--seanHello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>
Subscribe to:
Posts (Atom)