Assumptions:
the name of the server machine is OZ1
TCP/IP networking

Server setup:

1. download form MS:Start at http://www.microsoft.com/sql/downloads/2000/sp3.asp, Full Download: Select language, Go, bottem of page are 3 links, you want sql2kdesksp3.exe 70815KB
2. run it, take the default to unpack into C:\sql2ksp3
3. Open a command prompt, cd \sql2ksp3, run the setup: C:\sql2ksp3>setup.exe sapwd=abc123
   
4. start the server: start button, programs, msde, server manager (I think it is different depending on OS.)
5. hit the "play" button - you should see a little server in the tray.

There are some options that are worth mentioning here:

The default is for MSDE sp3 to NOT accept commands from the network. You can enable network support with DisableNetworkProtocols=0 (yeah, kinda goofy.)

There are 2 ways of logging in: Sql and Windows Auth. If you install on win9x you only get SQL. If you install on NT it defaults to Windows Auth only, but you can enable SQL too with: SecurityMode=sql

So my installs look like this:
C:\sql2ksp3>setup.exe SecurityMode=sql sapwd=abc123 DisableNetworkProtocols=0

I hear that if you bundel msde with an app, you should name the instance of the server you install so that other servers can co-exist on the same box. That is done with setup.exe INSTANCENAME="InstanceName"


SQL Server Administrator
There is now a web based SQL Server Administrator that can be used with SQL and/or MSDE. This is great for those who want to work with MSDE but don't want to spring for the $49 to get the Dev version to have the tool set.

http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en

There is an excellent free admin tool here:

http://sqlmanager.net/products/mssql/manager/purchase.phtml

See 'SQL Manager Lite' link on middle of page for free version. You can also install the Client Tools only from a full SQL Server install disk and use those.

Also, it's "ess queue ell" Server, not 'Sequel Server'! --AlanBourke

Client setup:

Run this VFP program: (taken from Fox Talk Setting Up ODBC Connections for the Client User.)

DECLARE Integer SQLConfigDataSource IN odbccp32.dll Integer, Short, String @, String @
#DEFINE ODBC_ADD_DSN 1   && Add data source
lc_driver = "SQL Server" + CHR(0)
lc_dsn = "DSN=DemoSqlDsn" + CHR(0) ;
  + "Network=DBMSSOCN" + CHR(0) ;
  + "Address=OZ1" + CHR(0) ;
  + "SERVER=OZ1" + CHR(0) ;
  + "UID=sa" + CHR(0)
? SQLConfigDataSource(0, ODBC_ADD_DSN, @lc_driver, @lc_dsn)
* 1 = Successfully Configured DSN

(dosn't work right now, something wrong with the "UID=sa" parameter. SQL Installer Error() shows "Error No: (8) Error Msg: 'Invalid keyword-value pairs'". Anyone got any ideas? Code to get the errors: _ MSde Setup 1. I got the idea for the UID keyword by creating a file dsn: _ MSde Setup 2 -- ?CFK

Or follow these GUI steps:

1. Control Panel, ODBC Data sources
2. User DSN tab, Add, "Sql Server", finish (yea right - more to come)
3. Name: DemoSqlDsn
4. Description: whatever
5. Server: OZ1 (put your server's name here, which defaulted to the name of the machine it is running on)
6. Next
7. How should SQL server verify the authenticiy of the login ID? With SQL Server authentication....
8. hit "Client Configuration...", TCP/IP, "OK"
9. Login ID: "sa" (no password)
10. Next, Next, Finish (still not finished!)
11. Hit "Test Data Source..."
12. you should see "TESTS COMPLETED SUCCESSFULLY!". If you don't review and fix something.
13. "OK", "OK", "OK"

VFP program:

comments are in green
Required VFP commands are blue
code to make debugging easier is purple
code executed by the sql server is black
Data to be stored on the server is grey - the single quotes are black, but they just look gray

* program Remote Data demo
on error xErrHlr( line(), message(1) )

* First using SQL Passthrough

* all SPT commands should reutrn a positive number
* lCkErr() checks for that and calls the error handler otherwise

* Open a connection, get a connection handel
lhX = sqlconnect( "DemoSqlDsn", "sa", "" )
? lhX
lckErr( lhx, "connect" )

* nRet = sqlexec( lhX, "drop table foo" )
nRet = sqlexec( lhX, "create table foo ( pkFoo int, cFNam varchar(10), cLNam varchar(10))" )
lCkErr( nRet, "create table foo" )

nRet = sqlexec( lhx, "insert into foo ( pkFoo, cFNam, cLNam ) values ( 1, 'Carl', 'Karsten' )" )
lCkErr( nRet, "ins" )

* Better way using parameters: (the parameters get evaluated in the VFP realm.)
* Untested code.  Once someone runs this and makes sure it works, take me out, please.
lpk_foo = 2
lcFirstName = "Bonnie"
lcLastName = "Berent"
nRet = sqlexec( lhx, "insert into foo ( pkFoo, cFNam, cLNam ) values ( ?lpk_foo, ?lcFirstName, ?lcLastName )" )



nRet = sqlexec( lhx, "select * from foo" )
lCkErr( nRet, "select" )
list

? sqldisconnect( lhX )

* second using Views

create database Demo
create connection DemoCon datasource DemoSqlDsn userid "sa" password ""
create sql view rv_foo remote connection DemoCon as select * from foo

dbsetprop( "rv_foo", "view", "SendUpdates", .t. )
dbsetprop( "rv_foo.pkFoo", "field", "KeyField", .t. )

use rv_foo
append blank
replace pkFoo with 2, cFNam with "Mike", clNam with "Helland"
* Notice later in the ADO ecample,
* Mike's name gets padded with spaces, as where the SPT names did not.

list

? tableupdate() && sends the update to the server

use

* Now using ADO
* I think I'm cheating by using the odbc dsn.
* it would be grand if someone fixes this.

loCon = NewObject( "ADODB.Connection" )
loCon.open( ';DATA SOURCE=DemoSqlDsn;USER ID=sa;PASSWORD=' )

loRS = loCon.execute( 'select * from foo' )

do while !loRs.eof
  ? loRs.Fields("cfNam").value, loRs.Fields("clNam").value
  loRs.movenext()
enddo

loCon.Close()

return

*************************************************************************
function lCkErr( tcErr, tcDsc )
if !empty( tcDsc )
  ? tcDsc, trans( tcErr )
endif
if tcErr < 0
  xErrHlr()
else
  return .t.
endif

*************************************************************************
function xErrHlr( tnLin, tcLin )

#DEFINE CRLF chr(13)+chr(10)
#DEFINE CR_LF CHR(13) + CHR(10)

local ;
  laErrs[1], lnErrs, lnErr, ;
  llOleErr, llOdbcErr, llTrigErr, ;
  lcErrMsg

aError( laErrs )
llOleErr = between( laErrs[1], 1426, 1429 )
llOdbcErr = laErrs[1] = 1526
llTrigErr = laErrs[1] = 1539

lcErrMsg = ;
  '1. Error: ' + trans( laErrs[1] ) + CRLF ;
  + '2. Message: "' + laErrs[2] + '"' + CRLF ;
  + '3. ' + iif( llOleErr or llOdbcErr, "OLE/ODBC: ", "EMP: " ) + transform( laErrs[3] ) + CRLF ;
  + '4. ' + iif( llOleErr, "App: ", ;
    iif( llOdbcErr, "SQL State: ", ;
    "Work Area (?): " ) + transform( laErrs[4] ) ) + CRLF ;
  + '5. ' + iif( llOleErr, "OLE Help file: " + trans( laErrs[5] ), ;
    + iif( llOdbcErr, "ODBC Error: " + transform( laErrs[5] ), ;
    + iif( llTrigErr, "Trigger: " + substr( "InsertUpdateDelete", laErrs[5]*6-5, 6 ), ;
    + "na: " + transform( laErrs[5] ) ) ) ) + CRLF ;
  + '6. ' + iif( llOleErr, "OLE Help Context Id : ", ;
    + iif( llOdbcErr, "ODBC Connection Handle: ", ;
    + "na: " ) ) + transform( laErrs[6] ) + CRLF ;
  + '7. ' + iif( llOleErr, "OLE exception number: ", ;
    + "na: " ) + transform( laErrs[6] )

do case
  case laErrs(1) = 1539
    lcErrMsg = lcErrMsg ;
      + "Trigger error #: " ;
      + transform( laErrs(5) ) + ', ' ;
      + substr( "InsertUpdateDelete", laErrs(5)*6-5, 6 ) ;
      + CR_LF + CR_LF

      if vartype( gaErrors[1] ) = 'N'
        for lnErrorRow = 1 to alen( gaErrors, 1 )
            lcErrMsg = lcErrMsg +;
              "1. Error number: " + trans( gaErrors[lnErrorRow,1] ) + CR_LF + ;
              "2. Error text: " + trans( gaErrors[lnErrorRow,2] ) + CR_LF + ;
              "3. Source: " + gaErrors[lnErrorRow,3] + CR_LF + ;
              "4. Call stack: " + gaErrors[lnErrorRow,4] + CR_LF + ;
              "5. Parent table name: " + gaErrors[lnErrorRow,5] + CR_LF + ;
              "6. Parent record number: " + trans( gaErrors[lnErrorRow,6] ) + CR_LF + ;
              "7. Original value of Primary Key of the Parent: " + trans( gaErrors[lnErrorRow,7] ) + CR_LF + ;
              "8. Parent Primary Key (expression): " + gaErrors[lnErrorRow,8] + CR_LF + ;
              "9. Child Table Name: " + gaErrors[lnErrorRow,9] + CR_LF + ;
              "10. Child Record Number: " + trans( gaErrors[lnErrorRow,10] ) + CR_LF + ;
              "11. Original value of Foreign Key in the Child: " + trans( gaErrors[lnErrorRow,11] ) + CR_LF + ;
              "12. Child Foreign Key (expression): " + gaErrors[lnErrorRow,12 ] + CR_LF + CR_LF
        endfor
      endif
endcase


? "Line " + transform( tnLin ) + ": " + transform( tcLin )
? lcErrMsg

on error
suspend

cancel

---

DSN-Less VFP Program (Personally, I consider the ODBC setup to be absurd to expect a user to do, don't you? Well, here's a way to skip it, and to automate the MSDE server database setup through VFP. After all, with we're *really* not supposed to use "Connections" in DBC's or remote views or anything like that if we're doing real n-Tier design, right? Just use the SQLExec (at the bottom of code sample) in your Business Objects )

lcServerName = "OZ1" &&  (put your server's name here, which defaulted to the name of the machine it is running on)

lcDBName     = "Northwind"
lcUserName   = "sa"
lcUserPass   = ""

if type('_Screen.lst')<>'O'
  _SCREEN.AddObject('lst','ListBox')
endif
_Screen.lst.Clear
_screen.lst.top=0
_screen.lst.left=0
_screen.lst.height = _Screen.ViewportHeight
_screen.lst.Width  = _Screen.ViewportWidth
_screen.lst.visible = .t.
_Screen.lst.ColumnCount = 4

dmo = CreateObject('SQLDMO.SQLServer')
dmo.DisConnect
WAIT WINDOW NOWAIT "Connecting to SQL Server Using DMO..."
dmo.Connect( lcServerName, lcUserName, lcUserPass )
WAIT CLEAR
_Screen.lst.AddItem("SQL Server Name= "+dmo.name)
_Screen.lst.AddItem("DMO Host Name  = "+dmo.hostname)
_Screen.lst.AddItem("SystemAdmin Access? = "+iif(dmo.salogin,'Yes','No'))
_Screen.lst.AddItem("Databases:")
for each oDB in dmo.Databases
  _Screen.lst.AddItem( "  "+odb.name+' '+str(odb.Size))
endfor
_Screen.lst.AddItem("")
_Screen.lst.AddItem("Permissions: ("+lcDBName+")")
oPs = dmo.Databases(lcDBName).ListObjectPermissions()
for each oP in oPs
  _screen.lst.AddItem( oP.ObjectName )
  _screen.lst.List( _screen.lst.NewIndex, 2 ) = ;
      oP.ObjectTypeName
  _screen.lst.List( _screen.lst.NewIndex, 3 ) = ;
      oP.PrivilegeTypeName
  _screen.lst.List( _screen.lst.NewIndex, 4 ) = ;
      iif(oP.Granted,'Yes','no')
endfor
ops = .null.
op = .null.
* Manual Garbage Collection!!
RELEASE DMO
DMO = .NULL.

WAIT WINDOW NOWAIT "Connecting To SQL Server..."
lh=SQLStringConnect([uid=]+lcUserName+[;pwd=]+lcUserPass+[;server=]+lcServerName+[;driver={SQL Server};database=]+lcDBName+[;DSN=''])

WAIT WINDOW NOWAIT "Retriving Top 3 Customers..."
sqlexec(lh, [SELECT TOP 3 * FROM customers],'C1')&& C1 is the name of the cursor to create
WAIT CLEAR

browse normal

RETURN

- ?wgcs

I tried to run this, and got an error on this line:

dmo.Connect( lcServerName, lcUserName, lcUserPass )

OLE IDispatch exception code 18452 from Microsoft SQL-DMO (ODBC SQLState: 28000): [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection...

This was on a clean SQL2000 install, and I have not changed the sa password yet. I also went in and blanked it out just to be sure.

Any Ideas??? -- ?CFK

Take a look at Login Authentication

---

If you have the Sql2000 Eval CD, you can install the full version (not demo) of Enterprise manager, Query Analyzer, and Profiler: (not sure how legal this is, so use at your own risk.)

1. Database Components
2. database server
3. local computer
4. new instance... or client tools
5. name
6. yes, I agree to these confusing terms of use.
7. Client tools only
8. comonets - defaults are good
9. start - next

---

tip: "The behavior of DISABLENETWORKPROTOCOLS changed in the Desktop Engine SP3a to be more secure by default." - readme.htm

Here is my setup.ini file:

securitymode=sql
SAPWD=abc
DISABLENETWORKPROTOCOLS=0

DISABLENETWORKPROTOCOLS=0 - if you don't do this, it won't be avlaible over the lan. On the other hand, if it is a stand alone box, perhaps it is better, no chance of catching something like the Slammer virus.

To See what Network Protocols the server is using, run SVRNETCN.exe (it's in "C:\Program Files\Microsoft SQL Server\80\Tools\Binn" which the install should have added to your path.)

---

usefull links:

http://msdn.microsoft.com/vstudio/msde/instdwnld.asp

Link for MSDE 2000:
http://www.microsoft.com/downloads/details.aspx?FamilyID=a0dac778-60a6-4b11-8aa8-bf12261a303a&DisplayLang=en
Dan LeClair

Sample DBs (northwind, pub)
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034

---

See Also: Sql Dmo

---

Contributors: Carl Karsten, Mike Helland, Steve Sawyer, wgcs

---

Category SQL Versions Category MSDE Category Code Samples

Edit -Find- Recent Changes Categories Road Map [A-F] [G-P] [Q-Z] New Topic Home

Search:

http://fox.wikis.com - a low-impedance, fat-free Visual Foxpro site. - 2010.09.02 05:21:13 PM EST