SQL Server 2008 will be coming out sometime this summer (in theory). At last week's TechFuse (dead link removed; SF 2025) event in Minneapolis, and in blogs I sometimes read, I've started to pick up on a number of useful features and improvements that should make one strongly consider upgrading when the Release to Market (RTM) is ready (certainly this is not all-inclusive):
The last two posts have touched on some issues related to Reporting Services and XML transforms. Now I'll go back and put the pieces together, flesh in some more detail, and try to give a coherent picture of what I was trying to accomplish and how it worked out. Doing so will require a sub-series of posts, call them parts 3.x to the "RS and XSLT" series.
A customer has asked for a report, in plain ASCII text, that will contain a header record, a bunch of pipe delimited detail records, and a trailer record. Reporting Services' subscription capabilities are ideal for the report delivery, but does not have any built-in way to support this kind of output. One can use CSV export, but:
you try to deploy your xslt file from Visual Studio, and get an error saying "Value for the parameter 'MimeType' is not specified. it is either missing from the function call, or it is set to null".
In Solution Explorer, click on the xsl file. Then switch to the Properties pane and enter "text/xml" as the MimeType. Note: this is not required when deploying the xsl through Report Manager's upload button.
It was driving me nuts — my transforms just wouldn't apply. I tried
working with some fake data, from my little-used O'Reilly XML in a Nutshell, and that
worked fine. So why couldn't I transform my Reporting Services output? After
banging my head against this one for a while, I finally decided to mess around
with the root <Report ... /> node, first by removing all the extra
elements (because my samples did not have any elements in the root node).
Voilà, the transform now works. Why is that?
periodically, one of my SSIS packages was throwing an error saying
"The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission." The package in question then would hang, locking a file that it
was trying to import.
I was looking at a query today that has an inline expression in it, something similar to
CASE WHEN myTable.myColumn < 5 THEN NULL ELSE myTable.myColumn END
This particular piece of business logic is used in many different places (well,
not literally, since I made up a fake example), so I created a UDF to simplify
the queries that use this statement. Suppose that UDF is called
dbo.fnNulifyMyColumn.
In SSIS, you want to update a variable from a Script Component embedded in a Data Transform task. You get an error message reading "the collection of variables locked for read and write access is not available outside of postexecute". What's up?
You have some XML, and you need to validate it against a custom schema that you want to deploy with your .Net 2.0 assemblies. Issues addressed: opening the file and handling the schema validation.
The XmlSchema class contains a
Read
method that takes a stream as a parameter. Nicely matching up to that, you can
open a stream from an embedded resource (dead link removed; SF 2025).So, embed
the schema. In Visual Studio's Solution Explorer, right-click the schema file
and choose properties. Change the Build Action to "Embedded Resource".
you have image data from an HTML e-mail, but don't know what to do
with it. When you save it to disk it comes out with a weird string instead of binary data.
pretty simple, you just have to know the terms. This string of
data is actually Base64 encoded, which means that the binary data has been
converted to a text string for easy transmission over text protocols. .Net
Developer's Journal (dead link removed; SF 2025) has a good full explanation.
All you have to do is convert the string over to a byte array using
Convert.FromBase64String, then save your new byte[].
You want to run NUnit tests in a class library (dll). These test rely on an application configuration file (app.config) for some settings, i.e. custom appSettings or database connection strings. The code compiles and runs fine by itself, but your unit tests always fail. Attaching the Visual Studio debugger to NUnit and stepping through the code, you see that the config seems to be ignored.
