ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

Foreach variable cannot be applied

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.

The SQL Task that is linked to the Foreach container is somthing like this:

SELECT str1, str2, str2, nValue1, nValue2

FROM MyTable

In the SQL Task I've set the following

ResultSet = Full Result Set

Connection Type = OLE DB

Result Name = 0

I've created the following Variables with a Package scope

s1 as String

s2 as String

s3 as String

n1 as Double

n2 as Double

When I run the package I get the following errors

Foreach variable mapping number 4 to variable "User : : n1" cannot be applied

Foreach variable mapping number 5 to variable "User : : n2" cannot be applied

The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)

In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.

Any insight would be greatly appreciated.

Regards,

Bill

See if this blog post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Thanks for the pointer, I found that if I cast the numeric fields in my query to float that the mapping seemed to pass the variables ok.

Regards,

Bill

ForEach Loop sequence question

Hi,

This is related to an earlier post.

I have a ForEach loop that contains 3 script tasks in it.

The script tasks are connected by precedence constraints, as in:

script1 --> script2 --> script3

So they should execute in order.

When I run the debugger however, I see that script3 turns green before script2. It is a little disconcerting, however it seems to working correctly. Script3 can't even do what it's supposed to do until script1 and script2 are finished.

But as I've said, it's working. So why does script3 appear to finish before script2 is done?

Thanks

Double click on the precedence constraint between script2 and script3. What are the settings? You don't have any other precedence constraints going into script3 do you? (From any other tasks?)|||

No, there are no other precedence constraints to script3.

The precedence constraints are set to "success".

But script1 and script2 create files that script3 compares. The compare part is working fine, and it shows that it's running the diff on the files created in the previous 2 scripts. It can't do that unless the files exist first.

|||I understand the scenario perfectly.

Script3 can't possibly execute unless 1 and 2 have finished "successfully." That doesn't mean that 1 and 2 did what they were supposed to do though.|||My understanding (subject to correction by someone better informed) is that the IDE changes the colors based on receiving events. Events are not guaranteed to be received in the order that they occur. Of course, I could be completely wrong about this |||

jwelch wrote:

My understanding (subject to correction by someone better informed) is that the IDE changes the colors based on receiving events. Events are not guaranteed to be received in the order that they occur. Of course, I could be completely wrong about this

Something along those lines, K108, did you copy n paste the script tasks?|||I have seen many cases where the coloring of the tasks in BIDS (debug mode) does not behave in a logic order; but later while reviewing the execution progress and results, everything looks OK. This seems to occur more when there is a high number of tasks/rows in the packages. My bottom line: if the results and logs are correct; it is nothing to be concerned about.|||

K108 wrote:

Hi,

This is related to an earlier post.

I have a ForEach loop that contains 3 script tasks in it.

The script tasks are connected by precedence constraints, as in:

script1 --> script2 --> script3

So they should execute in order.

When I run the debugger however, I see that script3 turns green before script2. It is a little disconcerting, however it seems to working correctly. Script3 can't even do what it's supposed to do until script1 and script2 are finished.

But as I've said, it's working. So why does script3 appear to finish before script2 is done?

Thanks

I wouldn't worry about it. The tasks changig colours is based on the receiving of events from the execution engine. There could be any number of reasons - possibly that the reason script2 is not green is because its already executing again on the next iteration. It could be that the UI simply can't keep up with the execution engine. Who knows. Bottom line is (as everyone else has said) its really nothing to worry about.

-Jamie

|||

Thanks for the input.

I won't worry about it, as it working.

sql

ForEach Loop Container

I am using a SQL Task to load the data needed for the loop into a variable, then using a script task to perform calculations. Is it possible to update a table from the script task with the calculated values?.

Thanks
You can use System.Data and ADO.Net inside a script task. You could even use an ADO.Net connection manager. Just write some normal VB.Net code to do your data access.

I don't know what you are doing, but I would question this method. Why not do it all in a single SQL Task? Why use a SQL task to do the update? Why not do it all in a script task?|||I want to test the performance difference between a cusor in sql 2000 and the loop in sql2005.|||

I wouldn't recommend that as a use of SSIS... instead, consider revisiting what you do with the cursors to be done using the data flow task.

regards,
ash

|||Let me explain what I am trying to do. I have +/- 600 performance reports to calculate each with 40 odd items. If I'm on the wrong path please point me in the right direction. I would appreciate any help.|||

Do all performance reports have the same structure just different data? Are all the calculations identical as well?

I'm intruiged by the problem and can work with you on how to do this in most efficient manner, we can then let the group know what we found.

regards,
ash

|||

Yes the structure for all reports are the same, the calculations are dependant on the specific measure.

May I contact you offline and not involve the forum until a suggestion is ready

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
Paul G
Software engineer.
Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name =
'mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.
|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:

> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name =
> 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
>
>

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
Paul G
Software engineer.Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name =
'mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints
,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:

> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name
=
> 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
>
>

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
--
Paul G
Software engineer.Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name ='mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:
> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name => 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> > Hi just wondering if there is a way to have a script that creates a table
> > write in a desired string for the first table entry?
> > I have an application that I am allowing the de-activation of a record. I
> > could not allow a delete since this could result in an orphan record. So
> > basically the table at startup (when first created) would look like
> > ****************************
> > * pri key * varchar (20)*
> > ****************************
> > * 1 * Not Active *
> > ****************************
> > the application would add records to the table as it is used.
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>

forcing a truncate

I have a script that dumps data from several tables into one, where the
source tables are not always the same data length. They are all varchar but
some are 30 chars, some 255, etc. Below is my insert query, which errors
because it won't truncate the 255 character data into 30. Is there a simple
way to automatically truncate that data that doesn't fit? All source tables
are different, so I don't want to have to go through 10 or more fields to
determine what their length is.
insert into tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select r_firstname, r_lastname, r_address, r_organization, r_city,
r_state,
r_zip, r_phone_h, r_phone_o,
r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
where contactid is null and ...[query truncated])
Thanks for your help.dew,
The problem is not so much the source tables, but the destination table.
Your SELECT statement indicates one source to one destination table and so
it's (reasonably) simple.
The simple answer is to use the LEFT function as such::
INSERT INTO tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select LEFT(r_firstname, 30), LEFT(r_lastname, 30), LEFT(r_address, 30),
LEFT(r_organization, 30), LEFT(r_city, 30), LEFT(r_state, 30), LEFT(r_zip,
30), LEFT(r_phone_h, 30), LEFT(r_phone_o, 30), LEFT(r_phone_m, 30),
LEFT(r_phone_f, 30), LEFT(r_email, 30), LEFT(Username, 30), LEFT(change_date
,
30)
FROM SourceTable1
WHERE contactid IS NULL
AND ...[query truncated])
This will work in the main, but, of course, the LEFT unstion only needs to
be used on those columns that are obviously (or likely) to have in excess of
30 characters at the source table.
Obviously, the other way is simply to alter the destination table(s) to
accomodate the larger size.
Hope this assists,
Tony
"dew" wrote:

> I have a script that dumps data from several tables into one, where the
> source tables are not always the same data length. They are all varchar b
ut
> some are 30 chars, some 255, etc. Below is my insert query, which errors
> because it won't truncate the 255 character data into 30. Is there a simp
le
> way to automatically truncate that data that doesn't fit? All source tabl
es
> are different, so I don't want to have to go through 10 or more fields to
> determine what their length is.
> insert into tblcontact (firstname, lastname, streetaddress,
> organizationname, city, statecode,
> postalcode, homephone, businessphone,
> mobilephone, faxnumber, emailaddress, username,
> datechanged)
> (select r_firstname, r_lastname, r_address, r_organization, r_city,
> r_state,
> r_zip, r_phone_h, r_phone_o,
> r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
> where contactid is null and ...[query truncated])
> Thanks for your help.
>
>

ForceExecutionResult is ignored by parent

1 Add a Sequence Container.
2 Add a Script Task to the contianer, and set the script to fail, Dts.TaskResult = Dts.Results.Failure
3 Set the ForceExecutionResult on the Script to Success.
4 Execute the package.

The container fails, so it detects the Dts.Results.Failure, and ignores ForceExecutionResult. The task itself is Green, so that indicates success. I think this is wrong, as ForceExecutionResult should be observed by both the task and parent container. Not that it should matter, I think, FailParentOnFailure is false. By design? (Sep CTP)
Ok, so there is a simpler issue here. The sequence container takes no notice of the ForceExecutionResult. Try a container with a simple task that always succeeds. Set ForceExecutionResult on the container to Failure, and the container still succeeds (goes green).|||

Containers don't fail because their children fail. Containers fail because one of their settings (MaxErrorCount, FailParentOnFailure, etc) mandates it to fail.

The task isn't raising an error. So MaxErrorCount doesn't take effect. A failed task doesn't fail the parent by default. Neither is FailParentOnFailure set to true.

Here, like the Disable/Enable, is a brute force method of making something happen. You've made the conceptual assumption that because tasks fail when they have errors, if the task is forced to look like it failed, the parent should behave the same as a task that failed because it had errors.

The feature does what it says and no more. It forces the return value to false. Nothing more. It effects only those features that key on the return value. Precedence constraints are effected by the execution result, failing the parent is not. Neither is there an error raised.

If you set fail parent or fail package on failure, it will fail the sequence.

If you return an error from the script task, it will fail the parent if MaxErrors is set to 1.

HTH,
K

Force users/connections to disconnect from db

Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
DakkiOriginally posted by Dakki
Hello,

I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.

Thanks
Dakki

sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter|||Originally posted by peterdbd
sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter

Excellent, I shall try this...

many thanks

dakki|||peterdbd,

i don't think it's an accurate statement, because you'll get this error if there is at least 1 connection open for the database you're trying to detach, even if this connection has not performed a single operation:

Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'db_name' because it is currently in use.|||guru, you are right, I had to re run the script, since some of the db's had connections to it...

thanks again, to you both ...|||list all users with sp_who2
and find out sid of all the db u r looking at

using sa , kill all sid

eg:- kill sid

then proceed with detach.

Note:-
Do not any sid doing updates...|||Take help from this link (http://www.sql-server-performance.com/q&a37.asp) which includes the script to kill are users that are connected to a database that needs to be dropped/detached etc. etc.:cool:|||there are 3 problems with the script:

- based on stored procedure. i wouldn't recommend leaving such tool so handy

- based on cursor, - simply no need

- does not take into account the possibility of attempting to kill yourself (not that you'll succeed though)

there is a simpler way:

declare @.cmd varchar(100)
while (select count(*)
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'your_db_name') > 0 begin
set @.cmd = 'kill ' +(select cast(min(spid) as varchar(25))
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'mci2k')
exec ( @.cmd )
end

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.
> > > >

Force invalidate snapshot - how?

Hi,

In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:

Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.

I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();

I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.

I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.

I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.

Thanks for any assistance you might be able to provide :-)

Paul

More -

I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.

|||

I found it:

// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}

Force invalidate snapshot - how?

Hi,

In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:

Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.

I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();

I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.

I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.

I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.

Thanks for any assistance you might be able to provide :-)

Paul

More -

I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.

|||

I found it:

// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
MichelleThis script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
> if @.@.error = 0 but I want to make sure that it works as intended should DBCC
> CHECKDB FIND errors. How can I force a database to become corrupt to a point
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
> > Hi,
> >
> > I need to test a script to ensure that it behaves appropriately after
> > running DBCC CHECKDB and finding 'errors'. I have an if statement that
runs
> > if @.@.error = 0 but I want to make sure that it works as intended should
DBCC
> > CHECKDB FIND errors. How can I force a database to become corrupt to a
point
> > that DBCC CHECKDB will report errors?
> >
> > Thanks,
> >
> > Michelle
> >
> >

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
Michelle
This script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
> if @.@.error = 0 but I want to make sure that it works as intended should DBCC
> CHECKDB FIND errors. How can I force a database to become corrupt to a point
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>
|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
runs[vbcol=seagreen]
DBCC[vbcol=seagreen]
point[vbcol=seagreen]

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
MichelleThis script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that run
s
> if @.@.error = 0 but I want to make sure that it works as intended should DB
CC
> CHECKDB FIND errors. How can I force a database to become corrupt to a poi
nt
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
runs[vbcol=seagreen]
DBCC[vbcol=seagreen]
point[vbcol=seagreen]

For/Foreach loop

Hi,

I'm looping through some query data and doing a Script Task check inside the loop on a datetime field.

If the datetime happens before 10 o'clock:

- Store the data row in Table1

If the datetime happens after 10 o'clock:

- Store the data row in Table2

How can I store the datarow inside the loop without inserting it into a database table?

I need to access the data in the next step after the loop. How can I do this?

Thank you very much!

Can you store the values in some variables?

|||

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Am I misunderstanding you perhaps?

|||If you have a single source connection for the data and you want to split the rows based on the value of a certain column, used a conditional split, as per previous post, and then write the rows to a raw file for later use.

Other option would be to generate the rows based on the conditions and then write them to variables, as per Jamie's option, and use them later.

Depending on the number of rows and different conditions you have, either option would work. Variable route may give performance / resource problems on large numbers though.|||

MrHat wrote:

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Yes it would. Is that a problem?

MrHat wrote:

Am I misunderstanding you perhaps?

I don't think so

-Jamie

FOR XML in a .vbs script

Is it posible to query a database and use FOR XML in the query?
At the moment I convert all records from the ADODB.Recordset to a string
that can be handler as XML. In .ASP pages you can use the Responce
stream, but how do I handle this in a .VBS script ?
It's so much easier if the database generates XML, and not my own code.
Tnx,
Rob
You can generate XML from the database by having FOR XML in your queries. If
you are using ADO, you can use the stream object to get the result back and
then load it into a DOM document.
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Rob Moerland" <rob.moerland@.NOhetnet.SPAMnl> wrote in message
news:cdave6$ks6$1@.reader11.wxs.nl...
> Is it posible to query a database and use FOR XML in the query?
> At the moment I convert all records from the ADODB.Recordset to a string
> that can be handler as XML. In .ASP pages you can use the Responce
> stream, but how do I handle this in a .VBS script ?
> It's so much easier if the database generates XML, and not my own code.
> Tnx,
> Rob
|||Hi
Check out:
http://sqlxml.org/faqs.aspx?faq=10
John
"Rob Moerland" <rob.moerland@.NOhetnet.SPAMnl> wrote in message
news:cdave6$ks6$1@.reader11.wxs.nl...
> Is it posible to query a database and use FOR XML in the query?
> At the moment I convert all records from the ADODB.Recordset to a string
> that can be handler as XML. In .ASP pages you can use the Responce
> stream, but how do I handle this in a .VBS script ?
> It's so much easier if the database generates XML, and not my own code.
> Tnx,
> Rob