Count Query Help

I am not too good at programming but I need to get the
names and the count of the selected tables (Tables that
starts with aa). I should have the following fromat.
Tablename Count
table1 256
table2 347
..... ...
I wrote a while statement but my select at the end don't
work.
Can anyone show me how my query should look like '
Thanks for any help..There isn't any queries that I know of that will return the table names.
This would have to be done in program code there is probable an API for it,
however the query for the count is simple if you can get the table name (in
code) beginning with aa:
SELECT count(*) FROM TableName
Hope it helps
Gav
"George" <anonymous@.discussions.microsoft.com> wrote in message
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the
> names and the count of the selected tables (Tables that
> starts with aa). I should have the following fromat.
> Tablename Count
> table1 256
> table2 347
> ..... ...
> I wrote a while statement but my select at the end don't
> work.
> Can anyone show me how my query should look like '
> Thanks for any help..|||Here is a basic rowcount routine:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
--=20
Keith
"George" <anonymous@.discussions.microsoft.com> wrote in message =
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the=20
> names and the count of the selected tables (Tables that=20
> starts with aa). I should have the following fromat.
>=20
> Tablename Count
> table1 256
> table2 347
> ..... ...
>=20
> I wrote a while statement but my select at the end don't=20
> work.
> Can anyone show me how my query should look like '
>=20
> Thanks for any help..|||That did it.
Thanks a lot.

>--Original Message--
>Here is a basic rowcount routine:
>use pubs
>go
>SELECT A.name, B.rows
>FROM sysobjects A
>JOIN sysindexes B ON A.ID = B.ID
>WHERE A.type = 'U'
> AND B.INDID < 2
>ORDER BY A.Name
>--
>Keith
>
>"George" <anonymous@.discussions.microsoft.com> wrote in
message news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
don't
>.
>sql

Count of Descendents?

Bear with me I'm not a TSQL expert. I am writing a directory, and I want to get the count of all descendents underneath a selected parent category. I had this in done in VB code, but I want to transfer this function to a sproc.

I am able to build the structure and provide parent paths, counts, etc. The table structure consists of two tables a categories table, and sites table. Within the categories table their is a SiteCount field, which contains all sites within that specific category. Now I want to get all the descendants of the category and populate a CulCount field. The sproc I have now is the following:

ALTER procedure GetCategories

@.ParentID int,
@.moduleID int

as

DECLARE @.Level Int
DECLARE @.Rows Int
DECLARE @.id int
DECLARE @.cChildren int

SET @.Level = 0

--Temporary table
CREATE TABLE #Test2(PK Int IDENTITY (1,1), CategoryId Int, level Int, CategoryName varchar(100),SortColumn varchar(1000), Path varchar(1000), SiteCount Int, CulCount Int, DateSiteAdded datetime, ParentID Int)

INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

SELECT
CategoryID, 0, CategoryName, ';' + LTRIM(STR(@.ParentID)) + ';', 'Root > ' + Categories.CategoryName,
(SELECT Count(SiteID) FROM Sites WHERE Sites.SiteCatID = CategoryID AND SiteActive = 1) As SiteCount,
Categories.DateSiteAdded,
@.ParentID
FROM
Categories
WHERE
ParentId = @.ParentID AND ModuleID = @.ModuleId
SELECT @.Rows = @.@.RowCount

WHILE @.Rows > 0
BEGIN
INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

SELECT S.CategoryID, @.Level + 1, S.CategoryName, SortColumn + LTRIM(RTRIM(STR(T.CategoryId))) + ';', Path + ' > ' + T.CategoryName,
(Select Count(SiteID) As Count FROM Sites WHERE SiteCatID = S.CategoryID AND SiteActive = 1)As SiteCount, S.DateSiteAdded, S.ParentID
FROM #Test2 T
JOIN Categories S On S.ParentId=T.CategoryId And T.Level = @.Level

SELECT @.Rows = @.@.RowCount, @.Level = @.Level + 1
END

SELECT CategoryID, CategoryName, Path, SiteCount, DateSiteAdded, ParentID, SortColumn, CulCount
FROM #Test2 WHERE ParentID = @.ParentID
ORDER BY SortColumn, CategoryName

DROP TABLE #Test2

This provides me with almost everything I need, except the culmative count of all descendent sites, I have been working on this for days and I can't find an answer. I am trying to stay away from using recursive functions for this so I can support a larger variety of databases if needed. Thanks for the assist.While @.@.Rowcount > 0
update #Test2
set CulCount = SiteCount + ChildSites
from #Test2
inner join --Get the count of sites from children
(select #Test2.PK, sum(Children.CulCount) ChildSites
from #Test2
inner join #Test2 Chilren on #Test2.PK = Chilren.ParentID) ChildTotals
on #Test2.PK = ChildTotals.PK
where not exists --Only update the lowest level that has not been updated
(select *
from #Test2 SubTable
where SubTable.ParentID = #Test2.PK
and SubTable.CulCount is null)

blindman

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't|||

Thank you

I have followed that reference, after applying the sqljdbc.dll and running th script, I have got this error when run the testconnection: I restarted WS and MSSQL Server both

Test Connection failed for datasource celio on server server1 at node RBONAKDA03 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the DLLs it references. Reason: 193(error not found).. View JVM logs for further details. null

|||Hi there,

Apologies for my late response.

Is the DLL registered on the database server? From things I have read, the DLL needs to be placed and registered on the server on which SQL Server runs.

Other than that, I can't think of anything. Sorry.
|||

There seems to be a slight confusion here.

It sounds like you are trying to configure Sql Server to use the Microsoft 2000 JDBC driver but then you are referencing the 2005 JDBC driver.

For a new application I would recommend that you use the 2005 Jdbc driver:

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

To set up distributed transaction support follow the instructions on the xa_install.sql file found ont the Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\xa directory (or sqljdbc_1.0\ directory for the RTW driver). To set up Websphere to work with the 2005 driver you can:

1) Add a new provider from the Providers menu under JDBC Providers
2) For path specify the path to your sqljdbc.jar
example : C:\Microsoft SQL Server 2005 JDBC
Driver\sqljdbc_1.1\enu\sqljdbc.jar
3) For implementation class name use the following.
com.microsoft.sqlserver.jdbc.SQLServerXADataSource

4) One you saved the provider choose the datasources menu and create a new
data source
5) You can use the generic data store helper class provided by Websphere AS
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't|||

Thank you

I have followed that reference, after applying the sqljdbc.dll and running th script, I have got this error when run the testconnection: I restarted WS and MSSQL Server both

Test Connection failed for datasource celio on server server1 at node RBONAKDA03 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the DLLs it references. Reason: 193(error not found).. View JVM logs for further details. null

|||Hi there,

Apologies for my late response.

Is the DLL registered on the database server? From things I have read, the DLL needs to be placed and registered on the server on which SQL Server runs.

Other than that, I can't think of anything. Sorry.
|||

There seems to be a slight confusion here.

It sounds like you are trying to configure Sql Server to use the Microsoft 2000 JDBC driver but then you are referencing the 2005 JDBC driver.

For a new application I would recommend that you use the 2005 Jdbc driver:

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

To set up distributed transaction support follow the instructions on the xa_install.sql file found ont the Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\xa directory (or sqljdbc_1.0\ directory for the RTW driver). To set up Websphere to work with the 2005 driver you can:

1) Add a new provider from the Providers menu under JDBC Providers
2) For path specify the path to your sqljdbc.jar
example : C:\Microsoft SQL Server 2005 JDBC
Driver\sqljdbc_1.1\enu\sqljdbc.jar
3) For implementation class name use the following.
com.microsoft.sqlserver.jdbc.SQLServerXADataSource

4) One you saved the provider choose the datasources menu and create a new
data source
5) You can use the generic data store helper class provided by Websphere AS
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)