Beta

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Chronicle/Rant: Relegated to SQL Server

Chacham (981) writes | more than 5 years ago

User Journal 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.

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 :!!cmd (but backwards compatibility allows for a plain !!dir). Easy, but silly.

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.

cancel ×

10 comments

Sorry! There are no comments related to the filter you selected.

Not quite correct (1)

Marxist Hacker 42 (638312) | more than 5 years ago | (#26713929)

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 easier. You should have seen Server Manager before it (back in SQL 7/6.5 days).

Re:Not quite correct (1)

RailGunner (554645) | more than 5 years ago | (#26714573)

and Transact SQL is as robust as PL/SQL as far as scripting is concerned.

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:Not quite correct (1)

Chacham (981) | more than 5 years ago | (#26714623)

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 selected text"

That's what i meant by "lines". Script means the entire window. Line means just the selected text.

Oh yeah, and Management Studio is actually easier. You should have seen Server Manager before it (back in SQL 7/6.5 days).

That must be a matter of taste. I preferred Query Analyzer.

Seems like... (1)

Bill Dog (726542) | more than 5 years ago | (#26718541)

...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:Seems like... (1)

Chacham (981) | more than 5 years ago | (#26724289)

...using ":r " to run scripts after already in the command shell should work.

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 that bad. I'ts probably more the redundancy that is bothering me. Also, :ed does not accept a parameter, which means opening up an existing file is a two step process, and is probably faster to just open a browser to that directory.

I have SQLCMD set to take up a full screen. Typing ed resizes the window for EDIT. I find it cute that they default to EDIT as opposed to notepad, because SQLCMD was not around before Windows to have any reason to support the DOS editor. However, they by default keep it in the same command window which is a nice effort in itself.

The things i'm looking at now are no reports after a DDL statement:
1> CREATE TABLE a(A INT)
2> GO
1> SELECT * FROM A
2> GO
A
-----------

(0 rows affected)
1> DROP TABLE A;
2> go

Errors are reported, but not success messages. Not quite what i'm used to. Especially as the "1>" shows up with no blank line afterward, i need to realize that the GO worked, and i didn't mistype it or something, so that the "1>" (which is very similar to "3>" or any other number) is a new line.

Seems like having multiple command shells open should work.

Yeah, but the title is SQLCMD and the prompt is "1>" or the like. So, it is not easy to know which window is opened to which environment. There should be a setting for that somewhere; i forget. The actual names of the shortcuts are currently "0 - Express", 1, 2, 3, and started by <CTRL>-<ALT> and the corresponding Number Pad key. (This way 1,2,3 are lower rights, 4,5,6, are higher rights, but 1 and 4 are the same system, as are 2 and 5, and 3 and 6. Which is strange because the login with lower rights in Dev and QA is the login with the higher rights in Prod. As in Prod the lower rights are our personal logins. Or so it was on the last project.)

Re:Seems like... (1)

Bill Dog (726542) | more than 5 years ago | (#26730399)

* 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 instance, within each the command to open that db with sqlcmd. And create a shortcut for each command file, and edit the shortcuts and specify different background colors for each. So for example set Production to dark red, Dev to dark green, and QA to dark yellow. Then always open your sqlcmd window instances via the shortcuts. (This would also be a good way of quickly differentiating sqlcmd windows (colored) from regular cmd.exe ones (uncolored).)

Re:Seems like... (1)

Chacham (981) | more than 5 years ago | (#26732653)

* 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 parameter, so all it does is open the contents of the buffer and put them in the editor's buffer. Overall, that is kind of useless for anything other than saving and editting the current buffer.

* 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.

While that may be true of UNIX commands, that is not usually true for interactive programs. Oracle, DB2, and the like, which all started on UNIX, do indeed report success messages. As does SMTP, HTTP, and most other protocols.

* You could create command files for each db instance, within each the command to open that db with sqlcmd. And create a shortcut for each command file, and edit the shortcuts and specify different background colors for each. So for example set Production to dark red, Dev to dark green, and QA to dark yellow. Then always open your sqlcmd window instances via the shortcuts. (This would also be a good way of quickly differentiating sqlcmd windows (colored) from regular cmd.exe ones (uncolored).)

That is an interesting idea. Though, i prefer my yellow on black setup for all windows.

Thanx for the input. I'm interested in what you have to say here.

Re:Seems like... (1)

Chacham (981) | more than 5 years ago | (#26739549)

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,45,00,58,00,45,00,20,00,25,00,31,00,00,\
    00

Although, i seem to have made two mistakes. One, the default is .sql (that is what edit shows). Two, the default directory is %userprofile%\LOCALS~1\Temp\<filename>. Though, the default directory for SQLCMD (set in the shortcut, tested via !!dir) is my scripts directory. I don't think it would be a good idea to use the temp directory as a script store.

I changed the SQLCMDEDITOR to %SystemRoot%\system32\NOTEPAD.EXE, which works fine. Though the directory is My Documents (according to Notepad's "Save As"). Created a .lnk file with a different directory and setting that as the editor didn't change anything.

Which means, using ed for anything other than the current buffer is more difficult. Instead, i should just open an explorer view to the scripts directory (to which a keyboard shortcut should make it quite easy), leave them without extension and use :r. That is a workable solution, albeit not optimal.

Re:Seems like... (1)

Bill Dog (726542) | more than 5 years ago | (#26742001)

* 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, they talk for a while, then the visitor leaves, and ingenious idiot forgets what he was doing and reflexively hits Yes to saving!)

* Unless I've taken it too literally, I don't think "using ed for anything other than the current buffer is more difficult", I think it's impossible. I believe it only swaps out the current buffer to a temp file in a temp dir, and then swaps it back in, changing nothing in the file the buffer was originally loaded from.

* It's too bad that Notepad ignores a specified startup dir in a shortcut to it. MS seems to have coded it to either save to the dir of the file it was opened with (such as a temp dir when launched by sqlcmd), or to My Documents if launched without a file spec. Maybe vim works better. (Sometimes I have Notepad left set to wrapping "on", such as to read a readme.txt file (paragraph-oriented), but then that might mess up editing and saving a line-oriented, computer-parsed file.)

Re:Seems like... (1)

Chacham (981) | more than 5 years ago | (#26744619)

(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 the file i want. :) It's just a tad more difficult.

It's too bad that Notepad ignores a specified startup dir in a shortcut to it.

Notepad is actually quite good at using the startup directory mentioned in a shortcut. Could the My Documents might have been a cover for a hidden directory or something? My guess is SQLCMD creates the file on disk and then passes it as a parameter to the editor. Thus the temp directory.

Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?
or Connect with...

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>