I am using a DTS package to output a view to a pre-determined Excel file. Currently it just adds the output to the bottom of the current table in excel but i would like it to delete the contents of the worksheet before adding the new rows.
Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields.
How do i update the row coming from the transaction file in the tables which contain the rows from the master file .
to better explain the example is Master File
ID Name AGE Salary Transcode TransDate 2 dev 27 2777
Transaction File indicating change
ID Name AGE Salary Transcode TransDate 2 dev 27 24444 C 08072007
the ouput should be
2 dev 27 24444 C 08072007
replacing the existing row in the table(updating the whole row)
i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row.
what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row.
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear: column id (int) column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
How do I insert data into an existing temporary table? Note: I’m primarily a .NET programmer who has to do T-SQL to grab data from time to time.
What I am trying to do is this: 1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence. 2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).
I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I don’t want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.
If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.
-- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
My problem is that #GroupConfidence already exists so in fact I have this code below:
CREATE TABLE #GroupConfidence ([FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SubmitID] [int] NOT NULL, [GL_Score] [decimal](19, 10) NOT NULL, [GP_Score] [decimal](19, 10) NOT NULL, [GPH_Score] [decimal](19, 10) NOT NULL, [DL_Score] [decimal](19, 10) NOT NULL, [MP_Score] [decimal](19, 10) NOT NULL, [Role_MI_Score] [decimal](19, 10) NOT NULL, [Role_ASXRE_Score] [decimal](19, 10) NOT NULL, [Role_APRA_Score] [decimal](19, 10) NOT NULL, [AllCategories] [decimal](19, 10) NOT NULL )
-- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
So I get this error: Server: Msg 2714, Level 16, State 1, Line 109 There is already an object named '#GroupConfidence' in the database.
HelloI used to work in a Sybase database environment. When I had to insert/update records in the database, I always used "insert on existingupdate", in this way, you didn't have to check whether a recordalready existed (avoid errors) and you were always sure that afterrunning the scripts, the last version was in the database.Now I'm looking for the same functionality in MS SQL Server, asked afew people, but nobody knows about such an option.Does anybody here knows the SQL Server counterpart of "insert onexisting skip/update"? If this doesn't exist, this is a minus forMS ;).Greetz,Bart
When inserting records into a table created through a RDA Pull(), many users experience duplicate key violations. One reason for this is Identity columns. SQL Server CE RDA does not set the seed on the Identity columns when a table is pulled.
Source: Microsoft SQL Server 2000 Windows CE Edition
Native Error: 25016
HR: DB_E_INTEGRITYVIOLATION
Description: Value violated the integrity constraints for a column or table.
Interface defining error: IID_IRowsetChange
Param = 0
Param = 0
Param = 0
Param =
Param =
Param =
This error can be returned when the user attempts to insert a row with an automatically incremented Identity column. With RDA, this usually occurs when a user pulls rows from the server and attempts to insert new rows before setting the seed and increment values for the Identity column. By default, the seed and increment values are both 1.
To correct this error, set the seed to the next highest number after the table is pulled, before allowing users to enter data.
What do they mean by setting the seed to the next highest level? I this the seed to the GUID row in the pulled table? How do you correctly do this with the ALTER table statement? The database that I am pulling has 21 tables, and would be a pain to have to do this. Does anyone have any other ideas as to why this won't work properly. If I clear out the data from the tables on the publishing server, I can add data to the pull as long as I want until I do another pull later. After I do that, I keep getting the above issue.
I need to run a query and insert the count results and a name of another table into an existing table.
I have 20 jobs that import records from text files into seperate tables. When that completes I want the count to be sent to another table along with the table name that the count was run on. Any ideas? I can't get SELECT INSERT to work.
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2 Windows Server 2003 1GB Ram....(I know, I know)
TIA
Code Snippet GO CREATE TABLE [dbo].[Tokens]( [TokenID] [int] IDENTITY(1,1) NOT NULL, [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED ( [TokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens] ( [Token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE FUNCTION [dbo].[SplitTokenList] ( @TokenList varchar(max) ) RETURNS @ParsedList table ( Token varchar(255) ) AS BEGIN DECLARE @Token varchar(50), @Pos int SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ',' SET @Pos = CHARINDEX(',', @TokenList , 1) IF REPLACE(@TokenList , ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1))) IF @Token <> '' BEGIN INSERT INTO @ParsedList (Token) VALUES (@Token) --Use Appropriate conversion END SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos) SET @Pos = CHARINDEX(',', @TokenList, 1) END END RETURN END GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList] @TokenList varchar(max) AS BEGIN SET NOCOUNT ON; create table #Tokens (TokenID int default 0, Token varchar(50)) create clustered index Tind on #T (Token) DECLARE @NewTokens table ( TokenID int default 0, Token varchar(50) )
--Split ID's into a table INSERT INTO #Tokens(Token) SELECT Token FROM SplitTokenList(@TokenList) BEGIN TRY BEGIN TRANSACTION --get ID's for any existing tokens UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token) OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old SELECT TokenID, Token FROM #Tokens WHERE TokenID <> 0 UNION SELECT TokenID, Token FROM @Tokens COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @er nvarchar(max) SET @er = ERROR_MESSAGE(); RAISERROR(@er, 14,1); ROLLBACK TRAN END CATCH; END GO
hello friends my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn) 'you should use sproc instead cmd.Parameters.AddWithValue("@UserId", textbox1.text) 'your value Try conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery() conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString())) Catch sex As SqlExceptionThrow sex Finally If conn.State <> Data.ConnectionState.Closed Then conn.Close() End If End Try MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource() SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)" SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0 Try rowsaffected = SglDataSource2.Insert()Catch ex As Exception Server.Transfer("yardim.aspx") Finally SglDataSource2 = Nothing End Try If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx") ElseServer.Transfer("urunsat.aspx") End If
i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much
when i execute ... select * from Members where address = '6257 Rockwell's' ERROR :
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 's'. Server: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string ' '.
i just wanna replace the last word 'roa' to 'road' what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.
declare @table table(ad_str1 varchar(20)) insert @table select 'street road roa' union all select 'street street'
select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table
update @table set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road') where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'
I have a colunm in a table that has a list of email address. I want to remove a certain email address from that colunm, but it is in 54 different rows. What is the best way to do this ?
I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because 'IN' must return only one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.
Code Snippet SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc, sh.Balance, sh.Available_Bal FROM (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr = sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr) INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN Participation_Type ON ac.Participation_Id = Participation_Type.ID) WHERE ac.INDIVIDUAL_ID = 5249 Except SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc, sh.Balance, sh.Available_Bal FROM (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr = sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr) INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN Participation_Type ON ac.Participation_Id = Participation_Type.ID) WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249); This code gets me close using 'IN'. Code Snippet SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc, sh.Balance, sh.Available_Bal FROM (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr = sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr) INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN Participation_Type ON ac.Participation_Id = Participation_Type.ID) WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in (SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in (SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr WHERE ac.participation_id <> 101 AND sh.share_type > 49)
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
Hi all, I made a migration operation and when I am looking inside database I can see the <BR> tags. The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code. Thanks in advance.
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is @FieldName might be: ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output? Thanks.
Hi, I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ... I have this stored in a column IMAGESRC Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this Project/NEWUSERID/ProjectImages/702d_2.jpg How can I accomplish this in SQL???
I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.
I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''')
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal, a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS Technicals FROM a_Financials INNER JOIN a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3') ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal GO
Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?
That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.
I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.
Am I not using the replace function correctly? Is there another way in which I might accomplish the task?
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname" Dim CommandText As String
'Command text is greatly abreviated for this discussion. CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"
Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet() Dim dv as new dataview() myCommand.Fill(ds)
