ODBC Connection Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ODBC Connection Issue

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-15 : 16:24:19

Dear all,

This issue has frustrated me for 2 days.

We had 2 remote servers, each with sql server 2000 backend.

We were able to set up an ODBC data source on one server so our ASP code sitting on this same server can point to the other db sitting on another server.

This has worked for over 3 years until yesterday when we started getting the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

The error occurs when you click to Add a record.

This record is added to the db where where the ASP script resides. Let's call this server1.

However, the records to be added to server1 are retrieved from the db on server1.

As stated, this has worked now for over 3 years until when the db on server2 was upgraded from sql2000 to sql2005.

Suddenly, this error started occuring.

When I copy the ASP code that points to server2 to query analyze on server1, it works greate.

However, this error occurs when I run the asp code from the browser.

I have gone to sql server2005 on server2 and configured Surface Area to accept both local and remote connects as well as to accept either named pipes or tcp/ip.

so far, I am still getting the error.

Can someone please help?

Many thanks in advance

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-15 : 17:16:18

could you paste your connection string ? or check this www.connectionstrings.com

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-15 : 21:53:45

hi and thanks for your response.

I don't believe connection string has anything to do with it because of the error message that I am getting but just to show you,

I have:

Set Conn = Server.CreateObject("ADODB.Connection")

then codes

Conn.open session("erif1")
set rscnt = Conn.execute(sql)

the erif1 is the dsn name that I created using ODBC.

My best gut feeling about this is that something is different with the provider you connect to sql server2005 with versus the one you connect to sql server2000 with but I am just lost as to what it could be.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-15 : 23:00:14

quote:
Originally posted by simflex

My best gut feeling about this is that something is different with the provider you connect to sql server2005 with versus the one you connect to sql server2000 with but I am just lost as to what it could be.

Still did not put your connection string. See that website for connection to sql 2005

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 09:46:32

hi Afrika,
you said:
Still did not put your connection string

But I did, here it it again:

Set Conn = Server.CreateObject("ADODB.Connection")

then codes

Conn.open session("erif1")
set rscnt = Conn.execute(sql)

As I stated earlier, I set up an odbc data source.

I went to Control Panel -> Administrative Tools -> Data Sources (ODBC).

I went in there and created a DSN (data source name) and called it erif1

So, the ASP code is the way I am calling that dsn.

I am looking the link you sent now and I will report back.

Thanks for all your help.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 10:10:48

That is definitely not a connection string. But a DSN (Data source name) for a local computer, so when you upload to the remote computer, it searches for the DSN on that computer, whcih does not exist.

A DSN works with the local computer you created it in.

Try


dim simflex

simflex ="Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "

Dim users


Set Conn = Server.CreateObject("ADODB.Recordset")
users.ActiveConnection = simflex
users.Source = "select statement goes here"

'rest of the code goes here

ALSO WORTHY OF NOTE

1. Dont use session variables, as it will only weigh down your server and when it times out, or the server restarts the connection will fail

2. Dont declare your connection string in all the pages, that need a db connectin, but in a central location.

Since you are using classic ASP. You can use SSI to reference them.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 10:13:18

forgot to add, if you declare your connection string in all you pages. As adviced in option 2 above...

When you move your database or need to change your connection parameters, the headache of changing the connection string will be like, searching for mouse traps in an old dark house.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 10:48:30

hi again Afrika,

I appreciate your true knowledge but what you have not explained to me is that if I am using is for local machine, why has it worked for almost 4 years now?

This problem started because:

A) our network team relocated the server that the app points to and changed the ip address,

B) because our db admin upgraded sql server to 2005 from 2000.

Nothing has changed in this code.

So, I am really confused when you said "local machine".

I totally agree with you about opening connections on every page and also true about using SSI and I will look to make these changes in the next day or so.

This is someone's app and I have been maintaining it now since the guy left 2 years ago.

This is first such issue we have experienced and it is taking me 3 days to resolve.

Also, can that connection string be rewritten in such that I don't have to use these bits:

users.ActiveConnection = simflex
users.Source = "select statement goes here"??

Thanks again for all your help and efforts.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 10:52:03

No you dont have to use those bits. I just added them.

If you want to use a DSN its totally ok. But you have to setup the DSN on the remote machine.

Try re creating the DSN, if thats what you want to use. But bear in mind, thats local the the machine in question.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 10:53:59

quote:
Originally posted by simflex

hi again Afrika,

I appreciate your true knowledge

LOL

Am here like you to learn from real world experiences. From others

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 10:55:37

If you still cant connect, let me know and I can write new scripts for you.

I have loads of code snippets, that has worked previously for me in past projects.

But dont use a session variable to hold your connection string

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 10:56:23

one more point of interest, when I remove the session as you suggested, it reveals a different error message. Please have a look:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Also, I would like to use the connection string you proposed. I was just wandering if it could be written in a way that doesn't require dimming and usering another var like users.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 11:00:28

Yes, in VB script you dont have to declare your variable. Just use it without the dim

Replace, the values in red with your server values

simflex ="Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 11:07:24

All I am trying to say is that why can't it just work like the following:

Set Conn = Server.CreateObject("ADODB.Recordset")
Conn.Open "Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "

without the need for these:

users.ActiveConnection = simflex
of course I replace those in red with actual values.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 11:09:22

Just for starters, it didn't work anyway.

I got this:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 11:15:40

Try


Set Conn = Server.CreateObject("ADODB.Connection")
simflex ="Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "

Conn.open(simflex)
set rscnt = Conn.execute(sql)

REPLACE THE VALUES ABOVE with your values

If it doesnt work, Then i will write custom codes from scratch to fit your needs.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 11:26:28

Thank you very much for all your help.

My gut feeling tells me it has to do with the way sql2005 was set up.

I will need to contact the db guy to see if he can figure out something from the sql2005 end.

The last try didn't work either.

It says [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Your efforts are much appreciated.

Take care

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 11:31:58

Dont give up, that error says you did not use the correct DSN.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-16 : 11:34:12

Thanks for that word of encouragement.

If that was the case, why should dsn fit into this connection string?

simflex ="Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "

Conn.open(simflex)

I know I have to change the dbm uid, pwd and servename values.

How should dsn be used here? Is it even needed given the connection string?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 11:36:40

Paste your full EDITED code. Without your server details

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-16 : 11:37:23

The above error is a DSN error, the script i gave you is a connection string. You have to use one. If you want to use a DSN, configure that on your remote server. If not stick to mine.

Paste your full code here. As many things are missing from the above. also

as the value of the variable "sql" in set rscnt = Conn.execute(sql) is missing.

1. Set Conn = Server.CreateObject("ADODB.Connection")
2. simflex ="Driver={SQL Native Client};Server=YourServername;Database=yourDatabase;Uid=username;Pwd=password; "
3.
4. Conn.open(simflex)
5. set rscnt = Conn.execute(sql)

Try and see if you can ping the DB Ip or connect via telnet. If so, its your script and not the db

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources