MS SQL Server :: Create Trigger For View

Create Trigger On View Failed
I want to create a trigger on a view for insert, but I got a message said 'Invalid object name'. I can select from this view. Followings are my scripts and error message:
(run on SQL 2000)
create table t1 (c1 char (10) NULL )
create view vt as select * from t1
create table log1 (c1 datetime NULL )

create trigger tr1 ON t1
for insert
AS
insert into log1 values (getdate())
----------all above succeed
create trigger tr2 ON vt
for insert
AS
insert into log1 values (getdate())

Server: Msg 208, Level 16, State 4, Procedure tr1, Line 1
Invalid object name 'vt'.

Thanks!

Theresa

View Replies ! View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP
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 Replies ! View Related

Grant CREATE VIEW, CREATE PROCEDURE ...
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 Replies ! View Related

Alter View / Create View
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 Replies ! View Related

How To Create New CLR Trigger From Existing T-Sql Trigger
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance

View Replies ! View Related

Using A Trigger On A View
I have never written a trigger before, but I have a couple of uses forthem now so I am trying. I have a view that I created and I want tohave this trigger, run anytime the view is run. I don't know if myproblem is with the trigger or permissions on the view, right now Ihave given everyone permission to select and update on the view (justtrying to figure this out). Here is the begining of my trigger.use OTBIf Exists (Select name from sysobjectsWhere name='trg_otb_update_catalog_udf' and type='TR')Drop Trigger trg_otb_update_catalog_udfGoCreate Trigger trg_otb_update_catalog_udfOn vw_OTB_catalogFor Insert, updateasUpdate ar_cust_udfset part_catalog=c1.parts_catalogetc...Everything in the update statement to the end of the trigger works asI intended. However when I run this it will not create the trigger,instead it errors and returns the following:Server: Msg 208, Level 16, State 4, Proceduretrg_otb_update_catalog_udf, Line 1Invalid object name 'vw_OTB_catalog'.However this is the correct view name.any ideas?Thanks,Mike

View Replies ! View Related

Problem Trigger/View
I got a view that gest information från a certain table. Then i got a trigger that should copy new information from this view to a specific table. How should this trigger know when new information has been inserted in the View?

CREATE TRIGGER copyRow
ON person_View
INSTEAD OF INSERT
AS
BEGIN
INSERT into kontakt (nr, telefon, adress)
values ('2', '3', '4')
commit transaction
END

View Replies ! View Related

View, SP, & Trigger Reference
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 Replies ! View Related

Trigger On View--is It Possible Or I Missing Something
hi...

is it possible to create a trigger on view object....

usually we divide our table in to a smaller pieces and use view to get the big picture of it..

my task is to record any/every select statement on each table.. so logically we create view and is it possible to use trigger(to record the accesses) in view..

please help..

or just give me a link to get that info..
tnx

View Replies ! View Related

Trigger Update From View
I have one table with following structure:

Field 1 : (Identity increment field - PK)
Field 2 : (data code - possible combinations are Number1 or Number2)
Field 3 : (data column)
Field 4 : (data column)

I also have two views over this table:

View1:
Select Field 3, Field 4 where Field 2 = Number 1

View2:
Select Field 3, Field 4 where Field 2 = Number 2

Is there any solution to know inside Insert Trigger through which view
Inserted row comes?
Example: If new record is added through View1 then trigger must set value in
Field 2 to Number 1 and if someone adds new row through View2 than Field 2
must have value Number 2. Field 2 MUST be hidden for users (I can't add this
field in views)

Thanks

Damir

View Replies ! View Related

Trigger On System View
Hi,

Is there a way to create a Trigger on the Systemview [sys].[dm_exec_connections] ?

The Statement below Fails.

CREATE TRIGGER [dbo].[TG_CLEAN_LOCK_ID] ON [sys].[dm_exec_connections]

AFTER DELETE

AS

I need a trigger to be fired when a Connection ([sys].[dm_exec_connections] .[connection_id]) drops.

Is it principally impossible to create a trigger on a system view or is it just a lack on permissions making my SQL fail?

Is there any other way for surveying wether a connection is alive or not, without polling?

Thanks in advance

Raimund

View Replies ! View Related

Trigger View/Edit
Hi all!

How do u view the triggers in sql 2000 qry analyzer. I know you can use sp_helptrigger-- which will give the list of triggers-- -- But, I need to be able to run command from qry analyze and see the script used to create triggers
Having limited access to the design tables-- wont be able to see trigger from sql design table

Thank you

Josephine

View Replies ! View Related

Problem Creating TRIGGER On A VIEW
Hello,

I have a problem that definitely has me stumped.

I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.

I keep getting the message...

Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.

