Recompile All Stor Proc And Udf And Views
Oct 1, 2003
Hi ,
what would be correct way to recompile
all user objects ?
Developers working by using alter create and rename on udf and stored procedure, dependecicies window in EM does not repesent correct info.
If I want to see up to date all dependedcies of every user defined object in db
Thank you
Alex
View 5 Replies
ADVERTISEMENT
Jan 26, 2006
How I can get the best tutorial of using SQL Server 2005 Stored Procedured? For example this stor proc,
--------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[COMPANY_ADD]
-- Add the parameters for the stored procedure here
@CompanyID INT,
@CompanyName NVARCHAR(100),
@CreatedDT Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into COMPANY(CompanyID, CompanyName, CreatedDT, LastModifiedDT)
values (@CompanyID, @CompanyParentID, @CompanyContactID, @CompanyName, @CreatedDT, @CreatedDT)
return @CompanyID
END
--------------------------------------------------------------------------------------------------------------
I want to get tutorial of the meaning line by line?
Thanks
View 4 Replies
View Related
Jul 10, 2006
ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@varDate as varchar (255),
@StartDate as datetime,
@EndDate as datetime
)
AS
SELECT
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)
GROUP BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <---this part gave me an error, because of the comma,
END
ORDER BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END
The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~
View 10 Replies
View Related
May 21, 2007
Hello
I KNEW this would cause some problems, but I just can't seem to find how to do it....
Basically I have a stored procedure in 2005 dbase (which updates the data), and I want to schedule it to run daily (at night) HOW CAN I DO THIS????
:mad: Y did they have 2 go and change it??? :eek:
View 3 Replies
View Related
Jul 23, 2005
I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks
View 3 Replies
View Related
Mar 17, 2008
If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.
Is there a work around for this? An "auto-recompile when tables are modified" kind of option?
Thanks
Nick
PS: This is the script I used for testing:
create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2
View 9 Replies
View Related
Aug 30, 2007
I know that all the documentation always tells you that sp_recompile will force a stored procedure to recompile the next time it is executed. However, I am not seeing the recompiles in a SQL Trace, when capturing SP: Recompile events. I have tried this on many different database servers, using sp_recompile and also the WITH RECOMPILE option when creating the proc.
Can anyone explain this?
View 4 Replies
View Related
Jan 10, 2007
I am moving a fairly large app from Access 2003 to C#/SQL Server 2005.
The app has one front end mde, but it has two backend data mdbs, one is used in the US and one in the UK.
The two backend mdb's are identical in structure, but of course the data is different. There are 150 tables or so, and we plan to maintain separate databases after the upgrade.
Now, of course, all of the queries are in the front end, the backend db's contain only tables.
In the new world, I do not want to have to maintain the same views and stored procs in two different SQL Server databases.
Is there a way to have all views and stored procs in one database, but have them draw on the data in the two other databases. There must should be a way to deal with this scenario in SQL Server.
Many thanks
Mike Thomas
View 5 Replies
View Related
Apr 18, 2008
Hi,
I'm curious to know what templates/standards you guys use for T-SQL stored procs. I've been using the following standard:
Code Snippet
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<proc name>')
BEGIN
DROP Procedure <proc name>
END
GO
CREATE PROCEDURE <proc name>
@parm1,
@parm2
AS
BEGIN TRANSACTION <transactionName>;
BEGIN TRY
--DO WORK HERE
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION <transactionName>;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION <transactionName>;
ELSE
ROLLBACK TRANSACTION <transactionName>;
GO
If you guys have any advice/critisizm, please post it as I'm constantly looking into bettering this as much as possible.
Regards
Michael
View 6 Replies
View Related
Jul 20, 2005
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
View 1 Replies
View Related
Sep 13, 2000
We have lots of stored procedures containing temporary tables. In SQL 6.5 every thing was great. But in 7.0, it's doing a lot of recompile while executing. Tried trace flag 8720, didn't work..
Basically I am talking abt this problem :
http://support.microsoft.com/support/kb/articles/Q224/5/87.ASP
Let me know if any ideas/Remedies ??? How did any of you tackled this behaviour.
View 1 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Oct 25, 2007
Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)
View 1 Replies
View Related
Jun 2, 2002
What exactly does WITH RECOMPILE do? I read that it will create a new plan. I guess the next question is, what is the purpose behind that?
Can someone explain to me?
Thanks
View 2 Replies
View Related
Oct 30, 2000
Does someone know whether it is better to drop and reload or sp_recompile a stored procedure to get a new, recompiled execution plan? I have another DBA telling me it is better to drop and reload the stored procedure rather than use sp_recompile. I would think that sp_recompile would be the preferred method.
View 1 Replies
View Related
Dec 19, 2007
Is there a way (command or stored procedure) to RECOMPILE or REFRESH a USER DEFINED FUNCTION? I can recompile SPs with sp_recompile and refresh views with sp_refreshView, but I could not find any way to refresh User-defined functions (some of them are like views, with parameters).
Environment: SQL 2005 SP2.
Thanks !
View 4 Replies
View Related
Jul 20, 2005
Hello:The installation details:W2K SP4, SQL Server 2000 Ent with 1GB RAM. It is a Bi-P3.When I run the Profiler to trace Stored Procedure performance, I get abunch of SP:CacheMiss for couple of stored procedure I invoke quiteoften in a web app.But I do not see SP:Recompile.Here are my questions:i) If the plan is not in the Cache, why am I not see SP: Recompile.Where else can it be tugged.ii) What are the other counters I need to monitor to see if I need morememory.Thanks in advance for any leads on this.Regards:
View 4 Replies
View Related
Aug 10, 2000
If my data structure never changes, just the data itself, is
there a need to use the "with recompil option" on stored
procedures? Isn't there a performance hit having it in
the stored procedure?
Thanks
View 1 Replies
View Related
Dec 29, 1999
What's the performance hit for using 'WITH RECOMPILE' in a stored procedure? I'm not a serious DBA, nor do I pretend to be one, but I'm writing a sp_ to be used with both insert and updates. I'm using a variable that defines the operation (IF @operation = 'Update'...) which will be passed at run-time from ColdFusion. Do I need to use the 'WITH RECOMPILE' clause to keep the sp_ kosher with respect to the operation being performed? And what's the damage in resources?
Thanks,
John E.
View 3 Replies
View Related
Jun 15, 2000
How to recompile a stored procedure automatically in SQL server 6.5?
Thanks in advance
Stella
View 1 Replies
View Related
Jun 15, 2000
Does the SQL Server 6.5 system recompile all stored procedures automatically when the server is restarted?
Thanks.
Stella
View 1 Replies
View Related
Aug 20, 2004
Hi,
currently i am working on performance tuning on some stored procedure and found that most of the stored procedure include with recompile on top of it.
i try to remove it and now it improve a lot on speed tuning. However, for those stored procedure which is using dynamic sql, is it a must to include recompile in our stored procedure?
View 10 Replies
View Related
Mar 6, 2007
Hi!I need to refresh an entire database.I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), andrefresh views with sp_refreshView, but I cannot find any way torefresh my user-defined functions (some of them are like views, withparameters).Any help appreciated :) !Ben
View 5 Replies
View Related
Jul 20, 2005
Hi,I have a question in SQL Server 2K, I use SQL Profile to trace, andfind Stored Procedure was auto recompiled, like this row in thetrace:SP:Recompile151680762004-02-27 16:01:11.610How can I stop the auto recompile.ThanksHarold
View 7 Replies
View Related
Apr 11, 2001
We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.
View 4 Replies
View Related
May 17, 2007
Hey all,
We have a problem with one of our MS SQL 2000 databases and some stored procedures.
I'm not sure exactly what the problem is, but these are the symptons....
The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.
Calling it through the query analyzer it runs within a second.
Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.
We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.
I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.
The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.
The code for the second results set is...
Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate
From Pricing p
Inner Join Security s
On p.SecurityID = s.SecurityID
Left Outer Join Issuer i
On s.IssuerID = i.IssuerID
WHERE p.PricingTypeID = @PricingType
And p.TenorTypeID = @TenorType
And p.CaptureDate Between @DateFrom And @DateTo
AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
Does anyone have any idea what might be going on here?
Regards
Shaun
View 13 Replies
View Related
Jan 31, 2008
Hi,
I increased one of my base tables column which is referenced in view
I noticed sql server didn't recognized this change and its still showing old field size in the view.
I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.
Thanks
- D
View 11 Replies
View Related
Jan 23, 2008
Hi,
I use recompile option in SQL query to dynamic pass variable to optimizer.
I verify explain plan with SET STATISTICS PROFILE ON
and optimizer chose nested lookup ,ok. But if use Display Estimated Execution Plan (CTR+L) I€™ve get merge join. It€™s very confusing, some suggestion €¦?
Use AdventureWorks
go
declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
OPTION(RECOMPILE);
SQL2005SP2
djedgar
View 4 Replies
View Related
Mar 23, 2006
I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.
I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)
I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.
I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.
One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.
View 2 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
Apr 18, 2015
I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
View 7 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Mar 21, 2007
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
View 3 Replies
View Related