Connet to SQL using VB6 forms via Internet

We have one system that is developed using Visual Basic 6. The system connects to MS SQL database using ODBC configuration. We have another branch that has to access the same database. We've got an Internet connection with a speed of 2MB, as we tried to connect using Virtual Private Network (VPN), but the problem we faced that the system became very slow over the Internet, its taking half an hour to open one form. Is there any way to connect to the database and via the Internet using VB6 Forms? Or its not useful as its not a web based tool.

Hi,

it′s hard for us to tell where the bottleneck in your application design is. But as of your description the application is not designed for slow connections to the database. To find out exactly what your problem is, you will either have to through the code to see which data is requested from the server, or let the profiler runn to see where the latency is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Connections to SQL Server

I have been developing an Intranet for the past few months, and now that I
have developed about 4 apps, I am questioning my connection string. We have
a Windows 2000 Server with SQL Server 2000 and are using Integrated Auth. so
that people don't have to login to the sql server.
Here is my current connection string,
MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data
Source=swvtc06;Initial Catalog=CliCore;"
As you can see I am using Trusted Connection, which is the only way that I
could get this connection to work. If I remove the trusted connection info,
it doesn't work at all.
How should I go about setting this up? Why is using trusted connection such
a bad idea? It seems to work, if someone doesn't have permission, it
doesn't let them into the db. And, what are the other ways of doing this?
Thanks,
Drew LaingHi Drew,
Your connection should read as follows:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False
I use this string in a delphi ado connection component to set up a trusted c
onnection to my database server.
Regards,
Ruud Aalders

quote:

---

Originally posted by Drew
I have been developing an Intranet for the past few months, and now that I
have developed about 4 apps, I am questioning my connection string. We have
a Windows 2000 Server with SQL Server 2000 and are using Integrated Auth. so
that people don't have to login to the sql server.
Here is my current connection string,
MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data
Source=swvtc06;Initial Catalog=CliCore;"
As you can see I am using Trusted Connection, which is the only way that I
could get this connection to work. If I remove the trusted connection info,
it doesn't work at all.
How should I go about setting this up? Why is using trusted connection such
a bad idea? It seems to work, if someone doesn't have permission, it
doesn't let them into the db. And, what are the other ways of doing this?
Thanks,
Drew Laing

---

Connections to SQL Server

I have been developing an Intranet for the past few months, and now that I
have developed about 4 apps, I am questioning my connection string. We have
a Windows 2000 Server with SQL Server 2000 and are using Integrated Auth. so
that people don't have to login to the sql server.
Here is my current connection string,
MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data
Source=swvtc06;Initial Catalog=CliCore;"
As you can see I am using Trusted Connection, which is the only way that I
could get this connection to work. If I remove the trusted connection info,
it doesn't work at all.
How should I go about setting this up? Why is using trusted connection such
a bad idea? It seems to work, if someone doesn't have permission, it
doesn't let them into the db. And, what are the other ways of doing this?
Thanks,
Drew LaingHi Drew,
Your connection should read as follows:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Securit
Info=False
I use this string in a delphi ado connection component to set up
trusted connection to my database server.
Regards,
Ruud Aalders
Drew wrote:
> *I have been developing an Intranet for the past few months, and no
> that I
> have developed about 4 apps, I am questioning my connection string.
> We have
> a Windows 2000 Server with SQL Server 2000 and are using Integrate
> Auth. so
> that people don't have to login to the sql server.
> Here is my current connection string,
> MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data
> Source=swvtc06;Initial Catalog=CliCore;"
> As you can see I am using Trusted Connection, which is the only wa
> that I
> could get this connection to work. If I remove the truste
> connection info,
> it doesn't work at all.
> How should I go about setting this up? Why is using truste
> connection such
> a bad idea? It seems to work, if someone doesn't have permission
> it
> doesn't let them into the db. And, what are the other ways of doin
> this?
> Thanks,
> Drew Laing
-
Ruud Aalder
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message462636.htm

Connections to SQL Server

I have been developing an Intranet for the past few months, and now that I
have developed about 4 apps, I am questioning my connection string. We have
a Windows 2000 Server with SQL Server 2000 and are using Integrated Auth. so
that people don't have to login to the sql server.
Here is my current connection string,
MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Da ta
Source=swvtc06;Initial Catalog=CliCore;"
As you can see I am using Trusted Connection, which is the only way that I
could get this connection to work. If I remove the trusted connection info,
it doesn't work at all.
How should I go about setting this up? Why is using trusted connection such
a bad idea? It seems to work, if someone doesn't have permission, it
doesn't let them into the db. And, what are the other ways of doing this?
Thanks,
Drew Laing
Hi Drew,
Your connection should read as follows:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False
I use this string in a delphi ado connection component to set up a
trusted connection to my database server.
Regards,
Ruud Aalders
Drew wrote:
> *I have been developing an Intranet for the past few months, and now
> that I
> have developed about 4 apps, I am questioning my connection string.
> We have
> a Windows 2000 Server with SQL Server 2000 and are using Integrated
> Auth. so
> that people don't have to login to the sql server.
> Here is my current connection string,
> MM_CliCore_STRING = "Provider=SQLOLEDB.1;Trusted_Connection=Yes;Da ta
> Source=swvtc06;Initial Catalog=CliCore;"
> As you can see I am using Trusted Connection, which is the only way
> that I
> could get this connection to work. If I remove the trusted
> connection info,
> it doesn't work at all.
> How should I go about setting this up? Why is using trusted
> connection such
> a bad idea? It seems to work, if someone doesn't have permission,
> it
> doesn't let them into the db. And, what are the other ways of doing
> this?
> Thanks,
> Drew Laing *
Ruud Aalders
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message462636.html
sqlsql

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decid
e
> sql server connection strategy. The application uses by 15-20 Windows clie
nt
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is sh
ut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:[vbcol=seagreen]
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the sing
le
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SADIRODLU" wrote:
>
Thanks John for all answers.

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decide
> sql server connection strategy. The application uses by 15-20 Windows client
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is shut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the single
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SAÃ?IROÃ?LU" wrote:
>> Hi Folks,
>> I have a question. I've developed a desktop application. But i can't decide
>> sql server connection strategy. The application uses by 15-20 Windows client
>> and it connects to same sql server.
>> What am i do ?
>> 1- I can open connection start of the program and close when program is shut
>> down.
>> 2- I can open connection when i need a connection and i can close
>> connection when i executed sql statements.
>> Do you have any idea?
>> Thanks.
>>
Thanks John for all answers. :)

Connection Trusted with Windows98

Hi everybody,
we just developed a standalone application using .NET+ C# + MSDE.
So, we're working to get a setup installation in order to deploy our
application for Windows2000/NT/XP/98 Opertaing System.
At the moment we're making some test on W/98 machine but we've got some
problems:
MSDE has been installed with flag SECURITYMODE=SQL allowing connection
trusted. In fact, after installation by W/XP we're able to connect to db
with OSQL command with -E option and with our application specifying
Integrated Security=SSPI in ConnectionString.
We don't have the same behaviour on Windows98 Operating System.
In spite of the flag mentioned above in installation I can using a
connection trusted.
Any suggestion ?
Thanks in advance, Pierluigi.
hi Pierluigi,
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel messaggio
news:eguAq2PZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> we just developed a standalone application using .NET+ C# + MSDE.
> So, we're working to get a setup installation in order to deploy our
> application for Windows2000/NT/XP/98 Opertaing System.
> At the moment we're making some test on W/98 machine but we've got some
> problems:
> MSDE has been installed with flag SECURITYMODE=SQL allowing connection
> trusted. In fact, after installation by W/XP we're able to connect to db
> with OSQL command with -E option and with our application specifying
> Integrated Security=SSPI in ConnectionString.
> We don't have the same behaviour on Windows98 Operating System.
> In spite of the flag mentioned above in installation I can using a
> connection trusted.
> Any suggestion ?
> Thanks in advance, Pierluigi.
>
I don't know if I correctly undertand your question...
SECURITYMODE=SQL stands for supporting both trusted WinNT connections as SQL
Server authenticated connections...
trusted connections are available only when MSDE(SQL Server) is installed on
WinNT platform... Win9x boxes only allow standard SQL Server authenticated
connections
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
|||Yes Andrea,
you understood right and you gave me the right answer.
I'm sorry, but I don't realise that trusted connection concept is valid with
WinNT only (I suppose even for WinXP, of course).
So, it means if I want to have a application .net+msde who wants to run on
all MS Operating System including W/98 I MUST use a SQL autentication.
Right ?
We've to consider this fact on the setup routine and in the Connection
String specified inside the C# application.
Thanks so much, Pierluigi.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> ha scritto nel messaggio
news:2l59icF8qs72U1@.uni-berlin.de...
> hi Pierluigi,
> "Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel
messaggio
> news:eguAq2PZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I don't know if I correctly undertand your question...
> SECURITYMODE=SQL stands for supporting both trusted WinNT connections as
SQL
> Server authenticated connections...
> trusted connections are available only when MSDE(SQL Server) is installed
on
> WinNT platform... Win9x boxes only allow standard SQL Server authenticated
> connections
> --
> 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 Pierluigi,
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel messaggio
news:e9XFykQZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Yes Andrea,
> you understood right and you gave me the right answer.
> I'm sorry, but I don't realise that trusted connection concept is valid
with
> WinNT only (I suppose even for WinXP, of course).
> So, it means if I want to have a application .net+msde who wants to run on
> all MS Operating System including W/98 I MUST use a SQL autentication.
yep... MSDE must be installed on WinNT platform to take advantage of trusted
connections... the clients can be installed on all OS supported by .Net
thought...
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
|||Thanks again, Andrea.
Tomorrow we'll start working to use SQL autentication by our application,
supporting in this manner even W/98 platform.
So, we'll install MSDE on our instance with our user/pwd.
We'll use the parameters above in any ConnectionString on our application.
Have a good day, bye, Pierluigi.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> ha scritto nel messaggio
news:2l5c5dF8l8f7U1@.uni-berlin.de...
> hi Pierluigi,
> "Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel
messaggio[vbcol=seagreen]
> news:e9XFykQZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> with
on
> yep... MSDE must be installed on WinNT platform to take advantage of
trusted
> connections... the clients can be installed on all OS supported by .Net
> thought...
> --
> 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
>

