BCP And Bulk Insert To Linked Servers

Jul 23, 2005

BCP and Bulk Insert to Linked Servers


Hi guys!

Heres my set up:
1) Im using Win2003 with MS SQL 2000
2) I have a linked server in SQL Server pointing to an MS Access DB.

Why MS Access? Gee, I dont know. The guy who owns it refused to update his
VB app and point it to SQL Server.

Anyway, I have 190,000 records in SQL Server that I wanted to dump and
insert it to MS Access.
I tried to use OPENQUERY but OLE DB provider choked and wont be able to
handle that much records. Sucks!
Moreover, DTS packages wont do the job. I tried it and it have the same
problem.
Now, I got one last option to go to. I EXPORTED SQL Server data to a text
file using BCP but my problem is how to IMPORT those data from the TEXT
file to my Linked Server that points to an MS Access.

This is what Im trying to do:

SQL Server Data ---> Text file ---> Linked Server (MS Access)


bcp LinkedServerName..MSAccess_TableName in Shares1_tmp.txt -c -T -t ',' -r
'
'

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).

Thank you and you guys have a nice day.

--
Message posted via http://www.sqlmonster.com

View 5 Replies


ADVERTISEMENT

Why Does Bulk Insert Task Not Work From Remote Servers With Windows Authentication?

Jun 10, 2007

Using Bulk Insert Task extensively in our solution. Everything was working great till we deployed it in stage columns. The database server is different from application servers. We have ASP.NET web services driving SSIS packages on application server. After struggling thru several security issues to get this working (ended up creating an application pool with a domain account) we are now stuck with this problem. On a different note still don't understand what specific security permission is available to domain account that makes it work.

Read in some blog that SQL Server 2005 SP2 Beta had this (Bulk Insert) fixed but not in final production version. Is there a specific reason why this is so?

SSIS and the API is quite easy to work with but associated security and deployment issues are not always clear. A lot of answers seem to be coming from end users - thanks a lot to all for sharing your experiences - sadly not presented clearly in SSIS documentation.

View 2 Replies View Related

Problem With Linked Servers And INSERT

May 6, 2004

Hello

I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.

Here's the code:

---------------------

create table #b (type int,label varchar(100),x int,y int,so int)

declare @arg nvarchar(100)
set @arg = 'BLANKET.MaalGrupper.ID38'

declare @tsql varchar(1000)
select @tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( ''''' + @arg + ''''' )'' )'
insert #b exec (@tsql)

drop table #b

-------------------------

I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.

Can anybody help with this very irritating problem?

Thanks
MNJ

View 6 Replies View Related

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

Apr 18, 2008



Hello,

I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.

Thanks.

View 8 Replies View Related

Bulk Insert Using Script And Not Bulk Insert Task

Nov 2, 2007



Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.

View 6 Replies View Related

Bulk Insert - Bulk Load Data Conversion Error

Jan 17, 2008

Im having some issues with bulk insert.

This is the table:

CREATE TABLE [dbo].[tmp_GA_status](

[GA_recno] [int] NOT NULL,

[GA_desc] [varchar](40) NULL

)


This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"


and this is the sql:

bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'

with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')



so yeah, pretty simple. But whatever I do I get this;

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).



So what am I doing wrong ?

View 13 Replies View Related

I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?

Sep 27, 2007

I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.

Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?

Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!

View 1 Replies View Related

Cannot Fetch A Row From OLE DB Provider BULK With Bulk Insert Task

Nov 23, 2005

Hi, folks:

View 18 Replies View Related

Pros: How To Bulk Delete And Bulk Insert?

Oct 11, 2000

I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)

Updates and cursors just seem to be too slow.

So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.

This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert


Any comments are appreciated,

-Marc

View 3 Replies View Related

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

Apr 8, 2008


I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:


Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".

Task failed: Bulk Insert Task

In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');


What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}

Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul

View 1 Replies View Related

How To Bulk Deploy Reports To Multiple Servers?

Mar 23, 2007

Hi,

I'm just wondering if there is any easy way to deploy reports to multiple servers. My current issue is that I need to deploy tens of reports onto tens of production server. Does anyone know how to do that?

Thanks a lot.

Cheers,

View 1 Replies View Related

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')

[code]...

View 5 Replies View Related

Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'

Feb 1, 2007

Hi~,

Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.

- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility

- SQL Server's resource usage : when running memory based bulk copy, server resource's influence

- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?

- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit

- any other guide lines

View 1 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007



Hi,
i have a file which consists data as below,

3
123||
456||
789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?

Thanks,
-Badri

View 5 Replies View Related

Linked Servers

Aug 17, 2006

Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?

View 1 Replies View Related

What Are Linked Servers And What R Its Uses

Apr 5, 2008

can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
 any help on this would be highly appreciated.

View 3 Replies View Related

Linked Servers

Jun 10, 2004

Hello All,

I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?

Thanks

View 2 Replies View Related

LINKED SERVERS

Jan 22, 2002

I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.

Can anyone help

View 1 Replies View Related

Linked Servers.....

May 10, 2001

Hi All,
I have successfully linked a server and had SQL queries running
OK for a few weeks, but today I get the following message....

"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time."
(Server: Msg 7353, Level 16, State 1, Line 1)

I've dropped the link and recreated it but I still get the same error
message. Can anyone help???

Thanks
David.

View 2 Replies View Related

Linked Servers In DTS...

Jun 25, 2001

Hi,

I'm using SQL Server 7.0 SP1.

