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 seq.nextval from within a pl/sql function

Hi,

Im using Oracle 9i and writing a PL/SQL function and i need to get the sequence.nextval within the function. I am getting an error saying sequence does not exist. I have tryed the following :

select mySeq.nextval
into v_myvar
from dual;

but I still get the same error message. Is there any way to get the next value in a sequence from within a PL/SQL function.

Any help would be greatly appreciated.
ThanksCREATE SEQUENCE myseq;

CREATE OR REPLACE FUNCTION fun_seq
RETURN NUMBER
AS
retval NUMBER;
BEGIN
SELECT myseq.NEXTVAL
INTO retval
FROM DUAL;

RETURN (retval);
END;
/

SELECT fun_seq FROM DUAL;

FUN_SEQ
---
1|||Hi,
Thanks. I dont need to create a sequence because the sequence that I am using (mySeq) already exists on the database.

Thanks|||Figured it out. It was a permissions issue.

how do I force connections to close before detaching a DB

Hi,
I need to detach a DB, but need to force any current connections to be
disconnected as I am getting an error saying that I cannot detach the DB as
it is being used.
What is the best way of doing this?
I am using DMO to detach at the moment.
ThanksALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
(Why are you detaching the database? Why are you detaching a database that
is in use?)
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:OTM174ZkFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to detach a DB, but need to force any current connections to be
> disconnected as I am getting an error saying that I cannot detach the DB
> as it is being used.
> What is the best way of doing this?
> I am using DMO to detach at the moment.
> Thanks
>

How do I fix an intenal NS SP error?

I am in the process of moving some Notification Services 2.0 applications to another server running SQL Server 2005 with Notification Services 2005. I have modified my old ADF files to get rid of the Notify() function as it is no longer supported in version 2005.

When I try to create a new instance using the old IDF file I get some errors, that I believe is internal Notification Services errors: The creation-script tries to create an SP named NSGet<MyAlertName>SMTPNotificationsDeveloperDefined but failed because of an undeclared scalar variable @.workItemId. This is not a variable I have created.

Looking at my old application, currently running fine on SQL Server 2000, I can not find the NSGet<MyAlertName>SMTPNotificationsDeveloperDefined SP, but one that is named NSGet<MyAlertName>SMTPNotifications instead. This SP takes the @.workItemId variable as a parameter, but the new SP (NSGet<MyAlertName>SMTPNotificationsDeveloperDefined) does not.

These stored procedures and the variable is as far as I know created by Notification Services so I don't see how I can correct the error? But as things are now, I'm not able to create the new instance because of this error. I have tried on both Management Studio and the NS command promt - but the same error occurs.

Anyone know how to fix this issue?

Please ignore or delete my post.

It seems that I have done some querying on the old SP which had @.workItemId via an <SQLExpression>.

Sorry for the inconvinience.

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 caused a deadlock?

I got an error message from my customer saying that once, during the night,
when executing a report it got an error message that stopped the application
the extended error was
Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Thats fine but It was an untrapped error that required a stop and start of
the application and its the first time it occured in seven years of running
this app. The app is a report, so everyting is read-only, there are no
record editing or creations involved, so how can it create a deadlock?
To answer these questions I think I need to know what the conditions were
that created the deadlock, I think that may be possible looking through the
transaction log of sql server, but how do I do that? When I looked under
administration logs in the sql server explorer all I saw was basically the
logs of the backups , but no place where I could find what transact sql
statement executed at what time with what results. Is there a way to find
this out or is that info lost after commandes execute?
Thanks for your help,
Bob
Here is a start
http://support.microsoft.com/default...b;en-us;832524
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks Denis
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1144345622.731620.232190@.v46g2000cwv.googlegr oups.com...
> Here is a start
> http://support.microsoft.com/default...b;en-us;832524
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

How do I find out what caused a deadlock?

I got an error message from my customer saying that once, during the night,
when executing a report it got an error message that stopped the application
the extended error was
Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Thats fine but It was an untrapped error that required a stop and start of
the application and its the first time it occured in seven years of running
this app. The app is a report, so everyting is read-only, there are no
record editing or creations involved, so how can it create a deadlock?
To answer these questions I think I need to know what the conditions were
that created the deadlock, I think that may be possible looking through the
transaction log of sql server, but how do I do that? When I looked under
administration logs in the sql server explorer all I saw was basically the
logs of the backups , but no place where I could find what transact sql
statement executed at what time with what results. Is there a way to find
this out or is that info lost after commandes execute?
Thanks for your help,
BobHere is a start
http://support.microsoft.com/default.aspx?scid=kb;en-us;832524
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1144345622.731620.232190@.v46g2000cwv.googlegroups.com...
> Here is a start
> http://support.microsoft.com/default.aspx?scid=kb;en-us;832524
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

How do I find out what caused a deadlock?

I got an error message from my customer saying that once, during the night,
when executing a report it got an error message that stopped the application
the extended error was
Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Thats fine but It was an untrapped error that required a stop and start of
the application and its the first time it occured in seven years of running
this app. The app is a report, so everyting is read-only, there are no
record editing or creations involved, so how can it create a deadlock?
To answer these questions I think I need to know what the conditions were
that created the deadlock, I think that may be possible looking through the
transaction log of sql server, but how do I do that? When I looked under
administration logs in the sql server explorer all I saw was basically the
logs of the backups , but no place where I could find what transact sql
statement executed at what time with what results. Is there a way to find
this out or is that info lost after commandes execute?
Thanks for your help,
BobHere is a start
http://support.microsoft.com/defaul...kb;en-us;832524
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1144345622.731620.232190@.v46g2000cwv.googlegroups.com...
> Here is a start
> http://support.microsoft.com/defaul...kb;en-us;832524
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

How do I find I am administrator?

Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>

How do i display a checkbox on a report?

Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?

Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||

I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.

Thanks anyway.

|||

I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.

thanks,

|||

please post the code for this issue.

thanks.

|||

textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2

Expression
Value =iif( Fields!....Value = "True", "P", "" )

How do i display a checkbox on a report?

Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?

Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||

I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.

Thanks anyway.

|||

I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.

thanks,

|||

please post the code for this issue.

thanks.

|||

textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2

Expression
Value =iif( Fields!....Value = "True", "P", "" )

How do i display a checkbox on a report?

Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?

Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||

I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.

Thanks anyway.

|||

I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.

thanks,

|||

please post the code for this issue.

thanks.

|||

textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2

Expression
Value =iif( Fields!....Value = "True", "P", "" )