How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ck
use the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableN ame T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ckuse the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableName T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

How do I fix this cluster??

I have two w2k3 Servers on a SAN. I also have separate instances of SQL Server on the separate physical servers. I have have set up a cluster between the two. And I also have them in separate groups. I am trying to set up an active/active on SQL Server. H
owever, when I try to set up the 2nd virtual server for the active/active cluster, I get an error that says,
'The path, x:\Program Files\Microsoft SQL Server, that you have chosen is either not on a cluster disk resource, or if it is, the resource may be unavailable for the following reasons:
-The disk resource is owned by another node.
-You are installing a virtual server, but selected a disk which is in a group with a virtual server already in it.
HELP!
Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
you want to add another instance and do A/A? You have two groups, but does
each group have a separate disk resource? Say X for Group 1, Y for Group 2?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> I have two w2k3 Servers on a SAN. I also have separate instances of SQL
Server on the separate physical servers. I have have set up a cluster
between the two. And I also have them in separate groups. I am trying to set
up an active/active on SQL Server. However, when I try to set up the 2nd
virtual server for the active/active cluster, I get an error that says,
> 'The path, x:\Program Files\Microsoft SQL Server, that you have chosen is
either not on a cluster disk resource, or if it is, the resource may be
unavailable for the following reasons:
> -The disk resource is owned by another node.
> -You are installing a virtual server, but selected a disk which is in a
group with a virtual server already in it.
> HELP!
|||Yes they are on separate physical win2k3 servers
"Rodney R. Fournier [MVP]" wrote:

> Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
> you want to add another instance and do A/A? You have two groups, but does
> each group have a separate disk resource? Say X for Group 1, Y for Group 2?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> Server on the separate physical servers. I have have set up a cluster
> between the two. And I also have them in separate groups. I am trying to set
> up an active/active on SQL Server. However, when I try to set up the 2nd
> virtual server for the active/active cluster, I get an error that says,
> either not on a cluster disk resource, or if it is, the resource may be
> unavailable for the following reasons:
> group with a virtual server already in it.
>
>
|||Other question. Do I need to set up the sql server cluster prior to a w2k3 server cluster
"Rodney R. Fournier [MVP]" wrote:

> Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
> you want to add another instance and do A/A? You have two groups, but does
> each group have a separate disk resource? Say X for Group 1, Y for Group 2?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> Server on the separate physical servers. I have have set up a cluster
> between the two. And I also have them in separate groups. I am trying to set
> up an active/active on SQL Server. However, when I try to set up the 2nd
> virtual server for the active/active cluster, I get an error that says,
> either not on a cluster disk resource, or if it is, the resource may be
> unavailable for the following reasons:
> group with a virtual server already in it.
>
>
|||Agreed two servers (nodes in clustering). They also need shared storage, SAN
or SCSI based. Can you tell us more about your clustering groups. Does
failover currently work?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:031DC113-90E3-4448-8756-AE4FAF1A9E09@.microsoft.com...[vbcol=seagreen]
> Yes they are on separate physical win2k3 servers
> "Rodney R. Fournier [MVP]" wrote:
Now[vbcol=seagreen]
does[vbcol=seagreen]
2?[vbcol=seagreen]
SQL[vbcol=seagreen]
set[vbcol=seagreen]
is[vbcol=seagreen]
a[vbcol=seagreen]
|||I am currently just setting them up. When I tell them to initiate failure, it seems to work fine
"Rodney R. Fournier [MVP]" wrote:

> Agreed two servers (nodes in clustering). They also need shared storage, SAN
> or SCSI based. Can you tell us more about your clustering groups. Does
> failover currently work?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:031DC113-90E3-4448-8756-AE4FAF1A9E09@.microsoft.com...
> Now
> does
> 2?
> SQL
> set
> is
> a
>
>
|||No. You need to first setup up Windows cluster server and then setup SQL Server 2000 failover clustering. SQL cluster runs on top of Windows cluster.
For additional information please review
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/SQL/techinf...vercluster.asp
Need more references, here you go :
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Microsoft Webcasts
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd061002/wcdblurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
Microsoft Knowledge Base Articles
Q243218 INF: Installation Order for SQL 2000 Enterprise Edition
http://support.microsoft.com/?kbid=243218
Q260758 - INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?kbid=260758
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||However,
When I do this I get an error saying that the system files
could not be found to complete setup. What does that mean?
Also when I look at the Cluster admin, the only thing that
does not start is the virtual SQL Server itself. The IP
add and net name are fine
>--Original Message--
>No. You need to first setup up Windows cluster server and
then setup SQL Server 2000 failover clustering. SQL
cluster runs on top of Windows cluster.
>For additional information please review
>Microsoft Whitepaper - SQL Server 2000 Failover
Clustering
>http://www.microsoft.com/SQL/techinf...stration/2000/
failovercluster.asp
>Need more references, here you go :
>Microsoft SQL Server 2000 High Availability Series
>http://www.microsoft.com/technet/pro.../sql/2000/depl
oy/sqlhalp.mspx
>Microsoft Webcasts
>Introduction to Microsoft SQL Server 2000 Clustering
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wc051001/wcblurb051001.asp
>Microsoft SQL Server 2000 Virtual Server: Things You
Should Know
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wc032602/wcblurb032602.asp
>
>Microsoft SQL Server 2000 Virtual Server Basic Setup,
Maintenance, and Service Pack
http://support.microsoft.com/default.aspx?
>scid=/servicedesks/webcasts/wcd061002/wcdblurb061002.asp
>Microsoft SQL Server 2000 Failover Clustering Disaster
Recovery Procedures
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd101802/wcdblurb101802.asp
>Troubleshooting SQL 2000 Virtual Server and Service Pack
Setups for Failover Clustering
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd020703/wcdblurb020703.asp
>
>Microsoft Knowledge Base Articles
>Q243218 INF: Installation Order for SQL 2000 Enterprise
Edition
>http://support.microsoft.com/?kbid=243218
>Q260758 - INF: Frequently Asked Questions - SQL Server
2000 - Failover Clustering
>http://support.microsoft.com/?kbid=260758
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
>http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
>way to do this is to visit the following websites:
http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>
|||Please give more details for us to help you. Here are some things I would like to know:
1) From your postings, it appears that you have installed Windows 2003 Cluster Server? Is that correct?
2) If yes, what are the groups that you have? Also, write the resources in each group?
3) Have you successfully installed SQL Server 2000 virtual instance? What method did you use to install SQL Server 2000 virtual instance?
4) You wrote "Also when I look at the Cluster admin, the only thing that
does not start is the virtual SQL Server itself. The IP
add and net name are fine"
Has the virtual SQL Server resource ever come online?
5) What error do you get when you try to get the resource online? See NT app log, NT sys log, Cluster.log and SQL Errorlog
6) Can you start SQL Server 2000 virtual instance from the command window?
7) If yes, can you connect to it ? Let us know what you do to conect to it and what is the servername that you use to connect to it?
8) You wrote " When I do this I get an error saying that the system files
could not be found to complete setup. What does that mean? "
I do not understand what you mean by saying "when I do this" --> "when you do what?".
Provide as much details as you can. As you might have already realized that troubleshooting SQL Clustering issues are not that straight forward? So the more info you give i.e.details setps that you took and the
errors that you get, attaching/copying/pasting the errors will help us help you with your issues.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005's taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.