I am trying to make an ODBC connection to a remote SQL Server 2000 server
over TCP/IP. I get a "dialog failed" error when I call SQLDriverConnect()
with the following syntax:

"DRIVER={SQL
Server};SERVER=192.168.0.2;DATABASE=Fred;Trusted_C onnection=Yes"

can anyone tell me the syntax error?

The IP address is actually the address of my local machine and the following
(non-TCP/IP) connestion string deos work.

"DRIVER={SQL Server};SERVER=(local);DATABASE=MCS
Logger;Trusted_Connection=Yes"

Many thanks.

Try using the string with local for the server but add the
network library parameter to specify the network library to
use: "Network=DBMSSOCN;" for TCP/IP

-Sue


On Mon, 27 Oct 2003 14:40:42 -0600, "Andrew Chalk"
<achalk@XXXmagnacartasoftware.com> wrote:

>I am trying to make an ODBC connection to a remote SQL Server 2000 server
>over TCP/IP. I get a "dialog failed" error when I call SQLDriverConnect()
>with the following syntax:
>
>"DRIVER={SQL
>Server};SERVER=192.168.0.2;DATABASE=Fred;Trusted_ Connection=Yes"
>
>can anyone tell me the syntax error?
>
>The IP address is actually the address of my local machine and the following
>(non-TCP/IP) connestion string deos work.
>
>"DRIVER={SQL Server};SERVER=(local);DATABASE=MCS
>Logger;Trusted_Connection=Yes"
>
>Many thanks.
>

Many thanks, Sue. Turned out that works for my local app. to access the
database. However, when the app. is not on the SQL server machine my (.NET)
application gives a security exception to do with the database acccess. The
app. appears but no data gets accessed. I think it is something to do with
the SQL machine password/database password. Can you see how I should modify
the string to provide this data?

Here is how the connection string looks now:

"DRIVER={SQL
Server};SERVER=(local);NETWORK=dbmssocn;ADDRESS=19 2.168.0.6,1433;DATABASE=Fr
ed;Trusted_Connection=Yes"

Thanks!



"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:36lrpvkdd1nfgs52uua4bag14pin22s799@4ax.com...
> Try using the string with local for the server but add the
> network library parameter to specify the network library to
> use: "Network=DBMSSOCN;" for TCP/IP
>
> -Sue
>
>
> On Mon, 27 Oct 2003 14:40:42 -0600, "Andrew Chalk"
> <achalk@XXXmagnacartasoftware.com> wrote:
>
> >I am trying to make an ODBC connection to a remote SQL Server 2000 server
> >over TCP/IP. I get a "dialog failed" error when I call SQLDriverConnect()
> >with the following syntax:
> >
> >"DRIVER={SQL
> >Server};SERVER=192.168.0.2;DATABASE=Fred;Trusted_ Connection=Yes"
> >
> >can anyone tell me the syntax error?
> >
> >The IP address is actually the address of my local machine and the
following
> >(non-TCP/IP) connestion string deos work.
> >
> >"DRIVER={SQL Server};SERVER=(local);DATABASE=MCS
> >Logger;Trusted_Connection=Yes"
> >
> >Many thanks.
> >
>

If you are suppose to be using a login and password to
connect to this other SQL Server, then you need to remove
Trusted_Connection as this is for Windows Authentication.
For SQL Server logins or standard security use
UID=YourUserID and PWD=YourPassword.
Remove the Server=local if you are no longer connecting to a
server on your own PC - server should be the new server.
It's a good idea to learn what the pieces of the connection
string represent so you can make modifications and
adjustments as needed. You can find more information and
sample connection strings for different scenarios at:
http://www.able-consulting.com/ADO_Conn.htm

-Sue

On Tue, 28 Oct 2003 00:30:35 -0600, "Andrew Chalk"
<achalk@XXXmagnacartasoftware.com> wrote:

>Many thanks, Sue. Turned out that works for my local app. to access the
>database. However, when the app. is not on the SQL server machine my (.NET)
>application gives a security exception to do with the database acccess. The
>app. appears but no data gets accessed. I think it is something to do with
>the SQL machine password/database password. Can you see how I should modify
>the string to provide this data?
>
>Here is how the connection string looks now:
>
>"DRIVER={SQL
>Server};SERVER=(local);NETWORK=dbmssocn;ADDRESS=1 92.168.0.6,1433;DATABASE=Fr
>ed;Trusted_Connection=Yes"
>
>Thanks!
>
>
>
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>news:36lrpvkdd1nfgs52uua4bag14pin22s799@4ax.com.. .
>> Try using the string with local for the server but add the
>> network library parameter to specify the network library to
>> use: "Network=DBMSSOCN;" for TCP/IP
>>
>> -Sue
>>
>>
>> On Mon, 27 Oct 2003 14:40:42 -0600, "Andrew Chalk"
>> <achalk@XXXmagnacartasoftware.com> wrote:
>>
>> >I am trying to make an ODBC connection to a remote SQL Server 2000 server
>> >over TCP/IP. I get a "dialog failed" error when I call SQLDriverConnect()
>> >with the following syntax:
>> >
>> >"DRIVER={SQL
>> >Server};SERVER=192.168.0.2;DATABASE=Fred;Trusted_ Connection=Yes"
>> >
>> >can anyone tell me the syntax error?
>> >
>> >The IP address is actually the address of my local machine and the
>following
>> >(non-TCP/IP) connestion string deos work.
>> >
>> >"DRIVER={SQL Server};SERVER=(local);DATABASE=MCS
>> >Logger;Trusted_Connection=Yes"
>> >
>> >Many thanks.
>> >
>>
>