Hello everyone,
Can someone help me with updating data into a view using triggers?
I am struggling hard to write a trigger or stored procedure to update the values on the base tables of the view.
Please help me out...
Thanks,
Godwin
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
Hi,
In what way a view can be updated / inserted / deleted (records)? Kinldy make me clear on this. Have gone through lot many blogs / articles and got more and more confused.
Thanks,
Rahul Jha
I don't know if this is possible but, I have a view that retrieves data between 2 dates. The data is then exported to an Excel spreadsheet. My question is this: Is there some way I can automatically change the date in my alter view window? For example, I want to extract the data at the beginning of each month for the previous month. Then whoever is looking at the data can then refresh every month to get the previous months data. I want something that will automatically update my view every month with the previous month, without me having to go in and physically doing it. Is there a way, and how complicated would it be?
Thanks.
Hello everyone,
I'm using sqlserver 2005 with SQL management studio.I have a view and it has a problem.
The problem is that...SQL management studio automatically generated some sql script for me to update the view.But,it doesn't work.HELP ME please! This is the script it gave me...
-------------
UPDATE [skips].[dbo].[Schedule]
SET [ContractId] = <ContractId, int,>
,[DriverId] = <DriverId, int,>
,[TruckId] = <TruckId, int,>
,[Completed] = <Completed, bit,>
,[Day] = <Day, varchar,>
WHERE <Search Conditions,,>
--------------
When I try to execute it...I get this error ,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.
How do I make this work and update the view?
Thanks so much in advance
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
I have a database that has SQL 2000 as the engine and Access 2003 on the front end, we scan our documents and hyperlink them through Access, the address is stored in SQL Server but we are wanting to move SQL, the scanned documents along witht he ADP over to a different server so instead of the files linking to \gcfso1 they will link to \gcfsql. is there a way to update the address link in SqL to link to \gcfsql as an update view rather then relinking them by hand????
Help
This post concerns updating across a partitioned view, and not unlike others about this subject I am getting this error:
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'dbII.dbo.MyTable' is not updatable because a partitioning column was not found.
I am aware of the rules for defining a partitioning column, but interpreting them may have beaten me. So perhaps I haven't abided by all the rules. How to spot which one(s) from the view and table definitions? I suspect the CHECK constraint does not allow the ASCII function, but I can't see how to avoid using it given SYSCODE entries in one table are like "[A-Z]%" and in the other are like "[0-9]%".
Otherwise, I suspect it is because one of the tables has, by legacy, a text column and the view is casting it to varchar(MAX). I also suspect it is because there's a second column with a unique index. These aren't mentioned in the rules (are they?).
Here's the view definition:
SELECT SYSCODE, COL2, CAST(COMMENTS AS varchar(MAX)) AS COMMENTS
FROM dbo.MYTABLE
UNION ALL
SELECT SYSCODE, COL2, COMMENTS
FROM OTHERDATABASE.dbo.MYTABLE AS MYTABLE_1
And here are the table definitions:
-- Table in the database where view is defined
CREATE TABLE [dbo].[MYTABLE](
[SYSCODE] [char](12) NOT NULL,
[Code]....
Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN SET @Tstamp = @CHANGED_Tstamp RAISERROR('This survey has already been updated since you opened this record',16,1) RETURN 14 ENDELSE
BEGIN
SELECT * FROM tblSurvey WHERE Constructor_ID = @Constructor_ID AND --Contractor_ID = @Contractor_ID AND Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND IsAbortiveCall = @AbortiveCall IF @@ROWCOUNT>0 SET @ActionDone = 'Update'
UPDATE tblSurvey SET Constructor_ID = @Constructor_ID , SurveyorName_ID = @SurveyorName_ID , Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) , IsAbortiveCall = @AbortiveCall , Note = @Notes WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin exec dhoc_ChangeLog_Insert 'tblSurvey', @Survey_ID, @User_ID, @ActionDone, 'Main Details', @Survey_ID
end else BEGIN RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1) RETURN 10 END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1, dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code, dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID, dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2, dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name, dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date, dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID, LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address, dbo.tblProperty.TenureFROM dbo.tblPropertyType RIGHT OUTER JOIN dbo.tblProperty LEFT OUTER JOIN dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?
Thanks
I notice that when I change my table schema, the view that was created based on the older schema remains using the older schema, and when I try running it, it will give me error.
I assume that's because the view is still using the old execution plan? Is there a way to force an automatic recompilation of execution plans for all views when there is a change to the underlying table?
Thanks!
Hello everyone,
I'm using sqlserver 2005 with SQL management studio.I have a view and it has a problem.
The problem is that...SQL management studio automatically generated some sql script for me to update the view.But,it doesn't work.HELP ME please! This is the script it gave me...
-------------
UPDATE [skips].[dbo].[Schedule]
SET [ContractId] = <ContractId, int,>
,[DriverId] = <DriverId, int,>
,[TruckId] = <TruckId, int,>
,[Completed] = <Completed, bit,>
,[Day] = <Day, varchar,>
WHERE <Search Conditions,,>
--------------
When I try to execute it...I get this error ,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.
How do I make this work and update the view?
Thanks so much in advance
I have gridview bound with SqlDataSource control. One of the grid columns is a dropdown list which is populated programatically, (the gridview has template column with edittemplate field with dropdown list), dropdown Value contains of course collection of IDs.Please tell me how to declare UpdateCommand which will update the row with the dropdown list. The problem is with parameter regarding the column with dropdown list. When i declare this parameter and bind it to SelectedValue property of the dropdownlist from edittemplate tag - it doesnt work, because datasource control cannot see this dropdown list. So, how am i supposed to declare the ID of the row being updated in <updateParameters>? Thanks in advance!
View 4 Replies View RelatedHi,
I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').
How do I handle in this case? Did any one come across such case?
Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?
If yes, how to do?
Thanks
mka
building some kind of ecommerce site.
I want to allow the user to modify the image that he has stored for a product.
The image is stored in a directory, in the table GAMME I only have the image name.
When the user selects a new Image, I first delete the old image in the directory, then save the new image, but what I can't do is to update the table with the new image name, when I write sqldatasource1.update() I have an error "the dictionnary passed with old values is empty"(translated from frengh).
the update statement in sqldatasource1 is: " UPDATE Gamme SET imagename = @imagename, imagesize = @imagesize WHERE (product_ID = @original_product_ID)"
on events sqldatasource1.updating I have written:
e.Command.Parameters("@imagename").Value = (FileUpload1.FileName).ToString()
e.Command.Parameters("@taillevignette").Value = FileUpload1.PostedFile.ContentLength
e.Command.Parameters("@original_product_ID").Value = Session("Product_ID").ToString
this is not written but the session("product_ID") is the good value
the problem must come from the last lign, but I don't Understand the problem and how to solve it.
Hi,
I would like to update about 4,000 records. I would only be updating one column for 4,000 rows in a table that has 50,000 rows. The update information is not the same for each row. I will be updating this from an excell file. Can somebody please tell me how to do this without messing the rest of the data in the table?
Thank you
JG
I rebuilt my databases using bcp,in order to change sort order and char set. That was OK,the data were re-inserted,but now i can only modify data by SQL Server Enterprise Manager.When a try to chage data by Visual Data Manager or VB application using ODBDC,it's accused that the database is not updatable,it's read only.But in the properties it's updatable.Anybody has a hint of what'sgoing on??
Thanks for yoour attention again,
Luciano
Hi,
What is the best to update line_no in following code.
As you can see 1 invoice can have multiple lines.
I would like to assign sequential number to all lines in invoice.
CREATE TABLE #xx (invoicenumber INT, line_no int )
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
SELECT * FROM #xx
result of select * from #xx should be:
10001
10002
10003
10004
20001
20002
20003
20004
Thanks
mk_garg
I have posted on this problem before, and got some wonderful help, but the problems keep growing!
In my table, I have projects,activities, credit amount, debit amount, starting balance.
Each project can have several activities associated with it. Each project has a starting balance. Each activity posts an expense to the starting balance of the project.
If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". Then I want to re-adjust the balance and
check the second activity. If there is still enough money to handle the charge from the second activity, then the expenses can be posted and the balance
adjusted again. In this checking, I am creating a field called status, and flagging if the activity can clear or not, and a new balance field. The data I have can fall into
different examples, listed below.
First example:Only one activity for a project
project: 122400
activity: 0000
Cr_Amt: 2145.00
Dr_Amt: 0
Balance: 1190.00
In the above case, as the balance is less than the Cr_Amt, I want to set the Cr_Amt = Balance, and the new balance = 0 and have done that as follows:
Update r
set r.Post_Cr = r.Bal_2300,
r.new_Status = 'Can Clear',
r.new_Balance = 0
From Rev_Rec_Check r
WHERE r.project IN (Select project
from Rev_Rec_Check d
group by d.Project
having count(d.project) = 1)
That seems to work for me.
Second Example: More than one activity, with credits and debits
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452
project: 145587
activity: 0110
Cr_Amt: 3953
Dr_Amt: 0
Balance: 1452
So, in this case, I need to add the Dr_Amt to the Balance and create a new balance (3952), and then check that the Balance can cover the Cr_Amt
UPDATE t
SET t.New_Balance=(t.Bal_2300 + b.postDrSum) - b.PostCRSum,
t.New_Status=(CASE WHEN (t.Bal_2300 -b.PostCRSum) >0 THEN 'Can Clear'
ELSE 'Still Check'
END)
FROM Rev_Rec_Check t CROSS APPLY
(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM Rev_Rec_Check a
WHERE (a.project =t.project)
AND (a.activity<=t.activity)) b
This does seems to work, and gives me the correct status, and adjusts the new balance correctly, but I realize that doing that doesn't achieve what I want.
Ideally, in this example - where there is more than one project/activity, this is what I would like to see:
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452
newBalance: 3952
project: 145587
activity: 0110
Cr_Amt: 3952
Dr_Amt: 0
Balance: 1452
newBalance: 0
I want the amount that gets posted (the Cr_Amt) to reflect what is left in the balance.
There could be cases where there are several activities for one project, so I want to be able to scroll through each activity and post what I can from what
they have left in their balance.
I don't know how to approach this. Am I making it more complicated than it actually is? Have I made any sense in trying to explain it?
Thanks in advance for any help!
skumar writes "Hi guys,
i'm a newbie in database and i need some ideas for the below mentioned
problem.
i'm creating sql table from a txt file using DTS package, now once the table
gets created, i need to multiple some revenue accounts with xyz number and
expense accounts with abc number.
i'm thinking more towards the line of writing a store procedure but don't
have any experience in it.
PLease guide me in the right direction, also if a good book could be refered
for future; which will help me query language and this kind of issues, that
will be GREAT!!
Thanx in advance"
I have the following below. I have a page that has a text box. I want it to look in the database (SQL Server 2005) and display the number (Assessor_Score) in the box if it exist. If it does not exist I want the user to put a number in and have it insert a new record to the database. I also want the user to be able to change the number that does show up. The code below just inserts two identical records into the database. Also, how do I assign Assessor_Score a value from the textbox to use in the insert statement? Thanks
Dim pageTitle, sRedirect, str
pageTitle = "QRS Assessors's Score"
dim iAsmtID, sSubScale_Short, Assessor_Score, sScale, sSubScale
Assessor_Score = request.Form("Assessor_Score")
sRedirect = request.QueryString("sRedirect")
iAsmtID = request.QueryString("iAsmtID")
sScale = request.QueryString("sScale")
sSubScale_Short = request.QueryString("sSubScale_Short")
sSubScale = request.QueryString("sSubScale")
sFormAction = "/Member/FSR/FSR_ScoreSheet.asp?iAsmtID=" & iAsmtID
'Open Connection
set conn = Server.CreateObject("ADODB.Connection")
conn.open NCRLAP_Conn
set rs = Server.CreateObject("ADODB.Recordset")
set rs2 = Server.CreateObject("ADODB.Recordset")
''''''''''''''''''Get QRS Assessor's Score''''''''''''''''''
str = "SELECT AssessorScore.iAsmtID, AssessorScore.sSubScale_Short, AssessorScore.Assessor_Score FROM AssessorScore WHERE (((AssessorScore.sSubScale_Short)='" & sSubScale_Short & "') AND ((AssessorScore.iAsmtID)=" & iAsmtID & "))"
set rs = conn.execute(str)
If rs.EOF = True then
'This score is not currently in Assessor_Score
'Insert now
str = "INSERT INTO AssessorScore (iAsmtID,sSubScale_Short,Assessor_Score) " & _
"VALUES ('" & iAsmtID & "','" & sSubScale_Short & "','" & Assessor_Score & "')"
response.Write(str)
conn.execute(str)
Else
This score is currently in Assessor_Score
Assessor_Score = rs("Assessor_Score")
UPDATE now
str = "Update AssessorScore SET AssessorScore.iAsmtID = " & iAsmtID & ", AssessorScore.sSubScale_Short = " & sSubScale_Short & ", AssessorScore.Assessor_Score = " & Assessor_Score & " WHERE (((AssessorScore.sSubScale_Short)=" & sSubScale_Short & ") AND ((AssessorScore.iAsmtID)=" & iAsmtID & "))"
End If
'response.Write(str)
set rs = nothing
conn.execute(str)
str = ""
Hello.
Is it possible to somehow update data as it is copied from source to destination using SSIS.
Currently I extract data from Database A, load it into database B, then clean/update the data in database B and then load it into database C using DTS on 2000.
What I would like to do is extract data from database A, clean/update it, then load it straight into database C without having to load it into database B first.
I am unable to clean/update the source data in database A, as this would be the obvious thing to do.
Thanks.
hello guyshere is my problem:i am developing a asp.net web app in .net 2.0. i have some sensitive data in my database. which is encrypted using DES ( with some key which is only known by the top level authorities ). now there is an option of changing the secret key. on changing the key the sensitive data has to decrypted using the old key and then again encrypted using the new key. Now if the no of records increases i am afraid that it might take a longer time and the application might look as it got hanged. guys i have no clue on how to do this. if you guys have any idea on how to implement this please let me know. any help would be appreciatedVignesh
View 7 Replies View Relatedhello all,
I ran into a problem using the "sql data source updating " method. i'm using a form view with about 20 parameters that are bind to controls in the form view and 5 other parameters that i'm setting the value to in the "sql data source updating" method. Every thing updates find execept for the last 5 parameters that i'm setting the values to in the "sql data source updating" method. My store procedure(sp) updates and insert mulitple tables. For some UNKOWN reason the tables that uses the parameteres in the "sql data source updating " are inserting multple records when only 1 record should be inserted. Have anyone have this problem?
I have a database which is used for the asp.net login control and i use the same database for my website work too. In this database there are asp.net created tables for login controls and the tables that i have created for the website. Now when i add a user to the website, data is added in the asp.net created tables (like aspnet_membership, aspnet_users). I want to add some of the data that is added to these tables into the tables that i have created. Is there a way i can do this?
View 4 Replies View RelatedHi,
I want to get (SELECT??) data from a db (SQL), edit the data (+1) and then update (UPDATE??) the table with the edited value. How do I get the value from the db and then edit it and then update the field in the db?? Does someone has an example so I get on the way??
Thanks,
Roel
Sorry if this is a dumb question but I am just learning ASP.NET. My database is on a server running SQL Server 2000 and I have used Dreamweaver to build sites. I am trying to convert to ASP.NET.
The problem I am having is that data is not updating when I change it in a GridView, DataList, etc. I have tried including the user name an password in the connection string without success. What did I miss?
connectionString="Data Source=<ServerName>;Initial Catalog=<DatabaseName>;Integrated Security=True;Uid=sa;Pwd=<Password>;"
This is the string I use in Dreamweaver which works;Driver={SQL Server};Server=<ServerName>;Database=<DatabaseName>;Uid=sa;Pwd=<Password>;
Hello, I am a relatively new user to SQL so forgive me if the solution is not very dificult :) I have a SQL database that contains a products table that includes part numbers with part descriptions. I need to change all the part descriptions (over 6000 records) so I exported the table into Excel. All the changes have been done in Excel but i am having problems trying to re-import the new data into the existing table. Any info would be appreciated. Thanks
View 4 Replies View RelatedI am relatively new to SQL and am having trouble with an update. I know I cannot use a join update and have to use subquery. I am not sure of the syntax on how to do this. Below is basically what I want to do, but cannot figure out how to do it with a subquery. Any help would be appreciated.
update revisedapps
set alternateapp = t.alternateapp
from tempalternateapps t
join revisedapps r on t.appname = r.softwarename
Hi,
Could someone please tell me how to update data in a column that contains unicode data in SQL 2000?
I know how to write update scripts on my own, but when it comes to Unicode character updates I canÂ?ft manage. In the script that I have pasted below I can update the customerÂ?fs details, but this only works if I update with Romaji characters (non-unicode).
The data that I need to update is in Japanese Kanji, but when I use the 'Update' script below, the data is updated as all question marks (???????????).
Is it very much harder to get the script to where it will handle Unicode characters and insert the correct data?
UPDATE CUSTOMER_LANGUAGE_DETAILS
SET FIRST_NAME = '‰h�s'
WHERE CUSTOMER_ID = 1592
Hey...newbie question: I've got three columns in my database, thethird of which is blank right now, and I need it to equal the value ofcolumn one minus column two. While I can accomplish this in the .aspxpage with a subroutine, I want to do it in SQL Server so I can simplyread the data in the page and not have to do any calculations. Helpplease? Thanks.Erik
View 5 Replies View RelatedFirst of all I don't normally use SQL, so please excuse my ignorance.1st QueryWe have a table with a column with data that looks like this:AMTL03256636What I need to do is change the AMTL portion of the column to another set ofcharachters i.e TESTCurrently i'm extracting the data, loading it into excel changing the dataand then bulk inserting it backinto the SQL7 table. Is there away to change the AMTL without the othersteps?Alan
View 3 Replies View Related