I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
Maury
Why use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury
|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon
|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts
Tuesday, March 27, 2012
Are all DSN's really ODBC?
I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't reall
y
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
" Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstra
ctions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstr
actions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. Th
ere exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, b
ut
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's i
n
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't rea
lly
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maurysql
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't reall
y
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
" Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstra
ctions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstr
actions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. Th
ere exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, b
ut
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's i
n
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't rea
lly
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maurysql
Are all DSN's really ODBC?
I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
--
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
--
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
Wednesday, March 7, 2012
Application Roles ENCRYPT function Valid Password Characters
When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
get an ODBC error when certain otherwise good characters are used in the
password. What characters are and are not allowed for passwords for
application roles while using the ENCRYPT function?You might ask me what is a good password? Here is a sample:
wZ726BcF_vR?goLVmxgsGLkZpqQbZ<Yfu<?L<t
RQzCa85c?"DXtI,QPbUtIyBSJbqF?
(without the line return)
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message
news:ebySvpBfFHA.3944@.TK2MSFTNGP10.phx.gbl...
> When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
> get an ODBC error when certain otherwise good characters are used in the
> password. What characters are and are not allowed for passwords for
> application roles while using the ENCRYPT function?
>|||You can find the valid characters in the books online help
topic: Security Rules.
You can find the topic in the index under passwords, rules
for
-Sue
On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
<charles.hawkins@.NOSPAMjenzabar.net> wrote:
>When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
>get an ODBC error when certain otherwise good characters are used in the
>password. What characters are and are not allowed for passwords for
>application roles while using the ENCRYPT function?
>|||Thank you, Sue. I went back and re-wrote my password generation script to
remove references to the unallowed characters mentioned in Security Rules
for passwords - []{}(),;?*! @..
I'm still having the problem with the ENCRYPT function. I execute:
sp_setapprole
@.rolename = 'TEST',
@.password = {Encrypt N
'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??D
k2?q6L?EvrvI1mOENycWpLvz
jL?3kn'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
And get:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I know I don't have a syntax error (other than an ugly password). When I
switch the TEST app role over to a password of 'easy', it works.
Am I supposed to put braces [] around the password somehow?
So the question remains, what characters are not allowed for passwords? I
know []{}(),;?*! @.. are not, but I don't have any of these.
Chuck
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||Incidentally, here is the ugly password generation code:
set nocount on
declare @.counter int,
@.password varchar(128),
@.char char(1),
@.charindex int,
@.loop int
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@. = 64
[ = 91
] = 93
{ = 123
} = 125
*/
select @.counter = 1, @.password = ''
while @.counter < 2
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 65 and 90 or @.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 5
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if --@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
--or
(@.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 11
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 129
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
or @.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
select RTRIM(@.password) AS Password
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||What I've discovered:
The password characters are not allowed for or the canonical ENCRYPT functio
n does not work with characters with the following ASCII codes:
(33,40,41,42,59,63,64,91,93,123,125,130,
132,133,134,135,136,137,139,161,162,
166,167,168,169,171,172,173,174,175,176,
177,180,182,184,187,188,189,190,191,
215,247)
Further, in order for the ENCRYPT function to work, the password cannot be m
ore than 64 characters (vice 128 allowed in Security Rules).
All that said, it still doesn't work. When I enter the following code, I can
not get the SP_SETAPPROLE to work:
exec sp_dropapprole 'TEST_APPROLE'
go
exec sp_addapprole
@.rolename = 'TEST_APPROLE',
@.password = 'rwq4?37ctEPn0izwhJ6dq
dACSZcfmfia?fWG
'
go
sp_setapprole
@.rolename = 'TEST_APPROLE',
@.password = {Encrypt N 'rwq4?37ctEPn0izwhJ6dq
dACSZc
fmfia?fWG'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
Server: Msg 2764, Level 16, State 1, Procedure sp_setapprole, Line 41
Incorrect password supplied for application role 'TEST_APPROLE'.
So the question remains, what are valid password characters for application
roles in order for the ENCRYPT function to work?
And now we have a new question, why does the ENCRYPT function limit you to 6
4 characters? I have my suppositions but I'd love to hear from someone who k
nows.
Chuck Hawkins
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message news:OUXAtKKfFHA.572@.T
K2MSFTNGP15.phx.gbl...
> Thank you, Sue. I went back and re-wrote my password generation script to
> remove references to the unallowed characters mentioned in Security Rules
> for passwords - []{}(),;?*! @..
> I'm still having the problem with the ENCRYPT function. I execute:
>
> sp_setapprole
> @.rolename = 'TEST',
> @.password = {Encrypt N
> 'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??
Dk2?q6L?EvrvI1mOENycWpLv
zjL?3kn'}
> --@.password = {Encrypt N 'easy'}
> ,@.encrypt = 'odbc'
> go
>
> And get:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
>
> I know I don't have a syntax error (other than an ugly password). When I
> switch the TEST app role over to a password of 'easy', it works.
>
> Am I supposed to put braces [] around the password somehow?
>
> So the question remains, what characters are not allowed for passwords? I
> know []{}(),;?*! @.. are not, but I don't have any of these.
>
> Chuck
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
>
>
get an ODBC error when certain otherwise good characters are used in the
password. What characters are and are not allowed for passwords for
application roles while using the ENCRYPT function?You might ask me what is a good password? Here is a sample:
wZ726BcF_vR?goLVmxgsGLkZpqQbZ<Yfu<?L<t
RQzCa85c?"DXtI,QPbUtIyBSJbqF?
(without the line return)
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message
news:ebySvpBfFHA.3944@.TK2MSFTNGP10.phx.gbl...
> When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
> get an ODBC error when certain otherwise good characters are used in the
> password. What characters are and are not allowed for passwords for
> application roles while using the ENCRYPT function?
>|||You can find the valid characters in the books online help
topic: Security Rules.
You can find the topic in the index under passwords, rules
for
-Sue
On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
<charles.hawkins@.NOSPAMjenzabar.net> wrote:
>When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
>get an ODBC error when certain otherwise good characters are used in the
>password. What characters are and are not allowed for passwords for
>application roles while using the ENCRYPT function?
>|||Thank you, Sue. I went back and re-wrote my password generation script to
remove references to the unallowed characters mentioned in Security Rules
for passwords - []{}(),;?*! @..
I'm still having the problem with the ENCRYPT function. I execute:
sp_setapprole
@.rolename = 'TEST',
@.password = {Encrypt N
'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??D
k2?q6L?EvrvI1mOENycWpLvz
jL?3kn'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
And get:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I know I don't have a syntax error (other than an ugly password). When I
switch the TEST app role over to a password of 'easy', it works.
Am I supposed to put braces [] around the password somehow?
So the question remains, what characters are not allowed for passwords? I
know []{}(),;?*! @.. are not, but I don't have any of these.
Chuck
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||Incidentally, here is the ugly password generation code:
set nocount on
declare @.counter int,
@.password varchar(128),
@.char char(1),
@.charindex int,
@.loop int
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@. = 64
[ = 91
] = 93
{ = 123
} = 125
*/
select @.counter = 1, @.password = ''
while @.counter < 2
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 65 and 90 or @.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 5
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if --@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
--or
(@.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 11
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 129
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
or @.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
select RTRIM(@.password) AS Password
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||What I've discovered:
The password characters are not allowed for or the canonical ENCRYPT functio
n does not work with characters with the following ASCII codes:
(33,40,41,42,59,63,64,91,93,123,125,130,
132,133,134,135,136,137,139,161,162,
166,167,168,169,171,172,173,174,175,176,
177,180,182,184,187,188,189,190,191,
215,247)
Further, in order for the ENCRYPT function to work, the password cannot be m
ore than 64 characters (vice 128 allowed in Security Rules).
All that said, it still doesn't work. When I enter the following code, I can
not get the SP_SETAPPROLE to work:
exec sp_dropapprole 'TEST_APPROLE'
go
exec sp_addapprole
@.rolename = 'TEST_APPROLE',
@.password = 'rwq4?37ctEPn0izwhJ6dq
dACSZcfmfia?fWG
'
go
sp_setapprole
@.rolename = 'TEST_APPROLE',
@.password = {Encrypt N 'rwq4?37ctEPn0izwhJ6dq
dACSZc
fmfia?fWG'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
Server: Msg 2764, Level 16, State 1, Procedure sp_setapprole, Line 41
Incorrect password supplied for application role 'TEST_APPROLE'.
So the question remains, what are valid password characters for application
roles in order for the ENCRYPT function to work?
And now we have a new question, why does the ENCRYPT function limit you to 6
4 characters? I have my suppositions but I'd love to hear from someone who k
nows.
Chuck Hawkins
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message news:OUXAtKKfFHA.572@.T
K2MSFTNGP15.phx.gbl...
> Thank you, Sue. I went back and re-wrote my password generation script to
> remove references to the unallowed characters mentioned in Security Rules
> for passwords - []{}(),;?*! @..
> I'm still having the problem with the ENCRYPT function. I execute:
>
> sp_setapprole
> @.rolename = 'TEST',
> @.password = {Encrypt N
> 'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??
Dk2?q6L?EvrvI1mOENycWpLv
zjL?3kn'}
> --@.password = {Encrypt N 'easy'}
> ,@.encrypt = 'odbc'
> go
>
> And get:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
>
> I know I don't have a syntax error (other than an ugly password). When I
> switch the TEST app role over to a password of 'easy', it works.
>
> Am I supposed to put braces [] around the password somehow?
>
> So the question remains, what characters are not allowed for passwords? I
> know []{}(),;?*! @.. are not, but I don't have any of these.
>
> Chuck
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
>
>
Saturday, February 25, 2012
Application Name
Hi
Does anyone know how the ODBC driver gets the application name that is
displayed by the SQL Server profiler? Our application does not explicitly
pass it in with the connection string but we are seeing a name under the
ApplicationName column in SQL Server profiler.
Many thanks
Shakti
I'm just curious. Does the application name that is generated make sense? I
know that if I don't set one in ADO.Net, the app name is something like ".net
client." If I need the information to say something that I really need to
set the AppName
Russel Loski, MCSD.Net
"Johnny" wrote:
> Hi
> Does anyone know how the ODBC driver gets the application name that is
> displayed by the SQL Server profiler? Our application does not explicitly
> pass it in with the connection string but we are seeing a name under the
> ApplicationName column in SQL Server profiler.
> Many thanks
> Shakti
>
>
|||Hi,
there are several way, one would be to tweak the connection string with
the appName parameter.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Agree that your suggestion is the way to set the application name but if it
is not set by the application where does it get the name from? I can see
the program name in the sysprocesses table but if the application is not
setting it explicitly where does it come from?
Thanks
Shakti
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1169569609.022799.219340@.l53g2000cwa.googlegr oups.com...
> Hi,
> there are several way, one would be to tweak the connection string with
> the appName parameter.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Still from the application side. It's a parameter for the
connection string, property for the connection, different
ways depending on the application.Not all connections from
applications will supply them.
-Sue
On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
<prem14@.acm.org> wrote:
>Agree that your suggestion is the way to set the application name but if it
>is not set by the application where does it get the name from? I can see
>the program name in the sysprocesses table but if the application is not
>setting it explicitly where does it come from?
>Thanks
>Shakti
>"Jens" <Jens@.sqlserver2005.de> wrote in message
>news:1169569609.022799.219340@.l53g2000cwa.googleg roups.com...
>
|||We are going round in circles! Application is neither supplying the
application name parameter nor setting any connection property whatsoever.
So, where does ODBC driver / sql server / sysprocesses get the program name
from? It appears to have our company name!
Cheers
Shakti
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.4ax.com...
> Still from the application side. It's a parameter for the
> connection string, property for the connection, different
> ways depending on the application.Not all connections from
> applications will supply them.
> -Sue
> On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
> <prem14@.acm.org> wrote:
>
|||Client would have been the better word to use rather than
application. It depends on what application.you are using.
And driver.
-Sue
On Wed, 24 Jan 2007 11:30:36 -0000, "Johnny"
<prem14@.acm.org> wrote:
>We are going round in circles! Application is neither supplying the
>application name parameter nor setting any connection property whatsoever.
>So, where does ODBC driver / sql server / sysprocesses get the program name
>from? It appears to have our company name!
>Cheers
>Shakti
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.4ax.com.. .
>
Does anyone know how the ODBC driver gets the application name that is
displayed by the SQL Server profiler? Our application does not explicitly
pass it in with the connection string but we are seeing a name under the
ApplicationName column in SQL Server profiler.
Many thanks
Shakti
I'm just curious. Does the application name that is generated make sense? I
know that if I don't set one in ADO.Net, the app name is something like ".net
client." If I need the information to say something that I really need to
set the AppName
Russel Loski, MCSD.Net
"Johnny" wrote:
> Hi
> Does anyone know how the ODBC driver gets the application name that is
> displayed by the SQL Server profiler? Our application does not explicitly
> pass it in with the connection string but we are seeing a name under the
> ApplicationName column in SQL Server profiler.
> Many thanks
> Shakti
>
>
|||Hi,
there are several way, one would be to tweak the connection string with
the appName parameter.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Agree that your suggestion is the way to set the application name but if it
is not set by the application where does it get the name from? I can see
the program name in the sysprocesses table but if the application is not
setting it explicitly where does it come from?
Thanks
Shakti
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1169569609.022799.219340@.l53g2000cwa.googlegr oups.com...
> Hi,
> there are several way, one would be to tweak the connection string with
> the appName parameter.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Still from the application side. It's a parameter for the
connection string, property for the connection, different
ways depending on the application.Not all connections from
applications will supply them.
-Sue
On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
<prem14@.acm.org> wrote:
>Agree that your suggestion is the way to set the application name but if it
>is not set by the application where does it get the name from? I can see
>the program name in the sysprocesses table but if the application is not
>setting it explicitly where does it come from?
>Thanks
>Shakti
>"Jens" <Jens@.sqlserver2005.de> wrote in message
>news:1169569609.022799.219340@.l53g2000cwa.googleg roups.com...
>
|||We are going round in circles! Application is neither supplying the
application name parameter nor setting any connection property whatsoever.
So, where does ODBC driver / sql server / sysprocesses get the program name
from? It appears to have our company name!
Cheers
Shakti
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.4ax.com...
> Still from the application side. It's a parameter for the
> connection string, property for the connection, different
> ways depending on the application.Not all connections from
> applications will supply them.
> -Sue
> On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
> <prem14@.acm.org> wrote:
>
|||Client would have been the better word to use rather than
application. It depends on what application.you are using.
And driver.
-Sue
On Wed, 24 Jan 2007 11:30:36 -0000, "Johnny"
<prem14@.acm.org> wrote:
>We are going round in circles! Application is neither supplying the
>application name parameter nor setting any connection property whatsoever.
>So, where does ODBC driver / sql server / sysprocesses get the program name
>from? It appears to have our company name!
>Cheers
>Shakti
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.4ax.com.. .
>
Application Name
Hi
Does anyone know how the ODBC driver gets the application name that is
displayed by the SQL Server profiler? Our application does not explicitly
pass it in with the connection string but we are seeing a name under the
ApplicationName column in SQL Server profiler.
Many thanks
ShaktiI'm just curious. Does the application name that is generated make sense?
I
know that if I don't set one in ADO.Net, the app name is something like ".ne
t
client." If I need the information to say something that I really need to
set the AppName
--
Russel Loski, MCSD.Net
"Johnny" wrote:
> Hi
> Does anyone know how the ODBC driver gets the application name that is
> displayed by the SQL Server profiler? Our application does not explicitly
> pass it in with the connection string but we are seeing a name under the
> ApplicationName column in SQL Server profiler.
> Many thanks
> Shakti
>
>|||Hi,
there are several way, one would be to tweak the connection string with
the appName parameter.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Agree that your suggestion is the way to set the application name but if it
is not set by the application where does it get the name from? I can see
the program name in the sysprocesses table but if the application is not
setting it explicitly where does it come from?
Thanks
Shakti
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1169569609.022799.219340@.l53g2000cwa.googlegroups.com...
> Hi,
> there are several way, one would be to tweak the connection string with
> the appName parameter.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Still from the application side. It's a parameter for the
connection string, property for the connection, different
ways depending on the application.Not all connections from
applications will supply them.
-Sue
On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
<prem14@.acm.org> wrote:
>Agree that your suggestion is the way to set the application name but if it
>is not set by the application where does it get the name from? I can see
>the program name in the sysprocesses table but if the application is not
>setting it explicitly where does it come from?
>Thanks
>Shakti
>"Jens" <Jens@.sqlserver2005.de> wrote in message
>news:1169569609.022799.219340@.l53g2000cwa.googlegroups.com...
>|||We are going round in circles! Application is neither supplying the
application name parameter nor setting any connection property whatsoever.
So, where does ODBC driver / sql server / sysprocesses get the program name
from? It appears to have our company name!
Cheers
Shakti
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.
4ax.com...
> Still from the application side. It's a parameter for the
> connection string, property for the connection, different
> ways depending on the application.Not all connections from
> applications will supply them.
> -Sue
> On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
> <prem14@.acm.org> wrote:
>
>|||Client would have been the better word to use rather than
application. It depends on what application.you are using.
And driver.
-Sue
On Wed, 24 Jan 2007 11:30:36 -0000, "Johnny"
<prem14@.acm.org> wrote:
>We are going round in circles! Application is neither supplying the
>application name parameter nor setting any connection property whatsoever.
>So, where does ODBC driver / sql server / sysprocesses get the program name
>from? It appears to have our company name!
>Cheers
>Shakti
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.
4ax.com...
>
Does anyone know how the ODBC driver gets the application name that is
displayed by the SQL Server profiler? Our application does not explicitly
pass it in with the connection string but we are seeing a name under the
ApplicationName column in SQL Server profiler.
Many thanks
ShaktiI'm just curious. Does the application name that is generated make sense?
I
know that if I don't set one in ADO.Net, the app name is something like ".ne
t
client." If I need the information to say something that I really need to
set the AppName
--
Russel Loski, MCSD.Net
"Johnny" wrote:
> Hi
> Does anyone know how the ODBC driver gets the application name that is
> displayed by the SQL Server profiler? Our application does not explicitly
> pass it in with the connection string but we are seeing a name under the
> ApplicationName column in SQL Server profiler.
> Many thanks
> Shakti
>
>|||Hi,
there are several way, one would be to tweak the connection string with
the appName parameter.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Agree that your suggestion is the way to set the application name but if it
is not set by the application where does it get the name from? I can see
the program name in the sysprocesses table but if the application is not
setting it explicitly where does it come from?
Thanks
Shakti
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1169569609.022799.219340@.l53g2000cwa.googlegroups.com...
> Hi,
> there are several way, one would be to tweak the connection string with
> the appName parameter.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Still from the application side. It's a parameter for the
connection string, property for the connection, different
ways depending on the application.Not all connections from
applications will supply them.
-Sue
On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
<prem14@.acm.org> wrote:
>Agree that your suggestion is the way to set the application name but if it
>is not set by the application where does it get the name from? I can see
>the program name in the sysprocesses table but if the application is not
>setting it explicitly where does it come from?
>Thanks
>Shakti
>"Jens" <Jens@.sqlserver2005.de> wrote in message
>news:1169569609.022799.219340@.l53g2000cwa.googlegroups.com...
>|||We are going round in circles! Application is neither supplying the
application name parameter nor setting any connection property whatsoever.
So, where does ODBC driver / sql server / sysprocesses get the program name
from? It appears to have our company name!
Cheers
Shakti
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.
4ax.com...
> Still from the application side. It's a parameter for the
> connection string, property for the connection, different
> ways depending on the application.Not all connections from
> applications will supply them.
> -Sue
> On Tue, 23 Jan 2007 20:39:32 -0000, "johnny"
> <prem14@.acm.org> wrote:
>
>|||Client would have been the better word to use rather than
application. It depends on what application.you are using.
And driver.
-Sue
On Wed, 24 Jan 2007 11:30:36 -0000, "Johnny"
<prem14@.acm.org> wrote:
>We are going round in circles! Application is neither supplying the
>application name parameter nor setting any connection property whatsoever.
>So, where does ODBC driver / sql server / sysprocesses get the program name
>from? It appears to have our company name!
>Cheers
>Shakti
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m9hdr2h8qehp2c1q96jibubcmd44s900ht@.
4ax.com...
>
Sunday, February 19, 2012
Append Unicode string source to non-Unicode string destination
In SQL 2000 DTS, I was able to append data from an ODBC source to a SQL 2000 destination table. The destination table was created by copying an attached source table in Access to a new table, then upsizing it to SQL. The character fields come over as varchar, and that seemed to be fine with the DTS job.
Now using the same source table and the same SQL destination, only in SQL 2005 with Integration Services instead of DTS, I get an error because the connection manager interprets the source text fields as Unicode and the destination fields are varchar.
I could script the table and change the text fields in the destination table to nvarchar, but this could have adverse affect on the application that uses the destination table. Is there a way to make the connection manager see the source text fields as varchar, or have the integration package allow the append even though the destination is varchar and the source is nvarchar?
Use the Data Conversion component to change the values from DT_WSTR to DT_STR.
-Jamie
|||Thanks for the quick response. I was able to finish the SSIS package successfully.Thursday, February 16, 2012
Append new records from ODBC source
I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?
Steve Jensen wrote:
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
Steve,
Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior
|||Sounds good. Could you give me some detailed instructions for doing this?
Thanks,
Steve
|||Steve here is a detailed description:
http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx
I Hope this can help you
Rafael Salas
|||Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.
Thanks,
Steve
Append new records from ODBC source
I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?
Steve Jensen wrote:
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
Steve,
Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior
|||
Sounds good. Could you give me some detailed instructions for doing this?
Thanks,
Steve
|||Steve here is a detailed description:
http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx
I Hope this can help you
Rafael Salas
|||
Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.
Thanks,
Steve
Thursday, February 9, 2012
Anyone using OSISoft PI system with SSRS?
Have any of you interfaced with OSI Soft PI system? This is a proprietary database which is said to support OLEDB and ODBC. OSISoft PI is a common "historian" for time-series data storage in process control systems and real-time control systems.
Thanks
The PI Sytem does indeed support OLEDB and ODBC access. For more info, please contact OSIsoft Technical Support at techsupport@.osisoft.com for assistance. Reference the system number of the PI Server which you are inquiring on behalf of when you send in your question.
Thanks, Steve Nye, OSIsoft Technical Support
Anyone using OSISoft PI system with SSRS?
Have any of you interfaced with OSI Soft PI system? This is a proprietary database which is said to support OLEDB and ODBC. OSISoft PI is a common "historian" for time-series data storage in process control systems and real-time control systems.
Thanks
The PI Sytem does indeed support OLEDB and ODBC access. For more info, please contact OSIsoft Technical Support at techsupport@.osisoft.com for assistance. Reference the system number of the PI Server which you are inquiring on behalf of when you send in your question.
Thanks, Steve Nye, OSIsoft Technical Support
Subscribe to:
Posts (Atom)