SQLState: '42000' SQL Server Error: 4060 using MSDE on XP

Did you try to login as user Recurrent using osql -U Recurrent?
What is result?

- Open up Enterprise Manager
- Go to your server, expand it
- Expand Security
- Expand Logins
- Find Recurrent, right-click, properties
- Under the Defaults section, look at the Database drop-down.

Odds are that Recurrent has no access to the database selected there. You can do one of two things:

- Change the drop-down to a database the login does have access to
- Grant Recurrent access to that database (go to the Database Access tab and check the checkmark in front of the database that was selected in the dropdown).

For obvious reasons, the first alternative is preferable.

--------------------------------------------------------------
patrikt. I ran a small batch file to slow down the message in order see the output:

c:
cd\
osql -U Recurrent
pause

The message returned,  "Login failed for user 'Recurrent'"
---------------------------------------------------------------
SjoerdVerweij. This MSDE version is scaled down, am not finding any Enterprise Manager. Are there ways to run this from the command line or do you have an example how to hard code it into the VB 6.0 application?  

Thank you both.

What's strange is that I can log into my local server as "sa" but patrikt's comments are making me think of Recurrent as an additional user that can not log in, so perhaps with a password conflict. Do these error messages support this thinking? (I don't know what Recurrent is).  

SQLState: '42000' = Syntax error or access violation

SQL Server Error: 4060 = Ensure that the user ID, password, and Initial Catalog to the OLEDBConnectionString parameter of your RDA Pull method are correct.

Log in as sa and run these
EXEC sp_helplogins
USE <your database name>
EXEC sp_helpuser

You will see logins and default databases in first and users in your database in second.

Patrik

jedanner: Recurrent is a user, yes. Usually, you get this error because the login has a default database (database you end up in if you don't specify one) that the user does not have access to. Run sp_helplogins to see which one that is. To change it:

sp_defaultdb 'Recurrent', 'databasename'

"This MSDE version is scaled down, am not finding any Enterprise Manager"   MSDE doesnt come with Enterprise Manager.

hummmm SjoerdVerweij 'Recurrent' is a user?  ok.

When I check my database folder I have these files listed:

HS Recurrent_log.LDF
Recurrent_Data.MDF
Recurrent_Log.LDF
results.txt

Inside the result.txt states:
1> 2> Device activation error. The physical file name 'C:\HSI
\database\new\Recurrent_Log.LDF' may be incorrect.
New log file 'C:\Program Files\CD
\database\HSRecurrent_log.LDF' was created.

---------------------------------------------------

When I log into MSDE and use command:  sp_helplogins
the following prints:

      DefDBName      DefLangName      AUser

-----------------------------------------------------
BUILTIN\Administrators and a hex value
               
      master            us_english      NO
      master            NULL            yes

(2 rows affected)
LoginName DBNAME      UserName             UserOrAlias
-------- ----------    ----------              --------------
sa      master            db_owner            MemberOf
sa      master            dbo            User
sa      model                      db_owner            MemberOf
sa       model            dbo            User
sa      msdb            db_owner            MemberOf
sa      msdb            dbo            User
sa      tempdb            db_owner            MemberOf
sa      tempdb            dbo            User
(8 rows affected)


When I use command: sp_defaultdb 'Recurrent', 'Recurrent_Data.MDF'
I get error message, 'Recurrent_Data.MDF' does not exist. Use sp_helpdb to
show available databases.

Then I use command and will post output shortly.

Thanks guys.  

sp_helpdb produces following output:

name
      dbsize
owner
         dbid
created
status

HSRecurrent
__MB

sa
Status=ONLINE, Updatedability=..., UserAccess=MULTI_USER, Recovery=SIMPLE, Version..., Collation...,SQLSortOrder..., etc etc

Master
__ MB
Status, Updatedability..etc same

model
__ MB

msdb
tempdb

What's so odd is that I was informed all was working weeks ago. Would updating XP with the SP2 affect MSDE and require the SP3 server update?

It shouldn't. What is the connection string you're using in your VB6 app?

(I think you might be passing the database name as the login name)

Yes Sjored is right.
I'll sort out results you gived us:

You have one database (others are system) named HSRecurrent.

Members of BUILTIN\Administrators group can login to SQL with Windows authentication
and user sa can login with SQL authentication. No other users can login.
Default database is set to master for all.

Results it gives to me are:
There is no user named Recurent. May be was deleted or you have error in connection string.
You are confused by database files on disk. Don't look on disk, use logical database names; it is HSRecurrent is your database.
You have to set database in connection string to HS recurrent becouse default is master and it is wrong.

Show us connections string. Check connection properties in your application.

Patrik

Thank you all.

Patrikt, The application will not open until passwords and database authenitcation is complete/verified. A shut down sequence immeadiately follows. Since the connection to the database is in question and doesn't permit forward steps, I'm not able to check properties from the application. This however is the VB that I believe would be executed:

Private Sub cmdOK_Click()

    strUserName = Trim(Me.txtLogIn.Text)
    strPassword = Trim(Me.txtPassword.Text)
    strServerName = Trim(Me.txtServer.Text)
   
    frmInstall.sLogIn = "-U " & strUserName & " -P " & strPassword & " "
   
    sConnect = "Provider=SQLOLEDB.1;" _
                & "Password=" & strPassword & ";" _
                & "Persist Security Info=true;" _
                & "User ID=" & strUserName & ";" _
                & "Initial Catalog=" & Trim(txtDatabase) & ";" _
                & "Data Source=" & Trim(txtServer) & ";" _
                & "Use Procedure for Prepare=1;" _
                & "Auto Translate=True;" _
                & "Packet Size=4096;" _
                & "Workstation ID=" & Trim(Me.txtWorkstation.Text) & ";" _
                & "Use Encryption for Data=False;" _
                & "Tag with column collation when possible=False"

   
    SaveSetting "DigitalImaging", "Startup", "ConnectString", sConnect
    Unload Me
   
End Sub


Regarding showing the connection string, does this code supply what information needed to prove Sjored correct ? If not what specifically can I look for in the code? Thank you.
 

Just set a breakpoint on the SaveSetting line, go to the immediate window, type ?sconnect and paste the results here (you might want to change/remove the password, of course :-) )

Sjoerd: I tried the immediate window and typed '? sConnect' (after setting breakpoint on SaveSetting line) and the application starts, but doesn't get past the logon. The application closes before any printout inside the VB studio immediate window.

I may be doing it wrong; however I have accessed information from a log file and it includes this information and am hopeful it will suffice:

Connection string - Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=(local);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False
Database failed to open

O, the o of it all.

More Points.

Try simply

Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=ACTUALMACHINENAME;

Thank you SjorerdVerweij

Did that work?

Not yet. I'm so new at this that I don't know where to change it. The info about the SQLOLEDB.1 came from the log file and changing the code, I'm not usre exactly where to do that or if it is done using Visual's Data Manager, etc. I'm just so thankful you are pointing me in right direction.

Just replace the entire sConnect = blurb with

sConnect = "Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=ACTUALMACHINENAME;"

(on one line, after replacing OMITTED and ACTUALMACHINENAME with correct values)

Thanks SjoerdVerweij, I think that will work.

Is there a way I can isolate from this huge vb project just this code for testing?  The immediate window did not work properly.

I would like to write a separate vb6.0 small project for testing this code and confirm only the vb project's ability to fire this command and communicate successfully with the current databases. Could you suggest reference material for a small test?

Thank you.

How about

http://www.vbcode.com/asp/showzip.asp?ZipFile=http://www.vbcode.com%2Fcode%2FCompleteADOSampleApp.zip&theID=3457

I was just informed that this VB6.0 project initially worked on an XP machine using Office 2000 (unfortunately I have no access to the original machine). I'm now debugging this on a different XP machine and have Office 2002 running. When I uninstall the project, reboot, and access the project through Visual Studio many *.ocx files cannot be registered. Once I install VB6.0 Service Pack 6, the *.ocx files (mscomcl.ocx, comdlg32.ocx, msdatgrd.ocx, msinet.ocx) registering problem is solved. Then upon reinstallation of VB project I got an MSADO25.tlb error that required changing the Setup.lst file. Inside the Setup.lst file the msado25.tlb file was found and I changed (DLLSelfRegister) to (TLBRegister), solving the registering error of the msado25.tlb upon reinstallation.

I don't know, but I'm thinking that there's something the Office 2000 and Office 2002  that may affect this original problem of logging on to the local msde 2000 via the connection string. I am blind, but could the problem exist because of different versions of the Microsoft Data Access Components ?  

I don't know what MDAC is or what versions are currently being used, but it's a stab in the dark - VB project / MDAC affected by different Office 2000-2002 versions?  

more points. needing H E L P in a big way.

500 smackers

Having updated XP with SP2 would that have changed the MDAC  and cause errors above on logon attempt?

Try installing the latest version of MDAC from
www.microsoft.com/data

Thanks SjoerdVerweij, upon doing so I found material supporting idea of conflict between MSDE 2000 and XP SP2.


http://www.microsoft.com/sql/techinfo/administration/2000/security/winxpsp2faq.asp

How does Windows XP SP2 affect MSDE:

Windows XP SP2 may affect the behavior of Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). If you’re connecting to a network by means of TCP/IP, named pipes, or remote procedure call (RPC), the Windows Firewall will block the ability of the database to connect to the network. If you are using MSDE 2000 with SQL Server 2000 SP3a, you should not have a problem because the network and SQL Browser technology are already turned off by default.

So then I try to upgrade to SP3a ...


I must choose a command per using Mixed Mode or Windows Authenication.
I think I'm using Mixed mode, is there a way to confirm? The app does require sa and password
After keying in Mixed mode instructions into the command line, I get some error that says I must do it from
the control panel? Any ideas? Jeez it's just installing a service pack but I'm having problems with it.

Whew.

Way to go SjoerdVerweij!!! mixed mode changed, problem solved. Thank you so much!!!!