name of the calling stored proc from within a trigger

How do you get it from the system?
Thanks in advance,
Koni.Hi
You can't. It is not available as it might have been T-SQL, a SP, or a
Trigger that caused the modification.
Best you can do is a DBCC INPUTBUFFER(spid) to find out what was executed
(portions of the text)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Koni Kogan" wrote:

> How do you get it from the system?
> Thanks in advance,
> Koni.
>|||See if this helps:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Koni Kogan" <kkogan@.haiint.com> wrote in message
news:OyjzomLUFHA.3344@.TK2MSFTNGP10.phx.gbl...
How do you get it from the system?
Thanks in advance,
Koni.|||Narayana Vyas Kondreddi wrote:

> See if this helps:
> http://vyaskn.tripod.com/tracking_s...by_triggers.htm
Cool! I think this is exactly what I was looking for.
Thanks!

Mysteriously disappearing stored proc

Hi,
I have an issue with a stored procedure that is disappearing periodically.
It is called from the Agent every minute, and runs just fine for long
periods of a time, often more than a w at once. Then the stored
procedure will be deleted and I will see an entry in the event log for every
time the agent calls it that the stored proc is missing.
It is always the same stored proc. There is another stored proc that also
runs once per minute, and it is never missing.
Does anyone have a clue as to what could cause this, or perhaps some
recommendations for troubleshooting steps I can take in order to find this
out.
Thanks!
Ray ReavisSounds like someone's playing an april fools gag on you.
Response from Microsoft: "It's a feature!"
Sorry, couldn't resist... ; )
"Ray Reavis" wrote:

> Hi,
> I have an issue with a stored procedure that is disappearing periodically.
> It is called from the Agent every minute, and runs just fine for long
> periods of a time, often more than a w at once. Then the stored
> procedure will be deleted and I will see an entry in the event log for eve
ry
> time the agent calls it that the stored proc is missing.
> It is always the same stored proc. There is another stored proc that also
> runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find this
> out.
> Thanks!
> Ray Reavis
>
>|||Ray Reavis wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing
> periodically. It is called from the Agent every minute, and runs just
> fine for long periods of a time, often more than a w at once.
> Then the stored procedure will be deleted and I will see an entry in
> the event log for every time the agent calls it that the stored proc
> is missing.
> It is always the same stored proc. There is another stored proc that
> also runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find
> this out.
> Thanks!
> Ray Reavis
Some process is obviously dropping the procedure. What user accounts
have rights on the database to drop the procedure? I wouldn't think too
many processes have admin rights or DROP PROC rights on the production
server.
David Gugick
Imceda Software
www.imceda.com|||Yeah, I agree with David. Use Profiler to find the statement dropping
the SP. You may take the stored procedure "create date" as a hint if
the sp is being dropped and recreated.
Aramid
On Wed, 6 Apr 2005 21:08:37 -0400, "David Gugick"
<davidg-nospam@.imceda.com> wrote:

>Ray Reavis wrote:
>Some process is obviously dropping the procedure. What user accounts
>have rights on the database to drop the procedure? I wouldn't think too
>many processes have admin rights or DROP PROC rights on the production
>server.|||David,
Thanks for the tip. I'm not a dba so some of these things don't come
naturally to me. I'm using the profiler to run a trace on all object
deletions, so hopefully that will turn something up.
As far as permissions are concerned, I don't see a place in SQL2000 where it
specifically shows DROP PROC rights. I looked on the permissions of the
database itself and saw Create Proc rights, but couldn't find drop proc
rights. I'm not sure how much that would help anyway since my company
doesn't have control over this server. We're using windows authentication
rather than SQL Server accounts so I presume administrators on that server
will have those permissions.
Thanks,
Ray
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u#peO5wOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Some process is obviously dropping the procedure. What user accounts
> have rights on the database to drop the procedure? I wouldn't think too
> many processes have admin rights or DROP PROC rights on the production
> server.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Ray Reavis wrote:
> David,
> Thanks for the tip. I'm not a dba so some of these things don't come
> naturally to me. I'm using the profiler to run a trace on all object
> deletions, so hopefully that will turn something up.
> As far as permissions are concerned, I don't see a place in SQL2000
> where it specifically shows DROP PROC rights. I looked on the
> permissions of the database itself and saw Create Proc rights, but
> couldn't find drop proc rights. I'm not sure how much that would
> help anyway since my company doesn't have control over this server.
> We're using windows authentication rather than SQL Server accounts so
> I presume administrators on that server will have those permissions.
Create Procedure rights should do it. As far as permissions, they are on
the SQL side, even with Windows Authentication.
This will show you everyone (excluding administrators and other
fixed-server roles who may have inherent rights) who have explicit
rights to create procs.
select user_name(uid) as "Has Create Proc Rights", * from
dbo.sysprotects
where action = 222 -- create proc
and protecttype != 206 -- Deny
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#YL3RW4OFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ray Reavis wrote:
> Create Procedure rights should do it. As far as permissions, they are on
> the SQL side, even with Windows Authentication.
> This will show you everyone (excluding administrators and other
> fixed-server roles who may have inherent rights) who have explicit
> rights to create procs.
> select user_name(uid) as "Has Create Proc Rights", * from
> dbo.sysprotects
> where action = 222 -- create proc
> and protecttype != 206 -- Deny
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks! I've saved that query in my bag-o-tricks folder. It didn't return
any rows, which is not a surprise. I suspected it was all built-in accounts
that were doing the dirty deed. So far it's been two days since the last
missing sp. I'll be satisfied if it stops but it will kill me to not know
what is going on.
We have the same software running at a lot of businesses nationwide with no
problems such as this -- only this one customer has this issue. It makes us
look bad if we can't prove what is going on even when we're pretty certain
it's not our code causing the problem.
-- Ray

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

mysterious corruption of stored procedures

Hi everyone,

It looks like a mystery, but I hope there should be some explanation to
the issue I experience. Once in a blue moon a random stored procedure
stops working the way it was designed. The stored procedure code looks
unchanged. Recompiling, altering the code do not help. It looks like it
is simply does not execute some part of it or does not run at all.
However it returns no errors.
One time a procedure entered into infinite loop and almost hang the
whole server.

When I copy procedure code and save it under different name, it works
as designed. But nothing helps with existing procedure. The only way
how to fix it is to completely drop and recreate.

The problem is, that you usually have to do it in the middle of the
business day after you spent few hours trying to realize what went
wrong before you realize that you got another mysterious corruption. Of
cause I have no clue of how to detect such things in advance and to
prevent them from occurring in the future.

I can guarantee that the SQL code in those procedures was absolutely
bug free, fully tested and was working fine for a long time.

For the first time I thought that internal compiled code might corrupt.
In this case altering or recompiling should help.
I also thought about execution plan, but it should be also fixed by
doing things above.
DBCC checkdb does not find any errors
The issue never goes away until stored procedure is manually dropped
and recreated with the same SQL code.

So, I'm asking all if someone experienced something similar and can
explain how to prevent it, please share the knowledge. I would
appreciate any type of help.

Thank you.Sergey (afanas01@.gmail.com) writes:
> It looks like a mystery, but I hope there should be some explanation to
> the issue I experience. Once in a blue moon a random stored procedure
> stops working the way it was designed. The stored procedure code looks
> unchanged. Recompiling, altering the code do not help. It looks like it
> is simply does not execute some part of it or does not run at all.
> However it returns no errors.
> One time a procedure entered into infinite loop and almost hang the
> whole server.
> When I copy procedure code and save it under different name, it works
> as designed. But nothing helps with existing procedure. The only way
> how to fix it is to completely drop and recreate.

Have you ruled out the possibility there are two procedures with the same
name, but different schema? (= different owner in SQL 2000?) Or for some-
thing really exotic, what about numbered procedures? (That is, some_sp;2)

If you for instance change the code of the corrupted procedure to say
"PRINT 'Hello!" and nothing else, does print the message, or does it
continue what it used to do?

When this happens a SELECT * FROM sysobjects WHERE name = 'procname'
may reveal something.

--
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|||No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usually when you have a production problem everybody whant
a) have it fixed ASAP and only then b) provide them with reasonable
explanation. I have a problm with b) ;-)|||Sergey (afanas01@.gmail.com) writes:
> No numbered procedures, no different schema/owner. Everything is "plain
> vanilla". Unfortunately, I did not have chance to play with corrupted
> procedures.Usually when you have a production problem everybody whant
> a) have it fixed ASAP and only then b) provide them with reasonable
> explanation. I have a problm with b) ;-)

I would still encourage you to examine sysobjects next time it happens.
Maybe there is something in your system, you did not know of. :-)

--
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|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again

My Stored Procedure hell!

Ok, I posted here recently and received helpful replies which allowed me to work around a problem. The original question was posted here:

http://forums.asp.net/t/1112669.aspx

But because I'm learning both asp.net 2.0 AND vb 2005 I sort of want to get to the bottom of stuff. I've found out what was going wrong, but I don't understand it.

The problem related to retrieving an output parameter to a stored procedure. I was adding the parameter to the command object I was using as follows:

cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))

but it wasn't working (ie I wasn't seeing the return value). A helpful poster's work around was to instead do this:

Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)
pMemberId.Direction = ParameterDirection.Output
cmd.Parameters.Add(pMemberId)

Having poked around some more, I've discovered that if I use the original code and then type:

?cmd.Parameters("@.memberid").Direction

I get the value:

Input {1}

This even happens if I explicitly use 2 instead of Data.ParameterDirection.Output

Can anyone explain why this is happening? What's the point of allowing me to pass a parameter into a constructor if it's just going to ignore it?

This just in...

Ah. Might have found the answer...not sure.

I looked up the constructors for SqlParameter, and found that there are 7. As I was typing the parameters it popped up with Direction for the 4th one, so I thought `that's easy - it's output` and selected it. I then closed the brackets and that was that. But I think what might be happening is that the compiler is saying "ah - 4 parameters? That means the fourth parameter is a string called `sourcecolumn`".

http://msdn2.microsoft.com/en-us/library/f38c3x2s(VS.80).aspx

At the point that you're entering the 4th parameter you can click on little arrows to cycle through the 3 remaining possible contructors. To be able to enter the direction requires me to enter a load more parameters.

Perhaps it's getting confused and instead of raising an error about the fact that the number 2 isn't a string it's instead turning my value of 2 into a 1? I've turned on Option Strict but it hasn't made any difference.

|||

Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?

I ran into this problem a week or so ago, and the something along the following lines solved it:

SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))

...//other param code here

p.Value = 666

...//execute

myIntVar = Integer.Parse(p.value.ToString())

Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).

HTH

Aaron

|||

aaroncollett:

Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?

I ran into this problem a week or so ago, and the something along the following lines solved it:

SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))

...//other param code here

p.Value = 666

...//execute

myIntVar = Integer.Parse(p.value.ToString())

Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).

HTH

Aaron

With respect, as explained above, the problem is nothing to do with the call to execute the stored procedure - it's about the way the SqlParameter object is created. Do you have your exact code to hand - it looks like you've sort-of copied mine there! I imagine that part of your

...//other param code here

code is to set the direction, because the way your code is looking makes me think you'll have the same problem as me. Immediately after you've called the constructor to SqlParameter, try:

?p.direction

and I bet you'll see 1 (input) instead of 2(output) ! You can call the SqlParameter constructor with just the name of the parameter and the type, and then set the direction with

p.Direction = ParameterDirection.Output

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

|||

Here is some suggested code for you to review. I'll comment inline:

1-- I reviewed your stored procedure from the previous post2-- and came up withthis code to execute it3int memberID = -1;45using( SqlConnection conn =new SqlConnection( connString ) )6{7using( SqlCommand cmd =new SqlCommand("[dbo].[ValidateMemberUsrPwd]", conn ) )8 {9 cmd.CommandType CommandType.StoredProcedure;1011 -- we are adding parametersin order of the12 -- stored procedure signature13 -- not required, but good practice14 cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 16 ).Value = UserNameVariable;15 cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 16 ).Value = PasswordVariable;1617 --this parameter, not onlyis it an output parameter18 -- but by the signature of the stored procedure itis19 -- a'required' parameter. So, it needs avalue.20 --if you want it to not be required, then give it adefault21 --in the signature. Something like22 -- @.memberidint = -1 OUTPUT23 SqlParameter pMemberID =new SqlParameter("@.memberid", SqlDbType.Int );24 pMemberID.ParameterDirection = ParameterDirection.Output;25 pMemberID.Value = DBNull.Value;26 cmd.Parameters.Add( pMemberID );2728 conn.Open();29 cmd.ExecuteNonQuery();3031 memberID = (int) pMamberID.Value;32 }33}

|||

poldie:

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.

|||

davidpenton:

poldie:

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.

No, I mean why isn't the compiler complaining at design time that I'm passing a Direction parameter to a constructor which requires either a string, or a direction followed by several other parameters? I've narrowed down this problem to the point where I no longer need a stored procedure as part of the test. I set the direction as Output and immediately after that, with a breakpoint on the next line, I can take a look at the value of the direction I've just stored and it's been changed to Input!

|||

Ahh! Sorry about that. You are not actually setting the ParameterDirection at all in your scenario. So, I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:

public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);

So, it would be something like this:

cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbType.Int, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );

|||

davidpenton:

Ahh! Sorry about that. I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:

public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);

So, it would be something like this:

cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbTypeInt, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );

Exactly! I did try to enter some of the extra parameters after direction but I couldn't see an end to it - they just went on forever!

So: What I Learnt Today - don't trust the little pop-up `enter some parameters for the method` things because they'll not perform any type checking to ensure that you pass enough parameters of the correct type. Instead, they'll be ordered in alphabetical order. Check that what you're trying to pass is valid for what you want by typing the opening bracket for your parameters and then looking at the whole set of possible signatures. The .Net compiler silently casts ints to strings without so much as a warning, even in Option Strict mode. Be good to yourself...and each other.

my problem in detail

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again

my output parameter is being treated as an input parameter...why

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems...

ASP Code...

<asp:SqlDataSourceID="SqlDS_Form"runat="server"ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>"SelectCommand="PTN_sp_getFormDD"SelectCommandType="StoredProcedure"OldValuesParameterFormatString="original_{0}"UpdateCommand="PTN_sp_Form_Update"UpdateCommandType="StoredProcedure"OnUpdated="SqlDS_Form_Updated"OnUpdating="SqlDS_Form_Updating"DeleteCommand="PTN_sp_Form_Del"DeleteCommandType="StoredProcedure"OnDeleting="SqlDS_Form_Updating"OnDeleted="SqlDS_Form_Deleted">
<UpdateParameters><asp:ControlParameterControlID="GridView1"Name="DescID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="GridView1"Name="FormNum"PropertyName="SelectedValue"Type="String"/><asp:ParameterName="original_FormNum"Type="String"/><asp:ParameterDirection="InputOutput"size="25"Name="RetVal"Type="String"/></UpdateParameters>

<DeleteParameters>
<asp:ParameterName="original_FormNum"Type="String"/>
<asp:ParameterDirection="InputOutput"Size="1"Name="NewRetVal"Type="Int16"/>
</DeleteParameters>
</asp:SqlDataSource>

Code Behind:

protectedvoid SqlDS_Form_Deleted(object sender,SqlDataSourceStatusEventArgs e)
{
if (e.Exception ==null)
{ string strRetVal = (String)e.Command.Parameters["@.NewRetVal"].Value.ToString();
.....................

Stored Procedure:

CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (@.original_FormNumnvarchar(20), @.NewRetValINT OUTPUT )ASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @.stoptransvarchar(5), @.AvailFoundint, @.AssignedFoundintSet @.stoptrans ='NO'/* ------- Search PART #1 ---------------- */SET @.AvailFound = (SELECTCOUNT(*)FROM dbo.AvailableNumberWHERE dbo.AvailableNumber.FormNum = @.original_FormNum )SET @.AssignedFound = (SELECTCOUNT(*)FROM dbo.AssignedNumberWHERE dbo.AssignedNumber.FormNum=@.original_FormNum )IF @.AvailFound > 0OR @.AssignedFound > 0/* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/--This means the delete can't happen.........BEGINIF @.AssignedFound > 0AND @.AvailFound = 0BEGINSET @.NewRetVal = 1ENDIF @.AssignedFound > 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 2ENDIF @.AssignedFound = 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 3ENDENDELSEBEGINDELETE FROM dbo.FormWHERE dbo.Form.FormNum=@.original_FormNumSET @.NewRetVal = 0--Successful deletionENDGO

------------------

When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

Not sure whether it will help, but try to set the Direction of NewRetVal to Output instead of IntputOutput:

<asp:ParameterDirection="Output"Size="1"Name="NewRetVal"Type="Int16"/>

You may also take a look at this article:

Input and Output Parameters, and Return Values

|||It could also be because the type doesn't match. A SQL int is 32-bits, and you've specified a 16-bit int.

my money cast no longer works in sql 2005

Hi

In a SQL 2000 stored proc I have the following statement to return a formatted price without the decimal place and cents

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money(12,2)),1)

This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid attributes specified for type 'money')
By reading this forum I can see that I can use this instead:
select '$' + parsename(convert(varchar,convert(money,@.price),1),2)


I'm wondering why it doesn't work any more in SQL 2005 - are these differences documented anywhere ?

thanks
Bruce

It is because you are specifying precision and scale for money (money(12,2)) which is incorrect/invalid. Older versions allowed such syntaxes due to bug in parser and this has been corrected now. Do:

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money),1)

|||> This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid
> attributes specified for type 'money') Where is there any documentation that states that MONEY has precision or scale? The SQL Server 2005 error message is correct. SQL Server 2000 was a little bit more lenient and just ignored the attributes, but don't blame the tool that you misused. :-) I would MUCH RATHER have this kind of thing break on me, than just ignore my specifications and lull me into believing that it is working correctly, when it is in fact not. DECLARE @.PRICE MONEY SET @.Price = 476.2354 SELECT CAST(@.Price AS MONEY(12,2)) Now, try instead: SELECT CONVERT(MONEY(12,2), @.Price) Hey, they got the error message right for CONVERT, but not for CAST. Server: Msg 291, Level 16, State 1, Line 3 CAST or CONVERT: invalid attributes specified for type 'money'
> By reading this forum I can see that I can use this instead:
> select '$' + parsename(convert(varchar,convert(money,@.price),1),2) Why do you need to convert to money? What is the original data type? If it is decimal or numeric, then the following will work fine: SELECT '$' + RTRIM(CONVERT(DECIMAL(12,2), @.price)) Or, just SELECT @.price And let the client/presentation tier add a dollar sign and round to the correct number of decimal places. I've actually never ended up using MONEY or SMALLMONEY in any database application I've built. Several reasons are listed here: http://www.aspfaq.com/2503
> I'm wondering why it doesn't work any more in SQL 2005 - are these
> differences documented anywhere ? No, that's the problem with having a loose syntax checker in previous versions, that allowed invalid things to work. I'm not even sure that Microsoft is aware of all of these obscure things that just happened to work in previous versions. If Microsoft were to document all of the undocumented behaviors that change, where should they put it? How would people find it? Who is going to tell them about all of the undocumented things that we all know about, and should be documented? There are some other examples, though I know they are aware of these, e.g. CREATE VIEW ... AS SELECT TOP 100 PERCENT ... ... ORDER BY This still passes the syntax checker, and you can still create the view, but now the lazy "SELECT * FROM viewname" no longer guarantees . Since the behavior people relied on was undocumented, . Another is CREATE TABLE dbo.foo ( col1 INT, col2 CHAR(1), ) This passes the syntax checker in both versions and works, even though there is a syntax error (dangling comma). A

my issues with FT Search

Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
- FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
- Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
- Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
- Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!On 25 Jul 2005 18:35:20 -0700, barak.benezer@.gmail.com wrote:

>Hi, I am trying to implement a global full text search on our SQL
>Server. Our app has several entities that are stored in the DB. I would
>like to be able to search for 'John Doe' and get results in all types
>of entities. Problem is:
(snip)
>I would greatly appreciate any suggestions/tips/info/workaround.
Hi barak,
I think that your message will attract more answers if posted to the
group microsoft.public.sqlserver.fulltext, where the FTS experts prefer
to hang out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks for your reply. I posted it there and got an answer.

My guess is stored procedures

Greetings All!
I just started playing with MS SQL and was wondering how i could
possibly do the following for example.
I do a 'SELECT source FROM history' and that gives me a list of
sources. Now i want to run a SELECT for each source i received from
the previous SELECT.

For further clarity, in programming terms, it would be just like
looping through an array.

Thanks Guys,
PatricePlease define what you mean by "run a SELECT for each source". It might help
to post the DDL (CREATE TABLE statements) and some sample data (INSERT
statements) for your table(s). Also, show the end-result you would want to
get from that sample data.

In general in SQL you should try to minimise procedural code such as loops.
SQLServer is optimised for set-based queries. If you want to SELECT a set of
rows for each "source" then you can probably write that query as a JOIN to
one or more tables.

For example (from the Pubs database). To retrieve all the Titles for each
Author:

SELECT A.au_fname+' '+A.au_lname AS author, T.title
FROM Authors AS A
JOIN TitleAuthor AS B
ON A.au_id = B.au_id
JOIN Titles AS T
ON B.title_id = T.title_id
ORDER BY A.au_lname, A.au_fname

--
David Portas
SQL Server MVP
--|||Hi David,
Sorry for the lack of clarity.

My plan is to detect if any users login from more than 3 sources.

I have a history table
CREATE TABLE history(client_id int, when datetime, source varchar(20))

After some inserts a SELECT * FROM history looks like

-------------
|client_id | when | source |
-------------
|1010 | <date> | 202.22.21.33 |
-------------
|1818 | <date> | 192.10.22.31 |
-------------
|1542 | <date> | 222.452.1.36 |
-------------
|1010 | <date> | 192.22.21.33 |
-------------
|1010 | <date> | 242.22.21.33 |
-------------
|1010 | <date> | 256.22.21.33 |
-------------

so from this table i would to a
SELECT DISTINCT client_id from history (if thats the syntax) this would
return
----
|client_id |
----
|1010 |
----
|1818 |
----
|1542 |
----

Now from these values i want do a
SELECT count(DISTINCT source) from history where client_id = 1010

this would return 4. Since client_id 1010 logged in from 4 different
sources.
Then i want to run the same query with client_id = 1818 and so on. I.e
loop through the client ids.

How can i do this in a stored proc. My guess is cursors. Any help?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No need for cursors or loops. This should give the result you want:

SELECT client_id, COUNT(DISTINCT source)
FROM History
GROUP BY client_id

--
David Portas
SQL Server MVP
--|||
Dave, you're a champ!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!