Posts Tagged 'SQL Server 2008'



Script Column Default Values

Another database developer had copied several database from our model office back to our development environment. When we started to wire up out applications I noticed that there were no indexes, defaults, keys, or constraints of any kind. Just heaps.

Adding these constraints is fairly straigtforward, and there are many tools out there to do that. But I could not find a tool that would script just the default constraints without also dropping and/or creating the table. It took a couple of days to get the databases moved over in the first place, but there were over a thousand default constraints, which could also take several days if done manually.

I was sure that there was a way to script this, but after over an hour of searching I decided that I burned enough time and that I would write one myself. I don’t know how often anyone would come across this issue, but here it is anyways.

SELECT
    'ALTER TABLE dbo.' + OBJECT_NAME(s.id) + ' ADD CONSTRAINT DF_' + OBJECT_NAME(s.id) + '_' + sc.name + ' DEFAULT ' + c.text + ' FOR ' + sc.name
FROM
    dbo.sysconstraints s
INNER JOIN syscolumns sc
	ON s.id = sc.id AND
	   s.colid = sc.colid
INNER JOIN dbo.syscomments c
    ON s.constid = c.id
WHERE
    s.status & 5 = 5
Advertisements

Installing (and Running) SSRS 2008 in Windows Vista

It’s been a while since I’ve played around with SQL Server Reporting Services, and I found a couple of interesting articles on using a report model with SSRS, so I decided to give it a go.

The installation went smoothly, as I have performed this task many times. However, when I visited the reporting services site I had no access. I am an administrator on the machine, and as such should have had access to the site.

I searched around for a good half hour and still hadn’t found anything that worked. Somehow I got the idea to run my internet browser as Administrator. Eureka! I added my local account to the SSRS instance and everything works very nicely.

~Ron