bulk insert into a linked server fails

Hello,

I am trying to perform bulk insert operation on a linked server.

Here is the T-sql code for the same:

Declare @.dynamic_sql nvarchar(1000)
Declare @.file_name varchar(100)

set @.file_name = 'C:\mvam\calls\11182003.txt'

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE]...cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

execute sp_executesql @.dynamic_sql

On executing the above posted code I get the following error

Invalid object name 'MUMBAI\AMIT_DATABASE...cdr_repositroy'

Any suggestions would be helpful

ThanxAmit,
Unless you specifically configured your linked server to point to the appropriate database it defaults to the master DB.

To remove ambiguity you need to specify the full 4 part name
e.g

insert [DBDEVSERVER\DEVELOP].pubs.dbo.test
select 'a'

For your bulk insert specify the database name

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE].databasename.ownername.cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

let us know if you are still having problem|||I tried with the option you mentioned in your reply i.e. using the complete naming convention but of no use. I still get the same error.

Also, I checked if the Database is correct on linked server and its correct.

Anyways, thanx for the help.

Amit|||Does the link work with a select statement to the linked table

ie
Select * from servername.databasename.ownername.objectname.

BULK INSERT - FIELDTERMINATOR

Hi, I need to bulk insert a .csv file into a database table. However, the problem I am encounter now is the in one of my field which is nvarchar(50) ... the data in .csv has ',' - comma ... which my FIELDTERMINATOR in BULK INSERT is also comma. It gives me error because the next field is an integer field. How do I solve this problem? I hereby give few data example as follow:

In .csv file:

============

"J2825JA","FEB22,MAR1,8/05 - RE","22FEB05",45,20000

"J2825JB","FEB22,MAR1,8/05 - RE","22FEB06",765,435653

Query

=========

SQL = "BULK INSERT [" & sTableName & "]" & _
" FROM '" & sNewFile & "'" & _
" WITH ( FIELDTERMINATOR = ',', " & _
" FIRSTROW = 1, " & _
" ROWTERMINATOR = '\n' ) "

BTW, this is written in ASP. But I this is DB problem. So I posted here. Thanks in advance.

Personally I never use comma-delimited files because of this sort of thing; there is nothing wrong with the format per se, but it seems like many software programs do not know how to handle it correctly. Is there any way you can have the file supplied in tab-delimited format? SQL Server will handle this for you a lot more gracefully. It will also fixed-width format readily.

If this is not a possibility, Access should be able to import the file as-is. FoxPro will also handle this format. And then you can export it in a SQL Server-friendly format.