A forum for technical support discussion related to Fogbugz.
Hi, I was trying to create some reports against our FogBugz database (Sql Server) and I noticed that when it was setup the owner of the table structure was the Windows account it was created with rather than dbo. Everything runs fine from the FogBugz client side but when I run queries from Query Analyzer against the database, I run into problems even when fully qualifying the name. My question is if I change these tables to dbo ownership should I expect any problems on the FogBugz client side? (From other posts I've seen out here on the board it appears that the standard installation does use dbo for setup; is that correct?)
This is the top issue that I get asked about with regards to connecting CaseDetective for FogBugz to a SQL Server database.
A generous person called "bmschkerke" wrote a nice post to the CaseDetective forums about how to automate the changing of the table owner to dbo for the entire database. The post at http://forums.imijsoft.com/viewtopic.php?pid=46#p46 includes links to the original source of the script.
Here's the link and script:
SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
ltrim(u.name) + '.' + ltrim(s.name) + ''''''
+ ', @newowner = dbo'')'
FROM sysobjects s,
WHERE s.uid = u.uid
AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name not like 'INFORMATION%'
order by s.name
It's a single sql command that generates an EXEC command for each object in the database that needs changing.
I just ran it in SQL Query Analyzer on one of my test FogBugz databases that had a couple of tables not owned by dbo and it worked fine.
I copied the results back up into the SQL editor and ran them (after checking them over), when I flipped back to Enterprise Manager and refreshed the table list, both tables had been updated correctly.
I'm sure it goes without saying (but I'm going to) that you should test this script and it's results on a non-production copy before running for real, it worked for me but that's no guarantee it'll work for you.
Ian M. Jones (CaseDetective)
Friday, January 27, 2006
This topic is archived. No further replies will be accepted.Other recent topics