Almost solved : MySQL to MS SQL Server
I am much closer than before:
I compiled FreeTDS from source.
tsql connects no problems
DBD::Sybase compiles no problem and no longer causes errors.
BUT
#!/usr/local/perl
#
use DBI;
use DBD::Sybase;
my $server=192.168.100.2;
my $uid=ss_userid;
my $dbh = DBI-connect(dbi:Sybase:$server, '$uid', '$uid');
die Unable for connect to server $DBI::errstr
unless $dbh;
Gives the error:
DBI connect('192.168.100.2','$uid',...) failed: Server message
number=18456 severity=14 state=1 line=0 text=Login failed for user
'$uid'.OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER
= (46)
Server 192.168.100.2, database
Message String: Login incorrect.
at test1.pl line 9
Unable for connect to server Server message number=18456 severity=14
state=1 line=0 text=Login failed for user '$uid'.OpenClient message: LAYER
= (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (46)
Server 192.168.100.2, database
Message String: Login incorrect.
Re: Almost solved : MySQL to MS SQL Server
I found it..duh!
On Fri, 20 Aug 2004, Joel West wrote:
I am much closer than before:
I compiled FreeTDS from source.
tsql connects no problems
DBD::Sybase compiles no problem and no longer causes errors.
BUT
#!/usr/local/perl
#
use DBI;
use DBD::Sybase;
my $server=192.168.100.2;
my $uid=ss_userid;
my $dbh = DBI-connect(dbi:Sybase:$server, '$uid', '$uid');
die Unable for connect to server $DBI::errstr
unless $dbh;
Gives the error:
DBI connect('192.168.100.2','$uid',...) failed: Server message number=18456
severity=14 state=1 line=0 text=Login failed for user '$uid'.OpenClient
message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (46)
Server 192.168.100.2, database
Message String: Login incorrect.
at test1.pl line 9
Unable for connect to server Server message number=18456 severity=14 state=1
line=0 text=Login failed for user '$uid'.OpenClient message: LAYER = (0)
ORIGIN = (0) SEVERITY = (78) NUMBER = (46)
Server 192.168.100.2, database
Message String: Login incorrect.
Re: Almost solved : MySQL to MS SQL Server
[EMAIL PROTECTED] (Joel West) wrote in
news:[EMAIL PROTECTED]:
my $dbh = DBI-connect(dbi:Sybase:$server, '$uid', '$uid');
Gives the error:
DBI connect('192.168.100.2','$uid',...) failed: Server message
number=18456 severity=14 state=1 line=0 text=Login failed for user
'$uid'.OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78)
NUMBER = (46)
Server 192.168.100.2, database
Message String: Login incorrect.
at test1.pl line 9
Well I should hope that the correct username and password were distinct and
something other than a dollar sign followed by a u, an i, and a d. Watch
them thar quotes. :)
Re: MySQL to MS SQL Server
I tried the below on the RedHat box I am on and got a different error:
DBI connect('driver={SQL
Server};Server=ipaddress ;database=databasename','userid',...) failed: [unixODBC][Driver Manager]
Data source name not found, and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at
dump1.pl line 12
Now I am really lost.
I am using:
Perl 5.8.0
DBD::ODBC 1.09
libiodbc-3.51.2-1
unixODBC-2.2.8-5
libiodbc-admin-3.51.2-1
libiodbc-devel-3.51.2-1
freetds-0.62.1-1
freetds-devel-0.62.1-1
freetds-unixodbc-0.62.1-1
my odbc.ini is:
[ODBC Data Sources]
MSSQL Server = SQLServer
[SQLServer]
Driver = /usr/lib/libtdsodbc.so
Description = MSSQL Server
Host= 192.168.100.2
ServerType = MS SQL 7
FetchBufferSize = 99
UserName= ss_userid
Password= ss_userid
Database= UCP_SUB
ServerOptions =
ConnectOptions =
Options =
ReadOnly= no
[Default]
Driver = /usr/lib/libtdsodbc.so
my odbcinst.ini is:
[SQLServer]
Description = ODBC for MS SQL Server
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so.1
FileUsage =1
# Included in the unixODBC package
#[PostgreSQL]
#Description= ODBC for PostgreSQL
#Driver = /usr/lib/libodbcpsql.so
#Setup = /usr/lib/libodbcpsqlS.so
#FileUsage = 1
# Driver from the MyODBC package
# Setup from the unixODBC package
#[MySQL]
#Description= ODBC for MySQL
#Driver = /usr/lib/libmyodbc.so
#Setup = /usr/lib/libodbcmyS.so
#FileUsage = 1
I am still horribly lost.
On Wed, 18 Aug 2004, amonotod wrote:
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/18 Wed PM 05:33:54 GMT
my $dsn = DBI:ODBC:databasename:ipaddress;
(Assuming you're on Windows...)
my $dsn = DBI:ODBC:driver={SQL Server};Server=ipaddress;database=databasename;;
Joel (Yossel) West
HTH,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
Re: MySQL to MS SQL Server
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/19 Thu PM 12:24:30 GMT
Server};Server=ipaddress ;database=databasename','userid',...) failed:
[unixODBC][Driver Manager]
Data source name not found, and no default driver specified (SQL-IM002)(DBD:
db_login/SQLConnect err=-1) at
dump1.pl line 12
Now I am really lost.
I can tell you that you are missing a trailing semi-colon after the
;database=databasename', it should be ;database=databasename;', but
other than that, I just can't help you. I imagine the 'driver={SQL Server};
syntax is not valid under Linux, but cannot tell you what is.
Perl 5.8.0
DBD::ODBC 1.09
libiodbc-3.51.2-1
unixODBC-2.2.8-5
libiodbc-admin-3.51.2-1
libiodbc-devel-3.51.2-1
freetds-0.62.1-1
freetds-devel-0.62.1-1
freetds-unixodbc-0.62.1-1
I think, however, that you should look at using the DBD::Sybase
module, which includes a README.freetds file. I've seen mention of
using the FreeTDS drivers wtih DBD::Sybase to connect to MS SQL from
Unix/Linux. Perhaps someone with more knowledge in this area can help...
I am still horribly lost.
Well, good luck; sorry I'm not of more direct help...
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
RE: MySQL to MS SQL Server
I tried the below on the RedHat box I am on and got a different error:
DBI connect('driver={SQL
Server};Server=ipaddress
;database=databasename','userid',...) failed:
[unixODBC][Driver Manager] Data source name not found, and no
default driver specified (SQL-IM002)(DBD: db_login/SQLConnect
err=-1) at
dump1.pl line 12
Now I am really lost.
That message is because unixODBC can't find a DSN or a SQL Server driver
or a DSN.
Here's what I would suggest (you may have done some of this already):
1) Test that freeTDS itself (without the ODBC layer) can connect to
the server. I suggest you download the tarball and look at the
documentation, as I don't know what documentation comes with the RPMS.
There is a .freetds (or something similar) that I've had to use to get
connectivity.
2) When you get freeTDS working, then test that freeTDS's ODBC part
is working. I believe there are some ODBC tests in the freeTDS distro, but
it's been a while (you may need to setup a DSN for this).
3) Then -- use unixODBC to setup a DSN and test with isql, which
comes with UnixODBC.
4) From there, you should be able to connect to your DSN with
DBD::ODBC.
*** NOTE: You *may* have an easier time with DBD::Sybase when using FreeTDS.
*** The ODBC layer on top of FreeTDS is constantly evolving, so your
mileage may vary.
I am using:
Perl 5.8.0
DBD::ODBC 1.09
libiodbc-3.51.2-1
Be careful here. Iodbc and unixODBC are two different beasts that,
generally do the same thing [provide an ODBC driver manager].
unixODBC-2.2.8-5
libiodbc-admin-3.51.2-1
libiodbc-devel-3.51.2-1
freetds-0.62.1-1
freetds-devel-0.62.1-1
freetds-unixodbc-0.62.1-1
my odbc.ini is:
[ODBC Data Sources]
MSSQL Server = SQLServer
Note, in connect above, you indicated SQL Server with a space and in this
document, no space...
Jeff
[SQLServer]
Driver = /usr/lib/libtdsodbc.so
Description = MSSQL Server
Host= 192.168.100.2
ServerType = MS SQL 7
FetchBufferSize = 99
UserName= ss_userid
Password= ss_userid
Database= UCP_SUB
ServerOptions =
ConnectOptions =
Options =
ReadOnly= no
[Default]
Driver = /usr/lib/libtdsodbc.so
my odbcinst.ini is:
[SQLServer]
Description = ODBC for MS SQL Server
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so.1
FileUsage =1
# Included in the unixODBC package
#[PostgreSQL]
#Description= ODBC for PostgreSQL
#Driver = /usr/lib/libodbcpsql.so
#Setup = /usr/lib/libodbcpsqlS.so
#FileUsage = 1
# Driver from the MyODBC package
# Setup from the unixODBC package
#[MySQL]
#Description= ODBC for MySQL
#Driver = /usr/lib/libmyodbc.so
#Setup = /usr/lib/libodbcmyS.so
#FileUsage = 1
I am still horribly lost.
On Wed, 18 Aug 2004, amonotod wrote:
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/18 Wed PM 05:33:54 GMT
my $dsn = DBI:ODBC:databasename:ipaddress;
(Assuming you're on Windows...)
my $dsn = DBI:ODBC:driver={SQL
Server};Server=ipaddress;database=databasename;;
Joel (Yossel) West
HTH,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
RE: MySQL to MS SQL Server
So
I compiled FreeTDS from souce.
tsql - The FreeTDS utility connects okay.
unixODBC would not compile so I am using iODBC
DBD::Sybase compiles lovely and gives the following error if used:
[EMAIL PROTECTED] joelonx]# perl -w
use DBD::Sybase;
Can't load
'/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.so'
for module DBD::Sybase:
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.so:
undefined symbol: bsd_tcp at
/usr/lib/perl5/5.8.0/i386-linux-thread-multi/DynaLoader.pm line 229.
at - line 1
Compilation failed in require at - line 1.
BEGIN failed--compilation aborted at - line 1.
So I am using DBD::ODBC
My odbc.ini is:
;
; odbc.ini
;
[ODBC Data Sources]
FreeTDS = FreeTDS
[OpenLink]
Driver = /usr/local/lib/libtdsodbc.so
Description = MS SQL Driver
Host= 192.168.100.2
ServerType = MSSQL
FetchBufferSize = 99
UserName= ss_userid
Password= ss_userid
Database= UCP_SUB
ServerOptions =
ConnectOptions =
Options =
ReadOnly= no
[Default]
Driver = /home/local/lib/libtdsodbc.so
My odbcinst.ini is:
# Example driver definitinions
#
#
[FreeTDS]
Description = ODBC for SQLServer
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsodbc.so.1
FileUsage = 1
My perl script:
#!/usr/bin/perl
use DBI;
use DBD::ODBC;
my $DSN = 'dbi:ODBC:FreeTDS:192.168.100.2:UCP_SUB:ss_username:ss_username';
my $dbh = DBI-connect(dbi:ODBC:$DSN) or die $DBI::errstr\n;
And the command line:
[EMAIL PROTECTED] joelonx]# perl -w dumpy.pl
DBI
connect('dbi:ODBC:FreeTDS:192.168.100.2:UCP_SUB:ss_username:ss_username','',...)
failed: [iODBC][Driver Manager]Data source name not found and no default
driver specified. Driver could not be loaded (SQL-IM002)(DBD:
db_login/SQLConnect err=-1) at dumpy.pl line 8
[iODBC][Driver Manager]Data source name not found and no default driver
specified. Driver could not be loaded (SQL-IM002)(DBD: db_login/SQLConnect
err=-1)
Again...help!
On Thu, 19 Aug 2004, Jeff Urlwin wrote:
I tried the below on the RedHat box I am on and got a different error:
DBI connect('driver={SQL
Server};Server=ipaddress
;database=databasename','userid',...) failed:
[unixODBC][Driver Manager] Data source name not found, and no
default driver specified (SQL-IM002)(DBD: db_login/SQLConnect
err=-1) at
dump1.pl line 12
Now I am really lost.
That message is because unixODBC can't find a DSN or a SQL Server driver
or a DSN.
Here's what I would suggest (you may have done some of this already):
1) Test that freeTDS itself (without the ODBC layer) can connect to
the server. I suggest you download the tarball and look at the
documentation, as I don't know what documentation comes with the RPMS.
There is a .freetds (or something similar) that I've had to use to get
connectivity.
2) When you get freeTDS working, then test that freeTDS's ODBC part
is working. I believe there are some ODBC tests in the freeTDS distro, but
it's been a while (you may need to setup a DSN for this).
3) Then -- use unixODBC to setup a DSN and test with isql, which
comes with UnixODBC.
4) From there, you should be able to connect to your DSN with
DBD::ODBC.
*** NOTE: You *may* have an easier time with DBD::Sybase when using FreeTDS.
*** The ODBC layer on top of FreeTDS is constantly evolving, so your
mileage may vary.
I am using:
Perl 5.8.0
DBD::ODBC 1.09
libiodbc-3.51.2-1
Be careful here. Iodbc and unixODBC are two different beasts that,
generally do the same thing [provide an ODBC driver manager].
unixODBC-2.2.8-5
libiodbc-admin-3.51.2-1
libiodbc-devel-3.51.2-1
freetds-0.62.1-1
freetds-devel-0.62.1-1
freetds-unixodbc-0.62.1-1
my odbc.ini is:
[ODBC Data Sources]
MSSQL Server = SQLServer
Note, in connect above, you indicated SQL Server with a space and in this
document, no space...
Jeff
[SQLServer]
Driver = /usr/lib/libtdsodbc.so
Description = MSSQL Server
Host= 192.168.100.2
ServerType = MS SQL 7
FetchBufferSize = 99
UserName= ss_userid
Password= ss_userid
Database= UCP_SUB
ServerOptions =
ConnectOptions =
Options =
ReadOnly= no
[Default]
Driver = /usr/lib/libtdsodbc.so
my odbcinst.ini is:
[SQLServer]
Description = ODBC for MS SQL Server
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so.1
FileUsage =1
# Included in the unixODBC package
#[PostgreSQL]
#Description= ODBC for PostgreSQL
#Driver = /usr/lib/libodbcpsql.so
#Setup = /usr/lib/libodbcpsqlS.so
#FileUsage = 1
# Driver from the MyODBC package
# Setup from the unixODBC package
#[MySQL]
#Description= ODBC for MySQL
#Driver = /usr/lib/libmyodbc.so
#Setup = /usr/lib/libodbcmyS.so
#FileUsage = 1
I am still horribly lost.
On Wed, 18 Aug 2004, amonotod wrote:
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/18 Wed PM 05:33:54 GMT
my $dsn =
MySQL to MS SQL Server
Greetings,
I've read what I can so far about the topic and have resisted posting but
I am at a dead end.
I need to move data from a mysql server to an MS SQL server on another
machine. Retrieving the data from the mysql server is easy. I had that
done in a few minutes using DBI.
Now I am attempting to use DBD::ODBC to transfer the information to an MS
SQL server on the network.
Here is the code I am using:
#!/usr/bin/perl -w
use DBI;
use DBD::ODBC;
my $dsn = DBI:ODBC:databasename:ipaddress;
my $uid = uid;
my $pwd = pwd;
my $dbh = DBI-connect($dsn, $uid, $pwd, { RaiseError = 1, odbc_cursortype = 2}) or die
DBI::errstr \n;
At this point I consistently get the same error:
DBI connect('databasename:ipaddress','uid',...) failed: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
(SQL-08001)(DBD: db_login/SQLConnect err=-1) at dump1.pl line 12
Please help.
Joel (Yossel) West
Re: MySQL to MS SQL Server
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/18 Wed PM 05:33:54 GMT
my $dsn = DBI:ODBC:databasename:ipaddress;
(Assuming you're on Windows...)
my $dsn = DBI:ODBC:driver={SQL Server};Server=ipaddress;database=databasename;;
Joel (Yossel) West
HTH,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
Re: MySQL to MS SQL Server
Actually I'm on Linux.
- Original Message -
From: amonotod [EMAIL PROTECTED]
To: Joel West [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 5:42 PM
Subject: Re: MySQL to MS SQL Server
From: Joel West [EMAIL PROTECTED]
Date: 2004/08/18 Wed PM 05:33:54 GMT
my $dsn = DBI:ODBC:databasename:ipaddress;
(Assuming you're on Windows...)
my $dsn = DBI:ODBC:driver={SQL
Server};Server=ipaddress;database=databasename;;
Joel (Yossel) West
HTH,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
