Objects related to DB: via

Showing posts with label via. Show all posts

Friday, March 30, 2012

ODBC DSN for SQL Server via script

Hi
I have the below script to create DSN for SQL Server. The problem is this
does not include a username and password option for SQL Server
authentication. Could someone please guide me how to add the username and
password option for authentication to this script?
Many Thanks
Regards
= Script Below ========================
Const HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = "My SQL Server"
strValue = "SQL Server"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
strValueName = "Database"
strValue = "My Database"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Driver"
strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Server"
strValue = "111.111.111.111"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Trusted_Connection"
strValue = "No"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValueAFAIK you can't programmatically store the password.
http://support.microsoft.com/kb/184608
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"John" wrote:
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||To add to Dave's response, the SQL Server ODBC driver does not store the SQL
password for security reasons; anyone with access to the DSN could then
connect to SQL Server. The password needs to be provided by the application
when connecting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||what are you doing?
SQL linked tables in a MDB?
you should jsut use Access Data Projects, kid
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||Dan
crap answers like that is why SQL Server is _STILL_ not secure.
We don't friggin care if you think that it's secure
We don't friggin care what your premise is.
It's goddamn ridiculous that MS refuses to take SQL authentication
seriously.
I GOT CANNED AND BLACKLISTED FROM MICROSOFT FOR COMPLAINING ABOUT SQL
AUTHENTICATION
We just want to create the passwords and use SQL Authentication
I've worked in 100 different SQL Server environments these past 10 years..
and 80% of the clients in the real world use SQL Authentication for
_SOMETHING_.
stfu and take your premise back to MS and tell them to stfu and fix SQL
Authentication
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:418F29B3-9C30-406D-80A5-6B4F1AE9758F@.microsoft.com...
> To add to Dave's response, the SQL Server ODBC driver does not store the
SQL
> password for security reasons; anyone with access to the DSN could then
> connect to SQL Server. The password needs to be provided by the
application
> when connecting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I have the below script to create DSN for SQL Server. The problem is
this
> > does not include a username and password option for SQL Server
> > authentication. Could someone please guide me how to add the username
and
> > password option for authentication to this script?
> >
> > Many Thanks
> >
> > Regards
> >
> >
> > = Script Below ========================> >
> > Const HKEY_LOCAL_MACHINE = &H80000002
> >
> > strComputer = "."
> >
> > Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> > strComputer & "\root\default:StdRegProv")
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> > strValueName = "My SQL Server"
> > strValue = "SQL Server"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > strValueName = "Database"
> > strValue = "My Database"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Driver"
> > strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Server"
> > strValue = "111.111.111.111"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Trusted_Connection"
> > strValue = "No"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> >
>

Monday, March 26, 2012

ODBC data source

I am trying to use the ADO.NET bridge to connect to an old database on RISC6000 via an ODBC connection, but I receive an error.

So, I would use a script component to retrieve data directly from an ODBC connection, using a stored procedure.

Any sample code ?

Any idea ?

Thanks

You can use the DataReader source in the data flow as another option connecting to an ODBC source.

ODBC Connectivity Issues

I have a windows 98 pc trying to connect to a sql server running on Windows Server 2003 via a frame relay. The 98 pc will connect to the domain and it is able to ping the server. However, when I try to use my MS Access program that connects to the sql server, I get this error message:

Connection Failed
SqlState: '01000'
Sql ServerError: 11001
[Microsoft][ODBC Sql Server Driver][TCP/IP Sockets] Connection Open (Connect ()).
Connection Failed
SqlState: '08001'
Sql ServerError: 6
[Microsoft][ODBC Sql Server Driver][TCP/IP Sockets] Specified Sql server not found.

I have other pcs that connect to the Sql Server fine but they are located locally in the same building as the server. I've tried connecting through named pipes but for some reason, I get the same error. I have downloaded and installed the new MDAC drivers for the 98 pc. Any suggestions?Just a couple of things to check:

1. Are all the DSN names the same on all machines (are you using linked tables into MSAccess?) Does a DSN name exist on the PC?
2. If they are linked tables into MSAccess, when you linked the table in MSAccess, did you check the "Save Password".
3. Does the user have permissions to the tables in SQL Server? Is their loginID or their domain group set up for permissions to the database?

Hope any of these helps.|||Yes, the DSN names are the same, and the user name and password are the same. There are multiple users that use the "generic" username and password and it works fine for them. I looked into the linked tables idea and this is what happened. When I link the tables in Access, it worked. However, when I exited the program and went back into it, it did not work anymore. Now when I try to link the tables again, it will not let me and the program will not work and I get that error message again. Any other ideas?

ODBC Connections

I have a network with 6 machines sitting on it.
All PC`s are configued in the same way. 5 of the machines connect to a SQL
server via ODBC with no problems.
The sixth PC however does not connect to the server giving the following :-
Connection Failed
SQL State '01000;
SQL Server Error: 11001
TCP/IP Sockets Connection Open
GetHosting Name
General network error.
I`m connecting via aTCP/IP on port 1433.
Does anyone have any idea why I am encountering these problems ?did you try 'telnet YourDBServerIP 1433'?
"Simon" wrote:

> I have a network with 6 machines sitting on it.
> All PC`s are configued in the same way. 5 of the machines connect to a SQL
> server via ODBC with no problems.
> The sixth PC however does not connect to the server giving the following :
-
> Connection Failed
> SQL State '01000;
> SQL Server Error: 11001
> TCP/IP Sockets Connection Open
> GetHosting Name
> General network error.
>
> I`m connecting via aTCP/IP on port 1433.
>
> Does anyone have any idea why I am encountering these problems ?
>|||I`m probably being a sponge but I don`t seem to get anything using that
command from any of the PC`s.
"AAO" wrote:
[vbcol=seagreen]
> did you try 'telnet YourDBServerIP 1433'?
> "Simon" wrote:
>|||You should get an error if SQL Server isn't listening on
1433. Or you should blank screen with the title of the
window changing to Telnet <Your Server> if you have
successfully established a telnet connection on that port.
It just sounds like you are connecting.
-Sue
On Fri, 12 May 2006 07:22:02 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I`m probably being a sponge but I don`t seem to get anything using that
>command from any of the PC`s.
>"AAO" wrote:
>|||Firewall?
/Henning
"Simon" <Simon@.discussions.microsoft.com> skrev i meddelandet
news:1359348E-85DA-43DD-AA79-313F4F92DF94@.microsoft.com...[vbcol=seagreen]
> I`m probably being a sponge but I don`t seem to get anything using that
> command from any of the PC`s.
> "AAO" wrote:
>
SQL[vbcol=seagreen]
following :-[vbcol=seagreen]|||The firewall setup is the same accross all the machines so I don`t think is
causing the problem.
"Henning" wrote:

> Firewall?
> /Henning
> "Simon" <Simon@.discussions.microsoft.com> skrev i meddelandet
> news:1359348E-85DA-43DD-AA79-313F4F92DF94@.microsoft.com...
> SQL
> following :-
>
>

Friday, March 23, 2012

ODBC connection to SQL server failing

Hi, I have an access 2003 database which connect to sql server via a
DSN that goes through a VPN. The database works fine and everyone can
coonect from their normal computers but for anyone working on their
Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
The DSN fails upon connection attempt with :

Connection Failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access is denied.

This has been driving me insane as i cannot understand why it works for
our desktop computers and not for our laptops. The version of MDAC is
the same on a computer that connects as it is on a laptop that doesn't.
TCP/IP is enabled on the server and is using the default 1433 port.
severs, desktops and laptops all patches and up to date with the latest
SP's. DSN has been dropped and re-created. Domain user has been put in
localAdmin group for laptop.

I hope i'm mising something really obvious...can someone please please
put me out of my misery and tell me they have come accross this before
and have a solution. Ive tried everything i can think of.

Cheers

DanDan (dan_barber2003@.hotmail.com) writes:
> Hi, I have an access 2003 database which connect to sql server via a
> DSN that goes through a VPN. The database works fine and everyone can
> coonect from their normal computers but for anyone working on their
> Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
> The DSN fails upon connection attempt with :
> Connection Failed:
> SQLState: '01000'
> SQL Server Error: 1326
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection Failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access is denied.

The problem is that the laptop somehow does not find the SQL Server.
This KB article discusses posible reasons:
http://support.microsoft.com/defaul...B;EN-US;q328306

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland. I had found that article and gone through (as best i
could) all the options but still had no joy. I have finally solved it
though. On our desktops we have microsoft firewall client installed and
enabled but on the laptops we dont. For some reason it seems that we
cannot connect through our proxy server and a connection to sql server
has to be done through this firewall client?? Even though i have solved
the problem i am still not sure why this happened??

Thanks again

Dan|||Dan (dan_barber2003@.hotmail.com) writes:
> Thanks, Erland. I had found that article and gone through (as best i
> could) all the options but still had no joy. I have finally solved it
> though. On our desktops we have microsoft firewall client installed and
> enabled but on the laptops we dont. For some reason it seems that we
> cannot connect through our proxy server and a connection to sql server
> has to be done through this firewall client?? Even though i have solved
> the problem i am still not sure why this happened??

It sounds a little funny, I will have to admit. But being nowhere
close to a Windows networking expert, I don't have any explanations.

But if you have the SQL connection working, and also have the firewall
enabled on the laptops, that sounds like a double win to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ODBC Connection to SQL

I have a network with 6 machines sitting on it.
All PC`s are configued in the same way. 5 of the machines connect to a SQL
server via ODBC with no problems.
The sixth PC however does not connect to the server giving the following :-
Connection Failed
SQL State '01000;
SQL Server Error: 11001
TCP/IP Sockets Connection Open
GetHosting Name
General network error.
I`m connecting via aTCP/IP on port 1433.
Does anyone have any idea why I am encountering these problems ?Is there error actually GetHostByName?
On the PC that can't connect, try pinging the server by
server name and try pinging the server by IP and see if
there are any issues with pinging by name. If there are then
check that client for network issues as it's having problems
resolving the server name.
Otherwise, the issue you are seeing is often due to MDAC
issues. Try updating or reinstalling on that PC. You may
also
want to check the MDAC installation using component
checker. You can download component checker and MDAC
versions from:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
-Sue
On Fri, 12 May 2006 01:17:02 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:

>I have a network with 6 machines sitting on it.
>All PC`s are configued in the same way. 5 of the machines connect to a SQL
>server via ODBC with no problems.
>The sixth PC however does not connect to the server giving the following :-
>Connection Failed
>SQL State '01000;
>SQL Server Error: 11001
>TCP/IP Sockets Connection Open
>GetHosting Name
>General network error.
>
>I`m connecting via aTCP/IP on port 1433.
>
>Does anyone have any idea why I am encountering these problems ?
>

ODBC connection prompting for 'sa' password

We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versions
of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior versio
ns so that the password in the ODBC connection does not have to be re-entered?
Don't know much about Excel. But can you create another account (not sa)
to access the DB? This way you can keep your sa password secret.
Eric
Mike wrote:

> We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versio
ns of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior vers
ions so that the password in the ODBC connection does not have to be re-entered?
Eric Li
SQL DBA
MCDBA

ODBC connection problems in SSIS

I'm trying to connect to a Postgres database via ODBC from SSIS. The odbc connection is defined on my PC and works perfectly with other applications, including Access. However, when I set up the connection in SSIS, everything appears to connect fine (test connection works, etc.) but when I look at the data connection in Server Explorer, there are no tables, no views, and no procedures. Also, the name of the data connection is ODBC.csidb. csidb is the name of the server where the postgres database resides, but the database name itself isn't showing up. Can someone please let me know what I'm doing wrong? Thanks!

Laurie

Further investigation has proved that I am, in fact, connecting successfully to the database but for some reason the metadata is not being shown. Any ideas?|||

Can you repro the same problem elsewhere? (i.e. not in SSIS)

-Jamie

|||No, I can't. It works in other places.

Wednesday, March 21, 2012

ODBC Connection failure

My application (written in a proprietary old 4GL) is connecting to a SQL 2000 database via ODBC

This works fine nearly all the time, but occasionally, it throws the following error when trying to connect :

Connection Failed

SQLState '01000'

SQL Server Error: 10060

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection Open(Connect()).

Connection Failed:

SQLState '08001'

SQL Server Error: 17

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

This error is displayed in a Windows dialog box. When the OK button on this dialog is clicked, it throws up a login/password box. When this is filled in and submitted, the connection is usually made cleanly and the application continues.

The big problem is that the application is an unattended service, and this dialog can go unnoticed for quite a while.

If these dialogs are 'Cancelled' rather than 'OK'd, the 4GL receives the error and arranges to re-try the connection itself.

Is ODBC responsible for popping up the interactive dialog, and, if so, can I do anything to force it to return an error instead ?

Hi Steve,

Interesting situation. The error 10060 means this: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. So you are probably experiencing a timeout in one of the socket-relation ops, most probably connect() (different from the SQL netlib's Connect() mentioned above).

The login popup is implemented by the SQL ODBC driver. You are probably using SQLDriverConnect in ODBC? It has a parameter "DriverCompletion", which could be:

SQL_DRIVER_PROMPT
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
SQL_DRIVER_NOPROMPT

You probably have passed the first one (SQL_DRIVER_PROMPT). In order to make sure the SQLDriverConnect fails without popping up the troublesome dialog, you need to pass the SQL_DRIVER_NOPROMPT. I truly hope you are able to modify the 4GL application?

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks a lot Jivko.

Unfortunately, the only parameter the 4GL allows me to specify is the DSN name.

I'll be on to the vendor's support desk rather forcefully first thing on Monday morning - thanks for the Ammo!

ODBC connection failure

After upgrading to Windows 2003 Server, I can no longer
access SQL Server via ODBC. It says the SQL Server
doesn't exist or access denied. None of the settings have
changed after the upgrade.
Is there a fix to this?
Thanks in advance.(a) stop using ODBC, it's been deprecated. OLEDB is much preferred.
(b) SQL Server authentication? Windows authentication? What is the
username you are using? What kind of account is it? Have you tried a
different authentication method and/or a different user?
(c) http://support.microsoft.com/?kbid=328306
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:19b9c01c44d6d$b0030b20$a401280a@.phx.gbl...
> After upgrading to Windows 2003 Server, I can no longer
> access SQL Server via ODBC. It says the SQL Server
> doesn't exist or access denied. None of the settings have
> changed after the upgrade.
> Is there a fix to this?
> Thanks in advance.|||How would I switch to the OLEDB method? It's SQL Server
auth., and also tried different methods and users.
Also, I found that the ODBC Driver versions are different
on the client machines. Would I need to update the
clients to the new version?
>--Original Message--
>(a) stop using ODBC, it's been deprecated. OLEDB is much
preferred.
>(b) SQL Server authentication? Windows authentication?
What is the
>username you are using? What kind of account is it?
Have you tried a
>different authentication method and/or a different user?
>(c) http://support.microsoft.com/?kbid=328306
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:19b9c01c44d6d$b0030b20$a401280a@.phx.gbl...
>> After upgrading to Windows 2003 Server, I can no longer
>> access SQL Server via ODBC. It says the SQL Server
>> doesn't exist or access denied. None of the settings
have
>> changed after the upgrade.
>> Is there a fix to this?
>> Thanks in advance.
>
>.
>|||> How would I switch to the OLEDB method?
That really depends... what application/language are you using for the
client applications?
> Also, I found that the ODBC Driver versions are different
> on the client machines. Would I need to update the
> clients to the new version?
Yes, apply the latest MDAC all around. You should try to avoid mismatched
versions between client and server.
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Installing the MDAC results in the same SQL Server does
not exist error.
All of the clients are using Windows XP Pro to connect to
the SQL Server.
>--Original Message--
>> How would I switch to the OLEDB method?
>That really depends... what application/language are you
using for the
>client applications?
>> Also, I found that the ODBC Driver versions are
different
>> on the client machines. Would I need to update the
>> clients to the new version?
>Yes, apply the latest MDAC all around. You should try to
avoid mismatched
>versions between client and server.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-b037-
185d0506396c&DisplayLang=en
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)|||> All of the clients are using Windows XP Pro to connect to
> the SQL Server.
That's the operating system, but it doesn't tell me anything about the
application that is attempting to make a connection. Are you trying to
connect from Excel, notepad, ColdFusion, Query Analyzer, the ODBC control
panel applet, ... ?
Have you gone through http://support.microsoft.com/?kbid=328306 ?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I'm trying to connect from the ODBC Control Panel.
Yes, I have through the 328306 article and haven't found
any resolutions.
Would the easiest thing to do be revert back to Windows
2000 when this all worked correctly?
>--Original Message--
>> All of the clients are using Windows XP Pro to connect
to
>> the SQL Server.
>That's the operating system, but it doesn't tell me
anything about the
>application that is attempting to make a connection. Are
you trying to
>connect from Excel, notepad, ColdFusion, Query Analyzer,
the ODBC control
>panel applet, ... ?
>Have you gone through http://support.microsoft.com/?
kbid=328306 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||> I'm trying to connect from the ODBC Control Panel.
Why? And then what are you going to do with it once you've connected?
Typically this is used to create a DSN for some other application, like an
ASP page or VB app. In which case, you are much better off connecting
through OLEDB. I can't tell you how to do that until you provide more
information.
> Yes, I have through the 328306 article and haven't found
> any resolutions.
Can you tell us what you tried, with regard to each article listed at that
link? I'm guessing you didn't inspect every single article listed there,
since it has been less than an hour, and you'd have to be Number 5 from
Short Circuit to have read all of the content already.
> Would the easiest thing to do be revert back to Windows
> 2000 when this all worked correctly?
No, that's silly. Windows XP is not to blame; you have a configuration
issue, that's all. If you tell us everything you tried regarding 328306,
you will leave the remaining potential options.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I need the DSN to run Crystal Reports.
As for KB #328306, I tried everything under the Server-
Related Causes. The protocols are all ok, port 1433 is
the same on each machine, and tried giving the server a
new alias.
As for the Client/App-Related Causes, the protocols don't
change, no network adaptor issues, not worried about MDAC
connections, and Named Pipes don't apply.
There was nothing applicable under the Network Causes as
everything is run locally.
>--Original Message--
>> I'm trying to connect from the ODBC Control Panel.
>Why? And then what are you going to do with it once
you've connected?
>Typically this is used to create a DSN for some other
application, like an
>ASP page or VB app. In which case, you are much better
off connecting
>through OLEDB. I can't tell you how to do that until you
provide more
>information.
>> Yes, I have through the 328306 article and haven't found
>> any resolutions.
>Can you tell us what you tried, with regard to each
article listed at that
>link? I'm guessing you didn't inspect every single
article listed there,
>since it has been less than an hour, and you'd have to be
Number 5 from
>Short Circuit to have read all of the content already.
>> Would the easiest thing to do be revert back to Windows
>> 2000 when this all worked correctly?
>No, that's silly. Windows XP is not to blame; you have a
configuration
>issue, that's all. If you tell us everything you tried
regarding 328306,
>you will leave the remaining potential options.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||So what name are you using to refer to the local server? Have you tried
"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server name, the actual IP
address?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
> I need the DSN to run Crystal Reports.
> As for KB #328306, I tried everything under the Server-
> Related Causes. The protocols are all ok, port 1433 is
> the same on each machine, and tried giving the server a
> new alias.
> As for the Client/App-Related Causes, the protocols don't
> change, no network adaptor issues, not worried about MDAC
> connections, and Named Pipes don't apply.
> There was nothing applicable under the Network Causes as
> everything is run locally.
> >--Original Message--
> >> I'm trying to connect from the ODBC Control Panel.
> >
> >Why? And then what are you going to do with it once
> you've connected?
> >Typically this is used to create a DSN for some other
> application, like an
> >ASP page or VB app. In which case, you are much better
> off connecting
> >through OLEDB. I can't tell you how to do that until you
> provide more
> >information.
> >
> >> Yes, I have through the 328306 article and haven't found
> >> any resolutions.
> >
> >Can you tell us what you tried, with regard to each
> article listed at that
> >link? I'm guessing you didn't inspect every single
> article listed there,
> >since it has been less than an hour, and you'd have to be
> Number 5 from
> >Short Circuit to have read all of the content already.
> >
> >> Would the easiest thing to do be revert back to Windows
> >> 2000 when this all worked correctly?
> >
> >No, that's silly. Windows XP is not to blame; you have a
> configuration
> >issue, that's all. If you tell us everything you tried
> regarding 328306,
> >you will leave the remaining potential options.
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >.
> >|||The actual server name is what I'm using.
>--Original Message--
>So what name are you using to refer to the local server?
Have you tried
>"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server
name, the actual IP
>address?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
>> I need the DSN to run Crystal Reports.
>> As for KB #328306, I tried everything under the Server-
>> Related Causes. The protocols are all ok, port 1433 is
>> the same on each machine, and tried giving the server a
>> new alias.
>> As for the Client/App-Related Causes, the protocols
don't
>> change, no network adaptor issues, not worried about
MDAC
>> connections, and Named Pipes don't apply.
>> There was nothing applicable under the Network Causes as
>> everything is run locally.
>> >--Original Message--
>> >> I'm trying to connect from the ODBC Control Panel.
>> >
>> >Why? And then what are you going to do with it once
>> you've connected?
>> >Typically this is used to create a DSN for some other
>> application, like an
>> >ASP page or VB app. In which case, you are much better
>> off connecting
>> >through OLEDB. I can't tell you how to do that until
you
>> provide more
>> >information.
>> >
>> >> Yes, I have through the 328306 article and haven't
found
>> >> any resolutions.
>> >
>> >Can you tell us what you tried, with regard to each
>> article listed at that
>> >link? I'm guessing you didn't inspect every single
>> article listed there,
>> >since it has been less than an hour, and you'd have to
be
>> Number 5 from
>> >Short Circuit to have read all of the content already.
>> >
>> >> Would the easiest thing to do be revert back to
Windows
>> >> 2000 when this all worked correctly?
>> >
>> >No, that's silly. Windows XP is not to blame; you
have a
>> configuration
>> >issue, that's all. If you tell us everything you tried
>> regarding 328306,
>> >you will leave the remaining potential options.
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >.
>> >
>
>.
>|||So, try the others...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:199f801c44d81$97deaf90$a301280a@.phx.gbl...
> The actual server name is what I'm using.
> >--Original Message--
> >So what name are you using to refer to the local server?
> Have you tried
> >"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server
> name, the actual IP
> >address?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Mike" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
> >> I need the DSN to run Crystal Reports.
> >>
> >> As for KB #328306, I tried everything under the Server-
> >> Related Causes. The protocols are all ok, port 1433 is
> >> the same on each machine, and tried giving the server a
> >> new alias.
> >>
> >> As for the Client/App-Related Causes, the protocols
> don't
> >> change, no network adaptor issues, not worried about
> MDAC
> >> connections, and Named Pipes don't apply.
> >>
> >> There was nothing applicable under the Network Causes as
> >> everything is run locally.
> >>
> >> >--Original Message--
> >> >> I'm trying to connect from the ODBC Control Panel.
> >> >
> >> >Why? And then what are you going to do with it once
> >> you've connected?
> >> >Typically this is used to create a DSN for some other
> >> application, like an
> >> >ASP page or VB app. In which case, you are much better
> >> off connecting
> >> >through OLEDB. I can't tell you how to do that until
> you
> >> provide more
> >> >information.
> >> >
> >> >> Yes, I have through the 328306 article and haven't
> found
> >> >> any resolutions.
> >> >
> >> >Can you tell us what you tried, with regard to each
> >> article listed at that
> >> >link? I'm guessing you didn't inspect every single
> >> article listed there,
> >> >since it has been less than an hour, and you'd have to
> be
> >> Number 5 from
> >> >Short Circuit to have read all of the content already.
> >> >
> >> >> Would the easiest thing to do be revert back to
> Windows
> >> >> 2000 when this all worked correctly?
> >> >
> >> >No, that's silly. Windows XP is not to blame; you
> have a
> >> configuration
> >> >issue, that's all. If you tell us everything you tried
> >> regarding 328306,
> >> >you will leave the remaining potential options.
> >> >
> >> >--
> >> >http://www.aspfaq.com/
> >> >(Reverse address to reply.)
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

ODBC Connect from MS Access to SQL Server 2000-Viable?

How viable is it to use MS Access as a front end (via ODBC) to a SQL Server
2000 database?

The users would access via the internet using a netgear vpn setup.

Thanks,

Paul SPaul Stanial wrote:
> How viable is it to use MS Access as a front end (via ODBC) to a SQL Server
> 2000 database?
> The users would access via the internet using a netgear vpn setup.
> Thanks,
> Paul S

I've seen this configuration more as an afterthought. What I mean is
that someone uses Access to design an application, complete with Forms
and Reports. Afterwards, they wish to put the data in SQL Server, but
they don't want to redo their Forms and Reports. From those projects
that I've moved the data to SQL Server, things have worked really well.

That being said, if this was a new application, I'd be deploying
something web-based (since you indicated that your users would access
via the internet). With many of the web-based tools out there, you can
quickly create applications as well.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@.remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Monday, March 19, 2012

ODBC Connect Failed in SQL Job

I am running SQL Server 2000 sp4 in Win Server 2003. I have a DTS

packages which connects to FileMaker 6 files via the ODBC driver

installed with FielMaker.

The DTS packages work correctly when

executed directly. However, when executed from a SQL Server Agent job,

the same DTS packages fail. Always with the same message:

Error: -2147467259 (80004005);
Provider Error: 16394 (400A)
Error string: [FileMaker][ODBC FileMaker Pro driver][FileMaker Pro]Connect failed
Error source: Microsoft OLE DB Provider for ODBC Drivers

Searching

for info on the above message seems to indicate a permissions problem.

However, I am running the jobs with an full administrator account. All

connections and the FielMaker files reside on the one server.

Any help on this would be greatly appreciated.

Thank you.

The error you saw is very general, the message indicated a connection fail, so please provide your connection string, and try to connect to your sql instance by simply use osql.exe, if failed, that means the basic connection did not work on your server, otherwise, the error triggered during data operation, then you need sql profile to track which client operation cause server close connection.

Thanks!

Ming.

ODBC cannot connect to MSDE 2000a

Using MSDE 2000a (w/spk3a). XP Pro. Cannot connect w/ ODBC driver to server.
Have tried many things.
Below is the error when trying to connect via ODBC.
Help!
GIL
Microsoft SQL Server Login
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist
or access denied.
hi,
"Padji" <gil.pajany@.free.fr> ha scritto nel messaggio
news:410f60ea$0$1890$636a15ce@.news.free.fr...
> Using MSDE 2000a (w/spk3a). XP Pro. Cannot connect w/ ODBC driver to
server.
> Have tried many things.
> Below is the error when trying to connect via ODBC.
> Help!
> GIL
>
> --
> Microsoft SQL Server Login
> --
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 10061
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist
> or access denied.
>
>
please verify your instance has netwrok protocols enabled... run Server
Network Utility (svrnetcnexe) to check that and, eventually, enable TCP/IP
as needed..
eventually, run Client Network Utility (cliconfg.exe) and make sure TCP/IP
are enabled ... try to make an alias using TCP using SQL server name and
test connection. if it still fails, try creating an alias with the IP
address of the SQL server.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I am having the exact same problem. I'm using SQL Server 2005 beta.
In the SQL manager utility I did what you suggested, which is to enable all
network protocols, and am getting the same issue.
It doesn't matter what client configuration I use for ODBC -- shared memory,
named pipes, tcp.
Should I uninstall the DB, delete all registry settings related to it, and
then reinstall?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2n9m2eFti527U1@.uni-berlin.de...
> hi,
> "Padji" <gil.pajany@.free.fr> ha scritto nel messaggio
> news:410f60ea$0$1890$636a15ce@.news.free.fr...
> server.
> exist
> please verify your instance has netwrok protocols enabled... run Server
> Network Utility (svrnetcnexe) to check that and, eventually, enable TCP/IP
> as needed..
> eventually, run Client Network Utility (cliconfg.exe) and make sure TCP/IP
> are enabled ... try to make an alias using TCP using SQL server name and
> test connection. if it still fails, try creating an alias with the IP
> address of the SQL server.
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
"Yair Sageev" <geekyheeb-news@.yahoo.com> ha scritto nel messaggio
news:eFIcuAkeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> I am having the exact same problem. I'm using SQL Server 2005 beta.
> In the SQL manager utility I did what you suggested, which is to enable
all
> network protocols, and am getting the same issue.
> It doesn't matter what client configuration I use for ODBC -- shared
memory,
> named pipes, tcp.
> Should I uninstall the DB, delete all registry settings related to it, and
> then reinstall?
>
before reinstalling, pleas have a look at
http://support.microsoft.com/default...06&Product=sql
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

ODBC Call Fail/Record locked

I have recently moved some native Access tables to SQL Server 7. These tables are updated in code. When the update is done via a SQL statement, I get
"This record is being modified by another user. . . Save, Copy to Clipboard, Drop Changes"

When the record is being updated via DAO code, I get,
"ODBC Call Fail"

Both errors are most irritating and I desperately need to find a way around this. Any suggestions would be greatly appreciated.

CrystalHi there,

Maybe this information helps you on the way...

-- Microsoft Knowledge Base Article - 128809

-- Zoek in de Google nieuwsgroepen naar Onderwerp:
"auto_increment fields and #Deleted in MS Access?"
Nieuwsgroep:
-> mailing.database.myodbc

Greetz,
DePrins
:)

Monday, March 12, 2012

ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbName;UID=xx;P WD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMiller
It's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
>Client};Server=ServerName;Database=DbName;UID=xx; PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and another
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credentials
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMillerIt's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
> Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and anothe
r
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credential
s
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

ODBC & Multi-threading

I'm working on a C# (2005) app that requires shooting a large number of queries via Odbc, wait until the last one is done and then compute an overall result.

Since the queries are not interdependent, I would like to get them to run concurrently on multiple threads.

I did manage to get several queries run on different threads but the response time for the same query varies dramatically when another query is running concurently (even on a different ODBC connection). All is as if something was queing those queries regardless of the threads and/or the connection objects being used.

Basically when I run a very simple query q1 (thread 1 connection1) on its own, the result is instantaneous, but if I first start a big query q2 (thread 2, connection 2) and then q1 while q2 is running q1 takes forever.

Is it possible at all (for example, does the Odbc driver support being used that way)

Any suggestion as to how to handle that problem would be appreciated.

I do not see why it could not be done. Miltithreading is basically the same as multiple connections from different clients at same time and it is definitely allowed. First it is quite possible that your first thread blocks another one until it finishes execution. In this case second block is doing nothing and just waits. Another potential issue is your big query is so *heavy* for the server that sever just uses 100% of its resources for this query. But I am doubt that this is the case|||

Are you using (nolocks) on your tables?

Select * from Customers (nolock)

Where yada yada yada

If you are not, one query is locking the table until the connection closes.

This is how it works:

1. Query1 --> Select * from Customers

Customers Table is locked until connection closes|||

Yes, I have no doubt that from a server standpoint there is no problem.

The question was more whether multiple queries running simultaneously from the same client machine may be a problem.

For example what if the ODBC driver queues the queries.

I read somewhere that depending on the provider some drivers may or may not support multithreading but have no ide whether it's true or if it has anything to do with my problem.

Thanks anyway,

|||

Adamus,

Good thought, it may be the cause of my problem.

I did try shooting the queries at different tables and it seemed to work fine.

a- Now, for the (nolock); I believe this is a SQL Server specific syntax, do you know if there's a generic way to issue a 'dirty read' through ODBC without being tied to the syntax of particular vendor/DB ?

Would starting a transaction from the ODBCConnection used for the command and set its isolationlevel to 'UncommitedRead' work ?

b- Assuming there's a way of doing a dirty read. Any suggestion as to how best handle the following ? :

- Do first select and get a list of values

- For each of the values issue a scalar command and get the result

- Once all the results are collected, do something with them and display the final result.

The trick of course is to create as many threads are scalar commands to be issued since it would improve perfs to run them in parallel. But it has to be done dynamically since we don't know how many we'll get. Then we need the ability to tie everything back together. Not exactly obvious to do, at least to me.

Thanks,

|||

ODBC? Kind of unfamiliar ground for me unfortunately. (I'm more of a SQL guy)The first thing that comes to mind is .LockType

...but good luck :)

Adamus

|||

.Locktype ? Where did you find that property ? On which class ?

I can't find it in the doc.

Thanks,

|||

LockType = adLockOptimistic

google will give you some good material on this.

http://support.microsoft.com/default.aspx/kb/281998

Adamus

ODBC & Multi-threading

I'm working on a C# (2005) app that requires shooting a large number of queries via Odbc, wait until the last one is done and then compute an overall result.

Since the queries are not interdependent, I would like to get them to run concurrently on multiple threads.

Is it possible at all (for example, does the Odbc driver support being used that way)

Any suggestion as to how to handle that problem would be appreciated.

Are you using (nolocks) on your tables?

Select * from Customers (nolock)

Where yada yada yada

If you are not, one query is locking the table until the connection closes.

This is how it works:

1. Query1 --> Select * from Customers

Customers Table is locked until connection closes|||

Yes, I have no doubt that from a server standpoint there is no problem.

The question was more whether multiple queries running simultaneously from the same client machine may be a problem.

For example what if the ODBC driver queues the queries.

I read somewhere that depending on the provider some drivers may or may not support multithreading but have no ide whether it's true or if it has anything to do with my problem.

Thanks anyway,

|||

Adamus,

Good thought, it may be the cause of my problem.

I did try shooting the queries at different tables and it seemed to work fine.

Would starting a transaction from the ODBCConnection used for the command and set its isolationlevel to 'UncommitedRead' work ?

b- Assuming there's a way of doing a dirty read. Any suggestion as to how best handle the following ? :

- Do first select and get a list of values

- For each of the values issue a scalar command and get the result

- Once all the results are collected, do something with them and display the final result.

Thanks,

|||

ODBC? Kind of unfamiliar ground for me unfortunately. (I'm more of a SQL guy)The first thing that comes to mind is .LockType

...but good luck :)

Adamus

|||

.Locktype ? Where did you find that property ? On which class ?

I can't find it in the doc.

Thanks,

|||

LockType = adLockOptimistic

google will give you some good material on this.

http://support.microsoft.com/default.aspx/kb/281998

Adamus

Friday, March 30, 2012

ODBC DSN for SQL Server via script

Monday, March 26, 2012

ODBC data source

ODBC Connectivity Issues

ODBC Connections

Friday, March 23, 2012

ODBC connection to SQL server failing

ODBC Connection to SQL

ODBC connection prompting for 'sa' password

ODBC connection problems in SSIS

Wednesday, March 21, 2012

ODBC Connection failure

ODBC connection failure

ODBC connection failure

ODBC connection failure

ODBC Connect from MS Access to SQL Server 2000-Viable?

Monday, March 19, 2012

ODBC Connect Failed in SQL Job

ODBC cannot connect to MSDE 2000a

ODBC Call Fail/Record locked

Monday, March 12, 2012

ODBC Access Link to SS2K5-Cannot update new records.

ODBC Access Link to SS2K5-Cannot update new records.

ODBC & Multi-threading

ODBC & Multi-threading

Objects related to DB

Blog Archive

About Me