Archive for July, 2009

list all logins of a server

July 14, 2009

I’ve found some tremendous resources for administrative sql scripts:
in general:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
for the one I’m posting about: http://demiliani.com/blog/archive/2005/05/13/2449.aspx

But , you may ask, why should you get excited about some complicated sql script that generates other sql script that you must copy past and execute? Well, I have a server where someone created the SA userid, and it’s probably been there forever, and I’d like to get rid of it, but how do I know which databases it’s being used for?

put all the logins into a table:
1)
use master
select
'insert into loginstemp select ''' + name + ''' as DBName , name as Login from ' + name + '.sys.sysusers where islogin=1 '
as 'CopyAndExecute'
from master.dbo.sysdatabases

2) create temp table
use MyWriteTable

drop table loginstemp

create table loginstemp (DBName varchar(50), [Login] varchar(50) )

3) copy and execute the generated sql

use MyWriteTable

insert into loginstemp select 'ATable' as DBName , name as Login from ATable.sys.sysusers where islogin=1
insert into loginstemp select 'MyWriteTable' as DBName , name as Login from MyWriteTable.sys.sysusers where islogin=1
etc.

4) view temp table
use MyWriteTable
select * from loginstemp

size of all tables in a database

July 13, 2009

purpose – what if some tables are growing a lot more than you expected? wouldn’t it be handy to have a snapshot of how large all your tables are?


-- replace DB2Data for your own database
use db2data

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempCountRecords '))
DROP TABLE #TempCountRecords

CREATE TABLE #TempCountRecords (
[Table_Name] varchar(50),
[CountRecords] int
)

declare @SQLcommand varchar(200)

declare @TableName varchar(50)
declare @TableSchema varchar(50)
DECLARE c_db CURSOR FOR SELECT table_name, table_schema from information_schema.tables where table_type='base table'

OPEN c_db
FETCH NEXT FROM c_db INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
begin

set @TableName = '['+@tableschema + '].[' + @tableName + ']'

set @SQLCommand = ' select ''QQQ'' as [Table_Name] , count(*) as [CountRecords] from QQQ '
set @SQLcommand = replace( @SQLcommand , 'QQQ' , @TableName )
print @SQLcommand

-- insert and execute all in one statement - refer to ms_Script.sql in the Status_DBQueries
insert #TempCountRecords
EXECUTE (@SQLcommand)

FETCH NEXT FROM c_db INTO @TableName, @tableschema

end
DEALLOCATE c_db

select * from #TempCountRecords order by CountRecords desc
drop table #TempCountRecords

mvc – RouteTable not defined

July 7, 2009

It’s kind of a bummer when you try to make you’re first asp.net mvc app, just select the defaults, and get a lot of weird errors.
For me, in global.asax, RouteTable and RouteCollection were not defined.
1) I noticed in comparing my global.asax to an example I found somewhere that the following lines were missing from mine:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.Mvc
Imports System.Web.Routing

2) but to get system.web.routing to be valid, I had to include some references. This is what I found at
(http://www.packtpub.com/article/mixing-asp.net-webforms-and-asp.net-mvc)
“add a reference to the following three assemblies to your existing ASP.NET application:”

System.Web.Routing
System.Web.Abstractions
System.Web.Mvc

So now my “hello world” app actually builds.

Asp.Net MVC template problem

July 6, 2009

Text of the problem (as I type it in):
Error: this template attempted to load an untrusted component ‘Microsoft.VisualStudio.Web.Extensions, Version=9.0.0.0,Culture=neutral, PublicKeyToken=31bf3856ad364e35’. etc.

Resolution: downloaded and installed Asp.Net MVC 1.0 from http://www.microsoft.com/downloads/details.aspx?FamilyID=53289097-73ce-43bf-b6a6-35e00103cb4b&displaylang=en

Root Cause? perhaps I had a beta already installed that I had never attempted to use before.