Recommend Courses ? 4

[jimlee]

Hi All,

I am looking to get into SQL server as I have seen many Access / SQL server jobs around and I have a few years experience with access / vba / jetsql.

Could anyone recommend any good courses they may have been on or the most relevant qualification to aquire, I suppose i'm looking for distance learning really.

Any help much appreciated

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[AlexCuse]

There are lots of free online tutorials like

http://www.sqlcourse.com

or

http://www.sqlzoo.net.

What are you looking to learn specifically? There are lots of 'teach yourself SQL' type books that I think would be good for you as well. I'd hit amazon before sinking the money into a course.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jimlee]

Great thanks, i'll check out these courses when I get time. I'm not sure what specifically I need to learn. Like I said, I know some JET SQL (from ms access) so I can use the basics (SELECT, INSERT, UPDATE, DELETE etc). I really need to learn 2 things...

1) The sql server environment as apposed to msaccess query builder/vba editor

2) More indepth SQL like Inner / Outer joins (i've limited knowledge of this) and sub queries etc.

Also, things like transact queries and any other facets of sql server which do not appear in ms access

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[AlexCuse]

I think you ought to just get a book and download SQL Server express (it is free). There is a query builder in SQL Server, but I would recommend you really learn t-SQL so that you can type your queries 'by hand', this will teach you to write more efficient code, and there are some things that the query builder can't do (or doesn't do right).

If you know some jet SQL you will be fine!

ALex

Ignorance of certain subjects is a great part of wisdom

 

[onpnt]

>> 1) The sql server environment as apposed to msaccess query builder/vba editor

Sorry but there is no comparison other than MS Access is a desktop application and SQL Server is a enterprise database server.

It would be like comparing a HP DL580 G4 to a desktop running a PIII @ 64MHZ

The first thing I would recommend doing is forgetting you ever worked on Access and start reading with a fresh mind on database servers and how they work (in regards to SQL Server)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[jimlee]

Thanks to you both for your help,

Alex,
I didn't realise that sql server express was free, that's great, i'll download it.

Jeff,
forgetting i've ever worked with Access sounds great! the only reason that i've mentioned this though is because i've seen many jobs where they want access front end with sql server back end but I suppose this is irrelivant for learning sql server anyhow. Good money though if you contract

Cheers


jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[onpnt]

Thanks Jim,

I always try to stress not to pick your career path based on money. IT isn't really a big money field unless you get into management. If you are making 6 figures but after 6 months dread going to work it isn't worth it and you may end up going backwards instead of forwards.

After taking a look at database servers take caution and choose what you think is best for you carefully.

Good luck!

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[jimlee]

Thanks Jeff, Your advice is much appreciated

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[gmmastros]

I'm going to take Jeff's advice too. Everytime Jeff gives advice, it is good. And I want to make as much money as Jeff one day. Thanks Jeff.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[onpnt]

[LOL] You'll never get there Fortune cookie

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[SQLSister]

Here's some recommended reading

http://www.tek-tips.com/faqs.cfm?fid=3324

I can up from Access as well, here are some subjects you need to learn about that are very different than Access;

Theory of relational database design (many many Access developers are weak in this, you need to get strong if converting to an Enterprise level db)
Database security
stored procs
dynamic SQl and why it should be avoided
cursors and why they should be avoided (many access developers are used to looping through record sets, get the whole idea of looping out of your brain at the start, you' thank me for it)
performance tuning
Use of the Case statement instead of IIF
Cross tab queries
Avoiding the use of the GUI when writing queries
Using a development environment and then pushing the changes to production
Using Souce control
Audit tables

and in your case - how to best store historical data for reporting purposes.

You can get a lot of help from this forum. I certainly learned a lot from here when I first started.

Questions about posting. See faq183-874

 

[SQLDenis]

>>If you are making 6 figures but after 6 months dread going to work it isn't worth it and you may end up going backwards instead of forwards.



does that include precision?

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[jimlee]

Very helpful, thanks SQLSister

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[jrbarnett]

Like SQLSister I've also made the jump from Access, but use both of them now.

From the Access perspective (slightly OT for this forum, but relevant if you do the client side of apps).

Connect from your client app with ADO (or ADO.NET for .NET based languages) to the server as the app starts up. You can use the ADO error codes to check for invalid usernames/passwords and other connection errors.

Have one public connection object in its own module, connect at startup and then execute your data access requirements as command objects against this, picking up the results and acting on them where necessary. Be sure to close it on exiting the application though.

In recent versions of Access, forms can accept ADO recordsets as well as DAO recordsets (which is what you use with Access local tables). I've not tried this with reports in depth though, but hope it would be the same.
If you need a query as such, use a form in datasheet view, it gives you the same functionality plus the ability to handle VBA.

By using parameterised stored procedures for database access and ADO command objects, the account that is used to connect to the DB only needs execute permissions on the SP's, so you instantly remove the possibility of being able to access the data directly. Use this ability to have server side data validation in your code.

If you get involved in SQL server admin, remember that the permissions that SQL Server itself has on the server are those of the service account - this is why it can't see local mapped drives, your own email MAPI profiles etc.

If you do this properly, there will be no need for linked tables in the app. If you open linked tables from SQL Server in Access, a lot of records get locked, and this hampers performance a lot. Some queries require dragging all the data across the network.

As an idea of the performance that can be improved, by totally rewriting a linked table based reporting system using these techniques (sometimes referred to as unbound forms), I knocked the run time of some of the queries down from 3 minutes to under 3 seconds. This was for returning a recordset with 130 rows which is appalling.

Looking at SQL profiler running the old version of the system, it was sending several 10,000 row tables across the network then joining them on the client to a local table in access, no wonder.

John

 

[SQLBill]

And a top way to learn some of the 'ins and outs' of SQL Server coding is to read through the FAQS on this forum and follow some of the threads. In the FAQs, you will find a lot of answers to commonly asked questions (especially DATETIME issues). In the threads, you will see our solutions/advice to member's questions and normally an explanation of the code is also provided.

Also, take a look at FORUM962 which is the SQL Server: Setup and Adminstration forum.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

Oh and one thing I forgot that is critical - make sure all of your tables have a primary key defined. There are lots of problems with Access front ends and SQL Server back ends that are caused by not having a primary key. And the equivalent of autonumbering a table is to create a numeric datatype (typically int) with the identity property set to yes. Look in BOL (Books online for more on identity). And another thing to do first off is to read the data type descriptions as they are differnt from waht is available in Access. DO not convert using the wizard or you may get really bad datatypes. For instance in SQL server a varchar field holds more daa than the Access equivalent, so there are fields in Access that are memo fields which no longer need to be so. The wizard will not know that.

Questions about posting. See faq183-874

 

[jimlee]

Thankyou, thankyou, thankyou! Everyone is so helpful, it's much appreciated.

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins