import of data in text file How ?

Hello group,
Each month we want to import data from an ascii file,
the records are of a fixed length (some spare space is added to fill them
up)
there are 12 different types of records (variants)
Each type has a different (but within the type always the same) build.
Each record starts with a type indication. Then the records follows.
One type can have 2 fields the other type can have 15 fields.
Records following eachother can have relations, so a type two record
belongs to the type 1 record before the type 2 record.
Example with 3 types
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
2 CCCDDDDDDEEEEFFGGHH
3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
The letters are indications for the different fields.
Any suggestions how to solve this ?
In our propriety system this was solved by reading the record
into a pascal record with all twelf variants and then writing it to the
propriety database. We could reuse the technique of the program,
but can not reuse the program.
And we still have to split up each record into fields.
Our propriety database just accepted the 'binairy' data as is.
ben brugmanBen,
You can reimplement your method of reading a record at a time and inserting
the row into the proper destination by using a DTS package and doing some
scripting for each row.
How I tend to do things like this is:
BULK INSERT the file to a work table
CREATE TABLE BulkInsertWork
(RowID INT IDENTITY,
RowText NVARCHAR(1000))
TRUNCATE TABLE BulkInsertWork
BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
Then I write a stored procedure that parses out the specific rows, such as:
INSERT INTO Table1 (ColumnA, ColumnB)
SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
FROM BulkInsertWork
etc. for each type
FWIW
Russell Fields
http:/www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-even dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:bob7k7$4dj$1@.reader11.wxs.nl...
> Hello group,
> Each month we want to import data from an ascii file,
> the records are of a fixed length (some spare space is added to fill them
> up)
> there are 12 different types of records (variants)
> Each type has a different (but within the type always the same) build.
> Each record starts with a type indication. Then the records follows.
> One type can have 2 fields the other type can have 15 fields.
> Records following eachother can have relations, so a type two record
> belongs to the type 1 record before the type 2 record.
> Example with 3 types
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> 2 CCCDDDDDDEEEEFFGGHH
> 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> The letters are indications for the different fields.
> Any suggestions how to solve this ?
> In our propriety system this was solved by reading the record
> into a pascal record with all twelf variants and then writing it to the
> propriety database. We could reuse the technique of the program,
> but can not reuse the program.
> And we still have to split up each record into fields.
> Our propriety database just accepted the 'binairy' data as is.
> ben brugman
>|||I immeadiatly went ahead with your suggested method.
I created a .txt file from the example in the mail.
I changed the nvarchar in the table to varchar.
When inserting from the .txt file I get the following errors :
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
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.
The statement has been terminated.
Added :
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n'
)
But stil the same errors.
(Lastrow and rowsperbatch did not bring a solution either).
Please advise.
Also played around a bit with DTS, but the problem is that I can do an
import and then
a selection. Or that I can do a split up of the line into different fields.
But I can not do them both.
(Or I have to split up every line in every possible way and after selection
throwing away
the not desired results. Make a table for each type and insert every row in
every table and
then trowing away the types which do not belong in the table. I think this
is pretty ugly).
Ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Ben,
> You can reimplement your method of reading a record at a time and
inserting
> the row into the proper destination by using a DTS package and doing some
> scripting for each row.
> How I tend to do things like this is:
> BULK INSERT the file to a work table
> CREATE TABLE BulkInsertWork
> (RowID INT IDENTITY,
> RowText NVARCHAR(1000))
> TRUNCATE TABLE BulkInsertWork
> BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> Then I write a stored procedure that parses out the specific rows, such
as:
> INSERT INTO Table1 (ColumnA, ColumnB)
> SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> FROM BulkInsertWork
> etc. for each type
> FWIW
> Russell Fields
> http:/www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-even dedicated to SQL Server! Register TODAY!
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bob7k7$4dj$1@.reader11.wxs.nl...
> > Hello group,
> >
> > Each month we want to import data from an ascii file,
> > the records are of a fixed length (some spare space is added to fill
them
> > up)
> > there are 12 different types of records (variants)
> > Each type has a different (but within the type always the same) build.
> > Each record starts with a type indication. Then the records follows.
> > One type can have 2 fields the other type can have 15 fields.
> >
> > Records following eachother can have relations, so a type two record
> > belongs to the type 1 record before the type 2 record.
> >
> > Example with 3 types
> >
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> > 2 CCCDDDDDDEEEEFFGGHH
> > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> >
> > The letters are indications for the different fields.
> >
> > Any suggestions how to solve this ?
> >
> > In our propriety system this was solved by reading the record
> > into a pascal record with all twelf variants and then writing it to the
> > propriety database. We could reuse the technique of the program,
> > but can not reuse the program.
> > And we still have to split up each record into fields.
> > Our propriety database just accepted the 'binairy' data as is.
> >
> > ben brugman
> >
> >
>|||Ben,
A guess: The last row of the text file does not have the '\n'. If that is
so, append the character.
Less likely and more obscure: Are you on SQL Server 2000? If you are on
7.0, KB article 324122 has obscure problems that it is unlikely apply,
but...
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
All error numbers that are in the range of 7300 to 7399 indicate a problem
with the provider, in this case STREAM.
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I immeadiatly went ahead with your suggested method.
> I created a .txt file from the example in the mail.
> I changed the nvarchar in the table to varchar.
> When inserting from the .txt file I get the following errors :
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> 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.
> The statement has been terminated.
>
> Added :
> WITH (
> DATAFILETYPE = 'char',
> ROWTERMINATOR = '\n'
> )
> But stil the same errors.
> (Lastrow and rowsperbatch did not bring a solution either).
> Please advise.
> Also played around a bit with DTS, but the problem is that I can do an
> import and then
> a selection. Or that I can do a split up of the line into different
fields.
> But I can not do them both.
> (Or I have to split up every line in every possible way and after
selection
> throwing away
> the not desired results. Make a table for each type and insert every row
in
> every table and
> then trowing away the types which do not belong in the table. I think this
> is pretty ugly).
> Ben brugman
>
>
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Ben,
> >
> > You can reimplement your method of reading a record at a time and
> inserting
> > the row into the proper destination by using a DTS package and doing
some
> > scripting for each row.
> >
> > How I tend to do things like this is:
> > BULK INSERT the file to a work table
> >
> > CREATE TABLE BulkInsertWork
> > (RowID INT IDENTITY,
> > RowText NVARCHAR(1000))
> >
> > TRUNCATE TABLE BulkInsertWork
> >
> > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> >
> > Then I write a stored procedure that parses out the specific rows, such
> as:
> >
> > INSERT INTO Table1 (ColumnA, ColumnB)
> > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > FROM BulkInsertWork
> >
> > etc. for each type
> >
> > FWIW
> > Russell Fields
> > http:/www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-even dedicated to SQL Server! Register TODAY!
> >
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > Hello group,
> > >
> > > Each month we want to import data from an ascii file,
> > > the records are of a fixed length (some spare space is added to fill
> them
> > > up)
> > > there are 12 different types of records (variants)
> > > Each type has a different (but within the type always the same) build.
> > > Each record starts with a type indication. Then the records follows.
> > > One type can have 2 fields the other type can have 15 fields.
> > >
> > > Records following eachother can have relations, so a type two record
> > > belongs to the type 1 record before the type 2 record.
> > >
> > > Example with 3 types
> > >
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > >
> > > The letters are indications for the different fields.
> > >
> > > Any suggestions how to solve this ?
> > >
> > > In our propriety system this was solved by reading the record
> > > into a pascal record with all twelf variants and then writing it to
the
> > > propriety database. We could reuse the technique of the program,
> > > but can not reuse the program.
> > > And we still have to split up each record into fields.
> > > Our propriety database just accepted the 'binairy' data as is.
> > >
> > > ben brugman
> > >
> > >
> >
> >
>|||After loads of trying, reading the KB file, still getting the same Error
message.
In the end we deleted the identity column from "BulkInsertWork" and now the
insert works, but offcourse we have lost the order of the table.
Also did try some DTS work, but DTS is not very flexible when it comes to
variant records in a text file.
Thanks for your time,
and if you have suggestions to bring the identity column back,
I would appreciate that.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Ben,
> A guess: The last row of the text file does not have the '\n'. If that
is
> so, append the character.
> Less likely and more obscure: Are you on SQL Server 2000? If you are on
> 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> but...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> All error numbers that are in the range of 7300 to 7399 indicate a problem
> with the provider, in this case STREAM.
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > I immeadiatly went ahead with your suggested method.
> > I created a .txt file from the example in the mail.
> > I changed the nvarchar in the table to varchar.
> >
> > When inserting from the .txt file I get the following errors :
> >
> > Server: Msg 4832, Level 16, State 1, Line 1
> > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > 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.
> > The statement has been terminated.
> >
> >
> > Added :
> > WITH (
> > DATAFILETYPE = 'char',
> > ROWTERMINATOR = '\n'
> > )
> >
> > But stil the same errors.
> > (Lastrow and rowsperbatch did not bring a solution either).
> >
> > Please advise.
> >
> > Also played around a bit with DTS, but the problem is that I can do an
> > import and then
> > a selection. Or that I can do a split up of the line into different
> fields.
> > But I can not do them both.
> > (Or I have to split up every line in every possible way and after
> selection
> > throwing away
> > the not desired results. Make a table for each type and insert every row
> in
> > every table and
> > then trowing away the types which do not belong in the table. I think
this
> > is pretty ugly).
> >
> > Ben brugman
> >
> >
> >
> >
> >
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > Ben,
> > >
> > > You can reimplement your method of reading a record at a time and
> > inserting
> > > the row into the proper destination by using a DTS package and doing
> some
> > > scripting for each row.
> > >
> > > How I tend to do things like this is:
> > > BULK INSERT the file to a work table
> > >
> > > CREATE TABLE BulkInsertWork
> > > (RowID INT IDENTITY,
> > > RowText NVARCHAR(1000))
> > >
> > > TRUNCATE TABLE BulkInsertWork
> > >
> > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > >
> > > Then I write a stored procedure that parses out the specific rows,
such
> > as:
> > >
> > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > FROM BulkInsertWork
> > >
> > > etc. for each type
> > >
> > > FWIW
> > > Russell Fields
> > > http:/www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > >
> > >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > Hello group,
> > > >
> > > > Each month we want to import data from an ascii file,
> > > > the records are of a fixed length (some spare space is added to fill
> > them
> > > > up)
> > > > there are 12 different types of records (variants)
> > > > Each type has a different (but within the type always the same)
build.
> > > > Each record starts with a type indication. Then the records follows.
> > > > One type can have 2 fields the other type can have 15 fields.
> > > >
> > > > Records following eachother can have relations, so a type two record
> > > > belongs to the type 1 record before the type 2 record.
> > > >
> > > > Example with 3 types
> > > >
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > >
> > > > The letters are indications for the different fields.
> > > >
> > > > Any suggestions how to solve this ?
> > > >
> > > > In our propriety system this was solved by reading the record
> > > > into a pascal record with all twelf variants and then writing it to
> the
> > > > propriety database. We could reuse the technique of the program,
> > > > but can not reuse the program.
> > > > And we still have to split up each record into fields.
> > > > Our propriety database just accepted the 'binairy' data as is.
> > > >
> > > > ben brugman
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ben,
Yes, I am sorry that I glossed this in my earlier quick explanation. (I
thought about it during the night, don't you know.)
You need a format file to tell bulk insert to skip over the Identity column.
Something like:
8.0
2
1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> After loads of trying, reading the KB file, still getting the same Error
> message.
> In the end we deleted the identity column from "BulkInsertWork" and now
the
> insert works, but offcourse we have lost the order of the table.
> Also did try some DTS work, but DTS is not very flexible when it comes to
> variant records in a text file.
> Thanks for your time,
> and if you have suggestions to bring the identity column back,
> I would appreciate that.
> ben brugman
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Ben,
> >
> > A guess: The last row of the text file does not have the '\n'. If that
> is
> > so, append the character.
> >
> > Less likely and more obscure: Are you on SQL Server 2000? If you are
on
> > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > but...
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> >
> > All error numbers that are in the range of 7300 to 7399 indicate a
problem
> > with the provider, in this case STREAM.
> >
> > Russell Fields
> > http://www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-event dedicated to SQL Server! Register TODAY!
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > I immeadiatly went ahead with your suggested method.
> > > I created a .txt file from the example in the mail.
> > > I changed the nvarchar in the table to varchar.
> > >
> > > When inserting from the .txt file I get the following errors :
> > >
> > > Server: Msg 4832, Level 16, State 1, Line 1
> > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > 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.
> > > The statement has been terminated.
> > >
> > >
> > > Added :
> > > WITH (
> > > DATAFILETYPE = 'char',
> > > ROWTERMINATOR = '\n'
> > > )
> > >
> > > But stil the same errors.
> > > (Lastrow and rowsperbatch did not bring a solution either).
> > >
> > > Please advise.
> > >
> > > Also played around a bit with DTS, but the problem is that I can do an
> > > import and then
> > > a selection. Or that I can do a split up of the line into different
> > fields.
> > > But I can not do them both.
> > > (Or I have to split up every line in every possible way and after
> > selection
> > > throwing away
> > > the not desired results. Make a table for each type and insert every
row
> > in
> > > every table and
> > > then trowing away the types which do not belong in the table. I think
> this
> > > is pretty ugly).
> > >
> > > Ben brugman
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > Ben,
> > > >
> > > > You can reimplement your method of reading a record at a time and
> > > inserting
> > > > the row into the proper destination by using a DTS package and doing
> > some
> > > > scripting for each row.
> > > >
> > > > How I tend to do things like this is:
> > > > BULK INSERT the file to a work table
> > > >
> > > > CREATE TABLE BulkInsertWork
> > > > (RowID INT IDENTITY,
> > > > RowText NVARCHAR(1000))
> > > >
> > > > TRUNCATE TABLE BulkInsertWork
> > > >
> > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > > >
> > > > Then I write a stored procedure that parses out the specific rows,
> such
> > > as:
> > > >
> > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > FROM BulkInsertWork
> > > >
> > > > etc. for each type
> > > >
> > > > FWIW
> > > > Russell Fields
> > > > http:/www.sqlpass.org/events/seattle03
> > > > 2003 PASS Community Summit - Seattle
> > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > >
> > > >
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > Hello group,
> > > > >
> > > > > Each month we want to import data from an ascii file,
> > > > > the records are of a fixed length (some spare space is added to
fill
> > > them
> > > > > up)
> > > > > there are 12 different types of records (variants)
> > > > > Each type has a different (but within the type always the same)
> build.
> > > > > Each record starts with a type indication. Then the records
follows.
> > > > > One type can have 2 fields the other type can have 15 fields.
> > > > >
> > > > > Records following eachother can have relations, so a type two
record
> > > > > belongs to the type 1 record before the type 2 record.
> > > > >
> > > > > Example with 3 types
> > > > >
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > >
> > > > > The letters are indications for the different fields.
> > > > >
> > > > > Any suggestions how to solve this ?
> > > > >
> > > > > In our propriety system this was solved by reading the record
> > > > > into a pascal record with all twelf variants and then writing it
to
> > the
> > > > > propriety database. We could reuse the technique of the program,
> > > > > but can not reuse the program.
> > > > > And we still have to split up each record into fields.
> > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > >
> > > > > ben brugman
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I'll try that on monday.
At the moment I have done away with the identity column,
I am not sure if I will need it, because it seems that the only dependency
in order of the records is the dependency on the very first record, which
only exists once.
I am using your method of
"SUBSTRING(RowText,3,6), SUBSTRING(RowText,9,2)"
But because there are 153 columns in total, I do not want to type this out.
So I am in the process of writing scripts to generate this code.
Up to know I have managed to get three tables done, but the script
still needs some 'hand editing' for each table.
Using constructs like
Select 'INSERT INTO' + @.@.tablename+ '('
Select 'SUBSTRING(Rowtext,
'+convert(varchar(3),offset)+','+convert(varchar(3),length)+'),'
From metatable where table_name = @.@.tablename and length <> 0
Select 'From Bulkinsertwork'
Or something similar, then I cut and past this into another QA window,
clean it up and execute it.
Some things still to be solved are
The extra comma behind the last SUBSTRING line.
The extra lines generated (with the number of rows affectted and headers).
So I might go and do this in a stringvariabele and try to get the last comma
out.
Haven't decided jet if I am going to deliver the end script or enough tools
to build
the script.
Still some problems with referential constraints and spaces which have to be
converted to
<NULLS>. (After or during ? inserting ?).
Chopping all problems up and going from intermediate result to intermadiate
result makes
the code less complex.
And some problems with the content of the columns coming from other lines.
(The first line contains a sort of number which should be included in almost
all records).
Probably use temporary tables resolve all problems and then copy the content
to the
production tables.
Thanks for you help.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ucvlkTTpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Ben,
> Yes, I am sorry that I glossed this in my earlier quick explanation. (I
> thought about it during the night, don't you know.)
> You need a format file to tell bulk insert to skip over the Identity
column.
> Something like:
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
>
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> > After loads of trying, reading the KB file, still getting the same Error
> > message.
> >
> > In the end we deleted the identity column from "BulkInsertWork" and now
> the
> > insert works, but offcourse we have lost the order of the table.
> >
> > Also did try some DTS work, but DTS is not very flexible when it comes
to
> > variant records in a text file.
> >
> > Thanks for your time,
> > and if you have suggestions to bring the identity column back,
> > I would appreciate that.
> >
> > ben brugman
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Ben,
> > >
> > > A guess: The last row of the text file does not have the '\n'. If
that
> > is
> > > so, append the character.
> > >
> > > Less likely and more obscure: Are you on SQL Server 2000? If you are
> on
> > > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > > but...
> > >
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> > >
> > > All error numbers that are in the range of 7300 to 7399 indicate a
> problem
> > > with the provider, in this case STREAM.
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-event dedicated to SQL Server! Register TODAY!
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > > I immeadiatly went ahead with your suggested method.
> > > > I created a .txt file from the example in the mail.
> > > > I changed the nvarchar in the table to varchar.
> > > >
> > > > When inserting from the .txt file I get the following errors :
> > > >
> > > > Server: Msg 4832, Level 16, State 1, Line 1
> > > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > > 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.
> > > > The statement has been terminated.
> > > >
> > > >
> > > > Added :
> > > > WITH (
> > > > DATAFILETYPE = 'char',
> > > > ROWTERMINATOR = '\n'
> > > > )
> > > >
> > > > But stil the same errors.
> > > > (Lastrow and rowsperbatch did not bring a solution either).
> > > >
> > > > Please advise.
> > > >
> > > > Also played around a bit with DTS, but the problem is that I can do
an
> > > > import and then
> > > > a selection. Or that I can do a split up of the line into different
> > > fields.
> > > > But I can not do them both.
> > > > (Or I have to split up every line in every possible way and after
> > > selection
> > > > throwing away
> > > > the not desired results. Make a table for each type and insert every
> row
> > > in
> > > > every table and
> > > > then trowing away the types which do not belong in the table. I
think
> > this
> > > > is pretty ugly).
> > > >
> > > > Ben brugman
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > > Ben,
> > > > >
> > > > > You can reimplement your method of reading a record at a time and
> > > > inserting
> > > > > the row into the proper destination by using a DTS package and
doing
> > > some
> > > > > scripting for each row.
> > > > >
> > > > > How I tend to do things like this is:
> > > > > BULK INSERT the file to a work table
> > > > >
> > > > > CREATE TABLE BulkInsertWork
> > > > > (RowID INT IDENTITY,
> > > > > RowText NVARCHAR(1000))
> > > > >
> > > > > TRUNCATE TABLE BulkInsertWork
> > > > >
> > > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt'
...
> > > > >
> > > > > Then I write a stored procedure that parses out the specific rows,
> > such
> > > > as:
> > > > >
> > > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > > FROM BulkInsertWork
> > > > >
> > > > > etc. for each type
> > > > >
> > > > > FWIW
> > > > > Russell Fields
> > > > > http:/www.sqlpass.org/events/seattle03
> > > > > 2003 PASS Community Summit - Seattle
> > > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > > >
> > > > >
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > > Hello group,
> > > > > >
> > > > > > Each month we want to import data from an ascii file,
> > > > > > the records are of a fixed length (some spare space is added to
> fill
> > > > them
> > > > > > up)
> > > > > > there are 12 different types of records (variants)
> > > > > > Each type has a different (but within the type always the same)
> > build.
> > > > > > Each record starts with a type indication. Then the records
> follows.
> > > > > > One type can have 2 fields the other type can have 15 fields.
> > > > > >
> > > > > > Records following eachother can have relations, so a type two
> record
> > > > > > belongs to the type 1 record before the type 2 record.
> > > > > >
> > > > > > Example with 3 types
> > > > > >
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > >
> > > > > > The letters are indications for the different fields.
> > > > > >
> > > > > > Any suggestions how to solve this ?
> > > > > >
> > > > > > In our propriety system this was solved by reading the record
> > > > > > into a pascal record with all twelf variants and then writing it
> to
> > > the
> > > > > > propriety database. We could reuse the technique of the program,
> > > > > > but can not reuse the program.
> > > > > > And we still have to split up each record into fields.
> > > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > > >
> > > > > > ben brugman
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

import ascii file with ssis and script

Hi, i've question about how to import an ascii-file in a sql 2005 table.
I want to import this file also with an unique key. There i first have to get the last key form the table and then raise this key. Next step is to use this key during the import.

How do i have to do this in ssis?
Thanks in advance

OlafNo problem.

While in your control flow, add a variable named MaxKey of integer type. Then in your control flow, right before the data flow task, add an Execute SQL task. Set its ResultSet to Single row. Setup the connection in it to point to your database and the for the SQLStatement, use: "select max(keyfield) from your_table". Click on the Result Set option on the left-hand side of the editor. Set the result name to 0 and chose User::MaxKey as the Variable Name. Click OK.

In your data flow right before the OLE destination, add a Script Component. Chose to use it as a transformation. Edit it. On the left, select "Inputs and Outputs". Expand Output 0 and then "Add Column" to the Output Columns folder and call it "NewKey. Make sure it is an integer data type big enough to hold a key big enough for your table. Then click on "Script" on the left to bring up the script parameters. Add "MaxKey" to the ReadOnlyVariables box. Then, click on the Design Script... button. Here's your script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data in the table, or we'll start with 0+1=1 if we don't
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If

Row.NewKey = NextKey ' Assign NextKey to our AdFormKey field on our data row
'
End Sub

End Class|||Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf|||See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.|||thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance|||

jaegd wrote:

See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.

I don't think this is the problem. I detailed the steps exactly as I use it. Either the OP has a typo, or he didn't define the variable in the right scope. The variable must be scoped to the package. The solution above is too much work.|||

Olaf vd Sanden wrote:

Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf

You must define the variable when in the control flow. Make sure that the variable's scope is set to the package.

Then make sure you type the variable name in the ReadOnlyVariables section on the script parameters page, as I have indicated in my instructions. Do not include any extra spaces when typing this variable name in the ReadOnlyVariables box.|||

Olaf vd Sanden wrote:

thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance

Please make sure you are using a destination OLE connector and not a source connector.|||Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf|||

Olaf vd Sanden wrote:

Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf

Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."|||Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf|||

Olaf vd Sanden wrote:

Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf

Your OLE DB destination has to be at the END of the dataflow. You cannot have two in the same dataflow, unless you've split the dataflow with a multicast, conditional statement, etc...

Why do you have the OLE DB destination between the Data Conversion transformation and the Script component?|||Phil, Thanks for your reply. I think i've solved the problem. I've used an 'Union All' where i connected the 'Flate File source editor' and 'Script Component'. After the 'Union' comes a 'Data conversion' and then the 'OLE DB Destination'. I like to hear from you if this the right way.

Now i get an error message in the 'Execution Results': [DTS.Pipeline] Error: component "Union All" (14793) failed the pre-execute phase and returned error code 0x80070057.

What do I do wrong now?
Thanks.

Olaf|||Your flat file should hook into the data conversion transformation and then to the script component and then to the OLE DB destination.

FF -> Data Conversion -> Script Component -> OLE DB Destination.

No union is necessary.|||Phil, Thanks again for your fast reply. And it works.
Again thanks.
Olaf

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.commattc66 via SQLMonster.com (u16013@.uwe) writes:

Quote:

Originally Posted by

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?
>
Using SQL 2005.

There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@.uwewrote in message
news:65826d5d02090@.uwe...

Quote:

Originally Posted by

>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?
>
Using SQL 2005.
>
Thanks
Matt
>
--
Matt Campbell
mattc (at) saunatec [dot] com
>
Message posted via http://www.sqlmonster.com
>
>

Implicit Text to Binary Conversion

/***
Requirement: Insert ascii text into varbinary field allowing SQL to convert
the data implicitly
Problem: The data is encoded as Unicode and is truncated or simply blanked
out after the first 10 characters
The following is valid and works; however, it doesn't solve my problem.
As a result, I've tried several approaches, including creating a cursor and
calling Cast() to convert the data to binary. This appears to work, but
again, stops after the first 10 characters
**/
DECLARE @.docHandle int
DECLARE @.xmlDoc varchar(5000)
SET @.xmlDoc ='
<?xml version="1.0"?>
<f2dbarchive>
<archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
<archive_test id="1530" />
<!-- tables used in this archive -->
<tables>
<Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
lngKeyValue="2096" lngRecordCount="4" />
</tables>
<!-- data from the tables -->
<data>
<tblFinalData>
<Row lngDataID="5336" lngInstanceID="2096" binData="10946534210000000000276
Final
35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
0.085 DUST SPOTTER 3 LOF 85.00
NY3849.475909519025.7722760243122.0620566407829311 98.67 32086.86
160 85 31198.67 32086.86
140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
123.21566117 -0.30555699 0.0010055 0.00555699
162.7038151636657.46 2017 Y Y34642.169 33069.594
172.887 MND 5.5B_NEW
147.95450669 -0.39920593 0.02882665 0.34920593 167.814226706570
2019 N N31198.912 32050.403 140.93 GP
8 OBS PT 0 0
-0.000382 0.01695564 0 0 1658 Y
Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
203.64517087 -1.08627593 0.00482913 0.03627593
163.7961106000857.63 2015 N N31892.772 32960.261
153.253 OBS PT 3B SOUTH 0
0 0.00531249 0.11706955 0 0
"
intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0" />
<Row lngDataID="5337" lngInstanceID="2096" binData="10946539640000000000277
Final
34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
0 1ST LIGHT SPOTTER 3
NY3717.384364552516.449301983071517.08833337179311 98.67
32086.86 160 85 31198.67
32086.86 140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH
PED 128.4489945 34.57777634 0.0010055
0.00555699 829.755965865400 2017 Y Y34642.169
33069.594 172.887 MND 5.5B_NEW
158.30450669 44.65079407 0.02882665 0.34920593
856.898004060910 2019 N N31198.912 32050.403
140.93 GP 8 OBS PT 0
0 -0.000382 0.01695564 0 0 1658
N N35282.726 33023.024 175.67 TOWER @. 6B
SOUTH PED 0 0
0.00482913 0.03627593 0 0 2015 Y
Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
100.09468751 12.76626379 0.00531249 0.11706955
845.995612034910
" intPackageCount="" booGood="1" txtShotID=""
txtStatus="Field" qvTime="0" />
</tblFinalData>
</data>
</f2dbarchive>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
DECLARE @.tableName varchar(80)
DECLARE @.instID int
SET @.instID=2096
SET @.tableName='tblFinalData'
IF(SELECT lngRecordCount FROM OPENXML(@.docHandle,
'/f2dbarchive/tables/Row', 1)
WITH (txtTablename varchar(80), lngRecordCount int)
WHERE txtTablename=@.tableName) > 0
BEGIN
print 'at least 1 row in '+@.tableName
INSERT INTO tblFinalData
(lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
qvTime)
SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
booGood, txtShotID, txtStatus, qvTime
FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
WITH (
lngInstanceID int,
binData varbinary(2000),
intPackageCount int,
booGood bit,
txtShotID char(10),
txtStatus char(10),
qvTime decimal(16, 6)
)
END
ELSE
BEGIN
print '0 records in ' + @.tableName
END
EXEC sp_xml_removedocument @.docHandle
/**
--undesired unicode results: 1) it's unicode and 2) the data is blank after
the first number is translated
0x310030003900340036003500330039003600340030003000 300030003000
...
0x310030003900340036003500330034003200310030003000 300030003000
...
--normal result using vb
0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
**/
The XML is Unicode once it is parsed. If you want to get it into
non-Unicode, change

> binData varbinary(2000),
to

> binData varchar(8000),
in your WITH clause.
HTH
Michael
"TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
> /***
> Requirement: Insert ascii text into varbinary field allowing SQL to
> convert
> the data implicitly
> Problem: The data is encoded as Unicode and is truncated or simply
> blanked
> out after the first 10 characters
> The following is valid and works; however, it doesn't solve my problem.
> As a result, I've tried several approaches, including creating a cursor
> and
> calling Cast() to convert the data to binary. This appears to work, but
> again, stops after the first 10 characters
> **/
> DECLARE @.docHandle int
> DECLARE @.xmlDoc varchar(5000)
> SET @.xmlDoc ='
> <?xml version="1.0"?>
> <f2dbarchive>
> <archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
> <archive_test id="1530" />
> <!-- tables used in this archive -->
> <tables>
> <Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
> lngKeyValue="2096" lngRecordCount="4" />
> </tables>
> <!-- data from the tables -->
> <data>
> <tblFinalData>
> <Row lngDataID="5336" lngInstanceID="2096"
> binData="10946534210000000000276
> Final
> 35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
> 0.085 DUST SPOTTER 3 LOF 85.00
> NY3849.475909519025.7722760243122.0620566407829311 98.67
> 32086.86
> 160 85 31198.67 32086.86
> 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
> 123.21566117 -0.30555699 0.0010055 0.00555699
> 162.7038151636657.46 2017 Y Y34642.169 33069.594
> 172.887 MND 5.5B_NEW
> 147.95450669 -0.39920593 0.02882665 0.34920593
> 167.814226706570
> 2019 N N31198.912 32050.403 140.93
> GP
> 8 OBS PT 0 0
> -0.000382 0.01695564 0 0 1658
> Y
> Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
> 203.64517087 -1.08627593 0.00482913 0.03627593
> 163.7961106000857.63 2015 N N31892.772 32960.261
> 153.253 OBS PT 3B SOUTH 0
> 0 0.00531249 0.11706955 0 0
> "
> intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0"
> />
> <Row lngDataID="5337" lngInstanceID="2096"
> binData="10946539640000000000277
> Final
> 34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
> 0 1ST LIGHT SPOTTER 3
> NY3717.384364552516.449301983071517.08833337179311 98.67
> 32086.86 160 85 31198.67
> 32086.86 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B
> SOUTH
> PED 128.4489945 34.57777634 0.0010055
> 0.00555699 829.755965865400 2017 Y Y34642.169
> 33069.594 172.887 MND 5.5B_NEW
> 158.30450669 44.65079407 0.02882665 0.34920593
> 856.898004060910 2019 N N31198.912 32050.403
> 140.93 GP 8 OBS PT 0
> 0 -0.000382 0.01695564 0 0
> 1658
> N N35282.726 33023.024 175.67 TOWER @. 6B
> SOUTH PED 0 0
> 0.00482913 0.03627593 0 0 2015
> Y
> Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
> 100.09468751 12.76626379 0.00531249 0.11706955
> 845.995612034910
> " intPackageCount="" booGood="1" txtShotID=""
> txtStatus="Field" qvTime="0" />
> </tblFinalData>
> </data>
> </f2dbarchive>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
> DECLARE @.tableName varchar(80)
> DECLARE @.instID int
> SET @.instID=2096
> SET @.tableName='tblFinalData'
> IF (SELECT lngRecordCount FROM OPENXML(@.docHandle,
> '/f2dbarchive/tables/Row', 1)
> WITH (txtTablename varchar(80), lngRecordCount int)
> WHERE txtTablename=@.tableName) > 0
> BEGIN
> print 'at least 1 row in '+@.tableName
> INSERT INTO tblFinalData
> (lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
> qvTime)
> SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
> booGood, txtShotID, txtStatus, qvTime
> FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
> WITH (
> lngInstanceID int,
> binData varbinary(2000),
> intPackageCount int,
> booGood bit,
> txtShotID char(10),
> txtStatus char(10),
> qvTime decimal(16, 6)
> )
> END
> ELSE
> BEGIN
> print '0 records in ' + @.tableName
> END
>
> EXEC sp_xml_removedocument @.docHandle
> /**
> --undesired unicode results: 1) it's unicode and 2) the data is blank
> after
> the first number is translated
> 0x310030003900340036003500330039003600340030003000 300030003000
> ...
> 0x310030003900340036003500330034003200310030003000 300030003000
> ...
> --normal result using vb
> 0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
> **/
|||Thanks...that's great...gets me past creating a cursor. If I may be so dense
to ask one more issue:
After processing, this is all I get:
0x313039343635333432313030303030303030303032373620 202020202020
From a source string of:
10946534210000000000276 Final
35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
actually about 1600 characters wide}
Is there something wrong with mixed numeric/text?
Any followup is greatly appreciated.
Dave
"Michael Rys [MSFT]" wrote:

> The XML is Unicode once it is parsed. If you want to get it into
> non-Unicode, change
>
> to
>
> in your WITH clause.
> HTH
> Michael
> "TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
> in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
>
>
|||I've discovered that cast(binData as binary(2000)) properly stores the data.
My guess is SQL Sever see the space as a field terminator and stops
processing.
If this is right, the follow-on question should be, how do I force SQL to
read the entire data word?
Or am I stuck with fixed width binary data? (incidently, the reason for
using a varbinary column was for efficiency)
Thanks again...I'm feeling close to resolving my delimma.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> Thanks...that's great...gets me past creating a cursor. If I may be so dense
> to ask one more issue:
> After processing, this is all I get:
> 0x313039343635333432313030303030303030303032373620 202020202020
> From a source string of:
> 10946534210000000000276 Final
> 35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
> actually about 1600 characters wide}
> Is there something wrong with mixed numeric/text?
> Any followup is greatly appreciated.
> Dave
> "Michael Rys [MSFT]" wrote:
|||How do you check? If you use the query analyzer, did you set the column
width large enough?
The space should not be treated as a field terminator...
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the
> data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Found the bug:
cast(binData as varbinary) as binData
Should be
cast(binData as varbinary(2000)) as binData
SQL Server was kind enough to all the former, but this allow it to determine
the length of the varBinary data.
Many thanks to Michael Rys for expert guidance.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Yes I use QA using select len(bindata) as ldata, bindata from tblFinalData
That eventually led me to discover the need for cast(binData as
varbinary(2000))
which solves my problem.
It would be noteworthy to discover why SQL Server stopped parsing the text
Try this:
Sql#1: select cast('1094653421000000000027635031.16534415632448. 03061756'
as varbinary) as tdata
Sql#2: select cast('1094653421000000000027635031.16534415632448. 03061756' as
varbinary(100)) as tdata
The results I get are
#1 is truncated to 30 characters (doesn't matter what characters)
#2 is complete and variable as expected
Therefore: (with my Florida hurricane-ridden mind), SQL Server's cast
function defaults to 30-characters. [guess I missed that in the Books-Online]
Dave
cast('somedata' as
"Michael Rys [MSFT]" wrote:

> How do you check? If you use the query analyzer, did you set the column
> width large enough?
> The space should not be treated as a field terminator...
> Best regards
> Michael
> "TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
> message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...
>
>
|||Hmm. I should have seen this (mea culpa) as well.
In SQL Server 2005, you probably want to use varbinary(max) instead :-).
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:2233608D-86CD-4EF3-AFCE-E22C274A8216@.microsoft.com...[vbcol=seagreen]
> Found the bug:
> cast(binData as varbinary) as binData
> Should be
> cast(binData as varbinary(2000)) as binData
> SQL Server was kind enough to all the former, but this allow it to
> determine
> the length of the varBinary data.
> Many thanks to Michael Rys for expert guidance.
> Dave
>
> "TeraComp in Florida" wrote: