Max Connections?

Hi,
We're frequently getting a 'Timeout Expired' error on ASP pages that access
one of our SQL database servers. Is there a limit to the number of
concurrent connections with SQL server 2000 or has anyone else experienced
this behaviour? It is becoming quite frequent (around once a day). The only
thing that seems to solve it is a restart of the SQL services.
Thanks for any assistance - Ian
"Ian Bell" <ianbell6862@.hotmail.comm> wrote in
news:uva#C1vSFHA.3392@.TK2MSFTNGP12.phx.gbl:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that
> access one of our SQL database servers. Is there a limit to the number
> of concurrent connections with SQL server 2000 or has anyone else
> experienced this behaviour? It is becoming quite frequent (around once
> a day). The only thing that seems to solve it is a restart of the SQL
> services.
> Thanks for any assistance - Ian
>
See FAQ for limited connections in IIS. Review your ASP code.
http://www.microsoft.com/technet/com...si1203.mspx#EB
AA
Regards
JTC ^..^
|||According to this article there is not a limit to the number of concurrent
connections with SQL server databases.
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp
"Ian Bell" wrote:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that access
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The only
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>
|||According to this article there is not a limit to concurrent connections in
SQL Server 2000.
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp
You might want to check the setting for multiple connections in your website
settings in IIS.
"Ian Bell" wrote:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that access
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The only
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>

Max Connections?

Hi,
We're frequently getting a 'Timeout Expired' error on ASP pages that access
one of our SQL database servers. Is there a limit to the number of
concurrent connections with SQL server 2000 or has anyone else experienced
this behaviour? It is becoming quite frequent (around once a day). The only
thing that seems to solve it is a restart of the SQL services.
Thanks for any assistance - Ian"Ian Bell" <ianbell6862@.hotmail.comm> wrote in
news:uva#C1vSFHA.3392@.TK2MSFTNGP12.phx.gbl:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that
> access one of our SQL database servers. Is there a limit to the number
> of concurrent connections with SQL server 2000 or has anyone else
> experienced this behaviour? It is becoming quite frequent (around once
> a day). The only thing that seems to solve it is a restart of the SQL
> services.
> Thanks for any assistance - Ian
>
See FAQ for limited connections in IIS. Review your ASP code.
http://www.microsoft.com/technet/co...isi1203.mspx#EB
AA
Regards
JTC ^..^|||According to this article there is not a limit to the number of concurrent
connections with SQL server databases.
_0ciq.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_0ciq.asp
"Ian Bell" wrote:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that acces
s
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The onl
y
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>|||According to this article there is not a limit to concurrent connections in
SQL Server 2000.
_0ciq.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_0ciq.asp
You might want to check the setting for multiple connections in your website
settings in IIS.
"Ian Bell" wrote:

> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that acces
s
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The onl
y
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>

Max Connections?

Hi,
We're frequently getting a 'Timeout Expired' error on ASP pages that access
one of our SQL database servers. Is there a limit to the number of
concurrent connections with SQL server 2000 or has anyone else experienced
this behaviour? It is becoming quite frequent (around once a day). The only
thing that seems to solve it is a restart of the SQL services.
Thanks for any assistance - Ian"Ian Bell" <ianbell6862@.hotmail.comm> wrote in
news:uva#C1vSFHA.3392@.TK2MSFTNGP12.phx.gbl:
> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that
> access one of our SQL database servers. Is there a limit to the number
> of concurrent connections with SQL server 2000 or has anyone else
> experienced this behaviour? It is becoming quite frequent (around once
> a day). The only thing that seems to solve it is a restart of the SQL
> services.
> Thanks for any assistance - Ian
>
See FAQ for limited connections in IIS. Review your ASP code.
http://www.microsoft.com/technet/community/columns/insider/iisi1203.mspx#EB
AA
--
Regards
JTC ^..^|||According to this article there is not a limit to the number of concurrent
connections with SQL server databases
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp
"Ian Bell" wrote:
> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that access
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The only
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>|||According to this article there is not a limit to concurrent connections in
SQL Server 2000.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp
You might want to check the setting for multiple connections in your website
settings in IIS.
"Ian Bell" wrote:
> Hi,
> We're frequently getting a 'Timeout Expired' error on ASP pages that access
> one of our SQL database servers. Is there a limit to the number of
> concurrent connections with SQL server 2000 or has anyone else experienced
> this behaviour? It is becoming quite frequent (around once a day). The only
> thing that seems to solve it is a restart of the SQL services.
> Thanks for any assistance - Ian
>
>sql

Matrix Report, groups and subtotal error

Please, i need a very urgent help, for this question... tkssss :-)

I need to create a report with dynamic columns. I try use Matrix, but in this time, i dont konw how to calcutate the subtotal e groups in the correct way.

The data for generate the report ( http://xs116.xs.to/xs116/07233/DataReport.JPG )
This is the actual report ( 2 rows groups and 1 columns group ) ok, but how group dates now ? ( http://xs116.xs.to/xs116/07233/ReportInvalid.JPG
The correct report i need( if is possible?) (http://xs116.xs.to/xs116/07233/ReportOK.JPG)
I need group by Filial (idFilial) Dates (Dta) and inside dates the Dispositivos(NomeDispositivo or IdDispositivo) of the date.... and finally total all by group Filial (idFilial)...

Code of my actual report....

Code Snippet

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="INTEGRAReport">
<ConnectionProperties>
<ConnectString>server=111.11.1.111\GPS;Integrated Security=False;User Id=integraapp;Password=asasasas;initial catalog=INTEGRA_DESENV;Persist Security Info=True;Connection Timeout=360</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>637ed8b0-ea9b-4da1-b6b8-6710cf62db84</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeFilial.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_GroupFilial">
<GroupExpressions>
<GroupExpression>=Fields!IdFilial.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Dta.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!IdProduto.Value</GroupExpression>
<GroupExpression>=Fields!Dta.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeDipositivo.Value</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="NomeProduto">
<rd:DefaultName>NomeProduto</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeProduto.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_NomeProduto">
<GroupExpressions>
<GroupExpression>=Fields!NomeProduto.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.375in</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.375in</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Qtd</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Saldo</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas</DataSetName>
<Width>6.25in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Filial
/
Dispositivo</Value>
</Textbox>
</ReportItems>
</Corner>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Height>1.125in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.375in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qtd">
<rd:DefaultName>Qtd</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Qtd.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Saldo.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.875in</Height>
</Body>
<PageHeader>
<Height>0.25in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>872304d3-bc47-4430-8d0b-ea8c9d927f42</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>ConsolidadoEntregasDataSet</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>RelatorioConsolidadoEntregasTableAdapter</rd:TableAdapterName>
<rd:TableName>RelatorioConsolidadoEntregas</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>dbo.RelatorioConsolidadoEntregas</CommandText>
<DataSourceName>INTEGRAReport</DataSourceName>
</Query>
<Fields>
<Field Name="IdFilial">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdFilial</DataField>
</Field>
<Field Name="IdProduto">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdProduto</DataField>
</Field>
<Field Name="NomeProduto">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeProduto</DataField>
</Field>
<Field Name="NomeFilial">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeFilial</DataField>
</Field>
<Field Name="NomeDipositivo">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeDipositivo</DataField>
</Field>
<Field Name="Dta">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Dta</DataField>
</Field>
<Field Name="Qtd">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Qtd</DataField>
</Field>
<Field Name="Saldo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Saldo</DataField>
</Field>
<Field Name="IdDispositivo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdDispositivo</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>11.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

try right clicking the date row group and click subtotal then right click first row group and and click subtotal. By the way, i see 3 row groups and 2 column groups. If this does not work could you show a screenshot of the layout and the result of this fix? Thanks.|||you need to move the date grouping down one

matrix, properties (dialog), groups, row groups, select the date one, click the down button one time|||this may be of use also:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

Matrix filter - rendering error - BUG?

I have a matrix-rowgroup with a filter. The filter is a simple
"DataSet.SomeValue = True" and it works just fine most of the time.
However, depending on my query-data there will be situations where filter
will filter out all data, thus leaving zero rows. This 'should' result in an
empty matrix.
But instead i get an error when viewing my report.
RenderingException: "Operation is not valid due to the current state of the
object."
Is this intentional?
Can anyone suggest a work-around?You probably have an expression somewhere that tries to access an item from
the matrix. As the matrix item is not there, you are getting the error. You
should recode your expression with an IIF(Fields!MatrixElement is
nothing,...,...).
I also have a matrix which I am filtering. Sometimes I get no data. When I
have no rows returned, there is just a blank report, no errors like you are
getting. Of course I could use the NoRows property to display an alternative
text when no rows are returned.
HTH
Charles Kangai, MCT, MCDBA
"Kristian Vinther" wrote:
> I have a matrix-rowgroup with a filter. The filter is a simple
> "DataSet.SomeValue = True" and it works just fine most of the time.
> However, depending on my query-data there will be situations where filter
> will filter out all data, thus leaving zero rows. This 'should' result in an
> empty matrix.
> But instead i get an error when viewing my report.
> RenderingException: "Operation is not valid due to the current state of the
> object."
> Is this intentional?
> Can anyone suggest a work-around?

Matrix - Totals

What does the following error mean?
[rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
references a field outside an aggregate function. Value expressions in
matrix cells should be aggregates, to allow for subtotaling.
Build complete -- 0 errors, 1 warningsResolved
"Terry" wrote:
> What does the following error mean?
> [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> references a field outside an aggregate function. Value expressions in
> matrix cells should be aggregates, to allow for subtotaling.
> Build complete -- 0 errors, 1 warnings|||Figured it out. For others knowledge, you need to add the First() around the
value, like this for BorderStyle:
=iif(First(Fields!desc.Value)="Sent","Solid","None")
"Sharon" wrote:
> How was this resolved?
>
> "Terry" wrote:
> > Resolved
> >
> > "Terry" wrote:
> >
> > > What does the following error mean?
> > >
> > > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > > references a field outside an aggregate function. Value expressions in
> > > matrix cells should be aggregates, to allow for subtotaling.
> > > Build complete -- 0 errors, 1 warnings|||How was this resolved?
"Terry" wrote:
> Resolved
> "Terry" wrote:
> > What does the following error mean?
> >
> > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > references a field outside an aggregate function. Value expressions in
> > matrix cells should be aggregates, to allow for subtotaling.
> > Build complete -- 0 errors, 1 warnings

Matix Grouping error need advice fast

SQLServer 2000 SP4 with RS SP2:
I have a matrix that displays URLs and Dates. The grouping is by URL
within Date. The URL is being formatted through custom code that strips
of everything after the first '/' ignoring 'http://' as below:
<Code>
Public Function FormatUrl(ByRef url As String) As String
Dim r As New
System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
System.Text.RegularExpressions.RegexOptions.IgnoreCase)
Dim m As System.Text.RegularExpressions.Match = r.Match(url)
return m.ToString()
End Function
</Code>
When I display the URL as in
<Textbox Name="Address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
</Textbox>
It works as I hoped but when I try to group the matrix of off
'=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
<Grouping Name="matrix1_DateRow">
<GroupExpressions>
<GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
the report compiles, but throughs an exception at runtime as below:
--
Processing Errors
--
An error has occurred during report processing.
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown.
--
OK
--
Can someone explain why I am getting this exception? Can you group off
of this type of expression?
Thanks for any helpI'm not sure, I'd call MS Support. The help doesn't offer a whole lot...
http://msdn2.microsoft.com/zh-cn/library/ms153581.aspx
Steve MunLeeuw
"p91473" <p91473@.sbcglobal.net> wrote in message
news:1159896928.329150.68180@.h48g2000cwc.googlegroups.com...
> SQLServer 2000 SP4 with RS SP2:
> I have a matrix that displays URLs and Dates. The grouping is by URL
> within Date. The URL is being formatted through custom code that strips
> of everything after the first '/' ignoring 'http://' as below:
> <Code>
> Public Function FormatUrl(ByRef url As String) As String
> Dim r As New
> System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> Dim m As System.Text.RegularExpressions.Match = r.Match(url)
> return m.ToString()
> End Function
> </Code>
> When I display the URL as in
> <Textbox Name="Address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>9pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
> </Textbox>
> It works as I hoped but when I try to group the matrix of off
> '=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
> <Grouping Name="matrix1_DateRow">
> <GroupExpressions>
> <GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> the report compiles, but throughs an exception at runtime as below:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Exception of type
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> was thrown.
> --
> OK
> --
> Can someone explain why I am getting this exception? Can you group off
> of this type of expression?
> Thanks for any help
>|||Seems like what you are doing is correct, can you try a dummy select
statement with the strings?
SELECT 'http://foo.com'
UNION
SELECT 'http://foo.a.com'
UNION
SELECT 'http://foo.c.com'
UNION
SELECT 'http://foo.b.com'
Steve MunLeeuw
"p91473" <p91473@.sbcglobal.net> wrote in message
news:1159896928.329150.68180@.h48g2000cwc.googlegroups.com...
> SQLServer 2000 SP4 with RS SP2:
> I have a matrix that displays URLs and Dates. The grouping is by URL
> within Date. The URL is being formatted through custom code that strips
> of everything after the first '/' ignoring 'http://' as below:
> <Code>
> Public Function FormatUrl(ByRef url As String) As String
> Dim r As New
> System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> Dim m As System.Text.RegularExpressions.Match = r.Match(url)
> return m.ToString()
> End Function
> </Code>
> When I display the URL as in
> <Textbox Name="Address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>9pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
> </Textbox>
> It works as I hoped but when I try to group the matrix of off
> '=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
> <Grouping Name="matrix1_DateRow">
> <GroupExpressions>
> <GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> the report compiles, but throughs an exception at runtime as below:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Exception of type
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> was thrown.
> --
> OK
> --
> Can someone explain why I am getting this exception? Can you group off
> of this type of expression?
> Thanks for any help
>

math error

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

Try

select convert(decimal(20,2),1.2334e+006)

Madhivanan|||Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.
Thanks.

Madhivanan wrote:

Quote:

Originally Posted by

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

>
Try
>
select convert(decimal(20,2),1.2334e+006)
>
Madhivanan

|||(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.

Unfortunately, there are not really any good options. The best is probably
to run a SELECT query, and takes some hint from where it terminates. This
example illustrates:

CREATE TABLE #tmp1 (a varchar(23) NOT NULL, b int IDENTITY)
go
INSERT #tmp1(a) VALUES ('1234')
INSERT #tmp1(a) VALUES ('1232')
INSERT #tmp1(a) VALUES ('2344')
INSERT #tmp1(a) VALUES ('34.34')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('1234e+006')
INSERT #tmp1(a) VALUES ('777')
go
SELECT convert(decimal(20, 2), a) FROM #tmp1 ORDER BY b
go
DROP TABLE #tmp1

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

Materialized View Error 8908

Microsoft SQL Server 2005 - 9.00.1187.07

dbcc checkdb is failing with an interesting message:

Msg 8908, Level 16, State 1, Line 1

Indexed view 'BritishEnglishMV' (object ID 226099846) does not contain all rows that the view definition produces. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.

The data materialized in the indexed view is exactly the same as the data in the underlying tables...
Books online has no info on this error.
Rebuilding the index fixes the problem.

This warning is produced if the indexed view does not 100% match the "newly generated" indexed view. This may happen in cases when there are updates performed on the underlying tables.
I will use an example to explain. If a view contains for examle an aggregation SUM, then inserting of new value to underlying table will add a new value to this sum. If the SUM was produced originally from a sequence of numbers, say a1, a2, ..., an, and the new inserted value is bb, then updating the indexed view means
(a1+a2+a3+...+an) + bb while recalculating the indexed view may prform the sum in different order.
We are still working on providing more information about warnings and errors we generate. This should improve substantially by the time we ship the final release of SQL Server 2005.

Lubor Kollar

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:
> Is it possible to recover master database from problematic master.mdf and/or
> problematic mastlog.ldf? No backups exist. The error that appeared in event
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know there
> is something starnge going on because there was no hard disk failure and the
> problem appeared after power loss of the server. But scan disk did not report
> any problem when the server rebooted after the power loss. So maybe the file
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas
"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.
Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:

> Is it possible to recover master database from problematic master.mdf and/or
> problematic mastlog.ldf? No backups exist. The error that appeared in event
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know there
> is something starnge going on because there was no hard disk failure and the
> problem appeared after power loss of the server. But scan disk did not report
> any problem when the server rebooted after the power loss. So maybe the file
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.
|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas

"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

master.mdf recovery

Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not repor
t
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in advance
.Hi
9003: The LSN %S_LSN passed to log scan in database '%.*ls' is invalid.
You have corruption. SQL server might have been in the middle of writing to
the DB when the power outage occured. This can corrupt the DB.
Without a backup of Master, you are high and dry. Your only option is the
run the Rebuild Wizard (Rebuildm.exe), but you loose all your user logins.
Look up the topic in BOL.
Regards
Mike
"Radis Bill" wrote:
[vbcol=seagreen]
> Is it possible to recover master database from problematic master.mdf and/
or
> problematic mastlog.ldf? No backups exist. The error that appeared in even
t
> log is
> Error:9003, Severity:20, State 1. and right after that: "Cannot recover
> master database.Exiting" . The reason I am posting this is that I know the
re
> is something starnge going on because there was no hard disk failure and t
he
> problem appeared after power loss of the server. But scan disk did not rep
ort
> any problem when the server rebooted after the power loss. So maybe the fi
le
> is "not so corrupted" or maybe it is a fixable problem. Thank you in advance.[/vbc
ol]|||From the console, you might be able to start sqlservr with the -T3607
option. The recovers non of the databases, system nor users. This may
allow you to export the configuration contexts from the system tables from
within the master database. If you do this, then there is a chance that you
would have all the information necessary to replace the content on a rebuilt
master. However, THIS IS A COMPLICATED SITUATION. CALL MS PSS FOR
ASSISTANCE.
Sincerely,
Anthony Thomas
"Radis Bill" <Radis Bill@.discussions.microsoft.com> wrote in message
news:63EFB560-DFC7-4D36-B331-62846758B058@.microsoft.com...
Is it possible to recover master database from problematic master.mdf and/or
problematic mastlog.ldf? No backups exist. The error that appeared in event
log is
Error:9003, Severity:20, State 1. and right after that: "Cannot recover
master database.Exiting" . The reason I am posting this is that I know there
is something starnge going on because there was no hard disk failure and the
problem appeared after power loss of the server. But scan disk did not
report
any problem when the server rebooted after the power loss. So maybe the file
is "not so corrupted" or maybe it is a fixable problem. Thank you in
advance.

master..xp_cmdshell Error 997 from GetProxyAccount

Hello all,
I have an aspx page that inserts a record in a table via a stored
procedure. That table has a trigger which gets fired on an insert. The
trigger has the following code
EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
This all works when the stored procedure is fired inserting the record
in turn firing off the trigger; but when the ASPX page runs the stored
procedure I get the following error.
A severe error occurred on the current command. The results, if any,
should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
472
The Guest login has permissions to the extended stored procedure
xp_cmdshell on the master db, but this has not helped.
Any help would be greatly appriciated.
PhilHi,
You need to set up a proxy account.
Create an user and then configure that in EM (Management, right-clickSQL
Agent, configure that proxy account).
Have a look into below article:-
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
Thanks
Hari
MCDBA
"Phil" <toomuchphill@.hotmail.com> wrote in message
news:8358f7c7.0406132044.6b353856@.posting.google.com...
> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil|||Thanks for that Hari,
I am going forward... I think
The error I am getting now is General network error. Check your
network documentation.
I have now configured the proxy account within EM. I unticked the
'Only users with SysAdmin privileges can execute cmdExec and
ActiveScripting job steps.'
I have since changed the trigger from executing my .exe to execute a
.bat which simply copies a file. The Internet Guest account has
privileges to the directory.
The batch file is being run but the copy is not working. I guess this
is to do with the privileges still.
Any suggestions would be greatly appreciated.
Phil.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<eRBIv2cUEHA.3944@.tk2msftngp13.phx.
gbl>...[vbcol=seagreen]
> Hi,
> You need to set up a proxy account.
> Create an user and then configure that in EM (Management, right-clickSQL
> Agent, configure that proxy account).
> Have a look into below article:-
> [url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:[/ur
l]
> 80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
>
> --
> Thanks
> Hari
> MCDBA
> "Phil" <toomuchphill@.hotmail.com> wrote in message
> news:8358f7c7.0406132044.6b353856@.posting.google.com...|||- Start SQL Enterprise manager
- Open your server
- open the Management folder
- right mouse on SQL Server Agent and select properties
- select Job System tab
- UNCHECK the box in the section 'Non SysAdmin job step proxy account'
which says 'Only users with SysAdmin priveleges can ...'
- when the dialog comes up enter the username, password, [domain] of
some defined Windows user with sysAdmin priveleges (create one if needed fir
st).
- click Apply and OK
"Phil" wrote:

> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil
>

master..sysmessages

Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
When the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:

>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks
|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:

> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:

> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:

master..sysmessages

Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:

>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:

> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:

> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>

master..sysmessages

Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >Hi
> >I am trying to create a job to monitor the error log in sql server. I
> >understand that the error types with their severity are in the table
> >master..sysmessages.
> >
> >However, which table to I frequently query or monitor so that whenever the
> >error which I am looking for with specific severity, when occurs the job will
> >be fired?
> >
> >What table stores this information, so that I can run the query against it?
> >
> >Thanks
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>> When the error occurs, you want it to be logged. That's how
>> you know the error happened. Otherwise, they aren't really
>> stored anywhere.
>> You can create an alert that will watch for the error and in
>> response to the error, it can execute a job. That's sounds
>> like what you are looking for. You can find more information
>> on alerts in SQL Server books online.
>> -Sue
>> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >Hi
>> >I am trying to create a job to monitor the error log in sql server. I
>> >understand that the error types with their severity are in the table
>> >master..sysmessages.
>> >
>> >However, which table to I frequently query or monitor so that whenever the
>> >error which I am looking for with specific severity, when occurs the job will
>> >be fired?
>> >
>> >What table stores this information, so that I can run the query against it?
>> >
>> >Thanks
>>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >No I do not want to create alerts, I just want to find out where and in which
> >table sql server stores the errors that occur?
> >
> >
> >
> >"Sue Hoegemeier" wrote:
> >
> >> When the error occurs, you want it to be logged. That's how
> >> you know the error happened. Otherwise, they aren't really
> >> stored anywhere.
> >> You can create an alert that will watch for the error and in
> >> response to the error, it can execute a job. That's sounds
> >> like what you are looking for. You can find more information
> >> on alerts in SQL Server books online.
> >>
> >> -Sue
> >>
> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> >> <Pari@.discussions.microsoft.com> wrote:
> >>
> >> >Hi
> >> >I am trying to create a job to monitor the error log in sql server. I
> >> >understand that the error types with their severity are in the table
> >> >master..sysmessages.
> >> >
> >> >However, which table to I frequently query or monitor so that whenever the
> >> >error which I am looking for with specific severity, when occurs the job will
> >> >be fired?
> >> >
> >> >What table stores this information, so that I can run the query against it?
> >> >
> >> >Thanks
> >>
> >>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>> Not stored in any table.
>> -Sue
>> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >No I do not want to create alerts, I just want to find out where and in which
>> >table sql server stores the errors that occur?
>> >
>> >
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> When the error occurs, you want it to be logged. That's how
>> >> you know the error happened. Otherwise, they aren't really
>> >> stored anywhere.
>> >> You can create an alert that will watch for the error and in
>> >> response to the error, it can execute a job. That's sounds
>> >> like what you are looking for. You can find more information
>> >> on alerts in SQL Server books online.
>> >>
>> >> -Sue
>> >>
>> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> >> <Pari@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hi
>> >> >I am trying to create a job to monitor the error log in sql server. I
>> >> >understand that the error types with their severity are in the table
>> >> >master..sysmessages.
>> >> >
>> >> >However, which table to I frequently query or monitor so that whenever the
>> >> >error which I am looking for with specific severity, when occurs the job will
>> >> >be fired?
>> >> >
>> >> >What table stores this information, so that I can run the query against it?
>> >> >
>> >> >Thanks
>> >>
>> >>
>>