IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS
IF UPDATE(colA)
select "updating col A"
IF UPDATE(colB)
select "updating col B"
go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go
insert into TestTrigger
(colA)
values
(1)
go
insert into TestTrigger
(colA, colB)
values
(2,3)
go
I'm new to this forum. This 'problem' has occured many times, but I've always found a way around it. I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values. Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank? Thank you very much.
how can I write decode function in sql such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
HI there, Is there a function on transact SQL which test if a character belong to expression,I want to select from a table only the address that contain '@'.
I have the following code in a stored procedure which needs modification as a result of the change in the input format for display_cur_price_tx field(price range). ------------------------------------------------------------------------------ old format for display_cur_price_tx is: $69 - $99
------------------------------------------------------------------------------ New format for display_cur_price_tx is: $69-$99 9without the spaces inbetween).
What needs to be new code for sp?
If I still run the old code mentioned above now, I am getting the following error:
Server: Msg 8114, Level 16, State 5, Line 4 Error converting data type varchar to numeric.
Any help on how to modify the above code to suit the new format for price range?? Any help is appreciated. Thanks. Sheila.
Hi all, I have a search function in my website, which is fully functionable. But currently, this is the issue that I am having problem with. For example: I have only the name 'John' in my database. When the user search for 'John Tan', no results is shown as there was no such name in the database But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee" So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in. Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%')
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:
SELECT NewColumn= CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END, Table2.SelectCol2 FROM Table1 JOIN Table2 ON NewColumn = Table2.ColumnName GROUP BY NewColumn, Table2.SelectCol2 ORDER BY Table2.SelectCol2
DataSet updateSet = finalSet.GetChanges(DataRowState.Added); saveCenterCoaches.Update(updateSet.Tables[0]); } catch(Exception ex) { throw ex; } Iam getting "Procedure expects parameter @ConsultantName, which was not supplied." I have consultantname and other parameters built in my datatable. Is it the correct way of doing? Can someone help.It is urgent.
There are two table table A and table B , if something is updtaed in table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....
Here is the Update trigger i wrote :
CREATE TRIGGER [updbacklog] ON [testbacklog] FOR UPDATE AS Update test1backlog Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber , test1backlog.SalesOrderNumber = inserted.SalesOrderNumber , test1backlog.CustPONumber = inserted.CustPONumber , test1backlog.Status = inserted.Status , test1backlog.Comments = inserted.Comments , test1backlog.TargetCompletionDate = inserted.TargetCompletionDate , test1backlog.ActualCompletionDate = inserted.ActualCompletionDate , test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired , test1backlog.WorkOrderType = inserted.WorkOrderType , test1backlog.CustomerName = inserted.CustomerName , test1backlog.Attn = inserted.Attn , test1backlog.CustAddr1 = inserted.CustAddr1 , test1backlog.CustAddr2 = inserted.CustAddr2 , test1backlog.CustAddr3 = inserted.CustAddr3 , test1backlog.City = inserted.City , test1backlog.State = inserted.State , test1backlog.Postal = inserted.Postal , test1backlog.Customer = inserted.Customer , test1backlog.Address = inserted.Address , test1backlog.ShipDate = inserted.ShipDate , test1backlog.Carrier = inserted.Carrier , test1backlog.Waybill = inserted.Waybill , test1backlog.CanBeShipped = inserted.CanBeShipped , test1backlog.PlannerCode = inserted.PlannerCode , test1backlog.rowguid = inserted.rowguid from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber and test1backlog.CustPONumber = inserted.CustPONumber and test1backlog.Status = inserted.Status and test1backlog.Comments = inserted.Comments and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired and test1backlog.WorkOrderType = inserted.WorkOrderType and test1backlog.CustomerName = inserted.CustomerName and test1backlog.Attn = inserted.Attn and test1backlog.CustAddr1 = inserted.CustAddr1 and test1backlog.CustAddr2 = inserted.CustAddr2 and test1backlog.CustAddr3 = inserted.CustAddr3 and test1backlog.City = inserted.City and test1backlog.State = inserted.State and test1backlog.Postal = inserted.Postal and test1backlog.Customer = inserted.Customer and test1backlog.Address = inserted.Address and test1backlog.ShipDate = inserted.ShipDate and test1backlog.Carrier = inserted.Carrier and test1backlog.Waybill = inserted.Waybill and test1backlog.CanBeShipped = inserted.CanBeShipped and test1backlog.PlannerCode = inserted.PlannerCode and test1backlog.rowguid = inserted.rowguid
Hi Guys, I have a table created like (cname,perfweek,orderstaken,callsmade). the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade. somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week). And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week. cname perfweek orderstaken callsmade ----- ------- --------- -------- abc 1 3 4 bbb 1 5 6 abc 3 3 1 bbb 3 2 3 bbb 2 4 5
in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like : cname perfweek orderstaken callsmade ---- ------- ---------- ---------- abc 1 8 10 bbb 1 5 6 abc 3 5 4 bbb 3 2 3 bbb 2 4 5 (note: after combine, I have to delete the bbb for the perfweek of 1 and 3) and then I have to update the rest of the wrong cname to the correct cname. Then the firnal table looks like
Hi Folks, Somehow i am stuck at a very basic step. I have two pages - 1. DomainList.aspx which just displays all the records from the Domains table.2. DomainAddEdit.aspx which displays the selected record in FormView(Edit Mode) with two link for Update and Cancel. The Update link in the FormView does nothing on the first click. It just reloads the page with the new data I entered. If I click again on the Update link, it throws me an error: "Cannot insert the value NULL into column 'DNS', table 'MSInteractive.dbo.Domains'; column does not allow nulls. UPDATE fails.The statement has been terminated. " I have no clue why all this is happening. I have spent more than two days on this and this is very very frustrating. Just to mention, I haven't written any code for this. Its developed all using the VWD tools available. I have posted this message earlier but haven't got any response. I am sure most of you guys must have been doing these steps everyday. So, please post your thoughts. Thanks a million. Here is the relevant code for my DomainADDEdit.aspx: <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DefaultMode="Edit"> <EditItemTemplate> Id: <asp:TextBox ID="IdTextBox" runat="server" Text='<%# Bind("Id") %>'></asp:TextBox><br /> RegistrarAccountId: <asp:TextBox ID="RegistrarAccountIdTextBox" runat="server" Text='<%# Bind("RegistrarAccountId") %>'></asp:TextBox><br /> Registrar: <asp:TextBox ID="RegistrarTextBox" runat="server" Text='<%# Bind("Registrar") %>'></asp:TextBox><br /> DNS: <asp:TextBox ID="DNSTextBox" runat="server" AutoPostBack="True" OnTextChanged="DNSTextBox_TextChanged" Text='<%# Bind("DNS") %>'></asp:TextBox><br /> EmailHost: <asp:TextBox ID="EmailHostTextBox" runat="server" Text='<%# Bind("EmailHost") %>'></asp:TextBox><br /> Registered: <asp:TextBox ID="RegisteredTextBox" runat="server" Text='<%# Bind("Registered") %>'></asp:TextBox><br /> Expires: <asp:TextBox ID="ExpiresTextBox" runat="server" Text='<%# Bind("Expires") %>'></asp:TextBox><br /> MsiResponsible: <asp:CheckBox ID="MsiResponsibleCheckBox" runat="server" Checked='<%# Bind("MsiResponsible") %>' /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSInteractiveConnectionString %>" SelectCommand="SELECT [Id], [RegistrarAccountId], [Registrar], [DNS], [EmailHost], [Registered], [Expires], [MsiResponsible] FROM [Domains] WHERE ([Id] = @Id)" UpdateCommand="UPDATE Domains SET DNS = @txtDNS WHERE (Id = @Id)"> <UpdateParameters> <asp:FormParameter FormField="DNSTextBox" Name="txtDNS" /> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </UpdateParameters> <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" Type="String" /> </SelectParameters> </asp:SqlDataSource><br /> <asp:LinkButton ID="UpdateButton" runat="server" CommandName="Update" Text="Update" OnClick="UpdateButton_Click"></asp:LinkButton> <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton> </EditItemTemplate>
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
Hello all. I have 2 tables members1 and members2. members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.
There is a strange problem on my tables . When i execute a simple query from Query Analyzer, say select * from ' table name' , then i can edit or enter text in my result in one of the columns.
But the same table if i try to open through the Enterprise problem , i.e selecting the table , right click and select open table and then if i select return all rows , it does not allow me to enter text in a specific coloumn .
But the same is possible if i execute a query and try to enter text in the results which is displayed in grid from the Query Analyser .
Could anybody help me to know what could be the problem and how can i enter a text it ?
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID) select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}', '{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID From members Inner Join groupLeaders ON members.memberID = groupLeaders.memberID SELECT @@Identity AS UserID
How can I modify the portion that is inserting the '56' at the end of each username to do the following:
1) check to see if username already exists in the database (using a query with "LIKE %'")
2) if not, create the username "as-is" or how it should be without the number
3) if already exists, get a count of records matching your search criteria .... now make a new username + + (count + 1).ToString();
Any thoughts... I am struggling to put these two pieces together.
Thanks,
Zoop
[EDIT - original post below this]
I have modified my method to make this a bit easier. I added a memberID field to my [Users] table so that I can update my [Members] table in a difference statement after the insert takes place.
I have the following query, and it completes succesfully in query analyzer (though I haven't actually executed the SP, just testing the syntax...) anyway, here is what I have:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID) select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}', '{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID From members Inner Join groupLeaders ON members.memberID = groupLeaders.memberID SELECT @@Identity AS UserID
I am hoping this will create a user for all members whose 'memberID' can be found in the groupLeaders table... is this correct?
Also, notice the 56 being appended to the end of each username. I would like this to be a random number generated within a given range... can this be done? any advice?
Thanks,
Zoop
[Original post below - provide more background]
I have three tables involved with this insert/update:
I want to insert into the [Users] table the memberFirstName.memberLastName + randomNum into the 'UserName' column from the [Members] table. Also, I want to make all passwords the same, in this case I know the Salt, Hash1, Hash2 I will be using and would like to pass these in for the 'UserHash1' 'UserHash2' fields.
Now, I only want to make this insert where the memberID is in the GroupLeaders table. and Finally, I need to Update my Members table with a UserID where the memberID matches the one used from the groupLeaders table.
Does anyone have any ideas on how I can accomplish this, even if it requires adding a temporary field to one of my tables... here is what I have so far, but am recieving errors and can't quite figure this one out. (btw - I also don't know how to gen the rand num and was using the literal 23 as a placeholder.) Thanks...
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2) select a.memberFirstName + '.' + a.memberLastName + '23' + as userName, '{AA99FCDE-6E06-437D-B9E9-3E3D27955C3E}', '{7xxxxxx2-4xx6-9xx1-7xx9-4x3xx4Axxx59}', '{0xx8xxE-Cxx4-6xxx-xxxx-Fxx3xxxx3xxF}', b.memberID as newMemID From members a, groupLeaders b Where a.memberID = b.memberID SELECT @@Identity AS UserID
Update Members Set UserID = Ident_Current('Users') where memberID = newMemID
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float) RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float) AS BEGIN IF (@IncludeParent=1) BEGIN INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID END DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float DECLARE RetrieveReports CURSOR STATIC LOCAL FOR SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID OPEN RetrieveReports FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID) INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID) FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID END CLOSE RetrieveReports DEALLOCATE RetrieveReports
FROM Statements..ParticipantFundBalances pfb JOIN ( Select cp.PlanId, p.ParticipantId, @PeriodId Period, CASE WHEN a.FUND_ID = 'LOAN' Then 0 ELSEf.FundId END FundId, a.ACT_ID1, a.TOT_ACT1, a.ACT_ID2, a.TOT_ACT2, a.ACT_ID3, a.TOT_ACT3, a.ACT_ID4, a.TOT_ACT4, a.ACT_ID5, a.TOT_ACT5, a.ACT_ID6, a.TOT_ACT6, a.ACT_ID7, a.TOT_ACT7, a.ACT_ID8, a.TOT_ACT8, a.ACT_ID9, a.TOT_ACT9, a.ACT_ID10, a.TOT_ACT10, a.ACT_ID11, a.TOT_ACT11, a.ACT_ID12, a.TOT_ACT12, a.ACT_ID13, a.TOT_ACT13, a.ACT_ID14, a.TOT_ACT14, a.ACT_ID15, a.TOT_ACT15, a.ACT_ID16, a.TOT_ACT16, a.ACT_ID17, a.TOT_ACT17, a.ACT_ID18, a.TOT_ACT18, /*a.ACT_ID19, a.TOT_ACT19, a.ACT_ID20, a.TOT_ACT20, */ a.UNIT_OP, a.PRICE_OP, a.UNIT_CL, a.PRICE_CL, Cast(Rtrim(i.ALLOC_PER1) as decimal) as ALLOC_PER1 FROM ASDBF a -- Derive the unique PlanId from the Statements ClientPlan table INNER JOIN Statements..ClientPlan cp ON a.PLAN_NUM = cp.ClientPlanId AND cp.ClientId = @ClientId -- Derive the unique ParticipantId from the Statements Participant table INNER JOIN Statements..Participant p ON a.PART_ID = p.PartId--Derive the unique FundID from the Statements Fund Table...Left Outer JOIN Statements..Fund f ONa.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker OR a.FUND_ID = f.ClientFundId -- get the allocation percent from the INVSRC LEFT Outer JOIN INVSRC i ONa.FUND_ID = i.INV_ID AND a.PLAN_NUM = i.Plan_Number AND a.PART_ID = i.PART_ID
WHERE a.Import = 1 )a ON pfb.PlanId = a.PlanId AND pfb.ParticipantId = a.ParticipantId AND pfb.PeriodId = PeriodId AND pfb.FundId = a.FundId
While i insert data in my table i am checking if there are any loans in the ASDBF table and if there i am inserting a 0 in the particular i am trying to up date the with in 3 different plans in the same table..
Take a table, where not all the columns are populated:CREATE TABLE #T (A int, B int, C int, D int)INSERT #T (A,B) VALUES (1,2)INSERT #T (A,B) VALUES (3,4)INSERT #T (A,B) VALUES (5,6)INSERT #T (A,B) VALUES (7,8)INSERT #T (A,B) VALUES (9,10)The values for C and D can be computed as functions of A and B. For thisexample, let's say they are twice A and three times B, respectively:CREATE FUNCTION dbo.F(@A int,@B int)RETURNS @Tbl TABLE (X int, Y int)AS BEGININSERT @Tbl (X,Y) VALUES (@A*2, @B*3)RETURNENDNow we use the function to compute the other columns:UPDATE #T SET C=X, D=YFROM dbo.F(A,B)Right? Well, no. Instead, I get this message:Server: Msg 155, Level 15, State 1, Line 2'A' is not a recognized OPTIMIZER LOCK HINTS option.Any suggestions? I would like to use this structure, if possible.Jim GeissmanCountrywide Home Loans
I have been trying to get a function to UPDATE a row in the database. I know that this is not supported but by using code from a previous post this can be accomplished [See my code below].
I am really stuck here, I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db, 1- Stages 2- Activities 3- Tasks 4- Subtasks Structure is like: Stages 1- SrNo (Unique) 2- Stage 3- StartDate 4- FinishDate
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages. I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE) {int vendorid = Convert.ToInt32(VEE.VendorevalVendor); int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter(); SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int; pmvendorid.Value = vendorid; pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int; pmuserid.Value = userid; cmd.Parameters.Add(pmvendorid); cmd.Parameters.Add(pmuserid); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.
Below there is a piece of code that demostrate the false documentation assertion in the case of a trigger fired by an INSERT operation, infact even if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.
Now the SP1 don't solve this BUG, even if i had read some month ago (before that SP1 was out) that in SP1 this will be fixed!
Anyone Can Help me to solve this problem, indicating me where i can post the problem to sensitize the Microsoft's Service Pack Factory ?
Thank in Advance
PS:Sorry for my Bad English .... correct me please if some part are not readable!
------[Cut Here]-------------------
CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER ) GO
CREATE TRIGGER TriggerIU_Test ON TEST FOR INSERT, UPDATE AS
IF UPDATE(FIELD1) PRINT 'Field 1 Touched'
IF UPDATE(FIELD2) PRINT 'Field 2 Touched' GO
print 'InsertIng ' INSERT INTO TEST (FIELD1) VALUES (2)
print 'Updating ' UPDATE TEST set FIELD1 = 1
GO DROP TABLE TEST ------[Cut Here]-------------------
I have an application which uses DTS to import data. After the data is imported the transaction logs are no good since the import wasn't logging. Is there a way to turn logging on?
I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.
update abtimesummary set hours = sum(a.hours) from abtimestore a join abtimesummary b on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid
and this is the error message:
Server: Msg 157, Level 15, State 1, Line 2 An aggregate may not appear in the set list of an UPDATE statement.
Table test2 has multiple amounts for each account, I would like to sumthe amounts for the same account and use the result to update thevariable 'tot_amount' in table test1. But SQL does not allow me to usesum function in update. Is there any other way to do this? Thanks.update test1set tot_amount=sum(b.amount)from test1 as ajoin test2 as bon a.acc_no=b.acc_no
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?