import into more than 1 table

Hi everyone,
im trying to import data using a query...how ever i can only insert data into one table, and i need to insert data into many...this is at the part where you map the fields...
is there a way to do this? or something similar?
Thanks, Justinyou could export your data to a flat file using bcp, then import that file to as many destinations as you like, again using bcp.|||you could export your data to a flat file using bcp, then import that file to as many destinations as you like, again using bcp.

hi, and thanks for your reply...

but im not sure what bcp is?

Thanks, Justin|||Open up Books Online (or press F1 from the Query Analyzer screen) ... click on the INDEX tab ... type bcp ... peruse the various topics ... click on bcp utility for the syntax ... give it a try.

Import flat file into SQL Server 2005 Express

I am new to SQL Server, and migrating part of an Access application to
SSE. I am trying to insert a comma delimited file into SSE 2005. I am
able to run a BULK INSERT statement on a simple file, specifying the
field (,) and row (\n) terminators. I can also do the same with a
format file.

Here is the problem. My csv file has 185 columns, with a mixture of
datatypes. Sometimes, a text field will contain the field delimiter as
part of the string. In this case (and only in this case) there will be
double quotes around the string to indicate that the comma is part of
the field, and not a delimiter.

Is there any way to indicate that there is a text delimiter that is
only present some of the time?

If not, any suggestions on getting the data into SSE?

Many thanks for your input.

Cheryl(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

I am new to SQL Server, and migrating part of an Access application to
SSE. I am trying to insert a comma delimited file into SSE 2005. I am
able to run a BULK INSERT statement on a simple file, specifying the
field (,) and row (\n) terminators. I can also do the same with a
format file.
>
Here is the problem. My csv file has 185 columns, with a mixture of
datatypes. Sometimes, a text field will contain the field delimiter as
part of the string. In this case (and only in this case) there will be
double quotes around the string to indicate that the comma is part of
the field, and not a delimiter.

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

Most other people would probably try to write a package in Integration
Services, but I have never used Integration Services myself. And for your
part - SQL Express does not come with Integration Services, I believe.

--
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|||In message <Xns98B096E86FA00Yazorman@.127.0.0.1>, Erland Sommarskog
<esquel@.sommarskog.sewrites

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

>I am new to SQL Server, and migrating part of an Access application to
>SSE. I am trying to insert a comma delimited file into SSE 2005. I am
>able to run a BULK INSERT statement on a simple file, specifying the
>field (,) and row (\n) terminators. I can also do the same with a
>format file.
>>
>Here is the problem. My csv file has 185 columns, with a mixture of
>datatypes. Sometimes, a text field will contain the field delimiter as
>part of the string. In this case (and only in this case) there will be
>double quotes around the string to indicate that the comma is part of
>the field, and not a delimiter.

>
>So a file could look like this:
>
2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie
>
>There is now way to get BULK INSERT to handle this file in that shape.
>If I were faced with this file, I would write Perl script that replaced
>the commas outside the "" with a different delimiter and then removed the
>"". And it would not be trivial.
>
>Most other people would probably try to write a package in Integration
>Services, but I have never used Integration Services myself. And for your
>part - SQL Express does not come with Integration Services, I believe.

Two things to add, both useful options if the amount of data is small.
First, the import filters in MS Access are better than those in SQL
Server. If the data will fit into an Access table that might just do the
trick. Second, spreadsheets have more flexible parsing options than
databases. It may be possible to load the data into a spreadsheet. That
allows different algorithms to be applied to different rows.

Lastly, text files can be opened and read by VBA code in any of the
office languages, or any of the .NET languages. Either could be used,
but writing code to cope with all of the possible options may take time.

--
Bernard Peek
back in search of cognoscenti|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

So a file could look like this:
>
2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie
>
There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

In addition to Bernard's post, is not Excel able to read that format?
In such case open in Except, and save as a tab-delimited file and importing
that should be a breeze. (Assuming, of course, there are no tabs in the
data!)

--
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|||Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

>
In addition to Bernard's post, is not Excel able to read that format?
In such case open in Except, and save as a tab-delimited file and importing
that should be a breeze. (Assuming, of course, there are no tabs in the
data!)
>
--
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

|||(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

>Thank you both for your suggestions. Yes, I was thinking that BULK
>INSERT was not going to be able to handle this. I had thought about
>dumping the file into an Access table first, but the file could be very
>large (200,000+ rows). I am going to try the Excel spreadsheet idea.

>
200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

I think the latest version of Excel may have a higher row limit - which
only increases the tendency of newbies to misuse Excel as a "database".|||You are correct - Excel has a limit on the number of rows. I thought
about that after I sent the reply. So now I am looking at Access.

Here is my next question. I want to use OPENROWSET in a procedure to
get the data from Access into SSE. My code looks something like this:

INSERT INTO sse_table1 Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'path to mdb';'admin';'',
'Select * FROM access_table1
) as GFF

This works great. However, the location of the access database is only
known at runtime. I can pass the path as a parameter to the stored
procedure, but using it as a variable in OPENROWSET fails. Code looks
like this

CREATE PROCEDURE [dbo].[spImportBillingFile]
@.strTableLocation varchar(255),
@.btSuccess bit OUTPUT
AS
BEGIN

DECLARE @.strConnect varchar(255)
SET @.strConnect = @.strTableLocation

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF

Set @.btSuccess = 1

END

Does OPENROWSET not allow a variable to be used?

Thanks again for the help.

Ed Murphy wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:
>

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
at 65536 rows?

>
I think the latest version of Excel may have a higher row limit - which
only increases the tendency of newbies to misuse Excel as a "database".

|||(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF
>
Set @.btSuccess = 1
>
END
>
Does OPENROWSET not allow a variable to be used?

No. Either you have to use dynamic SQL, or define a linked server on the
fly. The former is probably simpler. Look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for a similar example
on how to deal with the nested strings.

--
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|||Thanks for all the info - I figured out how to use the dynamic sql.

Erland Sommarskog wrote:

Quote:

Originally Posted by

(cabrenner@.optonline.net) writes:

Quote:

Originally Posted by

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @.strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF

Set @.btSuccess = 1

END

Does OPENROWSET not allow a variable to be used?

>
No. Either you have to use dynamic SQL, or define a linked server on the
fly. The former is probably simpler. Look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for a similar example
on how to deal with the nested strings.
>
>
--
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

sql

Import data from Excel sheet to sql Database-asp.net 2.0

In admin tool of my application,i want to give facility to administrator that he can import
data from the Excel Sheet and can insert in sql database. for example...user id and password
that from excel sheet to user table in sql database.

how can i do this..please help me. it's urgent.

thanks

raj

Did you mean you want to customize the WebSite Admin Tool? Then why not using import/export wizard in SQL Management Studio to directly import data from excel file? I mean you can detach the database file under the app_data folder in VS2005 Solution Explorer and then attach the database in Management Studio, then you can use import/export wizard to transfer data easily. Some useful links:

How to: Attach a Database:http://msdn2.microsoft.com/en-us/library/ms190209.aspx

Import/Export Wizard:http://msdn2.microsoft.com/en-us/library/ms140052.aspx

Import Data Error- Insert Error

I'm trying to use the import wizard in a database
conversion. I get 1 error in this process that states...
--
Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
status 6: Data overflow. Invalid character value for cast
specification.
--
The tables are there after the conversion. The data is
not. During the import wizard process, I can preview the
table and the data is in the columns. Only when I go to
convert does it lose the data.
If it was the columns that were the problem, I'd re-
program the code. But I need this data transfered over.
Please HelpI'm guessing this is caused because you have some invalid data in your
PAY_PERIOD field in the input record. You need to review your input data
and verify that all data in the PAY_PERIOD field do in fact contain valid
dates. One option would be to put the data into a temporary table where the
PAY_PERIOD is just character data. You could then review the PAY_PERIOD
column in the temporary table and fix up any data that is not a valid date.
Once all the data is fixed you can then insert the data into the target you
desire from the temporary table.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca4501c45324$26b7bc00$a601280a@.phx
.gbl...
> I'm trying to use the import wizard in a database
> conversion. I get 1 error in this process that states...
> --
> Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
> status 6: Data overflow. Invalid character value for cast
> specification.
> --
> The tables are there after the conversion. The data is
> not. During the import wizard process, I can preview the
> table and the data is in the columns. Only when I go to
> convert does it lose the data.
> If it was the columns that were the problem, I'd re-
> program the code. But I need this data transfered over.
> Please Help

Import Data Error- Insert Error

I'm trying to use the import wizard in a database
conversion. I get 1 error in this process that states...
--
Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
status 6: Data overflow. Invalid character value for cast
specification.
--
The tables are there after the conversion. The data is
not. During the import wizard process, I can preview the
table and the data is in the columns. Only when I go to
convert does it lose the data.
If it was the columns that were the problem, I'd re-
program the code. But I need this data transfered over.
Please HelpI'm guessing this is caused because you have some invalid data in your
PAY_PERIOD field in the input record. You need to review your input data
and verify that all data in the PAY_PERIOD field do in fact contain valid
dates. One option would be to put the data into a temporary table where the
PAY_PERIOD is just character data. You could then review the PAY_PERIOD
column in the temporary table and fix up any data that is not a valid date.
Once all the data is fixed you can then insert the data into the target you
desire from the temporary table.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca4501c45324$26b7bc00$a601280a@.phx.gbl...
> I'm trying to use the import wizard in a database
> conversion. I get 1 error in this process that states...
> --
> Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
> status 6: Data overflow. Invalid character value for cast
> specification.
> --
> The tables are there after the conversion. The data is
> not. During the import wizard process, I can preview the
> table and the data is in the columns. Only when I go to
> convert does it lose the data.
> If it was the columns that were the problem, I'd re-
> program the code. But I need this data transfered over.
> Please Help

Import Data Error- Insert Error

I'm trying to use the import wizard in a database
conversion. I get 1 error in this process that states...
Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
status 6: Data overflow. Invalid character value for cast
specification.
The tables are there after the conversion. The data is
not. During the import wizard process, I can preview the
table and the data is in the columns. Only when I go to
convert does it lose the data.
If it was the columns that were the problem, I'd re-
program the code. But I need this data transfered over.
Please Help
I'm guessing this is caused because you have some invalid data in your
PAY_PERIOD field in the input record. You need to review your input data
and verify that all data in the PAY_PERIOD field do in fact contain valid
dates. One option would be to put the data into a temporary table where the
PAY_PERIOD is just character data. You could then review the PAY_PERIOD
column in the temporary table and fix up any data that is not a valid date.
Once all the data is fixed you can then insert the data into the target you
desire from the temporary table.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca4501c45324$26b7bc00$a601280a@.phx.gbl...
> I'm trying to use the import wizard in a database
> conversion. I get 1 error in this process that states...
> --
> Insert error, column 23 ('PAY_PERIOD',DBTYPE-DBTIMESTAMP),
> status 6: Data overflow. Invalid character value for cast
> specification.
> --
> The tables are there after the conversion. The data is
> not. During the import wizard process, I can preview the
> table and the data is in the columns. Only when I go to
> convert does it lose the data.
> If it was the columns that were the problem, I'd re-
> program the code. But I need this data transfered over.
> Please Help

Import cell data from XLS into SQL table

I'm trying to use DTS to import data from an XLS into a SQL table.

It works fine in that it INSERT's the data. However, I need it to
UPDATE the table, based upon a ProjectID. Can this be done?

Can a DTS package be fired from a SP using parameters?
Eg UPDATE tProjects SET MyField1=XLS.Sheet1.CellA1,
MyField2=XLS.Sheet2.CellA1 WHERE ProjectID = @.ProjectID.

Also, it must handle dynamic XLS file names, eg 981-Budget.xls,
513-Budget.xls, xyz-Budget.xls

Is this the best way to go? Other suggestions most welcome?

Thanks everyone in advance!Sorry, you also has another question:

"Can a DTS package be fired from a SP using parameters? "

Yes, you have to run the DTSRUn in a cmdshell with XP_cmdshell and hand
over the paramters to global paramerters defined in the DTS package.
(consider the /A switch and look in the BOL for more syntax
information)

HTH, Jens Suessmeyer.|||I would do the querying once and put the data in a temporary table:

<SQLCode>
DECLARE @.Folder varchar(200)
DECLARE @.Filename varchar(200)
DECLARE @.Workbook varchar(200)

DECLARE @.Sqlstring varchar(4000)

SET @.FileName = 'SomeSheet.xls'
SET @.Folder = 'C:\SomeFolder\'
Set @.Workbook = 'SomeWorkbook'

SET @.SQLString = 'SELECT * FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @.Folder + @.FileName +
'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...' +
@.Workbook

Create Table #SomeTable
(
<YourTableDefinitionhere>
)

INSERt INTO #SomeTable
EXEC(@.SQLString)

</Do anything with the data
</SQLCode
HTH, Jens Suessmeyer.

Import + ActiveX (cross)

What I will do is create a temporary table in the same
database with the data from the file.
Next, I will write a script to (import) insert from the
temp table to where you want the data to go.
Mary

>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>Ah, allright - does somebody have code examples I can use?
Best regards
Jakob
"Mary Lou Friend" <anonymous@.discussions.microsoft.com> skrev i en
meddelelse news:4d9301c402bf$698bdf80$a601280a@.phx.gbl...
> What I will do is create a temporary table in the same
> database with the data from the file.
> Next, I will write a script to (import) insert from the
> temp table to where you want the data to go.
> Mary
>
> commaseparated files
> import of ALL info in
> command...
> the file has an ID
> UPDATE all NAME-cols in
> the file might have
> are in the file, they
> but it would be
> perhaps...? or is the
> within an ActiveX task?

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: