Showing posts with label odbc. Show all posts

Sunday, March 25, 2012

Connection Pooling Limit thru ODBC

Hello, Does anyone know if it's possible to limit the actual number of possible connections made to a database using the connection pooling services provided by odbc (let's assume odbc and not ole, as quite honestly i intend this for connecting to MSDE)? my understanding is that the connection limit provided with the pool strictly manages the number of connections within the pool versus the total number of connections.

Ideally I would like connection requests to be queued and offered when available.. which I would further like to specify in the code using the connection. what I mean by this is that I would like to explicitly flag the connection as unused (thereby skipping the wait for idle time), when I'm done with it.

why do I want to do such things? well, and here's where my problem may lie, I want to use MSDE (with it's reccomended 5 connection limit) on a single machine. Although there will only be a single user, there exist a whole slew of *different* apps that need to jump in, read/write data and leave again.

please excuse my ignorance if I'm attempting to get around this the wrong way. i look forward to a discussion.

abLook at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s|||Excellent, gotta love how simple that is! do you happen to know what happens when the max connections are used? is the connection request denied or simply qeued until a possible timeout? also do you know what i should do with a connection, once finished with it in a DLL for instance, to best take advantage of connection pooling? thanks

ab

Originally posted by PedroHenrique
Look at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s

connection pooling for access jet connected ODBC tables?

Is 'connection pooling' available when using an access front end connecting
to sql server 2000 tables via jet connected ODBC (not an access
project/ado)?
If yes, is there any configurable options? if yes where? On client side or
server side or both?
any help is appreciated. Thanks.
You can configure ODBC connection properties in an Access mdb in
several places -- database options, query options, and in an ODBC
connection string (or DSN). However, I'm not sure about
resource/connection pooling. Access handles a lot of that under the
covers, spawning additonal connections when needed, say for opening a
complex form. What exactly are you trying to dos?
--Mary
On Mon, 24 May 2004 16:37:11 -0400, "djc" <noone@.nowhere.com> wrote:

>Is 'connection pooling' available when using an access front end connecting
>to sql server 2000 tables via jet connected ODBC (not an access
>project/ado)?
>If yes, is there any configurable options? if yes where? On client side or
>server side or both?
>any help is appreciated. Thanks.
>
sql sql

connection pooling for access jet connected ODBC tables?

Is 'connection pooling' available when using an access front end connecting
to sql server 2000 tables via jet connected ODBC (not an access
project/ado)?
If yes, is there any configurable options? if yes where? On client side or
server side or both?
any help is appreciated. Thanks.You can configure ODBC connection properties in an Access mdb in
several places -- database options, query options, and in an ODBC
connection string (or DSN). However, I'm not sure about
resource/connection pooling. Access handles a lot of that under the
covers, spawning additonal connections when needed, say for opening a
complex form. What exactly are you trying to dos?
--Mary
On Mon, 24 May 2004 16:37:11 -0400, "djc" <noone@.nowhere.com> wrote:

>Is 'connection pooling' available when using an access front end connecting
>to sql server 2000 tables via jet connected ODBC (not an access
>project/ado)?
>If yes, is there any configurable options? if yes where? On client side or
>server side or both?
>any help is appreciated. Thanks.
>

Thursday, March 22, 2012

connection pooling

is connection pooling something that can be used after moving tables from
mdb file to sql server 2000 and just relinking the tables via ODBC in the
front end file? Or is this something that must be setup in code?Connection pooling is on by default. It's typically used if you make and
break connections with the same connection string parameters. A web server
is a primary candidate for it's use.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql sql

connection pooling

is connection pooling something that can be used after moving tables from
mdb file to sql server 2000 and just relinking the tables via ODBC in the
front end file? Or is this something that must be setup in code?
Connection pooling is on by default. It's typically used if you make and
break connections with the same connection string parameters. A web server
is a primary candidate for it's use.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Tuesday, March 20, 2012

Connection no longer works

I was able to connect to a SQL Server 2000 instance using ODBC on a Windows
2000 workstation (Service Pack 4). But after installing several security
updates and rollups, I am no longer able to connect to SQL Server.
The drivers tab in the ODBC Administrator lists SQL Server as Not marked
under Version and Company. Another machine that was not as updated lists
Version as 3.70.06.90 and Company as Microsoft.
Have you run component checker to verify your MDAC
installation? You could also try reinstalling MDAC - sounds
like the issue is likely with once of the MDAC components.
You can download component checker and mdac versions from:
http://msdn.microsoft.com/data/mdac/...s/default.aspx
-Sue
On Mon, 3 Oct 2005 10:42:01 -0700, "Alton Chinn" <Alton
Chinn@.discussions.microsoft.com> wrote:

>I was able to connect to a SQL Server 2000 instance using ODBC on a Windows
>2000 workstation (Service Pack 4). But after installing several security
>updates and rollups, I am no longer able to connect to SQL Server.
>The drivers tab in the ODBC Administrator lists SQL Server as Not marked
>under Version and Company. Another machine that was not as updated lists
>Version as 3.70.06.90 and Company as Microsoft.
|||I used it to determine that the various windows updates and hotfixes may have
knocked my MDAC version down to 2.5 SP3. For the SQL Server connection to
work, the version has to be at least 2.6 RTM, I think.
I tried to bring the version up to 2.8 but the rollback would always fail.
I tried to rename ODBCCONF.DLL and run unmgrt, then re-run mdac_type, but
that did not seem to work.
Alton
"Sue Hoegemeier" wrote:

> Have you run component checker to verify your MDAC
> installation? You could also try reinstalling MDAC - sounds
> like the issue is likely with once of the MDAC components.
> You can download component checker and mdac versions from:
> http://msdn.microsoft.com/data/mdac/...s/default.aspx
> -Sue
> On Mon, 3 Oct 2005 10:42:01 -0700, "Alton Chinn" <Alton
> Chinn@.discussions.microsoft.com> wrote:
>
>
|||You're going to have to get that MDAC updated. Don't try a rollback. Just
install the latest MDAC over the top. If you get an error try rebooting. If
that doesn't work, post the error and we'll help you out from there.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Alton Chinn" wrote:
[vbcol=seagreen]
> I used it to determine that the various windows updates and hotfixes may have
> knocked my MDAC version down to 2.5 SP3. For the SQL Server connection to
> work, the version has to be at least 2.6 RTM, I think.
> I tried to bring the version up to 2.8 but the rollback would always fail.
> I tried to rename ODBCCONF.DLL and run unmgrt, then re-run mdac_type, but
> that did not seem to work.
> Alton
> "Sue Hoegemeier" wrote:
|||I tried running mdac_type.exe for version 2.8 sp1. After copying the new
files, it starts rolling back components automatically. Then I get a message
saying the setup failed to complete. After restarting, I ran the checker and
it lists the verison as 2.5 sp3.
Alton
"MeanOldDBA" wrote:
[vbcol=seagreen]
> You're going to have to get that MDAC updated. Don't try a rollback. Just
> install the latest MDAC over the top. If you get an error try rebooting. If
> that doesn't work, post the error and we'll help you out from there.
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Alton Chinn" wrote:
|||First try reading through the MDAC setup troubleshooting
guide:
http://support.microsoft.com/?id=232060
Typically you get more information in the error than just
setup failed to complete - that may provide more pointers to
what is going on.
I remember an issue with automatic rollbacks with other
versions of MDAC - see the info in the following:
http://support.microsoft.com/?id=286442
You may want to check the event logs to see if you have
something similar going on.
-Sue
On Tue, 4 Oct 2005 11:22:02 -0700, "Alton Chinn"
<AltonChinn@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I tried running mdac_type.exe for version 2.8 sp1. After copying the new
>files, it starts rolling back components automatically. Then I get a message
>saying the setup failed to complete. After restarting, I ran the checker and
>it lists the verison as 2.5 sp3.
>Alton
>
>"MeanOldDBA" wrote:
|||According to dasetup, installation fails at the ODBC configuration:
************************************************** *****
ODBCConf called with arguments: '/S /Lv C:\WINNT\dasetup.log /F
C:\WINNT\system32\sqlclnt.rsp'
Data Struct:
Reboot First : 0
Use Response File : 1
Response File : 'C:\WINNT\system32\sqlclnt.rsp'
Erase Response File: 0
Silent : 1
Continue on Error : 0
Log Mode : 2
Log File : 'C:\WINNT\dasetup.log'
Actions:
5, 'SQL Server|Driver=SQLSRV32.dll||', '(null)', '"SQL
Server|Driver=SQLSRV32.dll||"'
1, 'SQL Server', 'CPTimeout=60', '"SQL Server" "CPTimeout=60"'
2, 'SQL Server', 'DSN=MQIS|Server=(Local)|Description=SQL
Server|Database=MQIS||', '"SQL Server"
"DSN=MQIS|Server=(Local)|Description=SQL Server|Database=MQIS||"'
EXECUTING ACTIONS
Executing Action: INSTALLDRIVER
arg1: 'SQL Server|Driver=SQLSRV32.dll||'
arg2: '(null)'
args: '"SQL Server|Driver=SQLSRV32.dll||"'
INSTALLDRIVER SQLInstallDriverEx succeeded
Return HR: 0x0
Executing Action: CONFIGDRIVER
arg1: 'SQL Server'
arg2: 'CPTimeout=60'
args: '"SQL Server" "CPTimeout=60"'
CONFIGDRIVER SQLConfigDriver succeeded
Return HR: 0x0
Executing Action: CONFIGDSN
arg1: 'SQL Server'
arg2: 'DSN=MQIS|Server=(Local)|Description=SQL Server|Database=MQIS||'
args: '"SQL Server" "DSN=MQIS|Server=(Local)|Description=SQL
Server|Database=MQIS||"'
CONFIGDSN Unable to create a data source for the 'SQL Server' driver:
Could not load the setup or translator library
Return HR: 0x80004005
Error Executing Action: CONFIGDSN
Unable to create a data source for the 'SQL Server' driver:
Could not load the setup or translator library with error code -2147467259
Debug: Loaded string resource: 4. Characters = 23
Debug: Loaded string resource: 6. Characters = 16
Calling function: RestoreCatalog
RestoreCatalog: Success.
Entering function: ExecuteInstall()
Parameters:
nDirective = 5
hWnd = 0x00090042
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLODB C.INF
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 64
pContext = 0x0012B450
************************************************** *****
After that, the program starts to execute UNREGW2KMIGRATION.
|||Don't know what else to tell you. If you get an error, it
should be listed in the bottom of the log you posted -
that's the relevant part - where it goes into the
Entering function: ExecuteInstall()
-Sue
On Wed, 5 Oct 2005 10:26:14 -0700, "Alton Chinn"
<AltonChinn@.discussions.microsoft.com> wrote:

>According to dasetup, installation fails at the ODBC configuration:
>************************************************* ******
>ODBCConf called with arguments: '/S /Lv C:\WINNT\dasetup.log /F
>C:\WINNT\system32\sqlclnt.rsp'
>Data Struct:
>Reboot First : 0
>Use Response File : 1
>Response File : 'C:\WINNT\system32\sqlclnt.rsp'
>Erase Response File: 0
>Silent : 1
>Continue on Error : 0
>Log Mode : 2
>Log File : 'C:\WINNT\dasetup.log'
>Actions:
>5, 'SQL Server|Driver=SQLSRV32.dll||', '(null)', '"SQL
>Server|Driver=SQLSRV32.dll||"'
>1, 'SQL Server', 'CPTimeout=60', '"SQL Server" "CPTimeout=60"'
>2, 'SQL Server', 'DSN=MQIS|Server=(Local)|Description=SQL
>Server|Database=MQIS||', '"SQL Server"
>"DSN=MQIS|Server=(Local)|Description=SQL Server|Database=MQIS||"'
>EXECUTING ACTIONS
>Executing Action: INSTALLDRIVER
> arg1: 'SQL Server|Driver=SQLSRV32.dll||'
> arg2: '(null)'
> args: '"SQL Server|Driver=SQLSRV32.dll||"'
>INSTALLDRIVER SQLInstallDriverEx succeeded
> Return HR: 0x0
>Executing Action: CONFIGDRIVER
> arg1: 'SQL Server'
> arg2: 'CPTimeout=60'
> args: '"SQL Server" "CPTimeout=60"'
>CONFIGDRIVER SQLConfigDriver succeeded
> Return HR: 0x0
>Executing Action: CONFIGDSN
> arg1: 'SQL Server'
> arg2: 'DSN=MQIS|Server=(Local)|Description=SQL Server|Database=MQIS||'
> args: '"SQL Server" "DSN=MQIS|Server=(Local)|Description=SQL
>Server|Database=MQIS||"'
>CONFIGDSN Unable to create a data source for the 'SQL Server' driver:
>Could not load the setup or translator library
> Return HR: 0x80004005
>Error Executing Action: CONFIGDSN
>Unable to create a data source for the 'SQL Server' driver:
>Could not load the setup or translator library with error code -2147467259
>Debug: Loaded string resource: 4. Characters = 23
>Debug: Loaded string resource: 6. Characters = 16
>Calling function: RestoreCatalog
>RestoreCatalog: Success.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 5
>hWnd = 0x00090042
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLODB C.INF
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 64
>pContext = 0x0012B450
>************************************************* ******
>After that, the program starts to execute UNREGW2KMIGRATION.
>
|||HERE ARE THE VARIOUS CALLS TO ExecuteFunction():
Entering function: CJob::InitializeJobQueues()
Parameters:
hHive = 0x80000002
szRoot = Software\Microsoft\dasetup\jobs
Exiting function: CJob::InitializeJobQueues()
Return value: (HRESULT) 0x00000000
Debug: Loaded string resource: 3. Characters = 21
Debug: Loaded string resource: 5. Characters = 14
Calling function: BackupCatalog
BackupCatalog Result : Backup Catalog directory already exists for this
component.
Entering function: ExecuteInstall()
Parameters:
nDirective = 4
hWnd = 0x0004014C
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MDACXP AK.INF
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 32
pContext = 0x0012B450
INSTALLW2KMIGRATIONPACK Getting OS Version Info
INSTALLW2KMIGRATIONPACK Testing
C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MDACXP AK.INF for existance
INSTALLW2KMIGRATIONPACK Get the ComponentID
INSTALLW2KMIGRATIONPACK Setting SysSetup Function Pointers
INSTALLW2KMIGRATIONPACK HINSTANCE: 1727987712
INSTALLW2KMIGRATIONPACK SetupRegisterOsComponent ProcAddress: 1728218877
INSTALLW2KMIGRATIONPACK SetupUnRegisterOsComponent ProcAddress: 1728219747
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent ProcAddress:
1728220575
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
************
Executing Action: REGTYPELIB
arg1: 'simpdata.tlb'
arg2: '(null)'
args: '"simpdata.tlb"'
Return HR: 0x0
Debug: Loaded string resource: 3. Characters = 21
Debug: Loaded string resource: 5. Characters = 14
Calling function: BackupCatalog
BackupCatalog Result : Backup Catalog directory already exists for this
component.
Entering function: ExecuteInstall()
Parameters:
nDirective = 4
hWnd = 0x0004014C
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MSXMLX .inf
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 32
pContext = 0x0012B450
INSTALLW2KMIGRATIONPACK Getting OS Version Info
INSTALLW2KMIGRATIONPACK Testing
C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MSXMLX .inf for existance
INSTALLW2KMIGRATIONPACK Get the ComponentID
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
*************
Calling function: BackupCatalog
BackupCatalog Result : Backup Catalog directory already exists for this
component.
Entering function: ExecuteInstall()
Parameters:
nDirective = 4
hWnd = 0x0004014C
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLXML XP.inf
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 32
pContext = 0x0012B450
INSTALLW2KMIGRATIONPACK Getting OS Version Info
INSTALLW2KMIGRATIONPACK Testing
C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLXML XP.inf for existance
INSTALLW2KMIGRATIONPACK Get the ComponentID
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
*************
Calling function: BackupCatalog
BackupCatalog Result : Backup Catalog directory already exists for this
component.
Entering function: ExecuteInstall()
Parameters:
nDirective = 4
hWnd = 0x0004014C
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLNET .INF
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 32
pContext = 0x0012B450
INSTALLW2KMIGRATIONPACK Getting OS Version Info
INSTALLW2KMIGRATIONPACK Testing
C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLNET .INF for existance
INSTALLW2KMIGRATIONPACK Get the ComponentID
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
**************
Calling function: BackupCatalog
BackupCatalog Result : Backup Catalog directory already exists for this
component.
Entering function: ExecuteInstall()
Parameters:
nDirective = 4
hWnd = 0x0004014C
pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLODB C.INF
pwcsCabFile = (null)
pwcsSection = DefaultInstall
pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
dwSetupFlags = 32
pContext = 0x0012B450
INSTALLW2KMIGRATIONPACK Getting OS Version Info
INSTALLW2KMIGRATIONPACK Testing
C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLODB C.INF for existance
INSTALLW2KMIGRATIONPACK Get the ComponentID
INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
***************
|||Don't know...looks like you have other issues related to the
OS.
-Sue
On Tue, 11 Oct 2005 11:52:03 -0700, "Alton Chinn"
<AltonChinn@.discussions.microsoft.com> wrote:

>HERE ARE THE VARIOUS CALLS TO ExecuteFunction():
>Entering function: CJob::InitializeJobQueues()
>Parameters:
>hHive = 0x80000002
>szRoot = Software\Microsoft\dasetup\jobs
>Exiting function: CJob::InitializeJobQueues()
>Return value: (HRESULT) 0x00000000
>Debug: Loaded string resource: 3. Characters = 21
>Debug: Loaded string resource: 5. Characters = 14
>Calling function: BackupCatalog
>BackupCatalog Result : Backup Catalog directory already exists for this
>component.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 4
>hWnd = 0x0004014C
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MDACXP AK.INF
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 32
>pContext = 0x0012B450
>INSTALLW2KMIGRATIONPACK Getting OS Version Info
>INSTALLW2KMIGRATIONPACK Testing
>C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MDACX PAK.INF for existance
>INSTALLW2KMIGRATIONPACK Get the ComponentID
>INSTALLW2KMIGRATIONPACK Setting SysSetup Function Pointers
>INSTALLW2KMIGRATIONPACK HINSTANCE: 1727987712
>INSTALLW2KMIGRATIONPACK SetupRegisterOsComponent ProcAddress: 1728218877
>INSTALLW2KMIGRATIONPACK SetupUnRegisterOsComponent ProcAddress: 1728219747
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent ProcAddress:
>1728220575
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
>************
>Executing Action: REGTYPELIB
> arg1: 'simpdata.tlb'
> arg2: '(null)'
> args: '"simpdata.tlb"'
> Return HR: 0x0
>Debug: Loaded string resource: 3. Characters = 21
>Debug: Loaded string resource: 5. Characters = 14
>Calling function: BackupCatalog
>BackupCatalog Result : Backup Catalog directory already exists for this
>component.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 4
>hWnd = 0x0004014C
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MSXMLX .inf
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 32
>pContext = 0x0012B450
>INSTALLW2KMIGRATIONPACK Getting OS Version Info
>INSTALLW2KMIGRATIONPACK Testing
>C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\MSXML X.inf for existance
>INSTALLW2KMIGRATIONPACK Get the ComponentID
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
>*************
>Calling function: BackupCatalog
>BackupCatalog Result : Backup Catalog directory already exists for this
>component.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 4
>hWnd = 0x0004014C
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLXML XP.inf
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 32
>pContext = 0x0012B450
>INSTALLW2KMIGRATIONPACK Getting OS Version Info
>INSTALLW2KMIGRATIONPACK Testing
>C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLXM LXP.inf for existance
>INSTALLW2KMIGRATIONPACK Get the ComponentID
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
>*************
>Calling function: BackupCatalog
>BackupCatalog Result : Backup Catalog directory already exists for this
>component.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 4
>hWnd = 0x0004014C
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLNET .INF
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 32
>pContext = 0x0012B450
>INSTALLW2KMIGRATIONPACK Getting OS Version Info
>INSTALLW2KMIGRATIONPACK Testing
>C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLNE T.INF for existance
>INSTALLW2KMIGRATIONPACK Get the ComponentID
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
>**************
>Calling function: BackupCatalog
>BackupCatalog Result : Backup Catalog directory already exists for this
>component.
>Entering function: ExecuteInstall()
>Parameters:
>nDirective = 4
>hWnd = 0x0004014C
>pwcsInfFile = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLODB C.INF
>pwcsCabFile = (null)
>pwcsSection = DefaultInstall
>pwcsExtractPath = C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP
>dwSetupFlags = 32
>pContext = 0x0012B450
>INSTALLW2KMIGRATIONPACK Getting OS Version Info
>INSTALLW2KMIGRATIONPACK Testing
>C:\DOCUME~1\achinn\LOCALS~1\Temp\IXP000.TMP\SQLOD BC.INF for existance
>INSTALLW2KMIGRATIONPACK Get the ComponentID
>INSTALLW2KMIGRATIONPACK SetupQueryRegisteredOsComponent Failed with Error : 2
>***************
>
sql sql

Monday, March 19, 2012

Connection management

hello, I'm using Visual Foxpro and ODBC / sql server 2000.
How should i manage conections ? opening and closing it on each form ? or
opening at the begining of application and closing it at application finish
?
Thanks
Open then close not on each form, but lower.
At each transaction, on add open connection then close it.
On Select open connection then close it.
Joe
"Pedro Jose Caceres" wrote:

> hello, I'm using Visual Foxpro and ODBC / sql server 2000.
> How should i manage conections ? opening and closing it on each form ? or
> opening at the begining of application and closing it at application finish
> ?
> Thanks
>
>

Connection management

hello, I'm using Visual Foxpro and ODBC / sql server 2000.
How should i manage conections ? opening and closing it on each form ? or
opening at the begining of application and closing it at application finish
?
ThanksOpen then close not on each form, but lower.
At each transaction, on add open connection then close it.
On Select open connection then close it.
Joe
"Pedro Jose Caceres" wrote:

> hello, I'm using Visual Foxpro and ODBC / sql server 2000.
> How should i manage conections ? opening and closing it on each form ? or
> opening at the begining of application and closing it at application fini
sh
> ?
> Thanks
>
>

Sunday, March 11, 2012

Connection is busy with results for another hstmt

HI guys,

I'm using ODBC driver from WinXP SP2 with SQL Server 2005 Express (on my machine for test) and SQL Server 2005 Standard (on the production machine).

With SQL Server 2005 Express I've no problem, but with SQL Server 2005 Standard, in some query, I get "Connection is busy with results for another hstmt".

I'm working with VisualStudio 2003 with standard CDatabase and CRecordSet MFC classes.

I looked for an advice in the Microsoft KB but I found only Service Pack for SQL Server 2000 !

Everything could be useful !

Thanks a lot!

Bye, Domenico

You can avoid this error message by switching to the SQL Server 2005 ODBC driver, 'SQL Native Client' and enabling the MARS (Multiple Active Result Sets feature). There is more information in Books Online and also here http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Connection is busy with results for another command

We are migrating our databases from MS Access to SQL Server 2005 and using the SQL Native Client ODBC driver.

We also use a reporting tool called Actuate. Some of our Actuate reports use multiple datastreams to build the report.

When I run an Actuate report having multiple datastreams I get the following error message from SQL Server:

Error generated by the connected data source. Connection is busy with results for another command.

Is there a configuration setting in SQL Server to enable processing of multiple result sets?

Thanks.

sqlserver can handle thousands of connections. The error reported sounds like your application error/limitation. I suggest you take a look at the application configuration (i.e. connection or command method).

|||Thanks, oj.

I think what's happening is that Actuate is attempting to execute multiple queries simultaneously against the same db connection. The error I'm getting looks to be an ODBC error generated from SQL Server.|||

It's typically the data access provider (odbc, .net, etc.) that the application uses that throws the error. For example, you cannot execute another sqlcommand on the same connection that is being used/processed by a sqldatareader.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

|||

SQL Server introduced MARS which allows multiactive resultssets. have a look in the BOL and see have you can enabled it within your conenctionstring at www.connectionstrings.com

Jens K Suessmeyer

http://www.sqlserver2005.de

Connection is busy with results for another command

We are migrating our databases from MS Access to SQL Server 2005 and using the SQL Native Client ODBC driver.

We also use a reporting tool called Actuate. Some of our Actuate reports use multiple datastreams to build the report.

When I run an Actuate report having multiple datastreams I get the following error message from SQL Server:

Error generated by the connected data source. Connection is busy with results for another command.

Is there a configuration setting in SQL Server to enable processing of multiple result sets?

Thanks.

|||Thanks, oj.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

|||

SQL Server introduced MARS which allows multiactive resultssets. have a look in the BOL and see have you can enabled it within your conenctionstring at www.connectionstrings.com

Jens K Suessmeyer

http://www.sqlserver2005.de

Connection is busy with results for another command

We are migrating our databases from MS Access to SQL Server 2005 and using the SQL Native Client ODBC driver.

We also use a reporting tool called Actuate. Some of our Actuate reports use multiple datastreams to build the report.

When I run an Actuate report having multiple datastreams I get the following error message from SQL Server:

Error generated by the connected data source. Connection is busy with results for another command.

Is there a configuration setting in SQL Server to enable processing of multiple result sets?

Thanks.

|||Thanks, oj.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

|||

SQL Server introduced MARS which allows multiactive resultssets. have a look in the BOL and see have you can enabled it within your conenctionstring at www.connectionstrings.com

Jens K Suessmeyer

http://www.sqlserver2005.de

Connection is busy with results for another command

We are migrating our databases from MS Access to SQL Server 2005 and using the SQL Native Client ODBC driver.

We also use a reporting tool called Actuate. Some of our Actuate reports use multiple datastreams to build the report.

When I run an Actuate report having multiple datastreams I get the following error message from SQL Server:

Error generated by the connected data source. Connection is busy with results for another command.

Is there a configuration setting in SQL Server to enable processing of multiple result sets?

Thanks.

|||Thanks, oj.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

|||

SQL Server introduced MARS which allows multiactive resultssets. have a look in the BOL and see have you can enabled it within your conenctionstring at www.connectionstrings.com

Jens K Suessmeyer

http://www.sqlserver2005.de

Connection from XP SP2 to Windows 2003 R2 with SQL Server 2005 failed

I cannot make named pipe connection with client by ODBC DSA.

Here is information. Refering to http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1.

[1] Client side:

1. What is the connection string in you app or DSN? (please specify) ODBC \\[SERVER_NAME]\pipe\sql\guery

2. If client fails to connect, what is the client error messages? (please specify) Connection failed: SQLState: ’01000’ SQL ServerError: 1326 [Microsoft][ODBC SQL Server Driver][Named Pipes]connectionOpen (Connect()) Connection failed: SQLState: ’08001’ SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.

3. Is the client remote or local to the SQL server machine? [Remote | Local]

4. Can you ping your server? [YES | NO ]

In cmd.exe console, type “ping -a <server_name>”.

5. Can you telnet to your SQL Server? [YES | NO, please specify the error message ]

In cmd.exe console, type “telnet <server name> port, where port can be 135, 445 or sql_server_tcp_port. If your cmd.exe console turns into a complete black screen with a cursor flushing on top left corner, you are connected. Type ctrl+’[‘ to bring up telnet prompt and type “quit” <enter>.

7. Is your client computer in the same domain as the Server computer? (Same domain | Different domains | WorkGroup)

8. What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes].

9. Do you have aliases configured that match the server name portion of your connection string? If so, please check if it is correct. You can use cliconfg.exe (SS 2000) or SQL Server Configuration Manager (SS 2005) to configure the alias. [NO]

10. Do you select force encryption on server and/or client? [NO]

I was able to connect with TCP/IP settings.

[2] Server side:

1. What is the MS SQL version? [SQL Server 2005]

2. What is the SKU of MS SQL? [Enterprise | Standard | Workgroup | Express (or MSDE) | other (please specify)].

3. What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. Use SQL Server Configuration Manager to configure it and check ERRORLOG or event log to confirm.

4. Does the server start successfully? [YES | NO] If not what is the error messages in the SQL server ERRORLOG?

5. If SQL Server is a named instance, is the SQL browser enabled? [YES | NO]

6. What is the account that the SQL Server is running under?[Local System | Network Service | Domain Account]

7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES | NO | not applicable | not enabled]

8. Do you make firewall exception for SQL Browser UDP port 1434? In SQL2000, you still need to make firewall exception for UDP port 1434 in order to support named instance.[YES | NO | not applicable | not enabled]

I could connect locally with named pipes.

Do I have to make some file shares manually?

[2a] Tool Used to Connect

What tool or Application are you using to connect to SQL Server (eg: Visual Studio, SQL Server Management Studio, SQLCmd.exe, OSQL, etc) and especially the version of SQL Server (Express, Workgroup, Standard, Enterprise, Developer)

[3] Platform:

1. What is the OS version? [Windows XPSP2 | Windows 2003 | Windows 2000 | Windows 98 | others (please specify ) ].

1. Server [Windows Server 2003 R2]

2. Client [Windows XP SP2]

2. Do you have third party antivirus, anti-spareware software installed? [Symantec | Norton | other (please specify) ? NO].

[4] Misc:

1. If you have certificate configuration issue: Please use “certutil.exe –v –store my” to dump certificate specific info and post it in your question. [NO]

What happens if you execute
net use \\[server_name]\ipc$
from the client?

Connection from MS Access MDB

This is a cross-post (I originally posted in the ODBC forum).
I have an Access MDB with linked tables to a database that is running in 200
5
SQLExpress.
We created a new instance of SQLExpress and loaded the database from a
backup. We did a full restore of the d/b onto the new instance.
The new SQLExpress instance is using dynamic ports versus the static
(standard) port of 1433 that the old instance of SQLExpress was set to.
The MDB is used for some reporting. The linking of tables and production
of the reports works if run by a user with domain level administrator
permissions.
In the old instance of SQLExpress, a regular domain user would fail and the
pop-up window would appear allowing a change of credentials to produce the
report. In the new instance, this does not appear; only messages of an
exception occurring or if I run the report from the MDB Report section,
versus the menu that was built, I get an ODBC error, generic 3146 message
about being on a network.
I have used sql tracing from the ODBC Data Sources and I see (for both
regular and admin users) the attempted sql connections with the "admin"
account from the MDB and the domain user id; both fail; both are recorded in
the server Event Log.
In the case of a domain admin user running the reports, the log continues
and shows successful access to the database. In looking at the server Event
Log, the success connection is shown as a 'trusted' connection.
Is there a setting within SQLExpress for the server or database to cause the
pop up window to appear and allow the changing of credentials? I have tried
to create an account within the MDB to match an account within the database
and the server but I can not get the MDB to use that account; it always
defaults to the 'admin' account.
Thank you.Which ODBC forum? There are many of them.
It's not clear from your description if you want the credential window to
appear each time or not or if you want to use a domain (or windows or
"trusted") or a standard (or sql-server) user account.
If you are using a standard sql-server account, make sure that the
SQL-Server is setup for mixed authentification (Windows + SQL-Server
account) because only windows accounts are allowed by default. For the
standard accounts themselves, if you are trying to use accounts that were
created before the restoration, make sure that they are correctly mapped to
their SID by using the sp_change_users_login procedure (or better yet:
delete and recreate them). See
http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
If you want to use integrated security, make sure that the accounts that you
want to use are mapped as logins on the SQL-Server: the fact that an account
can log on a windows server doesn't mean that it can log on the sql-server
itself.
If you still have problem, then delete all links and recreate them using
either a standard sql-server account (account + password) or a windows
account (ie., integrated security or "trusted" account). If you want the
mdb file to retain the password, then check the option "Save Password" when
(re-)creating the links.
If these links are created programmatically (using vba code), then don't
forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
keep the password. Of course, you don't have to use this attribute for
windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
> This is a cross-post (I originally posted in the ODBC forum).
> I have an Access MDB with linked tables to a database that is running in
> 2005
> SQLExpress.
> We created a new instance of SQLExpress and loaded the database from a
> backup. We did a full restore of the d/b onto the new instance.
> The new SQLExpress instance is using dynamic ports versus the static
> (standard) port of 1433 that the old instance of SQLExpress was set to.
> The MDB is used for some reporting. The linking of tables and production
> of the reports works if run by a user with domain level administrator
> permissions.
> In the old instance of SQLExpress, a regular domain user would fail and
> the
> pop-up window would appear allowing a change of credentials to produce the
> report. In the new instance, this does not appear; only messages of an
> exception occurring or if I run the report from the MDB Report section,
> versus the menu that was built, I get an ODBC error, generic 3146 message
> about being on a network.
> I have used sql tracing from the ODBC Data Sources and I see (for both
> regular and admin users) the attempted sql connections with the "admin"
> account from the MDB and the domain user id; both fail; both are recorded
> in
> the server Event Log.
> In the case of a domain admin user running the reports, the log continues
> and shows successful access to the database. In looking at the server
> Event
> Log, the success connection is shown as a 'trusted' connection.
> Is there a setting within SQLExpress for the server or database to cause
> the
> pop up window to appear and allow the changing of credentials? I have
> tried
> to create an account within the MDB to match an account within the
> database
> and the server but I can not get the MDB to use that account; it always
> defaults to the 'admin' account.
> Thank you.
>|||Sylvain:
Thank you for this information; it is putting me in the correct direction.
Some comments and more questions:
1. I posted in the SQL Server Open Database Connectivity (ODBC) forum first
but saw similar answer of yours here; that is why I reposted.
2. I am set up for mixed authentication and I would prefer to have the
credential window appear each time so that the generic, read-only, account i
s
used to generate the report. If I could set the MDB to use the generic
account without the credential window, that would be good but that might
prevent administrative debugging or changing of the MDB without a relink of
tables.
3. I believe that I understand the sp_change_users_login procedure but I
have not done that yet. I did a REPORT and it shows 2 accounts are not
linked but there is another account that "must be" linked as it did not
appear in the report. I may still do the change on the account that appears
to be linked to ensure that it is linked. The accounts, the one not
reported, were created in the d/b and server AFTER the restore; so the chang
e
may be needed.
4. I am not sure how to get the MDB to retain the password as you
mentioned. I created a DSN for the linking of the MDB to the database. I
used the SA account for the linking process because any other account failed
.
Did I miss something here?
5. Unfortunately, we do not have in-house VBA expertise to use the
DSNlesslink that you mentioned; that makes me a little reluctant to go that
route.
F W Green
"Sylvain Lafontaine" wrote:

> Which ODBC forum? There are many of them.
> It's not clear from your description if you want the credential window to
> appear each time or not or if you want to use a domain (or windows or
> "trusted") or a standard (or sql-server) user account.
> If you are using a standard sql-server account, make sure that the
> SQL-Server is setup for mixed authentification (Windows + SQL-Server
> account) because only windows accounts are allowed by default. For the
> standard accounts themselves, if you are trying to use accounts that were
> created before the restoration, make sure that they are correctly mapped t
o
> their SID by using the sp_change_users_login procedure (or better yet:
> delete and recreate them). See
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
> If you want to use integrated security, make sure that the accounts that y
ou
> want to use are mapped as logins on the SQL-Server: the fact that an accou
nt
> can log on a windows server doesn't mean that it can log on the sql-server
> itself.
> If you still have problem, then delete all links and recreate them using
> either a standard sql-server account (account + password) or a windows
> account (ie., integrated security or "trusted" account). If you want the
> mdb file to retain the password, then check the option "Save Password" whe
n
> (re-)creating the links.
> If these links are created programmatically (using vba code), then don't
> forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
> keep the password. Of course, you don't have to use this attribute for
> windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html
.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
>
>|||First, sorry for the late response.
Second, I haven't used MDB with ODBC linked tables since many years, so I
forgot nearly everything about all these little naughty details; so you will
have to make your own little tests in order to see what works and what
don't.
The sp_change_users_login procedure is only for accounts that were created
before the restoration and only in the case when the master database has not
been restored or is from another installation/instance. Not usefull for new
accounts. Also, you see that the use of roles is a better idea than to
directly assign permission to an user account because with roles, it's
pretty easy and straightforward to recreate the old user accounts and
(re-)associate them with their respective roles. (BTW, I don't remember if
you have to use the sp_change_users_login procedure for these cases.)
For the point 4., some things can change when you are using a DSN instead of
a DSN-less connection but again, it's something that I've forgotten a long
time ago and you will have to make your own tests. I'm surprised however
that only the sa account is working for you. I suppose that you may have
forgot to associate these other accounts to their databases.
In all cases and excerpt maybe for the saving of the credentials - which I
don't remember the details -, both methods (DSN or DSN-less) should work as
well as each other.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...[vbcol=seagreen]
> Sylvain:
> Thank you for this information; it is putting me in the correct direction.
> Some comments and more questions:
> 1. I posted in the SQL Server Open Database Connectivity (ODBC) forum
> first
> but saw similar answer of yours here; that is why I reposted.
> 2. I am set up for mixed authentication and I would prefer to have the
> credential window appear each time so that the generic, read-only, account
> is
> used to generate the report. If I could set the MDB to use the generic
> account without the credential window, that would be good but that might
> prevent administrative debugging or changing of the MDB without a relink
> of
> tables.
> 3. I believe that I understand the sp_change_users_login procedure but I
> have not done that yet. I did a REPORT and it shows 2 accounts are not
> linked but there is another account that "must be" linked as it did not
> appear in the report. I may still do the change on the account that
> appears
> to be linked to ensure that it is linked. The accounts, the one not
> reported, were created in the d/b and server AFTER the restore; so the
> change
> may be needed.
> 4. I am not sure how to get the MDB to retain the password as you
> mentioned. I created a DSN for the linking of the MDB to the database. I
> used the SA account for the linking process because any other account
> failed.
> Did I miss something here?
> 5. Unfortunately, we do not have in-house VBA expertise to use the
> DSNlesslink that you mentioned; that makes me a little reluctant to go
> that
> route.
> F W Green
> "Sylvain Lafontaine" wrote:
>|||Sylvain:
Thank you very much for your assistance. I believe that I have solved the
issue mostly through trying many things; not because I really know much abou
t
SQL Server.
The SQL server and instance are new; therefore, there is a new master db. I
used SQL Mgmt Studio to restore the d/b from a backup from the old server.
Therefore, the issues of linking between the server and the d/b were real.
No matter what I did, I could not resolve the State 5 error which was an
invalid user. The server logs showed the MDB ADMIN attempted connection and
then my user account without domain preface attempted connection. Both
failed many times when the MDB report was being generated. If a non-Admin
user was running the report, it failed. If a domain Admin user (me) ran the
report, it worked.
In all of my searching, this link,
http://www.webservertalk.com/archiv...0-1710650.html, mentioned 2
things - a) SQLCMD and b) Builtin/Users.
I used the sqlcmd command on the Windows server where the new SQLExpress
instance was running and the logins were okay. Does not prove much but at
least the accounts and passwords to the server were correct.
I checked and in the new instance of SQLExpress, Builtin/Users did NOT have
'dbreader' access to my database. Builtin/Users did not exist in the old
instance but it had been upgraded to SQLExpress from MDSE. In the new
instance, I granted 'dbreader' access to Builtin/Users and now every person
in the firm can run the reports without issue and without the pop-up
authentication window. I am not too concerned about security as the MDB is
strictly a reporting tool and any tweaks will be done by my staff.
Thanks again.
F W Green
"Sylvain Lafontaine" wrote:

> First, sorry for the late response.
> Second, I haven't used MDB with ODBC linked tables since many years, so I
> forgot nearly everything about all these little naughty details; so you wi
ll
> have to make your own little tests in order to see what works and what
> don't.
> The sp_change_users_login procedure is only for accounts that were created
> before the restoration and only in the case when the master database has n
ot
> been restored or is from another installation/instance. Not usefull for n
ew
> accounts. Also, you see that the use of roles is a better idea than to
> directly assign permission to an user account because with roles, it's
> pretty easy and straightforward to recreate the old user accounts and
> (re-)associate them with their respective roles. (BTW, I don't remember i
f
> you have to use the sp_change_users_login procedure for these cases.)
> For the point 4., some things can change when you are using a DSN instead
of
> a DSN-less connection but again, it's something that I've forgotten a long
> time ago and you will have to make your own tests. I'm surprised however
> that only the sa account is working for you. I suppose that you may have
> forgot to associate these other accounts to their databases.
> In all cases and excerpt maybe for the saving of the credentials - which I
> don't remember the details -, both methods (DSN or DSN-less) should work a
s
> well as each other.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...
>
>

Connection from MS Access MDB

