SQL ODBC - Problem

Hi,

I am having some problem with the sql odbc- i have created a dsn on my lotus notes server
"SQLTEST". now i have a database on the same server where i have created an agent. in the agent i
am connecting to the sql database.the script is as below

Dim ws As New notesuiworkspace
Dim se As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim dc As notesdocumentcollection
Dim conn As New odbcconnection
flag=conn.ConnectTo("Proactiv","Notes","notes")
Msgbox conn.isconnected

now when i run the agent from my client machine i am getting msgbox result as False. and the
geterrormessage returns the below error message:

"[Microsoft][ODBC Driver Manager]Data Source name not found and no default driver specified
LS:DO-ODBC could not complete the requested operation."

When i create the dsn on my client machine it is working fine .

now what is the problem? is it that i have to access the odbc only from the client machine. i want to
have the dsn centralised and kept in the server such that i can access it from any workstation.

Is the problem related with the lotus script or something to do with the ODBC driver manager why i
am asking this because the error message states something like that.

Please help me with a solution immediately.

Thanks,
Gayathri.
SGayathriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HemanthaKumarCommented:
Your code looks perfect.

The culprit is ODBC Driver.

The ODBC drivers should be loaded on the client m/c and not on server. The ODBC calls will be executed only from the workstation. NOT ON THE SERVER.

That's why when ur executing the agent from the client's m/c it is searching for the datasource on the workstation and couldn't find and there goes our traditional ODBC error message.

Good Luck.
Hemanth
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AntonysinghCommented:
Hi G3,

If you want connect to the sql server from from web  , you should have dsn at server, if your running from local , u hould create dsn entry in your local machain not in the server. I don't think you can centralized from client.

Follow Hemanth instruction and try.

Regards,
Antony.
0
ghassan99Commented:
I dont know why Anita didnt believe me:)
This is why DSN is written for...to be on the client in the first place:)
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

SGayathriAuthor Commented:
Hi hemant,

thanks for ur answer.

Hi ghassan,
this is gayathri and not anitha .sometimes anitha uses my login to post questions.

ghassan and antony
actually i got confused with the server dsn that is used for web odbc with the workstation dsn. now i am clear that while accessing from web i can create the dsn on the domino server and while accessing from lotus notes we can create only the dsn on local machine.

am i right.

Thanks
Gayathri.
0
SGayathriAuthor Commented:
Hi hemant,

thanks for ur answer.

Hi ghassan,
this is gayathri and not anitha .sometimes anitha uses my login to post questions.

ghassan and antony
actually i got confused with the server dsn that is used for web odbc with the workstation dsn. now i am clear that while accessing from web i can create the dsn on the domino server and while accessing from lotus notes we can create only the dsn on local machine.

am i right.

Thanks
Gayathri.
0
ghassan99Commented:
yes u r...
sorry for confusing u with Anitha!:)
0
HemanthaKumarCommented:
You Bet !

Hemanth
0
AntonysinghCommented:
Hi G3,

We have used this so many placeses and i have sent to you mail regarding this. I hope now you are clear.


Regards,
Antony.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.