Where vwDC_DocInfo is the name of the view.

Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.

The script is included below...

CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS

DECLARE @ModifiedDate AS DATETIME
SELECT @ModifiedDate = GETDATE()

-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM DELETED DEL

-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM INSERTED INS

View Replies ! View Related

What Right Should I Give To User To View Trigger ?
Hi.
Developers have rights
db_datawriter
db_datareader

what rights should I give them in order to view but not modify Triggers ?

View Replies ! View Related

Self Join Using Instead Of Update Trigger On A View
Hello all...I'd appreciate any help on this one.

I created a View...the view looks at four seperate tables.

Next, I created an Instead of Update trigger on that view. It works fine...for a regular UPDATE...SET.

However, it throws an error when I try an update and self join based on that view:

    Update T1
    Set RateUsed = T1.RateUsed
    From Taxroll..Taxroll T1 Join
          Taxroll..Taxroll T2 on
        T1.Asmt = T2.Asmt and
        T1.Taxyear = T2.Taxyear
    Where T1.Asmt = '123456789012'
    And T1.Taxyear = 2007
    And T2.RollChgNum = ''
    And T1.RollChgNum Like '%X'
    And IsNull(T1.RateUsed,'') > ''

Msg 414, Level 16, State 1, Line 2
UPDATE is not allowed because the statement updates view "Taxroll..Taxroll" which participates in a join and has an INSTEAD OF UPDATE trigger.

There are a few caveats:
First, I thought the join issue was in the view itself. I re-created the view using no joins...all subqueries and still get the error. Second, I re-created the Instead of Update Trigger with no joins, and still get the error.

Thanks!

View Replies ! View Related

Update Trigger On Linked Server View
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com

View Replies ! View Related

Please Help: Create View
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 Replies ! View Related

Create A View In VBA
Hi,Is it possible to create some SQL in VBA, 'run it' then view as a datasheetthe results?I'm trying to throw together a fairly large search form, and VBA seems thebest way to parse the parameters.Cheers,Chris

View Replies ! View Related

Need Help On Create View
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 Replies ! View Related

Create An SQL View From VBA
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 Replies ! View Related

Create View
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 Replies ! View Related

Create View
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 Replies ! View Related

Create View
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 Replies ! View Related

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

View Replies ! View Related

Create View
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 Replies ! View Related

Create View
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 Replies ! View Related

Create View Only
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 Replies ! View Related

How Can I Create IIf In A View
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 Replies ! View Related

Create A View NEED HELP!!
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 Replies ! View Related

Intead Of Update/insert Trigger On View Question
Hello all,SQL Server 2000 documentationhttp://www.microsoft.com/technet/pr...rt10/c3761.mspxstates that if view is using "NOT NULL" columns of a base table, theninsert/update performed on a view must provide dummy values for thosecolumns, and code of the trigger should ignore them.But I cannot reproduce this restriction. Code below pasted to QueryAnalysershows that I can not supply dummy values for "NOT NULL" fields when I updateview and still have update done. What do I miss ?VT/*--setup step 1. execute only inside of this commentSET NOCOUNT ONCREATE TABLE TestTable(keyField INT IDENTITY(1,1),dataField1 INT NOT NULL,dataField2 INT DEFAULT 1 NOT NULL)*//* --setup step 2. execute only inside of this commentCREATE VIEW TestView ASSELECT * FROM TestTable*//*--setup step 3. execute only inside of this commentCREATE TRIGGER TestViewTrig_IUON dbo.TestViewINSTEAD OF UPDATEASBEGINSET NOCOUNT ONUPDATE TestTableSETDataField1 = inserted.DataField1,DataField2 = inserted.DataField2FROMTestTable ttINNER JOINinsertedON inserted.KeyField = tt.KeyFieldEND*//*--setup step 4. execute only inside of this commentINSERT INTO TestTable (DataField1,DataField2) Values (1,2)INSERT INTO TestTable (DataField1,DataField2) Values (3,4)INSERT INTO TestTable (DataField1,DataField2) Values (5,6)*/SELECT * FROM TestView-- SQL Server lets me not specify DataField2 when update DataField1 orreverse,-- which is opposed to what documentation saysUPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3SELECT * FROM TestView/*-- remove test environmentDROP VIEW TestViewDROP TABLE TestTable*/

View Replies ! View Related

How To Get Information From System Table Or View For Trigger's Definition
Such as check all triggers that assign value to some columns ?

Thank you very much.

View Replies ! View Related

Help Me Create This Trigger
Hi everybody,How can I Update a field from another table by Trigger? Can someone sendme the statment to do it?I have a table called Clients with fields : ID_Clients, ClientAnd Another called Doc with fields : ID_Doc, ID_Clients, ClientThese tables are in different databases and I would like to esure theintegrity by add a Trigger to update in Doc´s table the field Clienteverytime it´s changed in the Client´s table.Thanks for Attetion.Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies ! View Related

HOW TO CREATE TRIGGER ?
Hii have 2 Tablefirst one : Customerwith 4 Fields : cst_no,cst_name,total_Debit,tot_creditsecond one : Transactionwith 5 Fields : Trns_no,Trns_Date,cst_no,debit,creditMY QUESTION:HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS INCUSTOMER TABLE FROM Transaction TABLEThank you

View Replies ! View Related

Create Trigger Help?
I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.

I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.

I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.

CREATE TRIGGER NotifyDateFieldUpdates
ON RelocateeRemovalist
For INSERT, UPDATE, DELETE
AS
DECLARE @RemovalistNumber VARCHAR(200)
DECLARE @RelocateID INT

/*InspectionDate */
DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber
FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID)
WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL

OPEN getInsp

FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT
RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON
(a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null

UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE()
WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber

FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
END

DEALLOCATE getInsp

GO

This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.

Regards and thanks

View Replies ! View Related

Help To Create Trigger
I am trying to create a trigger to help me get a the time duration but this is not working.

Code Snippet

CREATE TRIGGER Duratn

ON dbo.CONTACTS
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE dbo.CONTACTS
SET Duratn = (DATEDIFF(CallStartTime) - DATEDIFF(CallFinishTime))

My table is as follows:

No Caller CallStartTime CallFinishTime Duratn

10000 John 10/05/2008 18:13:00 10/05/2008 18:14:00 NULL

View Replies ! View Related

Trigger. How To Create?
What I was trying to use to create the trigger was the same code I would use on Sql Server Express:

                cmd.CommandText = "CREATE Trigger [contactsLastUpdate] on [contacts] for Insert, Update " +
                                     "AS " +
                                     "Begin " +
                                          "SET NOCOUNT ON " +
                                             "Update t " +
                                                "set syncLastUpdate = GetDate() " +
                                              "From contacts t " +
                                                      "Join inserted i " +
                                                      "On t.id = i.id " +
                                             "SET NOCOUNT OFF " +
                                     "End"; +

But I get an error message:

"There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = Trigger ]"

How do you guys create Triggers on SQL Server CE (2005)?

Thanks

View Replies ! View Related

Create A Trigger Through C#/.NET
Hi folks,

I have created a trigger that I can enter and works great from the sqlcmd terminal. When I try to submit the same query in a .NET project I get an error that "Create Trigger must be the first statement in a batch". The Create trigger is submitted by itself through the sqlcommand.executenonquery() method.

I am trying to create a database for a project but the only thing that I can't seem to get working is submitting this trigger.

Appreciate any help.

Dave

View Replies ! View Related

Create Trigger Help
i do have my 'Product' TABLE IN DATABASE 'ABC'

Product TABLE OUTPUT

PRODUCT_CODE PRODUCT_TYPE PRODUCT_DESCPRODUCT_ID PRODUCT_GROUP_CODE
6001 computer NULL ENVD14
6002 keyboard NULL ENVD14
6003 mouse NULL ENVD14
6004 cables NULL ENVD14
6005 processor NULL ENVD14

AND 'Product_Mst' TABLE IN DATABASE 'XYZ'

Product_Mst OUTPUT

PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14

Now i want TO CREATE TRIGGER such that every updation in Product TABLE will UPDATE the appropriate record IN Product_Mst

FOR example IF i fire below query IN ABC Database

UPDATE Product
SET PRODUCT_DESC = 'Available' WHERE Product_Code = 6001

Then the OUTPUT OF the Product_Mst shoub be..

Product_Mst OUTPUT

PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6001 1 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14

Means i want to increment the version by 1 and Insert that records into Product_Mst Table at every updation.

I hope i am clear with my question.

Regards
Prashant Hirani

View Replies ! View Related

Problem With Create View
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 Replies ! View Related

How Do I Create An Expression In A View?
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 Replies ! View Related

Create View From Cursor
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 Replies ! View Related

Can't Create An Index On My View
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 Replies ! View Related

How Can Create View Autonumber
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 Replies ! View Related

Create Update View
I think I need to create an update view, but am unsure of the syntax...
One of my programmers would like an Update View created so he can
use the view to update 2 fields in a table. (He is creating a new program
in Delphi on my SQL database.) I can create an update statement
if I know what value is to be inserted, but how do I write one for a value
that is yet to be determined? Here is my Billing_Desc table:
BD_ID pk int Not Null
BD_DESC text Null

Thank you in advance for your help!
Toni

View Replies ! View Related

Challenging : Create View
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 Replies ! View Related