Db2 Connection Problems SSIS (OLEDB)

Hello,

I'm trying to connect to a DB2 database via SSIS and I'm getting some problems:

I'm creating a new OLE DB Connection Manager and I'm getting two distinct errors:

1) When I try to use "IBM OLEDB Provider for DB2 Servers" I can create the connection manager and the connection is tested successfully.

But when I try to use the connection in OLE DB Source when I will list the tables I get this error:

Could not retrieve the table information for the connection manager 'MyDataSource'.
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004

ADDITIONAL INFORMATION:

truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)

2) When I try to use "Microsoft OLE DB Provider for DB2" when I try to test the connection I get this error:

Test connection failed because of an error in initializing provider. The parameter is incorrect.

Anyone had these problems ?

Thanks,

Guber

i have been working on DTS/SSIS using DB2OLEDB. I do not have the error of using "Microsoft OLE DB Provider for DB2", which you described.

I guess that your connection manager was perhaps not correctly configured. Create a UDL file first to figure out the correct connection string and then create DB2 connection manager inside SSIS.

If you can post your connection string here, I should be able to tell you what went wrong.

Steve

|||Hi,

I am also getting the same problem. what is configuration you are using?
|||

I met the same problem yesteday. I found a solution to it.

My source table in db2 had a column defined decimal(20,2),but the length of numeric in SSIS is 16.

So the data will bu truncated .

When i had used a script component to read data from db2,the problem did not appear.

Db2 Connection Problems SSIS (OLEDB)

Hello,

I'm trying to connect to a DB2 database via SSIS and I'm getting some problems:

I'm creating a new OLE DB Connection Manager and I'm getting two distinct errors:

1) When I try to use "IBM OLEDB Provider for DB2 Servers" I can create the connection manager and the connection is tested successfully.

But when I try to use the connection in OLE DB Source when I will list the tables I get this error:

Could not retrieve the table information for the connection manager 'MyDataSource'.
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004

ADDITIONAL INFORMATION:

truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)

2) When I try to use "Microsoft OLE DB Provider for DB2" when I try to test the connection I get this error:

Test connection failed because of an error in initializing provider. The parameter is incorrect.

Anyone had these problems ?

Thanks,

Guber

i have been working on DTS/SSIS using DB2OLEDB. I do not have the error of using "Microsoft OLE DB Provider for DB2", which you described.

I guess that your connection manager was perhaps not correctly configured. Create a UDL file first to figure out the correct connection string and then create DB2 connection manager inside SSIS.

If you can post your connection string here, I should be able to tell you what went wrong.

Steve

|||Hi,

I am also getting the same problem. what is configuration you are using?
|||

I met the same problem yesteday. I found a solution to it.

My source table in db2 had a column defined decimal(20,2),but the length of numeric in SSIS is 16.

So the data will bu truncated .

When i had used a script component to read data from db2,the problem did not appear.

Db2 Connection Problems SSIS (OLEDB)

Hello,

I'm trying to connect to a DB2 database via SSIS and I'm getting some problems:

I'm creating a new OLE DB Connection Manager and I'm getting two distinct errors:

1) When I try to use "IBM OLEDB Provider for DB2 Servers" I can create the connection manager and the connection is tested successfully.

But when I try to use the connection in OLE DB Source when I will list the tables I get this error:

Could not retrieve the table information for the connection manager 'MyDataSource'.
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004

ADDITIONAL INFORMATION:

truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)

2) When I try to use "Microsoft OLE DB Provider for DB2" when I try to test the connection I get this error:

Test connection failed because of an error in initializing provider. The parameter is incorrect.

Anyone had these problems ?

Thanks,

Guber

i have been working on DTS/SSIS using DB2OLEDB. I do not have the error of using "Microsoft OLE DB Provider for DB2", which you described.

I guess that your connection manager was perhaps not correctly configured. Create a UDL file first to figure out the correct connection string and then create DB2 connection manager inside SSIS.

If you can post your connection string here, I should be able to tell you what went wrong.

Steve

|||Hi,

I am also getting the same problem. what is configuration you are using?
|||

I met the same problem yesteday. I found a solution to it.

My source table in db2 had a column defined decimal(20,2),but the length of numeric in SSIS is 16.

So the data will bu truncated .

When i had used a script component to read data from db2,the problem did not appear.

DB2 Connect and SSIS | Error in OLE DB Source Task

Hi All,

I am trying to connect to DB2 database via OLE DB connection manager in SSIS. But when I enter the SQL Query and press OK it gives following error

"Error at Data Flow Task - Header Load [OlE DB Source - Header_Load[1]]": An OLE DB Error has occured. Error Code: 0x80040E21

Additional Information:

Exception from HRESULT: 0xC0202009(Microsoft.SqlServer.DTSPipelineWrap)"

I followed following steps: -

1. I created OLE DB provider and tested the connection, it was successful(with give username and password)

2. Created query in Build query as following and tried executing it. It worked! Query used was

SELECT SRC_ID, ORG_ID FROM DB123.DEAL_HEADER

3. But when, in OLE DB Source Provider Task, when I press preview, It thorws the above error!

Kindly let me know, Because I am stuck at that point.

Thanks

Sid

What happens if you don't preview the results? Just run the package normally after building the query.|||In that case; when I press "OK" in OLE DB Source Task, the same Error appears. In short, I am not able to save the task and move futher with my implementation.|||What driver are you using to connect to DB2? IBM's DB2Connect?|||Yes I am using IBM's DB2 Connect|||

sidzone123 wrote:

Yes I am using IBM's DB2 Connect

Well, if you are on SQL Server Developer or Enterprise edition, you can download the Microsoft OLE DB for DB2 driver. That works really well for me.

Never-the-less, you can try some of the techniques in here to see if they'll help your situation, even though it doesn't deal with DB2 directly:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142282&SiteID=1|||

Hi Phil,

I am using SQL Server Standard Edition.

However, I tried all methods suggested by the link suggested by you.

But unfortunately nothing work!

Thanks

Sid

|||

Hi,

I found the workaround for this!

1) In OLE DB Source, Open the Advance Editor and in the Custom property, set the Access Mode to "OpenRowset"

2) In the OpenRowset property, write the table that you want to access i.e. say "CZ123"."DEAL_HEADER"

Thats it and press OK!

To my surpise it worked great. I was able to connect to DB2 and transfer the data.

However I am not sure about why I was getting the errors that I mentioned earlier and why the above solution worked. Still trying to find the aswer to this.

Hope the same works for all.

Thanks

Sid

db_owner role and table owner issues

Hi,

I have given a user db_owner role in a database. When he creates a table using Enterprise manager the table owner is dbo. When he creates a table using Query Analyzer the table owner is the user. eg

Enterprise Manager = dbo.Table1

Query Analyer = username.Table1

This causes a problem when the user is writing web applications. Is this an error in the way i have set up permissions ? How can i make them behave the same way?

Thanks for your help.First, don't create tables in EM

Second, it's how they are connecting...

Third, depending on how you want the application to work, qualify the owner...

Fourth, get control...

have them supply you with the DDL, and you create the tables for them...

My guess is that EM is connected with sa, and QA is connecting with their id...

just a guess...|||Thanks for your quick response. I should have included the following information.

I work in a University computing department where students must learn to create tables etc. I could not create their tables for them as it is part of their assessment (and there are 1700 students!)

Students can only log on to the server using windows authentication so will log on using the same windows account to both EM and QA.

Students are taught to use both EM and QA which is why they are finding problems.

Thanks again for all help.|||Gotta test it...which way do you want the tables qualified for their apps...

dbo?

I'll look into it...|||yes dbo thank you.

DB Suspect...

I'm using SQL Server 2K n 1 fine morning I recieve this
error saying "Database Suspect", in Enterprise Manager.
By the way, I'm using Windows NT.
How can I get my DB out of this Suspect mode?
Any answers will be highly appreciated.That CANNOT be a reason by any chance. As said have you tried the options
specified in the SQL Server BOL ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Biju" <reachcybercop@.yahoo.com> wrote in message
news:0e6201c37e83$75a614e0$a101280a@.phx.gbl...
> Well, I had installed SP3 just a couple of days back.
> Could this be the reason?
> >--Original Message--
> >Check in the SQL Server error logs and the NT event logs
> first. You
> >need to also know why it went west to prevent it
> happening again.
> >
> >Check out this in BOL also
> >
> >mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
> 20Server\80\Tools\Book
> >s\trblsql.chm::/tr_servdatabse_494j.htm
> >
> >
> >Also just in case I would start to dig out your Backups.
> >
> >
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >.
> >|||I wouldn't have thought so (SP3 what ? DB, Windows) as you would have
had to have rebooted after applying it and I would have expected things
to go west then. What do the logs say? Do you have enough Disk space?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||following err message comes up in SQL Server log:
Device activation error. The physical file
name 'C:\Program Files\Seagate
Software\Enterprise\ODBCSUPT\CE8_log.ldf' may be incorrect.
>--Original Message--
>Well, I had installed SP3 just a couple of days back.
>Could this be the reason?
>>--Original Message--
>>Check in the SQL Server error logs and the NT event logs
>first. You
>>need to also know why it went west to prevent it
>happening again.
>>Check out this in BOL also
>>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
>20Server\80\Tools\Book
>>s\trblsql.chm::/tr_servdatabse_494j.htm
>>
>>Also just in case I would start to dig out your Backups.
>>
>>Allan Mitchell (Microsoft SQL Server MVP)
>>MCSE,MCDBA
>>www.SQLDTS.com
>>I support PASS - the definitive, global community
>>for SQL Server professionals - http://www.sqlpass.org
>>.
>.
>|||have enough disk space
>--Original Message--
>following err message comes up in SQL Server log:
>Device activation error. The physical file
>name 'C:\Program Files\Seagate
>Software\Enterprise\ODBCSUPT\CE8_log.ldf' may be
incorrect.
>
>>--Original Message--
>>Well, I had installed SP3 just a couple of days back.
>>Could this be the reason?
>>--Original Message--
>>Check in the SQL Server error logs and the NT event
logs
>>first. You
>>need to also know why it went west to prevent it
>>happening again.
>>Check out this in BOL also
>>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
>>20Server\80\Tools\Book
>>s\trblsql.chm::/tr_servdatabse_494j.htm
>>
>>Also just in case I would start to dig out your Backups.
>>
>>Allan Mitchell (Microsoft SQL Server MVP)
>>MCSE,MCDBA
>>www.SQLDTS.com
>>I support PASS - the definitive, global community
>>for SQL Server professionals - http://www.sqlpass.org
>>.
>>.
>.
>|||In article <7f1701c37e88$ab23d490$a601280a@.phx.gbl>, Biju wrote:
> activation error. The physical file
> name 'C:\Program Files\Seagate
> Software\Enterprise\ODBCSUPT\CE8_log.ldf
>
check for
A. Diskspace on C:\
B. The existence of the file
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

db seperation

I was given a task of doing the following and I'm not 100% sure what the
manager means. I'm not a database admin at all so I'm totaly new to all of
this. The manager wants me to seperate the transactional database from the
backend database.
What does that mean exactly? Can anyone provide me any information on what
exactly is and how to go about designing it then putting it into a working
mode?
thanksHe might mean that the 2 databases are 'transactional' and 'reporting/BI'
and should go on different servers, but who knows? We can help with
technical details but you'll need to clarify the requirements fully with the
manager first otherwise it's guesswork...
Cheers,
Paul Ibison|||Thanks, I'm in that process now actually to get more details on what she
actually wants done with the databases.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6SNFx3rHHA.4180@.TK2MSFTNGP04.phx.gbl...
> He might mean that the 2 databases are 'transactional' and 'reporting/BI'
> and should go on different servers, but who knows? We can help with
> technical details but you'll need to clarify the requirements fully with
> the manager first otherwise it's guesswork...
> Cheers,
> Paul Ibison
>

DB properties cannot be changed with Enterprise Manager

Hello,

after the upgrade of MS SQL Server 7 to MS SQL Server 2000 on MS Windows 2000 Server and installation of MS SQL Server 2000 SP2 it is not possible to make changes to DB properties of user DBs with Enterprise Manager. MS SQL Server Instance properties and System DB properties are changeable with Enterprise Manager, but user DB properties can only be changed using Query Analyzer.

Has anybody an idea how to fix this problem?

Regards
Joe :rolleyes:Hi,

Do u trying to change by local Enterprise Manager or over Network?

Remember that are some new things in (2000) Permissions topic.

Explain more your schema.

[ ]'s|||Hi,

I tried to use the enterprise manager local and remote. Both doesn't work. I can display the user DB properties, but the ok button is grey. So I cannot use it.
What do you mean with new permissions at SQL 2000?

Regards
Joe

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?
ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?
|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:[vbcol=seagreen]
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...
|||Which means you no longer have a supported SQL server installation. Be careful when modifying system
objects. Not supported.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
see[vbcol=seagreen]
|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:[vbcol=seagreen]
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...|||Which means you no longer have a supported SQL server installation. Be caref
ul when modifying system
objects. Not supported.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...eagreen">
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
see[vbcol=seagreen]|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...
> > i've got a user that has permissions to read and write in two
> > databases. that user has enterprise manager installed and uses it (i
> > know he shouldn't really have it, but that's another topic). he can see
> > all of the databases on the server. is there any way to make EM only
> > show him the two databases which he can access?|||Which means you no longer have a supported SQL server installation. Be careful when modifying system
objects. Not supported.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can
see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?