Prediction Accuracy

hi,

I am using time series agorithm.I need standard deviation in %. I am using SELECT StudID, PREDICTSTDEV([Perf]) FROM [Stud_Model].This one is giving me the standard deviation like this

StudID stDev

001 2.891298978779

002 2.797288978779.

But I need like this

StudID stDev

001 +50%

002 +51%(From The Previous) like that.

Is it Possible.

Thanks,

Karthik.

To get the standard deviation as a percentage, you just need to get the predicted value and divide e.g.

PredictStdev([Perf])/Predict([Perf]) // of course Predict(Perf) could be 0.

However, I'm not sure what you meant by "From the Previous", though

Prediction Accuracy

hi,

I am using time series agorithm.I need standard deviation in %. I am using SELECT StudID, PREDICTSTDEV([Perf]) FROM [Stud_Model].This one is giving me the standard deviation like this

StudID stDev

001 2.891298978779

002 2.797288978779.

But I need like this

StudID stDev

001 +50%

002 +51%(From The Previous) like that.

Is it Possible.

Thanks,

Karthik.

To get the standard deviation as a percentage, you just need to get the predicted value and divide e.g.

PredictStdev([Perf])/Predict([Perf]) // of course Predict(Perf) could be 0.

However, I'm not sure what you meant by "From the Previous", though

sql

Prediction Accuracy

Hi ,

I am a novice Data Mining Programmer.

I am using Time series algorithm for forecasting.

We are Quite concerned about the accuracy of Prediction output.

For Example Our Data is like this

StudIdDatePerf

00101/01/200190

00102/01/200189

00103/01/200187

00201/01/200159

00202/01/200170

00303/01/200147

If I write my Prediction Query to predict for 100 th time step.Its giving me out put like

DatePerf

03/01/201547.000000115

We are not sure about the accuracy of the values. Is it possible to use trend information as input to my model and make my prediction based on that.

I don’t know how to do that? Can anyone help?

Thanks,

Karthik.

The time series algorithm in SQL Server 2005 - ARTxp is designed for near term prediction accuracy, not far term - e.g. 100 steps. You can get details on the research behind the algorithm at http://research.microsoft.com/~dmax/publications/dmart-final.pdf

Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #PlayerWin110011,2501100250210011,4502100275310011,60031002100410012,00041002175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #

Player

Win

1

1001

1,250

1

1002

50

2

1001

1,450

2

1002

75

3

1001

1,600

3

1002

100

4

1001

2,000

4

1002

175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

predict products ( data mining 2000)

i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).

how i do in data mining to predict that products ?

more: i want know how much percent of product is like by buyer

or i want show products with desc % of the like of people

You can use Microsoft Association Rules to resolve this problem: you can include the time info (such as month or summer) in each trasaction, our Assocation Rules will do the counting and find any rules apply. Please check the live sample http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/54.aspx on about the train Association Rules. You might also want to check out the tips and tricks on our data mining web site http://www.sqlserverdatamining.com. SQL Server books online is another resouce you can use to learn about SQL Server in general and SQL Server Data Mining.

Besides time info, you can also include basket info (other products the user has already chosen) and the user's demographic info(such as Geneder, Income --if you have those info) in your model.

DMX is the langue you can use to do prediction. The function PredictHistogram(Product) will given the list of products and their probability when you run queries against your model.

Good luck,

|||

For SQL Server 2000 you would use Microsoft_Decision_Trees. You will build a model based on your customer/shopping basket table using the wizard. Since 2000 doesn't support creating nested tables in the wizard, you then need to add the nested table from your transaction table in the data mining editor.

Your model will look something like this

CREATE MINING MODEL MyModel
(
BasketID LONG KEY,
Season TEXT DISCRETE
Products TABLE PREDICT
(
ProductName TEXT KEY
)
) USING Microsoft_Decision_Tree

If you have a large number of products (which you likely do) you will have to set the MAXIMUM_INPUT_ATTRIBUTES and MAXIMUM_OUTPUT_ATTRIBUTES on the model as well, otherwise the algorithm will perform feature selection to the default 255.

|||Thanks.sql

predict products ( data mining 2000)

i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).

how i do in data mining to predict that products ?

more: i want know how much percent of product is like by buyer

or i want show products with desc % of the like of people

You can use Microsoft Association Rules to resolve this problem: you can include the time info (such as month or summer) in each trasaction, our Assocation Rules will do the counting and find any rules apply. Please check the live sample http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/54.aspx on about the train Association Rules. You might also want to check out the tips and tricks on our data mining web site http://www.sqlserverdatamining.com. SQL Server books online is another resouce you can use to learn about SQL Server in general and SQL Server Data Mining.

Besides time info, you can also include basket info (other products the user has already chosen) and the user's demographic info(such as Geneder, Income --if you have those info) in your model.

DMX is the langue you can use to do prediction. The function PredictHistogram(Product) will given the list of products and their probability when you run queries against your model.

Good luck,

|||

For SQL Server 2000 you would use Microsoft_Decision_Trees. You will build a model based on your customer/shopping basket table using the wizard. Since 2000 doesn't support creating nested tables in the wizard, you then need to add the nested table from your transaction table in the data mining editor.

Your model will look something like this

CREATE MINING MODEL MyModel
(
BasketID LONG KEY,
Season TEXT DISCRETE
Products TABLE PREDICT
(
ProductName TEXT KEY
)
) USING Microsoft_Decision_Tree

If you have a large number of products (which you likely do) you will have to set the MAXIMUM_INPUT_ATTRIBUTES and MAXIMUM_OUTPUT_ATTRIBUTES on the model as well, otherwise the algorithm will perform feature selection to the default 255.

|||Thanks.

Precompiling checking problem

i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ

Postback takes a LONG time!

I have one other issue, possibly related to my last post, that I thought I
should post in a separate question.
When I specify a default value for a parameter using an expression, the form
postback process seems to get scrambled. If I enter a value in a text
parameter, then click "View Report", the form will do a postback, but NOT run
the query. If I click View Report again, the report runs fine.
This delayed postback occurs on any text field parameter, and can be
observed by entering text in a parameter field, then clicking in another
parameter field. This effect is very disconcerting.
As in my other question, I'm thinking this is a RS bug, but is there a
workaround?
Thanks in advance,
MikeThe server can not determine the dependencies between parameters when some
of them are expressions, so it is conservative and assumes that a dependency
exists. When you navigate away from the text box (to another text box or to
the view report button), the page posts back to get new defaults and valid
values for downstream parameters. If there is no dependency, you can
reorder the parameters to prevent the post back. A parmeter can only depend
on parameters that came before it. So if you make your expression based
parameter the last one, there will be no auto post back.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"rdcpro" <rdcpro@.discussions.microsoft.com> wrote in message
news:691D2719-81E7-46B8-9F26-E3515DD8CDB2@.microsoft.com...
> I have one other issue, possibly related to my last post, that I thought I
> should post in a separate question.
> When I specify a default value for a parameter using an expression, the
form
> postback process seems to get scrambled. If I enter a value in a text
> parameter, then click "View Report", the form will do a postback, but NOT
run
> the query. If I click View Report again, the report runs fine.
> This delayed postback occurs on any text field parameter, and can be
> observed by entering text in a parameter field, then clicking in another
> parameter field. This effect is very disconcerting.
> As in my other question, I'm thinking this is a RS bug, but is there a
> workaround?
> Thanks in advance,
> Mike|||Thanks Brian,
This particular control is the last parameter, but there is also a select
box that is the second-to-last parameter. This one uses a default value, but
not an expression. In particular, it has a set of values that look like
Label Value
foo 0
bar 1
snafu 2
and the default value is specified as:
0
and not
=0
In this case, a postback shouldn't occur, because this is not an expression
but a scalar value, right?
And even if it were an expression, while the server doesn't know if there is
a dependancy, the Designer (me) knows. It seems to me that when I design a
report with parameters that use expressions, I should have a checkbox to
disable postback or declaratively state there is no dependancy.
Any thoughts?
Thanks again,
Mike
"Brian Hartman [MSFT]" wrote:
> The server can not determine the dependencies between parameters when some
> of them are expressions, so it is conservative and assumes that a dependency
> exists. When you navigate away from the text box (to another text box or to
> the view report button), the page posts back to get new defaults and valid
> values for downstream parameters. If there is no dependency, you can
> reorder the parameters to prevent the post back. A parmeter can only depend
> on parameters that came before it. So if you make your expression based
> parameter the last one, there will be no auto post back.
>|||In case others run into this issue, I wanted to post my resolution.
At issue is the fact that a report parameter with an expression is assumed
to be dependant on another report parameter, even when in fact it's only
dependant on a global value or even if it simply uses an expression to return
a scalar result. So, when you set a default value using an expression, the
control is "disabled" and there is no way for the user to physically set the
control without first entering a fake value in another field, then causing a
postback to "refresh" the parameter. Only then can the user set the value
for the parameter using an expression. Of course, before the user runs the
query he/she must remove the "fake" value.
In my case, a user might enter a date in my "End Date" field, then click
"View Report". Instead of actually seeing the report, the screen blanks, a
postback occurs, and it appears to the user that either nothing happened, or
the report returned no results. They have to click "View Report" again to
get the report to run.
Evidently, the behavior is "by design" inasmuch as RS isn't smart enough to
realize there is no dependancy for this parameter, and there is no way in the
report designer to declaratively state as much.
In my case, I simply wanted to set the default value of the parameter to
"true" in one folder, and "false" in another. My solution was to use the
same approach that creating a linked report would use. Upload the report
with no default value on the control, then in the Report Manager, manually
specify the default value I want (either true or false) in the Properties >
Parameters page.
It would be really nice if there was a similar option in the Report
Designer, or if you could set the default to a scalar value directly the same
way you can for a drop-down parameter!
Hope this helps someone,
Mike
"rdcpro" wrote:
> Thanks Brian,
> This particular control is the last parameter, but there is also a select
> box that is the second-to-last parameter. This one uses a default value, but
> not an expression. In particular, it has a set of values that look like
> Label Value
> foo 0
> bar 1
> snafu 2
> and the default value is specified as:
> 0
> and not
> =0
> In this case, a postback shouldn't occur, because this is not an expression
> but a scalar value, right?
> And even if it were an expression, while the server doesn't know if there is
> a dependancy, the Designer (me) knows. It seems to me that when I design a
> report with parameters that use expressions, I should have a checkbox to
> disable postback or declaratively state there is no dependancy.
> Any thoughts?
> Thanks again,
> Mike
> "Brian Hartman [MSFT]" wrote:
> > The server can not determine the dependencies between parameters when some
> > of them are expressions, so it is conservative and assumes that a dependency
> > exists. When you navigate away from the text box (to another text box or to
> > the view report button), the page posts back to get new defaults and valid
> > values for downstream parameters. If there is no dependency, you can
> > reorder the parameters to prevent the post back. A parmeter can only depend
> > on parameters that came before it. So if you make your expression based
> > parameter the last one, there will be no auto post back.
> >
>

POST SQL CLUSTER NODE REPLACEMENCT..Not so Good

I had a hard time with my node replacement. And I am hoping to find out what
I did wrong and perhaps some answers to a few lingering questions, I want to
step through what I did
1. I was using a whole new name and IP address for the new node, so I place
it on the network the day before and attached it to the SAN, But did not
assign it to the storage group or attach LUNS or Any cluster environment.
The next day I first removed SQL from the Second node, It came up with thte
window that lets me pick the Node, However when I did this it removed my
Virtual SQL Cluster name and IP, which worried me tremendoulsy, is this
Normal?, then I evicted it from the cluster and it seemed okay.
I then turned off Node 1 and added the new Node to the storage group, added
all the appropriate SAN software, rebooted and when my Node 2 came up
assigned the same drive letters to the shared storage as on the first node,
then off the second node and restarted the first. waited for my drives to
appear Okay, then started the second node.
I had all my IP'S and Heartbeat configured and everything came up fine.
Then I added the new node 2 to the MS cluster and it went well,
Now I tried to load SQL and I was sure it wouldn't become part of any SQL
CLuster because it appeared it didn't exit anymore. I readded the SQL name
and IP to the cluster resources and went ahead and added it, But It didn't
seem to work right, Every time I fail over, It works and I can use the SQL
Virtual IP, But I have to re-attach the DB's every time I fail over.
I am not sure if I missed a step, Or if Node one should have been off when I
removed SQL from Node two, One thing I am questioning is whether the SQL
services hsould be running on both Nodes, even when one is passive
Can anyone pinpoint my issue or help me with Post-game analysis
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
It sounds like the uninstall didn't go quite right. It should have removed
SQL from the second node but not removed any resources from the SQL group.
After that, it was pretty much hosed.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:ABBD721F-1B05-4902-8C69-E22BA1A24408@.microsoft.com...
>I had a hard time with my node replacement. And I am hoping to find out
>what
> I did wrong and perhaps some answers to a few lingering questions, I want
> to
> step through what I did
> 1. I was using a whole new name and IP address for the new node, so I
> place
> it on the network the day before and attached it to the SAN, But did not
> assign it to the storage group or attach LUNS or Any cluster environment.
> The next day I first removed SQL from the Second node, It came up with
> thte
> window that lets me pick the Node, However when I did this it removed my
> Virtual SQL Cluster name and IP, which worried me tremendoulsy, is this
> Normal?, then I evicted it from the cluster and it seemed okay.
> I then turned off Node 1 and added the new Node to the storage group,
> added
> all the appropriate SAN software, rebooted and when my Node 2 came up
> assigned the same drive letters to the shared storage as on the first
> node,
> then off the second node and restarted the first. waited for my drives to
> appear Okay, then started the second node.
> I had all my IP'S and Heartbeat configured and everything came up fine.
> Then I added the new node 2 to the MS cluster and it went well,
> Now I tried to load SQL and I was sure it wouldn't become part of any SQL
> CLuster because it appeared it didn't exit anymore. I readded the SQL name
> and IP to the cluster resources and went ahead and added it, But It didn't
> seem to work right, Every time I fail over, It works and I can use the SQL
> Virtual IP, But I have to re-attach the DB's every time I fail over.
> I am not sure if I missed a step, Or if Node one should have been off when
> I
> removed SQL from Node two, One thing I am questioning is whether the SQL
> services hsould be running on both Nodes, even when one is passive
> Can anyone pinpoint my issue or help me with Post-game analysis
>
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
|||Hey Geoff
Yeah, That is where I had my first indication I was going ot have trouble, I
was able to readd the SQL Clsuter Virtual Resources man ually and I have good
failover and the databases are attached and everything seems to be working
good.
I have actually two lingering Questions, My SQl services are running and on
"automatic" on Both nodes. Now when I failed over after the original set up,
I kept having to 'Attach" the databases manually. I thought this was due to
the fact that My services aren't actually part of the SQL Cluster, tried to
add them as generic sercives and it tells me It Can't because they are not
installed services, SO I haven't tried to fail them over again, so Tuesday
Night I will see what happens.
Also, I may have some DC trouble that I was unaware of, I have a remotes on
down in Mesa, for my production side and My PDC is here In Scottsdale, when I
attached the database to my new Node, and tried to add users SQL said "Cannot
determine if TMPEMWDPROD003 is part of the domain, That was aprox 24 hours
after I had installed the server and added it to the domain, I believe
Yesterday that had cleared up. I may have to reconfigure My DC's, all this
was done before I started working here.
Let me ask this, Should I go into SQL before doing anything and detach all
databases before unistalling SQL, even though they were attached to the Node
I was NOT replacing?
And thanks again Geoff
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:

> It sounds like the uninstall didn't go quite right. It should have removed
> SQL from the second node but not removed any resources from the SQL group.
> After that, it was pretty much hosed.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:ABBD721F-1B05-4902-8C69-E22BA1A24408@.microsoft.com...
>
|||You have a split-brain SQL install. In a cluster, the services should be
MANUAL and only started up on the currently active node by the cluster
service. You may need to detach the databases, uninstall SQL completely,
and reinstall the SQL clustered instance to get everything back the way it
is supposed to be.
Normally when you kick a node out, you don't have to detach anything. The
installer should simply remove the node from the possible owners of the
resource group and fix the internal SQL configuration. This is where
practicing on a virtual installation really pays off.
And yes, the DC problems may have contributed to the issue.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:2581DDE3-C54D-4A65-B0F4-B5B8EA07BC86@.microsoft.com...[vbcol=seagreen]
> Hey Geoff
> Yeah, That is where I had my first indication I was going ot have trouble,
> I
> was able to readd the SQL Clsuter Virtual Resources man ually and I have
> good
> failover and the databases are attached and everything seems to be working
> good.
> I have actually two lingering Questions, My SQl services are running and
> on
> "automatic" on Both nodes. Now when I failed over after the original set
> up,
> I kept having to 'Attach" the databases manually. I thought this was due
> to
> the fact that My services aren't actually part of the SQL Cluster, tried
> to
> add them as generic sercives and it tells me It Can't because they are not
> installed services, SO I haven't tried to fail them over again, so Tuesday
> Night I will see what happens.
> Also, I may have some DC trouble that I was unaware of, I have a remotes
> on
> down in Mesa, for my production side and My PDC is here In Scottsdale,
> when I
> attached the database to my new Node, and tried to add users SQL said
> "Cannot
> determine if TMPEMWDPROD003 is part of the domain, That was aprox 24 hours
> after I had installed the server and added it to the domain, I believe
> Yesterday that had cleared up. I may have to reconfigure My DC's, all this
> was done before I started working here.
> Let me ask this, Should I go into SQL before doing anything and detach all
> databases before unistalling SQL, even though they were attached to the
> Node
> I was NOT replacing?
> And thanks again Geoff
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Geoff N. Hiten" wrote:
|||Geoff
Yeah, That is what I told my Boss, That I may have to uninstall SQL entirely
and "rebuild" the SQL Cluster, I am going to go ahead and "Evict" the other
node and replace it, And as long as everything works fine, and the only
problem I have is the SQL services and the "Attaching" the databases,
everytime we fail over. I wiill schedule that, It should be fairly simple. If
I uninstall everything and remove the Virtual SQL name and IP from AD and
DNS, It should load like a first time SQL Cluster install......I HOPE!!
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:

> You have a split-brain SQL install. In a cluster, the services should be
> MANUAL and only started up on the currently active node by the cluster
> service. You may need to detach the databases, uninstall SQL completely,
> and reinstall the SQL clustered instance to get everything back the way it
> is supposed to be.
> Normally when you kick a node out, you don't have to detach anything. The
> installer should simply remove the node from the possible owners of the
> resource group and fix the internal SQL configuration. This is where
> practicing on a virtual installation really pays off.
> And yes, the DC problems may have contributed to the issue.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:2581DDE3-C54D-4A65-B0F4-B5B8EA07BC86@.microsoft.com...
>
|||You really need a test platform so you have all your steps practiced. MS
Virtual Server will allow you to build a "practice" cluster and walk through
these steps so you have a proven checklist. I use it heavily to test client
scenarios before touching actual live systems. Keeps the "oops" rate down.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:8D9CDB0C-9481-41C3-B46D-A32E2267A63B@.microsoft.com...[vbcol=seagreen]
> Geoff
> Yeah, That is what I told my Boss, That I may have to uninstall SQL
> entirely
> and "rebuild" the SQL Cluster, I am going to go ahead and "Evict" the
> other
> node and replace it, And as long as everything works fine, and the only
> problem I have is the SQL services and the "Attaching" the databases,
> everytime we fail over. I wiill schedule that, It should be fairly simple.
> If
> I uninstall everything and remove the Virtual SQL name and IP from AD and
> DNS, It should load like a first time SQL Cluster install......I HOPE!!
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Geoff N. Hiten" wrote:
|||Hey Geoff
I couldn't agree more, unfortuantely, we have nothing of the sort, SO I have
insisted my Boss get the equipment we need to build a new one and test these
procedures,We are a poor little compnay with a very stingy big brother (ML)
Until then..
What I did was added the SQL services manually to the SQL cluster group and
made them dependent on My database drive, Transaction Logs drive, SQL Virtual
IP and Virtual name. I put the services on my passive node to manual and
stopped them. I am going to fail it over tomorrow afternoon and see what
happens. I am not sure if there are any registry settings I need, so I am
going to try it, If it doesn't work I will limp along with what I have until
I can uninstall and reinstall everything or just build a whole new cluster
If I am lucky ,it will owrk as though it is a cluster, I get so angry when I
look at the properties of my SQL services in "SQL CONFIGURATION MANAGER" and
it says CLUSTERED = NO...OHHHHHHH
Well I will let you Know.
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:

> You really need a test platform so you have all your steps practiced. MS
> Virtual Server will allow you to build a "practice" cluster and walk through
> these steps so you have a proven checklist. I use it heavily to test client
> scenarios before touching actual live systems. Keeps the "oops" rate down.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:8D9CDB0C-9481-41C3-B46D-A32E2267A63B@.microsoft.com...
>
|||Find a desktop with 1-2GB of RAM and install Virtual Server 2005. I run it
on a high-end notebook.
GNH
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:699110DB-4736-411A-88A1-8A073DC0BD5A@.microsoft.com...[vbcol=seagreen]
> Hey Geoff
> I couldn't agree more, unfortuantely, we have nothing of the sort, SO I
> have
> insisted my Boss get the equipment we need to build a new one and test
> these
> procedures,We are a poor little compnay with a very stingy big brother
> (ML)
> Until then..
> What I did was added the SQL services manually to the SQL cluster group
> and
> made them dependent on My database drive, Transaction Logs drive, SQL
> Virtual
> IP and Virtual name. I put the services on my passive node to manual and
> stopped them. I am going to fail it over tomorrow afternoon and see what
> happens. I am not sure if there are any registry settings I need, so I am
> going to try it, If it doesn't work I will limp along with what I have
> until
> I can uninstall and reinstall everything or just build a whole new cluster
> If I am lucky ,it will owrk as though it is a cluster, I get so angry when
> I
> look at the properties of my SQL services in "SQL CONFIGURATION MANAGER"
> and
> it says CLUSTERED = NO...OHHHHHHH
> Well I will let you Know.
>
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Geoff N. Hiten" wrote:
|||Hey Geoff
Two last things, I have been pouring over TechNet ans MSDN looking for where
I made my mistake (and I am sure I did) and I came across a white paper that
talks about removing a SQL node by using the SQL 2005 setup and removing the
SQL instance at least that is what I think it is talking about) and basically
leaving the SQL application structure in tact. I of course went to
"add/remove programsn and selected my node and nuked it. Do you know anything
about this and is it a function osf the setup to allow you to basically
remove a clustered instance yet leave the Application (and hence, the
cluster) in tact?
this is the link to what I was reading and as you can see in line 6..Yeah, I
never got that "Maintain the Virtual Server" so perhaps I wasn't as thorough
in my research, and I paid the price
http://msdn2.microsoft.com/en-us/library/ms191545.aspx
Also, I do have a spare Dell 2950, I am thiniing of using VMware to create
two virtual servers and 'Cluster" them, I just need to figure out the Sahred
Storage" and my "heartbeat" I have never cluster VM sessions, I only have the
freeware, so If it costs money to buy a full blown version, I will have to go
hat in hand.
Geoff, I appreciate all your help on this and at least, it wasn't a total
disaster.
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Scott Cummins" wrote:

> I had a hard time with my node replacement. And I am hoping to find out what
> I did wrong and perhaps some answers to a few lingering questions, I want to
> step through what I did
> 1. I was using a whole new name and IP address for the new node, so I place
> it on the network the day before and attached it to the SAN, But did not
> assign it to the storage group or attach LUNS or Any cluster environment.
> The next day I first removed SQL from the Second node, It came up with thte
> window that lets me pick the Node, However when I did this it removed my
> Virtual SQL Cluster name and IP, which worried me tremendoulsy, is this
> Normal?, then I evicted it from the cluster and it seemed okay.
> I then turned off Node 1 and added the new Node to the storage group, added
> all the appropriate SAN software, rebooted and when my Node 2 came up
> assigned the same drive letters to the shared storage as on the first node,
> then off the second node and restarted the first. waited for my drives to
> appear Okay, then started the second node.
> I had all my IP'S and Heartbeat configured and everything came up fine.
> Then I added the new node 2 to the MS cluster and it went well,
> Now I tried to load SQL and I was sure it wouldn't become part of any SQL
> CLuster because it appeared it didn't exit anymore. I readded the SQL name
> and IP to the cluster resources and went ahead and added it, But It didn't
> seem to work right, Every time I fail over, It works and I can use the SQL
> Virtual IP, But I have to re-attach the DB's every time I fail over.
> I am not sure if I missed a step, Or if Node one should have been off when I
> removed SQL from Node two, One thing I am questioning is whether the SQL
> services hsould be running on both Nodes, even when one is passive
> Can anyone pinpoint my issue or help me with Post-game analysis
>
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
|||Scott,
Yep, step 6 is exactly what you missed. A SQL Clustered Instance maintains
internal information on what nodes it can run on. The "Maintain the Virtual
Server" option is how you update this information. The Instance stays
intact, but the allowed nodes are changed by this process. This maintains
the separation between nodes and instances.
You can download Virtual Server 2005 R2 SP1 for free
http://www.microsoft.com/windowsserversystem/virtualserver/
Here is how to build a SQL 2005 test cluster using VS 2005.
http://support.microsoft.com/kb/891798
So, your basic requirement of zero cost is now met. All it takes is a bit
of time.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:C11D097B-B49E-4ED3-B805-EE25EFE5E556@.microsoft.com...[vbcol=seagreen]
> Hey Geoff
> Two last things, I have been pouring over TechNet ans MSDN looking for
> where
> I made my mistake (and I am sure I did) and I came across a white paper
> that
> talks about removing a SQL node by using the SQL 2005 setup and removing
> the
> SQL instance at least that is what I think it is talking about) and
> basically
> leaving the SQL application structure in tact. I of course went to
> "add/remove programsn and selected my node and nuked it. Do you know
> anything
> about this and is it a function osf the setup to allow you to basically
> remove a clustered instance yet leave the Application (and hence, the
> cluster) in tact?
> this is the link to what I was reading and as you can see in line 6..Yeah,
> I
> never got that "Maintain the Virtual Server" so perhaps I wasn't as
> thorough
> in my research, and I paid the price
> http://msdn2.microsoft.com/en-us/library/ms191545.aspx
> Also, I do have a spare Dell 2950, I am thiniing of using VMware to create
> two virtual servers and 'Cluster" them, I just need to figure out the
> Sahred
> Storage" and my "heartbeat" I have never cluster VM sessions, I only have
> the
> freeware, so If it costs money to buy a full blown version, I will have to
> go
> hat in hand.
> Geoff, I appreciate all your help on this and at least, it wasn't a total
> disaster.
>
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Scott Cummins" wrote:

Post method in ASP stops working

I am having an issue on a windows 2003 box where I run a report to a new
window, and I do a request.form to get all my parameters. The first time I
run a report to a new window I am prompted for the windows user name and
password. Once I enter in the login info my report runs fine. Then when I try
to run another report to a new window the request.form doesn't work to pull
things in from my form. Everything is blank, so I get error messages. Another
report will not run until I shutdown IE restart IE log back into my website
and run the report.
My redirect string is
http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
"&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
Can anyone tell me why the post method or Request.form will only work once
and then I have to restart IE. Again I am opening the report in a new window.
--
Thanks,
CraigI fixed this by going into IIS and going into the properties on the Reports
and ReportServer Virtual directories and under Directory security enabling
anonymous access.
"Craig" wrote:
> I am having an issue on a windows 2003 box where I run a report to a new
> window, and I do a request.form to get all my parameters. The first time I
> run a report to a new window I am prompted for the windows user name and
> password. Once I enter in the login info my report runs fine. Then when I try
> to run another report to a new window the request.form doesn't work to pull
> things in from my form. Everything is blank, so I get error messages. Another
> report will not run until I shutdown IE restart IE log back into my website
> and run the report.
> My redirect string is
> http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
> "&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
> Can anyone tell me why the post method or Request.form will only work once
> and then I have to restart IE. Again I am opening the report in a new window.
> --
> Thanks,
> Craig

Post install processor addition

I am getting ready to install SQL Server 2000 Enterprise
in a new server but wanted to add an additional processor.
Time crunch is making me want to install the software
prior to getting the processor in. My question is whether
SQL will recognize the additional processor after it is
installed or do I have to wait until I have the part
installed? We were intending to allow SQL to use any
available processor for processing. Thanks in advance for
any help.As long as your host computer already has a multi-processing kernal
installed, you should be fine.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rick" <rvia@.vw.vccs.edu> wrote in message
news:0de801c4ac99$ea52b880$a301280a@.phx.gbl...
> I am getting ready to install SQL Server 2000 Enterprise
> in a new server but wanted to add an additional processor.
> Time crunch is making me want to install the software
> prior to getting the processor in. My question is whether
> SQL will recognize the additional processor after it is
> installed or do I have to wait until I have the part
> installed? We were intending to allow SQL to use any
> available processor for processing. Thanks in advance for
> any help.

Post install processor addition

I am getting ready to install SQL Server 2000 Enterprise
in a new server but wanted to add an additional processor.
Time crunch is making me want to install the software
prior to getting the processor in. My question is whether
SQL will recognize the additional processor after it is
installed or do I have to wait until I have the part
installed? We were intending to allow SQL to use any
available processor for processing. Thanks in advance for
any help.
As long as your host computer already has a multi-processing kernal
installed, you should be fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rick" <rvia@.vw.vccs.edu> wrote in message
news:0de801c4ac99$ea52b880$a301280a@.phx.gbl...
> I am getting ready to install SQL Server 2000 Enterprise
> in a new server but wanted to add an additional processor.
> Time crunch is making me want to install the software
> prior to getting the processor in. My question is whether
> SQL will recognize the additional processor after it is
> installed or do I have to wait until I have the part
> installed? We were intending to allow SQL to use any
> available processor for processing. Thanks in advance for
> any help.

Possible to have 2 Identity columns in a field?

Hi - is it possible to have 2 Identity (auto increment) fields in table?
Each time I try to change the one to an Identity column, it changes the
other to NOT an identity column.
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
No.
You cannot have more than one an identity column as well as updating
(identity) is not allowed.
You can generate your own an autoincrement colunm
create table #t
(
col1 int not null identity(1,1),
col2 as col1
)
insert into #t default values
select * from #t
Note: There are some "divantages" . With an identity you cannot be sure
that there will not be gaps or duplicates
"Mark" <anonymous@.devdex.com> wrote in message
news:uGw12ikyFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No.
Such situation never occurs in proper design
--
Regards
R.D
--Knowledge gets doubled when shared
"Mark" wrote:

> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||No. Please explain why you would want more than one IDENTITY column in
a table.
What can you do with 2 columns that you can't already do with 1?
David Portas
SQL Server MVP
--|||Hi,
Its not possible to have two identity columns in the same table.
However I have never seen it changing an existing identity column into
not an identity column. Can u pls send me the script.
Regards,
Shanmugam
Mark wrote:

> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***

possible to get real time transaction log replication?

hi,
i know there are a variety of 3rd party products that can accomplish this
but am curious if anyone knows of a way to accomplish this using sql? thanks.
You can tweak transactional replication to get minimum latency (very
roughly, a few secs), use log shipping (min 1 min) use distributed
transactions (immediate), or have a look at database mirroring (<1 second
ASAIK) in SQL 2005.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Under load you will be looking at 20s to 1 minute minimum for transactional.
With no load you can be looking at 2-4 secs.
Log shipping latency is greater than 1 min. typically pushing 2, but a more
practical limit is 5 minutes.
Database mirroring which ships in SQL 2005 has two modes high performance
(asynchronous) and high availability (synchronous). In high availability
mode logged operations are written on both sides (split write) which means
increased latency. A transaction is written to the source, and then written
to the destination, and then the app gets the commit. Latency can be
considerably increased depending on your throughput and other factors.
In high performance if your source goes down you can lose transactions.
Performance is better, latency can be larges, but the transactional latency
of transactions hitting your database is less.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O3dmxeVYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> You can tweak transactional replication to get minimum latency (very
> roughly, a few secs), use log shipping (min 1 min) use distributed
> transactions (immediate), or have a look at database mirroring (<1 second
> ASAIK) in SQL 2005.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||thanks for the info. im looking for some sort of a synchronous solution but
im rather new to this. operating in an OLTP environment so have very little
maneuvering room in regard to latency.
what sort of 'real-world' bandwidth / latency do i need to make DTC work?
|||Can't answer this one, but using immediate updating will increase the time
required to commit a transaction on the subscriber. I have seen transactions
take from 43 to 140 ms when using immediate updating.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mb" <mb@.discussions.microsoft.com> wrote in message
news:693B008E-8BE2-4D2C-BC61-737CE180ED88@.microsoft.com...
> thanks for the info. im looking for some sort of a synchronous solution
but
> im rather new to this. operating in an OLTP environment so have very
little
> maneuvering room in regard to latency.
> what sort of 'real-world' bandwidth / latency do i need to make DTC work?

possible to free-text search two tables at once in one query?

is it possible to use a full-text search query on two tables at the same time? for example, say i have a table of items, and another table of descriptions... would i be able to search both those tables for a string in one query? they are currently in indexed in separate catalogs, would i need to put them both in one?

thanksUse subqueries, maybe even with UNION [ALL] if the result set will contain the same number of fields in the same order, and of the same data types.

Possible to alert on SQL Server Agent Error Log?

We have one SQL server where the mail may disconnect from time to time with
the SQL Server Agent.
Is it possible to flag an alert to notify when the SQL Server Agent produces
an error in the SQL Server Agent Error Log?Hi
"ssciarrino" wrote:

> We have one SQL server where the mail may disconnect from time to time wit
h
> the SQL Server Agent.
> Is it possible to flag an alert to notify when the SQL Server Agent produc
es
> an error in the SQL Server Agent Error Log?
You don't give your SQL Server version number or whether SQL Agent is still
running and it is just mail that is not getting though? What message is put
in the log?
Have you looked at creating an ALERT for this if you can trap the error
level or number? You would have to use a method other than emails to send th
e
information! You could look at using MOM to check the log files for this.
A common cause of email stopping is issues connecting to the email server.
If you have network issues you may want to sort them out. SMTP email tends t
o
be less prone to this to use this with SQL 2000 you can use xp_smtp
http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database mail
.
John|||Hi John
Thanks for the insight I will look into this...at the moment we are SQl
2000 but as you mention SQL 2005 doesn't have this problem. We are currentl
y
migrating to SQL 2005, may not have to visit this after upgrade.
I was looking for any type of log message that was error (red) but it
appears I have to narrow down via alert via error number/message
"John Bell" wrote:

> Hi
> "ssciarrino" wrote:
>
> You don't give your SQL Server version number or whether SQL Agent is stil
l
> running and it is just mail that is not getting though? What message is pu
t
> in the log?
> Have you looked at creating an ALERT for this if you can trap the error
> level or number? You would have to use a method other than emails to send
the
> information! You could look at using MOM to check the log files for this.
> A common cause of email stopping is issues connecting to the email server.
> If you have network issues you may want to sort them out. SMTP email tends
to
> be less prone to this to use this with SQL 2000 you can use xp_smtp
> http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
> send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database ma
il.
> John

Possible to alert on SQL Server Agent Error Log?

Hi
"ssciarrino" wrote:

> We have one SQL server where the mail may disconnect from time to time with
> the SQL Server Agent.
> Is it possible to flag an alert to notify when the SQL Server Agent produces
> an error in the SQL Server Agent Error Log?
You don't give your SQL Server version number or whether SQL Agent is still
running and it is just mail that is not getting though? What message is put
in the log?
Have you looked at creating an ALERT for this if you can trap the error
level or number? You would have to use a method other than emails to send the
information! You could look at using MOM to check the log files for this.
A common cause of email stopping is issues connecting to the email server.
If you have network issues you may want to sort them out. SMTP email tends to
be less prone to this to use this with SQL 2000 you can use xp_smtp
http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database mail.
John
Hi John
Thanks for the insight I will look into this...at the moment we are SQl
2000 but as you mention SQL 2005 doesn't have this problem. We are currently
migrating to SQL 2005, may not have to visit this after upgrade.
I was looking for any type of log message that was error (red) but it
appears I have to narrow down via alert via error number/message
"John Bell" wrote:

> Hi
> "ssciarrino" wrote:
>
> You don't give your SQL Server version number or whether SQL Agent is still
> running and it is just mail that is not getting though? What message is put
> in the log?
> Have you looked at creating an ALERT for this if you can trap the error
> level or number? You would have to use a method other than emails to send the
> information! You could look at using MOM to check the log files for this.
> A common cause of email stopping is issues connecting to the email server.
> If you have network issues you may want to sort them out. SMTP email tends to
> be less prone to this to use this with SQL 2000 you can use xp_smtp
> http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
> send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database mail.
> John

Possible to alert on SQL Server Agent Error Log?

We have one SQL server where the mail may disconnect from time to time with
the SQL Server Agent.
Is it possible to flag an alert to notify when the SQL Server Agent produces
an error in the SQL Server Agent Error Log?Hi
"ssciarrino" wrote:
> We have one SQL server where the mail may disconnect from time to time with
> the SQL Server Agent.
> Is it possible to flag an alert to notify when the SQL Server Agent produces
> an error in the SQL Server Agent Error Log?
You don't give your SQL Server version number or whether SQL Agent is still
running and it is just mail that is not getting though? What message is put
in the log?
Have you looked at creating an ALERT for this if you can trap the error
level or number? You would have to use a method other than emails to send the
information! You could look at using MOM to check the log files for this.
A common cause of email stopping is issues connecting to the email server.
If you have network issues you may want to sort them out. SMTP email tends to
be less prone to this to use this with SQL 2000 you can use xp_smtp
http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database mail.
John|||Hi John
Thanks for the insight I will look into this...at the moment we are SQl
2000 but as you mention SQL 2005 doesn't have this problem. We are currently
migrating to SQL 2005, may not have to visit this after upgrade.
I was looking for any type of log message that was error (red) but it
appears I have to narrow down via alert via error number/message
"John Bell" wrote:
> Hi
> "ssciarrino" wrote:
> > We have one SQL server where the mail may disconnect from time to time with
> > the SQL Server Agent.
> >
> > Is it possible to flag an alert to notify when the SQL Server Agent produces
> > an error in the SQL Server Agent Error Log?
> You don't give your SQL Server version number or whether SQL Agent is still
> running and it is just mail that is not getting though? What message is put
> in the log?
> Have you looked at creating an ALERT for this if you can trap the error
> level or number? You would have to use a method other than emails to send the
> information! You could look at using MOM to check the log files for this.
> A common cause of email stopping is issues connecting to the email server.
> If you have network issues you may want to sort them out. SMTP email tends to
> be less prone to this to use this with SQL 2000 you can use xp_smtp
> http://www.sqldev.net/xp/xpsmtp.htm and include extra steps in the jobs to
> send the sucess/failure emails. SQL 2005 has SMTP inbuilt with database mail.
> John

Possible bug in RS -Using the Now function in expression in a Snap

We have created a report that creates a historical snapshot. In the header,
we use the Now vb function to return the current date/time. However, the Now
function does not evaulate at the report execution time. Rather, it seems to
evaluate upon the first opening of the report for viewing. Thus, the snapshot
might have been taken yesterday, but if I open the report in a browser (for
the first time) today, the Page Header now says today (and will always say
that).
Additionally, when you export the report to Excel, the Now evaluates
again... so you get the current date/time in the page header of the Excel
cell.
I will try using the Execution time to get around this, but thought I'd pass
this along.Currently, page headers and footers are calculated at rendering time. This
is so that we can recalculate the page numbering when the report is
repaginated. In SQL 2005, we will change this so that the HTML and GDI
renderers will do this at processing / snapshot time while page oriented
formats will do this at rendering time. In a future version, we will change
these to be static as well.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dbconsulting" <dbconsulting@.discussions.microsoft.com> wrote in message
news:AF88D084-DE6E-4E01-B2AE-2BFB26C1B821@.microsoft.com...
> We have created a report that creates a historical snapshot. In the
> header,
> we use the Now vb function to return the current date/time. However, the
> Now
> function does not evaulate at the report execution time. Rather, it seems
> to
> evaluate upon the first opening of the report for viewing. Thus, the
> snapshot
> might have been taken yesterday, but if I open the report in a browser
> (for
> the first time) today, the Page Header now says today (and will always say
> that).
> Additionally, when you export the report to Excel, the Now evaluates
> again... so you get the current date/time in the page header of the Excel
> cell.
> I will try using the Execution time to get around this, but thought I'd
> pass
> this along.
>|||Well said by Brian. In case you really wanna show snapshot date/time in page
header do the following-
1. Make textbox named as TextBox1 in report and make it invisble. now put
=now as expression in it.
2. In page header make new text box and use expression =ReportItems!TextBox1.Value
Above code works cause reportItems collection is evaluated and rendering
time but it picks the value from TextBox1 which was processed at the time of
snapshot creation.
Problem solved! :o)
-Suneet Mohan
"dbconsulting" wrote:
> We have created a report that creates a historical snapshot. In the header,
> we use the Now vb function to return the current date/time. However, the Now
> function does not evaulate at the report execution time. Rather, it seems to
> evaluate upon the first opening of the report for viewing. Thus, the snapshot
> might have been taken yesterday, but if I open the report in a browser (for
> the first time) today, the Page Header now says today (and will always say
> that).
> Additionally, when you export the report to Excel, the Now evaluates
> again... so you get the current date/time in the page header of the Excel
> cell.
> I will try using the Execution time to get around this, but thought I'd pass
> this along.
>|||Why not use Globals.ExecutionTime ?
On Fri, 1 Apr 2005 13:17:02 -0800, dbconsulting
<dbconsulting@.discussions.microsoft.com> wrote:
>We have created a report that creates a historical snapshot. In the header,
>we use the Now vb function to return the current date/time. However, the Now
>function does not evaulate at the report execution time. Rather, it seems to
>evaluate upon the first opening of the report for viewing. Thus, the snapshot
>might have been taken yesterday, but if I open the report in a browser (for
>the first time) today, the Page Header now says today (and will always say
>that).
>Additionally, when you export the report to Excel, the Now evaluates
>again... so you get the current date/time in the page header of the Excel
>cell.
>I will try using the Execution time to get around this, but thought I'd pass
>this along.|||Yes, using =Globals.ExecutionTime instead of =Now would be the right
solution.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Brown" <octavius@.gmail.com> wrote in message
news:k8e251tn28vut7uk22e16h1koe1h4h94bk@.4ax.com...
> Why not use Globals.ExecutionTime ?
>
> On Fri, 1 Apr 2005 13:17:02 -0800, dbconsulting
> <dbconsulting@.discussions.microsoft.com> wrote:
>>We have created a report that creates a historical snapshot. In the
>>header,
>>we use the Now vb function to return the current date/time. However, the
>>Now
>>function does not evaulate at the report execution time. Rather, it seems
>>to
>>evaluate upon the first opening of the report for viewing. Thus, the
>>snapshot
>>might have been taken yesterday, but if I open the report in a browser
>>(for
>>the first time) today, the Page Header now says today (and will always say
>>that).
>>Additionally, when you export the report to Excel, the Now evaluates
>>again... so you get the current date/time in the page header of the Excel
>>cell.
>>I will try using the Execution time to get around this, but thought I'd
>>pass
>>this along.
>