sp_addlinkedsrvlogin text file
-
I'm trying to use this to import the file temp.txt into SQL Server. SQL Server and c:\temp.txt are both on the same machine.
I get this error message when I "EXEC sp_tables_ex txtsrv"
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'c:\temp.txt' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\temp.txt',
NULL,
'Text'
GO
--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, CustServ, NULL
GO
--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO
--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]
sp_dropserver 'txtsrv', 'droplogins'
c:\temp.txt looks like this:
aaaaaaaaaa
bbbbbbbb
ccccccccc
dddddddddddddddddd
eeeeeeeeeeeee
ffffffffffffffffff
gggggggggggg
c:\schema.ini looks like this:
[temp.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname varchar Width 800
-
Why not use DTS or Bulk insert to do this ? Even if modifications are required after loading of the data, you can have a staging table to load this data into it and then do the transformations before it makes it's way to the destination table(s).
Using linked server will work fine too but in the case of large text files, you will run into performance issues.
-
I'm not very good at SQL, so DTS is out of the question. Well I've never figured out how to make a DTS stored procedure. DTS works differently right.
Actually I'm using bulk transfer now, but it does not load the file in order.
BULK INSERT
_RawData
FROM
'C:\_Work\ImportData\PreProcess\PreProc51.csv'
WITH
(FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n')
The stupid file PreProc51.csv has line feeds in the middle of rows. It is a mess. If I can get it into SQL Server in order, I can repair the issues.
-
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=14488
Looks like I'm not the only one who has a problem with this. Does anyone use sp_addlinkedsrvlogin? I'm wondering if it works at all, have tried just about everything.
-
I don't need the sp_addlinkedsrvlogin for this to work for me. Changes are highlighted:
c:\temp.txt
aaaaaaaaaa
bbbbbbbb
ccccccccc
dddddddddddddddddd
eeeeeeeeeeeee
ffffffffffffffffff
gggggggggggg
c:\schema.ini
[temp.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname Text
SQL Script
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\',
NULL,
'Text'
GO
--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[temp#txt]
go
sp_dropserver 'txtsrv', 'droplogins'
go
-
I'm still getting this message. Even ran it from the master database in care I didn't have permissions. I'm wondering if I need to set something up with 'Microsoft.Jet.OLEDB?
Error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
-
WOW ... hack boy here figured it out. When a linked server is created it shows in the security area.
Went to Properties, Security, "be made without security"
-
Hack boy is only good for one thing a year. I created a table, added two fields. One an identity called SEQ and another called COLUMNNAME.
In query analyzer ran:
INSERT INTO _TestIt
(rawdata)
SELECT
columnname
FROM txtsrv...[temp#txt]
Good news:
Even though it is slow (about 20 seconds to do 20000 records), it imported text data in the same sort order. Thats cool !!!
Bad news:
Tried to create a stored procedure with it and I get this error.
I also tried EXEC 'INSERT INTO _TestIt (rawdata) SELECTcolumnname FROM txtsrv...[temp#txt]' This gives me a syntex error.
-
EXEC ('INSERT INTO _TestIt (rawdata) SELECTcolumnname FROM txtsrv...[temp#txt]')
Hack figures another thing out, new problem now. All I needed was () and not this can be put in a stored procedure ... BUT
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Anyone know how I do this?
-
Here is my solution. Probably ugly but it works. I'm using OSQL and xp_cmdshell.
CREATE PROCEDURE
p_TryThis
AS
DECLARE @@DOSCommand varchar(150)
SET @@DOSCommand = 'c:\rci.bat'
TRUNCATE TABLE _TestIt
EXEC sp_dropserver 'txtsrv', 'droplogins'
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\',
NULL,
'Text'
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, 'Admin', NULL
EXEC master..xp_cmdshell @@DOSCommand
/*
RCI.BAT
OSQL -U******** -P******** -dRciBeta -Q"INSERT INTO _TestIt (rawdata) SELECT columnname FROM txtsrv...[temp#txt]"
*/
-
Why am I jumping through these hoops?
- I know how to manully use DTS but never figured out how to automate it.
- BULK INSERT does not move text data in the same sort as it resides in the text file
- This brings data in the same sort, so my table has an identify field
- The reason I need this in the same sort is the text file has line breaks in the middle of some of the transactions ... 5 different transaction types in the file
- so once I get the data in I have a routine in my mind that I can identify good and bad transactions, then repair the bad transactions
- I'm doing this as a favor for a freind, the vendor sending her this data should be hung
-
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE A
as
...
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply