Offline|Online database

Hi,
Is there any method to get an historical log where appears the database status (Online|offline) ?
Regards.Not really. You can check the SQL Server errorlog, which will tell you at least when a database is brought online.

OdbcCommand Parameter Failure

Hi,

I tried to create a utility method to accept the name of a stored procedure and an array of OdbcParameter objects. When I try to add the parameters from the array in the code, the parameters aren't recognized:

foreach (OdbcParameter prmin prm_array)cmd.Parameters.add (prm);

OR

cmd.Parameters.AddRange (prm_array);

However, when I hardcode the parameters without using the argument of the function, it works perfectly:

cmd.Parameters.Add ("@.login", OdbcType.VarChar, 50,"Joe");cmd.Parameters.Add ("@.password", OdbcType.VarChar, 50,"Bob");

I know the method calling the utility function is passing the parameters correctly; in fact, if I hard code the parameters in the utility function but add them with a foreach like above or using the AddRange function, it just does not recognize the parameters.

I've worked too long on this so I'm using an inelegant solution, but this nuance is very frustrating. Any possible insight?

~ mellamokb

Is the cmd object an ODBCCommand object. If not, this sure won't work.|||

Yep. I only use Odbc.... objects.

~ mellamokb

|||

can you post the entire function, this would be helpful

thanks

|||Yes, if it was not recognized, what runtime exception or compile time error was thrown?|||

I got it to work by reconstructing the parameters from an OdbcParameter array passed and by using the AddWithValue command:

foreach (OdbcParameter prmin prm_array) cmd.Parameters.AddWithValue(prm.ParameterName, prm.Value);

I'll call that good enough; but it doesn't make sense why cmd.Parameters.Add(prm_object) is different than cmd.Parameters.Add([parameter_name], [datatype], [datasize], [value]) when the prm_object and the hard-coded parameters are the same information.

This situation is independent of the function it is in; I've generalized to any situation, and there's no reason why it would work in a modified test case but not in the utility function I've created.

But I've seen stranger things, I guess.

Kevin Yu: The runtime exception is simply that the command cannot find the needed parameter, as if I hadn't passed it at all, when I use Add(prm_object); but using the four parameter version of Add works fine, even if the four parameters are the same as the four used to construct the new OdbcParameter object.

~ mellamokb

ODBC wire encryption

Is there a method to enable an ODBC client to encrypt all communications
between the client and the server?
I have a client that is having a cow about data moving over the network via
an ODBC driver to DBMS server unencrypted.
I haven't seen or heard of an option like this before but thought I would
ask.
Our application communicates with SQL Server and Oracle 9i via the vendors
native ODBC drivers.
Tony
ODBC is just an API. It's up to the individual drivers/DBMSes to perform
any encryption.
I'm not familiar with Oracle, but SQL Server supports encryption between
client and server.
If I remember correctly, there are two methods of encryption with SQL
Server. In one method, you install an SSL certificate on the server. This
enables encryption, and REQUIRES that all client connections to the server
use encryption. The other method involves installing a certificate on the
client. This allows single clients to use encryption, without requiring
encryption across-the-board, but has the downside that you can't require
encryption in situations that you WANT it.
My understanding is that the first method is MUCH easier to setup. There
are some gotchas though. If the server is configured with a certificate,
and there is something wrong with the certificate, then the server will fail
to start.
There should be plenty of documentation on this on the web.
Brannon
"Tony" <tonyng2@.spacecommand.net> wrote in message
news:#D9JqJumEHA.2948@.TK2MSFTNGP11.phx.gbl...
> Is there a method to enable an ODBC client to encrypt all communications
> between the client and the server?
> I have a client that is having a cow about data moving over the network
via
> an ODBC driver to DBMS server unencrypted.
> I haven't seen or heard of an option like this before but thought I would
> ask.
> Our application communicates with SQL Server and Oracle 9i via the vendors
> native ODBC drivers.
> Tony
>

ODBC in MS Access ignores DSN authorization method

I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.
Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:

>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.
|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/downl...s/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:

>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

ODBC in MS Access ignores DSN authorization method

I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:
I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.
When the Windows login I am using has access to the database, I get
the tables that login has permissions for.
When the Windows login I am using has no access to the database, I get
a failure for user NULL.
I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.
I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (Tina Robichaux)
wrote:

>I have found info on this problem at MS, but they say this problem
>does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
>experiencing it:
>I have a SQL user specifically created to SELECT on 5 tables in one
>database. I have created an ODBC file DSN using that login/password. I
>create an Access DB, attempt to link to the five SQL tables using that
>DSN, using a trusted connection. The ODBC drivers ignore the users
>permissions, and instead use the PUBLIC permissions.
>When the Windows login I am using has access to the database, I get
>the tables that login has permissions for.
>When the Windows login I am using has no access to the database, I get
>a failure for user NULL.
>I can forego the trusted connection, enter the login password, and see
>exactly the tables I want. I link them to the Access DB, can open them
>and see the data.
>I close Access and reopen the same Access DB. Now, when I try to open
>one of the linked tables, I get the same failure - permissions are no
>good.|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:

>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!