This is a cross-post (I originally posted in the ODBC forum).
I have an Access MDB with linked tables to a database that is running in 2005
SQLExpress.
We created a new instance of SQLExpress and loaded the database from a
backup. We did a full restore of the d/b onto the new instance.
The new SQLExpress instance is using dynamic ports versus the static
(standard) port of 1433 that the old instance of SQLExpress was set to.
The MDB is used for some reporting. The linking of tables and production
of the reports works if run by a user with domain level administrator
permissions.
In the old instance of SQLExpress, a regular domain user would fail and the
pop-up window would appear allowing a change of credentials to produce the
report. In the new instance, this does not appear; only messages of an
exception occurring or if I run the report from the MDB Report section,
versus the menu that was built, I get an ODBC error, generic 3146 message
about being on a network.
I have used sql tracing from the ODBC Data Sources and I see (for both
regular and admin users) the attempted sql connections with the "admin"
account from the MDB and the domain user id; both fail; both are recorded in
the server Event Log.
In the case of a domain admin user running the reports, the log continues
and shows successful access to the database. In looking at the server Event
Log, the success connection is shown as a 'trusted' connection.
Is there a setting within SQLExpress for the server or database to cause the
pop up window to appear and allow the changing of credentials? I have tried
to create an account within the MDB to match an account within the database
and the server but I can not get the MDB to use that account; it always
defaults to the 'admin' account.
Thank you.
Which ODBC forum? There are many of them.
It's not clear from your description if you want the credential window to
appear each time or not or if you want to use a domain (or windows or
"trusted") or a standard (or sql-server) user account.
If you are using a standard sql-server account, make sure that the
SQL-Server is setup for mixed authentification (Windows + SQL-Server
account) because only windows accounts are allowed by default. For the
standard accounts themselves, if you are trying to use accounts that were
created before the restoration, make sure that they are correctly mapped to
their SID by using the sp_change_users_login procedure (or better yet:
delete and recreate them). See
http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
If you want to use integrated security, make sure that the accounts that you
want to use are mapped as logins on the SQL-Server: the fact that an account
can log on a windows server doesn't mean that it can log on the sql-server
itself.
If you still have problem, then delete all links and recreate them using
either a standard sql-server account (account + password) or a windows
account (ie., integrated security or "trusted" account). If you want the
mdb file to retain the password, then check the option "Save Password" when
(re-)creating the links.
If these links are created programmatically (using vba code), then don't
forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
keep the password. Of course, you don't have to use this attribute for
windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
> This is a cross-post (I originally posted in the ODBC forum).
> I have an Access MDB with linked tables to a database that is running in
> 2005
> SQLExpress.
> We created a new instance of SQLExpress and loaded the database from a
> backup. We did a full restore of the d/b onto the new instance.
> The new SQLExpress instance is using dynamic ports versus the static
> (standard) port of 1433 that the old instance of SQLExpress was set to.
> The MDB is used for some reporting. The linking of tables and production
> of the reports works if run by a user with domain level administrator
> permissions.
> In the old instance of SQLExpress, a regular domain user would fail and
> the
> pop-up window would appear allowing a change of credentials to produce the
> report. In the new instance, this does not appear; only messages of an
> exception occurring or if I run the report from the MDB Report section,
> versus the menu that was built, I get an ODBC error, generic 3146 message
> about being on a network.
> I have used sql tracing from the ODBC Data Sources and I see (for both
> regular and admin users) the attempted sql connections with the "admin"
> account from the MDB and the domain user id; both fail; both are recorded
> in
> the server Event Log.
> In the case of a domain admin user running the reports, the log continues
> and shows successful access to the database. In looking at the server
> Event
> Log, the success connection is shown as a 'trusted' connection.
> Is there a setting within SQLExpress for the server or database to cause
> the
> pop up window to appear and allow the changing of credentials? I have
> tried
> to create an account within the MDB to match an account within the
> database
> and the server but I can not get the MDB to use that account; it always
> defaults to the 'admin' account.
> Thank you.
>
|||Sylvain:
Thank you for this information; it is putting me in the correct direction.
Some comments and more questions:
1. I posted in the SQL Server Open Database Connectivity (ODBC) forum first
but saw similar answer of yours here; that is why I reposted.
2. I am set up for mixed authentication and I would prefer to have the
credential window appear each time so that the generic, read-only, account is
used to generate the report. If I could set the MDB to use the generic
account without the credential window, that would be good but that might
prevent administrative debugging or changing of the MDB without a relink of
tables.
3. I believe that I understand the sp_change_users_login procedure but I
have not done that yet. I did a REPORT and it shows 2 accounts are not
linked but there is another account that "must be" linked as it did not
appear in the report. I may still do the change on the account that appears
to be linked to ensure that it is linked. The accounts, the one not
reported, were created in the d/b and server AFTER the restore; so the change
may be needed.
4. I am not sure how to get the MDB to retain the password as you
mentioned. I created a DSN for the linking of the MDB to the database. I
used the SA account for the linking process because any other account failed.
Did I miss something here?
5. Unfortunately, we do not have in-house VBA expertise to use the
DSNlesslink that you mentioned; that makes me a little reluctant to go that
route.
F W Green
"Sylvain Lafontaine" wrote:

> Which ODBC forum? There are many of them.
> It's not clear from your description if you want the credential window to
> appear each time or not or if you want to use a domain (or windows or
> "trusted") or a standard (or sql-server) user account.
> If you are using a standard sql-server account, make sure that the
> SQL-Server is setup for mixed authentification (Windows + SQL-Server
> account) because only windows accounts are allowed by default. For the
> standard accounts themselves, if you are trying to use accounts that were
> created before the restoration, make sure that they are correctly mapped to
> their SID by using the sp_change_users_login procedure (or better yet:
> delete and recreate them). See
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
> If you want to use integrated security, make sure that the accounts that you
> want to use are mapped as logins on the SQL-Server: the fact that an account
> can log on a windows server doesn't mean that it can log on the sql-server
> itself.
> If you still have problem, then delete all links and recreate them using
> either a standard sql-server account (account + password) or a windows
> account (ie., integrated security or "trusted" account). If you want the
> mdb file to retain the password, then check the option "Save Password" when
> (re-)creating the links.
> If these links are created programmatically (using vba code), then don't
> forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
> keep the password. Of course, you don't have to use this attribute for
> windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
>
>
|||First, sorry for the late response.
Second, I haven't used MDB with ODBC linked tables since many years, so I
forgot nearly everything about all these little naughty details; so you will
have to make your own little tests in order to see what works and what
don't.
The sp_change_users_login procedure is only for accounts that were created
before the restoration and only in the case when the master database has not
been restored or is from another installation/instance. Not usefull for new
accounts. Also, you see that the use of roles is a better idea than to
directly assign permission to an user account because with roles, it's
pretty easy and straightforward to recreate the old user accounts and
(re-)associate them with their respective roles. (BTW, I don't remember if
you have to use the sp_change_users_login procedure for these cases.)
For the point 4., some things can change when you are using a DSN instead of
a DSN-less connection but again, it's something that I've forgotten a long
time ago and you will have to make your own tests. I'm surprised however
that only the sa account is working for you. I suppose that you may have
forgot to associate these other accounts to their databases.
In all cases and excerpt maybe for the saving of the credentials - which I
don't remember the details -, both methods (DSN or DSN-less) should work as
well as each other.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...[vbcol=seagreen]
> Sylvain:
> Thank you for this information; it is putting me in the correct direction.
> Some comments and more questions:
> 1. I posted in the SQL Server Open Database Connectivity (ODBC) forum
> first
> but saw similar answer of yours here; that is why I reposted.
> 2. I am set up for mixed authentication and I would prefer to have the
> credential window appear each time so that the generic, read-only, account
> is
> used to generate the report. If I could set the MDB to use the generic
> account without the credential window, that would be good but that might
> prevent administrative debugging or changing of the MDB without a relink
> of
> tables.
> 3. I believe that I understand the sp_change_users_login procedure but I
> have not done that yet. I did a REPORT and it shows 2 accounts are not
> linked but there is another account that "must be" linked as it did not
> appear in the report. I may still do the change on the account that
> appears
> to be linked to ensure that it is linked. The accounts, the one not
> reported, were created in the d/b and server AFTER the restore; so the
> change
> may be needed.
> 4. I am not sure how to get the MDB to retain the password as you
> mentioned. I created a DSN for the linking of the MDB to the database. I
> used the SA account for the linking process because any other account
> failed.
> Did I miss something here?
> 5. Unfortunately, we do not have in-house VBA expertise to use the
> DSNlesslink that you mentioned; that makes me a little reluctant to go
> that
> route.
> F W Green
> "Sylvain Lafontaine" wrote:
|||Sylvain:
Thank you very much for your assistance. I believe that I have solved the
issue mostly through trying many things; not because I really know much about
SQL Server.
The SQL server and instance are new; therefore, there is a new master db. I
used SQL Mgmt Studio to restore the d/b from a backup from the old server.
Therefore, the issues of linking between the server and the d/b were real.
No matter what I did, I could not resolve the State 5 error which was an
invalid user. The server logs showed the MDB ADMIN attempted connection and
then my user account without domain preface attempted connection. Both
failed many times when the MDB report was being generated. If a non-Admin
user was running the report, it failed. If a domain Admin user (me) ran the
report, it worked.
In all of my searching, this link,
http://www.webservertalk.com/archive132-2006-10-1710650.html, mentioned 2
things - a) SQLCMD and b) Builtin/Users.
I used the sqlcmd command on the Windows server where the new SQLExpress
instance was running and the logins were okay. Does not prove much but at
least the accounts and passwords to the server were correct.
I checked and in the new instance of SQLExpress, Builtin/Users did NOT have
'dbreader' access to my database. Builtin/Users did not exist in the old
instance but it had been upgraded to SQLExpress from MDSE. In the new
instance, I granted 'dbreader' access to Builtin/Users and now every person
in the firm can run the reports without issue and without the pop-up
authentication window. I am not too concerned about security as the MDB is
strictly a reporting tool and any tweaks will be done by my staff.
Thanks again.
F W Green
"Sylvain Lafontaine" wrote:

> First, sorry for the late response.
> Second, I haven't used MDB with ODBC linked tables since many years, so I
> forgot nearly everything about all these little naughty details; so you will
> have to make your own little tests in order to see what works and what
> don't.
> The sp_change_users_login procedure is only for accounts that were created
> before the restoration and only in the case when the master database has not
> been restored or is from another installation/instance. Not usefull for new
> accounts. Also, you see that the use of roles is a better idea than to
> directly assign permission to an user account because with roles, it's
> pretty easy and straightforward to recreate the old user accounts and
> (re-)associate them with their respective roles. (BTW, I don't remember if
> you have to use the sp_change_users_login procedure for these cases.)
> For the point 4., some things can change when you are using a DSN instead of
> a DSN-less connection but again, it's something that I've forgotten a long
> time ago and you will have to make your own tests. I'm surprised however
> that only the sa account is working for you. I suppose that you may have
> forgot to associate these other accounts to their databases.
> In all cases and excerpt maybe for the saving of the credentials - which I
> don't remember the details -, both methods (DSN or DSN-less) should work as
> well as each other.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...
>
>

Connection from ASP- ODBC vs. SQLOLEDB

I am having a problem in an ASP site (HTTP/1.1:New application failed which
I believe has been caused by inefficient database connections/memory leaks).
One of the recent changes to the way connections are made is
changed connection string from
driver={SQL Server};server=production;uid=pub;pwd=pub;
database=pub
to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
I thought that the OLE DB Provider is quicker/more efficient than the ODBC
driver for SQL Server, isn't it (although in one of the ASP file, I had to
change the CursorLocation used by ADO Recordset from adUseServer to
adUseClient to get a specific ASP file functioning).
Would I normally expect any other adverse outcome switching from ODBC to
SQLOLEDB ?Hi Patrick,
Thank you for using the newsgroup an it is my pleasure to help you with you
issue.
As for you question of when develope ASP application, which is the
recommended method to connect the database, from my experiece, for the
script, ADO is preferable.
1) ODBC is an API for database access that's both a formal and a de facto
industry standard. Besides being one of the most popular database
interfaces used by applications today, ODBC has gained status as the formal
call-level interface standard by American National Standards Institute
(ANSI) and International Organization for Standardization (ISO). SQL Server
provides a high-performance ODBC interface for all Windows-based
programming environments, and it can be distributed royalty-free with any
application. The SQL Server ODBC driver implements every function in the
ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
highest level) conformant.
RDO is an object interface that's closely tied to ODBC, which means that it
exposes all the functionality in the ODBC driver and is easily available to
Visual Basic programs. RDO supports building visual controls tied directly
to SQL Server data, which greatly reduces the amount of code that must be
written to display data on the screen.
2) OLE DB
OLE DB was first released by Microsoft in 1996 to provide a COM interface
to any tabular data source (that is, data that can be represented with rows
and columns). This includes data in spreadsheets and even text files. OLE
DB can be considered an object version of ODBC but is more powerful in that
it can access data from data sources beyond those that ODBC can access.
Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
make programming a call-level interface like ODBC any easier. Also, because
OLE DB uses pointer data types extensively, it's only accessible from C and
C++.
3) ADO
ADO is a higher-level object interface on top of OLE DB that provides much
of the same functionality and performance. Because ADO is pointerless, it
can be accessed from scripting languages such as JScript and development
software such as Visual Basic, as well as from C and C++.
ADO is the recommended and supported interface for Internet applications
written using the Microsoft Visual InterDev development tool. Applications
written with Visual InterDev can call ADO from Active Server Pages (ASP)
and incorporate code written in VBScript or JScript.
For more information, I recommend you to ask you question in our related
newsgroup and the right engineer there will provide you detailed answer
about it:
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
c.inetserver.asp.general
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||The ASP code is already using ADO (adodb.connection, adodb.recordset
objects). However, the connectionString used for opening a DB connection
has been changed from
driver={SQL Server};server=production;uid=pub;pwd=pub;database=pub
in old environment to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
in new environment
The new provider is better than ODBC isn't it, in terms of the underlying
way ADO use to connect to SQL Server 2000?
Also, could any of the following have any effect on memory usage (e.g., poor
garbage collections in terms of not closing connections):
- SQL Server Client Network Utility- "Enable Shared Memory": I am not sure
whether this should be ticked or not. The Server is a Windows 2000 Server
SP4 which is a Domain Controller, running SQL Server 2000 Standard Edition,
ASP, ASP.NET as well as Microsoft Content Management Server 2002 SP1a. (The
application that has failed is the ASP application connecting using ADO)
- SQL Server Client Network Utility- Protocol: Should Named Pipes be used in
preference to TCP/IP
?
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:5JdW06MAEHA.320@.cpmsftngxa06.phx.gbl...
> Hi Patrick,
> Thank you for using the newsgroup an it is my pleasure to help you with
you
> issue.
> As for you question of when develope ASP application, which is the
> recommended method to connect the database, from my experiece, for the
> script, ADO is preferable.
> 1) ODBC is an API for database access that's both a formal and a de facto
> industry standard. Besides being one of the most popular database
> interfaces used by applications today, ODBC has gained status as the
formal
> call-level interface standard by American National Standards Institute
> (ANSI) and International Organization for Standardization (ISO). SQL
Server
> provides a high-performance ODBC interface for all Windows-based
> programming environments, and it can be distributed royalty-free with any
> application. The SQL Server ODBC driver implements every function in the
> ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
> highest level) conformant.
> RDO is an object interface that's closely tied to ODBC, which means that
it
> exposes all the functionality in the ODBC driver and is easily available
to
> Visual Basic programs. RDO supports building visual controls tied directly
> to SQL Server data, which greatly reduces the amount of code that must be
> written to display data on the screen.
> 2) OLE DB
> OLE DB was first released by Microsoft in 1996 to provide a COM interface
> to any tabular data source (that is, data that can be represented with
rows
> and columns). This includes data in spreadsheets and even text files. OLE
> DB can be considered an object version of ODBC but is more powerful in
that
> it can access data from data sources beyond those that ODBC can access.
> Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
> make programming a call-level interface like ODBC any easier. Also,
because
> OLE DB uses pointer data types extensively, it's only accessible from C
and
> C++.
> 3) ADO
> ADO is a higher-level object interface on top of OLE DB that provides much
> of the same functionality and performance. Because ADO is pointerless, it
> can be accessed from scripting languages such as JScript and development
> software such as Visual Basic, as well as from C and C++.
> ADO is the recommended and supported interface for Internet applications
> written using the Microsoft Visual InterDev development tool. Applications
> written with Visual InterDev can call ADO from Active Server Pages (ASP)
> and incorporate code written in VBScript or JScript.
> For more information, I recommend you to ask you question in our related
> newsgroup and the right engineer there will provide you detailed answer
> about it:
>
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
> c.inetserver.asp.general
> Hope this helps.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Patrick,
Thank you for your update.
In my last reply, I just want to say to choose the OLEDB, sorry for the
mistype. For you questions, I will answer them one by one
1)As for the ASP will use the ADO to connect the database and in my last
email, I said ADO is a higher-level object interface on top of OLE DB, so
the level of connect the database would be:
For the provider of OLEDB:
ASP -> ADO -> OLEDB -> Database
For the the provider of ODBC:
ASP -> ADO -> OLEDB -> ODBC -> Database
Also, if the database is SQL Server, you could get for more from OLEDB for
both of them are Microsoft technologies.
Please refer to the following link for detailed information
Choosing Your Data Access Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoledb/htm
l/choosingcomponents.asp
2) Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between
peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read
command. A network read typically involves a series of peek named pipes
messages before it begins to read the data. These can be very costly in a
slow network and cause excessive network traffic, which in turn affects
other network clients.It is also important to clarify if you are talking
about local pipes or network pipes. If the server application is running
locally on the computer running an instance of Microsoft SQL Server?2000,
the local Named Pipes protocol is an option. Local named pipes runs in
kernel mode and is extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance
enhancement mechanisms such as windowing, delayed acknowledgements, and so
on, which can be very beneficial in a slow network. Depending on the type
of
applications, such performance differences can be significant.TCP/IP
Sockets also support a backlog queue, which can provide a limited smoothing
effect compared to named pipes that may lead to pipe busy errors when you
are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it
offers more functionality, ease of use, and configuration options. You
could just choose which protocol to use depends on you production
environment.
3) As for Shared Memory Net-Library is used only for intra-computer
communications, you could let it as the default setting on the client
network utility.
Hope this helps!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Connection from Access to SQL Server does not show ALL tables

I have an ODBC connection to a SQL server database and for some reason I can
see some of the tables but not all of them when i try to establish a link to
a table.
any ideas? Is there such a thing as a hidden table at the SQL server end ?
Steve
No, there is not. I'd check your permissions to see if you have
adequate permissions for the ODBC login/DSN you are using to connect,
and if that's not it, check the syntax in your code for typos in
names, etc.
--Mary
On Fri, 3 Feb 2006 09:50:21 -0800, "Steve"
<Steve@.discussions.microsoft.com> wrote:

>I have an ODBC connection to a SQL server database and for some reason I can
>see some of the tables but not all of them when i try to establish a link to
>a table.
>any ideas? Is there such a thing as a hidden table at the SQL server end ?

Connection from Access to SQL Server does not show ALL tables

I have an ODBC connection to a SQL server database and for some reason I can
see some of the tables but not all of them when i try to establish a link to
a table.
any ideas? Is there such a thing as a hidden table at the SQL server end ?
--
SteveNo, there is not. I'd check your permissions to see if you have
adequate permissions for the ODBC login/DSN you are using to connect,
and if that's not it, check the syntax in your code for typos in
names, etc.
--Mary
On Fri, 3 Feb 2006 09:50:21 -0800, "Steve"
<Steve@.discussions.microsoft.com> wrote:

>I have an ODBC connection to a SQL server database and for some reason I ca
n
>see some of the tables but not all of them when i try to establish a link t
o
>a table.
>any ideas? Is there such a thing as a hidden table at the SQL server end ?

Connection for windows authorization

I have a vb script that uses odbc.connection that connects to the sql
server, snippet as shown:
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
if (g_bWinAuth = FALSE) then
szConnString = szConnString + "database=master;";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword;
else // windows authorization
szConnString = szConnString + "Initial Catalog=master;";
szConnString = szConnString + "Integrated security=True";
endif;
// Open the ADO Connection
pADOConnObj.Open(szConnString);
I have no problem with the login with uid-pwd. However, if I use windows
authorization, I got the following error:
Microsoft OLE DB Service Components (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available.
Is my connection string correct?
TIA.Hi
I assume you can use a trusted connection from other application with this
login?
Try the connection string
Driver={SQL
Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;
as described for an ODBC connection in:
http://www.connectionstrings.com/?carrier=sqlserver
John
"Light" wrote:
> I have a vb script that uses odbc.connection that connects to the sql
> server, snippet as shown:
> szADOConnObjID = "ADODB.Connection";
> set pADOConnObj = CreateObject(szADOConnObjID);
> // Create the SQL string to complete the connection
> szConnString = "driver={" + svDriver + "};";
> szConnString = szConnString + "server=" + svServerName + ";";
> if (g_bWinAuth = FALSE) then
> szConnString = szConnString + "database=master;";
> szConnString = szConnString + "uid=" + svUserName + ";";
> szConnString = szConnString + "pwd=" + svUserPassword;
> else // windows authorization
> szConnString = szConnString + "Initial Catalog=master;";
> szConnString = szConnString + "Integrated security=True";
> endif;
> // Open the ADO Connection
> pADOConnObj.Open(szConnString);
> I have no problem with the login with uid-pwd. However, if I use windows
> authorization, I got the following error:
> Microsoft OLE DB Service Components (0x80040E21)
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available.
> Is my connection string correct?
> TIA.
>|||Hi Light,
You may try John's suggestion first. Also, I would like to know:
What is the result if you change your code for windows authentication to
the following code:
szConnString = szConnString + "database=master;";
szConnString = szConnString + "Integrated security=SSPI";
Hope this helps. Please feel free to let me know if you have any questions
or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi John and Charlies,
The solution from John works but the SSPI doesn't.
As long as one of them works, I am happy. However, I would like to know the
difference between them though.
Thanks for all your helps.
Cheers.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:XyodACw4HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Light,
> You may try John's suggestion first. Also, I would like to know:
> What is the result if you change your code for windows authentication to
> the following code:
> szConnString = szConnString + "database=master;";
> szConnString = szConnString + "Integrated security=SSPI";
> Hope this helps. Please feel free to let me know if you have any questions
> or concerns.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Light,
After I dicussed this issue with an ADO expert, this issue was related to
the driver option specified in your connection string. ODBC driver does not
recognize "integrated security" option.
If you use SQL OLEDB provider for the connection string
"Provider=sqloledb;server=CharlesXP;database=Test;Integrated
security=SSPI", it will work fine.
You may refer to:
ADO Connection Strings
http://www.codeproject.com/database/connectionstrings.asp
If you have any other questions or concerns, please feel free to let me
now. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks Charlies.
This is nice info to have.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:6WMHNf04HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Light,
> After I dicussed this issue with an ADO expert, this issue was related to
> the driver option specified in your connection string. ODBC driver does
> not
> recognize "integrated security" option.
> If you use SQL OLEDB provider for the connection string
> "Provider=sqloledb;server=CharlesXP;database=Test;Integrated
> security=SSPI", it will work fine.
> You may refer to:
> ADO Connection Strings
> http://www.codeproject.com/database/connectionstrings.asp
> If you have any other questions or concerns, please feel free to let me
> now. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Light,
I am glad to hear that the suggestions are helpful. If you have any other
qeustions or concerns, please feel free to let us know.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Sunday, March 25, 2012

Connection Pooling Limit thru ODBC

connection pooling for access jet connected ODBC tables?

connection pooling for access jet connected ODBC tables?

Thursday, March 22, 2012

connection pooling

connection pooling

Tuesday, March 20, 2012

Connection no longer works

Monday, March 19, 2012

Connection management

Connection management

Sunday, March 11, 2012

Connection is busy with results for another hstmt

Connection is busy with results for another command

Connection is busy with results for another command

Connection is busy with results for another command

Connection is busy with results for another command

Connection from XP SP2 to Windows 2003 R2 with SQL Server 2005 failed

Do I have to make some file shares manually?

Connection from MS Access MDB

Connection from MS Access MDB

Connection from ASP- ODBC vs. SQLOLEDB

Connection from Access to SQL Server does not show ALL tables

Connection from Access to SQL Server does not show ALL tables

Connection for windows authorization

asp sql server

Blog Archive

About Me