DB2OLEDB Query Parameters in OLE DB Sources

Hi

I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:

TITLE: Microsoft Visual Studio

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

ADDITIONAL INFORMATION:

El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)

I would really appreciatte any help.

Thanks.

Well, the message looks pretty clear to me. The provider you are using does not support an interface used to derive parameters from queries so parametrized queries cannot be used in that source adapter.

The workaround is to use "Sql command from variable" access mode and build your query in a single variable.

Thanks,

Bob

|||

Hi

First of all, thanks for your answer. I know I can use the "Sql command from variable" workaround, for that matter I could also use a DataReader Source instead of an OLE DB Source and use an expression to build the query. The thing is, I was wondering if there was a more recent version of the driver that would allow me to build parametrized queries the same way I build them when accessing a MS SQL Server database. I know I should have been more specific when I posted my question, for that I apologize.

Right now I'm evaluating Microsoft's and IBM's DB2 providers, and both have certain limitations. For instance when I use MS provider and the code page of the DB2 database is improperly configured, the development studio simply vanishes when I try to create a Data Flow. I know is not that big of a problem, I just need to be more careful when setting up the connections properties, but still it took me a while to realize what was going on, probably because I have little experience with MS SQL Server.

My point is, I was hoping to access DB2 the same way I access any other database, instead of having to change the way I do things depending on the provider used to set up the connection, but I supposse this is the way things are.

Thanks again for your answer.

Cheers.

|||

Hi

It looks like it is possible to use parametrized queries. I just had to change the value of "Derive parameters" to true in the "All" tab.

Thanks again.

sql

db_datareader role**

Hi
I defined a new login"login1" in SQL Server 2000 and
tried to make an access(public+db_datareader)
to one of databases called "db1" successfully.
then in query analyzer I logined by "login1" and
saw the name of more than one databases in left window
,there were ("master","tempdb","distribution",
"db1","northwind","pubs","msdb"),and when I tried to open them ,for
example I selected "tempdb" and
then clicked the right key of mouse on a view
called "dbo.sysconstraints" and it opened successfully,why? I want my user
just read the information of one database called "db1"!!!!
second question: why didn't appear other databases
in left window of query analyzer?
3th question ,what's the best selection to access
"login1" just reading the information(just select statement) stored in
"db1"?(for example:
is it correct to be member of public role and then
check the check boxes of select column in permission
section of all tables in "db1" one by one!?)
any help would be greatly appreciated.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/A login can access a database only if the login is a user in the database or
the guest user is enabled. Since any login can access a database containing
the guest user, the other databases listed must have the guest user enabled
and this is why 'Login1' can access those databases even without a database
userid. Note that the guest user is required in the master and tempdb
system databases but can be removed from other databases if you don't want
all logins to have access to those databases.

> I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why
The public role has SELECT permissions on system tables and views. This is
needed in order to retrieve meta-data needed by database access APIs.

> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
Adding the user to only the db_datareader role will provide SELECT
permissions on tables and views. If you need more granular permissions, you
can create your own database role and grant the desired permissions to the
role. This allows you to control user database permissions via role
membership without granting direct permissions to individual users.
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4uk7scihqligo@.msnews.microsoft.com...
> Hi
> I defined a new login"login1" in SQL Server 2000 and
> tried to make an access(public+db_datareader)
> to one of databases called "db1" successfully.
> then in query analyzer I logined by "login1" and
> saw the name of more than one databases in left window
> ,there were ("master","tempdb","distribution",
> "db1","northwind","pubs","msdb"),and when I tried to open them ,for
> example I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why? I want my user
> just read the information of one database called "db1"!!!!
> second question: why didn't appear other databases
> in left window of query analyzer?
> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
> any help would be greatly appreciated.
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||How can I recognize a guest user in a database?
On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> A login can access a database only if the login is a user in the
> database or
> the guest user is enabled. Since any login can access a database
> containing
> the guest user, the other databases listed must have the guest user
> enabled
> and this is why 'Login1' can access those databases even without a
> database
> userid. Note that the guest user is required in the master and tempdb
> system databases but can be removed from other databases if you don't
> want
> all logins to have access to those databases.
>
> The public role has SELECT permissions on system tables and views. This
> is
> needed in order to retrieve meta-data needed by database access APIs.
>
> Adding the user to only the db_datareader role will provide SELECT
> permissions on tables and views. If you need more granular permissions,
> you
> can create your own database role and grant the desired permissions to
> the
> role. This allows you to control user database permissions via role
> membership without granting direct permissions to individual users.
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> How can I recognize a guest user in a database?
To see if the guest user is enabled in a particular database:
USE MyDatabase
sp_helpuser 'guest'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4vwbsg8hqligo@.msnews.microsoft.com...
> How can I recognize a guest user in a database?
> On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
>
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave this
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave thi
s
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

DB maintenance jobs not deleted

Hi
I had created a DB maintenace job that used to backup
full backup and trans log backup.
Recently we deleted those jobs and recreated the backup
jobs with manual SQL scripting.
The DB maintenance job keeps on appearing although there
is no corresponding plan for it.
We have checked all servers but with no help
Any idea what where that job might be coming from ?
Thanks
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
Can you elaborate a little bit? Did you remove a job that was realeted to MP
or first MP?
If you delete MP as far as I know all related jobs will be removed as well.
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6001c53e21$619206d0$a601280a@.phx.gbl...
> Hi
> I had created a DB maintenace job that used to backup
> full backup and trans log backup.
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
> We have checked all servers but with no help
> Any idea what where that job might be coming from ?
> Thanks

DB maintenance jobs not deleted

Hi
I had created a DB maintenace job that used to backup
full backup and trans log backup.
Recently we deleted those jobs and recreated the backup
jobs with manual SQL scripting.
The DB maintenance job keeps on appearing although there
is no corresponding plan for it.
We have checked all servers but with no help
Any idea what where that job might be coming from ?
Thanks> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
Can you elaborate a little bit? Did you remove a job that was realeted to MP
or first MP?
If you delete MP as far as I know all related jobs will be removed as well.
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6001c53e21$619206d0$a601280a@.phx.gbl...
> Hi
> I had created a DB maintenace job that used to backup
> full backup and trans log backup.
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
> We have checked all servers but with no help
> Any idea what where that job might be coming from ?
> Thanks

DB Maintainance Plan

HI
I have shedules a complete backup using DB maintainance plan and checked option to Remove files older than 1 day
Questions - Does file get removed after new backup is done or before the new backup starts
Also, what happens in case DB Consistency check fails in the Integrity tab, does backup still goes through ?
Thanks
Sanjay
The Delete happens after the backup completes. I believe the DBCC is a
separate job altogether but you should confirm this on your system.
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> HI
> I have shedules a complete backup using DB maintainance plan and checked
option to Remove files older than 1 day
> Questions - Does file get removed after new backup is done or before the
new backup starts
> Also, what happens in case DB Consistency check fails in the Integrity
tab, does backup still goes through ?
> Thanks
> Sanjay
>
>
|||I think the files get removed as the last step... And if DBCC fails from the
previous tab (when it is NOT included in the full db backup), it does not
prevent the backup from occurring...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> HI
> I have shedules a complete backup using DB maintainance plan and checked
option to Remove files older than 1 day
> Questions - Does file get removed after new backup is done or before the
new backup starts
> Also, what happens in case DB Consistency check fails in the Integrity
tab, does backup still goes through ?
> Thanks
> Sanjay
>
>
|||I beleive there was a problem with "Attempt to repair minor problems" in Optimization tab in SQL server
Is that still a problem and we shoudnt use it ?
"Wayne Snyder" wrote:

> I think the files get removed as the last step... And if DBCC fails from the
> previous tab (when it is NOT included in the full db backup), it does not
> prevent the backup from occurring...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> option to Remove files older than 1 day
> new backup starts
> tab, does backup still goes through ?
>
>
|||It was a problem in that it put the database in single user mode and left it
there. You should not have the MP automatically "Fix" the database. If
there is a problem you should determine what is is and what the best coarse
of action is and also determine why there was a problem in the first place.
That option should have been removed a long time ago in my opinion.
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:608CE6D4-455A-489E-9AF6-103C853E0A4B@.microsoft.com...
> I beleive there was a problem with "Attempt to repair minor problems" in
Optimization tab in SQL server[vbcol=seagreen]
> Is that still a problem and we shoudnt use it ?
>
> "Wayne Snyder" wrote:
the[vbcol=seagreen]
not[vbcol=seagreen]
checked[vbcol=seagreen]
the[vbcol=seagreen]

DB Maintainance Plan

HI
I have shedules a complete backup using DB maintainance plan and checked opt
ion to Remove files older than 1 day
Questions - Does file get removed after new backup is done or before the new
backup starts
Also, what happens in case DB Consistency check fails in the Integrity tab,
does backup still goes through ?
Thanks
SanjayThe Delete happens after the backup completes. I believe the DBCC is a
separate job altogether but you should confirm this on your system.
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> HI
> I have shedules a complete backup using DB maintainance plan and checked
option to Remove files older than 1 day
> Questions - Does file get removed after new backup is done or before the
new backup starts
> Also, what happens in case DB Consistency check fails in the Integrity
tab, does backup still goes through ?
> Thanks
> Sanjay
>
>|||I think the files get removed as the last step... And if DBCC fails from the
previous tab (when it is NOT included in the full db backup), it does not
prevent the backup from occurring...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> HI
> I have shedules a complete backup using DB maintainance plan and checked
option to Remove files older than 1 day
> Questions - Does file get removed after new backup is done or before the
new backup starts
> Also, what happens in case DB Consistency check fails in the Integrity
tab, does backup still goes through ?
> Thanks
> Sanjay
>
>|||I beleive there was a problem with "Attempt to repair minor problems" in Opt
imization tab in SQL server
Is that still a problem and we shoudnt use it ?
"Wayne Snyder" wrote:

> I think the files get removed as the last step... And if DBCC fails from t
he
> previous tab (when it is NOT included in the full db backup), it does not
> prevent the backup from occurring...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:8934B2F8-1980-4FA3-B2AE-D5CDBD7329AE@.microsoft.com...
> option to Remove files older than 1 day
> new backup starts
> tab, does backup still goes through ?
>
>|||It was a problem in that it put the database in single user mode and left it
there. You should not have the MP automatically "Fix" the database. If
there is a problem you should determine what is is and what the best coarse
of action is and also determine why there was a problem in the first place.
That option should have been removed a long time ago in my opinion.
Andrew J. Kelly SQL MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:608CE6D4-455A-489E-9AF6-103C853E0A4B@.microsoft.com...
> I beleive there was a problem with "Attempt to repair minor problems" in
Optimization tab in SQL server[vbcol=seagreen]
> Is that still a problem and we shoudnt use it ?
>
> "Wayne Snyder" wrote:
>
the[vbcol=seagreen]
not[vbcol=seagreen]
checked[vbcol=seagreen]
the[vbcol=seagreen]

DB mail fails with permission error

Hi:

I am trying to send mail using DB Mail. I made sure that there is a mail box setup for the service account and the service broker is enabled in the msdb database. Also I made the service account a user in the msdb and added him to the mail role as specified in BOL. However I consistently end up with the following error message when I try to send the mail.

Log ID 19
Process ID 5672
Mail Item ID 6
Last Modified 4/26/2006 11:05:00 AM
Last Modified By sa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-04-26T11:05:00). Exception Message: Cannot send mails to mail server. (Client does not have permission to submit mail to this server. The server response was: 5.7.3 Client does not have permission to Send As this sender.).
)

I am at loss to understand what Permissions it still needs?. Any thoughts appreciated.

Thanks

I have resolved this problem successfully and want to share it with the community for the benefit of others.

when the above error happens, please make sure that you have the following in place.

1). The Service account should have a mail box setup.

2). The account name you use when you configure DB Mail should be the same as that of the Mail box name.

3). The Account should be a user in the msdb and should be added to the DBMailuser role.

Everything then should work fine.

Thanks

|||Thank you so much for posting this! I was getting hung up on requirement #2. They don't make these things very obvious do they?

db hierarchy

HI!
I am new in DB so I need some advices for finding the right solution.
I need to be able to make automatically any join between tables which the user choose and deliver the result.
is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.

if u have any idea about how can I manage this, pls help me.

thanks a lot!I would start by getting a beginner sql server programming book.|||Automatically? You will need to define all your table relationships, and then write code that will will dive into the system tables to find these relationships.

Not an easy coding challenge.

I suggest you look for third party software that will do this, as I suspect that you are reinventing the wheel.