Archive for October, 2011

count database updates for all tables

October 17, 2011

To count all database updates for all tables (that containe a datetime field), just execute this query:

— purpose – see if this database is being updated
— use a set of queries on datetime fields.
— Make the queries dynamic, and pass in the table and column names.
— collate results into a temp table and then display all together
declare @t_name varchar(50)
declare @c_name varchar(50)
declare @query_base varchar(250)
set @query_base = ‘select {QQQTABLE} as [TableName], count(*) as CountRecentUpdates from QQQTABLE where QQQCOLUMN > dateadd(d,-1,getdate() )’
declare @query varchar(500)
declare @TempTableInsert varchar(80)
set @TempTableInsert = ‘ insert into #MyTempTable ([TableName] , [CountRecentUpdates] ) ‘

CREATE TABLE #MyTempTable ([TableName] varchar(50), [CountRecentUpdates] varchar(50) )

declare ci cursor for
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (DATA_TYPE = ‘datetime’) AND (NOT (COLUMN_DEFAULT IS NULL))

open ci

fetch next from ci into
@t_name,
@c_name

WHILE @@FETCH_STATUS = 0

BEGIN

–print @t_name + ‘ ‘ + @c_name

set @query = replace(@query_base,’QQQTABLE’, @t_name)
set @query = replace(@query ,’QQQCOLUMN’, @c_name)
set @query = replace(@query ,'{‘ , char(39) )
set @query = replace(@query ,’}’ , char(39) )
set @query = @TempTableInsert + @query
–print @query
EXECUTE(@query)
— *************************************************************
— RE-LOAD THE CURSOR
fetch next from ci into
@t_name,
@c_name

end
close ci
deallocate ci

select * from #MyTempTable
drop table #MyTempTable

mvc db context error

October 17, 2011

I was following the MVC Movie example, and decided at one point not to follow the directions strictly. (http://www.asp.net/mvc/tutorials/getting-started-with-mvc3-part7-cs)

I got the error: “The model backing the ‘MovieDBContext’ context has changed since the database was created. ”

There is a fix described at http://stackoverflow.com/questions/3552000/entity-framework-code-only-error-the-model-backing-the-context-has-changed-sinc

But, I just deleted the table from movies.sdf that had the index definitions in it, and things started working again.