SQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not
Mar 23, 2014I want to know, Is it possible to check a particular stored procedure is recompiling or not?
View 7 Replies
ADVERTISEMENT
Stored Proc Recompiling Over And Over
Jul 20, 2005I have a simple stored procedure that is recompiling over and over. Ihave run several traces and I can't figure out the problem. Here isthe code:CREATE procedure dbo.Sp_multi_selectEmployee(@EmployeeID int)Asselect EmployeeID, UserName, FirstName, LastName, DealerID, Password,tbldealeremployees.UserTypeID,tblEmpUserTypes.Name as UserRole, DateCreated, DateLastModified,Customized, Active, CustRoleName,Address, Address2, SSN, EmailAddress, City, State, Zip, Country,OwnerFlag, JobTitle, JobDescfrom dbo.tblDealerEmployees inner join dbo.tblEmpUserTypeson dbo.tblDealerEmployees.usertypeid = dbo.tblEmpUserTypes.usertypeidwhere EmployeeID = @EmployeeIDThe traces report an event sub-class of 1, which according to msdn is"Schema, bindings, or permissions changed between compileor execute." I don't see how the permissions orbindings have changed. I have seen a few similar posts but noanswers. I tried dropping all of the indexes on both tables but thathad no effect. I can replicate this in our development environment,so it seems something specific to the code. Any answers, ideas?thanks.
View 4 Replies View RelatedRecompiling Stored Proc
Dec 17, 2007
Hello
I have an issue that a job which executes a stored procs fails with a string truncation error but suceeds if I run the same proc WITH RECOPILE option.
This happens only at 12 a.m. daily.But during all other executions it runs fine.
Any comments or clues of why this happens.
How To Check Whether Stored Procedure Is Being Used
Apr 14, 2008Hi,
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
How To Check Stored Procedure?
May 2, 2004Hello, everyone:
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
ZYT
SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?
Jun 17, 2015Is 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?
Stored Procedure To Check Different Criterias
Aug 6, 2007I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0
,@OriginalMax DECIMAL=0
AS
DECLARE
@CRI8 VARCHAR(500)SELECT
@CRI1=''
SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax'
SELECT @Criteria = ......+ @CRI8
When I execute this stored procedure, I get the following error message.
SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin'.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin
Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
How To Check For Nulls With-in A Stored Procedure
May 24, 2008I have a Stored Procedure as followsUSE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SPLogins]
(
@LoginName varchar(50), @LoginD int output,
)
AS
Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
What Is the correct wayt to go about it.
How To Check Stored Procedure Status
Dec 18, 2003How to check status(valid/invalid) for all stored procedure in my DB...
View 14 Replies View RelatedCheck If A Stored Procedure Exists
Mar 14, 2007Hi All.
Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?
Thanks people.
Stored Procedure To Check Date Range
Nov 10, 2006Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
Select And Check A Boolean Value With Stored Procedure?
Apr 10, 2008Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
View 4 Replies View RelatedHow To Check When A Stored Procedure Was Last Called/executed
Jul 23, 2005HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies View RelatedQuery - Check For A String In Stored Procedure
Jul 23, 2005Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View 1 Replies View RelatedInsert Stored Procedure With Error Check And Transaction Function
Jul 21, 2004Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?
Jan 21, 2014On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
SQL 2012 :: Getting Returned Value Of A Stored Procedure?
Jun 2, 2015I am running a sproc we have developed and I am getting a -6 as a returned value.what that actually means.
View 7 Replies View RelatedSQL 2012 :: Using Stored Procedure To Truncate Tables?
Jun 30, 2014I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.
Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'
Of course there are different names, just changed for an example.
Here is the code in the Stored Procedure.
CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)
This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.
SQL 2012 :: Executing Stored Procedure As Different User
Nov 26, 2014I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
SQL 2012 :: Extended Stored Procedure Permissions
Jan 15, 2015I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
SQL Server 2012 :: Generate PDF From Stored Procedure
Mar 3, 2015We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
View 3 Replies View RelatedSQL Server 2012 :: Passing Sum To Stored Procedure
Sep 21, 2015Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error
exec mysp
@param = (@var1 + @var2)
SQL 2012 :: Executing Parameterized Stored Procedure From Excel
Aug 12, 2013I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server, then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
SQL Server 2012 :: Run ASPX File From Stored Procedure?
Dec 9, 2013After my Stored Procedure has run, I need to call [URL] ..... file. Can this be done from my Stored Procedure?
View 2 Replies View RelatedSQL Server 2012 :: Stored Procedure With One Or More Input Parameters?
Dec 17, 2013I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
SQL 2012 :: Is There Any Functional Impact By Altering Stored Procedure?
Mar 31, 2014I have a sp. I alter stored procedure by adding some logic to that. How can I test that is there any functional impact by altering that stored procedure? How to prove to the team that the modifications doesn't impact any functionality?
View 5 Replies View RelatedSQL 2012 :: Get Structure Of Stored Procedure Output Table
Apr 15, 2014To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.
CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );
INSERT INTO #Tmp
EXEC dbo.MyProc
Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table
EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )
SQL 2012 :: Assign Create Stored Procedure Permissions?
May 6, 2014Only to a specific schema? Can this be done?
View 5 Replies View RelatedSQL 2012 :: Create Stored Procedure In SSIS Package
Jun 5, 2014I have a really big stored proc that needs to be rolled out to various databases as part of db installs I run through SSIS.
The Stored proc is too long to run using Execute SQL Task. Is there another way that just running the create script manually.
SQL 2012 :: Can SSIS Package Replace Stored Procedure
Jun 25, 2014We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:
mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.
SQL Server 2012 :: How To Create Password On Stored Procedure
Jun 27, 2014is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?
View 1 Replies View RelatedSQL Server 2012 :: Restore Database By Stored Procedure
Jul 26, 2014I need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?
View 1 Replies View Related