Does A View Refresh Itself When I Reference It? (was Question On Views)

Feb 24, 2006

guys, ive never worked with Views before so forgive me.
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.

if i create a view:

CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable


When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'

does that View Refresh itself??

in other words does it run each time i Reference it??? or is it static from when i created it.

Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?

thanks for any help
rik

View 3 Replies


ADVERTISEMENT

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related

How Do I Refresh A View?

Jun 25, 2007

You know how I mentioned before that I was working with a database with a (bunch of) view(s) that are defined like this...

CREATE VIEW MyView AS
SELECT * FROM ExampleTable

Well, after altering the underlying table I noticed that the view is not picking up the new columns which leads me to believe I need to refresh the view.

Is this possible, if so, how?
SS 2K

View 14 Replies View Related

T-SQL (SS2K8) :: Can Refresh View Through Linked Server

May 20, 2014

Can i refresh view through linked server? I have full rights to modify the view?

Ex:ABC.MNC_DB.dbo.sp_refreshview 'view_ABC'

View 2 Replies View Related

How To Refresh A Data Source View And Autogenerate/Re-Deploy The Corresponding Report Model

Jan 9, 2007

Hi everyone!,

Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.

We prefer to do this action through a Agent job ( a diff approach is also welcome).

Are there any stored procs out there?

Appreciate your time.

-PN



View 6 Replies View Related

View, SP, & Trigger Reference

Nov 2, 1998

Can anyone recommend a good reference book on views, stored procedures (system stored
procedures as well) and triggers? I am in need of assistance in these areas.

Thanks!
Toni

View 2 Replies View Related

Can Reference A Column In A View

Aug 14, 2014

how can i reference a column in a view.

My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.

View 2 Replies View Related

Can I Add A Foriegn Key On A Indexed View To Reference Other Table

Nov 8, 2006

Is there anybody familiar with indexed view here?

Can I add a foriegn key on a indexed view to reference other table?

Thanks!

View 1 Replies View Related

Application Role How Query A View Whose Reference Table Is In Another Db?

Aug 23, 2007

Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()

I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:


The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania

View 5 Replies View Related

SQL 2012 :: DENY Permission On Column Subsequently Reference In View?

Aug 6, 2014

Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?

Code is below:-

--create test user
CREATE USER TestDenyOnViewUser WITHOUT LOGIN
GO

--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply)
CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo

[Code] ......

View 6 Replies View Related

Permissions To View INFORMATION_SCHEAM Views

Aug 9, 2002

SQL7, sp3

What specific permissions do you need to be able to view information_schema views? I thought public role had permissions to select on these views, but this is not the case? What do I do?

my developers have db_reader, db_writer, and db_ddladmin. They do not have db_owner. If I make them Sysadmin in sql they can view them, but that doens't fit in our security setup we have. THoughts?

Thanks,

View 2 Replies View Related

View That Unions Two Other Views Does Not Work

May 1, 2006

I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.

When I run the two views as below it works fine

Select * from V1
Union
Select * from V2


I then created another view V3 of the above union as

Create view V3
As
Select * from V1
Union
Select * from V2

Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables

View 1 Replies View Related

View That Would Output Data From Two Views

Aug 8, 2007

OK,

I have two data tables where depending on the time line item it is, it needs a different join.

So, it's like this:

Table Credit Card
Table Memo

If Credit Card is VISA, join on column A
If Credit Card is MA, join on column B

So, I created two views. One view has all the info joined on column A. The other view has all the info joined on column B.

How do I create a third view that will output all the data from View A and View B without making it into a cartesian product?

I can't change the table structure due to legacy application/data issues.

Thanks!

View 3 Replies View Related

Sql Views - Embedded View Work-a-rounds

Jul 11, 2007

hi guys,

i've been asked to re-write a sql view. the view itself contains several calls to other views (embedded). is there a way to get around using embedded views. I've written the same query up using temp. tables but obviously temp. tables can't be used in views?

Is there any special things I should be looking for?

View 2 Replies View Related

CREATE VIEW, Seperate One Column In Two Views..

Oct 17, 2007

I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----


From the table meta_host_types;

id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid

I would like to seperate value into type_value and connection_value, because it holds both values.

Any tip?

bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)



d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)

View 3 Replies View Related

SQL Views - Embedded View Work-a-rounds

Jul 12, 2007

Hi guys,I've been asked to re-write a sql view. The view itself containsseveral calls to other views (embedded). Is there a way to get aroundusing embedded views. I've written the same query up using temp.tables but obviously temp. tables can't be used in views?Is there any special things I should be looking for?

View 2 Replies View Related

Transact SQL :: Combine Two Views Into One Statement Or One View

Oct 15, 2015

I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.

CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,

[Code] ......

View 3 Replies View Related

Creating A Single View From 2 Existing Views

Aug 9, 2007

I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON

The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:

EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)


Below is my select command:

------

SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT

GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

ORDER BY
dbo.vw_SEASON06.EVENT

-----

This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.

How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?

Any help is appreciated!

View 7 Replies View Related

Im New To Using Views, And This Program They Have Me Working On Is Using Them Quite Heavily..(cant Read From A View)

Oct 11, 2007

This is my first post, so if i have not posted things in the best manner please lemme know how to be more informative,clear, so that i can learn.

So i have made a view with the following command



GO

/****** Object: View [dbo].[AppraisalView_C] Script Date: 10/11/2007 12:10:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[AppraisalView_C]

AS

SELECT a.Counter

,a.DateCreated

,a.DateModified

,a.UserCreated

,a.UserModified

,a.AppraisalDate_C

,a.TypeID_C

,a.Customer_C

,a.Employee_C

,b.Notes_C

,b.Value_C

,b.AppraisalLineItemID_C

,b.AppraisalID_C

FROM dbo.Appraisal_C AS a

INNER JOIN dbo.AppraisalLineItem_C AS b ON a.AppraisalID_C = b.AppraisalID_C



---------------------------------------------------------------------------------------

the program im working on creates the SQL call to read from this view and creates
the following query


SELECT A.[AppraisalDate_C], A.[AppraisalID_C], A.[AppraisalLineItemID_C], A.[Customer_C], A.[Employee_C], A.[Notes_C], A.[TypeID_C], A.[Value_C]

FROM AppraisalView_C A

WHERE [AppraisalView_C].[AppraisalID_C] = 'APP-000006'


but I end up getting the dreaded "Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AppraisalView_C.AppraisalID_C" could not be bound." error....

I cant change the Query that is called, but i can change the view, what is wrong?

View 4 Replies View Related

View Of All User Objects (Tables, Views) With Their Replication State NEEDED...

Jun 22, 2007

Hi!



There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):






Code Snippet

CREATE VIEW [dbo].[ViewREPL_PublishedObjects]

AS

SELECT TOP 100 PERCENT

CASE [xtype]

WHEN 'U' THEN 'Table'

WHEN 'V' THEN 'View'

ELSE NULL END AS [Object Type],

[name] AS [Object Name],

CASE WHEN [replinfo] = 0

THEN 0 ELSE 1

END AS [Replicated]

FROM [sysobjects]

WHERE

[xtype] in ('U', 'V')

AND [status] > 0

ORDER BY

(CASE [xtype]

WHEN 'U' THEN 1

WHEN 'V' THEN 2

ELSE 10

END),

[name]



Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].



So, I need a view with same functionality, that will work under SQL-2005 and 2008.



Please, help!

View 2 Replies View Related

SQL Server Admin 2014 :: How To Grant User Permission To View Specific Views

Aug 5, 2015

I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....

View 3 Replies View Related

SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference

Sep 10, 2014

I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:

Error system.NullReferenceException: Object refrence not set to an instance reference.

In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine

[code]...

is there something which i did not install or what can be the error?

View 0 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

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

Large Views Vs Multiple Small Views

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

Quicky : Views Of Views Of Views

Feb 22, 2007

Hello,

to make a report easier I'm developing it using a view of joined views of joined views.

Is there any significant performance penalty as opposed to just having one big select?

Cheers.

View 1 Replies View Related

How To Refresh

Nov 9, 2005

sql2000

I have an existing data warehouse that is refreshed nightly from system1.

We will be moving to a new system2 1/1/06.
The old system1 will be phased out by the end of 2006.
Several tables on system2 will have different keys.

I have to move the data from both systems in the data warehouse.

Here's the issue.
I have to totaly refresh the data warehouse nightly.
Once system2 is phased out how do I keep the data from system1
and refresh only data from system2.

View 7 Replies View Related

Views Dependent On Other Views

Mar 14, 2006

Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.

View 5 Replies View Related

Need Refresh Connection Or Something ?

Jun 23, 2007

HI,
I have a problem with  the data that I want to delete and insert new one on my SQL.It will work if I just delete a row. And work well if I just insert a row.But it will not work if I delete and insert at once on one procedure.
Here's the code :
    Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged        Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue        ' Call Sub RemoveUsersInRoles        RemoveUsersInRoles()        ' Call Sub AddNewUsersInRoles        AddNewUsersInRoles()    End Sub        Sub RemoveUsersInRoles()        '   Create SQL database connection        Dim sqlConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")        Dim cmd As New SqlCommand        cmd.CommandType = CommandType.Text        '   Delete that row with correct UserId        cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')"        cmd.Connection = sqlConn        sqlConn.Open()        cmd.ExecuteNonQuery()        '   Close SQL connecton        sqlConn.Close()    End Sub        Sub AddNewUsersInRoles()        '   Create SQL database connection        Dim sqlConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")        sqlConn.Open()        '   Create new row with new UserId        Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')"        Dim sqlComm As New SqlCommand(sqlString, sqlConn)        Dim sqlExec As Integer = sqlComm.ExecuteNonQuery        '   Close SQL connection        sqlConn.Close()    End Sub-------------------------------------------
I can delete the row if I call 'RemoveUsersInRoles'And I can insert new one if I call 'AddNewUsersInRoles'
But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.If the row not exist, it will insert new row. And that what I want.But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.
Do I need some 'pause' or 'refresh connection' here ?
Thank You.

View 2 Replies View Related

Refresh Issue

Mar 21, 2001

Our application is C++ and datbases are Sybase. We are 11.5.1 Sybase. Lately some of our clients have complained about data refresh problems. That if a user has made some changes within the application then these data changes are taking long time to reflect to other users in the system. We do have in our application processes to automatically refresh data for other users but for some odd reason it is not working properly. Also every day we run sp_recompile and sp_stat on the tables and stored procs. Any ideas or leads ?

Thanks
Gohar

View 1 Replies View Related

Refresh Data

May 12, 2005

I want refresh data de SQL Server, every certain time at night, but I want that it does only, automaticy.

In SQL sentencs or DTS
What I can do. Thanks

View 1 Replies View Related

RECOMPILE / REFRESH UDF?

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

Nightly Refresh

Sep 19, 2007

Please help.I have Database A (Archived data), Database B (Transactional data).Both A and B do not have any timestamps in any tables.I need to bring A (only once because it is archived data) and B withonly differentials into Database C for reporting purposes.Currently C is cleared before loading B with differentials every nightand A is not loaded since it takes 15 hours to load.Now I want to bring A once and leave it and then bring B every nightwith differentials without clearing C.Please suggest some solutions.Thank You

View 1 Replies View Related

Refresh Default Value

Dec 14, 2006

Hello,



I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed



Any idea ?

View 5 Replies View Related







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