Force Recompile on all Views and Stored Procs

Is there a command or a script that will force all views and Stored Procs to
recompile? I'm trying to resolve the issue when views fails because field
order is changed in a Database structure.
I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
but it doesn't recompile until the next call of the stored Proc.
Don't think that there is a database or server wide command to do that.
You have to do it in the SP or view level.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
jmhmaine wrote:
> Is there a command or a script that will force all views and Stored Procs to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.
|||Is there a way to script a loop of all views and Stored Procs instead of
creating a static list?
"Yih-Yoon Lee" wrote:

> Don't think that there is a database or server wide command to do that.
> You have to do it in the SP or view level.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> jmhmaine wrote:
>
|||here's something that may be helpful - it will return a resultset that
you can use, i.e., not actually run any drops.
be sure to review the output before you run the output though.
-- creates a script that drops all stored procedures and views.
-- excludes procedures starting w/ dt_ and sys.
begin
declare @.procName sysname
declare @.procType char(2)
declare @.dropProcSql varchar(256)
create table #procNameTbl (procName sysname)
declare procCursor cursor for
select name, type from sysobjects
where type in ('P', 'V') and
substring(name, 1, 3) <> 'dt_' and
substring(name, 1, 3) <> 'sys'
order by name
open procCursor
fetch next from procCursor into @.procName, @.procType
while @.@.fetch_status = 0
begin
if @.procType = 'P'
set @.dropProcSql = 'drop procedure ' + @.procName
else
set @.dropProcSql = 'drop view ' + @.procName
insert into #procNameTbl values (@.dropProcSql)
fetch next from procCursor into @.procName, @.procType
end
close procCursor
deallocate procCursor
select * from #procNameTbl
drop table #procNameTbl
end
go
|||Here are some examples, using undocumented stored procedure sp_execresultset
(do not recommend using it in production) and using a cursor to traverse
procedures and views and recompile using sp_recompile and refresh views using
sp_refreshview.
Example:
use northwind
go
execute sp_execresultset N'
select
''execute sp_recompile '' + quotename(routine_name)
from
information_schema.routines
where
routine_type = ''procedure''
and objectproperty(object_id(routine_schema + ''.'' +
quotename(routine_name)), ''IsMSShipped'') = 0'
go
declare @.rn sysname
declare @.sql nvarchar(4000)
declare routines_cursor cursor local fast_forward
for
select
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(routine_schema + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open routines_cursor
while 1 = 1
begin
fetch next from routines_cursor into @.rn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_recompile ' + quotename(@.rn)
execute sp_executesql @.sql
end
close routines_cursor
deallocate routines_cursor
go
execute sp_execresultset N'
select
''execute sp_refreshview '' + quotename(table_name)
from
information_schema.views
where
objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
''IsMSShipped'') = 0'
go
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_name
from
information_schema.views
where
objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.tn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
execute sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
go
AMB
"jmhmaine" wrote:
[vbcol=seagreen]
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" wrote:
|||I would use here DBCC FREEPROCCACHE to remove every cached execution plan
from memory. You must have administrative rights however to execute this.
Marc
"jmhmaine" <jmh@.online.nospam> wrote in message
news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> Is there a command or a script that will force all views and Stored Procs
to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.
|||I believe this is only for Stored Procs, not views.
"Marc Mertens" wrote:

> I would use here DBCC FREEPROCCACHE to remove every cached execution plan
> from memory. You must have administrative rights however to execute this.
> Marc
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> to
>
>
|||This looks good, but I need something I can run after updates in Production.
Why don't you recommend using this in production?
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here are some examples, using undocumented stored procedure sp_execresultset
> (do not recommend using it in production) and using a cursor to traverse
> procedures and views and recompile using sp_recompile and refresh views using
> sp_refreshview.
> Example:
> use northwind
> go
> execute sp_execresultset N'
> select
> ''execute sp_recompile '' + quotename(routine_name)
> from
> information_schema.routines
> where
> routine_type = ''procedure''
> and objectproperty(object_id(routine_schema + ''.'' +
> quotename(routine_name)), ''IsMSShipped'') = 0'
> go
> declare @.rn sysname
> declare @.sql nvarchar(4000)
> declare routines_cursor cursor local fast_forward
> for
> select
> routine_name
> from
> information_schema.routines
> where
> routine_type = 'procedure'
> and objectproperty(object_id(routine_schema + '.' +
> quotename(routine_name)), 'IsMSShipped') = 0
> open routines_cursor
> while 1 = 1
> begin
> fetch next from routines_cursor into @.rn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> execute sp_executesql @.sql
> end
> close routines_cursor
> deallocate routines_cursor
> go
> execute sp_execresultset N'
> select
> ''execute sp_refreshview '' + quotename(table_name)
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> ''IsMSShipped'') = 0'
> go
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> declare views_cursor cursor local fast_forward
> for
> select
> table_name
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> open views_cursor
> while 1 = 1
> begin
> fetch next from views_cursor into @.tn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> execute sp_executesql @.sql
> end
> close views_cursor
> deallocate views_cursor
> go
>
> AMB
> "jmhmaine" wrote:
|||> Why don't you recommend using this in production?
I posted an example using sp_execresultset (do not use this one in
production because this sp is not documented in BOL and microsoft can change
it without giving us a notice) and another using a cursor to traverse
routines and views (use these ones).
AMB
"jmhmaine" wrote:
[vbcol=seagreen]
> This looks good, but I need something I can run after updates in Production.
> Why don't you recommend using this in production?
> "Alejandro Mesa" wrote:

Force Recompile on all Views and Stored Procs

Is there a command or a script that will force all views and Stored Procs to
recompile? I'm trying to resolve the issue when views fails because field
order is changed in a Database structure.
I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
but it doesn't recompile until the next call of the stored Proc.Don't think that there is a database or server wide command to do that.
You have to do it in the SP or view level.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
jmhmaine wrote:
> Is there a command or a script that will force all views and Stored Procs
to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||Is there a way to script a loop of all views and Stored Procs instead of
creating a static list?
"Yih-Yoon Lee" wrote:

> Don't think that there is a database or server wide command to do that.
> You have to do it in the SP or view level.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> jmhmaine wrote:
>|||here's something that may be helpful - it will return a resultset that
you can use, i.e., not actually run any drops.
be sure to review the output before you run the output though.
-- creates a script that drops all stored procedures and views.
-- excludes procedures starting w/ dt_ and sys.
begin
declare @.procName sysname
declare @.procType char(2)
declare @.dropProcSql varchar(256)
create table #procNameTbl (procName sysname)
declare procCursor cursor for
select name, type from sysobjects
where type in ('P', 'V') and
substring(name, 1, 3) <> 'dt_' and
substring(name, 1, 3) <> 'sys'
order by name
open procCursor
fetch next from procCursor into @.procName, @.procType
while @.@.fetch_status = 0
begin
if @.procType = 'P'
set @.dropProcSql = 'drop procedure ' + @.procName
else
set @.dropProcSql = 'drop view ' + @.procName
insert into #procNameTbl values (@.dropProcSql)
fetch next from procCursor into @.procName, @.procType
end
close procCursor
deallocate procCursor
select * from #procNameTbl
drop table #procNameTbl
end
go|||Here are some examples, using undocumented stored procedure sp_execresultset
(do not recommend using it in production) and using a cursor to traverse
procedures and views and recompile using sp_recompile and refresh views usin
g
sp_refreshview.
Example:
use northwind
go
execute sp_execresultset N'
select
''execute sp_recompile '' + quotename(routine_name)
from
information_schema.routines
where
routine_type = ''procedure''
and objectproperty(object_id(routine_schema + ''.'' +
quotename(routine_name)), ''IsMSShipped'') = 0'
go
declare @.rn sysname
declare @.sql nvarchar(4000)
declare routines_cursor cursor local fast_forward
for
select
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(routine_schema + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open routines_cursor
while 1 = 1
begin
fetch next from routines_cursor into @.rn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_recompile ' + quotename(@.rn)
execute sp_executesql @.sql
end
close routines_cursor
deallocate routines_cursor
go
execute sp_execresultset N'
select
''execute sp_refreshview '' + quotename(table_name)
from
information_schema.views
where
objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
''IsMSShipped'') = 0'
go
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_name
from
information_schema.views
where
objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.tn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
execute sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
go
AMB
"jmhmaine" wrote:
[vbcol=seagreen]
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" wrote:
>|||I would use here DBCC FREEPROCCACHE to remove every cached execution plan
from memory. You must have administrative rights however to execute this.
Marc
"jmhmaine" <jmh@.online.nospam> wrote in message
news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> Is there a command or a script that will force all views and Stored Procs
to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||I believe this is only for Stored Procs, not views.
"Marc Mertens" wrote:

> I would use here DBCC FREEPROCCACHE to remove every cached execution plan
> from memory. You must have administrative rights however to execute this.
> Marc
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> to
>
>|||This looks good, but I need something I can run after updates in Production.
Why don't you recommend using this in production?
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here are some examples, using undocumented stored procedure sp_execresults
et
> (do not recommend using it in production) and using a cursor to traverse
> procedures and views and recompile using sp_recompile and refresh views us
ing
> sp_refreshview.
> Example:
> use northwind
> go
> execute sp_execresultset N'
> select
> ''execute sp_recompile '' + quotename(routine_name)
> from
> information_schema.routines
> where
> routine_type = ''procedure''
> and objectproperty(object_id(routine_schema + ''.'' +
> quotename(routine_name)), ''IsMSShipped'') = 0'
> go
> declare @.rn sysname
> declare @.sql nvarchar(4000)
> declare routines_cursor cursor local fast_forward
> for
> select
> routine_name
> from
> information_schema.routines
> where
> routine_type = 'procedure'
> and objectproperty(object_id(routine_schema + '.' +
> quotename(routine_name)), 'IsMSShipped') = 0
> open routines_cursor
> while 1 = 1
> begin
> fetch next from routines_cursor into @.rn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> execute sp_executesql @.sql
> end
> close routines_cursor
> deallocate routines_cursor
> go
> execute sp_execresultset N'
> select
> ''execute sp_refreshview '' + quotename(table_name)
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> ''IsMSShipped'') = 0'
> go
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> declare views_cursor cursor local fast_forward
> for
> select
> table_name
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> open views_cursor
> while 1 = 1
> begin
> fetch next from views_cursor into @.tn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> execute sp_executesql @.sql
> end
> close views_cursor
> deallocate views_cursor
> go
>
> AMB
> "jmhmaine" wrote:
>|||> Why don't you recommend using this in production?
I posted an example using sp_execresultset (do not use this one in
production because this sp is not documented in BOL and microsoft can change
it without giving us a notice) and another using a cursor to traverse
routines and views (use these ones).
AMB
"jmhmaine" wrote:
[vbcol=seagreen]
> This looks good, but I need something I can run after updates in Productio
n.
> Why don't you recommend using this in production?
> "Alejandro Mesa" wrote:
>

Force Recompile on all Views and Stored Procs

Is there a command or a script that will force all views and Stored Procs to
recompile? I'm trying to resolve the issue when views fails because field
order is changed in a Database structure.
I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
but it doesn't recompile until the next call of the stored Proc.Don't think that there is a database or server wide command to do that.
You have to do it in the SP or view level.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
jmhmaine wrote:
> Is there a command or a script that will force all views and Stored Procs to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||Is there a way to script a loop of all views and Stored Procs instead of
creating a static list?
"Yih-Yoon Lee" wrote:
> Don't think that there is a database or server wide command to do that.
> You have to do it in the SP or view level.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> jmhmaine wrote:
> > Is there a command or a script that will force all views and Stored Procs to
> > recompile? I'm trying to resolve the issue when views fails because field
> > order is changed in a Database structure.
> >
> > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > but it doesn't recompile until the next call of the stored Proc.
>|||here's something that may be helpful - it will return a resultset that
you can use, i.e., not actually run any drops.
be sure to review the output before you run the output though.
-- creates a script that drops all stored procedures and views.
-- excludes procedures starting w/ dt_ and sys.
begin
declare @.procName sysname
declare @.procType char(2)
declare @.dropProcSql varchar(256)
create table #procNameTbl (procName sysname)
declare procCursor cursor for
select name, type from sysobjects
where type in ('P', 'V') and
substring(name, 1, 3) <> 'dt_' and
substring(name, 1, 3) <> 'sys'
order by name
open procCursor
fetch next from procCursor into @.procName, @.procType
while @.@.fetch_status = 0
begin
if @.procType = 'P'
set @.dropProcSql = 'drop procedure ' + @.procName
else
set @.dropProcSql = 'drop view ' + @.procName
insert into #procNameTbl values (@.dropProcSql)
fetch next from procCursor into @.procName, @.procType
end
close procCursor
deallocate procCursor
select * from #procNameTbl
drop table #procNameTbl
end
go|||Here are some examples, using undocumented stored procedure sp_execresultset
(do not recommend using it in production) and using a cursor to traverse
procedures and views and recompile using sp_recompile and refresh views using
sp_refreshview.
Example:
use northwind
go
execute sp_execresultset N'
select
''execute sp_recompile '' + quotename(routine_name)
from
information_schema.routines
where
routine_type = ''procedure''
and objectproperty(object_id(routine_schema + ''.'' +
quotename(routine_name)), ''IsMSShipped'') = 0'
go
declare @.rn sysname
declare @.sql nvarchar(4000)
declare routines_cursor cursor local fast_forward
for
select
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(routine_schema + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open routines_cursor
while 1 = 1
begin
fetch next from routines_cursor into @.rn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_recompile ' + quotename(@.rn)
execute sp_executesql @.sql
end
close routines_cursor
deallocate routines_cursor
go
execute sp_execresultset N'
select
''execute sp_refreshview '' + quotename(table_name)
from
information_schema.views
where
objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
''IsMSShipped'') = 0'
go
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_name
from
information_schema.views
where
objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.tn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
execute sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
go
AMB
"jmhmaine" wrote:
> Is there a way to script a loop of all views and Stored Procs instead of
> creating a static list?
> "Yih-Yoon Lee" wrote:
> > Don't think that there is a database or server wide command to do that.
> > You have to do it in the SP or view level.
> >
> > Yih-Yoon Lee
> > My blog http://www.mssql-tools.com/blog
> > E-mail: yihyoon.online@.gmail.com
> > /* remove .online to send me e-mail */
> >
> > jmhmaine wrote:
> > > Is there a command or a script that will force all views and Stored Procs to
> > > recompile? I'm trying to resolve the issue when views fails because field
> > > order is changed in a Database structure.
> > >
> > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > but it doesn't recompile until the next call of the stored Proc.
> >|||I would use here DBCC FREEPROCCACHE to remove every cached execution plan
from memory. You must have administrative rights however to execute this.
Marc
"jmhmaine" <jmh@.online.nospam> wrote in message
news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> Is there a command or a script that will force all views and Stored Procs
to
> recompile? I'm trying to resolve the issue when views fails because field
> order is changed in a Database structure.
> I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> but it doesn't recompile until the next call of the stored Proc.|||I believe this is only for Stored Procs, not views.
"Marc Mertens" wrote:
> I would use here DBCC FREEPROCCACHE to remove every cached execution plan
> from memory. You must have administrative rights however to execute this.
> Marc
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:F4E829EA-0D99-47A7-ADAE-063CD3B5FDDE@.microsoft.com...
> > Is there a command or a script that will force all views and Stored Procs
> to
> > recompile? I'm trying to resolve the issue when views fails because field
> > order is changed in a Database structure.
> >
> > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > but it doesn't recompile until the next call of the stored Proc.
>
>|||This looks good, but I need something I can run after updates in Production.
Why don't you recommend using this in production?
"Alejandro Mesa" wrote:
> Here are some examples, using undocumented stored procedure sp_execresultset
> (do not recommend using it in production) and using a cursor to traverse
> procedures and views and recompile using sp_recompile and refresh views using
> sp_refreshview.
> Example:
> use northwind
> go
> execute sp_execresultset N'
> select
> ''execute sp_recompile '' + quotename(routine_name)
> from
> information_schema.routines
> where
> routine_type = ''procedure''
> and objectproperty(object_id(routine_schema + ''.'' +
> quotename(routine_name)), ''IsMSShipped'') = 0'
> go
> declare @.rn sysname
> declare @.sql nvarchar(4000)
> declare routines_cursor cursor local fast_forward
> for
> select
> routine_name
> from
> information_schema.routines
> where
> routine_type = 'procedure'
> and objectproperty(object_id(routine_schema + '.' +
> quotename(routine_name)), 'IsMSShipped') = 0
> open routines_cursor
> while 1 = 1
> begin
> fetch next from routines_cursor into @.rn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> execute sp_executesql @.sql
> end
> close routines_cursor
> deallocate routines_cursor
> go
> execute sp_execresultset N'
> select
> ''execute sp_refreshview '' + quotename(table_name)
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> ''IsMSShipped'') = 0'
> go
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> declare views_cursor cursor local fast_forward
> for
> select
> table_name
> from
> information_schema.views
> where
> objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> open views_cursor
> while 1 = 1
> begin
> fetch next from views_cursor into @.tn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> execute sp_executesql @.sql
> end
> close views_cursor
> deallocate views_cursor
> go
>
> AMB
> "jmhmaine" wrote:
> > Is there a way to script a loop of all views and Stored Procs instead of
> > creating a static list?
> >
> > "Yih-Yoon Lee" wrote:
> >
> > > Don't think that there is a database or server wide command to do that.
> > > You have to do it in the SP or view level.
> > >
> > > Yih-Yoon Lee
> > > My blog http://www.mssql-tools.com/blog
> > > E-mail: yihyoon.online@.gmail.com
> > > /* remove .online to send me e-mail */
> > >
> > > jmhmaine wrote:
> > > > Is there a command or a script that will force all views and Stored Procs to
> > > > recompile? I'm trying to resolve the issue when views fails because field
> > > > order is changed in a Database structure.
> > > >
> > > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > > but it doesn't recompile until the next call of the stored Proc.
> > >|||> Why don't you recommend using this in production?
I posted an example using sp_execresultset (do not use this one in
production because this sp is not documented in BOL and microsoft can change
it without giving us a notice) and another using a cursor to traverse
routines and views (use these ones).
AMB
"jmhmaine" wrote:
> This looks good, but I need something I can run after updates in Production.
> Why don't you recommend using this in production?
> "Alejandro Mesa" wrote:
> > Here are some examples, using undocumented stored procedure sp_execresultset
> > (do not recommend using it in production) and using a cursor to traverse
> > procedures and views and recompile using sp_recompile and refresh views using
> > sp_refreshview.
> >
> > Example:
> >
> > use northwind
> > go
> >
> > execute sp_execresultset N'
> > select
> > ''execute sp_recompile '' + quotename(routine_name)
> > from
> > information_schema.routines
> > where
> > routine_type = ''procedure''
> > and objectproperty(object_id(routine_schema + ''.'' +
> > quotename(routine_name)), ''IsMSShipped'') = 0'
> > go
> >
> > declare @.rn sysname
> > declare @.sql nvarchar(4000)
> > declare routines_cursor cursor local fast_forward
> > for
> > select
> > routine_name
> > from
> > information_schema.routines
> > where
> > routine_type = 'procedure'
> > and objectproperty(object_id(routine_schema + '.' +
> > quotename(routine_name)), 'IsMSShipped') = 0
> >
> > open routines_cursor
> >
> > while 1 = 1
> > begin
> > fetch next from routines_cursor into @.rn
> >
> > if @.@.error <> 0 or @.@.fetch_status <> 0 break
> >
> > set @.sql = N'execute sp_recompile ' + quotename(@.rn)
> >
> > execute sp_executesql @.sql
> > end
> >
> > close routines_cursor
> > deallocate routines_cursor
> > go
> >
> > execute sp_execresultset N'
> > select
> > ''execute sp_refreshview '' + quotename(table_name)
> > from
> > information_schema.views
> > where
> > objectproperty(object_id(table_schema + ''.'' + quotename(table_name)),
> > ''IsMSShipped'') = 0'
> > go
> >
> > declare @.tn sysname
> > declare @.sql nvarchar(4000)
> > declare views_cursor cursor local fast_forward
> > for
> > select
> > table_name
> > from
> > information_schema.views
> > where
> > objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> > 'IsMSShipped') = 0
> >
> > open views_cursor
> >
> > while 1 = 1
> > begin
> > fetch next from views_cursor into @.tn
> >
> > if @.@.error <> 0 or @.@.fetch_status <> 0 break
> >
> > set @.sql = N'execute sp_refreshview ' + quotename(@.tn)
> >
> > execute sp_executesql @.sql
> > end
> >
> > close views_cursor
> > deallocate views_cursor
> > go
> >
> >
> > AMB
> >
> > "jmhmaine" wrote:
> >
> > > Is there a way to script a loop of all views and Stored Procs instead of
> > > creating a static list?
> > >
> > > "Yih-Yoon Lee" wrote:
> > >
> > > > Don't think that there is a database or server wide command to do that.
> > > > You have to do it in the SP or view level.
> > > >
> > > > Yih-Yoon Lee
> > > > My blog http://www.mssql-tools.com/blog
> > > > E-mail: yihyoon.online@.gmail.com
> > > > /* remove .online to send me e-mail */
> > > >
> > > > jmhmaine wrote:
> > > > > Is there a command or a script that will force all views and Stored Procs to
> > > > > recompile? I'm trying to resolve the issue when views fails because field
> > > > > order is changed in a Database structure.
> > > > >
> > > > > I found the "DBCC FLUSHPROCINDB" to erase all Stored Procs from the cache,
> > > > > but it doesn't recompile until the next call of the stored Proc.
> > > >

For XML Problem with IIS6 and W2k3

Did anyone ever resolve this? I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.
"ajsmith02" wrote:

> I have this function that worked like a charm under IIS5 and W2K. You pas
s a
> sql string that has for xml auto or a stored produre that has for xml auto
in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason. No error log either. We applied all the service packs
> including sqlxml sp3. What is wrong? Thanks.
> Here is the code:
> function getSQLXML(byval sqlString)
> dim adoConn
> dim adoCmd
> dim adoStreamQuery
> set adoConn = vbsqlconnection 'located in sharedfunctions.asp
> adoConn.CommandTimeout = 300
> set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> set adoCmd = Server.CreateObject("ADODB.Command")'
> adoCmd.ActiveConnection = adoConn
> adoCmd.CommandTimeout = 300
> adoConn.CursorLocation = adUseClient
> dim sQuery
> sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
> sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
> sQuery = sQuery + "</recordset>"
> adoStreamQuery.Open 'Open the command stream so it may be written to
> adoStreamQuery.WriteText sQuery, adWriteChar 'Set the input command
> stream's text with the query string
> adoStreamQuery.Position = 0 'Reset the position in the stream, otherwis
e
> it will be at EOS
> adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" 'Set the
> dialect for the command stream to be a SQL query.
> adoCmd.CommandStream = adoStreamQuery 'Set the command object's comma
nd
> to the input stream set above
> dim outStrm
> set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
> stream
> outStrm.Open
> adoCmd.Properties("Output Stream").Value = outStrm 'Set command's output
> stream to the output stream just opened
> adoCmd.Execute , , adExecuteStream
> 'Response.Write(outStrm.ReadText)
> adoCmd.ActiveConnection = nothing
> adoConn.Close
> set adoConn = nothing
> getSQLXML = outStrm.ReadText
> end function
> P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the sam
e
> problemNobody helped me. It turned out to be a blessing in disguise. I wound up
creating a component using .Net to pass through the "For XML" sql statements
and return the xml in for of a text stream. After I created the component I
Com Wrapper (using .NET) so that my old asp page to use it.
"ashort" wrote:
> Did anyone ever resolve this? I am having the exact same issue...returnin
g
> results from a FOR XML procedure to an ado stream object stops working eve
ry
> several days.
> "ajsmith02" wrote:
>|||Folks,
Has ANYONE solved this problem? I am getting "Catastrophic failure" message
s and Err.Number = -2147418113 from my ASP pages, consistently.
Thanks in advance...
- Tom

quote:

---

Originally posted by ashort
[B]Did anyone ever resolve this? I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.
"ajsmith02" wrote:
> I have this function that worked like a charm under IIS5 and W2K. You pas
s a
> sql string that has for xml auto or a stored produre that has for xml auto
in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason.

---

F|||Hi,
Could anyone reproduce the problem with a generic ASP page against e.g.
Northwind database? If so, please send me the code if possible. I have the
following ASP page and run it for days on Windows Server 2003 and SQLXML3
SP3, but didn't see it hanging using a stress app.
Also, Windows Server 2003 SP1 can be downloaded, so if someone could give it
a try, it would be great.
<!--#include file="common.inc"-->
<% Response.ContentType = "text/xml" %>
<object id="conn" progid="ADODB.Connection" runat="Server"></object>
<%
Dim strSQL, getSQLXML, strSQLXML, objCmd
dim outStrm
set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
stream
outStrm.Open
strSQL = "<sql:query>select * from orders for xml auto</sql:query>"
strSQLXML = "<?xml version=""1.0"" ?><root
xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & strSQL & "</root>"
conn.Open strConnNew
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = conn
objCmd.CommandText = strSQLXML
objCmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
objCmd.Properties("Output Stream").Value = outStrm
objCmd.Execute , , 1024
outStrm.Position = 0
outStrm.Charset = "utf-8"
getSQLXML = outStrm.ReadText(-1)
outStrm.Close
Response.Write("<root/>")
%>
thx,
-kuen
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomKelleher" wrote:

> Folks,
> Has ANYONE solved this problem? I am getting "Catastrophic failure"
> messages and Err.Number = -2147418113 from my ASP pages, consistently.
>
> Thanks in advance...
> - Tom
> ashort wrote:
> F
>
> --
> TomKelleher
> ---
> Posted via http://www.codecomments.com
> ---
>|||Thanks for the explanation. I am checking with the MDAC team to see if they
are aware of the issue.
Thanks
Michael
<spamgone@.cox.net> wrote in message
news:1114110540.157389.251100@.g14g2000cwa.googlegroups.com...
>I have started experiencing the same problem when my company switched
> to the Windows 2003 servers. Since the switch, our asp web pages that
> contain the adodb.stream objects will randomly lockup until the
> application pool is recycled in IIS. Then everything will run smoothly
> for a few days. All ASP pages that don't contain the adodb.stream
> object will continue to work as normal, when the lockup occurs. No
> errors are generated in the logs and the web server and database server
> doesn't show any signs of heavy memory or CPU usage. You can even
> run the "FOR XML AUTO" stored procedures in query analyzer without
> any errors during this time. To try and prevent the problem we setup
> the application pools to be recycled nightly, but that doesn't appear
> to make any difference. We have Microsoft SQL Server on a different
> machine then our web server, but I doubt that makes a difference. The
> offending ASP pages were working perfectly fine without any issues when
> the server was still Windows 2000 Server.
> Here is the basics of the offending ASP pages:
> <%@. Language=VBScript %>
> <%Option Explicit%>
> <%
> Dim objCommand,objXML,objStream,objRoot
>
> Set objCommand = Server.CreateObject("ADODB.Command")
> Set objXML = Server.CreateObject("MSXML2.DomDocument")
> Set objStream = Server.CreateObject("ADODB.Stream")
>
> objStream.Open
> With objCommand
> .ActiveConnection = "Provider=SQLOLEDB; Data Source=ExampleServer;
> Network Library=DBMSSOCN; Initial Catalog=ExampleDB; User Id=Example;
> Password=Example"
> .CommandType = adCmdStoredProc
> .CommandText = "SP_Example"
> .Properties("Output Stream") = objStream
> .Execute ,, adExecuteStream
> End With
> objXML.loadXML("<root>" & objStream.ReadText & "</root>")
> Set objRoot = objXML.documentElement
> %>
> I have not included the rest of the code that displays the returned
> data because the page does not appear to get past the Execute
> statement.
> The stored procedure is basically:
> CREATE PROCEDURE SP_Example
> AS
> SELECT ExampleID, ExampleName from ExampleTable
> FOR XML AUTO
> GO
> Any suggestions, beyond recoding every web page, would be greatly
> appreciated.
> Thanks.
> Paul
>|||Any result from the MDAC team? I am fighting the same issue, and would be
very interested in a solution or workaround.
"Michael Rys [MSFT]" wrote:

> Thanks for the explanation. I am checking with the MDAC team to see if the
y
> are aware of the issue.
> Thanks
> Michael
> <spamgone@.cox.net> wrote in message
> news:1114110540.157389.251100@.g14g2000cwa.googlegroups.com...
>
>|||MDAC team hasn't seen the problem you describe in our internal tests.
Does this page lockup mean that the thread executing the script hangs?
If so is it possbile to get the stack trace and the version information for
the modules involved?
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hobnob" wrote:
> Any result from the MDAC team? I am fighting the same issue, and would be
> very interested in a solution or workaround.
> "Michael Rys [MSFT]" wrote:
>|||Below I have copied my post from the data.ado group - it contains my ASP cod
e
and the IISState log for the problem thread. I can provide module versions,
if you wouldn't mind telling me which dll's to examine. I did just install
the MDAC 2.8 upgrade, but the behavior was the same before as after. I'm no
t
sure how to provide a stack trace, but I'll see if I can Google something on
it. I'd be happy to provide anything I can to help solve this issue.
//bob
--snip--
Like many others, I am struggling with a problem in Win2K3 and IIS6 using an
ADO Stream object and an SQL FOR XML stored procedure. Let me preface by
saying that the ASP code has worked flawlessly on Win2K and IIS 5 for over a
year. Now on Win2K3, the page will work for a while, then it becomes
unresponsive. It returns no error, it just hangs the connection so that no
other site page will respond until the browser is closed and re-opened.
Sometimes recycling the app pool will clear it up temporarily, sometimes I
have to restart IIS. Page will work fine for a few hours or even days, then
it stops responding. CPU and memory utilization seem normal in TaskMan.
First, here's the ASP code:
<%
dim cn
set cn = server.CreateObject("ADODB.Connection")
cn.open MM_iiWeb_STRING
dim result
result = getSQLXML(cn, "exec proc_getLinkCatXML", "linkpage.xsl", ".")
response.Write result
if cn.state=1 then cn.close()
set cn = nothing
%>
<%
function getSQLXML(byref cn, byval sql, byval xslfile, byval basepath)
dim cmd
dim objOutStream
Set objOutStream = Server.CreateObject("ADODB.Stream")
objOutStream.open
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.Properties("Base Path").Value = Server.MapPath(basepath)
cmd.Properties("XML Root") = "root"
cmd.Properties("XSL") = xslfile
cmd.Properties("Output Stream") = objOutStream
cmd.Execute , , adExecuteStream
getSQLXML = objOutStream.ReadText
objOutStream.Close
set objOutStream = nothing
set cmd = nothing
end function
%>
When it hangs, here is IISState log info for the thread:
Thread ID: 15
System Thread ID: bc0
Kernel Time: 0:0:0.109
User Time: 0:0:0.968
Thread Type: ASP
Executing Page: C:\INETPUB\WWWROOT\IIWEB\TEMPLATES\LINKS
.ASP
# ChildEBP RetAddr
00 025de3d8 77f4262b SharedUserData!SystemCallStub+0x4
01 025de3dc 77e418ea ntdll!NtDelayExecution+0xc
02 025de444 77e416ee kernel32!SleepEx+0x68
03 025de450 0439c4f9 kernel32!Sleep+0xb
04 025de464 04382a20 mo15!CQuery::Cancel+0x50
05 025de49c 0437d84d mo15!CCommand::Cancel+0x58
06 025de4ac 0437516f mo15!CCommand::Term+0xd
07 025de4c8 04373951 mo15!CStdSymbiontObject::InternalRele
ase+0x6c
08 025de4d8 7710736a mo15!ATL::CComObject<CRecordset>::Release+0x11
09 025de4e8 7346384b OLEAUT32!VariantClear+0xad
0a 025de4fc 734641cf vbscript!VAR::Clear+0xab
0b 025de50c 7346416a vbscript!CScriptRuntime::Cleanup+0x59
0c 025de84c 73465184 vbscript!CScriptRuntime::Run+0x2ccc
0d 025de504 80020102 vbscript!CScriptRuntime::Run+0x99
WARNING: Frame IP not in any known module. Following frames may be wrong.
0e 025de504 80020102 0x80020102
0f 00000000 00000000 0x80020102
I have seen the same problem posted several times in various newsgroups and
message boards for the past 8 months or so, but I have seen no explaination,
solution, or workaround offered. I would appreciate any insights.
//bob
--end snip--
"Anton Klimov [MS]" wrote:

> MDAC team hasn't seen the problem you describe in our internal tests.
> Does this page lockup mean that the thread executing the script hangs?
> If so is it possbile to get the stack trace and the version information fo
r
> the modules involved?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||The stack you provided shows a problem with releasing a pointer to a rowset.
However the code fragment you posted does not have any rowsets defined,
unless I'm missing something.
You should see where you might have recordsets created with asynchronous
execution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hobnob" wrote:

