Need some assistance linking an Oracle database

I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.

The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.

Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]

This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.Made a few changes and now getting the following error.

"Error 7399: OLE DB provider 'MSDAORA' reported an error"

I get this error when trying to view the list of tables under the linked server in enterprise manager. When i run a basic query through query analyzer, i get this expanded error message.

"OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name"

Using Oracle ODBC Test utility in the Oracle client I am able to connect to the database and run queries.|||The most common problem in using Linked Server and other distributed services is SQL Server Agent did not have Network permissions which means your SQL Server is installed with the Microsoft provided local systems account, I maybe wrong but I think in 7.0 you may have to reinstall SQL Server with a Service account. You may not find the info about service account in SQL Server 7.0 BOL(books Online) because it took a while to get Microsoft to require the Service account. Hope This helps.

Kind regards,
Gift Peddie|||The error I would get is the following. "OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name".

I haven't been able to find a good step by step guide to follow to link to an Oracle database. Trying to reinstall the server might not be an option. If I could find something that did say it was the problem you mentioned, then it would probably be worth it.

Can anyone shed some light on this problem. Or managed to link to an oracle databse from SQL?|||Try these two urls, the first has most known SQL Server Errors and the solutions complined over the years by Neil Pikes and the second is one of the best Oracle info site on the Web including Oracle BOL(Books online). Hope this helps.
http://www.mssqlserver.com/faq
http://www.dbasupport.com

kind regards,
Gift Peddie

Need some assistance

Hi,
I am importing some phone numbers from a lagacy system and I find some of
them having the following exeptions:
1. 2122121222
2. 212 212 2122
3. 212/212 2122
4. 212/2122122
5. 212.212 2122
6. 212.2122122
7. 212.212.2122
8. 212-212-2122
9. (212) 212-2122
10. 212-2122122
11. (212)-212-2122
12. TEXT
13. 212 2122122
14. 212 -212-2122
15. 212 212 21222
16. 212) 212-2122
17. 2122122122#212
18. 1212 212 2122
How can I format them to (212) 212-2122? I was thinking a function but can
someone assist?
ThanksHi Chris
The code I have supplied below works in a controlled test environment and
may work for you BUT use with extreme caution.
Test the code by using selects before trying the inserts to verify that you
are getting the results required.
You may also like to backup your table before attempting this.
---
drop table #temp
create table #temp(pnumber varchar(50))
insert into #temp values('2122121222')
insert into #temp values('212 212 2122')
insert into #temp values('212/212 2122')
insert into #temp values('212/2122122')
insert into #temp values('212.212 2122')
insert into #temp values('212.2122122')
insert into #temp values('212.212.2122')
insert into #temp values('212-212-2122')
insert into #temp values('(212) 212-2122')
insert into #temp values('212-2122122')
insert into #temp values('(212)-212-2122')
insert into #temp values('TEXT')
insert into #temp values('212 2122122')
insert into #temp values('212 -212-2122')
insert into #temp values('212 212 21222')
insert into #temp values('212) 212-2122')
insert into #temp values('17. 2122122122#212')
insert into #temp values('1212 212 2122')
-- remove know extraneous characters
update #temp
set pnumber = replace(pnumber, ' ', '')
update #temp
set pnumber = replace(pnumber, '.', '')
update #temp
set pnumber = replace(pnumber, '/', '')
update #temp
set pnumber = replace(pnumber, '-', '')
update #temp
set pnumber = replace(pnumber, '(', '')
update #temp
set pnumber = replace(pnumber, ')', '')
update #temp
set pnumber = replace(pnumber, '#', '')
-- set to null where number length is not 10 --
update #temp
set pnumber = null where len(pnumber) <> 10
-- add braces and dash
update #temp
set pnumber = '(' + pnumber
update #temp
set pnumber = left(pnumber, 4) + ') ' + right(pnumber, 7)
update #temp
set pnumber = left(pnumber, 9) + '-' + right(pnumber, 4)
select * from #temp
---
Regards
Peter Hamilton
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||What are you using to import them? Is this in a table? Or a spreadsheet?
Is this a one time thing. Why are all of your phone numbers the same
number! (kidding about the last one :)
If you want to do it in sql (the algorithm would be the same anywhere) just
strip out all of the characters you don't want, leaving you with only
numbers. Then simply put it back together. I would suggest you might want
to put the data in three columns, but certainly a check constraint is
needed. You ought ot get a list of valid area codes to validate against as
swell, if this data is important to you (if it is I would have expected it
would be a bit better taken care of, but you never know.
Note that I just toss off the (likely) extension information, and just using
the 10 characters.
drop table test
go
create table test
(
phoneNumber varchar(15)
)
insert into test
select '2122121222'
union all
select '212 212 2122'
union all
select '212/212 2122'
union all
select '212/2122122'
union all
select '212.212 2122'
union all
select '212.2122122'
union all
select '212.212.2122'
union all
select '212-212-2122'
union all
select '(212) 212-2122'
union all
select '212-2122122'
union all
select '(212)-212-2122'
union all
select 'TEXT'
union all
select '212 2122122'
union all
select '212 -212-2122'
union all
select '212 212 21222'
union all
select '212) 212-2122'
union all
select '2122122122#212'
union all
select '1212 212 2122'
select case when cleaned like '%[a-z]%' then 'INVALID DATA'
when cleaned like '1%' then '(' + substring(cleaned,2,3) + ')' +
substring(cleaned,5,3) + '-' + substring(cleaned,8,4)
else '(' + substring(cleaned,1,3) + ')' + substring(cleaned,4,3) + '-' +
substring(cleaned,7,4) end
--this bit strips out the offensive characters and spaces
from (select
replace(replace(replace(replace(replace(
replace(replace(phoneNumber,'
',''),'(',''),')',''),'-',''),'.',''),'/',''),'#','') as cleaned
from test) as numbers
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||Probably better to validate this data externally and/or prior to acceptance
into the database. But here's one brute force method.
CREATE TABLE #t
(
phone VARCHAR(32)
);
SET NOCOUNT ON;
INSERT #t SELECT '2122121222';
INSERT #t SELECT '212 212 2122';
INSERT #t SELECT '212/212 2122';
INSERT #t SELECT '212/2122122';
INSERT #t SELECT '212.212 2122';
INSERT #t SELECT '212.2122122';
INSERT #t SELECT '212.212.2122';
INSERT #t SELECT '212-212-2122';
INSERT #t SELECT '(212) 212-2122';
INSERT #t SELECT '212-2122122';
INSERT #t SELECT '(212)-212-2122';
INSERT #t SELECT 'TEXT';
INSERT #t SELECT '212 2122122';
INSERT #t SELECT '212 -212-2122';
INSERT #t SELECT '212 212 21222';
INSERT #t SELECT '212) 212-2122';
INSERT #t SELECT '2122122122#212';
INSERT #t SELECT '1212 212 2122';
UPDATE #t
SET phone =
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
phone,
-- you may want to add more characters here
' ', ''),
'(', ''),
')', ''),
'#', ''),
'.', ''),
'/', ''),
'-', '')
));
UPDATE #t
SET phone = '('+STUFF(STUFF(phone,4,0,') '),9,0,'-')
WHERE
-- make sure phone has 10 numerical digits
-- you can make this more precise, e.g.
-- disallow 0 in the first position
phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
SELECT phone FROM #t ORDER BY phone;
DROP TABLE #t;
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||For short term, create a scalar UDF like:
CREATE FUNCTION dbo.f ( @.s VARCHAR( 20 ) )
RETURNS VARCHAR( 20 ) AS BEGIN
WHILE PATINDEX( '%[^0-9]%', @.s ) > 0
SET @.s = REPLACE( @.s, SUBSTRING( @.s, PATINDEX( '%[^0-9]%', @.s ), 1 ),
'' )
RETURN CASE WHEN LEN( @.s ) = 10
THEN '(' + STUFF( STUFF( @.s, 4, 0, ') ' ), 9, 0, '-' )
ELSE 'BAD DATA'
END
END
Now try:
SELECT phone_col, dbo.f( phone_col )
FROM tbl ;
Anith|||I am using DTS to import. Thanks for the assistance. I'll try to create a
function that I can add exceptions to in future. I'll be importng this data
on a daily basis from a legacy system which has to validation in the fronten
d
so the users practically enters anything.
"Chris" wrote:

> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks