Using Update For Modifying Text
Hello All,
I would like to update some values in my table that have this value: XXXX-XXXX. I would like to update the '-' and change it to a '_'. Does anyone know the best way to do this?
I tried this but I get a "Subquery returned more than 1 value error"
UPDATE PS_MTCH_RULES
SET MATCH_RULE_ID = (SELECT SUBSTRING (MATCH_RULE_ID,1,4)+'_'+SUBSTRING(MATCH_RULE_ID,6, 4) FROM PS_MTCH_RULES WHERE MATCH_RULES_ID <> 'ERS')
Any assistance would be appreciated.
Thank you.
E David Ramos
View Complete Forum Thread with Replies
Related Forum Messages:
Trouble With Update Trigger Modifying Table Which Fired Trigger
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View Replies !
TEXT UPDATE
Dear ALL, Have everyone can solve the following problem: I have a TABLE "description" in a SQL Server Datable version 7.0 In this table, the structure is ID descript 1 ..... 2 .... 3 .... 4 ... 5 . The field "descript" is TEXT field. The problem is that I want to change "center" to "centre" in the content in this all of the text. How can I search all the word "center" in the Text and change it to "centre"??? Similar problem like this to update keywords in this Text are found?? Have anyone Help ME???!!!!
View Replies !
Update Dataadapter With Text Box Value
hi this is probably a piece of pie to fix but i be having some problems...... i have this peice of code to update the sql dataapadapter Dstest1.Tables("test").Rows(0)("hello") = Me.textboxhello.Text SqlDataAdapter1.Update(Dstest1, "test") this does not work, it does work however if i replace the Me.textboxhello.Text with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!! thanks for all your help cq
View Replies !
How To Update Text Field
Hi all, I have a table with millions of records, table has three fields: case_id,line_no and notedata field, notedata field is 60 chars long, datatype varchar.for each case_id there could be as many as 2000 line_no meaning 2000 notes. I need to compress these notes into one note by case_no, For example case_no 1 could have 2000 lines of notes but my comressed table shoul have only one line containing all 2000 notes in line_no sequence. my compressed table contains two fields case_no and notetext, notetext is a text field. here is the script I am trying to use to accomplish the task but it does not append more than 8000 chars in one case, so my notes are chopping of, how should I do this, please let me know of any suggestions.. Thanks. truncate table eldoecinotescompressed insert into eldoecinotescompressed (app_code, case_no) select distinct app_code, substring(system_key, 6,8) from eldoecinotes DECLARE @case VARCHAR(20); DECLARE @note VARCHAR(80); DECLARE @lineno VARCHAR(5); DECLARE notes_cursor CURSOR FOR select substring(system_key, 6,8) case_no, line_no, rtrim(notedata) notedata FROM EldoECINotes where substring(system_key, 6,8)<>'' order by 1,2; OPEN notes_cursor; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; WHILE (@@FETCH_STATUS = 0) BEGIN BEGIN TRANSACTION; update eldoecinotescompressed set notetext = (case when isnull(datalength(notetext), 0) >= 0 then substring(isnull(notetext,''), 1, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 8000 then substring(isnull(notetext,''), 8001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 16000 then substring(isnull(notetext,''), 16001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 24000 then substring(isnull(notetext,''), 24001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 32000 then substring(isnull(notetext,''), 32001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 40000 then substring(isnull(notetext,''), 40001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 48000 then substring(isnull(notetext,''), 48001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 56000 then substring(isnull(notetext,''), 56001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 64000 then substring(isnull(notetext,''), 64001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 72000 then substring(isnull(notetext,''), 72001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 80000 then substring(isnull(notetext,''), 80001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 88000 then substring(isnull(notetext,''), 88001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 96000 then substring(isnull(notetext,''), 96001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 104000 then substring(isnull(notetext,''), 104001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 112000 then substring(isnull(notetext,''), 112001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 120000 then substring(isnull(notetext,''), 120001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 128000 then substring(isnull(notetext,''), 128001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 136000 then substring(isnull(notetext,''), 136001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 144000 then substring(isnull(notetext,''), 144001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 152000 then substring(isnull(notetext,''), 152001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 0 then char(13) + char(10) else '' end) + isnull(@note,'') where case_no=@case; commit; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; END CLOSE notes_cursor; DEALLOCATE notes_cursor;
View Replies !
Update A Text Field In T-SQL 2000.. How ??
First i use openxml to get my data to update the other server with webservices. my prob is that i cant update text Fields because i got an error ================ OLE DB provider 'OpenXML' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'OpenXML' IRowset::RestartPosition returned 0x80004005: The provider did not give any information about the error.]. ================ what would be my best shot here... thanx ======================================= XEC sp_xml_preparedocument @handle OUTPUT, @data begin transaction SELECT * FROM TblEvenement WHERE idEvenement = 95 UPDATE TblEvenement SET TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement), TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenement), TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescriptionCourte), TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescriptionLongue), TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle), /*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/ TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste), TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAfficherInternet), TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletLimite), TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivraisonCourrier), TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTypeRepresentation), TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInternet), TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique), TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmissionGenerale), TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEvenementEnVente), TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur), TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementDemo), /*TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA),*/ /*TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722),*/ TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDescriptionCourte_En), /*TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDescriptionLongue_En),*/ TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetailInternet_En) FROM OPENXML (@handle, N'//TblEvenement') WITH ( idEvenement int, sNomEvenement nvarchar (100), sDescriptionCourte nvarchar (100), sDescriptionLongue text, IdTypeSalle int, imgSalle image, sNomArtiste nvarchar (100), bAfficherInternet bit, nNbBilletLimite int, bLivraisonCourrier bit, IdTypeRepresentation int, sDetailInternet nvarchar (100), bHistorique bit, bAdmissionGenerale bit, bEvenementEnVente bit, IdProducteur int, bEvenementDemo bit, sLogoBOCA text, sLogoLP2722 text, sDescriptionCourte_En nvarchar (100), sDescriptionLongue_En text, sDetailInternet_En nvarchar (100), sNomEvenement_En nvarchar (100) ) iox WHERE TblEvenement.idEvenement = iox.IdEvenement /*and WRITETEXT (TblEvenement.sDescriptionLongue @ptrval 'Salut')*/ SELECT * FROM TblEvenement WHERE idEvenement = 95 rollback EXEC sp_xml_removedocument @handle
View Replies !
Appending To A Text Field Using UPDATE
I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0) I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement. Thanks, SBR
View Replies !
Openxml - Update Text Field In T-sql
I got an error when i try to udate any of my text field. Is there a good way to do it EXEC sp_xml_preparedocument @handle OUTPUT, @data begin transaction SELECT * FROM TblEvenement WHERE idEvenement = 95 UPDATE TblEvenement SET TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement), TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenemen t), TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescri ptionCourte), TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescri ptionLongue), TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle), /*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/ TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste), TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAffiche rInternet), TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletL imite), TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivrai sonCourrier), TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTyp eRepresentation), TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInt ernet), TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique), TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmiss ionGenerale), TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEveneme ntEnVente), TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur) , TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementD emo), TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA), TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722), TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDes criptionCourte_En), TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDes criptionLongue_En), TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetail Internet_En) FROM OPENXML (@handle, N'//TblEvenement') WITH ( idEvenement int, sNomEvenement nvarchar (100), sDescriptionCourte nvarchar (100), sDescriptionLongue text, IdTypeSalle int, imgSalle image, sNomArtiste nvarchar (100), bAfficherInternet bit, nNbBilletLimite int, bLivraisonCourrier bit, IdTypeRepresentation int, sDetailInternet nvarchar (100), bHistorique bit, bAdmissionGenerale bit, bEvenementEnVente bit, IdProducteur int, bEvenementDemo bit, sLogoBOCA text, sLogoLP2722 text, sDescriptionCourte_En nvarchar (100), sDescriptionLongue_En text, sDetailInternet_En nvarchar (100), sNomEvenement_En nvarchar (100) ) iox WHERE TblEvenement.idEvenement = iox.IdEvenement SELECT * FROM TblEvenement WHERE idEvenement = 95 rollback EXEC sp_xml_removedocument @handle
View Replies !
IF UPDATE() Function And Text Fields
I'm using IF UPDATE() function in the update trigger. the problem is -- that bloody function evaluates to true all the bloody time for a text field, even if text field is not present in the update clause. (I'm using SQL Server 6.5 with SP5a) microsoft article about "IF UPDATE() functionality is broken in service pack 5, 5a" applies only to insert triggers.... any ideas? suggestions? am I crazy? thanks in advance. Gregory
View Replies !
Update Of A Text/image And A Clustering Key
Hello Everyone, We receive this error with ADO.NET (on a SQL Server 2000): The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ?OnExecuteNonQuery@dtbmsq_Statement_c@@$$FUAE?AW4dtb_Result_t@@PAUdtbsql_Connection_ip@@PAH@Z(dtbmsq_Statement_c* , dtbsql_Connection_ip* Connection, Int32* NbRowsAffected) in s:ogl20061srcodbmsqdtbmsqdtbmsq_statement_c.cpp:line 598 N.B. The update concerned only update one row. Is it a known restriction? Is it a restriction from ADO.NET or from the database server? Is it documented somewhere? Regards. Carl
View Replies !
UPDATE Command Help?? Exclude Text In Brackets..
Good Morning Folks, Pretty new to Mysql, and have a query that if anyone can help me with id be very grateful!! I am using the UPDATE command and wish to copy a columns data to another column, but the first column contains text then (text in a bracket) i wish to copy everything from the first column except whats in the bracket to column 2??? example... UPDATE table_name SET `field1` = `field2`...............??? Any ideas?? Many Thanks
View Replies !
Update A Table With SqlDataAdapter...does It Work With Sql Text DataType ?
I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was : "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator." Is there a way to do it ? Thanks, Jeff
View Replies !
Appending Data To A Text Field In Update Query?
I would like to run an update query that will update multiple rows and append a value to a text field. In theory i would want something that would function like this if possible: update table1 set field1 = field1 + 'some text to be appended' where field2 = value field1 is of datatype Text Does anyone know of a way to acomplish this? Thanks in advance!
View Replies !
Full-text Search Does Not Automatically Update Index When CHANGE_TRACKING AUTO
On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index: insert SomeTable(keywords)values('this is a test') and then query: SELECT * FROM Message WHERE Contains(keywords, ' "test" '); I get the expected rows all rows that have "test" in the keyword. On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog. Is this a known limitation of Express? ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ CREATE TABLE [dbo].[SomeTable]( [key] [int] IDENTITY(1,1) NOT NULL, [keywords] [nvarchar](255) NOT NULL CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords]) ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE
View Replies !
Modifying XML In SQL
Greetings DECLARE @Xml AS XML Given the above variable I get using a SELECT @Xml ... FOR XML, AUTO, is it possible to change the value of one of the elements in the XML file in SQL 2005? Thanks
View Replies !
Modifying A Field
Hello, I need to modify a field in my table to add increment, the field already has data(1,2,3....). The thing is I am working with sqlserver 6.5(heard it all). How would I do that using the tool? Using SQL? Thanks in advance.
View Replies !
Modifying Data
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string. Alter Procedure usp_UpdateJobName @JobNameOld nchar, @JobNameNew nchar As UPDATE JobName SET JobName=@JobNameNew FROM tblRMADATA WHERE tblRMADATA.JobName=@JobNameOld
View Replies !
Modifying The DTS Package.
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata So how and where can I change my package. Thanks.
View Replies !
Modifying The DTS Package.
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
View Replies !
Modifying Job Notification
Is there a way to modify the information sent on a SQL Server job notification? The information sent now is rather lengthy and exceeds my pager capacity. As a result, the job status (Succeeded, Failed), which is the most important information does not appear. The workaround I am using now is that I have a job step which utilizes a xp_sendmail to notify of job completion.
View Replies !
Modifying A Column
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following: ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY but this does not work. Any idea how to accomplish this? Thanks! Mike
View Replies !
Creating And Modifying A Job
I really hope someone can help me with this: I've been struggling with it all day to no avail. We have a db user (batch: is only used in overnight processes) who has some rights, but not all, and as a result of this, the user cannot run the execute command. And now it needs to. So we had the bright idea of getting batch to create a job to do what it needed to. But, of course, batch is the owner of the job, so the same rights issue occurs. So I thought about creating job using sysadmin, then letting batch modify it as it needs to (changing the run date, etc), but it looks to me like a job created by sa is 'invisible' to batch. Any update attempt is met with "The specified @job_id does not exist". If I create the job with batch, no prob, but that's not going to work for me. Please, does anyone have any ideas here?
View Replies !
Help In Modifying Query
Greetings I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created SCRIPTS DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30), StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT) INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA055', 67, 195, Null DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT) INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 2, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 3, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 2, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532 DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime) INSERT @CallDetail SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL SELECT 6532, 1, '11/5/2007 12:12:34 PM' -- --select * from @Request Query written to achieve the requirement UPDATE r SET r.UniqueNo = p.RecID FROM @Request AS r INNER JOIN ( SELECT r.RequestID, ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID FROM @Request AS r INNER JOIN @Call AS c ON c.CallID = r.CallID INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID ) AS p ON p.RequestID = r.RequestID WHERE r.UniqueNo IS NULL select * from @Request OUTPUT : 324234 Jack SA023 12 111 1 223452 Tom SA023 12 112 2 456456 Bobby SA024 12 114 1 22322362 Guck SA024 44 123 1 22654392 Luck SA023 12 134 1 225652 Jim SA055 67 143 1 126756 Jasm SA055 67 145 2 786234 Chuck SA055 67 154 1 66234 Mutuk SA059 72 185 1 2232362 Buck SA055 67 195 1 EXPECTED OUTPUT: (See the last column for unique nos). 324234 Jack SA023 12 111 1 223452 Tom SA023 12 112 1 456456 Bobby SA024 12 114 1 22322362 Guck SA024 44 123 1 22654392 Luck SA023 12 134 1 225652 Jim SA055 67 143 2 126756 Jasm SA055 67 145 2 786234 Chuck SA055 67 154 2 66234 Mutuk SA059 72 185 2 2232362 Buck SA055 67 195 2 How can I modify the query to achieve the expected output? Thanks:
View Replies !
Modifying Report
Hi our reports server has some reports on it. we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO". can we get rdl file from reporting server ?
View Replies !
Help With Modifying Query
Hi, I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it. What I want to add is the following data. For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members. Any assistance is much appreciated!! thanks once again mike123 SELECT CONVERT(varchar(10),referDate,112) AS referDate, SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0', SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1', SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2', SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3', SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3', SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent', count(*) as totalRefers, count(distinct(referUserID)) as totalUsers, SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail', SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk', SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca', SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk', SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail', SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol', SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo', SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual', SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto' FROM tblreferemails R WHERE DateDiff(dd, referDate, GetDate()) < 5 GROUP BY CONVERT(varchar(10),referDate,112) ORDER BY referDate DESC CREATE TABLE [dbo].[tblUserDetails] ( [UserID] [int] IDENTITY(1,1) NOT NULL, [NameOnline] [varchar](15) NULL, [EmailAddress] [varchar](50) NULL, [Date] [datetime] NULL, [Active] [tinyint] NULL ) CREATE TABLE [dbo].[tblReferEmails]( [emailID] [int] IDENTITY(1,1) NOT NULL, [referUserID] [int] NOT NULL, [destinationName] [varchar](50) NULL, [emailaddress] [varchar](50) NOT NULL, [referDate] [datetime] NOT NULL, [referalTypeID] [int] NULL, [deleted] [int] NULL, [emailSendCount] [int] NULL, [lastDateSent] [smalldatetime] NULL ) ON [PRIMARY] GO
View Replies !
Needs Help With Modifying Stored Procedure
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID. I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure? Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View Replies !
Modifying A Lot Of Stored Procedures
I probably know that I don't want to do this, but I have an odd case. I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database. I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that. I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion. Thanks, Pete
View Replies !
Problem Modifying Out-of-the-box TFS Report
My office has adopted Team Foundation Server to track and manage thedevelopment and deployment process at our site. I have been asked tomodify one of the standard work item reports, "Related Work Items,"that ships with TFS. I've successfully done some of these, whichinvolved changing the MDX code for the datasets that generate defaultparameter values, but I am running into problems modifying the maindataset, dsRelatedWorkItems. I am using VS2005. When I look at thecomponents of this dataset on the tree diagram for all datasets, allof the correct fields are present, I can Preview the report and itworks. When looking at the Data page, I see the MDX query on top anda results pane on the bottom. The results pane correctly lists all ofthe fields I saw in the tree diagram across the top. But the queryitself does not look like the query that would have generated thesecolumns. Rather, the columns in the result pane correspond to therows in the query, not the columns. When I execute the query on thedata page, all of those columns disappear in the result pane, and theonly column that shows up is the single field that precedes the ONCOLUMNS statement in the MDX. At this point, if I attempt to previewthe report, it fails because it doesn't have the expected fields inthe dataset. If I try to switch to the design mode to see whetherthere is any alternate definition of the dataset lurking somewhere, itlooks like all dataset definitions are lost. The properties page forthe dataset shows the fields correctly if I haven't yet executed thequery. If I try to make any of the modifications that I need to make(which are simple -- I just need to remove some of the slicerconditions in the WHERE clause), I am again unable to preview thereport.It looks like the query that I see in the query pane is not what isreally being used for the dataset, and I'm stumped. I am not anexperienced MDX user, but I can do fairly basic modifications of thetype that are needed here. It seems like my problem isn't MDX syntax,but rather how (or where) Reporting Services is keeping the query thatunderlies this dataset.Anybody out there have a clue that might get me back on track withthis? Thanks.
View Replies !
Modifying User Security
I have an asp page that currently is creating a database and a userlogin for that database. After everything successfully (I thought)executed, I tried to change my connection properties for the serverand then login as this new user. It wouldn't allow me to, so I loggedback in as the administrator and looked at the properties for the newlogin. On the general tab, it had the user's default databasespecified as the new database that I had created in the asp page, butwhen I went to the database access tab, the database was not selected.So, I'm not sure how to set that in my script. I've done somesearching in BOL, but I can't figure it out. Also, if there's a wayto do this in a query, or stored procedure, will it also specify whattype of role the user has (public, db_owner, etc.)? Thanks.
View Replies !
Deleting/modifying Of Job Fails
When I try to delete/modify a job on my sqlserver the following error occurs: error 14274: Cannot Add, update or delete a job that originated from a n MSX server. What does that mean and how can I solve this ?
View Replies !
Modifying Table Column
Can someone tell me the easiest/best way to make a modification to a table where you change a column constraint from not null to null? Would you rename the table, create the new one with the different column and then transfer the old data into the new definition? thanks
View Replies !
Name Of Modifying User In Trigger ?
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username? Thanks, -Darin.
View Replies !
Modifying Table Size
Hi guys, I am trying to modify my table sizes. I need to extend the size of certain columns in several tables and keep the data presently in those tables also. On my first attempt I successfully made the changes but had no data after dropping the original tables. Then I had to restore from a back up and I was back to where I started. Does anyone know a reliable way of doing this with out having to create another DB and transferring the data after making the size changes to my tables ? Thanks, Alan
View Replies !
Modifying SYSJOBSTEPS Table
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts? Thanks!
View Replies !
Modifying System Table
Quick Question: I want to change an execution time for 345 jobs and do not want to do it one by one in EM. I have changed the active_start_time and next_run_time in the sysschedules table but it is not showing up within the EM job folder under the next run date as being modified. Does anyone know what I am missing? TIA, Daimon
View Replies !
Modifying Publication In SQL 7.0- Urgent Help .
Hello , I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration . Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified . The table is configured for transactional replication . I will have to drop the entire publication and create all the subcribers again . Any help in this regard please . Sameer
View Replies !
Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?
Hi, I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor. Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn? Thanks for your help, David
View Replies !
Modifying SQL Statement In A Lookup
Hi there, I'm trying to modify the SQL statement of a Lookup Transformation. In my DataSet I have 2 columns A, B - I join against a table on columns A and B by But I only want to match on column B - where column A doesn't match. (I'm looking for duplicate values of B) So I've tried modifying the SQL statement to: select * from (select * from [dbo].[LookupTable]) as refTable where [refTable].[ColumnB] = ? and [refTable].[ColumnA] <> ? (The paremeter mapping takes care of itself). Anyway, it seems sometimes I'm returning rows where it HAS matched on ColumnA. I'm I on the right track here? Cheers, dave.
View Replies !
Modifying Generated XSD File
Hello, I am working with an .xml file that I want to break up into various tables. A couple of fields in my xml file include html tags (<p> tags specifically). The generated xsd file thinks these are nested xml tags and creates a "P" table for the information contained. Is there a way I can modify the generated xsd within the designer? Or is the only way to fix this is to manually modify the xsd? There is also an <id> tag within the xml. However, SSIS is not using that <id> as the primary key as it adds data to the various tables that I've specified. Instead it appears to be creating its own primary key - calling it "Id". Is there some way to specify that SSIS use the <id> tag contained within the xml instead of creating its own primary key? Thank you for any help.
View Replies !
Modifying SSISTutorial.dtsConfig
I am going thru the Integration Services Tutorial found in Books Online and I'm on Lesson 3. It has to do with creating a configuration file for the source file location. I followed the steps and now I need to open the .dtsConfig file and type in the folder location. The exact direction says, " In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task. Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes. Save the change, and then close the text editor. " When I open the file, I see this. What am I supposed to do here? <?xml version="1.0" ?> - <DTSConfiguration> - <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="FFTWayi" GeneratedFromPackageName="Lesson3" GeneratedFromPackageID="{74BC5CCC-5C51-4ABD-9263-AD3C000E9E1C}" GeneratedDate="4/7/2008 11:25:53 AM" /> </DTSConfigurationHeading> - <Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String"> <ConfiguredValue /> </Configuration> </DTSConfiguration>
View Replies !
Modifying SSIS Jobs?
I have a brief question. With a DTS scheduled job if I needed to make a modification, I just changed the DTS and saved it, and the job would automatically Run the latest DTS (so long as the name was the same) When I migrate a package to SSMS and schedule it , if I need to modify the package, do I need to modify it through BIDS (visual studio), then re-import it to SSMS and then reschedule it ? Or is there a quicker way of modifying it ? Thanks
View Replies !
Modifying Model AttributeIDs...possible?
So the AttributeIDs used in a Report Model..they look and smell like a GUID..but they are rather finicky. For one, they need to start with a "G". Anyway, say I want to create my own GUIDs for these models..seems I can do so in limited fashion by creating a GUID, replacing the first char with a G. Of course, change all references to that particular item n the model.. Redploy the model and it works! I can create new reports all day long. But something strange happens behind the scenes..somewhere. So you can no longer use the wizard to update your model...even after changing the GUID references back to the original ones the model builder chose for you. I did a WinDiff on the before and after and saw absolutely no difference in the files..Something stored on the server maybe and not shows in Visual Studio. Long story short...is there anyway I can construct or edit models with my own GUIDS or is there a super secret algorithm that I must adhere to? I dont want to simply use the .GenerateModel method...I really need to use my own GUIDs..but it appears I am missing some piece to make this work perfectly.
View Replies !
Modifying With 3rd Party Application?
Hi, I have recently started using SQL Server with a 3rd party application and have come across a problem and i have no idea how to fix it... The database has a column of numbers that go up either by 10 or 100 depending on the letter in another column. eg: 100 M 110 P 120 P 200 M 300 M 400 M 500 M 510 P 520 P The 3rd party application has just been updated and now can add new rows to this table. The only problem is that the added row needs a new number, and the rows below need to be recalculated. Is there anyway this can be done? Any help or suggestions would be great! Cheers, BL
View Replies !
Modifying A Table From A View
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table. I also have an inner join view with the initials from both tables as the joining field. I want the users to be able to modify the data table (MainTable) but also display the users name field. here is the sql of the view: SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName, dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify, dbo.MainTable.AdvocateInitials FROM dbo.InitialsListing INNER JOIN dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
View Replies !
Modifying Blog Query For Use
I have been using this blog entry as a guide to finding the differences between an original table and one I modified: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx Here is the relevant snippet from my SQL query. SELECT MIN(Testing) AS TableName, Testing.Key, Testing.[Article Number], Testing.[Coder Initials], Testing.[Research Report Details], Testing.[%military participants], Testing.[%commercial participants], Testing.[%non-profit participants], Testing.[%educational participants], Testing.[Mean Participant Age],..... FROM (SELECT 'Affectivity_Data' as TableName, Affectivity_Data.Key, Affectivity_Data.[Article Number], Affectivity_Data.[Coder Initials], Affectivity_Data.[Research Report Details], Affectivity_Data.[%military participants], Affectivity_Data.[%commercial participants], Affectivity_Data.[%non-profit participants], Affectivity_Data.[%educational participants], Affectivity_Data.[Mean Participant Age],...... UNION ALL SELECT 'Affectivity_Data_Original' as TableName, Affectivity_Data_Original.Key, Affectivity_Data_Original.[Article Number], Affectivity_Data_Original.[Coder Initials], Affectivity_Data_Original.[Research Report Details], Affectivity_Data_Original.[%military participants], Affectivity_Data_Original.[%commercial participants], Affectivity_Data_Original.[%non-profit participants], Affectivity_Data_Original.[%educational participants], Affectivity_Data_Original.[Mean Participant Age], Affectivity_Data_Original.[SD Participant Age],....) AS tmp GROUP BY Key HAVING COUNT(*)=1 ORDER BY Key; I am getting popup boxes for filling in field values in the new table, with no data in the table unless I enter it in the popups. I have tested the union statement; it works. A pointer on what I might be doing wrong in the rest of the statement would be much appreciated. Using Access 2007 if that is applicable. Thanks ahead of time.
View Replies !
|
TRACKER
