Replace SQL Server 2000
Mar 6, 2008
I want to replace my Windows 2000 SQL Server 2000 with a new beefier machine. And I really don't want to change all the setups on the users PCs to point to this new machine. So, I want the new machine to have the same name as the old machine. I think that Windows actually uses SIDs and not the name so I'm wondering what problems I may cause by just naming the new machine the same as the old and replacing?
My guess is that this is done everyday. Does anyone know where I can find information on how to do this safely?
Thanks
View 4 Replies
ADVERTISEMENT
Jul 12, 2007
Hello
with the following query :
SELECT words FROM T1
i get :
A,B,C
how can i get
A > B > C
something like String.Replace(words , ',' , ' > ')
thank you
View 3 Replies
View Related
Apr 20, 2006
I'm moving some queries out of an Access front end and creating views out ofthem in SQL Server 2005 express. In some of the numeric fields, I use nzquite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.Is there anything equivalent to this in SQL Server? Right now I'm usingCASE WHEN ... but it seems like an awful lot of script to write just toreplace null with a zero.Any help would be greatly appreciated.Thanks!
View 16 Replies
View Related
Apr 21, 2006
I found a search-and-replace program for SQL server that works GREAT, but I have a list of about 200 words to search for, with corresponding replacements. Rather than editing the code below for *each* word and running it 200 separate times, I'd like to iterate through the list, but my MS-SQL programming skills are...light (to say the least). Anyone have any ideas how I can create a list or hash-type variable and use the code to loop through and do the replacements all at once?
/*
*
* Search & Replace
*
* Use Ctrl+Shift+M to replace template values
*
*/
set xact_abort on
begin tran
declare @otxt varchar(1000)
set @otxt = '<string1, text, text to be replaced>'
declare @ntxt varchar(1000)
set @ntxt = '<string2, text, replacing text>'
declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
declare curs cursor local fast_forward
for
select
id,
textptr(<field_name, sysname, target text field>),
charindex(@otxt, <field_name, sysname, target text field>)-1
from
<table_name, sysname, target table>
where
<field_name, sysname, target text field>
like
'%' + @otxt +'%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
updatetext <table_name, sysname, target table> .<field_name, sysname, target text field> @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs
commit tran
View 1 Replies
View Related
Feb 15, 2005
an example will be appriciated!!!
View 4 Replies
View Related
Dec 22, 2006
I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).
There are about 200 tables involved.
In SQL Server what would be the best way to do this and minimize logging?
Thanks in advance.
Gerry
View 1 Replies
View Related
Aug 11, 2015
Is it possible to replace while loop by CTE?
I will fetch records from table 1 based on tretmentid.
get the count of records
run the while loop
inside loop I will compare the column values with the same table(table 1) for different tretmentid.
if any one row matches will come out from loop and send status .
can it be replaced by CTE ?
View 1 Replies
View Related
Oct 15, 2006
I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.
View 8 Replies
View Related
Apr 14, 2004
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
View 8 Replies
View Related
Jul 5, 2015
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
View 3 Replies
View Related
Aug 12, 2015
I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?
View 14 Replies
View Related
Sep 27, 2007
I have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.
View 2 Replies
View Related
May 5, 2006
My existing SQL server is an old box that can no longer cope with the load. I need to replace it with a brand new box.
I am looking for a comprehensive set of steps that I could follow to ensure that the transition to the new server goes well.
I'm looking to transfer all my user databases but I'm not so sure whether I have to backup and restore my system databases to the new server as well.
I want to name the new server the same as the old server since I will be taking the old server out of production but I'm not sure whether I should rename the server AFTER I move over all the database and how that would affect SQL. Or should I first rename the old server, remove it out of the domain, then name my new server with the same name as my old server, and then proceed with the SQL installation and the moving of the databases.
Also, I do have a maintenance plan, DTS packages and some jobs set up on my old server that I would also like to move to the new server.
Is there a web page that explains all the steps in granular detail ?
View 1 Replies
View Related
Feb 26, 2008
hi all,i was using the system tables namely sysindexes and sysobjects in sql server 2000. But now sql server 2005 is using instead of sql server 2000. Since am using sysindexes and sysobjects, too much time is taking for the execution in sql server 2005. So I need to change the query suitable for sql server 2005.I have read in msdn that the system tables are replaced with corresponding catalog views in sql server 2005. The catelog view for sysobjects is sys.objects. plz check the link http://technet.microsoft.com/en-us/library/ms187997.aspxCould anyone please tell me how to replace the sysindexes and sysobjects in the query without rewriting the query too much. If I can replacesysobjects with just sys.objects, then it will be very helpful. Is it possible? And what about sysindexes. Any help anyone could give would be greatly appreciated!
View 8 Replies
View Related
Apr 7, 2006
How to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
View 3 Replies
View Related
Apr 24, 2014
There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )
View 1 Replies
View Related
May 7, 2015
I would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.
The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.
JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.
Current Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN
Desired Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN
Current SQL Select statement:
SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,
[code]....
View 9 Replies
View Related
May 22, 2015
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
View 3 Replies
View Related
Jul 13, 2015
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
View 7 Replies
View Related
May 27, 2014
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
View 9 Replies
View Related
Nov 18, 2014
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
View 1 Replies
View Related
Mar 3, 2015
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'
[code].....
View 0 Replies
View Related
Apr 15, 2015
I'm attempting to improve a vendor created query.
Here is the original -
SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1
[Code] ....
If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.
View 9 Replies
View Related
Apr 16, 2015
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
View 2 Replies
View Related
Jun 8, 2015
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
View 5 Replies
View Related
Aug 18, 2015
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
View 7 Replies
View Related
Mar 27, 2008
Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
I tried following
UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')
But this is producing following error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
Need help, how to do it?.
Thanks In Advance
Devloper
Anil Kumar Dwivedi
View 4 Replies
View Related
Aug 14, 2015
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
View 5 Replies
View Related
Feb 24, 2005
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 Replies
View Related
Feb 28, 2008
Hi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G
View 2 Replies
View Related
Sep 14, 2004
I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.
View 2 Replies
View Related
Jul 20, 2005
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies
View Related
Jul 20, 2005
What is the limitation of memory that SQL Server 2000 Standard can usewhen running on a Windows 2000 Advanced Server platform?
View 1 Replies
View Related
