SQL 2012 :: Find Out Which Store Procedures Fired
Apr 16, 2014Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies
ADVERTISEMENT
SQL 2012 :: How To Purge Old Store Procedures From Clients
Jul 11, 2014I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
Store Procedures
Jan 27, 2004hi everyone :)
I've got a store procedure(sp) on the database side ( MS SQL 2000 )
and want to access the sp via ASP.net
so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.
I've tested the SP and it does work fine
can you just give me a template of how to access the sp from ASP.net
mant thanks indeed
M
Store Procedures For DBA
Mar 16, 2008Can anyone plz tell me what kind of store procedures DBA's needs to write or DBA needs to know?
View 2 Replies View RelatedStore Procedures
Jan 11, 2006Hi:Does anyone know if IBM (or any other IT Software company) offers storeprocedure classes anywhere in the country?ThanksMarc
View 2 Replies View RelatedStore Procedures Return
Apr 28, 2000Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.
Thank you
TSQL And ASP - Store Procedures Help!!!!!!
Dec 1, 2000All,
I have beating my head over this and have asked questions to clear this up in small bits.
In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:
--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'
'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath
howmanyfields=SysMainQuery.fields.count -1
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"
' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue=" "
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"
' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>
The stored procedure takes the input an returns the sql statement:
Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int
Set @wheretmp = ''
Set @tmplen = 0
if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''
If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp
print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go
The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....
Has anyone ever run into this?
Thanks
~Lee
SQL 6.5 Security For Store Procedures
Sep 30, 1999How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
TIA for all you responses
Store Procedures && Functions
Feb 10, 2006hi,
what is the different between store procedure and function?
when it is right to use sp and when functions?
thanks in advanced
Search And Replace Within Store Procedures
Nov 29, 2004I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.
Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?
Speeding Up Store Procedures Using EXEC?
May 26, 2004Hello, can anyone offer any advice on this problem related to store procedures.
The following 2 chunks of SQL illustrate the problem
--1
declare @lsFilt varchar(16)
select @lsFilt = 'fil%ter'
select * from sysobjects where name like @lsFilt
--2
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
exec (@lsQuery)
When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.
Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of
create proc SP2Style
@psFilter varchar(16)
AS
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
exec (@lsQuery)
GO
instead of
create proc SP1Style
@psFilter varchar(16)
AS
select * from sysobjects where name like @psFilter
GO
Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?
thanks
Paul
How To Give The Grant On Store Procedures
Dec 19, 2007hello i am new to sql server 2000.
i am one of the user of database and in that user i created store procedures.
now i want to create another user and give that user to permission of excute all my proceudres and i also give him privillages such a way that user can also modify my procedures...
so can u assist me on that...
thanks in advance...
Can't Import Views And Store Procedures
Aug 6, 2007I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help
Thank
Andy Wong
awong@virginiadare.com
Temp Table In Store Procedures
Jan 25, 2008 Can any one please tell me where i am going wrong..
Code Snippet
create proc SP_PercentageRMU
as
SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty'
,RAW_MATERIAL
into #TZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]
group by RAW_MATERIAL
GO
select [Usage Qty],(case when r.raw_material = z.raw_material
then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8))
else 0
end) as '%UsageQty'
,r.[PRODL]
,r.RAW_MATERIAL
,r.[GRADECODE]
into #PZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r
inner join #TZMelt_Pound z on r.raw_material = z.raw_material
drop table #TZMelt_Pound
go
error
Msg 208, Level 16, State 0, Line 2
Invalid object name '#TZMelt_Pound'.
Find Next Value And Store The Result With Incremental
May 27, 2008Please help!
Please look at the sample data below; I need help to change the value of data from (Before) - (After)
Before
Station Cycle_Index
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
When Station = 'D_CC' then Cycle_Index suppose to start a new cycle. So my problem is trying to find the next 'D_CC' and store the incremental cycle # in a New_Cycle column.
After
Station Cycle_Index New_Cycle
D_CC 1 1
C_CC 1 1
C_CV 1 1
REST 1 1
D_CC 1 2
C_CC 1 2
C_CV 1 2
REST 1 2
D_CC 1 3
Thanks mucho for your help!
Updated:
Sorry to confuse you by changing the text color!
Case when Station = 'D_CC' then Cycle_Index = Cycle_Index + 1; based on the prior cycle # and continuos to find next 'D_CC' until the end; regardles whatever in between the prior D_CC to next D_CC.
Bottom line is searching the next value from Station = D_CC then cycle # in Cycle_Index column need to be incremented by 1 and stored the new cycle # in New_Cycle column as the actual cycle.
Calling DB2 Store Procedures From SQL Server 2000
Feb 5, 2004I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
THanks for the help....
Stoping Sql Server Agent Using Store Procedures
Jul 10, 2007Hi
all i need little help regarding stored procedure.
i have some job say ".one." that runs daily on sql server agent But i have some holiday schedule as well in database
suppose if a holiday come and i want to stop that job on sql server agent on that particular holiday.
can anybody help me writing it ..
how to do it by calling SQL server agent to stop and start with date in stored procedure.
Regards
Create Multiple Store Procedures In 1 SQL Statement
Nov 17, 2006Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.
Best Regards,
Hans
Link 2 Store Procedures In 1 Crystal Report
May 20, 2015In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.
I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.
I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?
Folder Underneath Programmability ==&&> Store Procedures
Aug 15, 2007Hello there
I have several developers working on different systems but same database; these developers make Store Procedures and all utilize these SP€™s; at the moment our SP list grows and keeps about 200 + SP's, whereas I expect a lot more to come.
Now I want to organize the SP''s under certain folders; example by the System and underneath by User name so that traceability of the work can be easy as well as organization of the SP's will be achieved.
I cannot find any way how to customize the Store Procedure node; and create folder underneath.
I know the filter option helps for searching the SP's but we also have remote users who are working somewhere away from us; so I want to get their work only by filtering the folder of these user name and also sometimes I need to know who made these SP's so that that developer can fix or alter the procedures.
We may do this using security instead of dbo, the developer who is creating the procedure will carry his name; but this needs tight security and we are in the phase of testing and lot of changes occurs on daily basis which requires little loose security till we pass this phase.
Any Idea or suggestions to make it viewable and accessible and copy the work of the specific users right away; rather than searching his Sp's, with the coordination of the user or with the maintained documentation.
SQL Search :: How To Find What Are All Columns Used In A Store Procedure
Nov 23, 2015I would like to know all the columns used in a store procedures.I have used dynamic sql to create these procedures .
View 4 Replies View RelatedStore Procedures: Pass Array Of Numbers For IN Clause?
May 24, 2006How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case).
I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this?
CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO
SQL Server 2008 :: List Store Procedures Contents?
Jul 27, 2015who i can to list the content of all store procedures and funtions in text file ?
View 3 Replies View RelatedScript To Store Stored Procedures In Seperate Files
Jul 20, 2005How does MicroSoft store the stored procedures in seperate files.What tools are used.Cause that's what I like to do too.Arno de Jong,The Netherlands.(SCPTFXR does not have the option to store the stored procedures indifferent filesI think)
View 2 Replies View RelatedStore Procedure To Find City And Zip Code By Radius
Mar 20, 2007Hi,
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
Thanks
Kate
How To Find Year From 1900 To 2000 Using Store Procedure In Sql Server
Nov 21, 2006hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server
How To Find Out Linked Server In A Procedures
Jan 24, 2008hi i have 100 of stored procedures and many has linked servers in my database.
and now i need to know how many storedprocedures are using particular linked server .
plz advice to do this
regards
karthikeyan.D
To Find Any Text In All Stored Procedures
Jul 20, 2005Hi Guys,I want to find any specific text in all SQL server stored procedure.How can I do this. Please let me knowThanks in Advance
View 1 Replies View RelatedCan't Find My Stored Procedures I've Created
Jun 24, 2007Hi
im using sqlserver studio managment
i goto a specific database and goto stored procedures and add stored procedures from context menu and created successfully .
i try to find it between procedures but not found i try to create it again gives me error that already found so.
where i can get my created stored procedures from sqlserver studio managment?
thanks in advance.
Use Of User Data Types For Creating Local Temporally Tables In Store Procedures
Aug 24, 2006
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4
Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
Diego Sierra
Unable To Find Stored Procedures On Server
Aug 8, 2007I have just successfully published an web app and db to a server
but when the web app tries to use a stored procedure it says that
it cannot find stored procedure.....in my data base......eeeer
anyone had this problem
New To Stored Procedures: Where To Find The Recordset Retrieved?
Aug 8, 2006Hi,
This may be a really silly question, but I am starting to experiment with stored procedures for the first time and I am not really sure about how they work. I am working in SQL Server Management Studio Express...
When putting a SELECT query statement into a stored procedure and executing it, how or where can you get or find the actual record set supposedly retrieved?
Thanks for any help..
--------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = 1ASBEGIN SET NOCOUNT ON; SELECT ID, TaxonID, Taxon FROM QueryView WHERE ParentID1 = COALESCE(@taxparent, ParentID1) AND ParentID2 = COALESCE(@taxparent, ParentID2) AND ParentID3 = COALESCE(@taxparent, ParentID3) AND ParentID4 = COALESCE(@taxparent, ParentID4) AND ParentID5 = COALESCE(@taxparent, ParentID5) AND ParentID6 = COALESCE(@taxparent, ParentID6) AND ParentID7 = COALESCE(@taxparent, ParentID7) AND ParentID8 = COALESCE(@taxparent, ParentID8)END
Script To Export Tables,store Procedures And Data In SQL Server Management Studio Expres
Nov 23, 2006how i can do a script to export tables,store procedures and data in SQL Server Management Studio Expres
I just arrive to do a script for tables and stores procedures
help please