At Least They Got The Data

OK, so is it just me or does anyone else think its rather missing the point that a group of scientists are relieved because their 400Mb disk drive, thought destroyed in the Columbia space shuttle disaster was recovered from the wreckage and restored by Ontrack.
http://www.pcpro.co.uk/news/194388/space-shuttle-hard-disk-survived-crash.html

“We got the data thank f**k for that.” Here’s a thought, what about building a space shuttle that doesn’t burn up in the atmosphere killing all on board in the first place?!?

Listing Folder Contents to a Text File

My brother in law Matt (take a bow buddy!) asked me a good one the other day. He had a directory (I still call them that, I know, they’re called Folders now) which contained a number of files that he wanted to be able to print a list of.
No, I don’t know why he wanted it either but it’s a good theoretical example that can show off how easy this kind of stuff can be from my homeland, the command line.

Once more, unto the DOS dear friends….
In order to get a list of files whenever we find ourselves on the rocky shores of the command line (you know, the place you go if you type cmd in the run box) you use the very handy and occasionally powerful dir command.
Dir does what it says on the tin, it gives you a directory list of the files in that directory. Where it gets powerful is when you combine it with the switches it can use. A switch is a special parameter you pass to the command to tell it how to behave. With most commands you can get a list of their switches and their uses by following the command with the /? switch (Eg dir /?).

Lets just go straight for the example I gave Matt. The commands will take you to a directory on the C drive of the PC you’re on (in this case we want the contents of the C:\Music directory) then list the contents of that directory into a text file for you.
C:
Cd\
Cd Music
Dir /b >C:\EdsExampleFile.txt

This is readable and doesn’t come with all the details of the files (making it a handy list for other purposes). It does however list any child directory names too. To get it even more useful, we can use more switches in the Dir command line to make it hide those folder names.
The /a switch is used to list files that have a particular attribute set (that it’s a directory, a hidden file, read-only, that sort of stuff). To list all directories you’d type dir /ad. Here we don’t want them so the command line should now be:
Dir /b /a-d >C:\EdsExampleFile.txt

Oh, and if we just wanted a list of the .mp3 files in that folder (see, what I did there? Using the right term) you’d go:
Dir /b *.mp3

Easy peasy!

Setting your PC to shut down automatically at a set time of day

I’ve been asked before how to set up a PC to automatically shut itself down at a particular time. In circumstances where there are a large number of PCs, this becomes one of the only options that are workable if you have a number of users who refuse to shut down their PCs.

In Windows XP, this is an easy task to manage.

In days gone by, you could use the command AT to set up scheduled tasks to run. This was OK and did the job adequately but it didn’t give you complete control of the task that would be created. For example, by using AT, you would create the task name AT1. That’s what it would be called; don’t like it? Tough!

Now, in Windows XP the command SCHTASKS has joined the party. In order to show you how to use it, let’s answer the original question.

Q- How do I set my PC to shut down every day at the same time?
A- Run the following command at the command line (Start – Run “cmd”):

SCHTASKS /Create /TN “Shutdown PC at Night” /TR “C:\Windows\System32\Shutdown.exe /s /f” /RU domain\username /RP password /SC DAILY /ST 21:30:00

OK, scary isn’t it? Let’s break it down:
SCHTASKS /Create (Create a task)
/TN “Task name” (Call the task by this name)
/TR “command line” (Run this command)
/RU domain/username (Run as this user)*
/RP password (with this password)
/SC DAILY (Schedule to run daily)
/ST 21:30:00 (Run at 21:30)

* the /RU command can also be used thus: /RU computername/username

In order to get a better idea of the kind of power this command can have, simply run SCHTASKS /CREATE /? at the command line. It’ll tell you what you want to know.

This was a very brief description of one of the many functions available through SCHTASKS.exe and Shutdown.exe, I’ll get into some others on further posts regarding these brilliant (and free) utilities.

Setting up DSN Connections

Setting up a Data Source Name (or DSN) in Windows is something akin to falling off a log, once you’ve found the appropriate log, falling off it is child’s play!

Windows comes with a number of Open DataBase Connectivity (ODBC) drivers already installed. Things like Access and SQL server are easily contactable from the get go, its only when you look at database systems from other suppliers that you need to think about downloading drivers.

Products such as Oracle, MySQL, PostgreSQL and the like will require their own drivers installing before they become an option for DSNs. When you think about it, it kind of makes sense. Why should Microsoft try to write the driver when the one from the manufacturer will do the job much better and will probably go through many incarnations before the next Windows release?

I’ll talk you through how to set up a vanilla, ready salted, sorbet of a DSN setup that will hopefully point you in the right direction if you’re using other clients.

OK, here goes:

• Go to Control Panel
• Double click Administrative Tools
• Open Data Sources (ODBC)

There are a number tabs available to you at this juncture, I’ll be concentrating on System DSN in these instructions. Please see my notes at the end of this post for a little further information about the others.

• Click the System DSN Tab
• Click the Add… button
• At this point you must choose the ODBC drive you’re going to use for the DSN, select SQL Server for now
• In the Name box type the name you want the DSN to have
• Type a meaningful description in the Description box
• Drop down the list at the bottom to select the server you wish to connect to
• Click Next

At this point you’ll be asked about how you’d like to connect to the server, you’ve got two options:

Windows Authentication uses the credentials of the user that’s currently logged in to the PC. Its OK from most connections as long as the database administrator has set up the user on the SQL server.

SQL Server Authentication is used when you’d like to connect to the server as a SQL Server user  account. It doesn’t care who you’re logged in as, you’ll authenticate as this user. In order for this to work, your dba will need to have created a SQL user and be running the server in question in Mixed Mode Authentication mode.

• Leaving the authentication as Windows for now, we’ll move on. Click next
• Now you have the option to change the database you’ll automatically connect to and some other settings to do with ANSI identifiers and nulls, ignore the lot and click Next
• At this screen you get the options for logging, encryption and languages, leaving these as they are you click Finish and away you go.

This is the most basic form of connection you can have. Using this you can create linked tables in Access, reports in Crystal Reports, use pass through queries, the lot!

Notes

Types of DSN – There are three major types of DSN: User DSNs, System DSNs and File DSNs.
User DSNs are used to create data sources that only that user will need. If you have roaming profiles enabled, they’ll follow the user about too.
System DSNs are machine specific and don’t care who’s logged in. They’re handy for things like reception desks and so such where the user can frequently change but the information available must always be there.
File DSNs are simply files that hold DSN information.

Creating Pass-Through queries in Access 2003

A pass-through query is one where your command is passed from MS Access over to the server to execute. Sounds a little tricky, I know, but you’ll get the hang of it.

OK, lets start with the scenario that you want to access a stored procedure (sometimes called a sproc) called sp_this_one on a SQL Server somewhere in your organisation.
In order to run this sproc you’d normally issue the command EXEC sp_this_one in Query Analyser or some such tool.

In order to run this sproc in access, you just need to make a pass-through query with that exec statement. Easy as.

Assuming you’ve already set up a Data Source Name (or DSN) to your SQL Server’s database (don’t worry, I’ll be writing about how to do that in another post) the process is straightforward.

1. Create a query
2. Close the Tables box
3. Go to the Query Menu
4. Select SQL Specific > Pass-Through Query
5. Click the Properties button on the toolbar
6. ODBC Connection String
7. … button
8. Select data source

And that’s it. All you need to do then is type into the query window the command you’d like to pass through to the server. In our case EXEC sp_this_one.

I realise I’ve been using some rather broad strokes in these instructions, please feel free to email me if you’d like a slightly better description.