MS SQL Data Connection Problem

Topics: Developer Forum, User Forum
Nov 2, 2014 at 8:13 AM
Hi. I know the following from the demo program could assess the MS SQL Server.

*sql
oSQL = CREATEOBJECT("AVFPsql")
lcLast=NVL(oRequest.Form("lastname"),"")
oSQL.LogIn(['Driver={SQL Server}; Server=FIREBALL; Database=pubs; UID=sa;PWD=fkcfkcfkc1;'])
oSQL.cSQLCursor = "tCursor"
oSQL.Execute([SELECT  Au_fname as First,Au_lname as Last,City,Contract  ]+;
          [ FROM Authors ]+;
          [ WHERE au_lname like '] +lcLast +[%' order by au_lname] )

But, I could not connect the MS SQL, while using windows authentication mode?

Anybody have a connection string for both SQL Server Authentication and Windows authentication mode?

TQ
Coordinator
Nov 5, 2014 at 5:20 PM
Have you tried going to this site?
http://www.connectionstrings.com/sql-server/
Nov 8, 2014 at 3:09 PM
apaustria wrote:
Have you tried going to this site?
http://www.connectionstrings.com/sql-server/
Yes. But not successful using activevfp.
The database is under SQL 2005 using SQL Server Management Studio Express which is able to connect.
Coordinator
Nov 10, 2014 at 7:31 PM
I honestly haven't used the Windows authentication mode to connect to my MS-SQL database. I always use the SQL Server authentication. I have a sample.prg that I run in VFP and if I can get that thing to connect and work then I use the same thing in the aVFP connection.
LOCAL oerr as Exception
cErrCompete = ""
TRY 
    oSQL = CREATEOBJECT("AVFPsql")
    oSQL.LogIn(['Driver={SQL Server}; Server=MYWORKSTATION\SQLEXPRESS2008R2; Database=pubs; Uid=sa; Pwd=dbadmin;'])
    oSQL.cSQLCursor = "tCursor"
    oSQL.Execute([SELECT  Au_fname as First, Au_lname as Last, City, Contract  ]+;
                  [ FROM Authors ])
                  BROWSE 
CATCH TO oerr
    cErrCompete =  [  Inner Exception Object: ] + CHR(13) +;
    [  Error: ] + STR(oErr.ErrorNo)  + CHR(13) +;
    [  LineNo: ] + STR(oErr.LineNo)  + CHR(13) +;
    [  Message: ] + oErr.Message  + CHR(13) +;
    [  Procedure: ] + oErr.Procedure  + CHR(13) +;
    [  Details: ] + oErr.Details  + CHR(13) +;
    [  StackLevel: ] + STR(oErr.StackLevel) + CHR(13) +;
    [  LineContents: ] + oErr.LineContents + CHR(13) +;
    [  UserValue: ] + oErr.UserValue 
    STRTOFILE(cErrCompete, "C:\Temp\errsql.txt")
ENDTRY

             
              
********************************************************
DEFINE CLASS AVFPsql AS CUSTOM
#DEFINE crlf CHR(13)+CHR(10)
* Note: SQL/MSDE (as well as networked VFP DBFs) will need to be set up in COM+
*     and impersonate a user with proper network rights
************************************************************************
* AVFPsql :: LOGIN
*********************************
PROCEDURE LOGIN
LPARAMETER lcLogIn
SQLSetProp(0,"DispLogin",3)
SQLSetProp(0,"DispWarnings",.F.)
THIS.cconnectstring = lcLogIn

THIS.nsqlhandle = SQLSTRINGCONNECT(&lcLogIn)
IF THIS.nsqlhandle < 1
    THIS.lerror = .T.
    THIS.GetErrors()
    RETURN .F.
ENDIF
RETURN .T.
ENDPROC
************************************************************************
* AVFPsql :: ERROR
*********************************
PROCEDURE ERROR
LPARAMETERS nError, cMethod, nLine
#DEFINE SQL_SERVER '[SQL Server]'
DO CASE
CASE nError = 1466 AND THIS.nerror <> 1466
    THIS.nerror = 1466
    THIS.cerrormsg = MESSAGE()
    IF THIS.LOGIN(THIS.cconnectstring)
        THIS.lerror = .F.
        THIS.nerror = 1466     
        THIS.cerrormsg = ''
        RETRY
    ENDIF
ENDCASE
lnCount = AERROR(laError)
IF lnCount > 0
    ACOPY(laError,THIS.aerrors)
    lcErrorMsg = laError[2]
    lnLoc = ATC(SQL_SERVER,lcErrorMsg)
    IF lnLoc > 0
        lcErrorMsg = SUBSTR(lcErrorMsg,lnLoc + LEN(SQL_SERVER))
    ENDIF
    THIS.cerrormsg = lcErrorMsg
    THIS.nerror = laError[1]
ELSE
    THIS.cerrormsg = MESSAGE()
    THIS.nerror = nError
ENDIF
ENDPROC
************************************************************************
* AVFPsql :: DESTROY
*********************************
PROCEDURE DESTROY
IF THIS.nsqlres = 0
    =SQLCancel(THIS.nsqlhandle)
ENDIF

IF THIS.nsqlhandle > 0
    =SQLDisconnect(THIS.nsqlhandle)
ENDIF
ENDPROC
************************************************************************
* AVFPsql :: Execute
*********************************
PROCEDURE execute
LPARAMETER lcSQL,lcCursorName
lcSQL=IIF(TYPE('lcSQL')='C',lcSQL,THIS.csql)
lcCursorName=IIF(TYPE("lcCursorName")="C",lcCursorName,THIS.csqlcursor)
IF THIS.nsqlres = 0
    THIS.SQLCancel()
ENDIF
THIS.nsqlres = SQLExec(THIS.nsqlhandle,lcSQL,lcCursorName)
IF THIS.nsqlres = -1
    THIS.lerror = .T.
    THIS.GetErrors()
    IF (THIS.nodbcerror=14) AND ;
        THIS.LOGIN(THIS.cconnectstring)
        THIS.nsqlres = SQLExec(THIS.nsqlhandle,lcSQL,lcCursorName)
        IF THIS.nsqlres = -1
            THIS.lerror = .T.
            THIS.GetErrors()
        ELSE
            THIS.GetErrors('START')
        ENDIF
    ENDIF
ELSE
    THIS.lerror = .F.
    IF SQLCOMMIT(THIS.nsqlhandle) = -1
        THIS.lerror = .T.
        THIS.GetErrors()
    ENDIF
ENDIF
RETURN THIS.nsqlres
ENDPROC
************************************************************************
* AVFPsql :: GetErrors
*********************************
PROCEDURE GetErrors
LPARAMETER lcSTART
LOCAL lnLoc, lcErrorMsg
lcSTART=IIF(TYPE('lcSTART')='C',UPPER(lcSTART),'')
IF lcSTART = 'START'
    THIS.lerror=.F.
    THIS.nerror = 0
    THIS.nodbcerror = 0
    THIS.cerrormsg = ''
    RETURN
ENDIF
lnCount = AERROR(laError)
IF lnCount > 0
    ACOPY(laError,THIS.aerrors)
    lcDetails=""
    FOR i=1 TO ALEN(laError,2)
        FOR x=1 TO ALEN(laError,1)
            lcDetails=lcDetails+TRANSFORM(laError(x,i))+crlf
        ENDFOR
    ENDFOR
    
    lcErrorMsg = laError[2]
    lnLoc = ATC(SQL_SERVER,lcErrorMsg)
    IF lnLoc > 0
        lcErrorMsg = SUBSTR(lcErrorMsg,lnLoc + LEN(SQL_SERVER))
    ENDIF
    THIS.nerror = laError[1]
    THIS.nodbcerror = laError[5]
    THIS.cerrormsg = lcErrorMsg + ' ['+LTRIM(STR(THIS.nerror))+':'+LTRIM(STR(THIS.nodbcerror))+']'
ENDIF
ENDPROC
************************************************************************
* AVFPsql :: SQLCancel
*********************************
PROCEDURE SQLCancel
RETURN SQLCancel(THIS.nsqlhandle)
ENDPROC
*********************************
cconnectstring = ''
nsqlhandle = 0
csql = ''
csqlcursor = 'tCursor'
cerrormsg = ''
nerror = 0
nsqlres = -5
lerror = .F.
nodbcerror = .F.
DIMENSION aerrors[1,1]

ENDDEFINE
*
Nov 11, 2014 at 4:34 PM
Hi. the quote you reply is sample.prg?
Coordinator
Nov 13, 2014 at 2:33 PM
StevenMoo123 wrote:
Hi. the quote you reply is sample.prg?
Yes. I execute the whole script in VFP and if I can browse a table using the connection string, then I'm sure that it will work in aVFP.
Nov 14, 2014 at 4:05 PM
apaustria wrote:
Yes. I execute the whole script in VFP and if I can browse a table using the connection string, then I'm sure that it will work in aVFP.
Thank you for being helpful. After changes some few times on the connection string, it works which is for SQL 2005, windows authentication.

oSQL.LogIn(['Driver={SQL Server}; Server=.\PC; Integrated Security=true; AttachDbFilename=c:\abc.mdf; User Instance=true;'])

Would keep on reading more about connection string. :)