Journal Chacham's Journal: Chronicle/Rant: Relegated to SQL Server 10
Over a year ago i started at Ford working with Oracle. At the end of the year due to cuts i was let go, but was brought back on in January to a new project. I finally do some of the design so it can be done correctly! It's a rare treat to get in at the beginning and design from scratch. We spoke to the Data Architect (DA) and it looks like he'll be OK with me doing his job. I even got the distinct feeling that he had a clue.
Unfortunately, i have to work with Microsoft SQL Server. I don't like it much. They expect you to use the graphical interface. The provided tool (i just downloaded 2005 Express, expecting to use 2005 here) for command line is SQLCMD. It runs scripts, but once interactive mode is started, it does not. In Oracle's SQL*Plus, i had tens of scripts that made my life easier. It was always @this or @that. Need to check indexes on a TABLE, no problem, @ind tablename. Need to check used space, @space. From simple to complex, @ scripts are one of the things that make SQL*Plus an excellent tool. Yet, nothing like that for SQL Server. It is expected for the user to use the graphical interface in Management Studio. Query Analyzer was integrated into it.
But wait, there's more. SQLCMD does not use ; as a statement terminator. Offically, SQLCMD is a T-SQL executor, so the T-SQL command GO must be used. And, it has to be on its own line. That means that every script is at least two lines. And that is if you want to COMMIT it. Don't want to commit? Well, then start with BEGIN TRANS to let SQL Server know you care about this obscure thing call transactions. And i thought mysql was bad. Sheesh.
To be fair SQLCMD does allow the word GO to be replaced with another token, such as a semi-colon. But it still must be on its own line. I tried / (to match Oracle's version of GO) but the command caused the shortcut to close the window right away. It's a solidus, not a backslash, why doesn't it work?
Microsoft wants all commands besides GO to start with a colon. The run-command token is !!. So, to run dir, the command is
The graphical tool is not too bad. F5 will runs lines or scripts. It just doesn't compare to the ease of use of a DOS window, especially a few DOS windows each for a different environment. On my last project i had a minimum of four windows: local, Dev, QA, and Prod. Then again, in Dev and QA we had two usernames. One with rights for development and one with the application rights. So, when testing a stored PROCEDURE, CREATE with the higher rights, but test with the lower. All in all, a few windows were open. Matching text, and alt-tab and numbered shortcut switching made this very easy to use. Management studio is probably going to be a lot harder. Remembering which tab is which (unless i can get the instance name in a prompt), and switching between them will be very different.
Oh well, i'll just have to get used to it. Now i need to find the best sites to follow. For Oracle i followed Tom Kyte, Richard Foote, and others (and probably will continue to do so), but i need to find the Microsoft equivalents. As long as they support RSS it ought to be easy to follow.
I feel better now.
Not quite correct (Score:2)
It just doesn't conform to SQL normal. A stored procedure in SQL Server is a script- and Transact SQL is as robust as PL/SQL as far as scripting is concerned. You can run a stored procedure by using C-like syntax: procedurename(paramters,...)
Also, if you haven't noticed yet- F5 doesn't just run lines or scripts- but ALSO runs "just selected text", so testing a part of your stored procedure without the create is just as easy as selecting the text and hitting F5.
Oh yeah, and Management Studio is actually e
Re: (Score:2)
I'm going to have to disagree with that... Can't do this in T-SQL:
v_Row SomeTable%ROWTYPE;
In T-SQL, you have to specify each column in it's own variable. PL/SQL and it's cousin PL-PGSQL for Postgres are far better than T-SQL in my humble opinion.
Re: (Score:1)
A stored procedure in SQL Server is a script- and Transact SQL is as robust as PL/SQL as far as scripting is concerned. You can run a stored procedure by using C-like syntax: procedurename(paramters,...)
A script is stored locally. An SP is stored on a particular server.
A script requires no rights to create. An SP requires rights to CREATE.
I'm sure there are other differences, but these come to mind right away.
Also, if you haven't noticed yet- F5 doesn't just run lines or scripts- but ALSO runs "just selecte
Seems like... (Score:1)
...using ":r <filename>" to run scripts after already in the command shell should work.
Seems like having multiple command shells open should work.
As for slightly differing syntax, wah.
Re: (Score:1)
Thanx! I saw :r but apparently it didn't click. I see it, i tested it, it works. That ought to be helpful.
I'm used to SQL*Plus that uses .sql as the extension (which can be changed). Which is nice as i had .sql open up in notepad. SQLCMD does not seem to use any extension by default, which means either the file must be created without an extension, or when using :r i'll have to type it in as well. It's only 4 characters, so its not
Re: (Score:1)
* You could eschew filename extensions and instead provide for identifying the file type by the name of the subdir they reside in, such as "SqlScripts".
* Set its editor env var to notepad or some other editor that doesn't need to resize your cmd window.
* MS SQL Server started out its life (in the 1990's) using a licensed version of Sybase's SQL Server engine, and Sybase was prolly created on UNIX, and on UNIX the convention is to not report success messages.
* You could create command files for each db insta
Re: (Score:1)
* You could eschew filename extensions and instead provide for identifying the file type by the name of the subdir they reside in, such as "SqlScripts".
Already set that up. The problem is that file association is based on extensions in Windows. Hmm... i guess on could make the default action Notepad (* in the registry). Maybe i'll try that.
* Set its editor env var to notepad or some other editor that doesn't need to resize your cmd window.
Yeah, i know. I just thought it was cute. Regardless, it won't take a
Re: (Score:1)
Got it. It required two changes. One, adding Notepad to the "Unknown" type. Two, changing the default to this new action:
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\Unknown\shell]
@="Notepad"
[HKEY_CLASSES_ROOT\Unknown\shell\Notepad]
[HKEY_CLASSES_ROOT\Unknown\shell\Notepad\command]
@=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,74,00,25,\
00,5c,00,73,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,4e,00,4f,00,\
54,00,45,00,50,00,41,00,44,00,2e,00,
Re: (Score:1)
* Interesting that you figured out how to get unassociated file types associated to Notepad -- didn't know this was possible. (The slight danger might be that some curious idiot might be using your computer and see that some binary files in system32 or C:\ are "text files" and open them, find a bunch of garbage and lose interest, they go to hit a keystroke to close Notepad but fumble and hit something else prior to it, causing Notepad to prompt for saving prior to exiting, someone comes by and interrupts, t
Re: (Score:1)
(The slight danger might be
Or, my computer might actually be a virtual terminal running off of million of pentiums. It which case, said user might open calc and decide to divide 5 by 3, causing a massive failure not seen since Bill Gates tried installing a scanner at Comdex.
I guess life is full of risks. :)
I don't think "using ed for anything other than the current buffer is more difficult", I think it's impossible.
Well, i was trying to be exact. I *could* open ed, then use the open dialog to get to