Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
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?
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
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?
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?
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.
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?
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,
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?
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?
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.
We need two reports one is QTD and another one YTD..FOR QTD report is working fine.We are doing summing of YTD report as Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as APPROVEd We are going to select the YTD Q1 reports we need the output as 10 Summing of Q1 We are going to select the YTD Q2 reports we need the output as 10 Summing of Q1+Q2 We are going to select the YTD Q3 reports we need the output as 30 Summing OF Q1+Q2+Q3 We are going to select the YTD Q4 reports we need the output as 40 Summing of Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as DRAFT We are going to select the YTD Q1 reports we need the output as 0 Summing of Q1 We are going to select the YTD Q2 reports we need the output as 15 Summing of Q1+Q2 We are going to select the YTD Q3 reports we need the output as 15 Summing OF Q1+Q2+Q3 We are going to select the YTD Q4 reports we need the output as 15 Summing of Q1+Q2+Q3+Q4
We need a output view level.We are not using procedure in the report.
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....
In SSMS, when editing a step in a SQL Agent job, on the Advanced screen for a step there is a button called View by the Output File. How do you get that to be enabled? It's always disabled for me and I wish I could view the file right from the job. This is something I miss from 2000. However, the button is there so I would think that I am just doing something wrong.
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ... View definition includes no output columns or no items in the FROM clause. A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select Case_CaseId, Logged, CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting, CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating, CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected, CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved, CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved, CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed from (
SELECT Case_CaseId, MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged, MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting, MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved, MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved, MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed FROM CaseProgress GROUP BY Case_CaseId ) as temp order by Case_CaseId
My company has upgraded some of the database systems to SQL 2005 while other have a fresh install.
The problem we're seeing is that the view button located in the Advanced section of the steps in the Job properties windows is greyed out. The path is valid as we can copy the path and paste it at the run command and view the log.
Does anyone know how this problem can be resolved? If we select to log the results to a table; we have access to the corresponding view button but not the one for the file system logging.
We rely heavily on this feature since we used it often under SQL 2K. Can anyone provide any helpful details to resolve this or has anyone seen this problem before???
Hi, i'm wondering which is the best way to search data in a SQL Server. I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query. I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices? Thanks for your help.
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.
Recently i've been working on a new project that would partition a large table 2 smaller tables. I then create a view to union the 2 smaller tables(table A, B). I've been getting a strange error when i try to update, insert, delete a record through the view. "View needs partitioning column"....i find this strange. Both of my table have a cluster primary key consisting of 3 columns, and one of the 3 columns(date field) consist of a check constraint. The constraint is used to determine what record goes into which table. Am i missing anything else? The really strange part is sometime it works, and sometimes i get the error message.
Hi! Is it possible to add/delete/modidy data through views? What is the major difference between SQL Stored procedure & SQL function?One diff i know is using SP we can execute transact-sql statements.Is there any other difference?
Hello All, I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
i have a textbox which a user enters a numeric value i want it to use SqlDataSource and check if the value exists in any of the tables. in my text box the users would enter starting from '100000' or '200000' i want it to check the view that starts the # with '100000' and 2ed view starts '200000' With this i can check in one of the tables and make the selection. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>" SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)"> <SelectParameters> <asp:ControlParameter ControlID="txtReport" Name="ReportNumber" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
How can i make this possible ? i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?
Hi I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale: I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
I am moving an app from Access 2003 to C#/SQL Server 2005. The Access app has one front end exe and two back end databases. One back end db is for UK users, one for US users. The tables and structures are identical, but the data is different. UK users link to the UK back end, US users to the US backend. (The queries are in the front end)
In SQL Server, the view and stored procs will be in one database (KCom), the US data tables in another database (KUS), and the UK data tables in another (KUK). All databases are on the same server.
My question is how to let the views and stored procs in KCom know whether to pull the data from KUK or KUS.
In the front end app, I use ADO.Net to deal with the SQL Server databases.
I have not been able to find a model for this, but it must be somewhat common.
I willl have a lost of nested views and stored procs, but as a simple example, say I have a view in KCom which is just "Select * From Invoice Where InvDate > '01/01/2007'. The ADO request would come from the front end app which would know whether it wanted the data from KUK or KUS. How would I get the view to get the data from one as opposed to the other (KUK vs KUS) ?
Is there some other strategy for this situation, say use of connection strings? If anyone has an idea, or knows of an explanation somewhere on the net I'd greatly appreciate it.
our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.
I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient.
