DB2 ODBC Connection Problems

Hello,

I've recently run across a couple of problems trying to connect to DB2 using the .NET ODBC Provider.

1) I can create a DataSource using the DataSource designer, and when I edit the connection, the User Name and Password text boxes are empty. If I fill in the correct information and hit "Test Connection", I am able to connect. However, this information is not saved in the DataSource the next time I open it or try to run a Data Flow task that uses it! Is there some property I can set to fix this?

2) When I open up a solution, the DataSource appears to be trying to connect to the DB2 server, and perhaps there is no valid User Name and Password, the connection is refused and I eventually get locked out of my account. This could be related to the issue above.

Does anyone have any experience connecting to DB2 using either an ODBC driver or the perhaps the IBM DB2 UDB iSeries OLE DB providers?

Thanks in advance,

Mark

Mark,

The same problem occurs with Progress Database using .NET ODBC Provider and a didn′t find a solution. Problably is a .NET ODBC Provider problem but I don′t know how to fix. If you find a solution, please post here.

Question: Do you receive this error message ?

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Thanks.

Rodrigo Chagas

|||

I found this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93045&SiteID=1

Maybe will be useful for you. It seems ODBC connections are having a lot of problems in SQL 2005.

My hope is getting smaller...

|||This is not a problem with .Net, but rather the way in which the ODBC driver builds the connection string or handles security. Some drivers including the CA ODBC driver do not append the password property to the connection string. So when you attempt to connect it will prompt for a password. Depending on how you invoke the program, you may not even see the prompt.
One way to resolve the issue is to append the password to your connection string in the form of "Password=MyPass;"
I've used the OLEDB provider for DB2 before, but there are some limitations to it. Per IBM, OLEDB does not provide the same level of functionality as their ODBC drivers. One thing that OLEDB does not provide is scrollable cursors.
I haven't used DB2 in almost a year, so IBM may have some updated drivers. Also MS just released their version of an OLEDB provider for DB2, so you could try it. You can get it at the following url.
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
Larry

db restoration via shared folder

I was reading the article on the subject above at the
http://examnotes.net/arch/178/2002/3/329543
The question here is:-
Testing was done and I was able to restore db using EM on
a Standard Edition (SP3) and mapped drive to another
server. Unlike I was unable to do that from a Enterprise
Edtion (SP3). Why is that so?
Thanks.So, you made certain that the SQL Server service has permissions on the
share and it still didn't work? What error message did you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"NG" <angiecmf@.hotmail.com> wrote in message
news:1101001c40fc1$4ab1af40$a501280a@.phx
.gbl...
> I was reading the article on the subject above at the
> http://examnotes.net/arch/178/2002/3/329543
> The question here is:-
> Testing was done and I was able to restore db using EM on
> a Standard Edition (SP3) and mapped drive to another
> server. Unlike I was unable to do that from a Enterprise
> Edtion (SP3). Why is that so?
> Thanks.|||Let me rephrase my question here, Sorry if my explaination
was not clear earlier.
Based on the article, I was able to backup/restore db
without any problem in MSSQL standard/enterprise edition.
The point here is the article stated that backup/restore
can't be done using Enterprise Manager. But I was able to
backup/restore db using Enterprise Manager in Standard
Edition but NOT Enterprise Edition. I was able to see the
mapped drive in the Standard Edition. Why I unable to
view the mapped drive in Enterprise Edition using the
Enterprise Manager? Is this a special feature in Standard
Edition or other setting need to be done here in order to
do so in the Enterprise Edition? Currently I am using
MSSQL 7 with sp(3) on NT4 (sp6).
Your help is much appreciated.

>--Original Message--
>So, you made certain that the SQL Server service has
permissions on the
>share and it still didn't work? What error message did
you get?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"NG" <angiecmf@.hotmail.com> wrote in message
> news:1101001c40fc1$4ab1af40$a501280a@.phx
.gbl...
on
Enterprise
>
>.
>|||> Based on the article, I was able to backup/restore db
> without any problem in MSSQL standard/enterprise edition.
I assume that you used Query Analyzer for this, is that what you mean?

> The point here is the article stated that backup/restore
> can't be done using Enterprise Manager. But I was able to
> backup/restore db using Enterprise Manager in Standard
> Edition but NOT Enterprise Edition. I was able to see the
> mapped drive in the Standard Edition. Why I unable to
> view the mapped drive in Enterprise Edition using the
> Enterprise Manager? Is this a special feature in Standard
> Edition or other setting need to be done here in order to
> do so in the Enterprise Edition?
I won't read the article, but just be aware that it might be incorrect. Back
to the problem. Here's what I think is going on:
On one installation, the service account is the same as the one you are
logged in as. Hence, the mapped drives are the same. On the other, you are
using different accounts. The key point here is that mapped drives are per
user. And typically, you are not logged in using the same account as the SQL
Server service account. So what you perceive as a mapped drive is different
to what SQL Server does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"NG" <angiecmf@.hotmail.com> wrote in message
news:11e7e01c4107a$848e7680$a101280a@.phx
.gbl...
> Let me rephrase my question here, Sorry if my explaination
> was not clear earlier.
> Based on the article, I was able to backup/restore db
> without any problem in MSSQL standard/enterprise edition.
> The point here is the article stated that backup/restore
> can't be done using Enterprise Manager. But I was able to
> backup/restore db using Enterprise Manager in Standard
> Edition but NOT Enterprise Edition. I was able to see the
> mapped drive in the Standard Edition. Why I unable to
> view the mapped drive in Enterprise Edition using the
> Enterprise Manager? Is this a special feature in Standard
> Edition or other setting need to be done here in order to
> do so in the Enterprise Edition? Currently I am using
> MSSQL 7 with sp(3) on NT4 (sp6).
> Your help is much appreciated.
>
> permissions on the
> you get?
> on
> Enterprise

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

Hi,

UserID is the promary key in Users table (like aspnet_Users) and a FK in related tables (like UserProfile etc). Since each profile can be identified by the userID only, there is nothing wrong in having it as the PK of the same table. Why would you like to have a surrogate key for such a table (when it doesnt really need one). IMHO, this is fine.

Vivek

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

This approach -- using the same name in the related table as in the

primary -- is appropriate when the related table is a subset of the

objects in the primary table, rather than a linking table between two or more primary entities, and has a

one-to-one relationship to the primary table. Usually the related

table is created to avoid nulls in a core table.

A product

inventory might be an example. It makes sense to have a unique

product_id for each product manufactured by a company. This would of

course be the primary key for the master "products" table. You would

not want to have information specific to a particular line of producs

in this table, though. Say the comapny makes tools, and one of their

lines is sockets. Fields such as socket_type [e.g. hex, star],

drive_size, or socket_size would be irrelevant to many other tools, but

quite necessary to the sockets line. Therefore, you might have a data

structure like:

tools (
product_id char(12) primary key,
tool_class varchar(64),
price decimal,
...
);

sockets (
product_id char(12) primary key references tools(product_id),
socket_type smallint,
drive_size decimal,
socket_size decimal,
...
);

This

works because all sockets are tools, though many tools are not

sockets. Likewise, I would choose product_id here, rather than say

tool_id, for flexibility. The company may also carry other lines of

products for which tool_class would be an inappropriate field. They

are still all products, though.

Hope this helps,

Joseph

Db owner list

Hi good day.
just a quick question. i cannot seem to find what I m
looking on the net so maybe some can help here.
I need to pull a report or just create a query for
this.
At a client site i have noticed that alot of people are
Db owners at specific servers.which could cause huge problem
for security purposes.
1. can someone help me with a procedure query that
will be able to check all servers in a specific
network where users are memebers of the Database owners
group. something like check all sql users(AD users and Sql
users) againts their user mapping and show if they belong to
the db_owners group.
2.. alternatively , iknow that this a big ask so maybe
if i could query each server individually..
to filter all users in a sql server that are in the Db
owners group. show only if they belong to the DB owners
group.
thanks
and hope that someone can help this google junkie..See if you can find it on http://www.sqlcommunity.com/ (under SQL Automation
section) If you can't then you can request one from "Tell us what you want"
section of the main page.
I think there's a script called SQL_ConfigSPY that can help you with what
you are looking for.
Thank you,
Saleem Hakani
WWW.SQLCOMMUNITY.COM
SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
SQL Server Radio, SQL Server Events..
"7736" wrote:
> Hi good day.
> just a quick question. i cannot seem to find what I m
> looking on the net so maybe some can help here.
> I need to pull a report or just create a query for
> this.
> At a client site i have noticed that alot of people are
> Db owners at specific servers.which could cause huge problem
> for security purposes.
> 1. can someone help me with a procedure query that
> will be able to check all servers in a specific
> network where users are memebers of the Database owners
> group. something like check all sql users(AD users and Sql
> users) againts their user mapping and show if they belong to
> the db_owners group.
> 2.. alternatively , iknow that this a big ask so maybe
> if i could query each server individually..
> to filter all users in a sql server that are in the Db
> owners group. show only if they belong to the DB owners
> group.
> thanks
> and hope that someone can help this google junkie..
>|||On Sep 29, 11:24 pm, Saleem Hakani
<SaleemHak...@.discussions.microsoft.com> wrote:
> See if you can find it onhttp://www.sqlcommunity.com/(under SQL Automation
> section) If you can't then you can request one from "Tell us what you want"
> section of the main page.
> I think there's a script called SQL_ConfigSPY that can help you with what
> you are looking for.
> Thank you,
> Saleem Hakani
> WWW.SQLCOMMUNITY.COM
> SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
> SQL Server Radio, SQL Server Events..
>
> "7736" wrote:
> > Hi good day.
> > just a quick question. i cannot seem to find what I m
> > looking on the net so maybe some can help here.
> > I need to pull a report or just create a query for
> > this.
> > At a client site i have noticed that alot of people are
> > Db owners at specific servers.which could cause huge problem
> > for security purposes.
> > 1. can someone help me with a procedure query that
> > will be able to check all servers in a specific
> > network where users are memebers of the Database owners
> > group. something like check all sql users(AD users and Sql
> > users) againts their user mapping and show if they belong to
> > the db_owners group.
> > 2.. alternatively , iknow that this a big ask so maybe
> > if i could query each server individually..
> > to filter all users in a sql server that are in the Db
> > owners group. show only if they belong to the DB owners
> > group.
> > thanks
> > and hope that someone can help this google junkie..- Hide quoted text -
> - Show quoted text -
HI there.
i tried looking in there.. even send a request but no
one has replied as yet.. I will keep o trying anyways.
Thanks for the link though.. Learnt more already.. from
that site..

db login info problem

------------------------

in my report, i went to "Database Expert" --> "Project Data" --> "ADO.NET Dataset" and selected the one that is filled by this query:

SELECT CYMUSER.UNAME, TEST.TNAME, RESULT.STARTTIME, RESULT.ENDTIME, RESULT.TOTALMARKS, RESULT.GRADE
FROM CYMUSER INNER JOIN
TEST ON CYMUSER.UID = TEST.UID INNER JOIN
RESULT ON CYMUSER.UID = RESULT.UID AND TEST.TID = RESULT.TID
WHERE (RESULT.RID = @.RID)

at run time, when i execute the report, it keep asks for db login information, that is DB Name, Server Name, Pw, User Name. I use MS SQL, i didnt set up any password and user id at all. Why these non-existing info is asked, can anyone help me to get around it plz?btw, if i go "Database Expert" --> "OLE DB ADO" --> and select "MICROSOFT OLE DB Provider for SQL Server" --> and tick "integrated security" to get dataset from the dabaset directly instead of the way mentioned above, at runtime, the report works just fine, wont ask for any DB login info. However, if i did this way, i need help on how to get the above query work and how to get the parameter "@.RID" and pass it to the report fields. please help|||Thru Front End, pass parameter value as

CR.parameter(0)='someval'

db Full auto-notification

Hi everybody,

Is it possible to make an alert on MSSQL so that the Administrator is automatically notified (by email or net message for instance) when one of the db comes full, or when the free space goes under a critical level ?

Is the "available space" for each database stored in some table of the Master database ?

many thanks in advance for your help ;-)Go thru books online on setting ALERTS and use ERROR 1105 to inform the status of the database size if its full by 90%.