Views Containing SELECT * Do Not Recompile When Table Is Modified

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


ADVERTISEMENT

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 View Related

Transact SQL :: Placement Of Option (Recompile) In Dynamic For XML Select Statement?

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

No Recompile After Sp_recompile Or WITH RECOMPILE Option

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

Want A Stored Procedure To Dynamically Select Views Pulled From A Table.

Feb 1, 2007

I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure? 
Thanks,
CG 
 
 
 

View 3 Replies View Related

Bulk Insert Into Views That Select From Table On Remote Server

Jul 20, 2005

Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal

View 7 Replies View Related

SELECT Last Modified Data

Sep 24, 2004

Hi,

I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?



Thanks

View 8 Replies View Related

SQL Server 2008 :: Need To Recompile Stored Procedure After A Table Alter?

Feb 5, 2015

Version 2008 R2

The stored procedure has the dependency on the table that was altered.

View 4 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related

Table Modified

Jan 14, 2004

Hi all,
Sql server 7

Is there anyway to findout if list of tables that has been modified after august 2003 in a database.

Waiting for ur reply.

TIA
Adil

View 3 Replies View Related

Table's Last Modified Date

Jan 9, 2001

Does anyone know how to get a table's 'last modified date' in SQL 7 ?
Sysobjects contains the 'create date', but I can't find a 'last modified date' anywhere......

View 1 Replies View Related

How To Get Modified Time Of A Table

Jul 11, 2006

Hello All,I would like to get last modified timestamp for a table. Is there any way toget that information using any commands?Thanks in Advance,Muthu.

View 2 Replies View Related

Last Modified Date Of The Table

Dec 27, 2007

Hi,

How to find out the Last Modified (Structure) in the SQL Table?. Is there any query please let me know.

Regards,
S.Balavenkatesh

View 3 Replies View Related

List Of Modified Table ?

Jan 7, 2008

Hi Everybody,

i am it sutck when i am writing this sql in sqlserver2005

my requirement is to list all the table name and along with their filed name if some one modified (table definition ) for a specifc date range ?

is there any way to get these information in sql server 2005 ?



regards

sujithf

View 3 Replies View Related

SQL Script To Find When Table Last Modified

Nov 8, 2007

 I am looking for two sql scripts,1. How can i check when a tables structure was last modified? 2. How can i find the structure differences between two databases that were created the same but changed over time individualy?Looking for sql scripts to do this. Any help on either would be great.Thanks 

View 2 Replies View Related

How To Get DateBase Table Last Modified Date ?

Dec 26, 2005

Hi

View 9 Replies View Related

How To Show Last Time TABLE Was MODIFIED?

May 15, 2000

Hi all,

What is the best way to show when a table was last MODIFIED?

Thanks,
Stan

View 1 Replies View Related

Indexes Update When Table Is Modified

Jun 8, 2004

Hello again,

Two Short questions this time,

I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.

Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?


Inon.

View 12 Replies View Related

Data/Table Last Accessed/Modified

Apr 28, 2004

Is anyone aware of a method of determining when a table was last modified or accessed?

Some of our databases have tables that I am sure are not being used and I would like to generate a list of tables that have not been accessed or modified for some period of time.

I looked for a system procedure but didn't see anything that satisfied my need.

Currently I rename suspect tables and wait for someone or some process to gripe, but I don't care for that method for obvious reasons.

Thanks!

View 4 Replies View Related

Check Whether The Database Table Has Been Modified

May 9, 2008

hello everybody,


i have one problem, I want to check the database table modified or not for every five minutes , how can check? please help me


Senthil

View 2 Replies View Related

Table Last Modified Date/time?

Jul 20, 2005

I'm curious if there is a quick way to query the date/time a table was lastmodified? I appreciate any tips or suggestions provided!TIA - Rob

View 2 Replies View Related

Pick Recent Modified Date In The Table

Feb 21, 2014

This is my table:

ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/12/14 451
1 Nestle NS Foods Products 01/17/14 495
2 Oracle OR Software Info 01/24/14 279
2 Oracle OR Soft & IT Info 01/26/14 310
2 Oracle ORL Software Info 01/25/14 219
2 Oracle ORL Soft IT 01/28/14 600

MonthlyAmount Funded AppCategory Research
37.5623 Yes NE NA
41.2365 No N NA
23.2568 Yes OR InProgress
25.8333 Yes ORL NA
18.2189 Yes SOF Approved
50.0000 No IT RejectedExpected Output:

ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/17/14 946
2 Oracle OR Soft & IT Info 01/26/14 589
2 Oracle ORL Soft IT 01/28/14 819

MonthlyAmount Funded AppCategory Research
78.7988 No N NA
49.0901 Yes ORL NA
68.2189 No IT Rejected

I want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.

select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group],
B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount,
B1.[FuBded], B1.[AppCategory], B1.[Research]
FROM Business B1
INNER JOIN
(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,
SUM(MonthlyAmount) as MonthlyAmount
from Business
Group by ID) B2
ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate

View 1 Replies View Related

Can Date Modified Col Be Automatically Updated W/o Trigger For Each Table?

Apr 10, 2006

Hello,
I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?
Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.
Thanks!

View 1 Replies View Related

Transact SQL :: Detecting Modified Rows Before Writing To A Table?

Apr 17, 2015

I've a table with more columns and 1 identifier. I need to write this table when a modified row is detecting respect to the columns not to the identifier.

So I've created a temporary table to put the potential rows to write on the real table, but I want to detect the modified rows. I've thought to use the checksum function, but I don't know how to use it and if it could be useful in this scenario.

Moreover, in the temporary table I've collected daily the rows to write: the first day a row could have a value respect to his columns, the next day a different value and the next one the same value respect to the first day.

View 26 Replies View Related

Table , Column Created, Modified Date In Sql Server 2000 Or 2005

Oct 10, 2007



Hi

Any one please tell me is there any possible way to identify the table modified date.

I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.
Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.

I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date
can any one help

View 8 Replies View Related

Select * In Views

Apr 15, 2006

People are telling me it is bad to putselect * from <atable>in a view. I better should list all fields of the table inside thedefinition of the view.I dont know exactly why but some say:A select * from makes sql server does a table scan.Is that true, even if i put a where on the select on the view? And what if idont list all fields in the select on the view?Thanks for the answer.Peter

View 33 Replies View Related

Select * In Views

Aug 16, 2007



Hi All,

Iam having 2 tables say Emp table and Department Table. Iam having a view which joins 2 tables.
example
select *,Department.DeptStatus, Department.DepartmentName from Employee INNER JOIN Department ON Department.deptno=Employee.DeptNO.
When I add a new column to the Employee table say EmployeeStatus then my view get corrupted.what i mean is that the last newly added column from emloyee table takes the Name as DeptStatus and shows values of the EmployeeStatus Column. We have been creating new Columns in SQL 2000 we never faced this issue but iam seeing this issue only in SQL Server 2005.

Thanks in Advance.

cheers,
Sada

View 7 Replies View Related

Views Or Select

Aug 2, 2007

Hai, I'm an novice in Sql Server. I wanna know why we are going for VIEWS instead of SELECT statement in Sql Server 2005. However, both are going to perform the same task (i.e, Retreiving data) from the database.

View 5 Replies View Related

Granting Select On Views

Dec 4, 2007

Hello,

How do , I grant select privileges on all the Views of the database to a user in SQl 2005. All the tables and views in the database are under the dbo schema.

Thanks.

View 1 Replies View Related

Views Vs SELECT Statement

Sep 9, 2007

Is it better to use just a SELECT statement or a view when working with a small database? Does anyone know which would perform better?

............................................................thanks

View 12 Replies View Related

Select From Database Without Grid Views Etc

Jul 9, 2006

Hi,

I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like

select id from table where name = "x"
store id in idvar
store the row count to a variable rowcount
if rowcount > 0
    select name from table2 where id = idvar
endif

etc

I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help

View 1 Replies View Related

SMO Problem / Can't Select Non System Views Without Crash

Mar 5, 2006

I've had a couple problems with SMO and I wonder if anyone else has.  Specifically, when I run through stored procedures (just like your example) it is painfully slow.  Maybe 5 a second.  Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.
Both cases crash my app.  Any ideas?
Below are two ways that both crash or hang.
SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{}
OR
 
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{
if (!view.IsSystemObject)
{}
}
 
 
 

View 1 Replies View Related

VIEWs - Can It Know Wether A SELECT, UPDATE, Etc Is Executing On It

Apr 6, 2000

VIEW behaviour depending on what operation is run on it.

VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie
CREATE VIEW xxx AS SELECT ..
CASE UPDATE THEN do_this
CASE SELECT THEN do_that etc.
Does anyone know if this is possible?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved