Thanks Frank!



>Shawn,
>
>here's the code that lists the databases:
>
>

>LPARAMETERS tcServer
>WAIT WINDOW NOWAIT "Getting databases on " + ALLTRIM(m.tcServer)
>
>lcCS = [DRIVER=SQL SERVER;SERVER=] + ALLTRIM(m.tcServer) + [;Trusted_Connection=Yes]
>*
>*  try to SQLSTRINGCONNECT() with the connection string
>*
>lnDispLogin = SQLGETPROP(0,"DispLogin")
>SQLSETPROP(0,"DispLogin",3)   &&& never
>lnHandle = SQLSTRINGCONNECT(m.lcCS,.T.)   &&& 2nd parameter as .T., as the single-connection-per user, shared by remote views
>SQLSETPROP(0,"DispLogin",lnDispLogin)
>IF m.lnHandle < 0
>	lcMessage2 = "A connection handle could not be established to the server."
>	WAIT CLEAR
>	MESSAGEBOX(m.lcMessage2, 0+16, _screen.caption)
>	RETURN .F.
>ENDIF
>
>llSuccess = SQLEXEC(m.lnHandle,"sp_helpdb")>0
>IF m.llSuccess
>	SCAN ALL
>		?RTRIM(SQLResult.NAME)
>		*!*RTRIM(SchemaCursor.Owner)
>		*!*SchemaCursor.db_size
>	ENDSCAN
>	USE IN SELECT("SQLResult")
>
>ELSE
>	SQLDISCONNECT(m.lnHandle)
>	lcMessage2 = "Unable to connect to the server using the specified connection."
>	WAIT CLEAR
>	MESSAGEBOX(m.lcMessage2, 0+16, _screen.caption)
>	RETURN .F.
>ENDIF
>WAIT CLEAR
>

>
>>Hi Frank, and Cetin, thanks for the help!
>>
>>Cetin, do you have SQL Server 2000 on the same instance of SQL server 2005?
>>
>>I have a fresh install of SQL Server 2005, reloaded my machine, as it needed it :-)
>>
>>I can not get SQLDMO to work! I tried one post that I say, and it said to register the SQLDMO.dll in the 80/bin folder (I think, that was the path, not on that machine right now)
>>
>>Frank I will give this a try; by any chance do you have code that lists the databases on a server? I kind of want to provide a tree that says here is the server and this is the databases on it.
>>
>>Thanks Shawn
>>
>>>Great Frank:)
>>>It lists much more servers than SQLDMO discovers. (To my surprise - SQLDMO too uses SQLBrowseConnect but cannot find all of those that this one finds).
>>>Cetin
>>>
>>>>Hi Shawn,
>>>>
>>>>I just did this by looking at the code in the dataexplorer.app (I think it was in the datamgmt_sql.prg). Basically you use some Windows APIs to get the list.
>>>>
>>>>Here's my adjusted code:
>>>>
>>>>

>>>>* Populate collection with available SQL servers
>>>>#DEFINE SQL_HANDLE_ENV			1
>>>>#DEFINE SQL_HANDLE_DBC			2
>>>>#DEFINE SQL_ATTR_ODBC_VERSION	200
>>>>#DEFINE SQL_OV_ODBC3			3
>>>>#DEFINE SQL_SUCCESS				0
>>>>#DEFINE SQL_NEED_DATA			99
>>>>#DEFINE DEFAULT_RESULT_SIZE		2048
>>>>#DEFINE SQL_DRIVER_STR			"DRIVER=SQL SERVER";
>>>>
>>>>LOCAL hEnv
>>>>LOCAL hConn
>>>>LOCAL cInString
>>>>LOCAL cOutString
>>>>LOCAL nLenOutString
>>>>LOCAL ARRAY aServerList[1]
>>>>
>>>>DECLARE SHORT SQLBrowseConnect IN odbc32 ;
>>>>	INTEGER   ConnectionHandle, ;
>>>>	STRING    InConnectionString, ;
>>>>	INTEGER   StringLength1, ;
>>>>	STRING  @ OutConnectionString, ;
>>>>	INTEGER   BufferLength, ;
>>>>	INTEGER @ StringLength2Ptr
>>>>
>>>>DECLARE SHORT SQLAllocHandle IN odbc32 ;
>>>>	INTEGER   HandleType, ;
>>>>	INTEGER   InputHandle, ;
>>>>	INTEGER @ OutputHandlePtr
>>>>
>>>>DECLARE SHORT SQLFreeHandle IN odbc32 ;
>>>>	INTEGER HandleType, ;
>>>>	INTEGER Handle
>>>>
>>>>DECLARE SHORT SQLSetEnvAttr IN odbc32 ;
>>>>	INTEGER EnvironmentHandle, ;
>>>>	INTEGER ATTRIBUTE, ;
>>>>	INTEGER ValuePtr, ;
>>>>	INTEGER StringLength
>>>>
>>>>
>>>>hEnv = 0
>>>>hConn = 0
>>>>cInString = SQL_DRIVER_STR
>>>>cOutString = SPACE(DEFAULT_RESULT_SIZE)
>>>>nLenOutString = 0
>>>>
>>>>ZAP IN (m.tcCursor)
>>>>
>>>>TRY
>>>>	IF SQLAllocHandle(SQL_HANDLE_ENV, hEnv, @hEnv) == SQL_SUCCESS
>>>>		IF (SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)) == SQL_SUCCESS
>>>>			IF SQLAllocHandle(SQL_HANDLE_DBC, hEnv, @hConn) == SQL_SUCCESS
>>>>				IF (SQLBrowseConnect(hConn, @cInString, LEN(cInString), @cOutString, DEFAULT_RESULT_SIZE, @nLenOutString)) == SQL_NEED_DATA
>>>>					nCnt = ALINES(aServerList, STREXTRACT(cOutString, '{', '}'), .T., ',')
>>>>					FOR i = 1 TO nCnt
>>>>						?(RTRIM(aServerList[i]))
>>>>					ENDFOR
>>>>				ENDIF
>>>>			ENDIF
>>>>		ENDIF
>>>>	ENDIF
>>>>CATCH TO oException
>>>>	MESSAGEBOX(oException.Message, 0+16, _screen.caption)
>>>>FINALLY
>>>>	IF hConn <> 0
>>>>		SQLFreeHandle(SQL_HANDLE_DBC, hConn)
>>>>	ENDIF
>>>>	IF hEnv <> 0
>>>>		SQLFreeHandle(SQL_HANDLE_ENV, hConn)
>>>>	ENDIF
>>>>ENDTRY
>>>>

>>>>
>>>>>Hi all, it has been forever since i posted here, I finally got a gig doing VFP work again, yeaaahhh ha!
>>>>>
>>>>>Time for the question currently the product use SQLDMO to list all the servers that the user can connect to. This no longer works with SQL Server 2005.
>>>>>
>>>>>Has anyone had any luck with connecting to SQLSMO to get the list of databases?
>>>>>
>>>>>TIA
>>>>>
>>>>>Shawn