29 posts tagged with "sql-server"

I've been having problems migrating some SSIS packages from localhost (32bit) to the server (Windows Server 2003 64bit, SQL Server 2005 Enterprise SP2). These packages have Excel data connectors, and hence must be run in 32 bit mode. When I try to run them with the 32bit dtexec, I get the "generic" error "the package failed to load" (0xC0010014). I stripped down the package until I found the source of the error: a ForEach container, using the File Enumerator with the directory set to a variable. When I remove the variable setting it works, when I add the variable expression it fails. Note: this does not fail in the 64bit dtexec.

We recently returned to the discussion of using XML style transforms to format flat file output from Reporting Services. The report we created was pretty basic, so let's look at something more complex: a fixed format.

Let's say the client has given us the following file format, and we've trimmed the data in the SQL query to make sure it doesn't exceed the data width:

In the third installment of a series, we defined some intended flat file output, designed a simple report in MSSQL Reporting Services, and looked at the plain XML output from Reporting Services. Now, long after that post, we can create and apply the XLST needed to convert that report to the desired output.

Good article on using indexed views: Indexed Views Basics in SQL Server. Similar good coverage in Ben-Gan's Inside Microsoft SQL Server 2005: T-SQL Programming. Problem: both specify that the hint WITH(NOEXPAND) needs to be used when executing a query against the index on anything other than Enterprise and Developer. However, I've found that it is also required for Enterprise.

Problem​

SQL Server 2005 queries that used to be fast are now rather slow. You've already tuned the query with good indexes and optimized structures. You've checked the running jobs with Activity Monitor and don't see anything that should be slowing down the server. Likewise you've checked the memory and CPU utilization and they don't seem out of line. Did you check the indexes fragmentation? Ah, there's the problem — a highly fragmented index was slowing things down. Is there a way to pre-emptively find and fix these?

Problem​

I have an SSIS package, MyPackage, stored on MyServer in the Package Store. I create an Agent to run the package, running under a SQL account hooked up to a proper proxy and credentials for Windows authentication. Works in development, doesn't work in production: the agent gets the error Connect to SSIS Service on machine "MyServer" failed: Access is denied.

I mentioned using the XML datatype for sparse column support in my recent post on some of the Useful Features in SQL Server 2008. Until today I had never had an opportunity to actually use this feature. I was pleasantly surprised at how easy it was to use, especially with some help from Itzik Ben-Gan's Inside SQL Server 2005: T-SQL Programming.

Problem​

I've begun using data-driven subscriptions in SQL Server Reporting Services. I want to create one table to house subscription data. Various reports will have different parameters, both in number and kind. It is impractical to create one column for each possible parameter, and seems senseless to create a separate table for every report.

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.

Problem​

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:

Problem​

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".

Solution​

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.