To do that, I quickly create a staging table in my database, then save the Excel file as tab-delimited.

Then I have to go play with bcp and figure out the syntax yet again, since I don't do it often enough to have made it unthinkingly automatic. Recently I got tired of these repeated lookups and created the following batch file to help out:

@echo off
:top
if .%1==. goto end
echo Ready to import file to SqlServerName, DB DBName:
echo  %1
set /P tablename=Type destination table name and press Enter:
osql -S SqlServerName -d DBName -E -Q "truncate table %tablename%"
bcp DBName.%tablename% in %1 -S SqlServername -T -c
echo.
shift
goto top
:end
set tablename=
pause

Name the batch file "SqlServerName DBName import replace (drop file).bat".

Now in Windows Explorer, just drag any file you want to import to your database onto the batch file. When prompted, type in the schema and table name you want it loaded to, and press Enter. You'll get a nice pause to see what the result was so you can fix it if not successful. It even supports dropping multiple files, and you'll be asked for a destination table name for each file.

If you find yourself needing to load a text file to a particular table over and over, just make a batch file specific to that text file, and you can drag-and-drop your imports super easily.

You can of course customize the batch file to do anything you want. To prompt for server name, database name, username, password, or anything you like. And you won't have to look up the syntax for bcp one more blasted time!

The scenario I'm thinking of is something like when you're solving a SQL puzzle/challenge and keep trying new ideas and query versions. You don't want to delete your old queries because they are useful for reference or to copy and paste parts of them, but you don't want to run them all the time. Especially during performance tweaking, you want to be able to quickly comment out the worst queries and keep pasting in new versions of the fastest queries.

Normally this would require either highlighting entire queries to comment/uncomment them (Ctrl-Shift-C/Ctrl-Shift-U in Query Analyzer or Ctrl-K:Ctrl-C/Ctrl-K:Ctrl-U in SSMS); adding or removing /* and */ at the top and bottom of the query; or highlighting just the portions of the code that you want to run each time, which is a pain and can be complicated if you are using table variables since your initialization code at the top has to run each time.

So try this!

Put --/* at the top of your query and --*/ at the bottom:

Notice that the query is NOT commented out.

Now, to toggle the whole query as commented or not, just uncomment the first line:

I just hit on this method yesterday and I'm really enjoying it. It provides a natural place to put a query comment, you only have to put the block comments in once, and then you can use your comment/uncomment shortcuts on just a single line to toggle an entire block of code! Just keep in mind that the action IS reversed: you comment to uncomment and you uncomment to comment.

A deadlock is nothing more than mutual blocking. Blocking is when a process is forced to wait for a resource while another process exclusively accesses it (where the exclusivity is managed through locks). Mutual blocking is when both processes have a lock on a resource the other is asking for. Neither can proceed, but neither will release its lock.

Normally, when SQL server detects this kind of mutual block, it picks one process as the "victim" and kills it, rolling back its work. This also releases any locks it had and allows the other process to acquire its previously blocked request, resolving the deadlock.

But what if the two resources in question are on separate servers? For SQL Server to detect deadlocks, it has to have enough information to see that a pattern of blocks is mutual.

Now, let's say that locks for these two queries are granted in this order: query 1 acquires a lock on TableA before Table B, but query 2 acquires a lock on TableB before TableA. Even though the example I'm giving here may not be the greatest, the possibility is not so unlikely, and I'm sure there are plenty of situations out in the wild where my suggested scenario is possible.

Locks are not granted all at once, nor can one assume that the final lock used for an update is granted first. Many times a less exclusive lock is acquired and then the lock's exclusiveness is increased or specificity is broadened, and that's all that's required to cause a deadlock. See Deadlocks with Custom Sequence for one example of surprising deadlocks occurring because of an unusual transaction isolation level.

Given the above hypothetical lock order, when each query's process requests a lock on the other table to perform its update, it will be blocked. But each server involved only has half the picture and can only see a simple block, without knowing it's mutual (because the other half is occurring on the other side of the linked server). So it is a perpetual mutual block and no deadlock is detected.

The next time I am working heavily with linked servers you can be sure I'll be thinking about this and coming up with a way to test if such a "distributed deadlock" really can occur, and if so, what to do about it.

In closing, I confess that it's possible that DTC could have provisions for sharing lock information, so perhaps a distributed deadlock will be properly detected. But I have my doubts about that.

Background

It's a fairly simple query to select the maximum date for each group in a GROUP BY query. You just throw in a Max() on the date column and then GROUP BY the other columns. For example,

But a common query need is to include other column values from the same row as the most recent date. Unfortunately, putting aggregates on other columns doesn't work:

It's almost like you need some kind of Max(OrderDate->SubTotal).

Note that the desired results are fairly easy in MS Access using the aggregate functions Last and First:

What happens in Access is that the Last() aggregate combines with the ORDER BY clause to select values from the row containing the most recent Order Date.

However, these functions are not available in SQL Server, likely because the query engine only does ordering at the very end, after GROUP BY and HAVING are processed. While this may seem like a defect, it is really a deliberate design trade-off, with compensations elsewhere in the engine.

Test Case

In order to have enough data to make results significant and also to let others run the same queries against the same data, we are going to use the AdventureWorks sample database.

In our scenario, we want to show customers' account numbers along with the date and subtotal of their most recent order. We're keeping it simple for clarity, but note that you can include as many columns as you like. In a later installment we'll do these same queries with more columns and different scenarios, and will expand on some of the queries a bit. We'll also discuss performance to help you select between them.

Note that the aggregate doesn't have to be on a date. Perhaps you want to know the date of the largest dollar value order per customer. In this case, you'd be doing a Max() on the order value instead of a date, and you would most certainly run into duplicates. Keep this in mind when reading below and when developing your own queries.

The basic query we'll be working with is:

What we want to do is also return the Subtotal value from the same row as the LastOrderDate.

Some Approaches to the Problem

We know of only a few basic approaches to the problem:

So let's use those ideas and turn them into real queries. Unless otherwise stated, queries will work with SQL Server 2000.

Key Search

1. Correlated Subquery

• Relies on unique order dates to avoid selecting multiple rows per customer.
• Correlated subqueries perform well with small datasets and badly with large datasets (though better with SQL 2005 and 2008 because they can sometimes get converted to real joins).
• Performance degrades geometrically as rows increase.
• Does not handle NULLs correctly.

Another version of correlated subquery which sometimes performs much better is

Another variation of this query is (suggested by Alejandro Mesa (Hunchback) in this MSDN thread):

Note, that the first variation of this query may return duplicate rows, but the second will always return only one row per group (with the latest OrderID).

The third variation of this query with NOT EXISTS will produce duplicates in case of the same date, but can also be easily adjusted to return only one row with the max OrderID:

2. Derived Table

• Relies on unique order dates to avoid selecting multiple rows per customer.
• Performs better than the correlated subquery when querying a significant portion of all customers, but worse when only querying a few, since the last order date for every customer is always calculated.
• Performance degrades linearly as rows increase.
• Does not handle NULLs correctly.

Number and Filter

Windowing Functions

The windowing functions were introduced in SQL Server 2005, so the two queries below will not work in SQL Server 2000.

• Will always return one row per customer. Using RANK() instead of ROW_NUMBER() will yield the same results as queries 1 and 2.
• Easily supports not just 1 but n rows per group.
• Performance degrades fairly linearly as rows increase. The windowing functions are fast but huge rowsets can begin to bog them down.

3. Windowing Function - Two Stages

• A common table expression is not needed, but is cleaner. The CTE query can be made into a derived table if desired.

4. Windowing Function - One Stage

Simulate MS Access

5. Compound Key, aka Packed Values

• Uses only one table access with a simple GROUP BY.
• Naomi first learned this idea from a Russian SQL FAQ.
• Erik cooked up a similar idea in 2008.
• This example uses a varchar column to hold the packed values, but be aware that converting to and from binary is faster than char (plus harder to use and more error-prone) and binary aggregation is faster. The crucial part is being able to faithfully extract the data that was put in.
• This query has no problem with duplicates. It does effectively sort by the full compound value, so if multiple orders for the same customer can be on the same order date and you want some other column to determine which one is selected, it has to be included in the packed data.
• As given, this query does not handle NULLs at all. A few strategic Coalesces can fix that.
• There is no significant performance difference between unpacking one many-item compound value or many one-item compound values (though you'd always include all columns needed for ordering).

Isn't that cool? I don't advocate using it all the time, but when performance is bad and the trade-offs are worth it, do it.

In closing, thank you for visiting. We hope this is useful to you. You might like to see the next installment Including an Aggregated Column's Related Values - Part 2, discussing this issue in further depth.

Erik

P.S. I would like to thank Naomi for co-authoring this blog post with me. She did the heavy lifting of actually writing queries and testing their performance; I just fleshed out the explanations a bit.

I have a report that returns specific documentation from the database with one parameter, a CustomerID. The report is bound to a stored procedure which has hard-coded in it the names of the specific documents that should be included. This solution is problematic because when the list of documents needs to change, the SP has to be updated, which means mistakes can be (and have been) made.

The good step of converting the SP to use a table doesn't satisfy me fully, because these reports are executed from a special reporting system that also has its own list of document names, so that it knows when to kick off a particular report—for example, if none of the documents exist, no report will be generated. I didn't like the idea of depending on having the same data updates made in two places: I should be able to just update the reporting system with the new document names and then trust that the reports will contain the correct data. In the past, we had a mistake where the reporting system received a new document name that didn't get included in the SP properly, so after finally fixing the SP, I had to do a recovery and resend some reports.

I realized that what could solve the problem once and for all was to provide the exact list of document names to the SSRS report as a parameter, and have the report pass the document names into the SP, so that even if the SP is not updated (or its source table is not updated), the correct content will still be pulled. The way to do this is with a "multi-value" parameter.

Starting Points

• A quick test will show that the way SSRS passes multi-valued parameters to a stored procedure is simply by taking all the values selected and packing them into a comma-delimited string. If any of the items in the list contain a comma, no escaping or special handling is done—so manual escaping is required.

• In case you're really new to all this, all the report development is being done in Visual Studio 2005, with a project of type Business Intelligence Projects - Report Server Project. I'm sure you can find tutorials elsewhere for how to set up a basic report. Once you have a working report, you're ready to add a multi-value parameter.

• In this article, the multi-value parameter I am working with is a list of document names. So wherever I say document names, you can insert the thing that you are concerned with. If you can guarantee that there will never be a comma in the list, you can skip or edit the parts of my explanation that have special handling for commas.

Adding the Multi-Value Parameter

What I learned while doing this is probably useful to someone, so here's a quick rundown of how to modify an SSRS report and a stored procedure to accept and use a multi-valued parameter.

Modify the Stored Procedure

1. Add the new parameter. I copied the old SP to a new name so that I couldn't possibly mess up the original, and so going forward no one else who works with these things will get confused about which does what.

   tsql

   Line number Off | Hide | Select all

   1. CREATE PROCEDURE dbo.MyReport_DocParam
   2.    @CustomerID int,
   3.    @DocumentNameParam varchar(Max) = ''
   4.    -- Please note that any document names with commas must be put inside single quotes.

   The default value of an empty string allows the SP to be run with just a CustomerID and it will still work. It may not have all the document names, but for testing and development of the SP that may not matter most of the time. I put the comment shown right into the SP to help prevent problems with other people trying to use the SP when I am not around.

   I chose to make the new parameter varchar(Max) because it won't hurt anything, and on the theory that I don't like my code breaking, and despite knowing that the list will almost surely never exceed 8000 characters, I'd rather be safe. In SQL 2000 you can probably get by with varchar(8000) or even the text data type.

2. Write a value-splitting function.

   Note: I have not experimented with other data types such as Integer. If you have done this and know how it works in SSRS differently from using strings, please post a comment. I suspect that whatever the actual data type of your items, they will have to be passed as a comma-separated string anyway. If your items are fixed-length or easily convert to relatively short fixed-length strings, padding them so the comma delimiters added by SSRS appear at exactly known positions will make your task of splitting much easier. You can just join to a special Numbers table and split at each position. For example, if you can make your inputs always 10 characters long then you can just break the string at every 11th character—the comma.

   Here's how I decided to escape commas:
   • If the value contains a comma, enclose it in single quotes and double up any single quotes in that value.
   • If the value does not contain a comma, don't escape it in any way even if it has a single quote.

   I chose this because it is similar to how SQL Server accepts string literals already and should be more familiar to anyone working with my code than other methods (such as backslash-escaping for example). I also considered replacing commas with a character that I know won't be in the document names such as character 0 or 255 and then restoring it after splitting values apart, but rejected that because it makes it much more difficult to understand and makes it much harder to provide the list of items if the SP is being run from a query window such as Query Analyzer or Management Studio.

   But I Don't Wanna Escape The Comma

   If you don't think you need to escape commas, please ensure that commas can't be in your data ever, not that they don't exist now or "we'll tell people not to put commas in." An utterly basic and beginner's rule of programming is to never trust user input. Bad Things happen when you do. How do you know that in 5 years when you may be gone or on vacation that some new person who never heard about the comma rule won't enter a comma?

   Here's my function to do the unescaping and splitting work:

   tsql

   Line number Off | Hide | Select all

   1. CREATE FUNCTION SplitQuotedString (@String varchar(max))
   2. RETURNS @Array TABLE (Item varchar(8000))
   3. AS
   4. BEGIN
   5.    DECLARE
   6.       @Pos int,
   7.       @End int,
   8.       @TextLength int,
   9.       @Item varchar(8000),
   10.       @Fragment varchar(8000),
   11.       @InQuotes bit
   12.    SET @TextLength = DataLength(@String)
   13.  
   14.    IF @TextLength = 0 RETURN
   15.  
   16.    SET @Pos = 1
   17.    SET @InQuotes = 0
   18.    SET @String = @String + ','
   19.    SET @Item = ''
   20.    WHILE 1 = 1 BEGIN
   21.       SET @End = CharIndex(',', @String, @Pos)
   22.       IF @End = 0 BREAK
   23.       SET @Fragment = Substring(@String, @Pos, @End - @Pos)
   24.       IF @InQuotes = 1 BEGIN
   25.          --InQuotes becomes false if there are an odd number of single quotes at the end of this fragment
   26.          SET @InQuotes = PatIndex('%[^'']%', Reverse(@Fragment) + 'x') % 2
   27.          SET @Item = @Item + ',' + Replace(Left(@Fragment, DataLength(@Fragment) - 1 + @InQuotes), '''''', '''')
   28.       END
   29.       ELSE BEGIN
   30.          IF @Fragment LIKE '''%' BEGIN
   31.             SET @InQuotes = 1
   32.             SET @Item = Replace(Substring(@Fragment, 1 + @InQuotes, 8000), '''''', '''')
   33.          END
   34.          ELSE BEGIN
   35.             SET @Item = @Fragment
   36.          END
   37.       END
   38.       IF @InQuotes = 0 BEGIN
   39.          INSERT @Array (Item) VALUES (@Item)
   40.          SET @Item = ''
   41.       END
   42.       SET @Pos = @End + 1
   43.    END
   44.  
   45.    RETURN
   46. END

   You're welcome to use it, or write your own. You may not agree with how I chose to do it, and if you have any seriously big improvements do let me know. But I was not overly concerned with extracting that last tiny bit of performance, as my strings will never be very long and will never have more than a dozen or so items. I could not use a Numbers table solution since I am doubling up single quotes and in the case of encountering input such as "''''" it would incorrectly match an extra time at the second quote mark. Preventing a false match requires knowing if the current quote mark is an even or an odd one, which would need to look backward in the string, which complicated it so much that I just went for a chunk-by-chunk method that looks at each string separated by commas and determines if it begins a quoted string. If so, then each fragment is added to the item until the closing quote mark is encountered, and finally the complete item is added to the output table. If the item is not quoted, it is simply added to the output table.

   Note: In this function I assumed that no individual value would exceed 8000 characters. I also assumed that an item will not be quoted without containing a comma character. If items are quoted that don't contain commas, this code will break. See the escaping logic I used in the query for the value list producing Dataset on the SSRS report.

   If your data will never have commas in it or you can use the the fixed-length method, then should use your own plain-vanilla split function rather than my unescaping version.

3. Split and use the multi-value string.

   Here's the code I used, which should be enough of an example to let you develop your own version that suits your needs:

   tsql

   Line number Off | Hide | Select all

   1. DECLARE @DocumentNames TABLE (
   2.     DocumentName varchar(60)
   3. )
   4. IF Coalesce(@DocumentNameParam, '') = '' BEGIN
   5.    INSERT @DocumentNames SELECT Item FROM dbo.SplitQuotedString(@DocumentNameParam)
   6. END
   7. ELSE BEGIN
   8.    INSERT @DocumentNames
   9.    SELECT 'Document Name 1'
   10.    UNION ALL SELECT 'Document Name 2'
   11.    UNION ALL SELECT 'Document Name 3, Special'
   12. END

   To my dismay, I was not allowed to make this SP read from a table. But I am still quite happy because now I don't care if the SP gets updated with new document names: when the reporting system creates a report, it will pass in document names and always be correct. I have insulated the reporting system against exactly the thing I was trying to avoid: mistakes when updating the SP. I'll even throw in that I was the one who made the mistake updating the SP last time—I didn't know the hard-coded document list was in three places. Now, at least, the list is in one place.

   And finally, using the document names:

   tsql

   Line number Off | Hide | Select all

   1. INNER JOIN @DocumentNames N on D.DocumentName = N.DocumentName

Modify The SSRS Report

1. Update the Dataset. On the Data tab, either create a new Dataset or update an existing Dataset so that it has the correct SP name and finds the new parameter you added. To edit a Dataset, click the "..." button to the right of the Dataset name dropdown.

2. Decide whether the report's default list of available document names will be hard-coded in the report or supplied by a query. I chose to make it data-driven so that if a new document is added, it will show up in the report without any changes to it, or with only a change to a shared data source, rather than having to change the SSRS report itself. I always prefer to make changes to data or shared objects than to code or types of things that have to be tested and deployed.

   From query: Create a new DataSet that pulls the document names, one per row. Here is the query I used:

   tsql

   Line number Off | Hide | Select all

   1. SELECT
   2.    DocNameString =
   3.       CASE
   4.       WHEN DocName LIKE '%,%' THEN '''' + Replace(DocName, '''', '''''') + ''''
   5.       ELSE DocName
   6.       END,
   7.    DocName
   8. FROM ReportDocName
   9. WHERE ReportID = 62 -- my particular report that has multiple document names
   10. ORDER BY DocName

   This is the logic that I wrote the SplitQuotedString to match. If you change how you escape commas, you'll need to write your own splitting/unescaping function. Run the query to make sure it's working correctly. It should have two columns, one with commas escaped, and one with them unescaped. If you don't have to escape commas in your data, then one column is okay.

   Non-queried: Run a query manually to extract the list you want or locate the values wherever they are. Keep this handy to paste in later.

3. Select Report->Report Parameters. If there isn't already a new parameter automatically added by the fact that your SP now has a new parameter, then Add a new parameter.
   • Name: Give it the same name that you called the parameter in your SP. For me: DocumentNameParam.
   • Data type: String
   • Prompt: Document Names
   • Check the "Multi-value" checkbox. I also checked "Allow blank value" because if no names are specified, I want the SP's list of document names to take over. You cannot choose "Allow null value" with a Multi-value parameter (at least, I couldn't select it and I think that is the reason).
   • Available values:
   - For "Non-queried" paste in each option, one at a time, from the list of options you built earlier.
   - For "From query" choose the Dataset you created that displays the possible list. For "Value field" select the column that has commas escaped, and for "Label field" select the one that is plain. If your Dataset only has one column, use it in both places.
   • Default values:
   - For "Non-queried" paste in each option that you want to be selected by default.
   - For "From query" choose your list Dataset, or perhaps select a special default Dataset you created that is different from the list of all possible options. I used the same Dataset since I want all values to be selected by default.

   

4. If you had to add the multi-value report parameter manually, edit the source Dataset and make sure it is mapped properly. Click on the Data tab, select the correct Dataset, click the "..." edit button, select Parameters, and make sure that the Name is mapped to the Value.

   

Happily Use Your Shiny New Report

I hope that this article has been useful to you, and helps you get a Multi-value parameter added to your SSRS report in no time!

Erik


Afterthoughts

• You could use a query instead of a stored procedure.
• Think carefully before using a multi-valued parameter to solve problems that should be solved by database normalization. A multi-value column in a database is a BAD idea if you ever have to work with the individual items. Don't try to adapt this solution to such a column. Fix the database design instead.

But what if you need to work with data that is longer than 8000 characters? When storing data in a table, you can use the text datatype which is stored out-of-row (though there are options about storing strings shorter than 8k characters in-row and then moving them out-of-row if they grow). But you can't use the text datatype as a variable. Look:

Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

But while that seems definitive, it is not the whole story, because you can use the text data type as a parameter in a stored procedure:

This works fine, but there are limitations on what can be done with that @longdata variable just like there are with text columns, such as the inability to use the Len() function (use DataLength instead).

If you truly must have variables with more than 8000 characters in a stored procedure, but they can't be passed in as parameters, then you are now in the unfortunate position of having to use tables and text pointers. You'll need the functions TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT. With these, you can work with pieces of the long data types (no longer than 8000 characters at a time in a variable).

Here's an example of putting more than 8000 characters into a column in an SP:

You can see that you have to loop over each row, and then loop over each chunk of data you want to read from or write to the column.

Now you know the basics of handling more than 8000 characters in SQL 2000. But there are a few subtle things you should know:

• The 8000-character limitation does not apply to literal strings, just variables and rowsets. This means that with the DisplayText stored procedure above you can put in more than 8000 characters like so:

This does not mean you can somehow exceed 8000 characters with a commonly-tried but mistaken method such as

Sure, this may actually temporarily create a longer string (I don't know for sure) but the final value in the rowset will not exceed 8000 characters.

You can concatenate strings when submitting dynamic SQL statements:

While I can't really recommend this method, if you are desperate and this is the only way to get the job done, it's a possibility. I've personally only used it in two narrow situations: creating pre-processed SQL objects where the dynamic SQL is executed rarely only when something changes, and the code it creates is static until the next change. One creates history-keeping triggers, and the other builds a standard set of pivoted views in response to changes of the metadata definitions of some web objects. Both required this method to be able to handle the number of columns possible.

• SQL 2005 has a new 'max' keyword for the length of the (n)var/char data types that allows variables to be as big as the (n)text datatype can be. You can do anything to them that you could with regular varchar types, but behind the scenes they function like the text data type with values less than 8000 characters in-row and values greater than 8000 characters stored in out-of-row pages.

Max here simply means that the data type can go up to the maximum storage size allowed, which is 2^31-1 bytes (minus 2 more bytes for presumably a length value). Note that you can't specify varchar(16000) or some value over 8000. You only get between 1 and 8000, or the special identifier max.

As a practical example of using a text datatype to defeat the 8000-character limitation in MS SQL Server 2000, here is a SendMail stored procedure that will use the CDO.Message object to send email with a body longer than 8000 characters. Note: put in your mail server name or make it a parameter or perhaps make it read from a table!

The error handling here after each SP_OA SP call uses a custom function I wrote. I found it to be invaluable when debugging problems with OLE calls to various objects from SQL Server. You may either tear out this error handling, call the sp_OAGetErrorInfo procedure yourself, or use mine, which is below (plus a couple of dependent functions that you may want to rewrite/modify/reimplement/stop using).

Here's a sample execution of SendMail for you:

If you are sending email to someone who uses Outlook, it helps to provide both TextBody and HTMLBody, because the TextBody is used in the pop-up preview window of the content, so if the person is present when the email arrives, the preview will work correctly.

I hope that you benefit from this brief but intended-to-be-thorough write-up on exceeding the 8000-character limitation in SQL 2000.

Erik

Not yet!

You do something like this:

With this solution, you can keep your database going for another few years, or for about as long as it took you to fill up the positive integers. Then you'll have to switch to bigints or a decimal data type, though I have to express my disgust for using a packed data type as a clustered index key, especially considering that compared to int or bigint, decimal always uses more bytes of storage for the same precision:

int: 8-9 digits, up to 4294967296 = 4 bytes storage
decimal: 9 digits = 5 bytes storage
bigint: 19-20 digits, up to 18446744073709551616 = 8 bytes storage
decimal: 10-19 digits = 9 bytes storage
decimal: 20-28 digits = 13 bytes storage
decimal: 29-38 digits = 17 bytes storage

So you can see that bigint is far superior to decimal until you get to 20 digits, supporting up to 18.4 quintillion unique values, which is a heck of a lot, for less than decimal would take to get you there.