Create A View NEED HELP!!

Jan 26, 2006

Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.

Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'

this is what I get.

server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.

albanie

View 20 Replies

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go

Thanks Guys.

View 14 Replies View Related

Grant CREATE VIEW, CREATE PROCEDURE ...

Apr 12, 2006

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

View 8 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

Create View

Jan 8, 2001

When I use following command it works fine.

CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom

but if I use following command with condition it gives me an error.

if not exists(select * from sysobjects where name ='ikw_custom' and type='V')
begin
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
end

help will be appriciated.
Thanks in advance
-Balbir

View 1 Replies View Related

Create View

Apr 15, 2006

I am learning how to work with MSSQL
here is what I like to do
I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do
Thank you

View 14 Replies View Related

Create An SQL View From VBA

Sep 25, 2005

Hello All;

I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

Can anyone tell me if I can create an SQL View from VBA?

I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

Does any of this make sense, and am I on the right track??

View 2 Replies View Related

Create View

Mar 18, 2006

hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))

if i want to create a view
how ?
thank you!

View 5 Replies View Related

How Can I Create IIf In A View

Nov 29, 2007

I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.

How can I do this ?

View 13 Replies View Related

Please Help: Create View

Jul 23, 2005

Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com

View 3 Replies View Related

Need Help On Create View

Jul 20, 2005

Hi.I created a simple view with the following statements:CREATE VIEW dbo.VIEW1ASSELECT dbo.VIEW_ALL.ID,dbo.VIEW_ALL.Code,Another.dbo.OTHER_VIEW.Label as SpecialCodeFROM dbo.VIEW_ALL LEFT OUTER JOINAnother.dbo.OTHER_VIEW ON(dbo.VIEW_ALL.Code + '_0') = Another.dbo.OTHER_VIEW.LabelWhen I run :select * from VIEW1 where code = 'abcde',I would get all 10 rows that are of the same data where the code is'abcde'. But what I thought it should return is really just one rowsince the rest of the 9 rows are exactly the same! I know that I canget my result if I useselect DISTINCT * from VIEW1 where code = 'abcde'But, is there any way I could change my SQL statements(CREATE VIEW)above so that I automatically get 1 row instead of 10 rows of exactlythe same value without using DISTINCT?Thank you for any help.

View 7 Replies View Related

Create View

Jan 8, 2008

I have a table in my program which going all the invoices,and I want to create a view where I can see the quantity of the products as many times by 1 as the quantity in the invoice:

Invoice:

Product name quantity

woods 3

at the view

woods 1

woods 1

woods 1

Thank you.

View 4 Replies View Related

Create View

Aug 2, 2006

how to create view in sql server 2005 that recognize Identity And Primary key of base table

View 2 Replies View Related

Create View Only

Aug 14, 2007

Good afternoon,

I have a user that needs read only access to all tables in a particular database - Which is working fine.

He also needs to create views, as well as the above, but nothing else.

Is this possible?

Many thanks.

View 1 Replies View Related

Create View

Mar 29, 2006

CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;

its working with oracle

how to chenge in t-sql

thanx

View 5 Replies View Related

How Do I Create An Expression In A View?

Jul 8, 2006

Hey guys, im really new ASP.net. I just upsized my access database to SQL Server Express and I'm trying to mimic some view functionalitty I had in access. I think they call it the expression builder.So bassically Im trying to make a column that does some calculations on other columns. Like:if columnA-columnB > 100 then columnC='YES' else columnC='NO'Does anyone know how to do this? I want to add this column in a view. Can i perhaps just do it in the datagrid? I'm so frusterated at the moment, ive been trying for about 3 hours!Thanks in advance for your help.

View 1 Replies View Related

Problem With Create View

Sep 17, 2007

I need to create view from the results of executing dynamic SQL.Query like this have incorrect syntax: CREATE VIEW vModuleCatalogObjectValues AS EXECUTE sp_executesql @sql, @paramDefinition, @moduleId; ENDWhat i need to do to get it work?!

View 1 Replies View Related

Create View Permision Only

Jul 16, 2001

Hi,

Is there a way you can give a user a permission to "Create View" only but not to create other objects like tables or SP's in a database.

Thanks

View 1 Replies View Related

Challenging : Create View

Feb 6, 1999

Hi,

I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :

'DB-Library Process Dead - Connection Broken'

