ASP, IIS4 and VB6, Project works on IIS in VB but not in DLL

[Maartin]

Somebody please help.

I'm about to hit the roof, I have an VB project that is accessed from an asp page on iis4 on nt4 sp6.

In dll form an odbc error occurs, I have tried using DAO, ADO and about to try RDO. All of these form in VB running on the iis box, but the moment that the dll is complied and used i get 'ODBC--call failed.; 3146' on the attampt to the database connection.

Does anybody have any ideas ?

Thanks.

[Ianpbaker]

Hi maartin

just a quick question, are you using a DSN to connect to the database?. If so, is the dsn set up on the web server?

Ian

[Maartin]

Yes for DAO I'm using a system dsn. But if there is no dsn even when using vb it will not work, but is works in VB but not in DLL form. for ADO you don't need a DSN at all, but even that does not make a difference.

Thanks for the help so far.

When using ADO have you made sure MDAC is installed on the server?

[Maartin]

Yes mdac_type version 2.5 is installed on the development station and on the server. Both has VB6 with SP4. The development station is Win2k and the server is Winnt 4 with sp6.

Thanks.

[Ianpbaker]

sorry, stupid question, I take it that the web server is on the same network as the SQL server

Ian

[Maartin]

Yes the web server ans MSSQL server is on the same network and in the same domain.

Thanks a lot.

[Looster]

Well the connection seems to be the problem ... I don't think this is a coding problem I see where Ianpbaker is heading try to check the connection id ODBC to the D-Base ... It might be worth the try.

aufWeidershen

[Maartin]

Ok, i get the idea. You guy's think it might be the ethrenet rule of the number of hops that might be causing the problem. I know that the pc with the database is about 4 hops away from the web server pc. I will try and place the two PC's on the same hub. Will let you guy's know what the outcome was of the test.

Thanks for all the help people.

[nialler]

At last somebody else who has come across this problem!!!

It is such a frustrating problem I don't know how we have not seen more of it!

Anyway, I was having a problem with my VB IIS apps working in design mode but not after compilation, I think this is the same probem that you are having. I contacted all the experts and was asked all the silly questions you were asked, none pointing at the solution:

Cursor locations, cursor types and lock types, I am not sure which exactly but a certain combination will cause the app to fail when runnng from from IIS, also make sure your DSN is a system one not user.

Check you cursor locations and stay away from disconnecting the recordset, it does not work.

This solved my problem and cured me of weeks of agony, let me know if it solves your problem.

Regards

[Maartin]

Hi

Well I followed nailler's advice and ran through all the cursor locations, cursor types, etc. nothing worked I narrowed it down to one line of code ...

***
Set dbMain = wrkMain.OpenDatabase("Alchemy", dbDriverComplete, False, "ODBC;DSN=Alchemy;Database=Alchemy;UID=guest;PWD=;")
***

IF some one can see something worng please help!!! I'm getting quite frustrated .... who isn't ;-)

Thanks in advance

[Clunietp]

First of all, NEVER use DAO with ASP, always ADO

Secondly, why are you using a DSN? Use a DSN-less connection with ADO

example

dim objCN as ADODB.Connection
set objCN = new ADODB.Connection

objCN.Open "Provider=SQLOLEDB;Data Source=Server Name;UID=UserName;PWD=Password;Database=MyDatabase"

[Maartin]

I have tried using ADO without System DSN, but this also gave me an error on the connection to the database. I have tried everything that I know of in VB and data in db apps.

Help please.

[Clunietp]

lets see your ADO DSN-less connection string

what error are you getting?

[Maartin]

Here is the connection string
cnMain.ConnectionTimeout = 60
cnMain.CommandTimeout = 60
cnMain.ConnectionString = "driver={SQL Server};server=DEV-SVR;uid=guest;pwd=;database=Alchemy"

cnMain.Open

and here is the error from the dll which is logged to a text file,
Description: [Microsoft][ODBC SQL Server Driver]Client unable to establish connection;
No: -2147467259

This the error that VB and ADO reports on the attempt tp connect to the database.

Thanks.

[Clunietp]

You're still using ODBC!

try this instead, tell me what it says

Provider=SQLOLEDB;Data Source=DEV-SVR;UID=Guest;PWD=;Database=Alchemy

[Maartin]

Hi Clunietp,

I tried your connection string but I still get an error, here is the description

Description: [DBNMPNTW]ConnectionOpen (CreateFile()).;
No: -2147467259

Thanks for the help man.

[Clunietp]

I've gotten that when my network is down or I specify an incorrect server name. Check your server name, make sure you can ping your server, and make sure the SQL Server service is running. I'm assuming you don't have any firewalls between your client and your server.....

[Maartin]

Yeah man, the network is up and running in VB itself I can access the server. But when I try to use the DLL I get this error. That is what is giving me gray hair.

I have never came across this, if it works in VB it will work in dll or exe form.

Thanks man.

[Clunietp]

What's up with the DLL? Is this just a regular DLL or are you using MTS/COM+? Are your references set correctly? I don't know why this would happen....try creating a new project and putting the code in a new DLL....

[SteveFlitIII]

I had that error message the other day when trying to connect to a msde database on a win 95 machine. I had to change the client network configuration to use TCP/IP instead of Named Pipes. Perhaps there may be a problem with this?
Just a thought

[Maartin]

ClunieTP, it is a normal VB dll using com (might be com+ that ships with Win2k, anyway of differating ?). This dll uses parameters passed to it form the asp page to query a MSSQL database these results are then used to permit access ect.

Yeah the refereces are correct and in VB they work fine but uppon connection the database in dll form is where i get these errors. I changed the dll to log all errors to a text file for debug.

Thanks for the help man.

[Maartin]

Hey SteveFlitIII


I did as you suggested and I have TCP/IP, Named Pipes and IPX/SPX but I still get this error.

ODBC--call failed.; 3146

Any other Ideas?

[Eclipse DevSoft]

Try helping your app by creating an alias configuration in the server thru MS SQL's Client Network Utility. If you can ping your server properly, use the IP address as the computer/server name. Also, try using 'Named pipes' as the default library. I know, named pipes is the slowest connection but we are debugging the connection here, right? I've seen a few servers manifest this kind of problem and so far, this is the only workaround that we've got, slow but working. And this is how I create my DSN-less connection

Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=DEV-SRV;Initial Catalog=Alchemy;User ID=Guest;Password=;ConnectionTimeout=10;

If you are using NT and want NT authentication as well, you can add this: "Integrated Security=SSPI;"-password is not neccessary.

By the way, does 'Guest' have an access to the Alchemy database? This may be a stupid question but who knows?

[Maartin]

Hi Eclipse DevSoft,

Well we have tried using the connection string provided, but no luck. Then we tried the using the IP address as the data source but both of the returned the following error:
[DBNMPNTW]ConnectionOpen (CreateFile()).; -2147467259

Named pipes are the default protocol on the server but the others Named Pipes, IPX/SPX are also loaded and active on the server.

The Guest account has got public access to the database, and the other required objects. I then tried to use the 'database administrator' account but this had the same error.

Any other sugestions that I can try ? I'm realy desperate.

Thanks man.

[Eclipse DevSoft]

I suggest you use ADO Data Control to create your connection string. This way you can test the connection right away. If the control can connect successfuly to the server, then there's no reason for your code not to connect. You can also test the connection by creating an ODBC DSN and playing with the Client Configuration option. The error you are getting suggest that the problem is the physical connection to the database server. This can be due to a lot of things, to say the least, incompatible libraries or it could be the hardware itself. If you can get your hands on other machine, I suggest you do that. Run your code on the same SQL server first (if you can) and narrow down the possible causes to your problem. Look at the SQL Server Utility and see if the required libraries are enabled. By the way, you don't need IPX/SPX if all the machines on your network is running on Windows, I guess you know this already.

[Clunietp]

Are you using SQL Server security or Windows NT authentication when you connect to your database?

Is the user "GUEST" defined as a user in SQL Server? Is this the same guest account that is an NT account? (this account has limited rights)

change your connection string to log in as SA and see what happens. I'll bet it's a permissions thing....

I had the 3146 error yesterday at work, it was because there was crap data in the database. By that I mean a text field had a single quote in it, that will screw SQL every time. In my case the connection had already been established, I know that because we had drilled down three layers into a dynamically populated treeview before the error occurred.

Are you sure you are not making a connection? Just a thought.

[Maartin]

Eclipse DevSoft,
We can try using a dat control in the dll but there is no forms and I would like to avoid inserting a form in the DLL. Do you think that the dat control will solve the problem of the DLL not connecting to the database. What bugs me about the whole story is that it all works in VB but the moment you compile and use the dll the connection to the database fails.

Clunietp,
the user account guest does exist on the server and has permissions to everything in the database and the whole server. The guest account is setup with public permissions. On the database in question the guest account has db_owner permissoins. We do not use NT authentication and security on SQL. I tried changing the login uid to sa but I had the very same error.

Madworm,
I had this single quote story aswell in the database but I wrote a function that solves the problem for me. The data in the table has been check manualy and everything is ok.


Thanks for the help guys.

[Clunietp]

Is your server listening on port 1433?

Is everything set properly on the client using cliconfg (TCP IP enabled, default port = 1433, no check on force encryption, no check on enabled shared mem protocol)?

This might be a long shot but how about using the IP address for the server name? Maybe that dash is throwing off the connection.....
instead of DEV-SRV use xxx.xxx.xxx.xxx (whatever your server IP address is)

[efrat]

Clunietp you are the best