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 Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegroups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegroups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

Bulk Insert

Friends,
If I need to import a text file into a pre-existing table using T-SQL, then
BULK INSERT works fine. But I cannot figure out how to import that same tex
t
file, using T-Sql in QA, if no target table currently exists. Is there a wa
y
to do this?
To clarify, I can import the same text file through DTS, even though the Sql
Server db doesn't currently contain a table to import into - I tell DTS to
import the data into Table_X, and DTS takes care of creating the necessary
table, naming column headers, and importig the data. Can I write my own
Bulk Insert statement in QA that performs the same action?
Thanks in advance for your help ...
bill morganI think you will have to create the table upfront and then use BULK INSERT
from QA.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:7292E462-D6F5-47AD-82F8-F194A6A5FB8B@.microsoft.com...
> Friends,
> If I need to import a text file into a pre-existing table using T-SQL,
> then
> BULK INSERT works fine. But I cannot figure out how to import that same
> text
> file, using T-Sql in QA, if no target table currently exists. Is there a
> way
> to do this?
> To clarify, I can import the same text file through DTS, even though the
> Sql
> Server db doesn't currently contain a table to import into - I tell DTS to
> import the data into Table_X, and DTS takes care of creating the necessary
> table, naming column headers, and importig the data. Can I write my own
> Bulk Insert statement in QA that performs the same action?
> Thanks in advance for your help ...
> bill morgan|||Thanks for your input. I think yer right. Good to get that confirmation.
"SriSamp" wrote:

> I think you will have to create the table upfront and then use BULK INSERT
> from QA.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
> news:7292E462-D6F5-47AD-82F8-F194A6A5FB8B@.microsoft.com...
>
>