CREATE TABLE dbo.SILT01 (
SILF01A varchar (8) NOT NULL ,
SILF01B varchar (25) NULL ,
SILF01C smallint NULL ,
CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED
(SILF01A)
)
GO

CREATE TABLE dbo.SILT19 (
SILF23A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF01A
)
)
GO

CREATE TABLE dbo.SILT20 (
SILF23A varchar (8) NOT NULL ,
SILF15A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF15A,
SILF01A
)
)
GO

CREATE TABLE dbo.SILT23 (
SILF23A varchar (8) NOT NULL ,
SILF23B varchar (30) NULL ,
SILF23C varchar (40) NULL ,
SILF23D varchar (8) NULL ,
SILF23E varchar (1) NULL ,
SILF23F text NULL ,
SILF23G varchar (25) NULL ,
SILF23H varchar (8) NULL ,
SILF23I varchar (25) NULL ,
SILF23J varchar (25) NULL ,
SILF23K text NULL ,
SILF23L varchar (25) NULL ,
SILF23M varchar (8) NULL ,
SILF23N varchar (25) NULL ,
SILF23O varchar (25) NULL ,
SILF23P text NULL ,
SILF23Q varchar (25) NULL ,
SILF23R varchar (8) NULL ,
SILF23S varchar (25) NULL ,
SILF23T varchar (25) NULL ,
SILF23U varchar (15) NULL ,
SILF23V varchar (15) NULL ,
SILF23W varchar (15) NULL ,
SILF23X varchar (15) NULL ,
SILF23Y varchar (15) NULL ,
SILF23Z varchar (15) NULL ,
SILF23A1 varchar (15) NULL ,
SILF23A2 varchar (15) NULL ,
SILF23A3 varchar (15) NULL ,
SILF23A4 varchar (15) NULL ,
SILF23A5 varchar (15) NULL ,
SILF23A6 varchar (3) NULL ,
CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED
(
SILF23A
)
)
GO

create view ANSI_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A)
INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A)
LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND
(SILT19.SILF23A = SILT20.SILF23A)

create view Conventional_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM SILT01, SILT19,SILT23, SILT20
WHERE ( SILT01.SILF01A = SILT19.SILF01A AND
SILT19.SILF23A = SILT23.SILF23A ) AND
( ( SILT19.SILF01A *= SILT20.SILF01A) AND
(SILT19.SILF23A *= SILT20.SILF23A) )

SELECT * FROM ANSI_VIEW
'DB-Library Process Dead - Connection Broken'

SELECT * FROM Conventional_View (Works fine)

Expecting Help or Suggestions from experts.

Thanx,

Gunvant Patil( gunvantp@mail.usa.com )

View 1 Replies View Related

Can't Create An Index On My View

Dec 1, 2004

G'day all.

I am trying to create an index on a view that joins two tables.

I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'

Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.

Code is below and any help greatly appreciated.

CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECTt_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)

GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)

View 1 Replies View Related

Create View From Specification (was Help!!!!)

Jun 29, 2007

I have to try and get my head around this question and ive never seen sql this advanced before.please set me in the right direction.thanks in advance.Using the meeting table schema below provide a SQL statement to create a view in Microsoft SQL Server that will return only 1 record displaying the contents of the MEETING_NUMBER field. The rules for the view are as follows:• The START_DATE field which contains the start date of the meeting record must contain a value before the current system date.• The START_DATE field must be the closest date to the current system date.ID int (4)DOC_CLASS varchar (30)DOC_NUMBER int (4)REVISION int (4)START_DATE datetime (8)FINISH_DATE datetime (8)MEET_TIME varchar (5)VENUE varchar (50)TYPE varchar (50)MEETING_NUMBER varchar (11)CUTOFF_PERIOD int (4)CUTOFF_TIME varchar (5)AGENDA_STATUS varchar (50)SUBJECT varchar (50)SPONSORING_MIN varchar (50)SUPP_AGENDA_STATUS varchar (50)Possible answer:select meeting_numberfrom meetingwhere start_date < getdate()order by start_date desc

View 14 Replies View Related

How Can Create View Autonumber

Feb 4, 2004

now i have two table

1st table
type_id type_name
4 a
5 b
6 c

2nd table
category_id category_name type_id
6 aaa 4
7 bbb 5
9 ccc 6

i want to join two table to create view
autonumber type_name category_name
1 c ccc
2 b bbb
3 a aaa

how can i write this sql to create autonumber field

thanks

View 1 Replies View Related

Trying To Create A View Across Two SQL Servers

May 20, 2008

I have two machines running SQL server. I need to create a view that contains data from both servers on Server 2.

ex:
Server 1 contains:
database (Employees)
Table (EmpMaster)
Column (EmpNumber)
column (EmpName)

Server 2 contains:
database (Training)
Table (TrainingEntry)
Column (EmpNumber)
Column (TrainingDate)
Column (TrainingDescription)

If the two tables were on the same server and in the same database, I'd just write the following:

select
TrainingEntry.EmpNumber
,Employee.EmployeeName
,TrainingEntry.TrainingDate
,TrainingEntry.TrainingDescription
from TrainingEntry
Left Outer Join Employee on TrainingEntry.EmpNumber =
Employee.EmpNumber

The question is how do I handle going between two databases, and more importantly, how do I handle linking between two servers?

All examples are appreciated.

Thanks

View 6 Replies View Related

CREATE INDEX ON VIEW

May 28, 2008

Cannot create index on
view 'View name ' because the view is not schema bound." What is the
Schema bound ? How can i create the view Schema bound ?

Pls help me out Sir

Yaman

View 1 Replies View Related

Create A View In Server

Oct 4, 2014

Trying to create a view in SQL Server 2014, like

create view test
as
select billno, CONVERT(char(10), billdt, 103) from BILL

but view not created, error

Msg 4511, Level 16, State 1, Procedure test, Line 8

Create View or Function failed because no column name was specified for column 2.

View 1 Replies View Related

Create View From Table

Oct 6, 2014

I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'.Also when viewing the original table and the new view the structures are slightly different.

CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'

View 5 Replies View Related

Trying To Create A View With Parameters.

Sep 13, 2007

Trying to create a view with parameters. I want to use view inside the SP. But I'm getting an error. Why?

-- 1st
-- drop table funkete
create table funkete (funketeid int, funketeFname varchar(20))
insert funkete
select 1, 'funkete' union all
select 5, 'amanda' union all
select 6, 'funkete'
go

-- 2nd
-- drop proc a
create proc a (@funketeid int)
as
select @funketeid, funketefname from funkete
go

-- 3rd
exec a @funketeid = 4
go

-- 4th
create view vw_funkete4 as
declare @funketeid int
select @funketeid, funketefname from funkete
go

=============================
http://www.sqlserverstudy.com

View 6 Replies View Related

Is There Anyway To Create A View Within A Function

Dec 5, 2007

Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said

'CREATE VIEW' must be the first statement in a query batch.

I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.

CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END

View 4 Replies View Related

How To Create A View With Schemabinding

Feb 28, 2008

hi,
my sql query is like this
create view table1 with schemabidning
as
select columnname from table1

but it is giving error msg like this
you cannot create view 'Cannot schema bind view'

please help me out how to create a view with binding to underlying table.

Raghu sunkara.

View 2 Replies View Related

Create View From Cursor

May 12, 2006

I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend

View 2 Replies View Related

Cannot Create A Order By View

May 22, 2006

Hello,

I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.

CREATE VIEW [dbo].[vw_SalesRep_Chaining]

AS

SELECT Top 100 percent Rep_id AS RepId,

Rep_cd AS RepCode,

Region as Region,

CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'

WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'

WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'

ELSE '' END AS Sub_Region,

RSM AS RSM,

UPPER(Regional_Manager) AS Regional_Manager,

Market,

Rep_Manager_id AS SalesManager,

UPPER(MarketManager) AS MarketManager,

New_Position AS NewPosition,

Rep_Status AS RepStatus,

UPPER(Rep_Payroll_name) AS PayrollName

FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,

Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind

FROM dbo.SalesRep

WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR

(current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A

INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM

FROM dbo.SalesRep AS SalesRep_2

WHERE (current_ind = 1)) AS B

ON A.Rep_Manager_id = B.Repid

INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1

WHERE (current_ind = 1)) AS C

ON B.RSM = C.Repid1

WHERE (A.Region IS NOT NULL)

Order by Rep_Id, Rep_Cd

This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd

View 1 Replies View Related

Question About Create View

Mar 31, 2008

Hi there

I made a view for some tables to be used for linking to Access2007
and I was wondering if I have to manually keep updating the view with new records or does it automatically get updated as the actual table gets updated??

thank you,

View 1 Replies View Related