Default for an int parameter in SP

I'm using a stored procedure that receives one parameter namely @.EmployeeID INT

but when I want to give this parameter a default value, my SP fails.

I did it like with a varchar where it works.

--
@.Employee INT = '%'

--

Is this correct or did I use a wrong syntac/wildcard?

Greetings,
GeoffINT is a numeric datatype only you should use digits to assign defaults.
If not use VARCHAR to assign such special chars.|||Isn't there a numeric default that I can use?

actually i use the SP to do the next thing.

If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?

Greetings,
Godofredo|||Only numerics are allowed as default for int.|||Can i define a range then?

like @.EmployeeID INT = [0-9]

so that all numbers can be received? or how precisely do I do this?

If no parameter is given I want al records returned.

Greetings,
Geoff|||CREATE PROCEDURE sp_myproc
@.emp_id int = 0
as

select * from my_table
where emp_id =
case @.emp_id when 0 then emp_id else @.emp_id end

when you call sp_myproc without specifying any parameters then all employees are retrieved.

Is that what you were asking?|||Yes indeed

Default data/log path

Hi,

Is there a SQL stored procedure or command to retrieve the server's default path for data and transaction log files?

Thanks.

Part of the Profiler trace created by opening the New Database dialog:

declare @.RegPathParams sysname

declare @.Arg sysname

declare @.Param sysname

declare @.MasterPath nvarchar(512)

declare @.LogPath nvarchar(512)

declare @.ErrorLogPath nvarchar(512)

declare @.n int

select @.n=0

select @.RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'

select @.Param='dummy'

while(not @.Param is null)

begin

select @.Param=null

select @.Arg='SqlArg'+convert(nvarchar,@.n)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @.RegPathParams, @.Arg, @.Param OUTPUT

if(@.Param like '-d%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.MasterPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-l%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.LogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-e%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.ErrorLogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

select @.n=@.n+1

end

SELECT

@.MasterPath AS [MasterDBPath],

@.LogPath AS [MasterDBLogPath]

Decrypting and Encrypted Stored Procedure

Hello,
In SQL 2000,
I have created a Stored Procedure as follows,

Code Snippet

CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer

Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.

Code Snippet

sp_helptext mytest

But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?

You won’t retrieve back the source using sp_helptext /SMO, when you say WITH ENCRYPT.

You have to maintain your procedure source (like in File system or VSS). The encryption is very useful when you launch a product along with your database to public. So they can see the table schema but they can’t change or edit or view your programmability source code.

DecryptByCert performance

I have bunch of encrypted rows in the table and have stored procedure to select those rows.
It looks like this
SELECT CAST(DecryptByCert(Cert_ID('CertId'), field1) AS VARCHAR) AS f1,
CAST(DecryptByCert(Cert_ID('CertId'), field2) AS VARCHAR) AS f2,
CAST(DecryptByCert(Cert_ID('CertId'), field3) AS VARCHAR(255)) AS f3
FROM [table]

This stored procedure takes really long time even with hundreds of rows, so I suspect that I do something wrong. Is there any way to optimize this stored procedure?

Encryption/decryption by an asymmetric key (i.e. certificate) is much slower than when using a symmetric key.The recommended way to encrypt data is by using a symmetric key (or set of keys if you prefer) for protecting the data (i.e. AES or 3DES key), and protect these key using the certificate.

Another limitation you should consider is that asymmetric key encryption in SQL Server 2005 is limited to only one block of data. This means that the maximum amount of plaintext you can encrypt is limited by the modulus of the private key. In the case of RSA 1024 (in case you are using SQL Server 2005-generated certificates), the maximum plaintext is 117 bytes.

I am also including an additional thread from this forum that talk about this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=114314&SiteID=1

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Decode Base64 to Image!

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

Convert.FromBase64String
You probably want to take a look at that. You could possibly create a memory stream from the byte array and then create a bitmap from the stream.

|||

Thank you for your help but as I've mentioned I have to extract images in a stored procedure, so I have to decode them in it too.

Sorry for my late reply.

|||hwat about usin a CLR procedure if you have SQL2k5 already. YOu could use the mentioned class in the last post. That should work for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Declare cursor for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

Decimal.MinValue throw OverflowException

I'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure.

But, I receive this stack trace exception:

System.OverflowException: Conversion overflows.
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?

From the online help:

The value of this constant is negative 79,228,162,514,264,337,593,543,950,335.

How many digits did you set the decimal database field to accept?

|||

Hi,

Please check if the decimal scale and precision are set correctly in both .NET and database.

The should be match, so the data conversion can work properly.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored pro
cedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!
Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegr oups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

decimal return on stored procedure.

There's something thats driving me nuts. I have a stored procedure that
returns the result of dividing two different stored prodecures. Here's the
code in QA:
declare @.trhhp decimal(9, 2)
declare @.acp decimal(9, 2)
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
select @.acp / @.trhhp
.003551038825
(1 row(s) affected)
...now when I place it into a stored procedure:
CREATE PROCEDURE [dbo].[PERCERNT_MKT_PENETRATION] AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
return (@.acp / @.trhhp)
GO
...and then execute the stored procedure, I seems to round the value to
zero. Any idea why this might be? I'm using decimal values when I can...
DECLARE @.RC decimal
-- Set parameter values
EXEC @.RC = [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION]
select @.rc
0
(1 row(s) affected)
Thanks for any help!
-- CaseyHow would I set up the output paramater?
I tried changing the sp...
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
...and then tried retriving the value in QA:
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
... still returns 0... I imagine I'm doing something wrong...
"Nigel Rivett" wrote:
> A return value is always an integer - you need to use an output parameter
(or
> result set).
> "Casey" wrote:
>|||ah! That would be the problem then. How to I set an output pramater? I tried
recreating the SP:
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
Then, when I try to grab that value out of the sp, I still only get zero...
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
I'm doing something wrong...
"KH" wrote:
> The return value of an SP is a success/failure indicator. As Nigel said us
e a
> recordset or output param.
>
> "Casey" wrote:
>|||> @.result decimal OUTPUT
Try setting scale / precision for your decimal parameter.|||I thought's that's what I did...
"Aaron Bertrand [SQL Server MVP]" wrote:

> Try setting scale / precision for your decimal parameter.
>
>|||Be careful with the decimal data type. Scale is 0 by default => specifying
decimal without precision and scale is equal to decimal(18, 0). And that's
not equal to decimal(9, 2) as you declared it in one of the cases.
ML|||>I thought's that's what I did...
Maybe you're not sure what precision/scale are?
You have:
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
You should try
@.result DECIMAL(9,2) OUTPUT
AS
DECLARE @.trhhp DECIMAL(9,2),
@.acp DECIMAL(9,2)
What does trhhp mean? I certainly can't pronounce it, never mind decipher
what it might stand for...
A

Decimal Digits Lost When Using Decimal(9,2) Parameter

Hello,
I am trying to use a decimal type parameter in my stored procedure.
When I pass any decimal value to it (for example: 12.34), the decimal
part is always truncated off. I am left with only 12 as the value that
get put into my table. I have checked the obvious...the datatype is
decimal in the procedure. The data type for the column is decimal(9,2)
in my table. Any idea about what is going on here?Can you show some code so we can repro/diagnose?
<joey.powell@.topscene.com> wrote in message
news:1125598102.549921.46630@.g47g2000cwa.googlegroups.com...
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
>|||joey.powell@.topscene.com wrote:
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
You are proably using the value in a calculation that includes an
integer data type. Make sure all operations use decimal as the data
type.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joey,
The datatype DECIMAL is shorthand for DECIMAL(18,0). Change
the data type of the procedure parameter to DECIMAL(9,2).
Steve Kass
Drew University
joey.powell@.topscene.com wrote:

>Hello,
>I am trying to use a decimal type parameter in my stored procedure.
>When I pass any decimal value to it (for example: 12.34), the decimal
>part is always truncated off. I am left with only 12 as the value that
>get put into my table. I have checked the obvious...the datatype is
>decimal in the procedure. The data type for the column is decimal(9,2)
>in my table. Any idea about what is going on here?
>
>

Debugging?

Hi,
Is there a way to debug an SQL Server stored procedure? I call a number of sp's from my .net app and need a way to step through them.
Thank you,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtskasqldebuggingexample.asp
|||Thanks. Your link also pointed to the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vchowapplicationdebugging.asp
that shows, supposedly, how to debug a stored procedure inline with your app. This is what I'm looking for, because I need to be able to see what the stored procedure is receiving in it's parameters. I followed the steps in the link above, put a breakpoing in the stored procedure and another one in my app just before the stored procedure, and, contrary to the claims of the article, I was not able to step into the procedure. Any help getting this to work would be appreciated!
Thanks.|||Is the SQL Server on your local machine? If not you have toinstall the remote debugging components. This article tells youhow to install and configure remote debugging (as well as various othercool debugging tips):
http://www.dbazine.com/sql/sql-articles/cook1
|||It is on my local machine.

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>

Debugging tool for SP

After writing a Stored Procedure(which is giving correct reasult) how can I go for optimizing it?Is there any tool to debug a stored procedure?You should optimize the sql statements _before_ creating the sp.

Debugging stored procedures

I have complete error handling and printing the error after every insert or update statements or after calling another procedure.

But somehow when executing the proc it is not printing the error.

The query analyzer shows a general message 'Query batch completed with errors'

All the logic seems to be working properly, but this message is bothering me. Why is this message displayed if everything is run correctly [or] is something wrong ?

Example:

[code]

/*************************************************************
** Error Handling
**************************************************************/

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

[/code]

IF (@.@.error <> 0) --this should be @.@.error @.error from previous statement is unreliable
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

|||

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.@.error

if (@.error <> 0)

begin

end

|||

QUOTED:

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.error + (other select clause ) --<-- what if the error lies in here

if (@.error <> 0)

begin

end

|||

thanks joeydj,

ok i see...

@.@.error is for select statements too ?

i can check the selects, but its a standard select as shown above and there seems to be no error there.

|||

thats a wild guess anyway.

|||

can you please check if this line is valid

,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)

|||

still not getting it try this. this one should do it.

hahaha

declare @.error int

select @.error=0

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

debugging stored procedures

I have a user that is db_owner for the a database in development on a SQL 7
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?I hope you have DB_DDLAdmin permissions on the database,
Just add your user account in the master database and
grant em Execute permissions to SP_SDIDEBUG system
procedure
HTH
Saleem Hakani
>--Original Message--
>I have a user that is db_owner for the a database in
development on a SQL 7
>SP 4 server. The user is attempting to debug a stored
procedure and getting
>the error:
>Server: Msg 229, Level 14, State 5, Procedure
sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE
permission denied on
>object 'sp_sdidebug', database 'master', owner 'dbo'.
>I saw this error listed on the support site at
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;328173&Product=sql2k
>.. The site says:
>This behavior is a design change in SQL Server 2000 SP3
to enhance security.
>This design change includes the following changes:
> a.. A database users can only step into stored
procedures that they own.
> b.. A database owner (DBO) can debug any stored
procedure in the database
>that the DBO owns. (A DBO owns the database and,
therefore, all its stored
>procedures.)
> c.. Members of the SysAdmin server role can debug any
stored procedure in
>any database on the server. (A member of the SysAdmin
server role owns the
>server and, therefore, all its databases.)
>For more information about Transact-SQL Debugging, see
the "Using
>Transact-SQL Debugger" and "Troubleshooting the Transact-
SQL Debugger"
>topics in SQL Server Books Online.
>Does the user need to be the dbo ( creator) of the
database to get the
>debugger to work and not just a member of db_owner role
(this does not
>appear to work)? Is there a work around so that my user
can debug his
>stored procedures without me having to debug every stored
procedure for the
>several development servers in house?
>
>.
>|||Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> > I have a user that is db_owner for the a database in development on a
SQL
> 7
> > SP 4 server. The user is attempting to debug a stored procedure and
> getting
> > the error:
> >
> > Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
> on
> > object 'sp_sdidebug', database 'master', owner 'dbo'.
> >
> > I saw this error listed on the support site at
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> > . The site says:
> >
> > This behavior is a design change in SQL Server 2000 SP3 to enhance
> security.
> > This design change includes the following changes:
> > a.. A database users can only step into stored procedures that they
own.
> > b.. A database owner (DBO) can debug any stored procedure in the
> database
> > that the DBO owns. (A DBO owns the database and, therefore, all its
stored
> > procedures.)
> > c.. Members of the SysAdmin server role can debug any stored procedure
> in
> > any database on the server. (A member of the SysAdmin server role owns
the
> > server and, therefore, all its databases.)
> > For more information about Transact-SQL Debugging, see the "Using
> > Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> > topics in SQL Server Books Online.
> >
> > Does the user need to be the dbo ( creator) of the database to get the
> > debugger to work and not just a member of db_owner role (this does not
> > appear to work)? Is there a work around so that my user can debug his
> > stored procedures without me having to debug every stored procedure for
> the
> > several development servers in house?
> >
> >
> >
>

debugging stored procedures

I have a user that is db_owner for the a database in development on a SQL 7
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/defaul...3&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/defaul...3&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
SQL
> 7
> getting
> on
>
http://support.microsoft.com/defaul...3&Product=sql2k
> security.
own.
> database
stored
> in
the
> the
>

debugging stored procedure with SSM studio?

Hi,
I know that it 's possible to debugg stored procedure with VS 2005 and is
there a way to do it with SQL Server Management Studio?
Thanks in adavance,
Laurent
Laurent,
check this link
http://msdn2.microsoft.com/en-us/library/ms241871
Markus
|||Sure, but I would like to work just within SQL server Management Studio.
<m.bohse@.quest-consultants.com> a crit dans le message de news:
1132055397.773192.152280@.z14g2000cwz.googlegroups. com...
> Laurent,
> check this link
> http://msdn2.microsoft.com/en-us/library/ms241871
> Markus
>