I have a DTS package that requires me to link 2 servers
and query both in order to get my set of records that I
want to Transform. My query runs fine in QA. I copy and
paste it into the SQL Query window of the transformation.
I click Preview and all that happens is a quick flicker of
the DTS window. When I go to Transformations, there are
no source columns.

Here is a copy of my code that I'm trying to use:
-------------------------------------------------------
declare@start_dteas datetime,
@end_dteas datetime

SELECT@start_dte = date_data_range_start_dte,
@END_DTE = date_data_range_END_dte
FROMSIDDEV.SID_DEVELOPMENT.DBO.T901_RUN
WHERERUN_ID = (selectmax(run_id)
from
SIDDEV.SID_DEVELOPMENT.DBO.t901_run
whererun_type_cd = 'M')


SELECTRUN_ID
INTO#RUNS
FROMCRMDEV02.MDCORE.DBO.T901_RUN
WHEREDATE_DATA_RANGE_START_DTE >= @START_DTE
ANDDATE_DATA_RANGE_END_DTE <= @END_DTE

SELECTT70.*
FROM
CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A
NGT AS T70 JOIN #RUNS
ONT70.RUN_ID = #RUNS.RUN_ID

DROP TABLE #RUNS
------------------------------------------------------
Is there an issue with Linked Servers and DTS?
Any help would be greatly appreciated.

Thanks in advance,
Darrin

View 1 Replies View Related

LINKED SERVERS

Aug 7, 2001

FOLKS
I GET THE FOLLOWING ERROR WHEN I RUN A SELECT FROM THE LINKED SERVER
THE LINKSERVER EXISTS AND THERE IS A RECORD IN SYSSERVERS TOO.

Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'

I AM NOT SURE WHERE TO GO FROM HERE.

REGARDS

GIRISH

View 1 Replies View Related

Linked Servers

Aug 10, 2001

Hi Gurus,
I am looking for literature to find out Pros and Cons of Using DTS Verses
Linked Servers in SQL 7.0.

My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.

Can somebody suggest where to look for.

Thanks
sekhar

View 1 Replies View Related

Linked Servers

Sep 18, 2001

When I create linked server using integrated security and <they will be impersonated> option I get this:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user ''
MS Technet says that SQL Server 7 doesn't allow double hops and to use mappings to standard security login to work around. @#%%~~@@#@#&^%@#
Impersonating to version 6.5 works fine.
Is there any way to link servers using ONLY integrated security?
Any help would be much appreciated.
Thanks

View 1 Replies View Related

Linked Servers

Jul 18, 2000

I have setup a linked Informix server in SQL7.0 and I am trying to create a
simple View with the following SQL statement

SELECT doc_code
FROM FOURSITE.foursite.informix.watdoc
WHERE (order_no = [PV01963B ])


I am getting the following error:

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or
catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but
the provider does not expose the necessary interfaces to use a catalog
and/or schema.
---------------------------
OK Help
---------------------------

Can any one tell me how to correct this problem?

Thanks

Peter
pczurak@bigfoot.com

View 5 Replies View Related

Linked Servers

Apr 17, 2000

Is it possible to add a 6.5 database as a linked server to a 7.0 database and query from it ?

View 6 Replies View Related

About Linked Servers

Nov 3, 2000

Hi.
I'm working with SQL Server 7.0 and I've been reading about the linked server option. My question is, if it's possible to link a server with a SQLBase database 7.01.

I have download some drivers to my computer, the odbc driver for sqlbase and the sqlbase ole db driver as well, but when I try to add a new linked server in the enterprise, in the provider name option I can not see any provider name addressing to Centura or Sql base driver.

Has anybody an idea how to make this connection then? or the right way for doing it??

Thanks for any help can be offered.
Regards, Elvia.
:)

View 2 Replies View Related

LINKED SERVERS

Feb 10, 2000

I have two 7.0 servers and I want to run a distributed query.

I did sp_addlinkedserver to link server B to server A
and I did sp_addlinkedsrvlogin for a specific login.
After adding login when I try to access a table on server B it
says login failed

All the logins are NT authenticated (in both the servers, So
both servers have same logins.)



but it is not working.

View 1 Replies View Related

Linked Servers

Sep 1, 2000

Hi all
I try to configure Linked Servers from the query analyze , and have some troubles.
My steps are :
1. from the enterprise mennenger , add 2 new servers : srv1 , srv2 (both SQL7)
2. from the query analyzer of srv1.master: "sp_addlinkedserver 'srv2'"
3. from the query analyzer of srv1.master:
"sp_addlinkedsrvlogin 'srv2',false,null,'sa','myPass'"
4. select * from srv2.pubs.srv2Table
5. I get the next error :"error 6 : Specified SQL srever not found."

What do I do wrong ?
Eyal

View 2 Replies View Related

Linked Servers

Apr 19, 2000

Anyone out there use linked servers? I just tried this SQL7 feature and am very impressed. Are there any pitfalls to watch out for? Comments?

Thanks in advance.

Steve

View 1 Replies View Related

Linked Servers

Feb 14, 2002

I have created link servers a few time within SQL2000 and pulled data from Oracle. How would an Oracle Server or Application pull / See from SQL?

Anyone have any experience?

Thanks,
David

View 1 Replies View Related

Linked Servers

May 11, 2002

How to determine by means of T-SQL
if a linked server is available(for processing with its tables)?

View 1 Replies View Related

Linked Servers

Jun 26, 2002

This is my problem I have a Server A and Server B. There is a master table in Table A. I link Server A and Server B. When I look in Under Linked Server tables of B I can only C tables in Server A not the Server B 's Tables. Dpn't know what is happening. Could SOmebody help

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved