Net start mssqlserver with counters?

Hello,
I need to restart a SQL server every morning. This is done by a local
scheduled task in control panel which runs at .bat file. The contents of the
.bat file is as below:
net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent
The restarting of the services works great...but...the sql performance
counters aren't there anymore. The same thing happens if I stop and start
the services through the control panel. The only way of restarting the
services and still have the performance counters is by restart the services
either by Enterprise Manager or the SQL Server Service Manager. We really
need those counters...
Please give me some ideas!
Regards,
/RogerSQL Server (even non clustered) loses its performance
counters when you stop the SQL server while running
perfmon (or monitor tool that uses perfcounters).
This feature is very badly reported (I 'am experiencing
simmular problems on all SQL Servers).
Stop your performance logging (or the services/tools that
are monitoring your sql server) prior to stopping
sqlserver. Start it up again after sqlserver is started.
Your counters will not dissapear.
Unfortionaly, this is not an option for me. I'm working in
a 365/365 environement and have + 20 SQL servers. I need a
monitor tool to check my servers and can't stop it just
like that).
I think there are some locking issues with perfmon.
I already tried to unload/load SQL server performance
counters (unlodctr/lodctr) but this doesn't help.
Hope this helps.
If sombody finds the cause of this info, please share
knowledge.

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

nested case - is there an simpler way to do this?

QtyPcntCompleted should never exceed 100 but I have no control over
what is in PostedQty or ForecastQty.
So if the calculation exceeds 100 I set it to 100.
What I'm wondering is can the nested CASE be replaced by a function?
QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
(((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
thanks in advance for your feedbackI don't think you need nested CASE statements here
SELECT 'QtyPcntCompleted'=
CASE
WHEN ForecastQty = 0 THEN CAST(0 AS decimal(5,2))
WHEN (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN
CAST(100 AS decimal(5,2))
ELSE CAST((((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) AS
decimal(5,2))
END
All the possible returned values from the CASE construct have to be of the
same data type, so I would cast the 0 and 100 values into decimal(5,2).
Otherwise, a fractional percentage may get truncated or rounded because the
CASE statement selected a datatype of integer.
"Gerard" wrote:

> QtyPcntCompleted should never exceed 100 but I have no control over
> what is in PostedQty or ForecastQty.
> So if the calculation exceeds 100 I set it to 100.
> What I'm wondering is can the nested CASE be replaced by a function?
> --
> QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
> (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
> ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
> --
> thanks in advance for your feedback
>|||Thanks Mark,
much appreciated
Gerard

Need to write value of session variable to SQL record

Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.

Try handling the ItemInserting event of your FormView where you can programmatically set the SqlDataSource.DefaultValue to your Session variable value. For example:

protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
{
SqlDataSource1.InsertParameters["UserID"].DefaultValue = Session["UserID"].ToString();
}|||Worked great with "(" instead of brackets "[". Thanks!|||I'm glad it worked out. My example was in C#. I guess you needed VB sample instead.|||You could of also created a SessionParameter and done it without any code at all.|||

Motley wrote:

You could of also created a SessionParameter and done it without any code at all.

Brilliant!!!