Setting CONTEXT_INFO from the Connection String? 
Author Message
 Setting CONTEXT_INFO from the Connection String?

(SQL Server 2000, SP2)

Hello, all!

Sorry about the cross-posting, but I wasn't quite certain to where this
question might best pertain.

I'm wondering if Transact-SQL can obtain any "user-defined" information that
might be provided on the connection string of an ADODB.Connection object?
(I don't even know if it's possible to *have* any user-defined information
in the connection string, or if it will be ignored.)

In essense, I would like to subtly change our connection string to append
something like ";Foo=test" and then be able to potentially reference that
"Foo=test" from Transact-SQL.

My guess is that it's not going to be quite so simple.  I've looked at the
CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could somehow
get some information into that field from the ADODB.Connection.Open()
method.

For example, if I could do something like:

   sConnect = "Provider=SQLOLEDB;Data Source=(local);User
ID=sa;Password=;Initial Catalog=tempdb;"

   sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

   Set Cn = CreateObject("ADODB.Connection")
   Call cn.Open(sConnect)

And have this call somehow "magically" set the CONTEXT_INFO to "MyData",
such that it can be referenced from Transact-SQL.

I'd like to be able to do this from the ADODB.Connection.Open() method via
the connection string, rather than in a separate call (if at all possible),
to have to avoid changing a million Open calls in an already established
.ASP project.  I have the connection string set as a Session variable in one
place, and changing it once and having it essentially propagate would be
fantastic.

Any thoughts/comments/criticisms would be *very* welcome.  Thanks!  :-)

John Peterson



Sun, 03 Jul 2005 05:28:08 GMT
 Setting CONTEXT_INFO from the Connection String?

You can use SET CONTEXT_INFO to set the value to the column in
master..sysprocesses table. See details on SQL Server Books Online.
I am not sure if you can do this with a connection string. The proper
way would be using a command object or a connection object and use
its Execute Method.

--
- Anith
(Please respond only to newsgroups)



Sun, 03 Jul 2005 05:47:00 GMT
 Setting CONTEXT_INFO from the Connection String?
Thanks, Anith!

Yeah...I was hoping to potentially be able to do it right from the
connection string to avoid having to essentially replace all instances of
Open() in our .ASP code (of which there are a million ;-), with some global
function that did the Open() and the "SET CONTEXT_INFO X" call.  In essence,
it was easier for me to

I was looking at the Language specifier of the connection string, and

hoped that maybe I could leverage something similar within the connection
string for the CONTEXT_INFO.

Additional thoughts?


Quote:
> You can use SET CONTEXT_INFO to set the value to the column in
> master..sysprocesses table. See details on SQL Server Books Online.
> I am not sure if you can do this with a connection string. The proper
> way would be using a command object or a connection object and use
> its Execute Method.

> --
> - Anith
> (Please respond only to newsgroups)



Sun, 03 Jul 2005 06:11:04 GMT
 Setting CONTEXT_INFO from the Connection String?
John,

I created an empty text file and renamed it a.udl. I then double-clicked it and drove the UI. In
the UI, there's a setting called "Extended Properties". I don't know exactly what that is, but
after setting this to "MyProp", I then opened the file a.udl (a text file) and this is what I
got:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data Source=tiborK;Extended Properties=MyProp;Application Name=App Name

Please let us know if the Extended Properties option indeed is the CONTEXT INFO...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> (SQL Server 2000, SP2)

> Hello, all!

> Sorry about the cross-posting, but I wasn't quite certain to where this
> question might best pertain.

> I'm wondering if Transact-SQL can obtain any "user-defined" information that
> might be provided on the connection string of an ADODB.Connection object?
> (I don't even know if it's possible to *have* any user-defined information
> in the connection string, or if it will be ignored.)

> In essense, I would like to subtly change our connection string to append
> something like ";Foo=test" and then be able to potentially reference that
> "Foo=test" from Transact-SQL.

> My guess is that it's not going to be quite so simple.  I've looked at the
> CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could somehow
> get some information into that field from the ADODB.Connection.Open()
> method.

> For example, if I could do something like:

>    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> ID=sa;Password=;Initial Catalog=tempdb;"

>    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

>    Set Cn = CreateObject("ADODB.Connection")
>    Call cn.Open(sConnect)

> And have this call somehow "magically" set the CONTEXT_INFO to "MyData",
> such that it can be referenced from Transact-SQL.

> I'd like to be able to do this from the ADODB.Connection.Open() method via
> the connection string, rather than in a separate call (if at all possible),
> to have to avoid changing a million Open calls in an already established
> .ASP project.  I have the connection string set as a Session variable in one
> place, and changing it once and having it essentially propagate would be
> fantastic.

> Any thoughts/comments/criticisms would be *very* welcome.  Thanks!  :-)

> John Peterson



Sun, 03 Jul 2005 17:33:23 GMT
 Setting CONTEXT_INFO from the Connection String?
Tibor,

Now that I knew that the possibility existed of an "Extended Properties"
specifier for the connection string, I did a little digging:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oled...
ledbconnectionstringsyntax.asp

That link shows an example of how to set the Extended Properties specifier,
and I see that it's really "ExtendedProperties" (no space).

I tried that, and it appears to make ADO/OLE DB much happier!  There still
isn't any evidence of whether that sets the CONTEXT_INFO, but I'm still
pursuing that...

I wish that there was some better documentation with respect to this
ExtendedProperties specifier, and whether that information was accessible
from SQL Server somehow...


Quote:
> Hello, Tibor!

> You gave me such hope for a minute that I might be able to reference that
> "Extended Properties" specifier in the connection string!

> Here is what I'm using (I tried to duplicate yours exactly), and I've
> attached the script (rename it to a .VBS extension):

> sConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=tempdb;Data Source=Scout;Extended
> Properties=MyProp;Application Name=App Name;"

> However, I get an error in my .VBS script:

> Error: Invalid connection string attribute
> Code: 80004005
> Source: Microsoft OLE DB Provider for SQL Server

> I had hoped that maybe this would leverage the CONTEXT_INFO, or even maybe
> the sp_addextendedproperty in some way.

> I'm running under Windows 2000, and I believe I have all the updates (and
> latest MDAC).

> If you have any more insight into this, I would be *truly* appreciative!
> :-)

> John Peterson

> "Tibor Karaszi"


- Show quoted text -

Quote:

> > John,

> > I created an empty text file and renamed it a.udl. I then double-clicked
> it and drove the UI. In
> > the UI, there's a setting called "Extended Properties". I don't know
> exactly what that is, but
> > after setting this to "MyProp", I then opened the file a.udl (a text
file)
> and this is what I
> > got:

> > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial
> > Catalog=Northwind;Data Source=tiborK;Extended
> Properties=MyProp;Application Name=App Name

> > Please let us know if the Extended Properties option indeed is the
CONTEXT
> INFO...
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -

Quote:



> > > (SQL Server 2000, SP2)

> > > Hello, all!

> > > Sorry about the cross-posting, but I wasn't quite certain to where
this
> > > question might best pertain.

> > > I'm wondering if Transact-SQL can obtain any "user-defined"
information
> that
> > > might be provided on the connection string of an ADODB.Connection
> object?
> > > (I don't even know if it's possible to *have* any user-defined
> information
> > > in the connection string, or if it will be ignored.)

> > > In essense, I would like to subtly change our connection string to
> append
> > > something like ";Foo=test" and then be able to potentially reference
> that
> > > "Foo=test" from Transact-SQL.

> > > My guess is that it's not going to be quite so simple.  I've looked at
> the
> > > CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could
> somehow
> > > get some information into that field from the ADODB.Connection.Open()
> > > method.

> > > For example, if I could do something like:

> > >    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> > > ID=sa;Password=;Initial Catalog=tempdb;"

> > >    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

> > >    Set Cn = CreateObject("ADODB.Connection")
> > >    Call cn.Open(sConnect)

> > > And have this call somehow "magically" set the CONTEXT_INFO to
"MyData",
> > > such that it can be referenced from Transact-SQL.

> > > I'd like to be able to do this from the ADODB.Connection.Open() method
> via
> > > the connection string, rather than in a separate call (if at all
> possible),
> > > to have to avoid changing a million Open calls in an already
established
> > > .ASP project.  I have the connection string set as a Session variable
in
> one
> > > place, and changing it once and having it essentially propagate would
be
> > > fantastic.

> > > Any thoughts/comments/criticisms would be *very* welcome.  Thanks!
:-)

> > > John Peterson



Sun, 03 Jul 2005 23:50:13 GMT
 Setting CONTEXT_INFO from the Connection String?
Tibor,

My research *seems* to suggest that DBPROP_INIT_PROVIDERSTRING (which is the
ExtendedProperties specifier on the connection string), doesn't appear to be
"exposed" to SQL Server at the Transact-SQL level.  That is, it doesn't look
like Transact-SQL has any way to obtain the information contained in the
ExtendedProperties specifier.

I've tried setting it in the connection string, and checking both the
CONTEXT_INFO, as well as the ::fn_listextendedproperty, thinking maybe it
got implemented as a SQL Server extended property somehow.  In both cases, I
don't see any evidence that the ExtendedProperties value was propagated to
SQL Server.

I *did* notice that the "Application Name" specifier for the connection
string *is* propagated to SQL Server, and can be accessed from the
master..sysprocesses table in the program_name field.  I suppose, in a
pinch, I might be able to somehow leverage that...but I had really hoped for
a more "endorsed" mechanism.

From what I can tell, it seems as if the ExtendedProperties and the
CONTEXT_INFO aren't related (but, they almost *should* be, IMO).

If you (or anyone else) know where else I might investigate whether the
ExtendedProperties specifier might reside in SQL Server, I'd be hugely
obliged!

Thanks again!

John Peterson


Quote:
> Tibor,

> Now that I knew that the possibility existed of an "Extended Properties"
> specifier for the connection string, I did a little digging:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oled...
Quote:
> ledbconnectionstringsyntax.asp

> That link shows an example of how to set the Extended Properties
specifier,
> and I see that it's really "ExtendedProperties" (no space).

> I tried that, and it appears to make ADO/OLE DB much happier!  There still
> isn't any evidence of whether that sets the CONTEXT_INFO, but I'm still
> pursuing that...

> I wish that there was some better documentation with respect to this
> ExtendedProperties specifier, and whether that information was accessible
> from SQL Server somehow...



> > Hello, Tibor!

> > You gave me such hope for a minute that I might be able to reference
that
> > "Extended Properties" specifier in the connection string!

> > Here is what I'm using (I tried to duplicate yours exactly), and I've
> > attached the script (rename it to a .VBS extension):

> > sConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security
> > Info=False;Initial Catalog=tempdb;Data Source=Scout;Extended
> > Properties=MyProp;Application Name=App Name;"

> > However, I get an error in my .VBS script:

> > Error: Invalid connection string attribute
> > Code: 80004005
> > Source: Microsoft OLE DB Provider for SQL Server

> > I had hoped that maybe this would leverage the CONTEXT_INFO, or even
maybe
> > the sp_addextendedproperty in some way.

> > I'm running under Windows 2000, and I believe I have all the updates
(and
> > latest MDAC).

> > If you have any more insight into this, I would be *truly* appreciative!
> > :-)

> > John Peterson

> > "Tibor Karaszi"


> > > John,

> > > I created an empty text file and renamed it a.udl. I then
double-clicked
> > it and drove the UI. In
> > > the UI, there's a setting called "Extended Properties". I don't know
> > exactly what that is, but
> > > after setting this to "MyProp", I then opened the file a.udl (a text
> file)
> > and this is what I
> > > got:

> > > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> > Info=False;Initial
> > > Catalog=Northwind;Data Source=tiborK;Extended
> > Properties=MyProp;Application Name=App Name

> > > Please let us know if the Extended Properties option indeed is the
> CONTEXT
> > INFO...
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -

Quote:



> > > > (SQL Server 2000, SP2)

> > > > Hello, all!

> > > > Sorry about the cross-posting, but I wasn't quite certain to where
> this
> > > > question might best pertain.

> > > > I'm wondering if Transact-SQL can obtain any "user-defined"
> information
> > that
> > > > might be provided on the connection string of an ADODB.Connection
> > object?
> > > > (I don't even know if it's possible to *have* any user-defined
> > information
> > > > in the connection string, or if it will be ignored.)

> > > > In essense, I would like to subtly change our connection string to
> > append
> > > > something like ";Foo=test" and then be able to potentially reference
> > that
> > > > "Foo=test" from Transact-SQL.

> > > > My guess is that it's not going to be quite so simple.  I've looked
at
> > the
> > > > CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could
> > somehow
> > > > get some information into that field from the

ADODB.Connection.Open()

- Show quoted text -

Quote:
> > > > method.

> > > > For example, if I could do something like:

> > > >    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> > > > ID=sa;Password=;Initial Catalog=tempdb;"

> > > >    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

> > > >    Set Cn = CreateObject("ADODB.Connection")
> > > >    Call cn.Open(sConnect)

> > > > And have this call somehow "magically" set the CONTEXT_INFO to
> "MyData",
> > > > such that it can be referenced from Transact-SQL.

> > > > I'd like to be able to do this from the ADODB.Connection.Open()
method
> > via
> > > > the connection string, rather than in a separate call (if at all
> > possible),
> > > > to have to avoid changing a million Open calls in an already
> established
> > > > .ASP project.  I have the connection string set as a Session
variable
> in
> > one
> > > > place, and changing it once and having it essentially propagate
would
> be
> > > > fantastic.

> > > > Any thoughts/comments/criticisms would be *very* welcome.  Thanks!
> :-)

> > > > John Peterson



Mon, 04 Jul 2005 00:57:41 GMT
 Setting CONTEXT_INFO from the Connection String?
John,

Quote:
> From what I can tell, it seems as if the ExtendedProperties and the
> CONTEXT_INFO aren't related (but, they almost *should* be, IMO).

It was a long shot in the first place...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> Tibor,

> My research *seems* to suggest that DBPROP_INIT_PROVIDERSTRING (which is the
> ExtendedProperties specifier on the connection string), doesn't appear to be
> "exposed" to SQL Server at the Transact-SQL level.  That is, it doesn't look
> like Transact-SQL has any way to obtain the information contained in the
> ExtendedProperties specifier.

> I've tried setting it in the connection string, and checking both the
> CONTEXT_INFO, as well as the ::fn_listextendedproperty, thinking maybe it
> got implemented as a SQL Server extended property somehow.  In both cases, I
> don't see any evidence that the ExtendedProperties value was propagated to
> SQL Server.

> I *did* notice that the "Application Name" specifier for the connection
> string *is* propagated to SQL Server, and can be accessed from the
> master..sysprocesses table in the program_name field.  I suppose, in a
> pinch, I might be able to somehow leverage that...but I had really hoped for
> a more "endorsed" mechanism.

> From what I can tell, it seems as if the ExtendedProperties and the
> CONTEXT_INFO aren't related (but, they almost *should* be, IMO).

> If you (or anyone else) know where else I might investigate whether the
> ExtendedProperties specifier might reside in SQL Server, I'd be hugely
> obliged!

> Thanks again!

> John Peterson



> > Tibor,

> > Now that I knew that the possibility existed of an "Extended Properties"
> > specifier for the connection string, I did a little digging:

> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oled...
> > ledbconnectionstringsyntax.asp

> > That link shows an example of how to set the Extended Properties
> specifier,
> > and I see that it's really "ExtendedProperties" (no space).

> > I tried that, and it appears to make ADO/OLE DB much happier!  There still
> > isn't any evidence of whether that sets the CONTEXT_INFO, but I'm still
> > pursuing that...

> > I wish that there was some better documentation with respect to this
> > ExtendedProperties specifier, and whether that information was accessible
> > from SQL Server somehow...



> > > Hello, Tibor!

> > > You gave me such hope for a minute that I might be able to reference
> that
> > > "Extended Properties" specifier in the connection string!

> > > Here is what I'm using (I tried to duplicate yours exactly), and I've
> > > attached the script (rename it to a .VBS extension):

> > > sConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security
> > > Info=False;Initial Catalog=tempdb;Data Source=Scout;Extended
> > > Properties=MyProp;Application Name=App Name;"

> > > However, I get an error in my .VBS script:

> > > Error: Invalid connection string attribute
> > > Code: 80004005
> > > Source: Microsoft OLE DB Provider for SQL Server

> > > I had hoped that maybe this would leverage the CONTEXT_INFO, or even
> maybe
> > > the sp_addextendedproperty in some way.

> > > I'm running under Windows 2000, and I believe I have all the updates
> (and
> > > latest MDAC).

> > > If you have any more insight into this, I would be *truly* appreciative!
> > > :-)

