I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 RepliesOne of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 Replies View RelatedSTEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO TraceÂ
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted  from History'),
('Insert into Account  Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
Is there any way/Process that we can find out all stored procedures from database where dynamic script has used in script.
View 6 Replies View RelatedHi,
This is driving me up the wall,
I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.
I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.
Here is the script (copied from the modification screen)
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@SearchVarChar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
IF (@Search='Dowle')
WHERE (dbo.Members.Username = @Search) OR
(dbo.Members.Forename = @Search) OR
(dbo.Members.Surname = @Search) OR
(dbo.ActiveMember_Mobile.Value = @Search) OR
(dbo.ActiveMember_Email.Value = @Search) OR
(dbo.ActiveMember_Location.Location1 = @Search) OR
(dbo.ActiveMember_Location.Location2 = @Search) OR
(dbo.ActiveMember_Location.Location3 = @Search) OR
(dbo.ActiveMember_Location.Location4 = @Search)
END IF
ORDER BY dbo.Members.Username
The bit I am trying to do above isn't real, but the same error appears every which way I try.
Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14
Incorrect syntax near the keyword 'WHERE'.
What am i doing wrong?
Thanks
David
Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks
View 1 Replies View RelatedIs it true that replacing embedded sql statements (that insert/update and fetch data
from sql databases) by stored procedures improves overall respones time and SQL performance.
We have tons of embedded SQL statements in our Visual Basic modules and are debating whether replacing them by SQL Stored procedures
will really be worht the effort.
Any insights on this?
Thanks in advance. If possible, please reply to my e-mail.Thanks.
i would like some conditional settings in a stored procedure
i have a variable
@Variable
and I want to do a conditional statement like
if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12
not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql
trying to get to the bottom of this for some time...... eventually tobe used with asp.heres the problemthe following rather complex SQL statement works fine via queryanalyser:SELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId ='W27P'AND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = 2AND tbl_LevelDetail_Report.nvchLevelTypeId = 1AND tbl_Levels.nvchReportingLevelId IN ('a')GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamereturns rows ok no problembut when trying to convert to a stored procedure i dont get anyresults:CREATE PROCEDURE usp_incmpwfilter_rs(@strPeriodID varchar ,@intLevelDetailID varchar,@intLevelReportID varchar,@strFilters varchar)ASset nocount onSELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =@strPeriodIDAND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailIDAND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportIDAND tbl_Levels.nvchReportingLevelId IN (@strFilters )GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamethen call it by SQL statement:EXEC usp_incmpwfilter_rs 'W27P',2,1,'a'Returns no rows. This is the initial problem. Also there will beanother issue if i can get the above to work: the @strFilters cancontain multiple data, ie 'a','k'this works fine in the 1st sql statement ie: ANDtbl_Levels.nvchReportingLevelId IN ('a','k') but I dont know how topass as a parameter to the stored procedure. I cannot create temporarytables.i had not created the intial SQL statement, i am just trying toconvert it to a stored procedure which accepts thos parameters. thishas been a real headache for me, any help as always appreciatedgreatly.
View 3 Replies View RelatedHi,
I am doing some research on sqljdbc 1.2. I have run into a few problems with the jdbc driver. I think it would be so much easier just to ask the expert here.
1. when to use CallableStatment, PreparedStatement, Statement for stored proc and why?
http://msdn2.microsoft.com/en-us/library/ms378046.aspx
talks about using Statements with Stored Procedures.
It says for stored Proc with no param, use Statement; for stored proc with only input param, use PreparedStatement; and for stored proc with output param, return status or update count, use CallableStatement.
Is there any special considerations to select which one to use, is there any performance differences? I assume we can always use CallableStatement for stored proc.
2. mutliple statements on one Connection
The JDBC spec allows multiple Statement on one Connection with no need to close the previous Statment. When Connection.close() is called, all the Statements and ResultSets will be closed too.
I tried with regular stored proc, it is fine of have a CallableStatement, then a Statement, without close the CallableStatment, all the Statements are executed with no error.
However, when I change the stored proc, using explicit transaction, i.e BEGIN TRANSACTION, and COMMIT TRANSACTION, the Statement failed and throw an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3500000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
When I add a CallableStatement.close() before the create the second Statment, then it worked. Is this a bug or something special needs to be set for stored proc with Transaction?
3. Statement.getGeneratedKeys
Under the hood, is it making another round trip to get the generatedkeys, or when the Statement created with Statement.RETURN_GENERATED_KEYS is excuted, the identity field is already retured with the Statement?
This method call works with regular insert Statement, but does not work for PreparedStatement calling a stored proc. Is this a bug?
Thanks in advance,
jessica
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
Hai,
I just waana know whether we can use "FOR statement" in Stored Procedures. If yes, can you describe it with some examples of how they are used in stored procedures ?
Hello,
Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.
I don’t want to use isql, osql, openquery, openresultset because I have to pass user id or password.
I have linked the required SQL servers using Linked servers.
If so please give some example.
Thanks in Advance,
Sateesh
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable
(like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns.
(I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies View RelatedHi 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 RelatedHi
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.
I 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
Hi,
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
How to find out last created stored procedures in sql.
View 5 Replies View RelatedHi
Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.
I would appreciate any reply !
Thanks you for your time.
I found lots of methods for individually scripting out stored procedures, but I'd like to know if there's a way to do many (like 100 or so) at once. Ideally, I'd like to execute something that would save all the procedures that start with 'dev_' to the file system somewhere, then delete the dev_ SP's from the database and leave the other SPs that actually get used. Is that possible in one step?Â
View 10 Replies View RelatedHow 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 RelatedReplication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.Â
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1
exec stproc2
.....
exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]
View 3 Replies View RelatedI have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.
I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used.Â
I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.
View 6 Replies View RelatedHow do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'