How Can I Update Is_event_logged Column In Sys.messages???
Oct 9, 2007
sp_altermessage can be run to alter a user-defined message, but I would like to alter a system-defined message to enable alerting. For example, error 262 "...permission denied...".
Select * From sys.messages has the following results for message 262.
message_id language_id severity is_event_logged text
262 1033 16 0 %ls permission denied in database '%.*ls'.
From what I've read this does not appear to be possible, but I find it hard to believe Microsoft would remove this functionality for 2005. It's too valuable. Is it possible to update the base table referenced by sys.messages?
Thanks, Dave
View 3 Replies
ADVERTISEMENT
Nov 11, 2007
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
View 2 Replies
View Related
Oct 23, 2007
I want to raise the custom success message after insert and update something like say 5 records updated successfully or inserted successfully?Is there a way i can do that in stored procedure?Also,can someone enlighten me on 'ReturnValue' part of [ ParameterDirection.ReturnValue;]here.
I am coding in c# ,(sql server 2005)and my insert and update codes are in DAL,which gets called from BLL like this. public int updateRecord(int data1, string text1, string text2, bool ind, string sproc,Label lblErr) {
using (SqlConnection fbConnect = new SqlConnection(appProperty.dbconnect))
{
SqlCommand fbCmd = new SqlCommand(sproc, fbConnect);
fbCmd.CommandType = CommandType.StoredProcedure;
..... parameter stuff......
SqlParameter val = fbCmd.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int));
val.Direction = ParameterDirection.ReturnValue;
try{
fbConnect.Open();
fbCmd.ExecuteNonQuery();}
catch(Exception ex){lblErr.Text = ex.Message}
int iSuccess = (int)fbCmd.Parameters["@ReturnValue"].Value;//I am NOT using this effectively.How can i make use of it?
return iSuccess;
}
}
View 3 Replies
View Related
Apr 2, 2008
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
View 2 Replies
View Related
Sep 9, 2015
I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
View 4 Replies
View Related
Aug 27, 2015
How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies
View Related
Jun 20, 2000
Hello,
I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format:
5/3/00
I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.
The following syntax did not work:
SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)
Any suggestions please :)
Thanks,
Denise
View 4 Replies
View Related
Jan 29, 2008
Hi.
I'm writing a web application with VS2005 andSQL Server 2005 express edition.
I have an SQL table:
Table name:
statistics
Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
Any help would be appreciated.
View 2 Replies
View Related
Mar 12, 2007
Hello,
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
View 6 Replies
View Related
Jun 14, 2007
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies
View Related
Mar 18, 2004
Hi,
i need to update the value in the column by adding to the value already in that column.
For eg, the value is in the column is already 5, i need to update it with addition of 8. i.e 5+8 = 13. The final value should be 13 in the column after that.
How do I go about doing it?
UPDATE Table 1 SET ColumnName +=8 ?
Thanks in advance. really at a loss and very urgent.
View 2 Replies
View Related
Jun 15, 2008
Hi,
I'm quite new to SQL and wondered if someone could help. I'm pretty good at writing reports but I now need to do an update.
Basically i need to update a column in table1 where coulmn 1 in table4 = Y, however to get to table4 I think I need to link to all 4 tables, table4 is the last table. The tables link based on ID's
Tried a few things such as
UPDATE table1
SET column1 = ''
WHERE column1 IN ( SELECT column1
FROM table4
WHERE column1 = Y)
Now I need to get to table 4 I need some other wHERE clauses to link them together, just like I would if I were writing a SELECT statement.
Any ideas?
Many thanks
View 3 Replies
View Related
Jan 23, 2007
I have a table with a column called data. In that column is a value like:
<settings><myVarOne>valueOne</myVarOne><myVarTwo>valueTwo</myVarTwo></settings>
All I'd like to do, is update all the myVarOne values. So the new value for data would be:
<settings><myVarOne>newValueHere</myVarOne><myVarTwo>valueTwo</myVarTwo></settings>
This will likely be SQL2000 not SQL2005 but it would be useful to know for both.
I've looked at OPENXML but all the examples seem keen on using sp_xml_preparedocument and then OPENXML needs the @idoc so I'm thinking there is something else.
If someone can point me in the right direction that would be extremely helpful as I haven't found anything that makes sense to me. UpdateGrams seems very overblown for manipulation like this when OPENXML is sooo very close to being correct.
Thanks!
Chad
View 1 Replies
View Related
Apr 16, 2007
Hi,
i have a column which has values like,
01234
00445
00067
....
i want to update those column like,
1234
445
67
how can i do it?
View 3 Replies
View Related
Sep 25, 2007
Is it possible to update from one table to another?Pls examine my code here:
UPDATE tStaffDir SET tStaffDir.ft_prevemp = ISNULL(tStaffDir_PrevEmp.PrevEmp01, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp02, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp03, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp04, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp05, ' ') Where tStaffDir_PrevEmp.ID=tStaffDir.ID
I am trying to concatenate the columns from tStaffDir_PrevEmp to tStaffDir but I have this error where tStaffDir_PrevEmp is recognised as a column and not a table.
Pls advise how this can be done. Many Thanks.
View 3 Replies
View Related
Jan 12, 2006
The problem is with @NUMERY parameterin code behind i setDim dr As GridViewRowDim numery As Stringnumery = ""
For Each dr In GridView1.RowsDim numeros As Label = dr.Cells(0).Controls(1)numery += numeros.Text & ", "Next
numery = numery.TrimEnd(", ")
SqlDataSource1.UpdateParameters("NUMERY").DefaultValue = numery'so numery will look like this 123, 65465, 54616, 56465Update command looks like this :UpdateCommand="UPDATE slon SET mrowka = @MROWKA WHERE (NUMER IN (@NUMERY))"
<UpdateParameters><asp:Parameter Name="MROWKA" /><asp:Parameter Name="NUMERY" /></UpdateParameters>
And because of @numery i have err: Error converting data type nvarchar to numeric. how should i post "123, 65465, 54616, 56465" as parameter for this query ?
View 2 Replies
View Related
Apr 6, 2001
Hi,
I have large table rrsn_security_t - more than half a million rows. I do a complete update on all the rows of the table using the following query.
UPDATE rrsn_security_t
set
cusip = b.fmr_cusip,
master_issuer_num = b.mstr_isr_cusip,
ticker = b.fmr_symb,
description = b.fmr_name,
prim_exchange_code = c.exchange_key,
shares_otstndng = d.amount,
iv_type = b.iv_typ,
active = case when b.deact_date is null then 'Y' else 'N' end
FROM rrsn_security_t a INNER JOIN
ref_security_t b ON
a.security_id = b.fmr_cusip LEFT OUTER JOIN
shares_outstanding_feed_t d ON
b.fmr_cusip = substring(d.fmr_cusip,1,9) AND
d.fmr_type = 'OUTS' LEFT OUTER JOIN
rrs_exchange_t c ON
b.dft_exch_cd = c.exchange_id
where b.fmr_cusip not in (select security_id from rrsn_scrty_ovrrd_in_effect_t)
This is a part of a daily batch load and a DTS package. The table has one clustered index and three non clustered indexes. Two of them are covering indexes. The update is on all the columns that have the non clustered indexes.
The problem is that when I run the update the transaction log runs to more than a GB of memory and alomost takes an hour to do so. Without the index it takes around 300 MB and 7 mins.
I am not confortable with the idea of dropping and recreating the index since it is not necessary in SQL Server 7.0, though it was the case in the previous versions of SQL Server.
Also the query plan with the indexes on shows that Table spool/Eager spool to optimize rewinds takes 50% of the query cost.
Could any one help me with how I should deal with this situation.
Thanks in advance
GV
View 3 Replies
View Related
Apr 20, 2005
I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.
View 6 Replies
View Related
Jan 30, 2007
I have a table wherein I have to update a particular column (receipt code) based on another column of the same table (receipt number). i have to do calculations in order to generate the correct receipt code, and i have to do this on every row of the table. how can i do this? will this update be in a loop or something?
View 6 Replies
View Related
Sep 18, 2004
Newbie here.
How would I do the following update?
I have over 100,000 rows and want to knock the first two characters off one of the columns in each row.
i.e. currently have
pcdocsvariousvarious
and want this to read
docsvariousvarious
I just need to remove the first two characters "pc".
View 6 Replies
View Related
Oct 22, 2007
I have a table called Projects, within that table is a field called UserId
I want to log everytime that UserID field in changed in a log called ProjectAllocationLog
so far i have written
CREATE TRIGGER LogUser
ON Projects
FOR Insert, Update
AS
if Update(UserId)
begin
INSERT INTO "ProjectAllocationLog" ("projectid", "UserID", "createdate")
VALUES ("123", "123", Now())
End
What I am stuck with is how do I pass the values of the field into the Values
ie where I have put "123", I actually want to pass the value of ProjectID, and UserID from the projects row that is changing
can anyone help me please
View 5 Replies
View Related
Oct 25, 2007
hi guys i have a problem;;;
i have to update table'''
querey is--TABLE1 HAS A COLUMN CALLED CITY .THE RECORDS WITH CITY='DELHI' ARE IS TO BE UPDATED WITH CITY='MUMBAI' AND THE RECORDS WITH CITY='MUMBAI' ARE IS TO BE UPDATED WITH CITY='DELHI' ...
i HAVE TO WRITE A SINGLE SQLSTATEMENT TO DO THIS NOT WITH THE HELP OF CURSOR
View 14 Replies
View Related
Apr 2, 2008
I have a temp tbl and in col1 row1 i would like to have 1, col1 row2 value 2, col1 row3 value 3 and so on. how can i do this?
View 3 Replies
View Related
May 13, 2014
I need to write a update statement which will update the same table with value from the same column but the values will be changing.
For an example.
The source table is as below.
ROWID DATA Header
1 AAA H1
2 isa1
3 isb2
4 AAA3b3 H2
5 same
Now the records with rowid 2 and 3 should be updated with H1 as Header which is present in the Rowid 1.
Similarly the record rowid 5 should be updated with H2 as header from rowid 4.
View 5 Replies
View Related
Oct 24, 2014
With cte_table (columna)
As (select a from cust)
Update columna
Set a = 'hello'
Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?
View 2 Replies
View Related
Apr 24, 2008
Hi I have added a new column to my table, i need to run a query and update this colmn with a count, so like
i = 1
loop
update table
set column = i
where column = 113
i=i+1
loop
just not sure what the sql syntax is?
View 20 Replies
View Related
Sep 9, 2013
I have table
id, series, seriesnr
and data
65557,AS,0
74443,AS,0
how to update table so seriesnr would be incremental , like
65557,AS,1
74443,AS,2
if i use
update x set seriesnr=select max(seriesnr) from x
then i got same seriesnr for all rows like max was calculated only one time on the begining not on each row update(why is that itseem so illogical)
65557,AS,1
74443,AS,1
View 1 Replies
View Related
Jan 2, 2014
I am trying to remove a user from Reporting Services via code. I have found that the way to do this is to remove their details from an XML field in the table SecData
The name of the column is xml_description
An example value of the column is below:
<Policy>
<GroupUserName>BUILTINAdministrators</GroupUserName>
<GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId>
<Roles>
<Role>
<Name>Content Manager</Name>
[Code]....
I would like to remove the bolded part in the column when I put in the user's name.
So the code would start with:
Declare @username varchar (20)
Set @ username = 'EMEAMJohnson'
Followed by the logic to remove the bolded part of the column.
The column is called xml_description and the name of the table is SecData.
View 17 Replies
View Related
Oct 21, 2005
I'm running into problems with ambiguous column names. TransID also exists in tblWork. I tried adding SHPD + WORK, but it won't accept the SHPD on the UPDATE line. How do I code around this error? Thanks.
UPDATE tblShipmentDet SHPD
SET
SHPD.Quantity = WORK.Quantity
FROM tblWork WORK
WHERE TransID = WORK.RecordID
View 9 Replies
View Related
May 21, 2006
Hello all,
I'd like to create a view which shows all specific column values
(null values for example)
as calculated values from another column,
if for example i have the table:
---------------
| col1 | col2 |
|------|------|
| 123 | null |
| 126 | 9 |
---------------
i would like the view to be:
---------------
| col1 | col2 |
|------|------|
| 123 | 6 | ----> 6, since it's the sum of col1 digits 1+2+3
| 126 | 9 |
---------------
I don't really know how to do that, although i'm quite sure it's possible, any help will be appreciated,
Best Regards,
pitt
View 3 Replies
View Related
Apr 26, 2007
Hello all, was wondering if you could point me in the right direction for this.
I have a db with a column classifications, and in that column are 'Accountants - (1234)' 'Book-keepers - (18) etc etc. Now what I want to do is remove the - (xxxx) section and obviously the white space, so I am just left with 'Accountants' 'Book-keepers' etc.
1. Is there an easy way to do this?
Ok so my thoughts were create a another table and put the ammended classifications in that to update the first table. Your probably asking why not just do it on the first table...Answer. There are over 150,000 records to change.
So I now have my first table with column classification and my second table with the correct classification ammendments.
I can sit down and manually type
UPDATE table1
SET classifications = 'Accountants'
Where classifications = 'Accounts - (xxxx)'
until i have completed the entire list in table 2 but I was hoping you good people would know a way to work through the list so it would automatically update each classification correctly.
The problem I have here is I dont know how to work through table 2 and match it to something in table 1 and update it.
Any help is greatfully appreciated
View 16 Replies
View Related
Jan 13, 2008
I have a table (table1) which I need to update with values based on another table (table2).
------------------------------------------------
table2 looks like this:
id, columnName, value_true, value_false.
value_true and value_false can contain values like NULL, and strings.
I need to write a dynamic store procedure that updates all columns in table1 that exists in table2 with the values in table2.value_true or table2.value_false if the column in table1 is either true or false.
View 2 Replies
View Related
Mar 21, 2008
hi, i have column salary in my table emp.
my column values are as follows:
salary:
20000
30000
40000
50000
so i need to update this column values in a single update statement.
so my values as follows:
salary:
10000
10000
10000
10000
so please give me example query to update this please
View 4 Replies
View Related
