How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. Thanks
Hi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks
|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks
|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
[vbcol=seagreen]
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
>
> "NS" wrote:
|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC for
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks
|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
[vbcol=seagreen]
> "Chandra" wrote:
ODBC for[vbcol=seagreen]
the[vbcol=seagreen]
login[vbcol=seagreen]
Showing posts with label vb6. Show all posts
Showing posts with label vb6. Show all posts
Monday, March 12, 2012
ODBC, VB, SQL Authentication
How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC f
or
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
[vbcol=seagreen]
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
>|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC fo
r
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
[vbcol=seagreen]
> "Chandra" wrote:
>
ODBC for[vbcol=seagreen]
the[vbcol=seagreen]
login[vbcol=seagreen]
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC f
or
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
[vbcol=seagreen]
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
>|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC fo
r
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
[vbcol=seagreen]
> "Chandra" wrote:
>
ODBC for[vbcol=seagreen]
the[vbcol=seagreen]
login[vbcol=seagreen]
ODBC, VB, SQL Authentication
How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
> > How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> > SQL using SQL Authentication. Is there a way that I do not hard code the
> > username and password in the VB program? I wouldn't like to have login
> > screen to ask for the user's credentials. Thanks|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC for
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
> "Chandra" wrote:
> > Hi NS
> >
> > You can very well prompt users to key in user name and pwd instead of
> > hardcoding. Infact it is the best technique to do that.
> >
> > do not provide username and pwd in the connection string, try to pass
> > parameters that were entered in the popped up text box.
> >
> > please let me know if u have any questions
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://www.SQLResource.com/
> > ---
> >
> >
> >
> > "NS" wrote:
> >
> > > How do I connect to SQL Server using VB6? I will be setting up the
ODBC for
> > > SQL using SQL Authentication. Is there a way that I do not hard code
the
> > > username and password in the VB program? I wouldn't like to have
login
> > > screen to ask for the user's credentials. Thanks
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
> > How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> > SQL using SQL Authentication. Is there a way that I do not hard code the
> > username and password in the VB program? I wouldn't like to have login
> > screen to ask for the user's credentials. Thanks|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC for
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
> "Chandra" wrote:
> > Hi NS
> >
> > You can very well prompt users to key in user name and pwd instead of
> > hardcoding. Infact it is the best technique to do that.
> >
> > do not provide username and pwd in the connection string, try to pass
> > parameters that were entered in the popped up text box.
> >
> > please let me know if u have any questions
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://www.SQLResource.com/
> > ---
> >
> >
> >
> > "NS" wrote:
> >
> > > How do I connect to SQL Server using VB6? I will be setting up the
ODBC for
> > > SQL using SQL Authentication. Is there a way that I do not hard code
the
> > > username and password in the VB program? I wouldn't like to have
login
> > > screen to ask for the user's credentials. Thanks
ODBC Write Failures
I've got an odd occasonal problem when writing to our server. In two
different applications, one an access app, and the other VB6 we will
experience a condition where we are able to read data, but when we attempt
to save the data we get ODBC timeouts. When this occurs, I can "fix" the
problem by going into one of the tables of the database in question and
modifying any value.
Configuration is a dual Xeon with 2gb of memory running SQL Server 2000.
Both databases are on this system and are accessed via different DSNs. One
is a web app, and is accessed by com dlls, the other is an app that was
created in MS Access and then modified so that it gets its data from linked
tables.
Any insite, or even better a good description of troublshooting techniques
would be appreciated.
Sam RobinsonHi Sam,
According to your description, I am not sure what your problem is. I would
like you to provide the following information so that I can narrow down
this issue.
1. Please provide me with the detailed error message. To do this, you can
take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
additional information regarding how to obtain the screen shot, please
review [Item 1].
2. How did you read and write data with Access to SQL Server, via a linked
table or programming? Can you describe it in detailed?
3. I would like to know when the error occurred. It seems that the error
occurred when you updated the table on SQL Server. Did the error message
occur when you connected to SQL Server?
4. When the error occurs again, please perform the problematic SQL
statements using Query Analyzer. Does the same error message occur again?
It helps us to identify the problem related to the SQL Server including SQL
statements and your VB code.
5. Please provide the SQL Server error log (usually under C:\Program
Files\Microsoft SQL Server\MSSQL\LOG)
On the SQL Server side, there is a query wait option. This query wait
option helps us specify the time in seconds (from 0 through 2147483647)
that a query waits for resources before timing out. We can increase the
value of this option trying to avoid the timeout error message. For
additional information regarding this query wait option, please refer to
the following article on SQL Server Books Online.
Topic: "query wait Option"
Also, such issues occurring randomly tend to be complex and take up
extensive research time. I'd like to set your expectations that it may take
a while for us to help you narrow down the problem and we may eventually
redirect you to PSS to continue working with a dedicated Support
Professional. If this is critical, I'd recommend contacting PSS and opening
a support incident troubleshoot this further. If you need any help in this
regard, please let me know.
[Item 1]
Try to obtain the screen shot of the error message.
When the error occurred please perform the following steps to capture the
screen shot of the error message.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start 'All programs 'Accessories ' Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Thank you for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:2tLJaornDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> According to your description, I am not sure what your problem is. I would
> like you to provide the following information so that I can narrow down
> this issue.
> 1. Please provide me with the detailed error message. To do this, you can
> take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
> additional information regarding how to obtain the screen shot, please
> review [Item 1].
>
I assume that you're referring to the error message that is being seen in
access. These are not terribly informative, and do not provide error
numbers. The errors being returned to the web app (which seems to have
somewhat better error handling) are timeouts.
> 2. How did you read and write data with Access to SQL Server, via a
linked
> table or programming? Can you describe it in detailed?
>
Access is using linked tables. These were created using the standard table
linking wizard from the file menu.
> 3. I would like to know when the error occurred. It seems that the error
> occurred when you updated the table on SQL Server. Did the error message
> occur when you connected to SQL Server?
>
No, it didn't. The applications are still able to read data when this
happens, which is what has me foxed. The connection seems to be almost
healthy.
> 4. When the error occurs again, please perform the problematic SQL
> statements using Query Analyzer. Does the same error message occur again?
> It helps us to identify the problem related to the SQL Server including
SQL
> statements and your VB code.
>
Unfortunately, the same SQL statements succeed more often than not.
> 5. Please provide the SQL Server error log (usually under C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG)
>
I can certainly do this, but what I'm seeing there is just that the backups
ran correctly.
> On the SQL Server side, there is a query wait option. This query wait
> option helps us specify the time in seconds (from 0 through 2147483647)
> that a query waits for resources before timing out. We can increase the
> value of this option trying to avoid the timeout error message. For
> additional information regarding this query wait option, please refer to
> the following article on SQL Server Books Online.
> Topic: "query wait Option"
> Also, such issues occurring randomly tend to be complex and take up
> extensive research time. I'd like to set your expectations that it may
take
> a while for us to help you narrow down the problem and we may eventually
> redirect you to PSS to continue working with a dedicated Support
> Professional. If this is critical, I'd recommend contacting PSS and
opening
> a support incident troubleshoot this further. If you need any help in this
> regard, please let me know.
>
I did find that the transaction logs had grown to truly monumental sizes (18
and 15 gb respectively) I cleaned that up last night, and am watching the
system. Does this seem like a likely culprit? I know that the log is broken
into virtual segments, but if the segment count gets very high can this
cause long lookup/write times that might impact external timers?
I can certainly appreciate your comments regarding intermittent problems and
how difficult they can be to resolve... or even to know if they're resolved.
I'm mostly looking for an approach to troubleshooting, and your posting has
given me several ideas.
Thanks for the help. As I see the problem (or don't as I'm hoping for!) I'll
post more information.
Sam Robinson|||Hi Sam,
Thanks for your feedback. Based on my experience, when server is busy, such
as performing many jobs on the background, the timeout error will occur.
According to your description with the monumental sizes of transaction
logs, it is possible that there are many jobs working on the background,
such as backup jobs, which impact the performance of the server. Because
the error occurs randomly, it is hard to us to find out the root cause of
this problem. When the error occurs again on your side, please feel free to
post in the newsgroup, I will glad to work with you continously. Of course,
I also hope it do not occur again.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:%239I3130nDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> Thanks for your feedback. Based on my experience, when server is busy,
such
> as performing many jobs on the background, the timeout error will occur.
> According to your description with the monumental sizes of transaction
> logs, it is possible that there are many jobs working on the background,
> such as backup jobs, which impact the performance of the server. Because
> the error occurs randomly, it is hard to us to find out the root cause of
> this problem. When the error occurs again on your side, please feel free
to
> post in the newsgroup, I will glad to work with you continously. Of
course,
> I also hope it do not occur again.
> Thanks for using MSDN newsgroup.
>
Michael, et al
Well, it's been a week now and I think it's safe to say that truncating and
setting up a backup maintenance plan has resolved the problem. My
speculation is that the problem came from the necessity to log the
transaction in the increadably huge transaction log. This would seem to
follow as we didn't get a timeout when reading, only when trying to commit a
record.
Wow. Given the size the logs had gotten, I'm amazed that the thing was
working as well as it was.
Thanks for your help!
Sam Robinson
different applications, one an access app, and the other VB6 we will
experience a condition where we are able to read data, but when we attempt
to save the data we get ODBC timeouts. When this occurs, I can "fix" the
problem by going into one of the tables of the database in question and
modifying any value.
Configuration is a dual Xeon with 2gb of memory running SQL Server 2000.
Both databases are on this system and are accessed via different DSNs. One
is a web app, and is accessed by com dlls, the other is an app that was
created in MS Access and then modified so that it gets its data from linked
tables.
Any insite, or even better a good description of troublshooting techniques
would be appreciated.
Sam RobinsonHi Sam,
According to your description, I am not sure what your problem is. I would
like you to provide the following information so that I can narrow down
this issue.
1. Please provide me with the detailed error message. To do this, you can
take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
additional information regarding how to obtain the screen shot, please
review [Item 1].
2. How did you read and write data with Access to SQL Server, via a linked
table or programming? Can you describe it in detailed?
3. I would like to know when the error occurred. It seems that the error
occurred when you updated the table on SQL Server. Did the error message
occur when you connected to SQL Server?
4. When the error occurs again, please perform the problematic SQL
statements using Query Analyzer. Does the same error message occur again?
It helps us to identify the problem related to the SQL Server including SQL
statements and your VB code.
5. Please provide the SQL Server error log (usually under C:\Program
Files\Microsoft SQL Server\MSSQL\LOG)
On the SQL Server side, there is a query wait option. This query wait
option helps us specify the time in seconds (from 0 through 2147483647)
that a query waits for resources before timing out. We can increase the
value of this option trying to avoid the timeout error message. For
additional information regarding this query wait option, please refer to
the following article on SQL Server Books Online.
Topic: "query wait Option"
Also, such issues occurring randomly tend to be complex and take up
extensive research time. I'd like to set your expectations that it may take
a while for us to help you narrow down the problem and we may eventually
redirect you to PSS to continue working with a dedicated Support
Professional. If this is critical, I'd recommend contacting PSS and opening
a support incident troubleshoot this further. If you need any help in this
regard, please let me know.
[Item 1]
Try to obtain the screen shot of the error message.
When the error occurred please perform the following steps to capture the
screen shot of the error message.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start 'All programs 'Accessories ' Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Thank you for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:2tLJaornDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> According to your description, I am not sure what your problem is. I would
> like you to provide the following information so that I can narrow down
> this issue.
> 1. Please provide me with the detailed error message. To do this, you can
> take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
> additional information regarding how to obtain the screen shot, please
> review [Item 1].
>
I assume that you're referring to the error message that is being seen in
access. These are not terribly informative, and do not provide error
numbers. The errors being returned to the web app (which seems to have
somewhat better error handling) are timeouts.
> 2. How did you read and write data with Access to SQL Server, via a
linked
> table or programming? Can you describe it in detailed?
>
Access is using linked tables. These were created using the standard table
linking wizard from the file menu.
> 3. I would like to know when the error occurred. It seems that the error
> occurred when you updated the table on SQL Server. Did the error message
> occur when you connected to SQL Server?
>
No, it didn't. The applications are still able to read data when this
happens, which is what has me foxed. The connection seems to be almost
healthy.
> 4. When the error occurs again, please perform the problematic SQL
> statements using Query Analyzer. Does the same error message occur again?
> It helps us to identify the problem related to the SQL Server including
SQL
> statements and your VB code.
>
Unfortunately, the same SQL statements succeed more often than not.
> 5. Please provide the SQL Server error log (usually under C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG)
>
I can certainly do this, but what I'm seeing there is just that the backups
ran correctly.
> On the SQL Server side, there is a query wait option. This query wait
> option helps us specify the time in seconds (from 0 through 2147483647)
> that a query waits for resources before timing out. We can increase the
> value of this option trying to avoid the timeout error message. For
> additional information regarding this query wait option, please refer to
> the following article on SQL Server Books Online.
> Topic: "query wait Option"
> Also, such issues occurring randomly tend to be complex and take up
> extensive research time. I'd like to set your expectations that it may
take
> a while for us to help you narrow down the problem and we may eventually
> redirect you to PSS to continue working with a dedicated Support
> Professional. If this is critical, I'd recommend contacting PSS and
opening
> a support incident troubleshoot this further. If you need any help in this
> regard, please let me know.
>
I did find that the transaction logs had grown to truly monumental sizes (18
and 15 gb respectively) I cleaned that up last night, and am watching the
system. Does this seem like a likely culprit? I know that the log is broken
into virtual segments, but if the segment count gets very high can this
cause long lookup/write times that might impact external timers?
I can certainly appreciate your comments regarding intermittent problems and
how difficult they can be to resolve... or even to know if they're resolved.
I'm mostly looking for an approach to troubleshooting, and your posting has
given me several ideas.
Thanks for the help. As I see the problem (or don't as I'm hoping for!) I'll
post more information.
Sam Robinson|||Hi Sam,
Thanks for your feedback. Based on my experience, when server is busy, such
as performing many jobs on the background, the timeout error will occur.
According to your description with the monumental sizes of transaction
logs, it is possible that there are many jobs working on the background,
such as backup jobs, which impact the performance of the server. Because
the error occurs randomly, it is hard to us to find out the root cause of
this problem. When the error occurs again on your side, please feel free to
post in the newsgroup, I will glad to work with you continously. Of course,
I also hope it do not occur again.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:%239I3130nDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> Thanks for your feedback. Based on my experience, when server is busy,
such
> as performing many jobs on the background, the timeout error will occur.
> According to your description with the monumental sizes of transaction
> logs, it is possible that there are many jobs working on the background,
> such as backup jobs, which impact the performance of the server. Because
> the error occurs randomly, it is hard to us to find out the root cause of
> this problem. When the error occurs again on your side, please feel free
to
> post in the newsgroup, I will glad to work with you continously. Of
course,
> I also hope it do not occur again.
> Thanks for using MSDN newsgroup.
>
Michael, et al
Well, it's been a week now and I think it's safe to say that truncating and
setting up a backup maintenance plan has resolved the problem. My
speculation is that the problem came from the necessity to log the
transaction in the increadably huge transaction log. This would seem to
follow as we didn't get a timeout when reading, only when trying to commit a
record.
Wow. Given the size the logs had gotten, I'm amazed that the thing was
working as well as it was.
Thanks for your help!
Sam Robinson
ODBC vs OLEDB driver for SQLserver
Hi:
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoonHi
Look at the following page:
http://msdn.microsoft.com/data/mdac/default.aspx?pull=/library/en-us/dnmdac/html/data_mdacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegroups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoonHi
Look at the following page:
http://msdn.microsoft.com/data/mdac/default.aspx?pull=/library/en-us/dnmdac/html/data_mdacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegroups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
ODBC vs OLEDB driver for SQLserver
Hi:
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoon
Hi
Look at the following page:
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegr oups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>
|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoon
Hi
Look at the following page:
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegr oups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>
|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
ODBC vs OLEDB driver for SQLserver
Hi:
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoonHi
Look at the following page:
http://msdn.microsoft.com/data/mdac...mdacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegroups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
Which is the preferable driver to connect to the latest SQLServer
(SQL2000) ?
I have an application written in vb6, some of the data entry screen
hang (but most of the time it work perfectly), we have to clear off all
the sql connection from Enterprise manager and relogin into the system
again.
Is it because the driver we used (ODBC driver) cause the problem ?
because another application which use OLEDB driver has no problem at
all.
Any idea ?
Thanks
JCVoonHi
Look at the following page:
http://msdn.microsoft.com/data/mdac...mdacroadmap.asp
OLE DB is the newer technology over ODBC.
If you screens hang, check for clocking when this is occurring (run sp_who2
in Query Analyzer) to see what is going on. You might have one user blocking
another user.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jcvoon" <jcvoon_99@.yahoo.com> wrote in message
news:1128133741.955485.315030@.o13g2000cwo.googlegroups.com...
> Hi:
> Which is the preferable driver to connect to the latest SQLServer
> (SQL2000) ?
> I have an application written in vb6, some of the data entry screen
> hang (but most of the time it work perfectly), we have to clear off all
> the sql connection from Enterprise manager and relogin into the system
> again.
> Is it because the driver we used (ODBC driver) cause the problem ?
> because another application which use OLEDB driver has no problem at
> all.
> Any idea ?
> Thanks
> JCVoon
>|||Mike Epprecht
Thanks for the reply.
I'm not SQL expert, can u please tell me when one user will block
another user ?
Regards
JCVoon
Monday, February 20, 2012
odbc index problem
Hi all,
Here is a brief description of a problem I encountered, and how I
found a work around after 3 long days.
Here is a brief description of a problem I encountered, and how I
found a work around after 3 long days.
I have a VB6 app that uses ADO and ODBC to get communicate with SQL
server 2000 (sp3, running in win2003).
Everything was running great for a few weeks, but one day an update
statement that used to work just stopped working. It was a simple
update of 1 field in a table (about 30 columns, about 20k records).
SQL server acutally hung while it waited for a response that never
came, and everyone else on the network was also locked out of sql
server. Everyone had to do a ctrl alt del to crash the programme.
Steps I took...
Rebuilt the database. Same.
Restored backup. Same.
Moved database to another server. Same.
Checked for viruses, that no updates had happened, memory checks and
so on.
Still no joy.
Someone then recommended tinkering with the indexes of the table in
question. Which I did.
I added indexes, removed them, and eventuall found a combination that
worked. And this is it.
I removed the primary key, and replaced it with a clustered index.
That's it. And now it all appears to be running OK.
WHY WHY WHY?
I just don't get it.
Have I found a bug in sql server or odbc or ado? Is it a known issue?
What is the impact of what I have done?
Thanks in advance for your comments, and I hope this solution saves
someone else 3 days of hell!
TimTim (thew@.ltons.freeserve.co.uk) writes:
> I have a VB6 app that uses ADO and ODBC to get communicate with SQL
> server 2000 (sp3, running in win2003).
You should be using the SQLOLEDB provider. The default provider, OLE DB
over ODBC may be good for data sources for which there is no targeted
OLE DB provider, but this is not the case for SQL Server. Not that this
has anything to do with the problem you are describing, but nevertheless
I like to point this out.
> Everything was running great for a few weeks, but one day an update
> statement that used to work just stopped working. It was a simple
> update of 1 field in a table (about 30 columns, about 20k records).
> SQL server acutally hung while it waited for a response that never
> came, and everyone else on the network was also locked out of sql
> server. Everyone had to do a ctrl alt del to crash the programme.
> Steps I took...
> Rebuilt the database. Same.
> Restored backup. Same.
> Moved database to another server. Same.
> Checked for viruses, that no updates had happened, memory checks and
> so on.
> Still no joy.
> Someone then recommended tinkering with the indexes of the table in
> question. Which I did.
> I added indexes, removed them, and eventuall found a combination that
> worked. And this is it.
> I removed the primary key, and replaced it with a clustered index.
> That's it. And now it all appears to be running OK.
> WHY WHY WHY?
> I just don't get it.
> Have I found a bug in sql server or odbc or ado? Is it a known issue?
> What is the impact of what I have done?
There is next to nothing of useful information to comment the actual
case, so I can only answer in general terms.
In general, one needs to understand there are few tools that are so
powerful to make things run really slow like a relational database engine.
All modern DBMS has a cost-based optimizer that seeks find the best
way to execute a query, and to make its decisions it uses some information
about the data. SQL Server maintains statistics about the data and how it
is distributed. Most queries can be executed in a number of ways, and
the optimizer tries to estimate the most effecient plan. Note that was
the best plan yesterday, may not be the best plan today, because data
has changed, for instance increased in size. While optimizers often do a
good job, they are estimates, and sometimes things can go seriously wrong.
So this could explain why your query worked fine one day and then was
out to lunch the next day.
Now, there is a whole lot of things you can do help the optimizer, and
the most important is to ensure that your tables properly indexed for
the queries you use. After all, if you are to access one single row in
a million-row tables without any index at all, there is no more effecient
plan than to scan all million rows.
So there is nothing magic going on here, and least of all of any bug.
And ODBC is completely innocent. If you submit your own UPDATE statement,
ADO is too. If you use the .Update method in ADO, which I recommend
against, ADO is the one that builds the UPDATE statement. But the major
area for concern is the database design. If one has no knowledge about
database design and no understanding about indexing, you are very likely
to run into performance problems sooner or later, as soon as you get any
volume in your database.
If you feel that you would like to learn something in this area,
attending a class on SQL Server performance may be a good idea.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)