DTS from SQL Server to DB2

I tried to copy tables from SQL Server 2000 to DB2 UDB by using DTS
package through ODBC, but always got an error that says:

"The number of failing rows exceeds the maximum specified.
[IBM][CLI Driver] CLI0150E Driver not capable, SQLSTATE=S1C00"

However, I can copy tables inversely from DB2 to SQL Server without
problem, and I can also copy tables to other non DB2 databases through
DTS.

DB2 Client is properly installed on SQL Server side, and can access to
DB2 Database successfully.

When I run DTS package, it can talk to DB2 database, and create a target
table (i.e. no connection problem), but failed to add data into the
table.

The problem appears after we upgraded Windows 2000 Server Service Pack
from SP2 to SP4. According to the error message, it seems that DB2 CLI
Driver is not happy about something.

Can anybody shed some light on the issue?

Thanks a lot

Jian

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGI am having a similar problem.

When using DTS designer when I click on the destination tab of the
data pump I get this error:
Error Description: Unspecified error
[IBM][CLI Driver][DB2/AIX64] SQL1131N DARI (Stored Procedure) process
has been terminated abnormally. SQLSTATE=38503

But the DB2 table comes up fine.

Then when I run the dts package I get this error:
The number of failing rows exceeds the maximum specified. [IBM][CLI
Driver] CLI0150E Driver not capable. SQLSTATE=S1C00

Does anyone have any ideas how to resolve this?

Thanks,
Sarah Larson|||I have just completed a migration to a new SQL Server and this problem
has reared its ugly head. I can continue to import data to SQL Server
from DB2, I can run an 'Execute SQL Task' to delete data from a DB2
table, but any attempt to insert fails instantly with 'The number of
failing rows exceeds the maximum amount specified.

The 'old' server was at W2K Advanced Server SP2 with SQL Server 2000
Ent at SP3 and ran exports to DB2 without issue.

The new server is W2K Advanced Server SP4 with SQL Server 2000 Ent
also at SP3.

It does seem that the W2K SP4 could be the cause of the problem and if
anyone has a solution to this issue I should be very grateful to know.

Regards

Clare Hilditch|||I had the same issue and resolved it by changing the connection type in DTS from the regular ODBC driver to IBM OLE DB Provider for DB2. Everything worked after that change.

DTS- Destination Column Name has spaces causing error

I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?

I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.

When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Use square brackets ( [ ] ) , instead of double quotes.|||

I tried this in a sql query against the Advantage ODBC and it failed. I believe this is a limitation of the ODBC drivers for Advantage.

The issus that the column names are causing this to fail inside the DTS Transformations themselves. Anyone know where I can edit the Destination column names so that I can wrap them with quotes or brackets.

Thanks

Donnie

|||

Have you tried enclosing the column names in single quotes instead of Microsoft brackets?,

i.e. SELECT 'My Column' FROM MyTable

|||

Yes tried single quotes.

If I try Insert into TableName ("Last Name") values ('Test') with the Advantage ODBC, it works. Issue is, I need to pull from SQL and push into Advantage. When I try with with transformations between the source and desitination, I get the missing closing ")" error.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Thanks for the help

Donnie

|||

This forum is for SSIS, not DTS.

You'll probably get better assistance on the DTS newsgroup.

|||Thanks DD. Got it over there as well as Google forums.|||Bumpsql

Dts

Hi,
I have a dts package that I created which uses an odbc connection to go and
copy a dbf file using visual fox pro drivers to a sql server database. The p
ackage runs fine when I execute it manually. But whenever I try to use the s
ql server agent(schedule) to run it as a job, it doesn't work. I realy dont
know what to do...
Thanks for the helpCan anyone help me ?

DSN's do not display in ODBC Data Source Administrator

I'm currently having to create a raft of DB2 connections in a
reasonable hurry. In addition, this list has to be propogated to three
other workstations (ultimate goal is to have DBArtisan access the
servers, instances and databases in an identical manner without having
to spend days doing this manually.)
I've figured out that the registry keys
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
allow me to specify the System DSN's so that they can be picked up by
the ODBC Data Source Administration application.
Unfortuantely, I think that there's a hidden limit in how much data
the tool can handle. Somewhere around 155 of the 219 DSN's, one row
gets chopped.
Instead of a line that reads
server.instance.database IBM DB2 ODBC DRIVER
I get variants such as the latest:
server.insta <rest of line is blank>
Looks like a classic buffer reaches max-size, burp and do nothing but
continue running.
BTW, this is only the tip of the iceberg, not only are there going to
be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
Informix, and Oracle, plus a few other antique clangers and
boat-anchors.
A follow-up question, would shortening the name of the ODBC server have any
impact? (eg, just DB2)
Thanks in advance...
BruceHi Bruce,
Just wanted to ask if you have already installed the ODBC drivers for
all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
Regards,
Ashok Rautela
"Byrocat" <strikemaster2000@.yahoo.ca> wrote in message news:<CM50c.68204$ee3.635958@.news20.
bellglobal.com>...
> I'm currently having to create a raft of DB2 connections in a
> reasonable hurry. In addition, this list has to be propogated to three
> other workstations (ultimate goal is to have DBArtisan access the
> servers, instances and databases in an identical manner without having
> to spend days doing this manually.)
> I've figured out that the registry keys
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
> and
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
> allow me to specify the System DSN's so that they can be picked up by
> the ODBC Data Source Administration application.
> Unfortuantely, I think that there's a hidden limit in how much data
> the tool can handle. Somewhere around 155 of the 219 DSN's, one row
> gets chopped.
> Instead of a line that reads
> server.instance.database IBM DB2 ODBC DRIVER
> I get variants such as the latest:
> server.insta <rest of line is blank>
> Looks like a classic buffer reaches max-size, burp and do nothing but
> continue running.
> BTW, this is only the tip of the iceberg, not only are there going to
> be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
> Informix, and Oracle, plus a few other antique clangers and
> boat-anchors.
> A follow-up question, would shortening the name of the ODBC server have an
y
> impact? (eg, just DB2)
> Thanks in advance...
> Bruce|||arautela@.openlinksw.co.uk (Ashok Rautela) wrote in message news:<f5f56ae7.0403020312.44da8d
0a@.posting.google.com>...
> Hi Bruce,
> Just wanted to ask if you have already installed the ODBC drivers for
> all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
> Regards,
> Ashok Rautela
>
I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
I don't think that that is the problem. I've played with the list
entries (having created and recreated the list via the Registry), and
can get the cut-off point to change locations.
Defintiely points to the buffer being hard-coded.
Did have an interesting suggestion... Could the MS ODBC Administrator
make use of a registry entry to determine the maximum size of its
buffer and can this be changed?|||I remember a posting from someone at Microsoft that the
limitation is the combined length of the DSNs cannot exceed
4096 characters. So if you have DSN names that are all 409
characters long, you can only have 10 of those that will
display in the ODBC Data Source Administrator. If you create
more DSNs after this, they won't show up in the
Administrator applet but will be written to the registry and
will be available for use by applications.
-Sue
On 2 Mar 2004 09:07:38 -0800, bdealhoy@.sympatico.ca
(Byrocat) wrote:

>I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
>I don't think that that is the problem. I've played with the list
>entries (having created and recreated the list via the Registry), and
>can get the cut-off point to change locations.
>Defintiely points to the buffer being hard-coded.
>Did have an interesting suggestion... Could the MS ODBC Administrator
>make use of a registry entry to determine the maximum size of its
>buffer and can this be changed?

DSN-less connection

I am looking to try and connect to SQL Server via a DSN-less
connection in a VB application.
Setting up ODBC data sources is a headache for each user workstation.
There has to be an easier way.
Is there a straight forward way to do that or do you know a good
reference source that would have information on this topic?

Thank you.RLN (rlntemp-newsgroup@.yahoo.com) writes:

Quote:

Originally Posted by

I am looking to try and connect to SQL Server via a DSN-less
connection in a VB application.
Setting up ODBC data sources is a headache for each user workstation.
There has to be an easier way.
Is there a straight forward way to do that or do you know a good
reference source that would have information on this topic?

What is the problem?

What means of authentication do you use? SQL Server authentication or
Windows authentication? Is the server always the one and the same? What is
the database?

Our application addresses all these problems by prompting the user. Isn't
that feasible for you?

--
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

DSN VS Logon

Our Database is ignores the authentication method that is chosen in the
database connection under ODBC. Everyone is getting a log on dialog.
Can any one help! ThanksYou didn't provide many details but is it safe to guess that
you are trying to use Windows Authentication in the DSN and
that all user who use this DSN are being prompted to enter a
user and password?
There was a similar issue with an old version of MDAC (2.5
maybe) if that's the case. You can check your MDAC
installation and version with component checker. Component
checker and MDAC versions are available for download from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Thu, 26 Oct 2006 12:49:02 -0700, Maida
<Maida@.discussions.microsoft.com> wrote:

>Our Database is ignores the authentication method that is chosen in the
>database connection under ODBC. Everyone is getting a log on dialog.
>Can any one help! Thanks|||Thanks Sue - Sorry I was short
We're moving on servers to a new Domain (btoins.com) then we will reimage
the workstation and move them. Before we moved it the DSN file worked fine w
e
didn't get any Windows Authentication box. We did a temp fix by putting the
domain users(genelco.com) in the local machine administrator group. Why
what's up with that - Thanks for your help!
"Sue Hoegemeier" wrote:

> You didn't provide many details but is it safe to guess that
> you are trying to use Windows Authentication in the DSN and
> that all user who use this DSN are being prompted to enter a
> user and password?
> There was a similar issue with an old version of MDAC (2.5
> maybe) if that's the case. You can check your MDAC
> installation and version with component checker. Component
> checker and MDAC versions are available for download from:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Thu, 26 Oct 2006 12:49:02 -0700, Maida
> <Maida@.discussions.microsoft.com> wrote:
>
>|||What protocol are you connecting with? Make sure it's TCP/IP
and not named pipes.
You can end up with some issues on logins, domain mappings
when moving domains but I can't tell from your post if this
is the issue or not. What domain are the users logging into
when they try to use the DSN? What domain are they listed
under in syslogins?
-Sue
On Fri, 27 Oct 2006 07:26:03 -0700, Maida
<Maida@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue - Sorry I was short
>We're moving on servers to a new Domain (btoins.com) then we will reimage
>the workstation and move them. Before we moved it the DSN file worked fine
we
>didn't get any Windows Authentication box. We did a temp fix by putting the
>domain users(genelco.com) in the local machine administrator group. Why
>what's up with that - Thanks for your help!
>"Sue Hoegemeier" wrote:
>