Hi,
I have a table with col_noteText defined as 'Text' datatype column.
I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.
Does anyone know how to do this for 'Text' datatype column.
Thanks in advance.
jfk
how can i update a column with datatype of text with a combination of columns having a datatype of float? do i convert the float columns to varchar/char/?? and/or can i convert the column i am updating?
thanks!
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies View RelatedHi, I am kinda new to this so here it goes:
table name: USERS
field names : LName, FName, EmpID
other table name:PERSONNEL
field names : (same as USERS)
The EmpID column in my USERS table is blank (I have 30 records in the table.)
I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data
how would I do this or what would the code be?
sanctos
Hi,
I have dataset which has max(column) and between_sale_dates columns So I would like to
update those columns. CustomerNo and Sales_date are important . This group by for these 2
colums. I can write a sproc but How can I do with SSIS?
thanks
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I have a integer column which has Default value alredy defined. How do i alter this column so that it's defult value will be removed. I want to do this using T-SQL statement. Not through design mode.
I have inherited a database that has a column defined as bit. Looking at BOL it shows the valid values are 0,1, null. However there are several rows of data which have -1. Is BOL wrong? we think a VB program runs an update which changes this.
View 1 Replies View RelatedI am looking for a query which will populate one of my columns that contains some blanks with a defined value based on a value in another column
For example in the below table I would like the blanks in serial number to be populated with "SN09"
Product_NameSerial_Number
PRODUCT1
PRODUCT1
PRODUCT2SN10
PRODUCT3SN11
PRODUCT3SN11
PRODUCT1
So the query I am looking for would basically add some values to the results where they meet the right criteria.
I am running the query through a view so I dont actually want to add the values to the physical tables only to the view of the results if you understand what I mean?
I have a user defined function (UDF) that takes 2 parameters. These parameters will be mapped from OLE DB source column values. I want the function result to be a new column that will be mapped to an OLE DB destination. Should this be done using a lookup task, OLE DB command task, or...? If this is possible I would appreciate a very specific example of how it's done.
BTW I tried using a lookup task with a SQL command before the UDF idea occurred to me and ran into problems using parameters in the SQL query:
Parameter Information cannot be derived from SQL statments. Set parameter information before preparing command.
I've seen the posts about going to the advanced tab and messing with the parameters there but I was hoping using a UDF would be easier, if it can be done.
Thanks.
I have a integer column which has Default value alredy defined. How do i alter this column so that it's defult value will be removed. I want to do this using T-SQL statement. Not through design mode.
I have my table like this
Code Amount Debit Credit
D 230
C 440
C 890
D 560
I want to update amount in Debit if Code is D
Else i want to update it into Credit
How can i establish this
Please Help
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
View 6 Replies View RelatedHello all,
I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'
If anyone knows a solution, please kindly let me know.
Thank you!
Here is the sample code:
ALTER PROCEDURE [dbo].[sp_premium_register]
@PolicyType AS VARCHAR(10),
@ReportFrom AS DATETIME,
@ReportTo AS DATETIME,
@Business AS VARCHAR(1),
@Term AS VARCHAR(1)
SELECT
ColumnA,
ColumnB,
SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END
FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType
WHERE
SLA = @Term
Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate
I am trying to key a Personnel Table on a person's initials. Obviously there may be more than one person with the same initials so I am trying to use a User Defined Function to see how many of a certain set of initials are already there, and add 1 and add the number to the Key to make it unique - DW1, DW2, DW3 etc.
But I cannot get the Computed Column Specification to accept a user Defined Function.
any thoughts?
help will be appreciated
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
View 7 Replies View RelatedI want to pass user-defined column names to views or procedures
I am using SQL Server 2005 Express.
I tried this.
CREATE PROCEDURE @userDefinedColumn varChar(10) = 'My_Column'
select @userDefinedColumn, count(ID) as [Total Records] from My_Table
GROUP BY @userDefinedColumn
This was not accepted. It won't allow a user defined parameter in the group by.
But I need to pass user defined parameters to views as well.
My dataset looks like this:
IDBuyDotComPriceCompanyIDShadowOf
AB CIRCLE PRO199.99203
AB CIRCLE PRO-b2199.99203AB CIRCLE PRO
AB CIRCLE PRO-TB249.99344AB CIRCLE PRO
AB CIRCLE PRO-TB-S10344AB CIRCLE PRO
I need to update the price of an item where the CompanyID is 344 and the ShadowOf is not null. The value in ShadowOf is the same as the ID that I want to get the BuyDotComPrice for. It should be simple, but I keep getting errors.
I use Microsoft SQL 2008
Here's a tricky SQL question that has definitely driven me to the end ofmy rope. I'm using Oracle 9i and I need to perform some simplemultiplication on a field and then display it with a percent sign usingthe COLUMN command. Here's the code thus far:COLUMN price format 9,999.99 HEADING 'Charged%'SELECT pricecharged * .231 as priceFROM VT_examdetailThe output from this reads:Charged%---------23.1034.6534.65....The kicker here is that I need to add a percent sign to the right of theoutput, so that it reads:Charged%---------23.10%34.65%34.65%....I thought I could do this by just adding "|| ('%')" into the SELECTstatement, but when I do this the decimal position defined in the COLUMNcommand is lost. Does anyone know another way around this?Thanks,Alex
View 3 Replies View RelatedI am trying to update a users status from Pending to either Approved or Rejected. I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{ protected void ApproveButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Approved"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); } protected void DenyButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Rejected"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }}
View 4 Replies View RelatedHi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code: protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = "HiddenConnection"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; Label productIDLabel = (Label)DataList1.FindControl("productIDLabel"); command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.Add("@productID", productIDLabel.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon
View 1 Replies View RelatedHow can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated.
Here is my table:
Code:
create table #add_diags(
add_diag_1 varchar(10) null,
add_diag_2 varchar(10) null,
add_diag_3 varchar(10) null,
add_diag_4 varchar(10) null,
add_diag_5 varchar(10) null,
add_diag_6 varchar(10) null,
add_diag_7 varchar(10) null,
add_diag_8 varchar(10) null
)
In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on.
Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved):
Code:
update ad
set add_diag_1=dsm_code
from #add_diags ad
join Doc_Entity de
on ad.patient_id=de.patient_id
and ad.episode_id=de.episode_id
and doc_code = 'DCDIAG'
join Patient_Assignment pa
on de.patient_assignment_id = pa.patient_assignment_id
and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
left outer join Doc_Diag_Axis_I_III dx1
on de.doc_session_no=dx1.doc_session_no
and de.current_version_no=dx1.version_no
where dx1.sequence_no=2
and de.is_locked = 'Y'
and dx1.rule_out = 'N'
and is_billable = 'Y'
and dx1.axis_type IN ('1','2')
and de.status in ('CO' , 'SA')
Any ideas? Thanks!
Hello i have this query and I havent been able to solve it
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguous.
I have tried with dbo and withou dbo, specifyng columns inside it,
Thanks
select a.nmempresa,a.nmtipocompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,
b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia,
g.DSOPCIONESCALA, g.PESO
from
tgsc_tiposcompetencias a, TGSC_TIPOSCOMPETENCIASxcargo b, tgsc_cargos c, tgsc_ciclos d,
tgsc_competenciasxcargo e, tgsc_competencias f,
(select
dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g
where a.nmempresa=72 and a.nmciclo=9
and b.nmtipocompetencia=a.nmtipocompetencia
and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9
and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9
and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72
and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia
and f.nmtipocompetencia=b.nmtipocompetencia
Hi all,
In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table:
dbo.LabTests Column_name Type Length Prec Scale
AnalyteID int 4 10 0 (Primary Key)
AnalyteName nvarch 510
CasNumber nvarch 510
Result numeric 5 8 2
Unit nvarch 510
SampleID int 4 10 0 (Foreign Key)
AnalyteID AnalyteName CasNumber Result Unit SampleID
1 Acetone 123-456-9 134.0 ug/L 1
2 Bezene 666-12-8 2.0 ug/L 1
3 Cobalt 421-008-7 10.0 ug/L 1
4 Acetone 123-456-9 201.0 ug/Kg 2
5 Bezene 666-12-8 1.0 ug/Kg 2
6 Cobalt 421-008-7 22.0 ug/Kg 2
7 Acetone 123-456-9 357.0 ug/L 3
8 Bezene 666-12-8 9.0 ug/L 3
9 Cobalt 421-008-7 56.0 ug/L 3
When I ran the following T-SQL code:
USE ChemAveRpd
GO
SELECT *
FROM dbo.LabTests as LabResults
Where AnalyteName = Acetone
GO
I got the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Acetone'.
Please help and tell me what right syntax I should write for [Where AnalyteName = Acetone] to generate a listing of LabResults for Acetone.
Thanks in advance,
Scott Chang
Please excuse my ignorance. I've researched this and it appears I am asking to do something that is ridiculous, so please let me know what is wrong with my design (or my brain). I'd like to update an ID number in a table. It is an identity column. In my solution I'm adding a lot of new entries into my table and deleting old ones. Call me anal, but It's driving me nuts that my ID numbers are growing so large so quickly and that I have so many unused ID's. If you were to look over my data ID's they would be something like 1,3,45,78,88,89,103,140,219. What I'm trying to do is renumber my data so that my data currently at say ID# 1067 can be moved to the unused ID#2, etc. So I either need a way to update an Identity column...or I need a way find the lowest unused number among a list of ID's. So is there anyway to achieve what I am trying to do? I see this guy had my same OCD issue (without a solution) :-(
View 3 Replies View RelatedDo anyone knows if there is a way that I can manually update the value in an IDENTITY column?
Thanks
hi i want to update column with new value in a table is it possible to do so in stored procedure . the name of the column will be an input to stored procedure ie at the time of writing the stored procedure i dont know which column the user will be updating
View 2 Replies View RelatedDoes anyone know of the SQL statement to add additional comuns to an existing table. I know i can do it through enterprise managaer, but I want to see if I can do it from query analyzer or some script to add columns without having to recreate the table. Or a way to save off the data and recreate the table then placing data back in? I hope that makes sense. I am thinking of this from the sens of doing all updates through source safe
Thanks!
If amc.amc2>0 then
“UPDATE amc set AMC2= AMC2 + “ & val(txtamt.text) & “where am1=1”
else if amc.amc2<0 then
“UPDATE amc set AMC2= “ & val(txtamt.text) & “where am1=1”
end if
Here I check the value of column with if condition statement. I need to check the column with out if condition statement.
What is my doubt is that how I can check value of amc2 column while updating.
eg: update amc set case when amc2>0 then AMC2= AMC2 + “ & val(txtamt.text)
like this
Is it possible with “case-when -end”?
If it is possible I can solve a big problem in my project.
Can you give me an example with this query?
Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )
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 RelatedHi all
I have a column the customer table which holds the telephone numbers,
0293 232 232 2
2323 23232333
0222229999 00
the problem is that they all appear with spaces in different places, i need to remove all the spaces, remove the leading 0 if ther eis one and put +44 on the front.
Any suggestions??
Regards Rich
Hi all,
I'm trying to update various rows in my DB with a new value once an action occurs. I am using the following code which does not throw any exceptions, but also does not change the values. Can someone steer me in the right direction?
public static void ChangeRefCode()
{
SqlConnection dbConn = new SqlConnection(ConnectDB.getConnectString());
SqlDataAdapter dataAdapt = new SqlDataAdapter("Select * from Posting",dbConn);
DataSet ds = new DataSet();
SqlCommandBuilder sqBuilder = new SqlCommandBuilder(dataAdapt);
dataAdapt.Fill(ds, "Posting");
foreach (DataRow dr in ds.Tables["Posting"].Rows)
{
dr["ref_code"] = DateTime.Today.Ticks + "-" + dr["poster"].ToString();
}
try
{
dataAdapt.Update(ds, "Posting");
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
dbConn.Close();
}
}