Report of the Best Prctice Analyser

First of all I have to say THANK YOU to Microsoft for that tool.
It is a great tool for managing SQL databases - we got many many intersting
hints to enhance our SQL servers.
Using that tool we now got two interesting things:
On our server we're also hosting the Sharepoint databases of the Sharepoint
Services - a Microsoft product!
When scanning the server with BPA the most reported databases were the
Sharepoint databases! I gave this information to Mike Fitzmaurice of the
Sharepoint team yet!
But we have an own problem too! Maybe someone can help:
We had (don't know why!) some tables an stored procedures in our master
database - BPA told us this!
Then we dropped these objects because we don't need them.
Now BPA tells us that 2 user objects remained in the master database:
Scan Details
...
Object Name Object Type Object Type Description
pk_dtproperties PK PRIMARY KEY constraint
DF__dtpropert__versi__4A0EDAD1 D Default or DEFAULT constraint
...
How can we get rid of these objects?
Many thanks in advance
Thomas
Seems like these constraints are associated with a table dt_properties (used
for storing database diagrams). Can you check if this exists in your master
database?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mueller-Lynch Thomas" <MuellerLynchThomas@.discussions.microsoft.com> wrote
in message news:63A92A40-BF14-4DE6-B43B-30C400266365@.microsoft.com...
First of all I have to say THANK YOU to Microsoft for that tool.
It is a great tool for managing SQL databases - we got many many intersting
hints to enhance our SQL servers.
Using that tool we now got two interesting things:
On our server we're also hosting the Sharepoint databases of the Sharepoint
Services - a Microsoft product!
When scanning the server with BPA the most reported databases were the
Sharepoint databases! I gave this information to Mike Fitzmaurice of the
Sharepoint team yet!
But we have an own problem too! Maybe someone can help:
We had (don't know why!) some tables an stored procedures in our master
database - BPA told us this!
Then we dropped these objects because we don't need them.
Now BPA tells us that 2 user objects remained in the master database:
Scan Details
...
Object Name Object Type Object Type Description
pk_dtproperties PK PRIMARY KEY constraint
DF__dtpropert__versi__4A0EDAD1 D Default or DEFAULT constraint
...
How can we get rid of these objects?
Many thanks in advance
Thomas

Report of every user for every database on server

For auditing purposes, I create a list of all the users who have access to
my databases. I do this by issuing this:
exec sp_MSForEachDB sp_helpuser
However, this doesn't include the name of the database that the list of
users is for. How can I include this info?
Thanks."Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23vGdjyRmFHA.2080@.TK2MSFTNGP10.phx.gbl...
> For auditing purposes, I create a list of all the users who have access to
> my databases. I do this by issuing this:
> exec sp_MSForEachDB sp_helpuser
> However, this doesn't include the name of the database that the list of
> users is for. How can I include this info?
> Thanks.
>
I use some dynamic sql and a cursor for this. In Query Analyzer, set your
output to Text (as opposed to grid).
BEGIN
SET NOCOUNT ON
-- Create some variables
DECLARE @.currentdb sysname, -- Current database that we are working with
@.sql varchar(4000) -- Dynamic SQL statement
-- Load the cursor with the current list of database names. Exclude SQL
Server databases.
DECLARE dbnames CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'Northwind',
'pubs')
ORDER BY [name] ASC
-- For each database, perform your query
OPEN dbnames
FETCH NEXT
FROM dbnames
INTO @.currentdb
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- The @.sql statement should be in the form of:
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
-- Print a header to the messages tab
PRINT '=======================================
=================='
PRINT @.currentdb
PRINT ''
-- Execute the sql statement
EXECUTE(@.sql)
PRINT ''
PRINT '=======================================
=================='
PRINT ''
PRINT ''
-- Get the next database name
FETCH NEXT
FROM dbnames
INTO @.currentdb
END
-- Cleanup
CLOSE dbnames
DEALLOCATE dbnames
END
Rick Sawtell|||Nice script! However, it seems to have an issue with databases that begin
with the number zero (0). I get the folowing error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '0'.
Any ideas?
Thanks.|||"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uykxweSmFHA.572@.TK2MSFTNGP15.phx.gbl...
> Nice script! However, it seems to have an issue with databases that begin
> with the number zero (0). I get the folowing error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '0'.
> Any ideas?
> Thanks.
>
Ummm.. Find a better naming convention.. <wink>
I was under the impression that all object qualifiers in SQL Server 2000
require a letter as the first character, followed by numbers and some
symbols...
Guess I was wrong.
You could modify the script to include the square brackets. That may fix
it.
So adjust as follows:
Old line
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
New line
SET @.sql = '[' + @.currentdb + '].dbo.sp_helpuser'
I should have probably done that in the first place for those who insist on
putting spaces etc. in their naming conventions.
Rick Sawtell|||Use QUOTENAME(@.currentdb) instead of @.currentdb
Steve Kass
Drew University
Joshua Campbell wrote:

>Nice script! However, it seems to have an issue with databases that begin
>with the number zero (0). I get the folowing error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '0'.
>Any ideas?
>Thanks.
>
>|||It's not sufficient. Because I had to something very similar but including
all the permissions per each user using sp_helpuser
"Steve Kass" wrote:

> Use QUOTENAME(@.currentdb) instead of @.currentdb
> Steve Kass
> Drew University
> Joshua Campbell wrote:
>
>|||I'm sorry, but I don't understand you. If there is problem with
QUOTENAME, could you show sample code where it doesn't
work as expected?
SK
Enric wrote:
>It's not sufficient. Because I had to something very similar but including
>all the permissions per each user using sp_helpuser
>"Steve Kass" wrote:
>
>

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

sql

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

Report model for an Oracle database

We use both Oracle and SQL Server databases. We'd like to create a report model for some on-demand reports, and it seems to be limited to the SQLClient. Is this the only choice? Can it connect to Oracle? Your guidance is much appreciated. This article seems to indicate that many choices exist for the provider: http://www.databasejournal.com/features/mssql/article.php/10894_3612131_3.

Try this link for what is needed to create reports with Oracle data. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms159219.aspx

|||That's what I was looking for. Thanks, Caddre|||

oneworld95:

That's what I was looking for. Thanks, Caddre

I am glad I could help, I am just like you we will be using both Oracle and SQL Server.

Report Model

Can you have more than one data source view in one report model? I have data from multiple sql databases that I want to combine into a single report model for the users.

Currently only one data source per model is supported.

It may be possible however to use Data Source View editor to manually add named queries which reference other databases.

|||

Lev is right. If you want to create a model over a set of databases you need to do some manual work with your DSV.

In model designer start with generating DSV for primary database (the one containing more data). Then manually add named queries to the DSV each selecting data from another database (select * from myotherdb.dbo.tableN). For each named query manually add info about which columns should be treated as PK and which are FKs. This is important for semantic model generation that would omit tables without PKs and will note create roles between entities without FKs.
After you finish with your DSV - generate model in model designer.

Report from multiple databases

Hello,
I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
Report should be done in Access. There are 4 different databases on same SQL
Server.
I need report showing:
CustomerID
AgencyName
State
TotalDVI
TotalDRI
TotalVI
.....
Totals are counts of all files in corresponding tables for all 4 databases.
To add to this problem, one database has different names of tables and
fields in them then other 3 databases.
Databases are definitely a mess and I can't do anything about it at this time.
Is there a way to accomplish this?
--
Deki PATwo ways:
Create linked tables in Access. Write your queries and reports.
Create linked servers in SQL and use reporting services.
Either is yucky architecturally speaking.
Get the data out of access whenever phesible.
Deki wrote:
> Hello,
> I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
> Report should be done in Access. There are 4 different databases on same SQL
> Server.
> I need report showing:
> CustomerID
> AgencyName
> State
> TotalDVI
> TotalDRI
> TotalVI
> .....
> Totals are counts of all files in corresponding tables for all 4 databases.
> To add to this problem, one database has different names of tables and
> fields in them then other 3 databases.
> Databases are definitely a mess and I can't do anything about it at this time.
> Is there a way to accomplish this?
> --
> Deki PA|||Thanks Frisco!
--
Deki PA
"FriscoSoxFan" wrote:
> Two ways:
> Create linked tables in Access. Write your queries and reports.
> Create linked servers in SQL and use reporting services.
> Either is yucky architecturally speaking.
> Get the data out of access whenever phesible.
>
> Deki wrote:
> > Hello,
> > I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
> > Report should be done in Access. There are 4 different databases on same SQL
> > Server.
> > I need report showing:
> >
> > CustomerID
> > AgencyName
> > State
> > TotalDVI
> > TotalDRI
> > TotalVI
> > .....
> >
> > Totals are counts of all files in corresponding tables for all 4 databases.
> > To add to this problem, one database has different names of tables and
> > fields in them then other 3 databases.
> > Databases are definitely a mess and I can't do anything about it at this time.
> > Is there a way to accomplish this?
> >
> > --
> > Deki PA
>