Query/Reporting Tool for SQL Server Tables

Just a general question about any recommendations for a third party tool that would allow setting up a data-dictionary against SQL Server tables and a user could go in and build their own queries, reports. Perhaps they could save their query/report definitions for reuse. Other nice to haves - ability to output to PDF and export to XLS. Would prefer web interface, but can consider desktop as well. Any immediate recommendations?

Thanks.Allowing users direct access to tables is exactly the sort of thing good DBAs AVOID doing.|||Now, Now...

http://www.cognos.com/products/cognos8businessintelligence/reporting.html?lid=//Products//Cognos8BI//Reporting|||Brett - thanks for the product suggestion. That's the exact type of suggestion I'm looking for and welcome others.|||Well, I know when I've been snubbed... :(
Cognos is a good product, and has been around for a while. You might also look into DI Diver: http://www.dimensionalinsight.com/|||Blindman - sorry if you were snubbed.

And great thanks for suggesting another potential tool.

Obviously I have a lot to learn - and you and other experts on this board have been key in me understanding. I'm indebted to you and others and am sending cases of vitual beer and wild women your way.|||I would recommend stylereport http://www.inetsoft.com. Definitely the best reporting tool around.

query to list db and recovery model

I want a query that would list all databases on a server along with its
current recovery model setting such as
DB1 Simple
DB2 Full
Hassan wrote:
> I want a query that would list all databases on a server along with
> its current recovery model setting such as
> DB1 Simple
> DB2 Full
Exec sp_MSForEachDB 'SELECT ''?'' as "Database",
DATABASEPROPERTYEX(''?'', ''Recovery'') as "Recovery"'
or
Create Table #DBRecovery (
db_name nvarchar(128),
recovery nvarchar(30) )
Insert Into #DBRecovery
Exec sp_MSForEachDB 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',
''Recovery'') as nvarchar(30))'
select * from #DBRecovery
drop table #DBRecovery
David Gugick
Imceda Software
www.imceda.com
|||Hi Hassan,
If you want it just as an information, you can use sp_helpdb to get the list
of all the database and their recovery properties, assuming you have access
to all the databases in the server.
Thanks
Yogish
|||No need to get complicated...
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eb399oELFHA.1948@.TK2MSFTNGP14.phx.gbl...
>I want a query that would list all databases on a server along with its
> current recovery model setting such as
> DB1 Simple
> DB2 Full
>
|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> No need to get complicated...
> select [name],databasepropertyex([name],'Recovery') as [Recovery]
> from master.dbo.sysdatabases
> order by [name]
> --
> HTH
>
Jasper
Excellent simple answer. My question is: why does this work? I didn't
realise that databasepropertyex could use the fieldname in that way.
Is this a general featrure of T-SQL to recognize a field reference
before interpreting as a string?
Steve
|||All functions work this way. The function takes a string as parameter. That string can be a
constant, as in 'pubs', or a column name which derives the value for each row for the specified
column used in a SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <stevester@.freeuk.com> wrote in message
news:73627c14.0504150739.64492ae8@.posting.google.c om...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> Jasper
> Excellent simple answer. My question is: why does this work? I didn't
> realise that databasepropertyex could use the fieldname in that way.
> Is this a general featrure of T-SQL to recognize a field reference
> before interpreting as a string?
> Steve

query to list db and recovery model

I want a query that would list all databases on a server along with its
current recovery model setting such as
DB1 Simple
DB2 FullHassan wrote:
> I want a query that would list all databases on a server along with
> its current recovery model setting such as
> DB1 Simple
> DB2 Full
Exec sp_MSForEachDB 'SELECT ''?'' as "Database",
DATABASEPROPERTYEX(''?'', ''Recovery'') as "Recovery"'
or
Create Table #DBRecovery (
db_name nvarchar(128),
recovery nvarchar(30) )
Insert Into #DBRecovery
Exec sp_MSForEachDB 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',
''Recovery'') as nvarchar(30))'
select * from #DBRecovery
drop table #DBRecovery
David Gugick
Imceda Software
www.imceda.com|||Hi Hassan,
If you want it just as an information, you can use sp_helpdb to get the list
of all the database and their recovery properties, assuming you have access
to all the databases in the server.
Thanks
Yogish|||No need to get complicated...
select [name],databasepropertyex([name],'Recovery') as [Recovery
]
from master.dbo.sysdatabases
order by [name]
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eb399oELFHA.1948@.TK2MSFTNGP14.phx.gbl...
>I want a query that would list all databases on a server along with its
> current recovery model setting such as
> DB1 Simple
> DB2 Full
>|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<e8VIdEOLFHA.568@.TK2MSFTNGP
09.phx.gbl>...
> No need to get complicated...
> select [name],databasepropertyex([name],'Recovery') as [Recove
ry]
> from master.dbo.sysdatabases
> order by [name]
> --
> HTH
>
Jasper
Excellent simple answer. My question is: why does this work? I didn't
realise that databasepropertyex could use the fieldname in that way.
Is this a general featrure of T-SQL to recognize a field reference
before interpreting as a string?
Steve|||All functions work this way. The function takes a string as parameter. That
string can be a
constant, as in 'pubs', or a column name which derives the value for each ro
w for the specified
column used in a SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <stevester@.freeuk.com> wrote in message
news:73627c14.0504150739.64492ae8@.posting.google.com...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> Jasper
> Excellent simple answer. My question is: why does this work? I didn't
> realise that databasepropertyex could use the fieldname in that way.
> Is this a general featrure of T-SQL to recognize a field reference
> before interpreting as a string?
> Steve

query to list db and recovery model

I want a query that would list all databases on a server along with its
current recovery model setting such as
DB1 Simple
DB2 FullHassan wrote:
> I want a query that would list all databases on a server along with
> its current recovery model setting such as
> DB1 Simple
> DB2 Full
Exec sp_MSForEachDB 'SELECT ''?'' as "Database",
DATABASEPROPERTYEX(''?'', ''Recovery'') as "Recovery"'
or
Create Table #DBRecovery (
db_name nvarchar(128),
recovery nvarchar(30) )
Insert Into #DBRecovery
Exec sp_MSForEachDB 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',
''Recovery'') as nvarchar(30))'
select * from #DBRecovery
drop table #DBRecovery
David Gugick
Imceda Software
www.imceda.com|||Hi Hassan,
If you want it just as an information, you can use sp_helpdb to get the list
of all the database and their recovery properties, assuming you have access
to all the databases in the server.
--
Thanks
Yogish|||No need to get complicated...
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eb399oELFHA.1948@.TK2MSFTNGP14.phx.gbl...
>I want a query that would list all databases on a server along with its
> current recovery model setting such as
> DB1 Simple
> DB2 Full
>|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> No need to get complicated...
> select [name],databasepropertyex([name],'Recovery') as [Recovery]
> from master.dbo.sysdatabases
> order by [name]
> --
> HTH
>
Jasper
Excellent simple answer. My question is: why does this work? I didn't
realise that databasepropertyex could use the fieldname in that way.
Is this a general featrure of T-SQL to recognize a field reference
before interpreting as a string?
Steve|||All functions work this way. The function takes a string as parameter. That string can be a
constant, as in 'pubs', or a column name which derives the value for each row for the specified
column used in a SELECT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <stevester@.freeuk.com> wrote in message
news:73627c14.0504150739.64492ae8@.posting.google.com...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
>> No need to get complicated...
>> select [name],databasepropertyex([name],'Recovery') as [Recovery]
>> from master.dbo.sysdatabases
>> order by [name]
>> --
>> HTH
> Jasper
> Excellent simple answer. My question is: why does this work? I didn't
> realise that databasepropertyex could use the fieldname in that way.
> Is this a general featrure of T-SQL to recognize a field reference
> before interpreting as a string?
> Steve

Query Timeout Setting

SQL Server 2005 is timing out when running loooong queries.

Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?

Thanks

Craig

Perhaps its better to fix the long running query?

|||

ndinakar:

Perhaps its better to fix the long running query?

I agree completely!

The number of reusable connections is limited. A few users of a single long-running query can bring an entire system down!

Think about driving on a road with ten lanes going your way. Just two slooooow drivers, each in different lanes, can cause traffic jams really fast in busy traffic.

|||

When you have 80 - 160 million records in 2 or more tables we need all options possible, so any answers to the original question?

|||

80-160 mill rows is nothing for SQL Server, if your query is properly optimized and have the right indexes. I work with tables over 500 mill rows to as high as 1.2 bill rows. So, fixing the query will be a better solution in the longer run. If you can show us the query that's timing out we can try to help with that.

Check this article on various ways to set timeout limits:http://vyaskn.tripod.com/watch_your_timeouts.htm

|||

Both the connection object and the command object have a property that deals with maximum times.

For legitimate, long-running queries that cannot be better tuned or designed, just up the time on those properties.

That way, you force your developers (assuming you are developing with test datasets of the appropriate size) to identify long-running queries before they hit production. It gives you an opportunity to improve the sql before the system goes live.

By searching for those property names, you can identify long-running queries (from the developer's point of view) and route them to your sql specialist for tuning.

query time out

What is best setting for query time out 600 or 0 , unlimited . and why?
On Sat, 12 Jan 2008 15:21:44 -0800, "OA" <omrana@.verizon.net> wrote:

>What is best setting for query time out 600 or 0 , unlimited . and why?
The best setting for query timeout is whatever is best for what you
are doing, because each circumstance is different.
I tend toward zero (unlimited) myself, but there are many applications
where that might be a mistake. Obviously an application that has
queries that take three minutes needs a timeout greater than that.
Likewise for an application that should respond in less than a second
it might make more sense to set a limit that is less than a delay that
causes the user to scream.
Roy Harvey
Beacon Falls, CT

query time out

What is best setting for query time out 600 or 0 , unlimited . and why?On Sat, 12 Jan 2008 15:21:44 -0800, "OA" <omrana@.verizon.net> wrote:
>What is best setting for query time out 600 or 0 , unlimited . and why?
The best setting for query timeout is whatever is best for what you
are doing, because each circumstance is different.
I tend toward zero (unlimited) myself, but there are many applications
where that might be a mistake. Obviously an application that has
queries that take three minutes needs a timeout greater than that.
Likewise for an application that should respond in less than a second
it might make more sense to set a limit that is less than a delay that
causes the user to scream.
Roy Harvey
Beacon Falls, CT