Property owner is not available for database

Recently I got this error message when tried to see database properties :

Cannot show requested dialog.

Additional information:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database ’[ ]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

(Microsoft.SqlServer.Smo)

This happened after we established a new domain so I suspected that this is caused by orphaned SID since most of the time I created database with Windows credential. After googling I got sure answer that this is database owner problem, exactly due to sid being out of sych.

So based on idea from this blog, I run the following simple sql statement (SQL 2005):

exec sp_msforeachdb 'alter authorization on database::? to sa;'

This will reset database owner of all databases to sa. This statement is safe as the alter authorization statement is not applicable to system databases.

If you want to update a single database only just use this statement:

alter authorization on database::dbname to sa;

For SQL 2000, use this statement :

exec dbname..sp_changedbowner 'sa'

Search which database has specific column name

select *
from information_schema.columns

where column_name = 'searchcolumn'

Find database given physical file name

I was looking for ways to clean up my server disk space. One of them by shrinking sql server log file. I found a log file that was quite big. Unfortunately, there was no database name that has similar name with the physical file. I would not check physical name of each database one by one by issuing sp_helpdb command as my server hold tens databases. I would rather run script to find exactly what database hold this file.
Here is what I get:
SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files
where physical_name ='physicalfilename'

SQL Server Reporting Service database version error

I was learning SQL Server 2005 reporting service, when I got this error during database setup:
Couldn't generate the upgrade script. There is no upgrade script available for this database version.
It seems that the database server that host the report server database is not the same with the reporting services installation.
I checked my database is SQL Server 2005 SP2, but I don't know my Reporting Service version.
Then I searched through Microsoft MSDN and found this page which tells me various ways to check reporting service version.
By checking the log file as suggested, I found that my Reporting Services is SQL Server 2005 SP1.
So now I am going to upgrade my reporting service.

Axapta view of view may cause wrong results

When create view in Axapta, an existing view should not be used as data source because it may produce wrong result.
This happen when the originating view has more than one data source in it.
When view is created, Axapta uses the following technique :
- Use the first data source's dataareaid field as the view dataareaid field
- Use the second data source's dataareaid field as the view dataareaid#2 field
When used as a whole, this view will only show the first dataareaid as its dataareaid. Axapta kernel will filter based on dataareaid and dataareaid#2 with the correct company account. This case no problem.

Problem occurs when the view is used in another view, with additonal datasource attached to it.
Using the same rule as above, the new view's dataareaid#2 field will be replaced by the new datasource attached to it, so the original view second data source is no longer filtered by dataareaid. This may lead to a duplicate data when the same data of the original view's second data source exists in more than one company.

Delete duplicate rows

Recently I need to build an Axapta instance where the application has been modified while I only have a standard demo database.
Problem arises as the modified application has new indexes or change the table properties 'SaveDataPerCompany' to No instead of default Yes. Both may cause duplicate record problem. I don't want to just delete the entire record yet I need to synchronize the table with Axapta application.

It will be no problem if the table contains less than 20 records, I can just delete it manually. The problem is that I find a table has hundreds of records. Then I write this script to do this job less tedious.

--drop table #mytemptable -- This is needed only if run more than once
select distinct
module,type,groupid --specify as many as key fields
into #mytemptable
from pricediscgroup --this is the table name

--set variable s correspond to the key fields
declare @module int
declare @type intdeclare @groupid varchar(20)
declare @numberofrows int
declare mycursor Cursor for
SELECT * from #mytemptable
open mycursor

fetch next from mycursor into @module,@type,@groupid

while @@fetch_status=0
begin

select @numberofrows = count(*) from pricediscgroup where
module = @module and type= @type and groupid = @groupid
select @numberofrows, @module,@type,@groupid
if @numberofrows > 1
begin
set @numberofrows = @numberofrows - 1 -- leave one row

set rowcount @numberofrows --the select within the subquery will return this number of rows,
delete from pricediscgroup where recid in (
select recid from pricediscgroup p2
where
p2.module = @module and p2.type= @type and p2.groupid = @groupid)

end

fetch next from mycursor into @module,@type,@groupid
end

close mycursor

deallocate mycursor

It does take time to change the table name and adding some variables depend on the duplicate fields found. But it takes less time rather than deleting many rows manually.

Role centers and enterprise portal on Ax 2009

During installation of role centers and enterprise portal I got this error:

Setup cannot connect to the Application Object Server instance (AOS) by using the Business Connector. Confirm that the AOS is running, and that your account (domain\username) is a valid Microsoft Dynamics AX user. See the log file C:\Documents and Settings\All Users\Application Data\Microsoft\Dynamics AX\Dynamics AX Setup Logs\2008-09-11 11-27-45\DynamicsSetupLog.txt for more information.

I have checked that the user already exists in Axapta. Then I tried changed the service account with the same user and it works.
The service account is in Administration > Setup > Security > System service account. Put the username and domain in the alias and network domain fields.

I post this with hope to save time just in case others get the same problem.