Best Way to transfer a table

What is the best way to send someone a table - Complete definition and data.
I have tried creating a new empty database and just add the one table and
back that up but it is a huge file. I tried exporting the rows to Excel but
that will only allow 65K rows. I was surprised to see not option for xml in
E.M.
thanks,
GMS Access? FoxPro? There are dozens of ways to _send_ the data.
However, no matter what you do, unless you send the end user an MSSQL DDL
Script, they'll never really know just _exactly_ how it was in SQL Server.
Why not just send a DDL script and a fixed-width text file?
James Hokes
"Gary" <gb@.nospam.com> wrote in message
news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> What is the best way to send someone a table - Complete definition and
data.
> I have tried creating a new empty database and just add the one table and
> back that up but it is a huge file. I tried exporting the rows to Excel
but
> that will only allow 65K rows. I was surprised to see not option for xml
in
> E.M.
> thanks,
> G
>|||My opinion is that the data should be sent in TDF (tab
delimited) format. Comma-separated is no good - since so
much data can have commas.
TDF's BULK INSERT perfectly.
In query analyzer, do a SELECT * From table and in the
RESULT GRID - select all / copy&paste into NOTEPAD.
This gives you the TDF data in a text file.
If you were to BULK INSERT this back into the table it
would be identical.
We have two little VB programs here called TextImport and
TextOutput that we use constantly. Part of
our "distribute" table data to a customer and back from a
customer. We use these because copy/paste and notepad do
have limitations on width that we have encountered...
>--Original Message--
>MS Access? FoxPro? There are dozens of ways to _send_
the data.
>However, no matter what you do, unless you send the end
user an MSSQL DDL
>Script, they'll never really know just _exactly_ how it
was in SQL Server.
>Why not just send a DDL script and a fixed-width text
file?
>James Hokes
>"Gary" <gb@.nospam.com> wrote in message
>news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> What is the best way to send someone a table -
Complete definition and
>data.
>> I have tried creating a new empty database and just
add the one table and
>> back that up but it is a huge file. I tried exporting
the rows to Excel
>but
>> that will only allow 65K rows. I was surprised to see
not option for xml
>in
>> E.M.
>> thanks,
>> G
>>
>
>.
>|||Even Tab is not bullet-proof.
Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
I'll send you a file that'll break your app right now if you like.
The only bullet-proof text file is a fixed-width one.
Absolutely bullet-proof.
Ask any mainframe DBA.
James Hokes
"Steve z" <szlamany@.antarescomputing.com> wrote in message
news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> My opinion is that the data should be sent in TDF (tab
> delimited) format. Comma-separated is no good - since so
> much data can have commas.
> TDF's BULK INSERT perfectly.
> In query analyzer, do a SELECT * From table and in the
> RESULT GRID - select all / copy&paste into NOTEPAD.
> This gives you the TDF data in a text file.
> If you were to BULK INSERT this back into the table it
> would be identical.
> We have two little VB programs here called TextImport and
> TextOutput that we use constantly. Part of
> our "distribute" table data to a customer and back from a
> customer. We use these because copy/paste and notepad do
> have limitations on width that we have encountered...
>
> >--Original Message--
> >MS Access? FoxPro? There are dozens of ways to _send_
> the data.
> >
> >However, no matter what you do, unless you send the end
> user an MSSQL DDL
> >Script, they'll never really know just _exactly_ how it
> was in SQL Server.
> >
> >Why not just send a DDL script and a fixed-width text
> file?
> >
> >James Hokes
> >
> >"Gary" <gb@.nospam.com> wrote in message
> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> >> What is the best way to send someone a table -
> Complete definition and
> >data.
> >> I have tried creating a new empty database and just
> add the one table and
> >> back that up but it is a huge file. I tried exporting
> the rows to Excel
> >but
> >> that will only allow 65K rows. I was surprised to see
> not option for xml
> >in
> >> E.M.
> >> thanks,
> >> G
> >>
> >>
> >
> >
> >.
> >|||Actually if this is going to another SQL Server I would use the native
format of BCP instead of text.
--
Andrew J. Kelly SQL MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:eiwloA3xDHA.2396@.TK2MSFTNGP09.phx.gbl...
> Even Tab is not bullet-proof.
> Tab is ASCII character 9. Plenty of tabs in varchar fields, I assure you.
> I'll send you a file that'll break your app right now if you like.
> The only bullet-proof text file is a fixed-width one.
> Absolutely bullet-proof.
> Ask any mainframe DBA.
> James Hokes
> "Steve z" <szlamany@.antarescomputing.com> wrote in message
> news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
> > My opinion is that the data should be sent in TDF (tab
> > delimited) format. Comma-separated is no good - since so
> > much data can have commas.
> >
> > TDF's BULK INSERT perfectly.
> >
> > In query analyzer, do a SELECT * From table and in the
> > RESULT GRID - select all / copy&paste into NOTEPAD.
> >
> > This gives you the TDF data in a text file.
> >
> > If you were to BULK INSERT this back into the table it
> > would be identical.
> >
> > We have two little VB programs here called TextImport and
> > TextOutput that we use constantly. Part of
> > our "distribute" table data to a customer and back from a
> > customer. We use these because copy/paste and notepad do
> > have limitations on width that we have encountered...
> >
> >
> > >--Original Message--
> > >MS Access? FoxPro? There are dozens of ways to _send_
> > the data.
> > >
> > >However, no matter what you do, unless you send the end
> > user an MSSQL DDL
> > >Script, they'll never really know just _exactly_ how it
> > was in SQL Server.
> > >
> > >Why not just send a DDL script and a fixed-width text
> > file?
> > >
> > >James Hokes
> > >
> > >"Gary" <gb@.nospam.com> wrote in message
> > >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > >> What is the best way to send someone a table -
> > Complete definition and
> > >data.
> > >> I have tried creating a new empty database and just
> > add the one table and
> > >> back that up but it is a huge file. I tried exporting
> > the rows to Excel
> > >but
> > >> that will only allow 65K rows. I was surprised to see
> > not option for xml
> > >in
> > >> E.M.
> > >> thanks,
> > >> G
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Been a VAX mainframe programmer since late 70's - used
fixed width for 20+ years on the mainframe. Lots of
effort in mapping data - input and output sides - lots of
room for bugs (we always consider the maintenance
nightmare).
We happen to be very familiar with our data - no tabs
anywhere - and we have medical claim tables with 2.5
million rows... (program checks for tab in data anyway -
simple to do)
What we like about our method is that our VB textoutput
program puts dates in a nice format so that BULK INSERT
works without issue. But as an app development shop, we
only support a handful of datatypes - we like the variety
that SQL has, but no reason to beat up the programmers
with each ones quirks...
Our textoutput program also uses an .INI file to specify
server/database/table and even a SELECT statement if you
want to override the defaults of SELECT *. We actually
use it inside the SQL Agent to run nightly jobs that push
text files back to the legacy VAX's at several sites.
The VAX-11 BASIC programs on the mainframe only have to
cutup the input lines by looking for TAB's - so column
issues are the only ones to consider - no mapping of
specific byte positions.
We even have mainframe programs that analyze the legacy
database structures and create .SQL scripts to "CREATE
TABLE's" and text files for BULK INSERT's. We even
include the BULK INSERT statement (with proper server
path to text file) in the .SQL script created on the
mainframe.
We can take a mainframe's application data and get it
into SQL in short order with these tools.
>--Original Message--
>Even Tab is not bullet-proof.
>Tab is ASCII character 9. Plenty of tabs in varchar
fields, I assure you.
>I'll send you a file that'll break your app right now if
you like.
>The only bullet-proof text file is a fixed-width one.
>Absolutely bullet-proof.
>Ask any mainframe DBA.
>James Hokes
>"Steve z" <szlamany@.antarescomputing.com> wrote in
message
>news:018701c3c75e$fca3c3b0$a601280a@.phx.gbl...
>> My opinion is that the data should be sent in TDF (tab
>> delimited) format. Comma-separated is no good - since
so
>> much data can have commas.
>> TDF's BULK INSERT perfectly.
>> In query analyzer, do a SELECT * From table and in the
>> RESULT GRID - select all / copy&paste into NOTEPAD.
>> This gives you the TDF data in a text file.
>> If you were to BULK INSERT this back into the table it
>> would be identical.
>> We have two little VB programs here called TextImport
and
>> TextOutput that we use constantly. Part of
>> our "distribute" table data to a customer and back
from a
>> customer. We use these because copy/paste and notepad
do
>> have limitations on width that we have encountered...
>>
>> >--Original Message--
>> >MS Access? FoxPro? There are dozens of ways to _send_
>> the data.
>> >
>> >However, no matter what you do, unless you send the
end
>> user an MSSQL DDL
>> >Script, they'll never really know just _exactly_ how
it
>> was in SQL Server.
>> >
>> >Why not just send a DDL script and a fixed-width text
>> file?
>> >
>> >James Hokes
>> >
>> >"Gary" <gb@.nospam.com> wrote in message
>> >news:epSLJt0xDHA.1764@.TK2MSFTNGP10.phx.gbl...
>> >> What is the best way to send someone a table -
>> Complete definition and
>> >data.
>> >> I have tried creating a new empty database and just
>> add the one table and
>> >> back that up but it is a huge file. I tried
exporting
>> the rows to Excel
>> >but
>> >> that will only allow 65K rows. I was surprised to
see
>> not option for xml
>> >in
>> >> E.M.
>> >> thanks,
>> >> G
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>