Rename MSDE table using SQL?

I am migrating a little VB6 app from Access to MSDE that
among other things has to rename some tables in a
database. That can be easily done with Enterprise
manager, but in my case has to be done programmatically
through ADO. I am wondering if this is possible, and if
so what is the syntax?
Take a look at sp_rename.
Mike
"s nikolsky" <anonymous@.discussions.microsoft.com> wrote in message
news:008c01c4aafd$480436a0$a401280a@.phx.gbl...
> I am migrating a little VB6 app from Access to MSDE that
> among other things has to rename some tables in a
> database. That can be easily done with Enterprise
> manager, but in my case has to be done programmatically
> through ADO. I am wondering if this is possible, and if
> so what is the syntax?

Rename a linked server

I have an app that has some stored procedures running queries across a linked server. In our development environment, the linked database is on a box called 10.xxx.xxx.xx. In the production environment, the linked database will be on a server called something else. What I want to do is have the linked server in the development environment named the same thing as the linked server in the production environment, so that I won't have to change the stored procedures when migrating the project into production.

SQL Server 2005 isn't giving me the option to rename or alias the linked server, though. Is there anyway to do this so that I can have the linked server named the same thing on the two different boxes and not have to change the sp's?

I don't think you can rename linked servers, but you can create them correctly for your needs. Don't select SQL Server, but do select the SQL native client, or just use T-SQL which may make more sense if trying to standardise stuff accross machines anyway

EXEC sp_addlinkedserver @.server= 'FRED1' ,@.provider='SQLNCLI' ,@.datasrc='10.1.1.1' ,@.srvproduct = 'SQL'

In future please try and pick a more appropriate forum. This is really SQL Server question issue, not SSIS.

sql

Removing duplicates

Hi

I have inherited a web app with the following table structure, and need to
produce a table without any duplicates. Email seems like the best unique
identifier - so only one of each e-mail address should be in the table.

Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able to
get a duplicate count working:

select Email, count(*) as UserCount
from dbo.Members
group by Email
having count(*) > 1
order by UserCount desc

But the methods for create a new table without duplicates fail. My code for
the 2nd method is:

sp_rename 'Members', 'temp_Members'

select distinct *
into Members
from temp_Members

Table...

CREATE TABLE [dbo].[Members] (
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[SendMail] [smallint] NOT NULL
) ON [PRIMARY]
GO

Thanks B.Can I assume that memberid is unique? If so, try this:

INSERT INTO NewTable
(username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail)
SELECT username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail
FROM Members AS M1
JOIN
(SELECT MIN(memberid) AS memberid
FROM Members
GROUP BY email) AS M2
ON M1.memberid = M2.memberid

--
David Portas
----
Please reply only to the newsgroup
--|||Microsoft has a great article on this at
http://support.microsoft.com/defaul...4&Product=sql2k

I use a code block for doing this that I load as required, I only run
it when I have to. Once you've cleaned up your data, put a unique
constraint to prevent dupes from getting back in.

This is the code that I use for cleaning up dupes, you'll have to
modify the table and field names obviously. This config assumes you
are allowing Select/Into's.

/*
--
--WARNING! EXECUTE THIS SCRIPT WITH EXTREME CAUTION!,
--AND THEN ONLY ONE STEP AT A TIME!
--
--In the TotalTransit (DDS) database, this needs to run against
--ddsTrip and ttVoucher_Trip. Since the two tables have different
--layouts, the table that holds dupe rows must be dropped and
--recreated every time this process is run.
--
--There are now two seperate scripts, one for each table.
--
--Run the process one step at a time by highlighting each step.
--
--Wayne West, 10/13/03
-- WW 10/17/03 -- split into two procs, added more comments
--
------------------------
--
--this is here to help find if there are any dupes
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

select tripid, count(*)
from ttVoucher_Trip
group by tripid
having count(*) > 1
--
------------------------
*/
/*
--Step 1 -- Verify there are dupes in the table
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

--Step 2a -- Drop table that will hold dupe key values
drop table zzzholdkey

--Step 2b -- Collect key values, save in zzzHoldKey
select RecKey = tripid, KeyCount = count(*)
into zzzHoldKey
from ddsTrip
group by tripid
having count(*) > 1

--Step 3a -- Drop table that will hold one instance of duplicate rows
drop table zzzholddupes

--Step 3b -- Collect one instance of duplicate rows
select DISTINCT t.*
into zzzHoldDupes
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 4 -- See if more than one field in addition to TripID is
duplicated
--This will indicate additional steps must be taken to clean up the
table.
select count(*), tripid
from zzzholddupes
group by tripid
having count(*) > 1

--Step 5 -- Delete ALL rows based on key value of duplicate records
delete ddsTrip
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 6 -- Reinsert the row captured in Step 3b
insert ddsTrip
select *
from zzzholddupes

--You probably ought to rerun Step 1 to make sure the file is clean.
*/

removing accents and doing accent-insensitive processing

Hi,
I have an app that does case-insensitive, accent-insensitive searches using
sql server fulltext engine. It deals only with western-european lanaguages
(mostly just English and French). The tables are stored in Latin1 General
CP1.
However, I also need to process the search results in an accent-insensitive
way outside of SQL server (using C#).
The approach I took was to create a separate table using CP1253 (gr)
which has no accented characters, and fulltext index that table instead.
When I copy the data from CP1 to the CP1253, SQL Server removes the accents
so I get back the search results without accents on (which is what I want)
However, if the search criteria includes accented characters, I get no
results. The text engine does not strip the accents off the search string
even though the table I indexed is accent insensitive.
It seems that what I need to do is strip accents off the search strings
before submitting the text search. does anyone know of a way to do this?
Failing that, is there another solution that would get me round this
problem?
TIA
Andyyou have to run a series of replace statement in your search string to
remove them.
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:ueUnF8WIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have an app that does case-insensitive, accent-insensitive searches
> using sql server fulltext engine. It deals only with western-european
> lanaguages (mostly just English and French). The tables are stored in
> Latin1 General CP1.
> However, I also need to process the search results in an
> accent-insensitive way outside of SQL server (using C#).
> The approach I took was to create a separate table using CP1253 (gr)
> which has no accented characters, and fulltext index that table instead.
> When I copy the data from CP1 to the CP1253, SQL Server removes the
> accents so I get back the search results without accents on (which is what
> I want)
> However, if the search criteria includes accented characters, I get no
> results. The text engine does not strip the accents off the search string
> even though the table I indexed is accent insensitive.
> It seems that what I need to do is strip accents off the search strings
> before submitting the text search. does anyone know of a way to do this?
> Failing that, is there another solution that would get me round this
> problem?
> TIA
> Andy
>|||Hi,
in ASP, you can use this function :
Public Function removeAccent(source)
avantConversion = "ǒ"
apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOoOoOuUuUuUcC'n"
temp = source
For boucle = 1 To Len(avantConversion)
temp = Replace(temp, Mid(avantConversion, boucle, 1),
Mid(apresConversion, boucle, 1))
Next
temp = Replace(temp, "", "oe")
removeAccent = temp
End Function
You can create this function directly in sql server like this :
CREATE FUNCTION replaceAccentChar (@.source as varchar(255))
RETURNS varchar(255) AS
BEGIN
declare @.charList as varchar(20)
declare @.temp as varchar(255)
declare @.t as int
set @.temp = @.source
set @.charList = 'aeioucn'
set @.t = 0
while @.t <= len(@.charList)
begin
set @.temp = replace(@.temp, substring(@.charList, @.t, 1),
substring(@.charList, @.t, 1))
set @.t = @.t + 1
end
set @.temp = Replace(@.temp, '', 'oe')
set @.temp = Replace(@.temp, '', '''')
return @.temp
END
Daniel
Andy Fish wrote:
> Hi,
> I have an app that does case-insensitive, accent-insensitive searches usin
g
> sql server fulltext engine. It deals only with western-european lanaguages
> (mostly just English and French). The tables are stored in Latin1 General
> CP1.
> However, I also need to process the search results in an accent-insensitiv
e
> way outside of SQL server (using C#).
> The approach I took was to create a separate table using CP1253 (gr)
> which has no accented characters, and fulltext index that table instead.
> When I copy the data from CP1 to the CP1253, SQL Server removes the accen
ts
> so I get back the search results without accents on (which is what I want)
> However, if the search criteria includes accented characters, I get no
> results. The text engine does not strip the accents off the search string
> even though the table I indexed is accent insensitive.
> It seems that what I need to do is strip accents off the search strings
> before submitting the text search. does anyone know of a way to do this?
> Failing that, is there another solution that would get me round this
> problem?
> TIA
> Andy
>|||A very nice bit of code! You forgot though:(
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Blais" <daniel@.danielblais.net> wrote in message
news:4229E151.3020703@.danielblais.net...
> Hi,
> in ASP, you can use this function :
> Public Function removeAccent(source)
> avantConversion = "ǒ"
> apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOoOoOuUuUuUcC'n"
> temp = source
> For boucle = 1 To Len(avantConversion)
> temp = Replace(temp, Mid(avantConversion, boucle, 1),
> Mid(apresConversion, boucle, 1))
> Next
> temp = Replace(temp, "", "oe")
> removeAccent = temp
> End Function
>
> You can create this function directly in sql server like this :
> CREATE FUNCTION replaceAccentChar (@.source as varchar(255))
> RETURNS varchar(255) AS
> BEGIN
> declare @.charList as varchar(20)
> declare @.temp as varchar(255)
> declare @.t as int
> set @.temp = @.source
> set @.charList = 'aeioucn'
> set @.t = 0
> while @.t <= len(@.charList)
> begin
> set @.temp = replace(@.temp, substring(@.charList, @.t, 1),
> substring(@.charList, @.t, 1))
> set @.t = @.t + 1
> end
> set @.temp = Replace(@.temp, '', 'oe')
> set @.temp = Replace(@.temp, '', '''')
> return @.temp
> END
> Daniel
>
> Andy Fish wrote:
using
lanaguages
General
accent-insensitive
accents
want)
string
>
>
>

removing accents and doing accent-insensitive processing

Hi,
I have an app that does case-insensitive, accent-insensitive searches using
sql server fulltext engine. It deals only with western-european lanaguages
(mostly just English and French). The tables are stored in Latin1 General
CP1.
However, I also need to process the search results in an accent-insensitive
way outside of SQL server (using C#).
The approach I took was to create a separate table using CP1253 (greek)
which has no accented characters, and fulltext index that table instead.
When I copy the data from CP1 to the CP1253, SQL Server removes the accents
so I get back the search results without accents on (which is what I want)
However, if the search criteria includes accented characters, I get no
results. The text engine does not strip the accents off the search string
even though the table I indexed is accent insensitive.
It seems that what I need to do is strip accents off the search strings
before submitting the text search. does anyone know of a way to do this?
Failing that, is there another solution that would get me round this
problem?
TIA
Andy
you have to run a series of replace statement in your search string to
remove them.
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:ueUnF8WIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have an app that does case-insensitive, accent-insensitive searches
> using sql server fulltext engine. It deals only with western-european
> lanaguages (mostly just English and French). The tables are stored in
> Latin1 General CP1.
> However, I also need to process the search results in an
> accent-insensitive way outside of SQL server (using C#).
> The approach I took was to create a separate table using CP1253 (greek)
> which has no accented characters, and fulltext index that table instead.
> When I copy the data from CP1 to the CP1253, SQL Server removes the
> accents so I get back the search results without accents on (which is what
> I want)
> However, if the search criteria includes accented characters, I get no
> results. The text engine does not strip the accents off the search string
> even though the table I indexed is accent insensitive.
> It seems that what I need to do is strip accents off the search strings
> before submitting the text search. does anyone know of a way to do this?
> Failing that, is there another solution that would get me round this
> problem?
> TIA
> Andy
>
|||Hi,
in ASP, you can use this function :
Public Function removeAccent(source)
avantConversion = "ǒ"
apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOoOoOuUuUuUcC'n"
temp = source
For boucle = 1 To Len(avantConversion)
temp = Replace(temp, Mid(avantConversion, boucle, 1),
Mid(apresConversion, boucle, 1))
Next
temp = Replace(temp, "", "oe")
removeAccent = temp
End Function
You can create this function directly in sql server like this :
CREATE FUNCTION replaceAccentChar (@.source as varchar(255))
RETURNS varchar(255) AS
BEGIN
declare @.charList as varchar(20)
declare @.temp as varchar(255)
declare @.t as int
set @.temp = @.source
set @.charList = 'aeioucn'
set @.t = 0
while @.t <= len(@.charList)
begin
set @.temp = replace(@.temp, substring(@.charList, @.t, 1),
substring(@.charList, @.t, 1))
set @.t = @.t + 1
end
set @.temp = Replace(@.temp, '', 'oe')
set @.temp = Replace(@.temp, '', '''')
return @.temp
END
Daniel
Andy Fish wrote:
> Hi,
> I have an app that does case-insensitive, accent-insensitive searches using
> sql server fulltext engine. It deals only with western-european lanaguages
> (mostly just English and French). The tables are stored in Latin1 General
> CP1.
> However, I also need to process the search results in an accent-insensitive
> way outside of SQL server (using C#).
> The approach I took was to create a separate table using CP1253 (greek)
> which has no accented characters, and fulltext index that table instead.
> When I copy the data from CP1 to the CP1253, SQL Server removes the accents
> so I get back the search results without accents on (which is what I want)
> However, if the search criteria includes accented characters, I get no
> results. The text engine does not strip the accents off the search string
> even though the table I indexed is accent insensitive.
> It seems that what I need to do is strip accents off the search strings
> before submitting the text search. does anyone know of a way to do this?
> Failing that, is there another solution that would get me round this
> problem?
> TIA
> Andy
>
|||A very nice bit of code! You forgot though
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Blais" <daniel@.danielblais.net> wrote in message
news:4229E151.3020703@.danielblais.net...[vbcol=seagreen]
> Hi,
> in ASP, you can use this function :
> Public Function removeAccent(source)
> avantConversion = "ǒ"
> apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOoOoOuUuUuUcC'n"
> temp = source
> For boucle = 1 To Len(avantConversion)
> temp = Replace(temp, Mid(avantConversion, boucle, 1),
> Mid(apresConversion, boucle, 1))
> Next
> temp = Replace(temp, "", "oe")
> removeAccent = temp
> End Function
>
> You can create this function directly in sql server like this :
> CREATE FUNCTION replaceAccentChar (@.source as varchar(255))
> RETURNS varchar(255) AS
> BEGIN
> declare @.charList as varchar(20)
> declare @.temp as varchar(255)
> declare @.t as int
> set @.temp = @.source
> set @.charList = 'aeioucn'
> set @.t = 0
> while @.t <= len(@.charList)
> begin
> set @.temp = replace(@.temp, substring(@.charList, @.t, 1),
> substring(@.charList, @.t, 1))
> set @.t = @.t + 1
> end
> set @.temp = Replace(@.temp, '', 'oe')
> set @.temp = Replace(@.temp, '', '''')
> return @.temp
> END
> Daniel
>
> Andy Fish wrote:
using[vbcol=seagreen]
lanaguages[vbcol=seagreen]
General[vbcol=seagreen]
accent-insensitive[vbcol=seagreen]
accents[vbcol=seagreen]
want)[vbcol=seagreen]
string
>
>
>