[freetds] FreeBCP to SQL Azure complains table is missing?

[Cade Roux]

Of course SQL Azure only lets you connect to one database, so I think
there is some sort of confusion here.

Has anyone used FreeBCP to connect to SQL Azure?

$ freebcp DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
serverfromfreetds -U user at azureserver -P password
Msg 208, Level 16, State 1
Server 'azureserver', Line 1
Invalid object name 'DWSTAGE.BCPTEST'.
Msg 208, Level 16
General SQL Server error: Check messages from the SQL Server

Msg 20064, Level 2
Attempt to use Bulk Copy with a non-existent Server table

$ freebcp DATABASENAME.DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
serverfromfreetds -U user at azureserver -P password
Msg 40515, Level 15, State 1
Server 'azureserver', Line 16
Reference to database and/or server name in
'DATABASENAME.DWSTAGE.BCPTEST' is not supported in this version of SQL
Server.
Msg 40515, Level 15
General SQL Server error: Check messages from the SQL Server

Msg 20064, Level 2
Attempt to use Bulk Copy with a non-existent Server table


I've also tried adding the database to the command line with the -D
option. The default database for that connection is set up as this one
and only Azure database in the freetds.conf.

The connection to SQL Azure seems fine otherwise - I just can't get
FreeBCP to work:

$ isql serverfromfreetds user at azuredatabasename password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTEST;
+------------+
| |
+------------+
| 0 |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTESTX;
[ISQL]ERROR: Could not SQLExecute
SQL>

This seems like some database/schema confusion, but I can't find a
combination of settings which works.



Cade Roux
cade at roux.org
504-717-4887

[Frediano Ziglio]

2012/11/19 Cade Roux <cade at roux.org>:
> Of course SQL Azure only lets you connect to one database, so I think
> there is some sort of confusion here.
>
> Has anyone used FreeBCP to connect to SQL Azure?
>
> $ freebcp DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
> serverfromfreetds -U user at azureserver -P password
> Msg 208, Level 16, State 1
> Server 'azureserver', Line 1
> Invalid object name 'DWSTAGE.BCPTEST'.
> Msg 208, Level 16
> General SQL Server error: Check messages from the SQL Server
>
> Msg 20064, Level 2
> Attempt to use Bulk Copy with a non-existent Server table
>
> $ freebcp DATABASENAME.DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
> serverfromfreetds -U user at azureserver -P password
> Msg 40515, Level 15, State 1
> Server 'azureserver', Line 16
> Reference to database and/or server name in
> 'DATABASENAME.DWSTAGE.BCPTEST' is not supported in this version of SQL
> Server.
> Msg 40515, Level 15
> General SQL Server error: Check messages from the SQL Server
>
> Msg 20064, Level 2
> Attempt to use Bulk Copy with a non-existent Server table
>
>
> I've also tried adding the database to the command line with the -D
> option. The default database for that connection is set up as this one
> and only Azure database in the freetds.conf.
>
> The connection to SQL Azure seems fine otherwise - I just can't get
> FreeBCP to work:
>
> $ isql serverfromfreetds user at azuredatabasename password
> +---------------------------------------+
> | Connected! |
> | |
> | sql-statement |
> | help [tablename] |
> | quit |
> | |
> +---------------------------------------+
> SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTEST;
> +------------+
> | |
> +------------+
> | 0 |
> +------------+
> SQLRowCount returns 1
> 1 rows fetched
> SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTESTX;
> [ISQL]ERROR: Could not SQLExecute
> SQL>
>
> This seems like some database/schema confusion, but I can't find a
> combination of settings which works.
>
>
>
> Cade Roux
> cade at roux.org
> 504-717-4887

Hi,
I thinkg freebcp just load the data into the table, you have to
create the table before running freebcp

Frediano

[Cade Roux]

The table exists - I included the example from isql doing a select at the
end without error.
On Nov 20, 2012 3:15 AM, "Frediano Ziglio" <freddy77 at gmail.com> wrote:

