How do I go to line number referenced in SQL error message

I get a SQL error message like:
Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
So I open up the stored proc with sp_helptext and select Edit | GoTo Line
205 in the results pane. Given the error, I would expect to see a linked
server reference on this line. However, I am on BEGIN of an IF statement
block.
How can I relate the actual stored proc line number with the line number
referenced in the error message?It is normally off by one or two lines, but begin counting at the create
proc command, NOT at the beginning of your script which might have (If
exists, drop, etc.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Dave" <dave@.nospam.ru> wrote in message
news:ePOQEdTQFHA.2348@.tk2msftngp13.phx.gbl...
> I get a SQL error message like:
> Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
> So I open up the stored proc with sp_helptext and select Edit | GoTo Line
> 205 in the results pane. Given the error, I would expect to see a linked
> server reference on this line. However, I am on BEGIN of an IF statement
> block.
> How can I relate the actual stored proc line number with the line number
> referenced in the error message?
>|||You can use the Debugger in Query Analyzer. Right-click on the SP in
the Object Browser and click Debug.
--
David Portas
SQL Server MVP
--

how do i get date into dd-mm-yy format?

i have the following in an insert stored proc

CONVERT(CHAR(8),GETDATE(),10)

but this inserts 03-06-06.

how do i get it to 06-03-06?

Cheers,
Craig

Probably by setting the machine you're running on to run in a time zone where this format is standard ?|||

my sp is as follows:-

IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC insert for YES consent where a ConsentDate is supplied
BEGIN
INSERT INTO tblStudyServices
(SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
ReadmissSixMonths, Consent, ConsentDate, CreationDate)
VALUES
(@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs, @.intFourRegularDrugsNo, @.blnHRD,
@.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate, CONVERT(CHAR(8),GETDATE(),10))
END

@.dteActualConsentDate is passed in as 14-03-06

if i then look in the table it has

CosentDate = 14-03-06
CreationDate = 03-06-06

i have tried SET_DATEFORMAT dmy

but still no luck

|||Datetime values are not stored in any special format. There are special allowed format and rules for inserting datetime. For output, it is the client application that formats the value to something that is human-readable. See http://www.karaszi.com/SQLServer/info_datetime.asp for more information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:3207581d-2ba8-4191-b788-7db50a967db0@.discussions.microsoft.com... >i have the following in an insert stored proc >
> CONVERT(CHAR(8),GETDATE(),10) >
> but this inserts 03-06-06. >
> how do i get it to 06-03-06? >
> Cheers,
> Craig >
>|||Did you see the reply I posted? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:acc61a6e-96e2-4412-9e4f-5ffdd7ab7b62@.discussions.microsoft.com...
> my sp is as follows:- >
> IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC
> insert for YES consent where a ConsentDate is supplied
> BEGIN
> INSERT INTO tblStudyServices
> (SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
> ReadmissSixMonths, Consent, ConsentDate, CreationDate)
> VALUES
> (@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs,
> @.intFourRegularDrugsNo, @.blnHRD,
> @.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate,
> CONVERT(CHAR(8),GETDATE(),10))
> END >
> @.dteActualConsentDate is passed in as 14-03-06 >
> if i then look in the table it has >
> CosentDate = 14-03-06
> CreationDate = 03-06-06 >
> i have tried SET_DATEFORMAT dmy >
> but still no luck >
>

How Do I Fix This Proc?

In a multi-user environment, this proc is expected to return distinct values
for concurrent users - but it doesn't, some duplicates are returned. I
thought that the 'BEGIN TRAN/COMMIT' would provide the required locking - I'
m
guessing that's where I went wrong. How do I fix this proc? The DDL for the
tables involved is included.
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId
WHERE (tableName = N'well')
BEGIN TRAN
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
CREATE TABLE dbo.tbTableMaxId
( tableName nvarchar(50) NOT NULL,
maxId int NOT NULL,
PRIMARY KEY (tableName)
)
CREATE TABLE dbo.well
( wellId int NOT NULL,
wellName nvarchar(50) NULL,
PRIMARY KEY (wellId)
)
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc."Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> In a multi-user environment, this proc is expected to return distinct
> values
> for concurrent users - but it doesn't, some duplicates are returned. I
> thought that the 'BEGIN TRAN/COMMIT' would provide the required locking -
> I'm
> guessing that's where I went wrong. How do I fix this proc? The DDL for
> the
> tables involved is included.
>
Basically strict serialization of the whole procedure will be required to
make this correct, so I hope you don't have to support concurrent inserts.
Which is the main reason why IDENTITY columns exist and you shouldn't try to
emulate them with custom code.
David
Here is your fix:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
BEGIN TRAN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END|||David,
Thank you for your response. I've modified your solution to avoid locking
the well table as follows:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
BEGIN TRAN
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
SELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
I'll be testing this next w and will post the results here.
Thanks again,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
"David Browne" wrote:

> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> Basically strict serialization of the whole procedure will be required to
> make this correct, so I hope you don't have to support concurrent inserts.
> Which is the main reason why IDENTITY columns exist and you shouldn't try
to
> emulate them with custom code.
> David
> Here is your fix:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> BEGIN TRAN
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
> WHERE (tableName = N'well')
>
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> ELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
>
>
>|||Hello Heinrich!
I think, it would a better idea if you try to lock only ,row for N'well'
table,
I thin you should use an rowlock hint
"Hal Heinrich" wrote:
> David,
> Thank you for your response. I've modified your solution to avoid locking
> the well table as follows:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
> BEGIN TRAN
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
> WHERE (tableName = N'well')
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> SELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
> I'll be testing this next w and will post the results here.
> Thanks again,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
>
> "David Browne" wrote:
>|||"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:8A55A9E5-C188-4DCF-BDD9-656AD810C31B@.microsoft.com...
> Hello Heinrich!
> I think, it would a better idea if you try to lock only ,row for N'well'
> table,
> I thin you should use an rowlock hint
>
Why? And what row do you propose locking?
David

How do I find the max row size..?

Hi,

How do I find the max row size for a particular table?
This was the error I recieved while execting my proc with the relevant
i/p I need to:

"cannot sort a row of size 8192, which is greater than the allowable
maximum of 8094"

I also understand that the max bytesize of a row is 8060 bytes.But
whtz this 8094?

TIA,
SeethaSeetha (seethakn@.yahoo.com) writes:
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?

I don't know, but I would guess this is a about a worktable that SQL
Server sets up internal, and for such a table the limit might be somewhat
higher.

Rather than scrutinizing tables, you should probably look at the query
that gives the error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||seethakn@.yahoo.com (Seetha) wrote in message news:<edf58070.0402162306.37ab77df@.posting.google.com>...
> Hi,
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?
> TIA,
> Seetha

Each datapage can actually 8192 bytes. This is because each kb is 1024
bytes. The Microsoft documentation specifies that a page header
contains 96 bytes of overhead. This is for keeping track of the page
within the system, kind of like a File Allocation Table on your hard
drive. This leaves 8096 bytes for data and row offsets (pg 247, Inside
SQL Server 2000). You get 8094 because each row has a 2 byte offset.
There is a decent article that goes into more detail at
"http://www.sqlservercentral.com/columnists/sjones/pagesize_printversion.asp"
if you're interested. Also, "Inside SQL Server 2000" by Kalen Delaney
is a great resource.

--Bryan

How do I find out what database Im using?

Hello all!

Quick question: How do I find out what database I'm currently using? I can't seem to find an appropriate system stored proc in BOL. For example:

use XYZ
go

[SQL statement(s) in question]
go

RESULTS:

database
----------
XYZ

Thanks in advance for your help!
Catselect db_name()|||Thanks a bunch!
Cat