April 24, 2007 at 11:55 am
has anyone used the Unix2Dos utility? im trying to automate a process in my SQL statement that runs this utility to convert this flat file i received from an oracle server to a regulart txt flat file
April 25, 2007 at 10:53 am
If you have access to the Oracle Server, meaning credentials and at least SELECT rights, create a Linked Server to the Oracle database and you will be able to query those tables and even do your SELECT INTO from those tables.
Here is a script to create a Linked Server. You need to:
1) replace server_id with the name of the Linked Server to be.
2) replace database_name with the db name in Oracle
3) replace user_id and password with your credential for Oracle
4) replace TDOLEDB.1 with the corresponding provider. If you don't have any, try to find it online.
/****** Object: LinkedServer [ServerID] Script Date: 03/20/2007 08:22:28 ******/
IF
EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'server_id')
EXEC master.dbo.sp_dropserver @server=N'server_id', @droplogins='droplogins'
/****** Object: LinkedServer [ServerID] Script Date: 03/20/2007 08:21:12 ******/
EXEC
master.dbo.sp_addlinkedserver @server = N'server_id', @srvproduct=N'database_name', @provider=N'TDOLEDB.1', @datasrc=N'server_ip_address'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC
master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server_id',@useself=N'False',@locallogin=NULL,@rmtuser=N'user_id',@rmtpassword='password'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'data access', @optvalue=N'true'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'dist', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'pub', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'rpc', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'sub', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'collation name', @optvalue=null
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC
master.dbo.sp_serveroption @server=N'server_id', @optname=N'use remote collation', @optvalue=N'true'
April 26, 2007 at 9:44 am
"has anyone used the Unix2Dos utility? "
Yes.
Note that the fact that the file originates on a UNIX server is important but the fact that server is running Oracle is not important.
What is the problem ?
SQL = Scarcely Qualifies as a Language
April 26, 2007 at 9:56 am
The tool is installed on my server. i need to use this utility to convert the '|' delimited text files from unix to dos so that i can load them into my table because they are unix files, meaning that the row terminator is an oracle rowterminator, so when i try to load the data into my SQL table, it doesnt know what the rowterminator is. i need to use this tool to convert the file..
i just want to know how to run the tool in my sql statement...
April 26, 2007 at 10:33 am
"i just want to know how to run the tool in my sql statement"
Quote from Joe Celko:
Q: What is the best kind of rock to drive in wood screws.
A: Granite.
The point is that the question is wrong, "rocks" are the wrong kind of tools to drive in wood screws.
So the answer to your question is "do not use SQL to run programs".
If you have a SQL Server Job that will run, have a prerequisite step that runs the Unix2Dos.exe
Alternatively, in a DTS package, have an "EXECUTE TASK" step to run the Unix2Dos.exe
SQL = Scarcely Qualifies as a Language
April 26, 2007 at 10:51 am
ahhh
got it, thank you very much. ill try this
April 26, 2007 at 7:52 pm
You can easily read Unix flat files using BULK INSERT with a little help from Format File.
See "BULK INSERT", "Using Format Files" topics in BOL.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply