Script Task Hang - Had to Re-Compile

Greetings,

This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.

There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?

Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.

Thanks

Read the first post at the top of this forum.|||

Thanks Phil,

Just wanted to give some details about what I understand was our situation here.

We have had SQL Server 2005 SP2 for several months. This morning there was a big push which included Windows SP2, .NET 2.0, and .NET 3.0. So it doesn't surprise me that I'd have to recompile, but according to the article referenced at the top of this forum, it seems that SQL SP2 should have taken care of this. Possibly it was our unique sequence of updates here.

|||I'm surprised .Net 2.0 wasn't already installed. It should've been, I believe. You might have to reinstall SQL Server SP2 to reap its benefits.

I'm not entirely sure on the whole deal. That sounds like a pretty major update though.|||

Yes I was thinking the same thing, that the DBA must have been mistaken when he told me that. I thought .NET 2 was installed with SQL 2005, a prerequisite. Will probably just wait to see if it happens again, and if so then reinstall as you suggested.

Thanks for for your time on this

Script needed to edit open objects in Sybase

Currently I must manually complete the following commands to set a new value
for the "open objects" and "open indexes". I need a way script this process
in a batch file.

COMMANDS RAN: This is nothing to script, these commands obtain the current
values for open objects and open indexes.

isql -Usa -Pmanager -SCOP1_DS
execute sp_countmetadata "open objects"
execute sp_countmetadata "open indexes"
go

RESULTS: below are the results, there are 5124 user objects and 2122 user
indexes. I need a way to be able to multiple these numbers by a set value
and then use the new value and reset the user objects and user indexes. The
question is how do I isolate the results for each result so I can run the
calculations below. Here is the calculation I need:

current user objects * 1.05 = new value
current user indexes * 1.1 = new value

There are 5124 user objects in all database(s), requiring 6985 Kbytes of
memory.

The 'open objects' configuration parameter is currently set to 500.
(return status = 0)
There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
memory.

The 'open indexes' configuration parameter is currently set to 5000.
(return status = 0)

SETTING THE NEW VALUES: Once I have the new values, I can set the ojects and
indexes as seen below.

1> execute sp_configure "open objects", 5380
2> execute sp_configure "open indexes", 2334
3> goAre you perhaps running Sybase? In that case, you'll probably get a more
help by asking this question in a Sybase forum (e.g. comp.databases.sybase)
rather than one dedicated to Microsoft SQL Server. We Microsoft SQL Server
DBAs are spoiled and don't need to deal with those config options anymore
:-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"News East" <Methu969@.yahoo.com> wrote in message
news:bY7Lf.171258$WH.51367@.dukeread01...
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script this
> process in a batch file.
> COMMANDS RAN: This is nothing to script, these commands obtain the current
> values for open objects and open indexes.
>
> isql -Usa -Pmanager -SCOP1_DS
> execute sp_countmetadata "open objects"
> execute sp_countmetadata "open indexes"
> go
>
>
> RESULTS: below are the results, there are 5124 user objects and 2122 user
> indexes. I need a way to be able to multiple these numbers by a set value
> and then use the new value and reset the user objects and user indexes.
> The question is how do I isolate the results for each result so I can run
> the calculations below. Here is the calculation I need:
> current user objects * 1.05 = new value
> current user indexes * 1.1 = new value
>
> There are 5124 user objects in all database(s), requiring 6985 Kbytes of
> memory.
> The 'open objects' configuration parameter is currently set to 500.
> (return status = 0)
> There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
> memory.
> The 'open indexes' configuration parameter is currently set to 5000.
> (return status = 0)
>
> SETTING THE NEW VALUES: Once I have the new values, I can set the ojects
> and indexes as seen below.
>
> 1> execute sp_configure "open objects", 5380
> 2> execute sp_configure "open indexes", 2334
> 3> go|||News East (Methu969@.yahoo.com) writes:
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script
> this process in a batch file.

I guess that you should be able to read these from syscurconfigs or
sysconfigure. Provided that is, that Sybase has not changed the
location for the configuration parameters since last I saw it, and
that was loooong ago.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Scope_Identity vs manually increment + 1 clarification needed

Hi
I was wondering if somebody could help clarifying the issue of

Scope_Identity vs manually increment + 1 .

Where I work since the days of sql server 7 we use our own way to get the next Identifier.
By having a table with (name and value column) EG CustomerID 234 and when we do an insert we update this Table by incrementing + 1 EG CustomerID 235.

I beleive that this way of doing things is not good and should be replaced by scope_Identity.

The reason I was given was to avoid Locking Tables whilst inserts executes whether in transactional operation or not.

I totally disagree with the above but cannot demostrate it.

My understanding of Scope_Identity is that is the most secure way to get the next Identifier by getting things
that are only in scope (EG A stored Procedure,statements etcc)

Now my question is:
Does scope_Indentity performs any locks of some sort ,or slows the process down?

Could somebody give me a example why incrementing by 1 manually is inefficient or what problem it can cause.

Thanks a lot in advance

No there is no lock for IDENTITY columns.

There are lot of points there to say manyally +1 is inefficeint.

1. You have to lock the record - identity never use any lock

2. You have to fetch the last value from your table - identity uses the LOG no query

3. You have to manullay increment the value - idenity does the same

4. You have to wirte the code manually on triggers - identity is internal opertion, you need not to write any code

5. Fetching the last inserted value , again a overhead to write a few line of code - idenity values can be fetched easily

Last but not least Idenity never cause any performance issue, but it may cause GAPs on your sequential value.

Want to know more see this article.. http://blogs.msdn.com/sqlprogrammability/archive/2006/04/04/567724.aspx

|||

Fantastic reply.Thanks!!!. If there are anymore points please add it here. I will wait a bit in case somebody else adds something else and then pack in a email and send it to my collegues who were not conviced.

Thanks again for such a clean answer