[T-SQL] Proper case function in SQL Server

CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' '
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90
WHILE @ctr <= @len
BEGIN
--This loop will take care of reccuring white spaces
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END
END
RETURN @output
END

[Cognos] Script for renaming prompt buttons

var pstr1=document.formWarpRequest.elements;
for(var i=0;i {
if(pstr1[i].value=='Finish')
{
pstr1[i].value='Run';
}

}

[Cognos] Scripts

Script for Work Wrapping
document.formWarpRequest._oLstChoicescompparam[0].text = 'All';

Script for first day of the month

var pstr1=document.formWarpRequest.elements;
var lastdate=new Date();

lastdate.setDate(lastdate.getDate()-(lastdate.getDate()-1));
var gsMonthNames = new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

for(var i=0;i {
if(pstr1[i].name=='txtDateFromDate')
{
var vDay = lastdate.getDate();
var vMonth = lastdate.getMonth()+1;
var vYearLong = (lastdate.getFullYear());
var vDateString = gsMonthNames[vMonth-1]+" "+vDay+", "+vYearLong;
pstr1[i].value=vDateString;
}

}

Script for last day of the month

var pstr1=document.formWarpRequest.elements;
var lastdate=new Date();

lastdate.setDate(lastdate.getDate());
var gsMonthNames = new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
var gsDay=new Array(31,28,31,30,31,30,31,31,30,31,30,31)
for(var i=0;i {
if(pstr1[i].name=='txtDateToDate')
{
var vDay = lastdate.getDate();
var vMonth = lastdate.getMonth()+1;
var vYearLong = (lastdate.getFullYear());
var vDateString = gsMonthNames[vMonth-1]+" "+gsDay[vMonth-1]+", "+vYearLong;
pstr1[i].value=vDateString;
}

}

[Cognos] Important Material

Path with parameter

http://KHIPC117/cognos8/cgi-bin/cognos.cgi?b_action=xts.run&m=portal/report-viewer.xts&method=execute&m_obj=content/package[@name='VICTORS']/folder[@name='Orders']/report[@name='Order Count by Date Range']&amp;amp;amp;amp;p_ENT_NUM=32&&run.prompt=true&ui.action=run&&nh=0&&tb=0

Path without parameter

http://KHIPC135/cognos8/cgi-bin/cognos.cgi?b_action=xts.run&m=portal/report-viewer.xts&method=execute&m_obj=/content/package[@name='VICTORS']/folder[@name='Clients']/folder[@name='Client Pricing']/report[@name='Client Pricing']&amp;amp;amp;run.prompt=true&ui.action=run&&nh=0&&tb=0

[SQL Server] SQL Injection

A customer asked that we check out his intranet site, which was used by the company's employees and customers. This was part of a larger security review, and though we'd not actually used SQL injection to penetrate a network before, we were pretty familiar with the general concepts. We were completely successful in this engagement, and wanted to recount the steps taken as an illustration.

"SQL Injection" is subset of the an unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises.

We'll note that this was a somewhat winding road with more than one wrong turn, and others with more experience will certainly have different -- and better -- approaches. But the fact that we were successful does suggest that we were not entirely misguided.

There have been other papers on SQL injection, including some that are much more detailed, but this one shows the rationale of discovery as much as the process of exploitation.

The Target Intranet
This appeared to be an entirely custom application, and we had no prior knowledge of the application nor access to the source code: this was a "blind" attack. A bit of poking showed that this server ran Microsoft's IIS 6 along with ASP.NET, and this suggested that the database was Microsoft's SQL server: we believe that these techniques can apply to nearly any web application backed by any SQL server.

The login page had a traditional username-and-password form, but also an email-me-my-password link; the latter proved to be the downfall of the whole system.

When entering an email address, the system presumably looked in the user database for that email address, and mailed something to that address. Since my email address is not found, it wasn't going to send me anything.

So the first test in any SQL-ish form is to enter a single quote as part of the data: the intention is to see if they construct an SQL string literally without sanitizing. When submitting the form with a quote in the email address, we get a 500 error (server failure), and this suggests that the "broken" input is actually being parsed literally. Bingo.

We speculate that the underlying SQL code looks something like this:

SELECT fieldlist
FROM table
WHERE field = '$EMAIL';

Here, $EMAIL is the address submitted on the form by the user, and the larger query provides the quotation marks that set it off as a literal string. We don't know the specific names of the fields or table involved, but we do know their nature, and we'll make some good guesses later.

When we enter steve@unixwiz.net' - note the closing quote mark - this yields constructed SQL:

SELECT fieldlist
FROM table
WHERE field = 'steve@unixwiz.net'';

when this is executed, the SQL parser find the extra quote mark and aborts with a syntax error. How this manifests itself to the user depends on the application's internal error-recovery procedures, but it's usually different from "email address is unknown". This error response is a dead giveaway that user input is not being sanitized properly and that the application is ripe for exploitation.

Since the data we're filling in appears to be in the WHERE clause, let's change the nature of that clause in an SQL legal way and see what happens. By entering anything' OR 'x'='x, the resulting SQL is:

SELECT fieldlist
FROM table
WHERE field = 'anything' OR 'x'='x';

Because the application is not really thinking about the query - merely constructing a string - our use of quotes has turned a single-component WHERE clause into a two-component one, and the 'x'='x' clause is guaranteed to be true no matter what the first clause is (there is a better approach for this "always true" part that we'll touch on later).

But unlike the "real" query, which should return only a single item each time, this version will essentially return every item in the members database. The only way to find out what the application will do in this circumstance is to try it. Doing so, we were greeted with:


--------------------------------------------------------------------------------
Your login information has been mailed to random.person@example.com.
--------------------------------------------------------------------------------
Our best guess is that it's the first record returned by the query, effectively an entry taken at random. This person really did get this forgotten-password link via email, which will probably come as surprise to him and may raise warning flags somewhere.

We now know that we're able to manipulate the query to our own ends, though we still don't know much about the parts of it we cannot see. But we have observed three different responses to our various inputs:

"Your login information has been mailed to email"
"We don't recognize your email address"
Server error
The first two are responses to well-formed SQL, while the latter is for bad SQL: this distinction will be very useful when trying to guess the structure of the query.

Schema field mapping
The first steps are to guess some field names: we're reasonably sure that the query includes "email address" and "password", and there may be things like "US Mail address" or "userid" or "phone number". We'd dearly love to perform a SHOW TABLE, but in addition to not knowing the name of the table, there is no obvious vehicle to get the output of this command routed to us.

So we'll do it in steps. In each case, we'll show the whole query as we know it, with our own snippets shown specially. We know that the tail end of the query is a comparison with the email address, so let's guess email as the name of the field:

SELECT fieldlist
FROM table
WHERE field = 'x' AND email IS NULL; --';

The intent is to use a proposed field name (email) in the constructed query and find out if the SQL is valid or not. We don't care about matching the email address (which is why we use a dummy 'x'), and the -- marks the start of an SQL comment. This is an effective way to "consume" the final quote provided by application and not worry about matching them.

If we get a server error, it means our SQL is malformed and a syntax error was thrown: it's most likely due to a bad field name. If we get any kind of valid response, we guessed the name correctly. This is the case whether we get the "email unknown" or "password was sent" response.

Note, however, that we use the AND conjunction instead of OR: this is intentional. In the SQL schema mapping phase, we're not really concerned with guessing any particular email addresses, and we do not want random users inundated with "here is your password" emails from the application - this will surely raise suspicions to no good purpose. By using the AND conjunction with an email address that couldn't ever be valid, we're sure that the query will always return zero rows and never generate a password-reminder email.

Submitting the above snippet indeed gave us the "email address unknown" response, so now we know that the email address is stored in a field email. If this hadn't worked, we'd have tried email_address or mail or the like. This process will involve quite a lot of guessing.

Next we'll guess some other obvious names: password, user ID, name, and the like. These are all done one at a time, and anything other than "server failure" means we guessed the name correctly.

SELECT fieldlist
FROM table
WHERE email = 'x' AND userid IS NULL; --';

As a result of this process, we found several valid field names:

email
passwd
login_id
full_name
There are certainly more (and a good source of clues is the names of the fields on forms), but a bit of digging did not discover any. But we still don't know the name of the table that these fields are found in - how to find out?

Finding the table name
The application's built-in query already has the table name built into it, but we don't know what that name is: there are several approaches for finding that (and other) table names. The one we took was to rely on a subselect.

A standalone query of

SELECT COUNT(*) FROM tabname

Returns the number of records in that table, and of course fails if the table name is unknown. We can build this into our string to probe for the table name:

SELECT email, passwd, login_id, full_name
FROM table
WHERE email = 'x' AND 1=(SELECT COUNT(*) FROM tabname); --';

We don't care how many records are there, of course, only whether the table name is valid or not. By iterating over several guesses, we eventually determined that members was a valid table in the database. But is it the table used in this query? For that we need yet another test using table.field notation: it only works for tables that are actually part of this query, not merely that the table exists.

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x' AND members.email IS NULL; --';

When this returned "Email unknown", it confirmed that our SQL was well formed and that we had properly guessed the table name. This will be important later, but we instead took a different approach in the interim.

Finding some users
At this point we have a partial idea of the structure of the members table, but we only know of one username: the random member who got our initial "Here is your password" email. Recall that we never received the message itself, only the address it was sent to. We'd like to get some more names to work with, preferably those likely to have access to more data.

The first place to start, of course, is the company's website to find who is who: the "About us" or "Contact" pages often list who's running the place. Many of these contain email addresses, but even those that don't list them can give us some clues which allow us to find them with our tool.

The idea is to submit a query that uses the LIKE clause, allowing us to do partial matches of names or email addresses in the database, each time triggering the "We sent your password" message and email. Warning: though this reveals an email address each time we run it, it also actually sends that email, which may raise suspicions. This suggests that we take it easy.

We can do the query on email name or full name (or presumably other information), each time putting in the % wildcards that LIKE supports:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x' OR full_name LIKE '%Bob%';

Keep in mind that even though there may be more than one "Bob", we only get to see one of them: this suggests refining our LIKE clause narrowly.

Ultimately, we may only need one valid email address to leverage our way in.

Brute-force password guessing
One can certainly attempt brute-force guessing of passwords at the main login page, but many systems make an effort to detect or even prevent this. There could be logfiles, account lockouts, or other devices that would substantially impede our efforts, but because of the non-sanitized inputs, we have another avenue that is much less likely to be so protected.

We'll instead do actual password testing in our snippet by including the email name and password directly. In our example, we'll use our victim, bob@example.com and try multiple passwords.

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'bob@example.com' AND passwd = 'hello123';

This is clearly well-formed SQL, so we don't expect to see any server errors, and we'll know we found the password when we receive the "your password has been mailed to you" message. Our mark has now been tipped off, but we do have his password.

This procedure can be automated with scripting in perl, and though we were in the process of creating this script, we ended up going down another road before actually trying it.

The database isn't readonly
So far, we have done nothing but query the database, and even though a SELECT is readonly, that doesn't mean that SQL is. SQL uses the semicolon for statement termination, and if the input is not sanitized properly, there may be nothing that prevents us from stringing our own unrelated command at the end of the query.

The most drastic example is:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x'; DROP TABLE members; --'; -- Boom!

The first part provides a dummy email address -- 'x' -- and we don't care what this query returns: we're just getting it out of the way so we can introduce an unrelated SQL command. This one attempts to drop (delete) the entire members table, which really doesn't seem too sporting.

This shows that not only can we run separate SQL commands, but we can also modify the database. This is promising.

Adding a new member
Given that we know the partial structure of the members table, it seems like a plausible approach to attempt adding a new record to that table: if this works, we'll simply be able to login directly with our newly-inserted credentials.

This, not surprisingly, takes a bit more SQL, and we've wrapped it over several lines for ease of presentation, but our part is still one contiguous string:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';
INSERT INTO members ('email','passwd','login_id','full_name')
VALUES ('steve@unixwiz.net','hello','steve','Steve Friedl');--';

Even if we have actually gotten our field and table names right, several things could get in our way of a successful attack:

We might not have enough room in the web form to enter this much text directly (though this can be worked around via scripting, it's much less convenient).
The web application user might not have INSERT permission on the members table.
There are undoubtedly other fields in the members table, and some may require initial values, causing the INSERT to fail.
Even if we manage to insert a new record, the application itself might not behave well due to the auto-inserted NULL fields that we didn't provide values for.
A valid "member" might require not only a record in the members table, but associated information in other tables (say, "accessrights"), so adding to one table alone might not be sufficient.
In the case at hand, we hit a roadblock on either #4 or #5 - we can't really be sure -- because when going to the main login page and entering in the above username + password, a server error was returned. This suggests that fields we did not populate were vital, but nevertheless not handled properly.

A possible approach here is attempting to guess the other fields, but this promises to be a long and laborious process: though we may be able to guess other "obvious" fields, it's very hard to imagine the bigger-picture organization of this application.

We ended up going down a different road.

Mail me a password
We then realized that though we are not able to add a new record to the members database, we can modify an existing one, and this proved to be the approach that gained us entry.

From a previous step, we knew that bob@example.com had an account on the system, and we used our SQL injection to update his database record with our email address:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';
UPDATE members
SET email = 'steve@unixwiz.net'
WHERE email = 'bob@example.com';

After running this, we of course received the "we didn't know your email address", but this was expected due to the dummy email address provided. The UPDATE wouldn't have registered with the application, so it executed quietly.

We then used the regular "I lost my password" link - with the updated email address - and a minute later received this email:

From: system@example.com
To: steve@unixwiz.net
Subject: Intranet login

This email is in response to your request for your Intranet log in information.
Your User ID is: bob
Your password is: hello

Now it was now just a matter of following the standard login process to access the system as a high-ranked MIS staffer, and this was far superior to a perhaps-limited user that we might have created with our INSERT approach.

We found the intranet site to be quite comprehensive, and it included - among other things - a list of all the users. It's a fair bet that many Intranet sites also have accounts on the corporate Windows network, and perhaps some of them have used the same password in both places. Since it's clear that we have an easy way to retrieve any Intranet password, and since we had located an open PPTP VPN port on the corporate firewall, it should be straightforward to attempt this kind of access.

We had done a spot check on a few accounts without success, and we can't really know whether it's "bad password" or "the Intranet account name differs from the Windows account name". But we think that automated tools could make some of this easier.

Other Approaches
In this particular engagement, we obtained enough access that we did not feel the need to do much more, but other steps could have been taken. We'll touch on the ones that we can think of now, though we are quite certain that this is not comprehensive.

We are also aware that not all approaches work with all databases, and we can touch on some of them here.

Use xp_cmdshell
Microsoft's SQL Server supports a stored procedure xp_cmdshell that permits what amounts to arbitrary command execution, and if this is permitted to the web user, complete compromise of the webserver is inevitable.
What we had done so far was limited to the web application and the underlying database, but if we can run commands, the webserver itself cannot help but be compromised. Access to xp_cmdshell is usually limited to administrative accounts, but it's possible to grant it to lesser users.
Map out more database structure
Though this particular application provided such a rich post-login environment that it didn't really seem necessary to dig further, in other more limited environments this may not have been sufficient.
Being able to systematically map out the available schema, including tables and their field structure, can't help but provide more avenues for compromise of the application.
One could probably gather more hints about the structure from other aspects of the website (e.g., is there a "leave a comment" page? Are there "support forums"?). Clearly, this is highly dependent on the application and it relies very much on making good guesses.
Mitigations
We believe that web application developers often simply do not think about "surprise inputs", but security people do (including the bad guys), so there are three broad approaches that can be applied here.

Sanitize the input
It's absolutely vital to sanitize user inputs to insure that they do not contain dangerous codes, whether to the SQL server or to HTML itself. One's first idea is to strip out "bad stuff", such as quotes or semicolons or escapes, but this is a misguided attempt. Though it's easy to point out some dangerous characters, it's harder to point to all of them.
The language of the web is full of special characters and strange markup (including alternate ways of representing the same characters), and efforts to authoritatively identify all "bad stuff" are unlikely to be successful.
Instead, rather than "remove known bad data", it's better to "remove everything but known good data": this distinction is crucial. Since - in our example - an email address can contain only these characters:
abcdefghijklmnopqrstuvwxyz
ABCDEFGHIJKLMNOPQRSTUVWXYZ
0123456789
@.-_+

There is really no benefit in allowing characters that could not be valid, and rejecting them early - presumably with an error message - not only helps forestall SQL Injection, but also catches mere typos early rather than stores them into the database.
Sidebar on email addresses
--------------------------------------------------------------------------------

It's important to note here that email addresses in particular are troublesome to validate programmatically, because everybody seems to have his own idea about what makes one "valid", and it's a shame to exclude a good email address because it contains a character you didn't think about.

The only real authority is RFC 2822 (which encompasses the more familiar RFC822), and it includes a fairly expansive definition of what's allowed. The truly pedantic may well wish to accept email addresses with ampersands and asterisks (among other things) as valid, but others - including this author - are satisfied with a reasonable subset that includes "most" email addresses.

Those taking a more restrictive approach ought to be fully aware of the consequences of excluding these addresses, especially considering that better techniques (prepare/execute, stored procedures) obviate the security concerns which those "odd" characters present.

--------------------------------------------------------------------------------
Be aware that "sanitizing the input" doesn't mean merely "remove the quotes", because even "regular" characters can be troublesome. In an example where an integer ID value is being compared against the user input (say, a numeric PIN):
SELECT fieldlist
FROM table
WHERE id = 23 OR 1=1; -- Boom! Always matches!

In practice, however, this approach is highly limited because there are so few fields for which it's possible to outright exclude many of the dangerous characters. For "dates" or "email addresses" or "integers" it may have merit, but for any kind of real application, one simply cannot avoid the other mitigations.
Escape/Quotesafe the input
Even if one might be able to sanitize a phone number or email address, one cannot take this approach with a "name" field lest one wishes to exclude the likes of Bill O'Reilly from one's application: a quote is simply a valid character for this field.
One includes an actual single quote in an SQL string by putting two of them together, so this suggests the obvious - but wrong! - technique of preprocessing every string to replicate the single quotes:
SELECT fieldlist
FROM customers
WHERE name = 'Bill O''Reilly'; -- works OK

However, this naïve approach can be beaten because most databases support other string escape mechanisms. MySQL, for instance, also permits \' to escape a quote, so after input of \'; DROP TABLE users; -- is "protected" by doubling the quotes, we get:
SELECT fieldlist
FROM customers
WHERE name = '\''; DROP TABLE users; --'; -- Boom!

The expression '\'' is a complete string (containing just one single quote), and the usual SQL shenanigans follow. It doesn't stop with backslashes either: there is Unicode, other encodings, and parsing oddities all hiding in the weeds to trip up the application designer.
Getting quotes right is notoriously difficult, which is why many database interface languages provide a function that does it for you. When the same internal code is used for "string quoting" and "string parsing", it's much more likely that the process will be done properly and safely.
Some examples are the MySQL function mysql_real_escape_string() and perl DBD method $dbh->quote($value).
These methods must be used.
Use bound parameters (the PREPARE statement)
Though quotesafing is a good mechanism, we're still in the area of "considering user input as SQL", and a much better approach exists: bound parameters, which are supported by essentially all database programming interfaces. In this technique, an SQL statement string is created with placeholders - a question mark for each parameter - and it's compiled ("prepared", in SQL parlance) into an internal form.
Later, this prepared query is "executed" with a list of parameters:
Example in perl
$sth = $dbh->prepare("SELECT email, userid FROM members WHERE email = ?;");

$sth->execute($email);

Thanks to Stefan Wagner, this demonstrates bound parameters in Java:
Insecure version
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery("SELECT email FROM member WHERE name = "
+ formField); // *boom*

Secure version
PreparedStatement ps = connection.prepareStatement(
"SELECT email FROM member WHERE name = ?");
ps.setString(1, formField);
ResultSet rs = ps.executeQuery();

Here, $email is the data obtained from the user's form, and it is passed as positional parameter #1 (the first question mark), and at no point do the contents of this variable have anything to do with SQL statement parsing. Quotes, semicolons, backslashes, SQL comment notation - none of this has any impact, because it's "just data". There simply is nothing to subvert, so the application is be largely immune to SQL injection attacks.
There also may be some performance benefits if this prepared query is reused multiple times (it only has to be parsed once), but this is minor compared to the enormous security benefits. This is probably the single most important step one can take to secure a web application.
Limit database permissions and segregate users
In the case at hand, we observed just two interactions that are made not in the context of a logged-in user: "log in" and "send me password". The web application ought to use a database connection with the most limited rights possible: query-only access to the members table, and no access to any other table.
The effect here is that even a "successful" SQL injection attack is going to have much more limited success. Here, we'd not have been able to do the UPDATE request that ultimately granted us access, so we'd have had to resort to other avenues.
Once the web application determined that a set of valid credentials had been passed via the login form, it would then switch that session to a database connection with more rights.
It should go almost without saying that sa rights should never be used for any web-based application.
Use stored procedures for database access
When the database server supports them, use stored procedures for performing access on the application's behalf, which can eliminate SQL entirely (assuming the stored procedures themselves are written properly).
By encapsulating the rules for a certain action - query, update, delete, etc. - into a single procedure, it can be tested and documented on a standalone basis and business rules enforced (for instance, the "add new order" procedure might reject that order if the customer were over his credit limit).
For simple queries this might be only a minor benefit, but as the operations become more complicated (or are used in more than one place), having a single definition for the operation means it's going to be more robust and easier to maintain.
Note: it's always possible to write a stored procedure that itself constructs a query dynamically: this provides no protection against SQL Injection - it's only proper binding with prepare/execute or direct SQL statements with bound variables that provide this protection.
Isolate the webserver
Even having taken all these mitigation steps, it's nevertheless still possible to miss something and leave the server open to compromise. One ought to design the network infrastructure to assume that the bad guy will have full administrator access to the machine, and then attempt to limit how that can be leveraged to compromise other things.
For instance, putting the machine in a DMZ with extremely limited pinholes "inside" the network means that even getting complete control of the webserver doesn't automatically grant full access to everything else. This won't stop everything, of course, but it makes it a lot harder.
Configure error reporting
The default error reporting for some frameworks includes developer debugging information, and this cannot be shown to outside users. Imagine how much easier a time it makes for an attacker if the full query is shown, pointing to the syntax error involved.
This information is useful to developers, but it should be restricted - if possible - to just internal users.

[T-SQL]STORE PROCEDURE TO REMOVE ALL Connected Database users

--STORE PROCEDURE TO REMOVE ALL Connected Database users

CREATE PROCEDURE dbo.clearDBUsers
@dbName SYSNAME
AS
BEGIN
SET NOCOUNT ON

DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)

SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID

PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'

WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
GO

[T-SQL] Row num in sql server

Select IDENTITY(int, 1, 1) as row_num,Region_Code,Region_Name
into #temp_region
from ADM_REGIONS

Select * from #temp_region

Migrating from Oracle to SQL Server

Migrating from Oracle to SQL Server

In Microsoft public newsgroups, I've been observing a recent increase in the number of questions that deal with migrating from Oracle to SQL Server. It is not an easy task to migrate your Oracle database and applications to SQL Server, as there are major architectural differences between SQL Server and Oracle. Further, Oracle's PL/SQL is vastly different from Microsoft's Transact-SQL (T-SQL).

The most commonly asked question is, "Is there a tool that I can use, to translate my PL/SQL scripts, stored procedures, triggers and functions into T-SQL?" Sadly, the answer is No. There is no magic tool that can convert all your PL/SQL code to T-SQL. Even if such a tool exists, I can guarantee that it will not work in all situations. So, migrating or porting your PL/SQL code to T-SQL is a painfully manual process. The more standard (ANSI SQL) SQL you write, the more easier it is to translate, as ANSI SQL is more likely to work independant of the RDBMS. However, it need not be that manual anymore. I recently learned about a tool from DBBest.com. Do check it out! Also, do not forget to check out SwisSQL Database Migration tools.

If you are tasked with porting or migrating an Oracle database application to Microsoft SQL Server, the following are the primary resources you should approach for help and migration guidelines:

White Paper: Migrating Oracle Databases to SQL Server 2000
Read chapter 7 (Migrating Oracle Databases to SQL Server 2000) from this very useful book: Microsoft SQL Server 2000 Resource Kit

Click here to read my review of this book

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca



At the end of this article, I will provide you with more links to books, that are useful for Oracle DBAs and developers, entering the Microsoft SQL Server world.

The following table addresses the most frequently asked questions (FAQ), related to migrating from Oracle to SQL Server. This is list is by no means complete and will be updated periodically.

Oracle SQL Server More Information
%TYPE data type No equivalent The %TYPE data type of Oracle, lets you create a variable and have that variable's data type be defined by a table or view column or a PL/SQL package variable.

There is no equivalent for Oracle's %TYPE datatype in T-SQL, but it can be simulated (not very conveniently though) using User Defined Data types (UDT). Here is an example:

EXEC sp_addtype 'MyType', 'smallint', NULL

CREATE TABLE MyTable (i MyType)

CREATE PROC MyProc
AS
BEGIN
DECLARE @i MyType
END

BEFORE triggers INSTEAD OF triggers Use INSTEAD OF trigger in SQL Server as an equivalent to Oracle's BEFORE trigger.

For more information on INSTEAD OF triggers, see SQL Server Books Online

DECODE() function CASE expression DECODE can be conveniently simulated using the T-SQL CASE expression. Here's an example:

SELECT Sport,
CASE Sport
WHEN 'Cricket' THEN 'England'
WHEN 'Hockey' THEN 'India'
WHEN 'Base Ball' THEN 'America'
ELSE NULL
END AS 'Originating Country'
FROM Sports


DESCRIBE sp_help or sp_columns There are a lot of alternatives for Oracle's DESCRIBE, in SQL Server. You could use the system stored procedure sp_help for detailed information about a table's columns and other properties.

If sp_help is providing you with too much information, then try the ODBC catalog stored procedure, sp_columns.

There are a bunch of other useful sp_help* stored procedures available in SQL Server. You can find more information about those in SQL Server Books Online.

If none of those procedures are suitable for your requirements, then you could query the system view INFORMATION_SCHEMA.COLUMNS, to get the desired information. You could wrap your code inside a stored procedure named DESCRIBE, if you wish.

As a last resort, you could even query system tables like sysobjects and syscolumns, but this is not a recommended approach.

DUAL table No equivalent There is no DUAL table in SQL Server. In fact, you don't need one in SQL Server, as you can have a SELECT statement without a FROM clause.

For example, consider the following SELECT statement in Oracle:

SELECT 'Something'
FROM DUAL

In SQL Server, the same result can be obtained by the following command:

SELECT 'Something'

If you are porting some code from Oracle into SQL Server and if you don't want to remove all references to DUAL table, then just create a DUAL table in your database using the following commands:

CREATE TABLE DUAL
(
DUMMY varchar(1)
)

INSERT INTO DUAL (DUMMY) VALUES ('X')

INTERSECT operator Not supported Use EXISTS clause to generate the same result.

The following example illustrates the simulation of Oracle's INTERSECT operator:

SELECT OrderID, OrderDate
FROM Orders O
WHERE EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

MINUS operator Not supported Use NOT EXISTS clause in your SELECT statement to generate the same result.

The following example illustrates the simulation of Oracle's MINUS operator:

SELECT OrderID, OrderDate
FROM Orders O
WHERE NOT EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

Nested tables Not supported Oracle 8i and prior versions didn't support this feature and is introduced in Oracle 9i. This feature basically enables you to store a table, within a column. It is like having an array of records in your database columns.

SQL Server has no concept of nested tables.

As a workaround, You could store your sub-tables or child tables in the form of XML inside a char, nchar, varchar, nvarchar, text or ntext type column, and parse it as needed, at runtime. See OPENXML, sp_xml_preparedocument, sp_xml_removedocument in SQL Server 2000 Books Online.

Another possible workaround would be to store comma separated values (CSV).

Note that this is against the basic rules of normalization. Columns are nomore atomic, with nested tables.

From a design point of view, best thing to do would be, to create different tables for representing different entities and link them with primary and foreign key relationships. This will also make searching easier.

Oracle SQL Server :-)

Packages Not supported No equivalent in SQL Server for Oracle's Packages and Package variables concept

PL/SQL T-SQL Every database product implements and extends the standard SQL. Oracle's implementation of SQL is called PL/SQL, while Microsoft's is called T-SQL (Transact-SQL)

Row level security No equivalent Though there is no inbuilt support in SQL Server for row level permissions, you can implement it using view and system functions.

For more information and a working example, read this article:

Implementing row level permissions/security in SQL Server databases

rownum pseudo column No equivalent Though there is no rownum or rowid in SQL Server, there are several ways in which a row number can be generated.

For some examples, read this article:

Q186133 INF: How to Dynamically Number Rows in a Select Statement


SELECT...FOR UPDATE UPDLOCK hint Use the locking hint UPDLOCK in your SELECT statement.

See SQL Server Books Online for more information.

Sequences IDENTITY It is much simpler in SQL Server to generate a sequence value for a primary key or a non-key column.

You just need to turn on the IDENTITY property of column. IDENTITY property can be set to columns of the following data types: tinyint, smallint, int, bigint, decimal, numeric

Try this example to see how it works:

CREATE TABLE foo
(
i int IDENTITY(1, 1)
j int
)

INSERT INTO foo (j) VALUES (911)
INSERT INTO foo (j) VALUES (999)

SQL *Plus Query Analyzer For connecting to SQL Server and executing queries and modifying data, use the built-in Query Analyzer. It is much more powerful and friendlier than Oracle's SQL *Plus

START WITH...CONNECT BY clause No equivalent Though there's no direct equivalent in T-SQL for Oracle's START WITH...CONNECT BY, there are several ways and efficient techniques for processing and querying hierarcical data.

For more information, read this article:

Working with hierarchical data in SQL Server databases


Synonym Views You can simulate Oracle Synonyms in SQL Server using Views. For example, the following creates a view that returns the OrderID and OrderDate from Orders table.

CREATE VIEW vOrders
AS
SELECT OrderID, OrderDate
FROM Orders

Now you can select data from the view, using the following SELECT statement:

SELECT * FROM vOrders

The following example will create a view which is equivalent to Oracles TABS synonym or USER_TABLES data dictionary view (For simplicity, I am only including the table name in the view definition):

CREATE VIEW TABS
AS
SELECT name AS table_name
FROM sysobjects
WHERE type = 'U'

Now you can execute the following SELECT statement to return all user table names:

SELECT table_name
FROM TABS

List of Serial of all Softwares

ACDSee 4 O-Power pack suite .......708-801- 298-633-437- 541
* Adobe Acrobat 6.0 profesional( s)...1118- 1911-4821- 7104-6966- 4189
* Adobe Acrobat 6.0 profesional( 5cd).1118- 1358-8576- 4538-6053- 6920
* Adobe Acrobat 6.0 profesional. .....1118- 1827-3832- 9514-8390- 2863
* Adobe Acrobat 6.0 profesional. .....1118- 1116-2299- 4729-9672- 0317
* Adobe Acrobat 6.0 profesional. .....1118- 1434-0839- 7048-0604- 8104
* Adobe Acrobat 6.0 profesional. .....1118- 1094-5318- 4976-5520- 0966
* Adobe Acrobat 6.0 profesional. .....1118- 1048-3029- 1402-5780- 8390
* Adobe Acrobat 5.0 ............ .....KWW500R7150 122-128
* Adobe Acrobat 5.0 unl. ............ KWW500U6814339- 105
* Adobe Acrobat Capture 3.0 .........WJW300R122 9020-850
* Adobe Audition 1.0 ............ ....1135- 1819-7359- 2042-7491- 0497
* Adobe After Effects 6.0 ...........1063- 1253-5173- 5245-2713- 1998
* Adobe After Effects 5.5 ...........EWW470R1 001999-030- 259
* Adobe After Effects 5.0 unl. ......EWW500U291775 8-331
* Adobe Creative Suite (2cd)....... ..1131-1028- 1537-2956- 7072-0359
* Adobe Creative Suite Premium (5cd).1131-1126- 1139-6167- 4149-2097
* Adobe Encore DVD 1.0 ............ ..11191840807128 9050282344
* Adobe Elements 2.0 ............ ....1057- 4427-8084- 7059-3638- 1053
* Adobe Frame Maker 7.0 ............ .103012088265 345892877077
* Adobe Go Live CS (5cd)....... ......1033- 4126-7217- 4976-2131- 6373
* Adobe Go Live CS (5cd)....... ......1033- 1477-9876- 4091-8712- 2371
* Adobe Go Live 6.0 ............ .....1033- 1127-2515- 4365-9787- 1611
* Adobe Go Live 5.0 unl. ............ GJW500U1811409- 339
* Adobe Illustrator CS 11.0 .........1034- 1455-6929- 6298-0325- 2279
* Adobe Illustrator CS 11.0 .........1034- 1050-8697- 5540-0366- 7862
* Adobe Illustrator 10.0 ............ 1034-1003- 4400-0000- 1115-2040
* Adobe Illustrator 9.0 unl. ........ABW900U2925 1590-898- 567
* Adobe InCopy CS ............ .......1036- 4425-0465- 5818-2112- 5970
* Adobe In Design CS ............ ....1037- 1064-1247- 2171-9446- 8530
* Adobe In Design 2.0 ............ ...1037-1206- 1879-8486- 1803-3172
* Adobe Live Motion 2.0 ............ .1038-1122- 4403-2805- 2740-1096
* Adobe Page Maker 7.0 ............ ..1039-1121- 2998-7586- 7388-7545
* Adobe Page Maker 6.0 unl. .........03W600U113 3114-901
* Adobe Photoshop CS.......... .......1131- 1028-1537- 2956-7072- 0359
* Adobe Photoshop 7.0 ............ ...1045-1209- 6738-4668- 7696-2783
* Adobe Photoshop 6.0 ............ ...PWW600R710546 7-948
* Adobe Photoshop 6.0 unl. ..........PWW600U75 83048-500
* Adobe Photoshop 5.0 ............ ...PWW400r710633 7-339
* Adobe Photoshop Elements 2.0 ......1057-4427- 8084-7059- 3638-1053
* Adobe Photoshop Album 2.0......... .1078-1206- 2363-5688- 9134-1127
* Adobe Premiere Pro 7.0 ............ 1132-1381- 7346-2847- 2617-6783
* Adobe Premiere 6.5 ............ ....MBE600R71000 24-900-960
* Adobe Premiere 6.0 ............ ....MBW600R71007 65-881
* Adobe Premiere 6.0 unl. ...........MBF600U1 840816-346
* Adobe Premiere 5.5 ............ ....MBF420U30002 05-940
* Adobe Premiere 5.0 ............ ....MBF420U30002 05-940
* Adobe Streamline 4.0 unl. .........SBW400U847 5395-113
* Adobe Version Cue CS.......... .....1131- 1495-5771- 5712-7976- 4172
* Audio Grabber 1.8 ............ .....D40D6565B4
* AutoCAD 2002 ............ ......... .400-12345678 ,(cd-key: T4ED6P)
* Autodesk Building Mechanical 2.0 ..400-00436865- VQVR56
* Autodesk Building Systems 2004.....400- 12345678 + crack
* Autodesk Envision 8.0......... .....400- 12345678
* Autodesk Invertor 5.0......... .....444- 44444444 ,(cd-key: XA2ZDX)
* Autodesk Mechanical Desktop 4.0 ...112-19990930 (5PPSUV)+(CCGMPVEAA TQN)
* Autodesk CIVIL 3D 2004........ .....400- 00000000
* Black Ice Defender 2.1......... ....FFE1E- RS-DCEF0
* Boris RED 3.0 ............ ......... AG0303-0644777- 15M17
* Boris FX 6.0 ............ ......... .123456-123456- 12349
* Borland J++ Builder..... ......... ..XA52-?NPKV- GCQZW ,(cd-key: M69-TET)
* Borland J-Builder 6.0 ent......... .8UQG-SPX3VN- NSKA7M-MWAV, (auth-key:
7AH-ET7)
* Borland J-Builder 5.0 ............ .XA52-?NPKV- GCQZW ,(cd-key: M69-TET)
* Borland J-Builder 4.0 ............ .xd62-2222y- qpgr? ,(cd-key: c3t-mdz)
* Borland J-Builder 4.0 ............ .xa22-?hrs5- 2ubgs ,(cd-key: f2j-46g)
* Borland Delphi 7.0 ent.suite... ....6AMD- PKG68E-DB8PP7- N7GE (Key:S6J-8AW)
* Borland Delphi 7.0 ............ ....6AKD- PD29Q9-RDF? JQ-X65Z (Key:QX8-EEC)
* Borland Delphi 6.0 ............ ....Z9J8- PUM4N-C6GZQ ,(cd-key: RW2-7JW)
* Borland Delphi 6.0 ............ ....j64m- dpu2t-srbea ,(cd-key: c68-p3b)
* Borland Delphi 6.0 ent. ...........p48n- b77mf-yqt7g ,(cd-key: my9-rne)
* Borland Delphi 5.0 ............ ....111-111- 1111 ,(cd-key: FEX1-91X0)
* Borland Delphi 5.0 ent. ...........200- 002-0839 ,(cd-key: 3CX0-91X0)
* Britannica 2004 ............ .......111111111 1111111
* Britannica 2003 ............ .......C3CA1ACA0 0012345
* Britannica 2003 ............ .......C3BA1AHA0 0000017
* Cakewalk Music Creator Pro 24......MC242. 21-002222
* Cakewalk MediaWorks.. ......... .....CWMW1. 00-009968
7135be-1339bd- 1a33-5c670
* CakeWalk Home Studio XL ...........CWHX1. 02-002397
* CakeWalk Pyro 2004........ ......... CWPY3.00- 030499
* CakeWalk Pyro 2003 ............ ....CWPY2. 00-006173
* CakeWalk Plasma ............ .......cwpl1. 02-001778
* Cakewalk Sonar 3 Producer.... ......CWSP3. 00-031374
* Cakewalk Sonar 3 VSamler3.... ......VS3- 6B2736CC- FE164B58- 987E5FCC- 71F211B7
* Cakewalk Sonar XL 2.0 ............ .CWSX2.00- 002841
* CakeWalk Guitar Tracks ver2 .......CWGT2. 00-017973 (cd-key:
442-200177-7700)
* CANOPUS Video FX Transitions. ......PA- VFTRT-1X1XD- 0XX3P-XX4XE
* Copernic 2000 plus........ ......... 032-622305744 ,(John
Peterson(Johnson) )
* CorelDraw Graphics Suite 12 (3xCD).DR12WEX- 1504397-KTY
* CorelDraw 11.0 ............ ........DR11CRD- 0012082-DGW
* CorelDraw 10.0 ............ ........D10NR- 3284253T76
* Corel WordPerfect Office 11........WS11WRD- 2452003-TTF
* Corel XMETAL 4.03........ ......... .XA04WBZ- 1101000-LCW
* Corel Painter 8.0 ............ .....PF08CUM- 0516190-DCN
* Contrast Plan Plus Millenium... ....P2-2437
* CyberLink PowerProducer 2.0 .......RR3161055526 2035
* CyberLink PowerDirector 3.0 .......RW8723538864 4278
* CyberLink PowerCinema 3.0 .........CN49313486 814599
* CyberLink PowerDVD 5 Gold .........MV39749288 155697
* Deep Space Explorer ............ ...D4-2188
* DesignCAD 3D Max 12 ............ ...1206-0426- 5400
* Drive Image 7.0......... ......... ..AY3A-PH7J- 3DM6-R7KR- 6XJT
* Easy CD Creator 5.0 platinium... ...P-7TGQ4- G06CM-N5G1L
* ejay DJ Mix Station 2........... ...CYTC-7R6B- 6N74A
* Encyclopedia of Circuits Suite.....DODROXHY
* Eovia Carrara Studio 3.0......... ..RF30CRD- 0000818-VVS
* Imaginate 1.0 ............ ......... PA-IMISO- XG44L-XXG0L- 4G044
* Intuitive MX ............ ......... .NEHRY-SKTPE- ZUHWU-SXUAW
* Instant Copy 7.0 ............ ......AAOUQ- AAJKP-MBLFQ- YNPHA-BIGCA
* kurs AUTOCAD (Multimedia) ......... .ser.br:9830H062 691 / KON.BR:39E04L23 /
datum 21.12.1999/ rez.1024x768
* Macromedia Studio MX 2004........ ..WSD700- 04068-87200- 78579
* Macromedia Authorware 6.0 .........APW600- 08018-27284- 59356
* Macromedia Contribute MX 2004......CTO200- 00256-07216- 25510
* Macromedia Director Studio 8.5 ....WDW850-02044- 87235-26475
* Macromedia DreamWeaver MX.......... WSW600-03579- 37260-92763
* Macromedia DreamWeaver 4.0 ........WBW400- 06648-47236- 27654
* Macromedia FireWorks MX.......... ..WSW600- 03579-37260- 92763
* Macromedia FireWorks 4.0 ..........WBW400- 06648-47236- 27654
* Macromedia Flash MX.......... ......FLW600- 59739-97340- 25714
* Macromedia Flash MX.......... ......WSW600- 03579-37260- 92763
* Macromedia Fontographer 4.1 .......65241- 0499-0166- 21702
* Macromedia Freehand 10.0 ..........WSW600- 03579-37260- 92763
* Macromedia Homesite+... ......... ...WSW600- 03579-37260- 92763
* Mathematica 4.0 ............ .......52109- 11310-47983- 00
* MathCAD 2001i ............ ......... RT105112UP0073 + crack
* MatLab 6.5
............ ......... ...13-22955- 32678-26249- 06848-40366- 39620-03472- 0 9765-2
0949-30945-19673- 43738-38950- 44548
* Mathworks Suite 13 SP1
with Matlab
6........... ....13-22935- 54640-30039- 45949-41945- 37494-18536- 34124-630 74-336
71-08341-01230- 63402-23598
* MediaStudio Pro 7........... .......011A2- 27042-0190DCN4
* MS Office 2003 11.0 ............ ...GWH28- DGCMP-P6RC4- 6J4MT-3HFDY
* MS Office FrontPage 2003........ ...WFDWY- XQXJF-RHRYG- BG7RQ-BBDHM
* MS Office Visio 2003........ .......WFDWY- XQXJF-RHRYG- BG7RQ-BBDHM
* MS Office Project 2003 Pro......... WFDWY-XQXJF- RHRYG-BG7RQ- BBDHM
* MS Office OneNoteT 2003........ ....WFDWY- XQXJF-RHRYG- BG7RQ-BBDHM
* MS Office Publisher 2003........ ...WFDWY- XQXJF-RHRYG- BG7RQ-BBDHM
* MS Office XP ............ ......... .FM9FY-TMF7Q- KCKCT-V9T29- TBBBG
* MS Office XP croatian ............ .Q672P-X37V4- M6RHQ-JX3D9- F49RQ
* MS Office 2000 beta........ ........MP4F9- W6C8V-HTCCT- T7M7R-Y7K3Y
* MS Visual FoxPro profesional 8.0 ..TCJC7-H2QDH- 3T7G7-R6RTM- YRK3Y
* MS Visual Studio.Net 7 (ent.ed) ...D64GG-GXY6T- V6FTR-WCPBB- 2YDYB
* MS Windows Longhorn Alpha 4.053....TCP8W- T8PQJ-WWRRH- QH76C-99FBW
* MS Windows Longhorn Beta 4.015.....CKY24- Q8QRH-X3KMR- C6BCY-T847Y
* MS Windows XP-Final with SP1 ......7QVT6- T2738-WRKJB- YKRFQ-XVK98
* MS Windows XP profesional (beta)...FCKGW- RHQQ2-YXRKT- 8TG6W-2B7Q8
* MS Windows XP home........ ......... BQJG2-2MJT7- H7F6K-XW98B- 4HQRQ
* MS Windows ME final....... ......... B6BYC-6T7C3- 4PXRW-2XKWB- GYV33
* MS Windows ME.......... ......... ...HBTD9- 6P338-XT2MV- QBTTF-WPGGB
* MS Windows 2003 Server 3in1 .......JB88F- WT2Q3-DPXTT- Y8GHG-7YYQY
* MS Windows 2000 profesional. .......RM233- 2PRQQ-FR4RH- JP89H-46QYB
,(X04-98176)
* MS Windows 2000 prof/120 day.......RBDC9- VTRC8-D7972- J97JY-PRVMG
,(X05-48965)
* MS Windows 98 SE.......... ......... QYHDT-7B4VX- XFH2P-JV7PJ- VGC9D
* MS Windows 98 SE.......... ......... HQ6K2-QPC42- 3HWDM-BF4KJ- W4XWJ
* MS Windows 98 ............ ......... K4HVD-Q9TJ9- 6CRX9-C9G68- RQ2D3
* MS Windows 98 ............ ......... HGBRM-RBK3V- M9FXV-YCXDK- V38J4
* MS Windows 97 (95 OSR2)....... .....00100- OEM-0123456- 00100
* MS Windows 97 (95 OSR2)....... .....14195- OEM-0000043- 27166
* MS Windows Small Business Server
Standard Edition 2003 4CD........BBGC6- TXDG9-J9CDW- JXK3R-GTMMB
* MS Proofing Tools 2003........ .....WMMF8- W93RH-BVPFM- WBMHT-DFCFG
* Native Instruments Vokator 1.0.....5080- 01001-01001- 50011-00101
* NeoDVD...... ......... ......... .....w4jya- 0389v-hcfxa- ebj80-yhzza
* NetObjects Fusion 7.0 ............ .NFW-700- R-010-18822- 37395
* NetObjects Fusion 7.5 ............ .NFW-750- R-215-00077- 25454
* NERO 6.3 Powerpack Retail...... ....1A25- 0006-7130- 1352-4239- 7903
* NERO 6 Ultra Edition..... ......... .1A23-0006- 7130-1204- 8976-4901
* NERO 6 Ultra Edition..... ......... .1A23-7081- 2054-8977- 8403-2561
* Norton AntiVirus 2004........ ......VHJFC2P7FB HKVQ88JYCBBJ4
* Norton AntiVirus 2004........ ......VH9VFRM8BB HKV3DWV7RBBGJ or
* Norton AntiVirus 2004........ ......VHTQ- FFTW-CBHK- VDKP-J9MV- BBJ4
* Norton AntiVirus 2004 prof........ .VCT3-63XD- 6BBD-2BBM- GBGJ-WDXD
* Norton AntiVirus 2004 prof........ .V7T2-C3Y2- JBBD-243M- QWGR-6KMB
* Norton AntiSpam 2004........ .......VQHV- T8H2-YBBF- DQ4H-D7BB- BBWR
* Norton Ghost 2002 ............ .....BB2566D65E2 D
* Omnipage Pro 12.0 office...... .....E789X- DCN-019066
* Photo/Graphic Edges 6.0......... ...97-6608015353 -301133267- E1201466645
* Pinnacle Instant CD DVD 8.0........ABGOE- AAKYY-ORUSH- MLHBA-CPKPA
* Pinnacle Instant Copy 8.0 .........AARTO- AAFQN-YGWFK- HJLMA-AAAAA
* Pinnacle Instant Video Album 1.01..7777777777
* Pinnacle Holywood FX 4.0 ..........HFXMP- SLV-000238- 15R
* Pinnacle Studio 9.0 ............ ...11111- 11111-11111- 11111-11111
* Pinnacle Studio 8.8.15 ............ 4517535441
* Pinnacle Studio 8.3 ............ ...1555856630
* Pinnacle steinberg clean plus 5....BBDPE-BAPLJ- BFVPA-GRRPA- DBZMA
* PowerQuest V2i Protector 2.0.......AY3A- PH7J-3FNQ- JPTR-YFGW
* PowerQuest V2i Protector 2.0.......AY3A- PH7J-3G4Y- ADQQ-HTWD
* PowerQuest Partition Magic 7.0 ....PM700ENEU- 110965
* PowerQuest Drive Image 7.01........ AY3A-PH7J- 3DM6-R7KR- 6XJT
* PowerDirector Pro 2.5 ............ .RR7314611713953 6
* Presto DVD PowerSuite 1.0......... .G300501- 4NJZTA433SFQ
G300501-OEM- hahahahawhatever youwant
* Primavera Project Planner 3.0 .....50122-77293- 2474-1104497
* Propellerheads Reason 2.5......... .RSN250-0000- 005336-NVV3- CGDJ-DR5Sv
* Propellerheads Reason 2.5......... .RSN250-0009- 101923-B4N8- UHBY-AP6X
* RED 1.5 Boris FX.......... ......... 123708-326183- 00615
* RED 1.5 Boris FX.......... ......... 123348-366123- 77695
* ReadIris Pro 9.0(OCR).... ......... .438111400159753
* Route 66 Prof. 2004........ ........2582121Z VNK26
* Roxio Easy Media Creator 7.0 ......YN-Y7WVY- CQXG6-K3KWZ
* Roxio WinonCD 6.0 DVD edition .....1C-HT789- SB62B-3YK12
* Roxio GoBack 3.03........ ......... .CB-ZM2VN- 5YP61-7N1VD
* ScanSoft Dragon Naturally Speaking Preferred... ......A109A- G00-006420
* ScreenBlast Acid 4.0 ............ ..FB-XZ51GN- JS8L5G-X3YVEX
* ScreenBlast Movie Studio 3.0 ......FC-TZPCH4- C2PNR3-FFTZ3X
* SOLIDWORKS 2003
Solidworks serial: 9980 0999 9191 7951 auth code: 7D9YY253
Solidworks office serial: 9980 0999 9191 7951 auth code: V0E3J979
Solidworks office pro serial: 9980 0999 9191 7951 auth code:
JA5O631B
* Sonar XL.......... ......... ........CWSX1. 00-009999
* Sonic MyDVD Video Suite 4.0 .......BDVQHQBTDGYN HQVS3 IamaLOZER
* Sonic Foundry Video Factory 2.0 ...9L-Z13XJF- SDJ1P3-SNY76P
* Sonic Foundry CD Architect 5.0 ....B5-C4CLC3- ZOY85H-49WE8W
* Sony Pictures ScreenBlast MovieStudio F9-S431QS-K894NC- 6LWNJP
* SPSS 12.0 ............ ......... ....5676 5034 7681 8288 2379 1238 3252 9988
8641 9306 27
* Stainberg Clean 3.0 ............ ... ? DL23-8C73-24Y9- MS5 ili
DZ08-FLU6-97K1- PM4
(Win9x S/N:
ST22-Y7YM8VV- EPPNBY8-V8H)
(WinNT S/N: ST22-Y7YPG2Y)

* Steinberg's Cubase SX 2.0 .........1080201188
* Steinberg DVD Movie Copy Suite 6.5.25311-41300019- 33092
* Sybase SQL Anywhare Studio 8.0
....DEPCKAQQREAC000 029800715933513- 00-0800-01
* Ulead COOL 3D Studio 1.0 ..........274A5- 11085-78578888
* Ulead DVD PictureShow 2.0 .........111A6- 12000-00132624
* Ulead DVD Workshop 2.0 ............ 1RO02-520RO- RORRORO6
* Ulead DVD Workshop 1.x ............ 96302-51072- 24530822
* Ulead DVD MovieFactory 2.0 ........111A2- 62000-00300082
* Ulead Gif Animator 5.0 ............ 11103-85011- 21226004
* Ulead Media Studio 7.0 pro......... 011A2-27042- 0190DCN4
* Ulead Media Studio 7.0 pro......... 011A2-27042- 0190DCN4
* Ulead Media Studio 6.5 ............ 12202-2650- 000481114
* Ulead PhotoImpact XL-2CD...... .....111A3- 08500-00002098
* Ulead PhotoImpact 7.0 ............ .111103-07000- 00001531
* Ulead VideoStudio 7.0 ............ .RORA2-870RO- ROROROR1
* Veritas Storage Reporter 3.5.......NEZ9- UCGW-PP3F- L3PN-ZBNM- 7HP6
* Visual Foxpro prof. 8.0 ...........TCJC7- H2QDH-3T7G7- R6RTM-YRK3Y
* VirtuaGirl ? ............ ......... .name: 82949 / password: 16907
* VirtuaGirl 1.22........ ......... ...name: 8866688 / Serial: 97917
* WinBoost 2001 Gold-v2.0 ...........AT9D3- D732-LX57- 8L2U
* QuarkXpress 6.0 Passport M.
.......TVWXNX- XCUVE2-641HQH- TWK7GH-RKGV12- E1ETPW-14X57Y- 38WRH +crack
* QuarkXpress 5.0 ............ .......39173027Q HSKKJF319 9PYJX2
* QuickBooks Pro 2004........ ........09701- 42330-29602- 400004
* Xara Webstyle 4.0......... ......... .....XWS- 5299-5077- 6209
* X-OOM DivX2DVD 5.0......... ......... ....L14LQ- VX1ER-F8LDW- GXDS3-6ZVXV
* X-OOM Video Clean ............ ......... .3GP0G-9BHX4- HCJE5-EPKFP- CYMQB
* X E.ON.Software. Vue.4.Profession al......MOV4- 31682-S-74523- QMT-SN
* Zone Alarm 4 Pro......... ......... .38ct1-bqf8g- x9jnr-tfth7c- 3iu540
* 3D Studio Max 5.0 ............ .....s/n: 226-19791979 ,(+cd-key: XLSQBQ)
(+request code: 3175446920385491) ,
(+authorization: SATW 6Z7U WJGW CSCP)
* 3D Studio Max 4.2 ............ .....s/n: 226-19791979 ,(+cd-key: XLSQBQ)

Third visit at Lahore Office

Left Home Around:6.30 A.M.
Reach Karachi Airport Around:7.10 A.M
Reach Lahore Airport Around:9.50 A.M
Reach Systems Lahore Office Around:10.30 A.M

It was third visit , visited for Freddie Mac ( completed Reporting Module and shipped to QA , also worked on DMU).

Nice visit but missing family a lot

Second Visit to Lahore Office

Left Home Around:6.30 A.M.
Reach Karachi Airport Around:7.10 A.M
Reach Lahore Airport Around:9.50 A.M
Reach Systems Lahore Office Around:10.30 A.M

People gave a warm welcome to me as this was my second visit to lahore office but every body was knowing me by name. Very happy from their respose. Will leave around on Friday inshallah.

SQL Server Security Best Practices

Here is an ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server:

Configure SQL Server to use Windows authentication mode


Depending upon the data access needs of your domain users, group them into different global groups in the domain


Consolidate these global groups from all the trusted domains into the Windows NT/2000 local groups in your SQL Server computer


The Windows NT/2000 local groups are then granted access to log into the SQL Server


Add these Windows NT/2000 local groups to the required fixed server roles in SQL Server


Associate these local group logins with individual user accounts in the databases and grant them the required permissions using the database roles


Create custom database roles if required, for finer control over permissions


Here is a security checklist and some standard security practices and tips:

Restrict physical access to the SQL Server computer. Always lock the server while not in use.


Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.


Use the NTFS file system as it provides advanced security and recovery features.


Prefer Windows authentication to mixed mode. If mixed mode authentication is inevitable, for backward compatibility reasons, make sure you have complex passwords for sa and all other SQL Server logins. It is recommended to have mixed case passwords with a few numbers and/or special characters, to counter the dictionary based password guessing tools and user identity spoofing by hackers.


Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password.


In a website environment, keep your databases on a different computer than the one running the web service. In other words, keep your SQL Server off the Internet, for security reasons.


Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft: http://www.microsoft.com/security/


If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command.


Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion.


If it fits your budget, use Intrusion Detection Systems (IDS), especially on high-risk online database servers. IDS can constantly analyze the inbound network traffic, look for trends and detect Denial of Service (DoS) attacks and port scans. IDS can be configured to alert the administrators upon detecting a particular trend.


Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser


Do not let your applications query and manipulate your database directly using SELECT/INSERT/UPDATE/DELETE statements. Wrap these commands within stored procedures and let your applications call these stored procedures. This helps centralize business logic within the database, at the same time hides the internal database structure from client applications.


Let your users query views instead of giving them access to the underlying base tables.


Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views.


Don't let applications accept SQL commands from users and execute them against the database. This could be dangerous (known as SQL injection), as a skilled user can input commands that can destroy the data or gain unauthorized access to sensitive information.


Take advantage of the fixed server and database roles by assigning users to the appropriate roles. You could also create custom database roles that suit your needs.


Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role.


Constantly monitor error logs and event logs for security related alerts and errors.


SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions.


Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.


If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn't recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.


If you are running SQL Server 7.0, you could use the encryption capabilities of the Multi-Protocol net library for encrypted data exchange between the client and SQL Server. SQL Server 2000 supports encryption over all protocols using Secure Socket Layer (SSL). See SQL Server 7.0 and 2000 Books Online (BOL) for more information on this topic. Please note that, enabling encryption is always a tradeoff between security and performance, because of the additional overhead of encryption and decryption.


Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers.


DBAs generally tend to run SQL Server service using a domain administrator account. That is asking for trouble. A malicious SQL Server user could take advantage of these domain admin privileges. Most of the times, a local administrator account would be more than enough for SQL Server service.


DBAs also tend to drop system stored procedures like xp_cmdshell and all the OLE automation stored procedures (sp_OACreate and the likes). Instead of dropping these procedures, deny EXECUTE permission on them to specific users/roles. Dropping these procedures would break some of the SQL Server functionality.


Be prompt in dropping the SQL Server logins of employees leaving the organization. Especially, in the case of a layoff, drop the logins of those poor souls ASAP as they could do anything to your data out of frustration.


When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords.


To setup secure data replication over Internet or Wide Area Networks (WAN), implement Virtual Private Networks (VPN) . Securing the snapshot folder is important too, as the snapshot agent exports data and object scripts from published databases to this folder in the form of text files. Only the replication agents should have access to the snapshot folder.


It is good to have a tool like Lumigent Log Explorer handy, for a closer look at the transaction log to see who is doing what in the database.


Do not save passwords in your .udf files, as the password gets stored in clear text.


If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click. Click here to find out more information about this product.


In database development environments, use a source code control system like Visual Source Safe (VSS) or Rational Clear Case. Control access to source code by creating users in VSS and giving permissions by project. Reserve the 'destroy permanently' permission for VSS administrator only. After project completion, lock your VSS database or leave your developers with just read-only access.


Store the data files generated by DTS or BCP in a secure folder/share and delete these files once you are done.


Install anti-virus software on the SQL Server computer, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.


SQL Server 2000 allows you to specify a password for backups. If a backup is created with a password, you must provide that password to restore from that backup. This discourages unauthorized access to backup files.


Windows 2000 introduced Encrypted File System (EFS) that allows you to encrypt individual files and folders on an NTFS partition. Use this feature to encrypt your SQL Server database files. You must encrypt the files using the service account of SQL Server. When you want to change the service account of SQL Server, you must decrypt the files, change the service account and encrypt the files again with the new service account.

Interview:DB Programming Questions

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.

Write down the general syntax for a SELECT statements covering all the options.

Here's the basic syntax: (Also checkout SELECT in books online for advanced syntax).

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

What is a join and explain different types of joins.

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".

Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.

What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.

Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()

There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

Interview:DB Admin Questions

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board's homepage

What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com

What are the steps you will take, if you are tasked with securing an SQL Server?

Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.

Read the white paper on SQL Server security from Microsoft website. Also check out My SQL Server security best practices

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.

Explain CREATE DATABASE syntax

Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online for more parameters and their explanations.

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats

What are the different ways of moving data/databases between servers and databases in SQL Server?

There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.

What is database replicaion? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

Snapshot replication
Transactional replication (with immediate updating subscribers, with queued updating subscribers)
Merge replication
See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions.

Interview:SQL Server Architecture Questions

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

Explain different isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

CREATE INDEX myIndex ON myTable(myColumn)

What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.

Explain the architecture of SQL Server

This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.

What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?

This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.

What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Interview:DB Design Questions

What is normalization? Explain different levels of normalization?

Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

It will be a good idea to read up a database designing fundamentals text book.

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

What is bit datatype and what's the information that can be stored inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What are defaults? Is there a column to which a default can't be bound?

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

T-SQL:Procedure to import SQL Server error log into a table

BEGIN
SET NOCOUNT ON

DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log

IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
BEGIN
IF @overwrite = 0
BEGIN
RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
RETURN -1
END
ELSE
BEGIN
EXEC('DROP TABLE ' + @log_name)
END
END


--Temp table to hold the output of sp_readerrorlog
CREATE TABLE #errlog
(
err varchar(1000),
controw tinyint
)

--Populating the temp table using sp_readerrorlog
INSERT #errlog
EXEC sp_readerrorlog @log_number

--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0


SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err,23)) [Date],
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
INTO ' + QUOTENAME(@log_name) +
' FROM #errlog ' +
'WHERE controw = 0'

--Creates the table with the columns Date, spid, message and controw
EXEC (@sql)

--Dropping the temporary table
DROP TABLE #errlog

SET NOCOUNT OFF
PRINT 'Error log successfully imported to table: ' + @log_name
END

VB:Sample code to run Crystal Report

Private Sub frmReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim paramFields As New ParameterFields
Dim paramField As New ParameterField
Dim discreteVal As New ParameterDiscreteValue

paramField.ParameterFieldName = "abc"
discreteVal.Value = "Wavetech"
paramField.CurrentValues.Add(discreteVal)
paramFields.Add(paramField)
paramField = New ParameterField
discreteVal = New ParameterDiscreteValue
paramField.ParameterFieldName = "def"
discreteVal.Value = "Pvt Ltd"
paramField.CurrentValues.Add(discreteVal)
paramFields.Add(paramField)
CRV.ParameterFieldInfo = paramFields
a = New DatabaseConnection
a.CreateConnection()
' ds = a.ExecuteSelect("Select * from employee where id=1", "Employee")
' CRV = ds

CRV.SelectionFormula = "{Fixed.ID}=1" 'Selection Formula
CRV.ReportSource = "Report1.rpt"


End Sub

T-SQL:Shrink Log File

use DatabaseName

BACKUP LOG DatabaseName With TRUNCATE_ONLY
GO
DBCC SHRINKFILE (Database_Log_FileName,300)
GO

T-SQL:Last day of the month

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))

T-SQL:Concatenate all values of row in a column

DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS [List of Title IDs]