> Below I have copied my post from the data.ado group - it contains my ASP c
ode
> and the IISState log for the problem thread. I can provide module version
s,
> if you wouldn't mind telling me which dll's to examine. I did just instal
l
> the MDAC 2.8 upgrade, but the behavior was the same before as after. I'm
not
> sure how to provide a stack trace, but I'll see if I can Google something
on
> it. I'd be happy to provide anything I can to help solve this issue.
> //bob
> --snip--
> Like many others, I am struggling with a problem in Win2K3 and IIS6 using
an
> ADO Stream object and an SQL FOR XML stored procedure. Let me preface by
> saying that the ASP code has worked flawlessly on Win2K and IIS 5 for over
a
> year. Now on Win2K3, the page will work for a while, then it becomes
> unresponsive. It returns no error, it just hangs the connection so that n
o
> other site page will respond until the browser is closed and re-opened.
> Sometimes recycling the app pool will clear it up temporarily, sometimes I
> have to restart IIS. Page will work fine for a few hours or even days, th
en
> it stops responding. CPU and memory utilization seem normal in TaskMan.
> First, here's the ASP code:
> <%
> dim cn
> set cn = server.CreateObject("ADODB.Connection")
> cn.open MM_iiWeb_STRING
> dim result
> result = getSQLXML(cn, "exec proc_getLinkCatXML", "linkpage.xsl", ".")
> response.Write result
> if cn.state=1 then cn.close()
> set cn = nothing
> %>
> <%
> function getSQLXML(byref cn, byval sql, byval xslfile, byval basepath)
> dim cmd
> dim objOutStream
> Set objOutStream = Server.CreateObject("ADODB.Stream")
> objOutStream.open
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cn
> cmd.CommandText = sql
> cmd.Properties("Base Path").Value = Server.MapPath(basepath)
> cmd.Properties("XML Root") = "root"
> cmd.Properties("XSL") = xslfile
> cmd.Properties("Output Stream") = objOutStream
> cmd.Execute , , adExecuteStream
> getSQLXML = objOutStream.ReadText
> objOutStream.Close
> set objOutStream = nothing
> set cmd = nothing
> end function
> %>
> When it hangs, here is IISState log info for the thread:
> Thread ID: 15
> System Thread ID: bc0
> Kernel Time: 0:0:0.109
> User Time: 0:0:0.968
> Thread Type: ASP
> Executing Page: C:\INETPUB\WWWROOT\IIWEB\TEMPLATES\LINKS
.ASP
> # ChildEBP RetAddr
> 00 025de3d8 77f4262b SharedUserData!SystemCallStub+0x4
> 01 025de3dc 77e418ea ntdll!NtDelayExecution+0xc
> 02 025de444 77e416ee kernel32!SleepEx+0x68
> 03 025de450 0439c4f9 kernel32!Sleep+0xb
> 04 025de464 04382a20 mo15!CQuery::Cancel+0x50
> 05 025de49c 0437d84d mo15!CCommand::Cancel+0x58
> 06 025de4ac 0437516f mo15!CCommand::Term+0xd
> 07 025de4c8 04373951 mo15!CStdSymbiontObject::InternalRele
ase+0x6c
> 08 025de4d8 7710736a mo15!ATL::CComObject<CRecordset>::Release+0x11
> 09 025de4e8 7346384b OLEAUT32!VariantClear+0xad
> 0a 025de4fc 734641cf vbscript!VAR::Clear+0xab
> 0b 025de50c 7346416a vbscript!CScriptRuntime::Cleanup+0x59
> 0c 025de84c 73465184 vbscript!CScriptRuntime::Run+0x2ccc
> 0d 025de504 80020102 vbscript!CScriptRuntime::Run+0x99
> WARNING: Frame IP not in any known module. Following frames may be wrong.
> 0e 025de504 80020102 0x80020102
> 0f 00000000 00000000 0x80020102
> I have seen the same problem posted several times in various newsgroups an
d
> message boards for the past 8 months or so, but I have seen no explainatio
n,
> solution, or workaround offered. I would appreciate any insights.
> //bob
> --end snip--
> "Anton Klimov [MS]" wrote:
>
>|||The code I posted is the complete content of the "links.asp" problem page.
This page is called via an ASP Server.Execute command inside another page.
This "parent" page does have 2 database routines of it's own - both use the
ADODB.Connection "Execute" method to run stored procedures that return
recordsets into variables (no recordset objects explicitly created). As far
as I know, the default "Execute" behavior is synchronous, yes? I certainly
am not specifying the asynchronous option. After the recordsets are
returned, I explicitly close and "nothing" the recordsets, then close and
"nothing" the connection object. This code is all supposed to run and
complete before the "links.asp" page gets executed.
Does any of this info raise a red flag?
//bob
"Anton Klimov [MS]" wrote:

> The stack you provided shows a problem with releasing a pointer to a rowse
t.
> However the code fragment you posted does not have any rowsets defined,
> unless I'm missing something.
> You should see where you might have recordsets created with asynchronous
> execution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>