SQL Server.

[Steve Thomas]

I have just been given access to a new SQL server. However, the system admin has no knowledge of how it works and is scheduled for training in April. I have looked through the CBT training courses. I am running Windows95. Does anyone know if I can build my tables using Windows95 since I do have access to the server and can see the hard drive. I can see executable files under the MSSQL7\BIN directory but I can not seem to find Enterprise manager anywhere. do I need access to NT system to build tables?.

[JHausmann]

You can use Access, if you have it , as a front end to SQL Server. You can also use MSQuery. But nothing beats Enterprise Manager. Ideally, you'll want a copy on your machine to develop your structures with (check licensing to see if it's allowed).


You do need to make sure that SQL Server is up and running on the server it was installed on _and_ that it has adequate storage for any databases you've a desire to create. Your best bet is to wait until someone has a better understanding of it before you start doing stuff.

[Gen-X]

Run the SQL Server installation CD on your local machine.

You will notice that there is an option to install ONLY the "utilities" and not SQL Server itself.

If you select to ONLY install the utilities what it will do is install Enterprise Manager, ISQL (handy for quick queries) and a series of other useful things.

Now you can go into your version of enterprise manager and register the SQL Server and work on it.

have a look at the Security Manager. What you can do here is get access to Windows NT domains and assign a workgroup who will automatically be granted SA (System Admin) when they log into NT.

[santiagope2000]

Howdy,

I have to concur with JHausmann. Access is good front end for first time development. Provided that your OS, SQL and Microsoft Access lisencing is squared away, you should have no difficulties scripting apps that connect up to the server. Attached is a quick recipe for a simple app that will qry a table through a connection to SQL. I don't know where it originated but it certainly helped me get off the ground

I also agree the MSSQLw (Microsoft Query) rules the day and should not be used if you have minimal experience with it. In any case if you feel comfortable with the risks then the attached may get you started.

SQL SERVER STUFF

1. Using MSSQLw create a db on the server and called it "myTestdb"
2. Using MSSQLw create a table in the db called "Employees"
3. Using MSSQLw create the following fields "Name" and "time_on_job"
4. Using MSSQLw Populate 10 records worth of data

Example:

Ted, 20
Fred, 5
Missy, 6
Charles, 3
Angel, 1
Mark, 12
Joseph, 9
Katrina, 10
Peter, 8
George, 120

CLIENT WORKSTATION STUFF

4. Go to your win95 (works on any OS) machine and fire up Visual Basic
5. Start new project call it myFirstqry
6. On a the blank form create a button.
7. Append the following line code to the button_click sub
"meTheClient"
8. Now paste the following below the sub.

NOTE: You do not require a mapped network connection directly to the SQL server (i.e. S://SQLDEV001) for this to work. In this case the Database connection is established through ODBC. Your machine however must be a member of the same LAN or WAN the SQL server is on. You will require the signon and password to the table on the server for a successful connection.

Sub meTheClient()

Dim mydbsCurrent As Database
'NOTE: Make sure you have included 'Microsoft Access 9.0 Object Library' (or facsimile thereof)
'in order for the above Dim to work

Dim qdfJustPassingThrough As QueryDef
Dim qdfClient As QueryDef
Dim rstTopFive As Recordset
Dim strMessage As String

'You'll need a db for the query to dump all of its findings
Set mydbsCurrent = OpenDatabase("c:\temp\experiment001.mdb")

' The pass-through query to retrieve data from
' a Microsoft SQL Server database.
Set qdfJustPassingThrough = _
mydbsCurrent.CreateQueryDef("AllEmployees")
qdfJustPassingThrough.Connect = _

"ODBC;DATABASE=myTestdb;UID=sysop;PWD=wow;DSN=smalltables
qdfJustPassingThrough.SQL = "SELECT * FROM Allemployees
"ORDER BY time_on_job DESC"
qdfJustPassingThrough.ReturnsRecords = True

' Create a temporary QueryDef object to retrieve
' data from the pass-through query.
Set qdfClient = mydbsCurrent.CreateQueryDef("")
qdfClient.SQL = "SELECT TOP 5 Employees FROM AllEmployees"

Set rstFirstFive = qdfClient.OpenRecordset()

' Display results of queries.
With rstFirstFive

strMessage = _
"Our first 5 employees were:" & vbCr

Do While Not .EOF
strMessage = strMessage & " " & !Employee & _
vbCr
.MoveNext
Loop

'In the event you have multiple records with the same Index

If .RecordCount > 5 Then
strMessage = strMessage & _
"(There was a tie, resulting in " & _
vbCr & .RecordCount & _
" employees in the list.)"
End If

MsgBox strMessage
.Close
End With

'Delete the query once your done with it. Then again, you may want to comment
'out the last line keep it for bragging rights.

mydbsCurrent.QueryDefs.Delete "AllEmployees"
mydbsCurrent.Close

End Sub

9. Save; Run; cross your fingers and click the button
10. Guess what? You've just built your first crude (..very, since it only has a qry method) but functioning Client/SQL server app.

[Gen-X]

Actually I would have to disagree that Access is a good front end to SQL Server.

While it might be "nice" for someone who hasn't done very much of this they will soon learn that Access restricts you too much and forces you to use non-standard features.

The SQL behind Access isn't even TRANSACT-SQL which means the queries you write use Access specific features that just dont exist in SQL Server.

If you want to construct a database then the best thing you could do is to first design it on paper and then go straight into the Enterprise Manager and contstruct the tables, add some indexes etc.

Then if you want to round out your understanding you would then DUMP the SQL scripts used to generate the tables you have just created and have a good look at the SQL generated that makes and drops tables.

That way you can come to a point of being able to write SQL to create and remove tables (which you ALWAYS have to do when you are developing a product considering that neither Access nor SQL Server allow you to totally modify the table structure once its created).

Take this from YEARS of experience working with Access, SQL Server, Infomix, Progress, Ingres, Oracle, DBase 4, Clipper, Sybase, MySql, MSSQL, SQLAnywhere and probably every other database language that was ever written to torment mankind

[santiagope2000]

Hey Gen-X,

Can't beat your profile with a stick

yes.. I'm forced to agree that Access is limited to a serious fault. 2 users (one full, the other on loaner) but it's still a friendly way to get started on a first time solo project no?

Now take SQLWindows on the other hand. Just as nightmarish but still a clever way to turn any old workbox into a server overnight.. heh.. heh

I've only developed in Access, Gupta SQLWindows, SQLBase, Oracle 7.x, PowerBuilder, Centura Team Dev, Informix and DB2.

Just recently started SAP R/3, ABAP/4 major punishment(4 months.. rookie). Hey, Perhaps you could help answer a question regarding variable parameters in ABAP's function editor? It's alot like the mainframe crud I've developed in years ago (TSO/MVS COBOL II) and it's giving me a headache.

[Steve Thomas]

Thanks for all the info. I was considering my options and I will indeed grab the install CD and install the utilities. I feel comfortable creating the table using enterprise manager.

I am not sure how to get to the MSSQLw application. I did a search but could not find it.

I am not sure what you mean by using Access as a front end? I know I can generate SQL code using Access but I don't follow you on how to get that table migrated from Access to SQL Server without using some utitlity??

Thanks again for all the suggestions!!!!

[Serge]

If you don't have Enterprise Manager, then you can go with other third party interfaces. For example DBArtisan or SQL Navigator by Quest software.
These two interfaces by far better then even Enterprise Manager. You can download BETA version of SQL Navigator at
SQL Navigator

[JHausmann]

I am not sure what you mean by using Access as a front end? I know I can generate SQL code using Access but I don't follow you on how to get that table migrated from Access to SQL Server without using some utitlity??

Within (and without) SQL Enterprise manager you can run an "DTS Import Wizard".

If you have to get started on your DB structures, do so in Access, if possible. You can move the structures and data, en masse, to the server once you have them right. Use the Access database for testing your code _until_ you get SQL server up and running (if you're using ADO, it's just a matter of changing the connection string, for the most part). I strongly advise you to get some experience in SQL server (either by a contractor/consultant or, at the very minimum, training) before doing production work in it. This comes from decades, yes that's plural, of experience in DBMS's (anyone here remember an NCR Criterion computer? <heh>). Ever do any SQL/DS with Rexx, santiagope2000?

You'll have plenty of time to learn DDL, if that's what you want to do...

[Steve Thomas]

I know Access very well and I guess I didn't understand your question. What you are saying is great because I really like designing the database in Access. It sounds like that is the way to first test and then once it is working then simply import it into SQL server using DTS. I have SQL up and running and I was able to import the Access table into SQL server. I was able to attach to the Server and pull my info back. I appreciate all your help on this one and with your help I am now up and running. At least for the time being until the next issue arises. Thanks again.

P.S. I have written an application that uses the Data Control. However, I do not see an option in the Data Control to use SQL server? I have been using Access. Any ideas?