Connection to SQL Server 2005 lost

We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?
Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?
|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:

> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>
|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||We upgraded to SNAC, but still experience the very same error. The text of
the error message reported is a little bit different, thought. It now says
"Named Pipes Provider: No process is on the other end of the pipe." We did
get rid of the error message when we changed the recordsets cursor from
server-side to client-side (CursorLocation = adUseClient).
"William Vaughn" wrote:

> Ah, I would STILL upgrade to SNAC. I agree that if the application is
> working and deployed it should not be disturbed, but yours is not working
> and SNAC solves any number of issues like this.
> --
> __________________________________________________ ________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
> __________________________________________________ __________________________________________
> "Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
> message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...
>
|||You are putting less work on the SQL Server by doing that and is generally a
good idea anyway. But you should also think about changing to TCP instead of
using named pipes.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:
|||It sounds like the server is rejecting the Open. Make sure that the server
configuration is not limiting the total number of connections in any way.
These might have been configured to comply with license or other artifical
constraints.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:

Connection to SQL Server 2005 lost

We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist
or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsis
t or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations
,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:

> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
________________________________________
__________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
________________________________________
____________________________________
________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>

connection strings

This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.

hi,

a long as no particular keywords for SQL Server 2005 are used (say, the unsupported "MARS_Connection=yes;", and the like), the connection string is quite similar.. please have a look at http://www.connectionstrings.com/ for samples..

regards

|||Also have a look at the driver, if you install the application on a non SQL System, the SNAC will probably not exist. YOu will either have to ship it within your application, or use a MADC driver which is installed on the most systems.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

connection strings

I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.

I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.

My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.

I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what???

Can't you just change the default schema of the user you use to login with through asp.net to the new schema?

|||

I'm not sure I understand what you are saying when you say change the schema of the user. I'm pretty sure the hosting account accesses via the user ASPNET via IIS and I also assume that Visual Web Developer 2005 accesses via my user account on the development machine, but I don't understand "user schema". Are we referring to access privileges?

|||

No I'm talking about the schema. When you say that you have to write owner.tablename to access a table that means that that table is in the schema called owner, which is also the default schema for the user with the same name. Normally the default schema for most database objects is dbo. So when you write a select statement like "select * from sometable" it's really the same as writing "select * from dbo.sometable". In sql server you have the option to change the default schema from dbo to something else. Say that all the database objects you want to work on are in a schema called "aspnet". If the default schema of the user you want to access these objects with is not "aspnet", you would have to write all your queries like "select * from aspnet.sometable". However if you change the default schema for that user to "aspnet" you can now write "select * from sometable" with the same result. The way to change schema is to open the properties sheet for the user and change the default schema. It's right there on the main page.

Hope it helps

|||

I tried

CREATE SCHEMA AUTHORIZATION usernamefromisp

GRANT ALL TO usernamefromisp

then changed to point to the restored production database and got the same error message that objects (tables) do not exist.

|||

Hi,

From your description, it seems that you are not able to select the tables in your database which restored from a backup file, right?

As you have mentioned, if you added the database owner in your select statement, it works well. So the cause of the problem is that the database(all datatables) owner is not dbo which is the default owner in your database. So while you are writing select statements without database owner, the system regard it as dbo.Object by default.

In your case, the system can't find the dbo.YourObject since the owner of YourObject is other than dbo. Now you have two workarounds, first, just write the select statement with the object owner. The second way is to change the object owner to dbo.

For how to change, you may refer the following link:
http://msdn2.microsoft.com/en-us/library/ms190909.aspx

Thanks.

|||

Thanks,

Your reply didn't quite fix the problem, but put me on the right track. I didn't give you enough definition because it was not just the database ownership at issue, but also many of the individual tables within the database. I used the sp_changeobjectowner on each of them and the problem cleared.

Thank you for your help.

Connection Problems to Oracle Database

Hi,

I have a package connecting to oracle database ,this was developed on 32 bit Windows OS now i moved the same to 64 bit OS and it fails to connect to oracle database.

I have created system DSN and tested it was successfull.

In my package i am using ado.net connection object ,I am using ODBC data provider.I select the Data source in the drop down .provide user name and password and when i hit on test connection it fails with the following message

Test connection failed because of error in initilizing the provider error[08004]

[oracle][odbc][ora]ORA 12154 : TNS could not resolve the connect identifier specified

[Error]IM006[Microsoft][ODBC driver manager] drivers SQL setconnectattr failed

Any help on this will be appreciated

Note:- even though the OS is 64 bit i am using 32 bit ODBC as BI studio can only access 32 bit odbc

32-bit ODBC drivers won't run with 64-bit SSIS. You either need a 64-bit ODBC driver for Oracle (not sure if one exists) or run the 32-bit version of SSIS on the 64-bit box.|||Initially i had installed 64 bit oracle drivers and had created sytem dsn in 64 bit odbc but when i tried accesing the DSN(from drop down in connection object) i created in ado.net connection object i was not able to do so , as using the BI studio you can only access 32 bit odbc for which i had to install 32 bit oracle drivers after creating the dsn in 32 bit odbc i was able to access the DSN but when i did the test connection it failed.with above error message.

connection problem to sql 2000 on windows 2003 from Windows 95

Hi all,
I have a Windows 2003 server, which is also a terminal server for
application, with sql 2000 installed. My company has developed an
application that uses SQL 2000 as its database. The application is a
client/server one. In each client computer there's a link to the
application on the server. There is no problem with Windows 98,
Windows 2000 pro, Windows xp pro clients, but the windows 95 ones
cannot log in to the database. The log of the application shows the
following error:
connection error -2147467259. Cannot open database requested in login
'database name'. Login fails.
Till a week ago the application was running on a Windows 2000 server
with SQL 2000 install and the W95 clients had no problem connecting to
the database, so my guess is the error has something to do with
Windows 2003 server, but what'causing the error?
I tried to install a newer version of MDAC (MDAC 2.5, the last version
of MDAC you can install on W95)but with no success. By the way W95
clients have no problem accessing shared folder on the Windows 2003
server.
Any idea?
Thanks
Marino[posted and mailed, please reply in news]

Marino (mmagi@.itconsult.it) writes:
> I have a Windows 2003 server, which is also a terminal server for
> application, with sql 2000 installed. My company has developed an
> application that uses SQL 2000 as its database. The application is a
> client/server one. In each client computer there's a link to the
> application on the server. There is no problem with Windows 98,
> Windows 2000 pro, Windows xp pro clients, but the windows 95 ones
> cannot log in to the database. The log of the application shows the
> following error:
> connection error -2147467259. Cannot open database requested in login
> 'database name'. Login fails.
> Till a week ago the application was running on a Windows 2000 server
> with SQL 2000 install and the W95 clients had no problem connecting to
> the database, so my guess is the error has something to do with
> Windows 2003 server, but what'causing the error?

That particular error message means that the logins have a default
database that does not exist on the server, or which they have not
been granted access to.

Are the W95 clients using SQL authentication or Windows authentication?
I would assume that SQL authentication is the only thing that works.

Review how the Win95 clients access the server, and then do an
sp_helplogins on the logins they use. Then check DefDbName, whether
this database exists, or if they have been granted access. Use
sp_defaultdb to change their default db if necessary. (sp_defaultdb
may be called something else; use Books Online to find out.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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