SQl server 2005

[lalitha1]

I am sure I am posting this to the wrong section of this forum.
My question is related to SQL Server 2005 and Classic ASP.

I can't seem to connect to our prod db using the below code.

<%
set cn=server.createobject("ADODB.Connection")
cn.provider = "sqloledb"
provstr = "server=myserver;Database=SysProd;UID=abc;PWD=def;"
cn.open ProvStr
%>

If I change the database(SysProd) to point to our dev system (SysDev), I am able to connect.

At first I though this was related to user security. I am now not sure. As far as I can tell, the security is configured the same on each database for the user. Could there be a setting at the database level that I am not looking at which would prevent connecting via Classic ASP?

 

[bborissov]

Are both DBs in the same server?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lalitha1]

Yes they both are on the same server.

 

[bborissov]

Could you connect with SSMS to that DB with the same credentials (UID=abs; Pwd=def?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lalitha1]

Yes, I tried that i am able to connect to both the DB's Prod db and Dev db with the same userid and passwd.

We have reports in Crystal 10 and when I open the raw report with the same user id it works fine.

Observed one more thing we call the crystal 10 reports in a Vb application and say i have 10 reports, 8 of them work except for two (all 10 reports uses the same id). Not sure
whatz causing the issue.

 

[bborissov]

Then it is Report problem (or that part of VB where you get RS for the report). Not SQL Server one.

WHat happend if you try:

set cn=server.createobject("ADODB.Connection")
provstr = "provider=[b]SQLNCLI[/b];server=myserver;Database=SysProd;UID=abc;PWD=def;"
cn.open ProvStr

?
or

set cn=server.createobject("ADODB.Connection")
cn.provider = [b]"SQLNCLI"[/b]
provstr = "server=myserver;Database=SysProd;UID=abc;PWD=def;"
cn.open ProvStr

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lalitha1]

Tried these, still doesn't work. One of the developers in my group think that this has global security setting or something similar. But I do not agree with this as we are able to connect to Db thru SSMS, the crystal reports call stps in the Db works fine. Not sure what else is something.
Any drivers/...

 

[lalitha1]

It works now. The issue was the stp that was referenced
was not sql 2005 compatible after we fixed the stp, it works fine. Thanks!