Does anyone know how to obtain the text for a stored procedure? Dano
mentinoed querying sysobjects, but it does not contain text. Is there
another data dictionary view that I can join sysobjects to to obtain the
text (code) for a procedure? Thanks.
-----Original Message-----
From: Smith Dano
[mailto:mssqldba-ezmlmshield-x61848703.[Email address protected]
Sent: Monday, October 30, 2006 12:04 PM
To: LazyDBA Discussion
Subject: RE: Any rule of thumb?
I don't want to start a religious war, but most of the items you
mentioned can in-fact be done.
It's your experience and comfort level that lead you back to Oracle.
1/2 - SQL is OS dependent - correct
3 - look at "implicit" transations to get the same behavior
4 - xp_cmdshell - I use it to write to files in many ways.
5 - blocking is on purpose - and can be avoided by using proper
transaction isolation level
6 - Any connection can be killed - you just have to know how !
7 - sp_help / sp_helptext
8 - select from sysobjects
9 - got me.. ?
10 - SOUNDEX / PATINDEX ?
Both are great tools.
I think the best way to qualify what should be used is..
What skills do you have in-house already ?
If none, either can be a good idea - if you have skills - use 'Em..
Dano Smith
-----Original Message-----
From: Jeff Seeman
[mailto:mssqldba-ezmlmshield-x21918437.[Email address protected]
Sent: Monday, October 30, 2006 2:38 PM
To: LazyDBA Discussion
Subject: RE: Any rule of thumb?
Importance: Low
Martin:
I have 13 years of Oracle development and DBA experience, and only three
months of SQL Server experience. With that in mind, I'll try not to
sound "religious" in my views, but I much prefer Oracle to SQL Server in
virtually every way. Here are my reasons:
1) SQL Server runs only under Windows, whereas the Oracle
implementations I've maintained have run under Unix, which I consider a
vastly superior operating system. If, however, you have no Unix
experience, you might still consider running Oracle under NT;
2) I consider PL/SQL to be a much simpler, more elegant language than
Transact-SQL. Most of the programmers I know who have used both have
come to the same conclusion. For example, try to find a FOR loop in
Transact-SQL. It doesn't have it; every other language I've ever worked
with does, including PL/SQL. You have to define a length for all
Transact-SQL procedure and function arguments, and that can be extremely
awkward; in PL/SQL you don't, just as you don't in every other language
I've ever used. Data conversions are trivial in PL/SQL, cumbersome and
complex in Transact SQL. Almost any procedural task is simpler in
PL/SQL, such as dynamic SQL. I was astonished at how complex it was,
including requiring a temporary table. Transact-SQL has no exception
handling, whereas Oracle has extensive and simple exception handling.
Oracle error messages are usually informative and tied to a specific
line; SQL Server error messages are vague and often don't match the
offending line;
3) Transaction processing is trivial in Oracle - just issue a commit or
rollback after every DML statement; in SQL Server you have to have to
create a block of code to have the option of rolling back;
4) Transact SQL has no method I know of for writing to or reading a
file; in Oracle it's easy to do so;
5) In SQL Server a query can cause a lock; in Oracle that can't ever
happen: readers don't block writers; writers don't block readers;
6) In SQL Server it's impossible, in my experience, to unlock a DDL
lock, such as that caused by an errant procedure, without rebooting the
server; in Oracle you can almost always identify the source of the lock
and unlock it;
7) in SQL Server it's difficult to obtain the structure of a table or
view without using the GUI; in SQL*Plus you can just type "desc <table>"
to see the structure;
8) SQL Server provides no way to query the data dictionary to see stored
procedure code; in Oracle you can say "select owner,name,line,text from
dba_source where lower(text) like '%<whatever text you're looking for>%'
and you can see every procedure in your instance and lines that contain
that code;
9) SQL Server has no built-in regular expression support; Oracle has
provided such support since Oracle 8i with the package owa_pattern (SQL
Server also has no packages) and in Oracle 9i and above, it's improved
regular expressions with regexp_substr, regexp_instr, and other
excellent functions. You CAN roll your own regular expressions using
.NET, which I've done, but don't you believe it when you read how "easy"
it is. Get ready to learn C#, which is a cumbersome knock-off of Java;
10) SQL Server has no built in approximate matching, i.e., fuzzy
hamming; Oracle has it since Oracle 10.2 with utl_match.
These are only some of the reasons that come immediately to mind in
justifying my preference for Oracle over SQL Server as a database and
application development environment.
Jeff
-----Original Message-----
From: Martin Eklund
[mailto:mssqldba-ezmlmshield-x796818.[Email address protected]
Sent: Monday, October 30, 2006 10:11 AM
To: LazyDBA Discussion
Subject: Any rule of thumb?
Hello
A question for those that operate both Oracle and MSSQL. How do you
choose which database engine to use? Based on size / transactions or
security model or other?
And please, spare me of any religious reasons :-)
// Martin
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page