Error 3029: Not a valid account name or password

Error 3029: Not a valid account name or password

Post by Dan Smit » Sat, 21 Aug 1999 04:00:00



I am in the process of rolling out an upgrade to 200 + users of one of
my applications and started receiving the above error message.  The
upgrade is mostly on the client side where I went from Access 2.0 to
Access 97.  On the server side I upgraded from Service Pack 1 to Service
Pack 5a of SQL 6.5.  The above error message occurs when the users try
to run various reports, all of which use SQL Passthrough to a Stored
Procedure.  Before the upgrade these reports ran off of attached tables.

    There were no changes related to user ids or permissions in this
upgrade which is why the Not a valid account name or password message is
so perplexing.  To make it even more complicated it doesn't happen on
all of the computers all of the time, but we can occasionally recreate
it.

Thanks in advance for any and all help you can provide.

Regards
Dan Smith

 
 
 

Error 3029: Not a valid account name or password

Post by Dan Smit » Mon, 23 Aug 1999 04:00:00


Below is my Stored Procedure and my VB code used to generate the
SQL-Passthrough query from within Access.  I am hoping this will help people
see what is happening:

CREATE PROCEDURE usp_gsmac_outstanding

AS
/*This stored procedure is a first step in recreating the outstanding
exceptions query.
*This stored procedure is to take the place of the Outstanding Exceptions
query used
*by Access for the detail reports.
*Note: for the ExceptionType field if a '~' is passed only those records
that have been
*populated will be returned, if a '!' or NULL only records that have been
populated will be passed
*if a '%' is passed all records will be returned.
*Created 11/06/1998 by DSS*/

SELECT tbl_gsmac.LOC,
 tbl_gsmac.FUND,
 datediff(dd, ReconDate, getdate()) AS Ageddays,
 case
  when datediff(dd, ReconDate, getdate()) < 30 then 1
  else 0
 end AS Day030,
 tbl_gsmac.SPECIAL_INSTRUCTIONS as SwiftComment,
 tbl_gsmac.SAFEKEEPING,
 tbl_gsmac.gsmac,
 tbl_gsmac.SUB,
 tbl_gsmac.VARIANCE,
 Abs(Variance) AS Varsort,
 tbl_regions.Country,
 tbl_regions.Bank,
 tbl_regions.area,
 tbl_regions.Region,
 case
  when SafCmmnt is null then convert(text, '')
  else SafCmmnt
 end AS ISOComments1,
 case
  when SafCmmntBy is null then ''
  else ' By: ' + ltrim(rtrim(SafCmmntBy)) + ' on: ' + convert(varchar(11),
SafCmmntDte)
 end as ISOComments2
FROM
 RDB.dbo.tbl_gsmac tbl_gsmac
left outer join
 rdb..ExceptionType ExceptionType
on
 tbl_gsmac.EXCEPTIONTYPE = ExceptionType.ExceptionType
inner join
 rdb..tbl_regions tbl_regions
on
 tbl_gsmac.loc = tbl_regions.loc
where

 and

 and

 and

 and

 or

 or

 and

 or

 or

 or

 or

 and
 RESOLUTIONDATE Is Null
 and
 tbl_gsmac.Safekeeping = 1
 and

 and

 and
 (TBL_regions.Safekeep_imp=1
 OR
 TBL_regions.Manual_Safekeep=1)
output:
GO

Access code:

Function BuildSQL(ByVal ExcType As String)
'****************************************************************
'*This function will build the SQL Passthrough query to be used *
'*by the openexceptions report(s).                              *
'*Created 12/16/98 by Dan Smith.                                *
'****************************************************************
On Error GoTo buildsqlerr
Dim sql As String
Dim myspace As Workspace
Dim mydb As Database
Dim rec_Outstanding As Recordset
Dim qry_Outstanding As QueryDef
Dim prpNew As Property

Set myspace = DBEngine.Workspaces(0)
Set mydb = myspace.Databases(0)

Select Case ExcType
    Case "Safekeeping"
        sql = "exec rdb_proc..usp_gsmac_outstanding "
    Case "Registration"
            sql = "exec rdb_proc..usp_gsmac_outstanding_Reg "
End Select

If Me.Loc = "*" Then

Else

End If
If Me.Fund = "*" Then

Else

End If
If Me.Settle = "*" Then

Else

End If
If Me.Reg = "*" Then

Else

End If
If Me.sleeve = "*" Then

Else

End If
If Me.asset = "*" Then

Else

End If
If Me.Clearance = "*" Then

Else

End If
Select Case STATUS
    Case 1
        'All

    Case 2
        'Uncoded

    Case 3
        'Coded

End Select

 mydb.QueryDefs.Delete "q_Outstanding"
'*Build query
Set qry_Outstanding = mydb.CreateQueryDef("q_Outstanding")
qry_Outstanding.Connect = "ODBC;DSN=" & sDSNName
qry_Outstanding.ODBCTimeout = 0
'Set prpNew = qry_Outstanding.CreateProperty("LogMessages", _
'        dbBoolean, True)
'qry_Outstanding.Properties.Append prpNew

qry_Outstanding.sql = sql
'qry_Outstanding.Execute dbSQLPassThrough

mydb.Close
myspace.Close
Exit Function
buildsqlerr:
If Err.number = 3011 Then
    '*Object doesn't exist continue on
    Resume Next
ElseIf Err.number = 3265 Then
    Resume Next
Else
    Dim errrs_value As Variant
    errrs_value = errrs("buildsql", "", GSMACDir, "tbl_stderror", Err,
Error$)
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
End If
Exit Function
Resume 0
End Function


> I am in the process of rolling out an upgrade to 200 + users of one of
> my applications and started receiving the above error message.  The
> upgrade is mostly on the client side where I went from Access 2.0 to
> Access 97.  On the server side I upgraded from Service Pack 1 to Service
> Pack 5a of SQL 6.5.  The above error message occurs when the users try
> to run various reports, all of which use SQL Passthrough to a Stored
> Procedure.  Before the upgrade these reports ran off of attached tables.

>     There were no changes related to user ids or permissions in this
> upgrade which is why the Not a valid account name or password message is
> so perplexing.  To make it even more complicated it doesn't happen on
> all of the computers all of the time, but we can occasionally recreate
> it.

> Thanks in advance for any and all help you can provide.

> Regards
> Dan Smith


 
 
 

Error 3029: Not a valid account name or password

Post by Dan Smit » Mon, 23 Aug 1999 04:00:00


Below is my Stored Procedure and my VB code used to generate the
SQL-Passthrough query from within Access.  I am hoping this will help people
see what is happening:

CREATE PROCEDURE usp_gsmac_outstanding

AS
/*This stored procedure is a first step in recreating the outstanding
exceptions query.
*This stored procedure is to take the place of the Outstanding Exceptions
query used
*by Access for the detail reports.
*Note: for the ExceptionType field if a '~' is passed only those records
that have been
*populated will be returned, if a '!' or NULL only records that have been
populated will be passed
*if a '%' is passed all records will be returned.
*Created 11/06/1998 by DSS*/

SELECT tbl_gsmac.LOC,
 tbl_gsmac.FUND,
 datediff(dd, ReconDate, getdate()) AS Ageddays,
 case
  when datediff(dd, ReconDate, getdate()) < 30 then 1
  else 0
 end AS Day030,
 tbl_gsmac.SPECIAL_INSTRUCTIONS as SwiftComment,
 tbl_gsmac.SAFEKEEPING,
 tbl_gsmac.gsmac,
 tbl_gsmac.SUB,
 tbl_gsmac.VARIANCE,
 Abs(Variance) AS Varsort,
 tbl_regions.Country,
 tbl_regions.Bank,
 tbl_regions.area,
 tbl_regions.Region,
 case
  when SafCmmnt is null then convert(text, '')
  else SafCmmnt
 end AS ISOComments1,
 case
  when SafCmmntBy is null then ''
  else ' By: ' + ltrim(rtrim(SafCmmntBy)) + ' on: ' + convert(varchar(11),
SafCmmntDte)
 end as ISOComments2
FROM
 RDB.dbo.tbl_gsmac tbl_gsmac
left outer join
 rdb..ExceptionType ExceptionType
on
 tbl_gsmac.EXCEPTIONTYPE = ExceptionType.ExceptionType
inner join
 rdb..tbl_regions tbl_regions
on
 tbl_gsmac.loc = tbl_regions.loc
where

 and

 and

 and

 and

 or

 or

 and

 or

 or

 or

 or

 and
 RESOLUTIONDATE Is Null
 and
 tbl_gsmac.Safekeeping = 1
 and

 and

 and
 (TBL_regions.Safekeep_imp=1
 OR
 TBL_regions.Manual_Safekeep=1)
output:
GO

Access code:

Function BuildSQL(ByVal ExcType As String)
'****************************************************************
'*This function will build the SQL Passthrough query to be used *
'*by the openexceptions report(s).                              *
'*Created 12/16/98 by Dan Smith.                                *
'****************************************************************
On Error GoTo buildsqlerr
Dim sql As String
Dim myspace As Workspace
Dim mydb As Database
Dim rec_Outstanding As Recordset
Dim qry_Outstanding As QueryDef
Dim prpNew As Property

Set myspace = DBEngine.Workspaces(0)
Set mydb = myspace.Databases(0)

Select Case ExcType
    Case "Safekeeping"
        sql = "exec rdb_proc..usp_gsmac_outstanding "
    Case "Registration"
            sql = "exec rdb_proc..usp_gsmac_outstanding_Reg "
End Select

If Me.Loc = "*" Then

Else

End If
If Me.Fund = "*" Then

Else

End If
If Me.Settle = "*" Then

Else

End If
If Me.Reg = "*" Then

Else

End If
If Me.sleeve = "*" Then

Else

End If
If Me.asset = "*" Then

Else

End If
If Me.Clearance = "*" Then

Else

End If
Select Case STATUS
    Case 1
        'All

    Case 2
        'Uncoded

    Case 3
        'Coded

End Select

 mydb.QueryDefs.Delete "q_Outstanding"
'*Build query
Set qry_Outstanding = mydb.CreateQueryDef("q_Outstanding")
qry_Outstanding.Connect = "ODBC;DSN=" & sDSNName
qry_Outstanding.ODBCTimeout = 0
'Set prpNew = qry_Outstanding.CreateProperty("LogMessages", _
'        dbBoolean, True)
'qry_Outstanding.Properties.Append prpNew

qry_Outstanding.sql = sql
'qry_Outstanding.Execute dbSQLPassThrough

mydb.Close
myspace.Close
Exit Function
buildsqlerr:
If Err.number = 3011 Then
    '*Object doesn't exist continue on
    Resume Next
ElseIf Err.number = 3265 Then
    Resume Next
Else
    Dim errrs_value As Variant
    errrs_value = errrs("buildsql", "", GSMACDir, "tbl_stderror", Err,
Error$)
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
End If
Exit Function
Resume 0
End Function


> I am in the process of rolling out an upgrade to 200 + users of one of
> my applications and started receiving the above error message.  The
> upgrade is mostly on the client side where I went from Access 2.0 to
> Access 97.  On the server side I upgraded from Service Pack 1 to Service
> Pack 5a of SQL 6.5.  The above error message occurs when the users try
> to run various reports, all of which use SQL Passthrough to a Stored
> Procedure.  Before the upgrade these reports ran off of attached tables.

>     There were no changes related to user ids or permissions in this
> upgrade which is why the Not a valid account name or password message is
> so perplexing.  To make it even more complicated it doesn't happen on
> all of the computers all of the time, but we can occasionally recreate
> it.

> Thanks in advance for any and all help you can provide.

> Regards
> Dan Smith