> 2012/11/19 Cade Roux <cade at roux.org>:
> > Of course SQL Azure only lets you connect to one database, so I think
> > there is some sort of confusion here.
> >
> > Has anyone used FreeBCP to connect to SQL Azure?
> >
> > $ freebcp DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
> > serverfromfreetds -U user at azureserver -P password
> > Msg 208, Level 16, State 1
> > Server 'azureserver', Line 1
> > Invalid object name 'DWSTAGE.BCPTEST'.
> > Msg 208, Level 16
> > General SQL Server error: Check messages from the SQL Server
> >
> > Msg 20064, Level 2
> > Attempt to use Bulk Copy with a non-existent Server table
> >
> > $ freebcp DATABASENAME.DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
> > serverfromfreetds -U user at azureserver -P password
> > Msg 40515, Level 15, State 1
> > Server 'azureserver', Line 16
> > Reference to database and/or server name in
> > 'DATABASENAME.DWSTAGE.BCPTEST' is not supported in this version of SQL
> > Server.
> > Msg 40515, Level 15
> > General SQL Server error: Check messages from the SQL Server
> >
> > Msg 20064, Level 2
> > Attempt to use Bulk Copy with a non-existent Server table
> >
> >
> > I've also tried adding the database to the command line with the -D
> > option. The default database for that connection is set up as this one
> > and only Azure database in the freetds.conf.
> >
> > The connection to SQL Azure seems fine otherwise - I just can't get
> > FreeBCP to work:
> >
> > $ isql serverfromfreetds user at azuredatabasename password
> > +---------------------------------------+
> > | Connected! |
> > | |
> > | sql-statement |
> > | help [tablename] |
> > | quit |
> > | |
> > +---------------------------------------+
> > SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTEST;
> > +------------+
> > | |
> > +------------+
> > | 0 |
> > +------------+
> > SQLRowCount returns 1
> > 1 rows fetched
> > SQL> SELECT COUNT(*) FROM DWSTAGE.BCPTESTX;
> > [ISQL]ERROR: Could not SQLExecute
> > SQL>
> >
> > This seems like some database/schema confusion, but I can't find a
> > combination of settings which works.
> >
> >
> >
> > Cade Roux
> > cade at roux.org
> > 504-717-4887
>
> Hi,
> I thinkg freebcp just load the data into the table, you have to
> create the table before running freebcp
>
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>

[James K. Lowden]

On Sun, 18 Nov 2012 20:16:59 -0600
Cade Roux <cade at roux.org> wrote:

> Has anyone used FreeBCP to connect to SQL Azure?
>
> $ freebcp DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
> serverfromfreetds -U user at azureserver -P password
> Msg 208, Level 16, State 1
> Server 'azureserver', Line 1
> Invalid object name 'DWSTAGE.BCPTEST'.
> Msg 208, Level 16
> General SQL Server error: Check messages from the SQL Server

The message 208 comes from the server. A quick look at freebcp.c shows
argv[1] isn't parsed. It's copied to a struct and used verbatim e.g.

if (dbfcmd(dbproc, "SET FMTONLY ON select *
from %s SET FMTONLY OFF", pdata->dbobject) == FAIL)

My guess is that the account you're logging in with has a default
database, and that database is not the one containing DWSTAGE.BCPTEST.
The Azure server rejects dbname.schema.object syntax, and freebcp has
no -D option because until Azure every TDS server did accept that
syntax.

You could verify that using

$ freebcp 'select db_name()' queryout /dev/stdout ...

As a temporary workaround, I think this would work:

freebcp DWSTAGE.BCPTEST in bcptest.txt \
-O 'USE dbname' \
-f cdr.fmt -S serverfromfreetds -U user at azureserver -P password

A permanent fix would support -D.

HTH.

--jkl

[Cade Roux]

Yes, that user's default database is probably master. There IS a
default database set up in the odbc config, but I was mistaken, there
is no such option in the freeetds.conf.

We've moved away from trying to get Linux to work for this process for
now, but I'll revisit this.

I expect that workaround will not work because USE isn't supported -
you do in fact have to connect directly to the database, because of
the nature of the SQL Azure architecture.

Cade
Cade Roux
cade at roux.org
504-717-4887


On Tue, Nov 20, 2012 at 8:51 PM, James K. Lowden <jklowden at freetds.org> wrote:
> On Sun, 18 Nov 2012 20:16:59 -0600
> Cade Roux <cade at roux.org> wrote:
>
>> Has anyone used FreeBCP to connect to SQL Azure?
>>
>> $ freebcp DWSTAGE.BCPTEST in bcptest.txt -f cdr.fmt -S
>> serverfromfreetds -U user at azureserver -P password
>> Msg 208, Level 16, State 1
>> Server 'azureserver', Line 1
>> Invalid object name 'DWSTAGE.BCPTEST'.
>> Msg 208, Level 16
>> General SQL Server error: Check messages from the SQL Server
>
> The message 208 comes from the server. A quick look at freebcp.c shows
> argv[1] isn't parsed. It's copied to a struct and used verbatim e.g.
>
> if (dbfcmd(dbproc, "SET FMTONLY ON select *
> from %s SET FMTONLY OFF", pdata->dbobject) == FAIL)
>
> My guess is that the account you're logging in with has a default
> database, and that database is not the one containing DWSTAGE.BCPTEST.
> The Azure server rejects dbname.schema.object syntax, and freebcp has
> no -D option because until Azure every TDS server did accept that
> syntax.
>
> You could verify that using
>
> $ freebcp 'select db_name()' queryout /dev/stdout ...
>
> As a temporary workaround, I think this would work:
>
> freebcp DWSTAGE.BCPTEST in bcptest.txt \
> -O 'USE dbname' \
> -f cdr.fmt -S serverfromfreetds -U user at azureserver -P password
>
> A permanent fix would support -D.
>
> HTH.
>
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Tue, 20 Nov 2012 21:04:39 -0600
Cade Roux <cade at roux.org> wrote:

> I expect that workaround will not work because USE isn't supported -
> you do in fact have to connect directly to the database, because of
> the nature of the SQL Azure architecture.

Yes, you're right. I forgot about that.

About a year ago we added the DBSETLDBNAME macro as a way to set the
dbname in the db-lib LOGINREC. That sets the dbname in the login
packet, obviating the need for "USE dbname". freebcp could be modified
to support that feature with a -D option.

--jkl

[Ken Collins]

I have not hit that need yet, but being able to use everything from tsql to freebcp with Azure would be cool.

- Ken

On Nov 20, 2012, at 11:03 PM, "James K. Lowden" <jklowden at freetds.org> wrote:

> On Tue, 20 Nov 2012 21:04:39 -0600
> Cade Roux <cade at roux.org> wrote:
>
>> I expect that workaround will not work because USE isn't supported -
>> you do in fact have to connect directly to the database, because of
>> the nature of the SQL Azure architecture.
>
> Yes, you're right. I forgot about that.
>
> About a year ago we added the DBSETLDBNAME macro as a way to set the
> dbname in the db-lib LOGINREC. That sets the dbname in the login
> packet, obviating the need for "USE dbname". freebcp could be modified
> to support that feature with a -D option.
>
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

[Frediano Ziglio]

See change

http://gitorious.org/freetds/freetds/commit/4a21ded022405693607e71938d0c6173816f5ff9/diffs/c34afafd2fec4cbba9b245e4f13a5471c6fb8041

(add support for -D in freebcp)

Frediano

2012/11/21 Ken Collins <ken at metaskills.net>:
>
> I have not hit that need yet, but being able to use everything from tsql to freebcp with Azure would be cool.
>
> - Ken
>
> On Nov 20, 2012, at 11:03 PM, "James K. Lowden" <jklowden at freetds.org> wrote:
>
>> On Tue, 20 Nov 2012 21:04:39 -0600
>> Cade Roux <cade at roux.org> wrote:
>>
>>> I expect that workaround will not work because USE isn't supported -
>>> you do in fact have to connect directly to the database, because of
>>> the nature of the SQL Azure architecture.
>>
>> Yes, you're right. I forgot about that.
>>
>> About a year ago we added the DBSETLDBNAME macro as a way to set the
>> dbname in the db-lib LOGINREC. That sets the dbname in the login
>> packet, obviating the need for "USE dbname". freebcp could be modified
>> to support that feature with a -D option.
>>
>> --jkl
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

[Ken Collins]

Awesome! When will the build system update these files and to which version numbers?
ftp://ftp.astron.com/pub/freetds/

- Ken

On Nov 21, 2012, at 9:37 AM, Frediano Ziglio <freddy77 at gmail.com> wrote:

> See change
>
> http://gitorious.org/freetds/freetds/commit/4a21ded022405693607e71938d0c6173816f5ff9/diffs/c34afafd2fec4cbba9b245e4f13a5471c6fb8041
>
> (add support for -D in freebcp)
>
> Frediano
>
> 2012/11/21 Ken Collins <ken at metaskills.net>:
>>
>> I have not hit that need yet, but being able to use everything from tsql to freebcp with Azure would be cool.
>>
>> - Ken
>>
>> On Nov 20, 2012, at 11:03 PM, "James K. Lowden" <jklowden at freetds.org> wrote:
>>
>>> On Tue, 20 Nov 2012 21:04:39 -0600
>>> Cade Roux <cade at roux.org> wrote:
>>>
>>>> I expect that workaround will not work because USE isn't supported -
>>>> you do in fact have to connect directly to the database, because of
>>>> the nature of the SQL Azure architecture.
>>>
>>> Yes, you're right. I forgot about that.
>>>
>>> About a year ago we added the DBSETLDBNAME macro as a way to set the
>>> dbname in the db-lib LOGINREC. That sets the dbname in the login
>>> packet, obviating the need for "USE dbname". freebcp could be modified
>>> to support that feature with a -D option.
>>>
>>> --jkl
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS at lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

[phineas vang]

Hi,

I have to convert a sql 2005 database to sql 2000 to run a
perl script with the win 32: :odbc connection. This is a repetitive
process. I do not know anything about perl coding. Is Free TDS the
correct tool for me to be able to run my perl scrip from sql 2005 rather
than having to convert it sql 2000?

Thanks in advance!

> From: ken at metaskills.net
> Date: Wed, 21 Nov 2012 10:05:23 -0500
> To: freetds at lists.ibiblio.org
> Subject: Re: [freetds] FreeBCP to SQL Azure complains table is missing?
>
>
> Awesome! When will the build system update these files and to which version numbers?
> ftp://ftp.astron.com/pub/freetds/
>
> - Ken
>
> On Nov 21, 2012, at 9:37 AM, Frediano Ziglio <freddy77 at gmail.com> wrote:
>
> > See change
> >
> > http://gitorious.org/freetds/freetds/commit/4a21ded022405693607e71938d0c6173816f5ff9/diffs/c34afafd2fec4cbba9b245e4f13a5471c6fb8041
> >
> > (add support for -D in freebcp)
> >
> > Frediano
> >
> > 2012/11/21 Ken Collins <ken at metaskills.net>:
> >>
> >> I have not hit that need yet, but being able to use everything from tsql to freebcp with Azure would be cool.
> >>
> >> - Ken
> >>
> >> On Nov 20, 2012, at 11:03 PM, "James K. Lowden" <jklowden at freetds.org> wrote:
> >>
> >>> On Tue, 20 Nov 2012 21:04:39 -0600
> >>> Cade Roux <cade at roux.org> wrote:
> >>>
> >>>> I expect that workaround will not work because USE isn't supported -
> >>>> you do in fact have to connect directly to the database, because of
> >>>> the nature of the SQL Azure architecture.
> >>>
> >>> Yes, you're right. I forgot about that.
> >>>
> >>> About a year ago we added the DBSETLDBNAME macro as a way to set the
> >>> dbname in the db-lib LOGINREC. That sets the dbname in the login
> >>> packet, obviating the need for "USE dbname". freebcp could be modified
> >>> to support that feature with a -D option.
> >>>
> >>> --jkl
> >>> _______________________________________________
> >>> FreeTDS mailing list
> >>> FreeTDS at lists.ibiblio.org
> >>> http://lists.ibiblio.org/mailman/listinfo/freetds
> >>
> >> _______________________________________________
> >> FreeTDS mailing list
> >> FreeTDS at lists.ibiblio.org
> >> http://lists.ibiblio.org/mailman/listinfo/freetds
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS at lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds