MS SQL Server

[David P. Riedel]

Dave Cole wrote:

> >>>>> "Ben" == Ben  <b.e.n. at .r.e.h.a.m.e...c.o.m> writes:
>
> Ben> Hi, Unfortunately I have to upload data to a MS SQL server
> Ben> ... Can anyone recommend a tool for doing this or docs\examples
> Ben> :] ... is it ANSI SQL compliant like MySQL and therefore can use
> Ben> the same commands?
>
> Ben> greatly appreciate any advice
>
> You don't say which platform you are using as the source for the
> data...
>
> The Sybase module which I wrote can be compiled on Linux or NT (so I
> hear) and it has bulkcopy support.  In my limited experience of using
> it to talk to MS SQL server I was able to bulkcopy data into tables
> which did not have IDENTITY columns.  There seems to be some
> difference between Sybase and MS SQL server in their handling of
> that column type.
>
>         http://www.object-craft.com.au/projects/sybase/
>
> An example of bulkcopy from the README file:
>
> >>> import Sybase
> >>>
> >>> data = (('pencils',  5),
> >>>         ('books',  300),
> >>>         ('videos',  11))
> >>>
> >>> db = Sybase.connect('SYBASE', 'user', 'password', bulkcopy = 1, auto_commit = 1)
> >>> db.execute('create table #bogus (name varchar(40), num int)')
> >>>
> >>> bcp = db.bulkcopy('#bogus)
> >>> for line in data:
> >>>     bcp.rowxfer(line)
> >>> print 'Loaded', bcp.done(), 'rows'
>
> The auto_commit is needed because bulkcopy does not work inside a
> transaction.
>
> Sybase 11.0.3 is a free download from http://linux.sybase.com/
> It is free for development and deployment.
>
> If your source data is CSV format then you can use another one of the
> modules I wrote to load it into a format suitable to feeding the
> bulkcopy object in the Sybase module.  The module was written
> specifically to handle data type data produced by Access and Excel.
>
>         http://www.object-craft.com.au/projects/csv/
>
> The above bulkcopy example enhanced to use the csv module would look
> something like this:
>
> >>> import Sybase, csv
> >>>
> >>> db = Sybase.connect('SYBASE', 'user', 'password', bulkcopy = 1, auto_commit = 1)
> >>> db.execute('create table #bogus (name varchar(40), num int)')
> >>>
> >>> p = csv.parser()
> >>> bcp = db.bulkcopy('#bogus')
> >>> for line in open('datafile').readlines():
> >>>     fields = p.parse(line)
> >>>     if fields:
> >>>         bcp.rowxfer(line)
> >>> print 'Loaded', bcp.done(), 'rows'
>
> - Dave
>
> P.S.  Ob.Vapour #1: I have some more appropriate software under
>       development but do not want to announce it until it is ready...
>
>       Ob.Vapour #2: I just tested the above and realised that I had
>       not added support for the optional bulkcopy = 1 argument.  I
>       have since fixed that and it will be in the next release.  Two
>       releases in two days - oops.
>
> --
> http://www.object-craft.com.au

in our shop, we work with ms sql server databases from linux clients using mxodbc and
the easysoftt odbc-odbc bridge.

so far, this has been successful.

dave riedel