SQL Server 2008 :: Find All Items That Call A Stored Procedure
Nov 2, 2015
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
View 9 Replies
ADVERTISEMENT
Jul 1, 2015
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies
View Related
Jul 22, 2015
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies
View Related
Mar 5, 2015
I am having few queries related to follow.
1. Is it possible to call SSIS package from procedure .
2. Also want to supply parameters to procedure.
3. Can multiple users execute that procedure simultaneously.
4. If yes then will it cause any issue if it is run simultaneously by 10 users.
View 3 Replies
View Related
Aug 24, 2000
I am having a problem trying to run a stored procedure
from a MS SQL Server version 6.5 server. The stored
procedure calls another stored procedure on a MS SQL
Server version 7.0 server.
The job task always fails with the error message...
"Login failed for user 'sa'. (Message 18456)".
I can run the stored procedure from the T-Query window
on the 6.5 server successfully but it will not run
as a job task.
Does anyone have any suggestions?
View 1 Replies
View Related
Nov 27, 2001
Hi,
I was wondering is anyone can help me out on this one,
I want to run a query, but I need to reference a Database that exists on a different server. I am using SQL 6.5
Any suggestions would be welcomed,
Thanks a mill,
Fin
View 1 Replies
View Related
Mar 17, 2015
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies
View Related
Mar 19, 2008
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Thanks, Gary
View 9 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Mar 10, 2005
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
View 2 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Sep 5, 2006
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources() { DAL.CreateCommand("sp_GetLatestFiveTrainingResources"); DAL.ExecuteReader(); SqlDataReader dr = DAL.DataReader; ..... } Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005
View 1 Replies
View Related
Jan 9, 2008
This is my first forray into writing stored procedures and like the manual said created one in SQL Server 2005 parsed or tested it, executed it, tested it again succesfully,need to modify it but can't find it under stored procedures file where I initially right clicked and selected New Stored Procedure.
Any idea where it went?
View 3 Replies
View Related
Jul 30, 2007
Hi
This is seshu.
Is there any way to find out the last used stored procedure.
Let me know about this one.
View 1 Replies
View Related
Jun 25, 2015
I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?
View 1 Replies
View Related
Feb 14, 2015
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
View 7 Replies
View Related
Jun 5, 2012
I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
View 5 Replies
View Related
Oct 7, 2013
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
View 3 Replies
View Related
Aug 10, 2015
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
View 1 Replies
View Related
Sep 23, 2015
I was trying to create stored proc
[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,
[Code] ....
But the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.
View 1 Replies
View Related
Sep 30, 2015
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));
insert into Product
select 'Prod1',10,'Black' union ALL
select 'Prod1',10,'BLue' union ALL
select 'Prod2',20,'Green' union ALL
select 'Prod2',10,'Black' ;
[Code] ....
In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
I had tried out join but it is not serving needs.
---===========================================
-- Sql
--============================================
DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
BEGIN
set @ProdId='Prod1';
[Code] .....
View 9 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
View 4 Replies
View Related
Mar 18, 2015
I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?
View 5 Replies
View Related
Apr 16, 2015
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
View 2 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 8 Replies
View Related
Jul 29, 2015
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
View 6 Replies
View Related
Oct 23, 2015
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies
View Related
Oct 6, 2015
Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies
View Related
Aug 29, 2007
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
View 5 Replies
View Related
Mar 31, 2006
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
View 2 Replies
View Related
Aug 7, 2001
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
View 2 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies
View Related
