Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unanswered: 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



    Thanx

  2. #2
    Join Date
    Nov 2003
    Posts
    3

    Bulk Insert

    Amit,
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    8
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    3

    Linked Server

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •