Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?
Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>
|||Jerry Spivey wrote:

>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general location
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?

>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>
>

Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>|||Jerry Spivey wrote:
>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general location
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?
>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>>Hello,
>>I am getting an error in my .NET app and the error comes from a SQL Server
>>sproc. I am able to capture the line number. My question is how to find
>>that line? Do I start counting from CREATE PROCEDURE or from the AS
>>statement? Or elsewhere?
>>
>
>

Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>|||Jerry Spivey wrote:

>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general locatio
n
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?

>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>
>

Limiting size of a table in SQL server

hi,

im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?Using a scheduled job in SQL Server would likely be the easiest. Create the code to do what you want in a stored procedure, and then schedule the stored procedure.

In Enterprise Manager, expand the database till you see Management, expand that and right click on Jobs below it and select New Job to create and schedule the job.

Limit to one value in fld b for given value in field a

Okay, i have a problem, which would be easy in a well-normalized db,
but im working in a legacy app here, and can't normalize the db because
it would break the existing apps around it.
I have a manifest table which essentially acts as both a header & line-items
table.
There is a MANIFESTID field which serves to link the lineitems together onto
one
manifest. There is also an ACTION field, which serves to indicate whether
the
manifest is for delivery or for pickup.
I dont know if it really is this hard, or if im just brain-freezing here,
but here is what
i'm trying to do... i want to put in some sort of constraint so that for all
the lines which
have any given MANIFESTID, the ACTION field is the same on all of them.
Thanks in advance,
- Arthur Dent.Not sure if I fully understood your problem, but in SQL Server, you cannot
create a constraint declaratively which can enforce some logic based on
multiple rows.
A general alternative is to use a after trigger with a rollback statement.
This is somewhat prohibitive in high performance systems since the logic can
turn out to be complex depending on your schema. In rare cases, you might be
able to work it out with a scalar udf used in a check constraint as well.
Anith|||As example for clarification,
ACTION is declared with a check as either D or P or Delivery or Pickup.
I might have 5 rows where the ManifestID is <10>. What i want is to be able
to say
For All Rows Where MANIFESTID = 10,
ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
must be 'D' or ALL must be 'P'
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23Y4JaqG7FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Not sure if I fully understood your problem, but in SQL Server, you cannot
> create a constraint declaratively which can enforce some logic based on
> multiple rows.
> A general alternative is to use a after trigger with a rollback statement.
> This is somewhat prohibitive in high performance systems since the logic
> can turn out to be complex depending on your schema. In rare cases, you
> might be able to work it out with a scalar udf used in a check constraint
> as well.
> --
> Anith
>|||On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:

>As example for clarification,
>ACTION is declared with a check as either D or P or Delivery or Pickup.
>I might have 5 rows where the ManifestID is <10>. What i want is to be able
>to say
>For All Rows Where MANIFESTID = 10,
>ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
>must be 'D' or ALL must be 'P'
Hi Arthur,
This might be one of those cases where a UDF + CHECK constraint will
work:
CREATE FUNCTION dbo.CheckNoOthers
(@.ManifestID int, @.Action char(1))
RETURNS char(3)
AS
BEGIN
DECLARE @.Result char(3)
IF EXISTS (SELECT *
FROM YourTable
WHERE ManifestID = @.ManifestID
AND Action <> @.Action)
BEGIN
SET @.Result = 'No'
END
ELSE
BEGIN
SET @.Result = 'Yes'
END
RETURN @.Result
go
ALTER TABLE YourTable
ADD CONSTRAINT NoOthers
CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yah, that looks like it would probably work. Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qgq1o1pc00til4dvnk7h2jk7nssscm6vk2@.
4ax.com...
> On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:
>
> Hi Arthur,
> This might be one of those cases where a UDF + CHECK constraint will
> work:
> CREATE FUNCTION dbo.CheckNoOthers
> (@.ManifestID int, @.Action char(1))
> RETURNS char(3)
> AS
> BEGIN
> DECLARE @.Result char(3)
> IF EXISTS (SELECT *
> FROM YourTable
> WHERE ManifestID = @.ManifestID
> AND Action <> @.Action)
> BEGIN
> SET @.Result = 'No'
> END
> ELSE
> BEGIN
> SET @.Result = 'Yes'
> END
> RETURN @.Result
> go
> ALTER TABLE YourTable
> ADD CONSTRAINT NoOthers
> CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
> go
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)