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
ADVERTISEMENT
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
Sep 27, 2004
Hi,
Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.
column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int
pId has been set to primary key with auto_increment.
My csv file has 2 columns of data and it looks like follows:
program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"
Now i use BULK INSERT like this
"BULK INSERT ord_programs FROM 'C:datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='', FIRSTROW=2)"
to import data into my table in SQL server and it gives me this error
"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"
I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...
Please help me out guys and please post a snippet of code if you have.
Thank You.
View 2 Replies
View Related
Jul 13, 2007
hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι?„?‰Î»Î¿Î±ÎºÎ±??ν16/11/1909
2 102Α??γολίδο?‚29/04/1949
3 103Α??καδία?‚16/11/1909
4 104Î ???„η?‚ 16/11/1909
5 105Α?„?„ική?‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile
DATAFILETYPE = 'char'
FIELDTERMINATOR=' '
ROWTERMINATOR='
'
KEEPNULLS
)
As a result of the above configuration 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 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
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)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
View 4 Replies
View Related
Jul 13, 2007
Hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι?„?‰Î»Î¿Î±ÎºÎ±??ν16/11/1909
2 102Α??γολίδο?‚29/04/1949
3 103Α??καδία?‚16/11/1909
4 104Î ???„η?‚ 16/11/1909
5 105Α?„?„ική?‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile,
DATAFILETYPE = 'char',
FIELDTERMINATOR=' ',
ROWTERMINATOR='
',
KEEPNULLS
)
As a result of the above configuration 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 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
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)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
View 5 Replies
View Related
May 15, 2014
Is there a switch I can use to force a bulk insert and if data is truncated, I'm good with that. The truncated data, in this case, is not data I can use anyway if it is long enough to be truncated.
I need to keep the field at VARCHAR(23) and if I expand it, I won't be able to join on it after the file load completes. I'd like the data to be inserted (truncated if need be) and then I'll deal with the records that are truncated after I load the file.
View 5 Replies
View Related
Oct 1, 2007
Msg 4862, Level 16, State 1, Server PATH-SQLDEV, Line 2
Cannot bulk load because the file "c:DATABATCHBCPFormat.fmt" could not be read. Operating system error code (null).
Above is the error I get. The problem is I do not know what is causing this error. It occurs when I attempt to use SQLCMD with bulk insert.
I am using SQLserver 2005 and I have a similar set up in a test database that works, why this format file does not is beyond me, but my experience is when the format file has an error in it, such as a mispelled datatype or a incorrect column number, the error will zero in on that, rather then declare the entire file unreadable. Furthermore if I go into the file, change something so where it is incorrect (like a column number) it will zero in on that error. So I know that the format file can be read. If I knew what this error was all about I would at least know where to begin in fixing it. I have also tried using a very small sample file for the data being inserted. Same error.
Please help
View 2 Replies
View Related
Dec 4, 2006
hi
"Bulk insert data conversion error (truncation) for row 1, column 1 (id)."
when you get the error above or similar in sql server 2000 does it continue inserting the data by truncating it or does it stop beacause looking at the data that i have got it seems to continue inserting the data but just truncates the colunm. i have tried it several time its seeems to be consistent.
I have data that has white spaces after the actual data e.g. '00093 ' hence i am happy aslong as i can be sure that it does always continue as i will be loading alot of data using a similar process.
hence my question is that will it load all the data all the time and just truncate it to fit the column size?
View 7 Replies
View Related
Nov 9, 2014
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
View 0 Replies
View Related
Aug 7, 2006
hi, i having a problem in bulk insert , which is regard the text file that
to insert into database, when insertion processing,
if my textfile have NULL value, it give me Bulk insert data conversion error
for example in my text file c:mytest.txt , it contains data NULL
123 studentname NULL
can we let bulk insert detect NULL value ?
i have try on putting "KEEPNULLS" , but it doesn't help , caused some fields in table may in datetime type
BULK INSERT [mytable] FROM c:mytest.txt WITH (FIELDTERMINATOR = '' '', ROWTERMINATOR = '''', KEEPNULLS )'
thank you
View 4 Replies
View Related
Apr 11, 2007
My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.
bcp <database name>.dbo.<table name> format nul -T -S <server name> -n -f out.fmt
The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".
Progress: The Bulk Insert task is completed. - 100 percent complete
Task Bulk Insert Task failed
Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.
View 7 Replies
View Related
Mar 27, 2007
Hi all,
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!
View 1 Replies
View Related
Mar 27, 2007
Below is an overview of my problem:
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
View 1 Replies
View Related
Jun 5, 2015
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
col1 [varchar](10) NOT NULL,
col2 [datetime] NOT NULL,
col3 [date] NOT NULL,
col4 [varchar](6) NOT NULL,
col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
1 SQLCHAR 0 10 "@|@" 3 Col3 ""
2 SQLCHAR 0 6 "@|@" 4 Col4 Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
Nothing works ....
View 7 Replies
View Related
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
Dec 5, 2006
With "bcp MyDatabase.dbo.MyTable out C:MyFile.Dat -n -T" command line, I could get an exported data file. And I can also import this file into MyTable using 'BULK INSERT MyDatabase.dbo.MyTable FROM 'C:MyFile.dat' WITH (DATAFILETYPE='native');' query statement.
Now, I want to make my own data file just like made by bcp above. Although I could make file of 'char' type, 'native' type file is needed for performance and other reasons. And the format file should not be used.
Any one help?
View 5 Replies
View Related
Mar 2, 2004
Hello dbforums,
I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?
The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.
ALTER TRIGGER DICastRaw1hrInsertGuid
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))
More Questions:
- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...
ALTER TRIGGER DICastRaw1hrInsertDate
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT
AS
DECLARE @ID as integer
SELECT @ID = i.recordid from inserted i
UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE recordid = @ID
- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?
Thanks,
Chris
View 6 Replies
View Related
Apr 3, 2015
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
View 1 Replies
View Related
Jan 23, 2007
I am facing a issue with bulk upload on Test Server.
Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error.
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\ServerNameinputFileName.csv" could not be opened. Operating system error code 5(Access is denied.).
For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine.
If the command is run from Test Server connecting to any SQL Server instance including Test Server it is running fine.
Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server
2) Given the read/write rights on shared folder to everyone.
Query:
SELECT DISTINCT * FROM OPENROWSET
(
BULK '\ServerNameinputFileName.csv',
FORMATFILE='\ServerNameFormat.xml'
)
AS FileList
Please provide me with some solution. What can be the reason for such behaviour?
View 22 Replies
View Related
Oct 11, 2007
Using BCP or BULK INSERT you can specify an Error File (-e and ERRORFILE). However this does not seem to be exposed in SSIS via the Bulk Insert Task.
Does anyone know if I'm missing something and the Property is called something else or if can be accessed via script?
Cheers,
-Ryan
View 1 Replies
View Related
Oct 25, 2006
Hello
I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error
"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\ENDUSER-SQLEnduserTextB1020063.txt" does not exist."
The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.
Thanks
Chris
View 12 Replies
View Related
Apr 22, 2004
Is that possible to load files (*.bmp, *.jpg etc) to table (field type IMAGE) using BULK INSERT?
Or is it better to do it otherwise?
Thanks
View 5 Replies
View Related
Mar 18, 2008
I am having a problem using the Bulk Insert task. I am getting the msg:
SSIS package "Package.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "You do not have permission to use the bulk load statement.".
Task failed: Bulk Insert Task
SSIS package "Package.dtsx" finished: Success.
I have been granted ownership of the database. I also tried in one of my old databases that I just finished
developing and I got the same msg.
The file I am importing is comma delimited. I am importing it into a table that has 50 bytes allocation for each field (the max input field size is 40 bytes).
The connection is solid;
Format = “Specify?
RowDelimiter = {CR}{LF}
columnDelimiter = Comma {,}
No other options are set.
The data looks like:
"tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000",
Any ideas why I am getting this message?
View 4 Replies
View Related
Mar 13, 2006
BCP thru xp_cmdshell from stored procedure:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE
EXEC xp_cmdshell 'bcp database.dbo.table in c:scheduled.csv -S SERVERSQLEXPRESS -T -t, -r -c -e "error.txt"';
This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.
=================================================================
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
=================================================================
Error message when using BULK INSERT as follows:
BULK INSERT database.dbo.table from 'c:unscheduled.csv' with
(FIELDTERMINATOR = ',', ERRORFILE = 'c:error.txt');
Returns the following error message:
=================================================================
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt" could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).
=================================================================
The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.
EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2
View 1 Replies
View Related
Apr 8, 2001
I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0.
I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done.
Any suggestions ?
Regards,
Stuart.
View 1 Replies
View Related
Apr 4, 2008
Hello, I am doing a bulk insert using a XML Format file from a csv file. Most everything works just fine, but my delimiter is a , the problem is one of the column sets sometimes contains a , within "" like this:
value1,"value,2",value3
So when I do my insert it is distorting the column values because unlike excel it is not ignoring the comma within the quotes. Is there any way to set an attribute within the format file to prevent this from happening?
View 2 Replies
View Related
Jan 17, 2008
I Have a fixed width file where the format look like below:
f1 - 16
f2 - 64
f3 - 64..
....etc
and the format file that i created looks like:
8.0
20
1 SQLCHAR 0 16 "" 0 ExtraField
2 SQLCHAR 0 64 "" 0 ExtraField
3 SQLCHAR 0 64 "" 0 ExtraField
4 SQLCHAR 0 16 "" 1 DatabaseName
5 SQLCHAR 0 128 "" 0 ExtraField
6 SQLCHAR 0 24 "" 2 DelivaryDay
7 SQLCHAR 0 4 "" 0 ExtraField
8 SQLCHAR 0 3 "" 0 ExtraField
9 SQLCHAR 0 24 "" 0 ExtraField
10 SQLCHAR 0 3 "" 0 ExtraField
11 SQLCHAR 0 24 "" 0 ExtraField
12 SQLCHAR 0 64 "" 0 ExtraField
13 SQLCHAR 0 24 "" 0 ExtraField
14 SQLCHAR 0 24 "" 0 ExtraField
15 SQLCHAR 0 24 "" 3 CompleteDate
16 SQLCHAR 0 24 "" 0 ExtraField
17 SQLCHAR 0 24 "" 0 ExtraField
18 SQLCHAR 0 24 "" 0 ExtraField
19 SQLCHAR 0 24 "" 0 ExtraField
20 SQLCHAR 0 256 "" 0 ExtraField
I need to take only three coulmns from the file and the text file won't contain any delimeters between the fields.
I tried to execute this using Bulk..Insert and i am getting the error:
Cannot bulk load. Invalid column number in the format file "C:sampleXXXX.fmt"
Any one can help me what is the problem here?
I am using SQL Server 2000.
View 1 Replies
View Related
Aug 24, 2007
Hi ,
I was wondering if there was a way in a format file to load a host file data field to more than one column in a table?
Thanks
View 1 Replies
View Related
May 21, 2008
Hi,
im attempting the following bulk insert statement:
BULK INSERT gtaRatesTemp FROM 'D: estData.CSV' WITH (FIRSTROW = 3, FORMATFILE = 'D: estFormat.Fmt'
testData.CSV example:
"Country","Country Code","City","City Code","Currency","Item","Item Code","Address 1","Address 2","Address 3","Address 4","Telephone","Rating","Location 1","Location 2","Location 3","No. of","Dates","Dates","Days","Days","Min","Min","Basis","Twin","Single","Triple","Quad","Twin for sole use","Child","Child Age","Child Age","Meals included","Distance to City Centre (Kms)","Child Details","Special Conditions"
"","","","","","","","","","","","","","","","","Rooms","From","To","From","To","Pax","Nights","","Price","Price","Price","Price","Price","Price","From","To","","",""
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","14 Jun 2007","30 Jun 2007","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Jul 2007","31 Oct 2007","Mon","Sun","1","2","Room","231.00","231.00","","","231.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Nov 2007","31 Mar 2008","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","14 Jun 2007","30 Jun 2007","Mon","Sun","1","1","Room","412.50","412.50","","","412.50","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","01 Jul 2007","30 Nov 2007","Mon","Sun","1","1","Room","463.00","463.00","","","463.00","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
testFormat.Fmt:
8.0
36
1 SQLCHAR 0 50 """ 0 country SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "","" 1 countryCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 200 "","" 2 city SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "","" 3 cityCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "","" 4 currency SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 500 "","" 5 item SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "","" 6 itemCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 400 "","" 7 address1 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 400 "","" 8 address2 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 400 "","" 9 address3 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 400 "","" 10 address4 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "","" 11 telephone SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "","" 12 rating SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "","" 13 location1 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "","" 14 location2 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "","" 15 location3 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "","" 16 noOfRooms SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "","" 17 datesFrom SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 100 "","" 18 datesto SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 50 "","" 19 daysFrom SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "","" 20 daysTo SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 100 "","" 21 minPAX SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 100 "","" 22 minNights SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 50 "","" 23 basis SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 100 "","" 24 twinPrice SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 100 "","" 25 singlePrice SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 100 "","" 26 triplePrice SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 100 "","" 27 quadPrice SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 100 "","" 28 twinForSoleUsePrice SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 100 "","" 29 childPrice SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 100 "","" 30 childAgeFrom SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 100 "","" 31 childAgeTo SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "","" 32 mealsIncluded SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 50 "","" 33 distanceToCityCentre SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 500 "","" 34 childDetails SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1500 ""
" 35 specialConditions SQL_Latin1_General_CP1_CI_AS
My problem is that once inserted, column 35(childDetails) and 36(specialConditions) are being merged into one. I think it may be an error with one of the values i have for the terminator in the format file but im unsure what exactly.
Does anyone have any ideas?
View 9 Replies
View Related
May 16, 2012
I need fmt(format ) file for below values
“Stuid”,”Stuname”,”Class”,”DOJ”,”English”,”Math”,”Science”
"S1","Ram","10/31/2011,Monday",40,32,50
"S2","Bala","10/31/2011,Monday",50,45,69
"S3","Sam","10/31/2011,Monday",74,78,79
"S4","Jon","10/31/2011,Monday",65,58,89
"S5","Jos","10/31/2011,Monday",41,25,69
"S6","Jim","10/31/2011,Monday",74,41,41
"S7","Jack","10/31/2011,Monday",98,57,47
"S8","Sate","10/31/2011,Monday",87,73,45
"S9","Brb","10/31/2011,Monday",47,89,65
"S10","Jom","10/31/2011,Monday",14,100,47
View 15 Replies
View Related
Mar 5, 2015
I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.
View 3 Replies
View Related
May 16, 2006
As part of a c# program, utilizing .Net 2.0, I am calling a sproc via a SqlCommand to bulk load data from flat files to a various tables in a SQL Server 2005 database. We are using format files to do this, as all of the incoming flat files are fixed length. The sproc simply calls a T-SQL BULK INSERT statement, accepting the file name, format file name and the database table as input paramaters. As expected, this works most of the time, but periodically (to often for a production environment), the insert fails. The particular file to fail is essentially random and when I rerun the process, the insert completes successfully.
A sample of the error messages returned is as follows (@sql is the string executed):
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt".
Starting spRAS_BulkInsertData.
@sql = BULK INSERT Raser.dbo.EMODT3_Work FROM '\RASDMNTTRAS_ROOTAmeriHealthworkpdclmsemodt3.20060511.0915.txt.DATA' WITH (FORMATFILE = '\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt');
The format file for this particular example is as follows (I apologize for the length):
8.0
62
1 SQLCHAR 0 1 "" 1 Record_Type SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "" 2 Vendor_Number SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "" 3 Extract_Subscriber_Number SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "" 4 Extract_Member_Number SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 2 "" 5 Claim_Nbr_Branch_Code SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 8 "" 6 Claim_Nbr_Batch_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "" 7 Claim_Nbr_Batch_Sequence_Nbr SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "" 8 Claim_Nbr_Sequence_Number SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 3 "" 9 LINE_NUMBER SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "" 10 Patient_Sex_Code SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 3 "" 11 Patient_Age SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 4 "" 12 G_L_Posting_Tables_Code SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "" 13 G_L_Posting_Tbls_Code_Desc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 2 "" 14 Fund_TYPE SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "" 15 Stop_Loss_Or_Step_Down_Code SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 2 "" 16 Stop_Loss_Fund SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 50 "" 17 Stop_Loss_Fund_Desc SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "" 18 Post_Date SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "" 19 Rebundling_Status_Indicator SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "" 20 Co_Payment_Grouper SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "" 21 Co_Payment_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 8 "" 22 Co_Payment_Accumulator SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "" 23 Co_Payment_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 8 "" 24 Co_Insurance_Grouper SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "" 25 Co_Insurance_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 8 "" 26 Co_Insurance_Accumulator SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 50 "" 27 CI_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 8 "" 28 Coverage_Grouper SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 50 "" 29 Coverage_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 8 "" 30 Coverage_Accumulator SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 50 "" 31 Coverage_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 8 "" 32 Deductible_Grouper SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "" 33 Deductible_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 8 "" 34 Deductible_Accumulator SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 50 "" 35 Deductible_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 8 "" 36 Unit_Grouper SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 50 "" 37 Unit_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 8 "" 38 Unit_Accumulator SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 50 "" 39 Unit_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 8 "" 40 Out_Of_Pocket_Grouper SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 50 "" 41 Out_Of_Pocket_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 8 "" 42 Out_Of_Pocket_Accumulator SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 50 "" 43 Out_Of_Pocket_Acc_Desc SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 3 "" 44 Service_Edit_Code SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 50 "" 45 Service_Edit_Code_Desc SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 8 "" 46 System_Date_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 8 "" 47 Last_Change_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 10 "" 48 Medicare_Termination_Reason_Code SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 10 "" 49 User_ID_MEDMAS SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 10 "" 50 User_ID_Last_Modified SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 8 "" 51 Adjudication_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 9 "" 52 Adjudication_Time SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 10 "" 53 Adjudication_User_ID SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 9 "" 54 A_P_Batch_Number SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 7 "" 55 A_P_Sequence SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 3 "" 56 CPA_Batch_Number SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "" 57 CPA_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 1 "" 58 Manual_Authorization_Flag SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 50 "" 59 Fund_Description SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 1 "" 60 DRG_Inclusion_Indicator SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 1 "" 61 Future_Expansion SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 2 "
" 62 Company_Number SQL_Latin1_General_CP1_CI_AS
Has anyboy run across this before, or have any ideas as to what might be happening?
Thanks in advance.
View 6 Replies
View Related
May 14, 2007
I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).
Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.
How shall I go about it?
many thanks! :D
View 2 Replies
View Related
