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!

Name of current db in proc

Hi,
What's the best way to get the name of the current db (not filename, but db
name) from inside a sp '
I'm writing a general sp that has to be put into numerous db's, except one
Kr.
SorenHi,
DB_NAME() must be the way to go :)
Soren
"Soeren S. Joergensen" <nospam@.nodomain.com> skrev i en meddelelse
news:eska3MgtFHA.2328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What's the best way to get the name of the current db (not filename, but
> db name) from inside a sp '
> I'm writing a general sp that has to be put into numerous db's, except one
>
> Kr.
> Soren
>sql

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

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