> > > John Peterson

> > > "Tibor Karaszi"


> > > > John,

> > > > I created an empty text file and renamed it a.udl. I then
> double-clicked
> > > it and drove the UI. In
> > > > the UI, there's a setting called "Extended Properties". I don't know
> > > exactly what that is, but
> > > > after setting this to "MyProp", I then opened the file a.udl (a text
> > file)
> > > and this is what I
> > > > got:

> > > > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> > > Info=False;Initial
> > > > Catalog=Northwind;Data Source=tiborK;Extended
> > > Properties=MyProp;Application Name=App Name

> > > > Please let us know if the Extended Properties option indeed is the
> > CONTEXT
> > > INFO...
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:

> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > > > > (SQL Server 2000, SP2)

> > > > > Hello, all!

> > > > > Sorry about the cross-posting, but I wasn't quite certain to where
> > this
> > > > > question might best pertain.

> > > > > I'm wondering if Transact-SQL can obtain any "user-defined"
> > information
> > > that
> > > > > might be provided on the connection string of an ADODB.Connection
> > > object?
> > > > > (I don't even know if it's possible to *have* any user-defined
> > > information
> > > > > in the connection string, or if it will be ignored.)

> > > > > In essense, I would like to subtly change our connection string to
> > > append
> > > > > something like ";Foo=test" and then be able to potentially reference
> > > that
> > > > > "Foo=test" from Transact-SQL.

> > > > > My guess is that it's not going to be quite so simple.  I've looked
> at
> > > the
> > > > > CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could
> > > somehow
> > > > > get some information into that field from the
> ADODB.Connection.Open()
> > > > > method.

> > > > > For example, if I could do something like:

> > > > >    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> > > > > ID=sa;Password=;Initial Catalog=tempdb;"

> > > > >    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

> > > > >    Set Cn = CreateObject("ADODB.Connection")
> > > > >    Call cn.Open(sConnect)

> > > > > And have this call somehow "magically" set the CONTEXT_INFO to
> > "MyData",
> > > > > such that it can be referenced from Transact-SQL.

> > > > > I'd like to be able to do this from the ADODB.Connection.Open()
> method
> > > via
> > > > > the connection string, rather than in a separate call (if at all
> > > possible),
> > > > > to have to avoid changing a million Open calls in an already
> > established
> > > > > .ASP project.  I have the connection string set as a Session
> variable
> > in
> > > one
> > > > > place, and changing it once and having it essentially propagate
> would
> > be
> > > > > fantastic.

> > > > > Any thoughts/comments/criticisms would be *very* welcome.  Thanks!
> > :-)

> > > > > John Peterson



Mon, 04 Jul 2005 23:13:35 GMT
 Setting CONTEXT_INFO from the Connection String?
John,

I went through the same exercise a while back, but I couldn't find any way
to recognize any additional info in the connection string from the db side.
I eventually gave up and decided to use a stored proc to pass the info I
wanted into CONTEXT_INFO.  However, client code can be somewhat simplified
by creating a procedure to use as a substitute for the connection's Open
method that both opens the connection and calls the "session prep" stored
proc.  Unfortunately, this isn't quite as convenient as a single-location
modification to the connection string--for new projects, I'm planning on
using a connection wrapper object to isolate the data access layer and its
callers from the mechanics of this sort of thing.

Nicole


Quote:
> Tibor,

> My research *seems* to suggest that DBPROP_INIT_PROVIDERSTRING (which is
the
> ExtendedProperties specifier on the connection string), doesn't appear to
be
> "exposed" to SQL Server at the Transact-SQL level.  That is, it doesn't
look
> like Transact-SQL has any way to obtain the information contained in the
> ExtendedProperties specifier.

> I've tried setting it in the connection string, and checking both the
> CONTEXT_INFO, as well as the ::fn_listextendedproperty, thinking maybe it
> got implemented as a SQL Server extended property somehow.  In both cases,
I
> don't see any evidence that the ExtendedProperties value was propagated to
> SQL Server.

> I *did* notice that the "Application Name" specifier for the connection
> string *is* propagated to SQL Server, and can be accessed from the
> master..sysprocesses table in the program_name field.  I suppose, in a
> pinch, I might be able to somehow leverage that...but I had really hoped
for
> a more "endorsed" mechanism.

> From what I can tell, it seems as if the ExtendedProperties and the
> CONTEXT_INFO aren't related (but, they almost *should* be, IMO).

> If you (or anyone else) know where else I might investigate whether the
> ExtendedProperties specifier might reside in SQL Server, I'd be hugely
> obliged!

> Thanks again!

> John Peterson



> > Tibor,

> > Now that I knew that the possibility existed of an "Extended Properties"
> > specifier for the connection string, I did a little digging:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oled...

- Show quoted text -

Quote:
> > ledbconnectionstringsyntax.asp

> > That link shows an example of how to set the Extended Properties
> specifier,
> > and I see that it's really "ExtendedProperties" (no space).

> > I tried that, and it appears to make ADO/OLE DB much happier!  There
still
> > isn't any evidence of whether that sets the CONTEXT_INFO, but I'm still
> > pursuing that...

> > I wish that there was some better documentation with respect to this
> > ExtendedProperties specifier, and whether that information was
accessible
> > from SQL Server somehow...



> > > Hello, Tibor!

> > > You gave me such hope for a minute that I might be able to reference
> that
> > > "Extended Properties" specifier in the connection string!

> > > Here is what I'm using (I tried to duplicate yours exactly), and I've
> > > attached the script (rename it to a .VBS extension):

> > > sConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security
> > > Info=False;Initial Catalog=tempdb;Data Source=Scout;Extended
> > > Properties=MyProp;Application Name=App Name;"

> > > However, I get an error in my .VBS script:

> > > Error: Invalid connection string attribute
> > > Code: 80004005
> > > Source: Microsoft OLE DB Provider for SQL Server

> > > I had hoped that maybe this would leverage the CONTEXT_INFO, or even
> maybe
> > > the sp_addextendedproperty in some way.

> > > I'm running under Windows 2000, and I believe I have all the updates
> (and
> > > latest MDAC).

> > > If you have any more insight into this, I would be *truly*
appreciative!
> > > :-)

> > > John Peterson

> > > "Tibor Karaszi"


> > > > John,

> > > > I created an empty text file and renamed it a.udl. I then
> double-clicked
> > > it and drove the UI. In
> > > > the UI, there's a setting called "Extended Properties". I don't know
> > > exactly what that is, but
> > > > after setting this to "MyProp", I then opened the file a.udl (a text
> > file)
> > > and this is what I
> > > > got:

> > > > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> > > Info=False;Initial
> > > > Catalog=Northwind;Data Source=tiborK;Extended
> > > Properties=MyProp;Application Name=App Name

> > > > Please let us know if the Extended Properties option indeed is the
> > CONTEXT
> > > INFO...
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -

Quote:



> > > > > (SQL Server 2000, SP2)

> > > > > Hello, all!

> > > > > Sorry about the cross-posting, but I wasn't quite certain to where
> > this
> > > > > question might best pertain.

> > > > > I'm wondering if Transact-SQL can obtain any "user-defined"
> > information
> > > that
> > > > > might be provided on the connection string of an ADODB.Connection
> > > object?
> > > > > (I don't even know if it's possible to *have* any user-defined
> > > information
> > > > > in the connection string, or if it will be ignored.)

> > > > > In essense, I would like to subtly change our connection string to
> > > append
> > > > > something like ";Foo=test" and then be able to potentially
reference
> > > that
> > > > > "Foo=test" from Transact-SQL.

> > > > > My guess is that it's not going to be quite so simple.  I've
looked
> at
> > > the
> > > > > CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could
> > > somehow
> > > > > get some information into that field from the
> ADODB.Connection.Open()
> > > > > method.

> > > > > For example, if I could do something like:

> > > > >    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> > > > > ID=sa;Password=;Initial Catalog=tempdb;"

> > > > >    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

> > > > >    Set Cn = CreateObject("ADODB.Connection")
> > > > >    Call cn.Open(sConnect)

> > > > > And have this call somehow "magically" set the CONTEXT_INFO to
> > "MyData",
> > > > > such that it can be referenced from Transact-SQL.

> > > > > I'd like to be able to do this from the ADODB.Connection.Open()
> method
> > > via
> > > > > the connection string, rather than in a separate call (if at all
> > > possible),
> > > > > to have to avoid changing a million Open calls in an already
> > established
> > > > > .ASP project.  I have the connection string set as a Session
> variable
> > in
> > > one
> > > > > place, and changing it once and having it essentially propagate
> would
> > be
> > > > > fantastic.

> > > > > Any thoughts/comments/criticisms would be *very* welcome.  Thanks!
> > :-)

> > > > > John Peterson



Wed, 06 Jul 2005 01:40:43 GMT
 Setting CONTEXT_INFO from the Connection String?
Thanks for the information, Nicole!

Yeah...that was exactly my dilemma.  As you've indicated, it's not technically difficult
to replace the Open() instances with a function that would do the Open() and the Execute()
(to set the CONTEXT_INFO).  Unfortunately, we didn't have the foresight to have started
this project with a convenient wrapper for the connection, and there are hundreds of
Open() calls in hundreds of files.  I had hoped to avoid modifying all these files and the
implied regression testing.

At this juncture, since we weren't using the "Application Name" specifier in the
connection string, and it looked like program_name was in the same table
(master..sysprocesses) as context_info, we kind of "punted" and are using the "Application
Name" from the connection string in the same way that I had hoped with the CONTEXT_INFO.

Thanks again for sharing your experience!  :-)


Quote:
> John,

> I went through the same exercise a while back, but I couldn't find any way
> to recognize any additional info in the connection string from the db side.
> I eventually gave up and decided to use a stored proc to pass the info I
> wanted into CONTEXT_INFO.  However, client code can be somewhat simplified
> by creating a procedure to use as a substitute for the connection's Open
> method that both opens the connection and calls the "session prep" stored
> proc.  Unfortunately, this isn't quite as convenient as a single-location
> modification to the connection string--for new projects, I'm planning on
> using a connection wrapper object to isolate the data access layer and its
> callers from the mechanics of this sort of thing.

> Nicole



> > Tibor,

> > My research *seems* to suggest that DBPROP_INIT_PROVIDERSTRING (which is
> the
> > ExtendedProperties specifier on the connection string), doesn't appear to
> be
> > "exposed" to SQL Server at the Transact-SQL level.  That is, it doesn't
> look
> > like Transact-SQL has any way to obtain the information contained in the
> > ExtendedProperties specifier.

> > I've tried setting it in the connection string, and checking both the
> > CONTEXT_INFO, as well as the ::fn_listextendedproperty, thinking maybe it
> > got implemented as a SQL Server extended property somehow.  In both cases,
> I
> > don't see any evidence that the ExtendedProperties value was propagated to
> > SQL Server.

> > I *did* notice that the "Application Name" specifier for the connection
> > string *is* propagated to SQL Server, and can be accessed from the
> > master..sysprocesses table in the program_name field.  I suppose, in a
> > pinch, I might be able to somehow leverage that...but I had really hoped
> for
> > a more "endorsed" mechanism.

> > From what I can tell, it seems as if the ExtendedProperties and the
> > CONTEXT_INFO aren't related (but, they almost *should* be, IMO).

> > If you (or anyone else) know where else I might investigate whether the
> > ExtendedProperties specifier might reside in SQL Server, I'd be hugely
> > obliged!

> > Thanks again!

> > John Peterson



> > > Tibor,

> > > Now that I knew that the possibility existed of an "Extended Properties"
> > > specifier for the connection string, I did a little digging:

> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oled...
> > > ledbconnectionstringsyntax.asp

> > > That link shows an example of how to set the Extended Properties
> > specifier,
> > > and I see that it's really "ExtendedProperties" (no space).

> > > I tried that, and it appears to make ADO/OLE DB much happier!  There
> still
> > > isn't any evidence of whether that sets the CONTEXT_INFO, but I'm still
> > > pursuing that...

> > > I wish that there was some better documentation with respect to this
> > > ExtendedProperties specifier, and whether that information was
> accessible
> > > from SQL Server somehow...



> > > > Hello, Tibor!

> > > > You gave me such hope for a minute that I might be able to reference
> > that
> > > > "Extended Properties" specifier in the connection string!

> > > > Here is what I'm using (I tried to duplicate yours exactly), and I've
> > > > attached the script (rename it to a .VBS extension):

> > > > sConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> > Security
> > > > Info=False;Initial Catalog=tempdb;Data Source=Scout;Extended
> > > > Properties=MyProp;Application Name=App Name;"

> > > > However, I get an error in my .VBS script:

> > > > Error: Invalid connection string attribute
> > > > Code: 80004005
> > > > Source: Microsoft OLE DB Provider for SQL Server

> > > > I had hoped that maybe this would leverage the CONTEXT_INFO, or even
> > maybe
> > > > the sp_addextendedproperty in some way.

> > > > I'm running under Windows 2000, and I believe I have all the updates
> > (and
> > > > latest MDAC).

> > > > If you have any more insight into this, I would be *truly*
> appreciative!
> > > > :-)

> > > > John Peterson

> > > > "Tibor Karaszi"


> > > > > John,

> > > > > I created an empty text file and renamed it a.udl. I then
> > double-clicked
> > > > it and drove the UI. In
> > > > > the UI, there's a setting called "Extended Properties". I don't know
> > > > exactly what that is, but
> > > > > after setting this to "MyProp", I then opened the file a.udl (a text
> > > file)
> > > > and this is what I
> > > > > got:

> > > > > Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> > > > Info=False;Initial
> > > > > Catalog=Northwind;Data Source=tiborK;Extended
> > > > Properties=MyProp;Application Name=App Name

> > > > > Please let us know if the Extended Properties option indeed is the
> > > CONTEXT
> > > > INFO...
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at:

> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > > > > > (SQL Server 2000, SP2)

> > > > > > Hello, all!

> > > > > > Sorry about the cross-posting, but I wasn't quite certain to where
> > > this
> > > > > > question might best pertain.

> > > > > > I'm wondering if Transact-SQL can obtain any "user-defined"
> > > information
> > > > that
> > > > > > might be provided on the connection string of an ADODB.Connection
> > > > object?
> > > > > > (I don't even know if it's possible to *have* any user-defined
> > > > information
> > > > > > in the connection string, or if it will be ignored.)

> > > > > > In essense, I would like to subtly change our connection string to
> > > > append
> > > > > > something like ";Foo=test" and then be able to potentially
> reference
> > > > that
> > > > > > "Foo=test" from Transact-SQL.

> > > > > > My guess is that it's not going to be quite so simple.  I've
> looked
> > at
> > > > the
> > > > > > CONTEXT_INFO feature of SQL Server, and it'd be *ideal* if I could
> > > > somehow
> > > > > > get some information into that field from the
> > ADODB.Connection.Open()
> > > > > > method.

> > > > > > For example, if I could do something like:

> > > > > >    sConnect = "Provider=SQLOLEDB;Data Source=(local);User
> > > > > > ID=sa;Password=;Initial Catalog=tempdb;"

> > > > > >    sConnect = sConnect & ";" & "CONTEXT_INFO=MyData"

> > > > > >    Set Cn = CreateObject("ADODB.Connection")
> > > > > >    Call cn.Open(sConnect)

> > > > > > And have this call somehow "magically" set the CONTEXT_INFO to
> > > "MyData",
> > > > > > such that it can be referenced from Transact-SQL.

> > > > > > I'd like to be able to do this from the ADODB.Connection.Open()
> > method
> > > > via
> > > > > > the connection string, rather than in a separate call (if at all
> > > > possible),
> > > > > > to have to avoid changing a million Open calls in an already
> > > established
> > > > > > .ASP project.  I have the connection string set as a Session
> > variable
> > > in
> > > > one
> > > > > > place, and changing it once and having it essentially propagate
> > would
> > > be
> > > > > > fantastic.

> > > > > > Any thoughts/comments/criticisms would be *very* welcome.  Thanks!
> > > :-)

> > > > > > John Peterson



Wed, 06 Jul 2005 04:43:35 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Problem using "Set Context_Info"

2. SET CONTEXT_INFO

3. SET CONTEXT_INFO

4. How to set connection string for PTable?

5. dsn-less connection string property settings

6. dynamically setting source and connection string.

7. Setting ADODC Connection String at run time

8. Setting connection string to db on network drive

9. Data Environment and setting the connection string via code

10. Setting adodc connection string at runtime?

11. ADO : Connection String for a VPN connection ?

12. Connection fails due to: invalid connection string attribute (01S00)


 
Powered by phpBB® Forum Software