SSRS and CSV format

Using SQL Server 2005 Reporting Services, I created a timed subscription to save a report as a CSV file to a Windows file share. However, the user was unable to import the file into their salesforce.com application.

Turns out that the default export format for CSV uses Unicode encoding, so if you open the file with Excel, each row of the data ends up in the first column.

Luckily SSRS has a file rsreportserver.config where you can add or modify the export formats. The config file is an xml file which in my case was located at

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer

(note: the wysiwyg on blogger has issues with angle brackets, so I am showing square brackets below)

Locate the section [Render] and you will find entries for all the formats; find the line that begins with [Extension Name = "CSV" and add the Configuration, Device Info, and Encoding:

[Render]
[Extension Name="CSV" Type=" ..."]
[Configuration]
[DeviceInfo]
[Encoding]ASCII[/Encoding]
[/DeviceInfo]
[/Configuration]
[/Extension]
[/Render]

Note: for the "Extension Name" entry, the default config file has a closing slash "/" that has to be removed for this to work.

After making this change, just to be safe I stopped and re-started IIS. After which it worked just fine, delivering an ASCII-encoded file that could be opening with Excel 2003 and imported into Salesforce.

Resize query columns

My MS-Access application has several buttons that display a query in datasheet view, so users can sort, filter or export the results. I'm frequently disappointed in the appearance when the columns are either too narrow or wide; of course you can easily resize columns with a double-click, but Access doesn't work the same as Excel, i.e. Access considers only the visible rows when it resizes.

I Google'd for a solution and found the article:
You Can Do That with Datasheets?
and the same code is presented here:
Resizing column to best fit vba
...and the answer relies on two important concepts:

• When you open a form in datasheet view, if you set ColumnWidth = -2 then Access will resize the columns to their proper widths.
• You can open a table or query in datasheet view, and then Dim and Set a Form object to it.
  

The code was a little wordy so I distilled it into the following:

Sub resize_query(query_name As String)
Dim frm As Form, ctl As Control
'
DoCmd.OpenQuery query_name, acViewNormal
Set frm = Screen.ActiveDatasheet
For Each ctl In frm.Controls
ctl.ColumnWidth = -2
Next ctl
DoCmd.Close acQuery, query_name, acSaveYes
End Sub

Now, if you have a Forms parameter in the query, you will need to have the corresponding form open when you run this code. Otherwise it will prompt you for the parameter(s).

EDIT on August 29:

I have been testing this code on a variety of queries and found that it does not work 100% of the time. Thinking that I might have left out something important, I returned to the original article mentioned above, and it also does not work 100% of the time. Will continue to test this out & hopefully post the final solution.

Stored procedure text

SQL Server has a system view sys.syscomments that holds the sql text for all of your stored procedures, views, triggers, UDF's, default constraints and computed columns.

I was considering using BULK INSERT to import a file but hadn't used it for a couple years, so I wanted to search some old scripts to get the basic syntax. I Google'd "SQL Server stored procedure text" and found a short article by Pinal Dave SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object which mentions sp_helptext, but you need to know the name of the sp in order to find it; catch-22.

So I opened up sp_helptext and found it was using sys.syscomments to get the sql text; from there it was easy to write:

SELECT OBJECT_NAME(id), [text]
FROM sys.syscomments
WHERE [text] LIKE '%BULK INSERT%'

...which in my case returned 7 rows. Eureka!

It is important to note that [text] is nvarchar(4000) so in the event that your sql text is larger than that, there will be multiple rows for that item, and the order is found in the column colid. So then, to get the complete text for an sp named "my_sp" you would use this:

SELECT object_name(id), colid, [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'my_sp'
ORDER BY 2

Now, sys.syscomments view does not have an identifier for the type of object, so if you normally use prefixes when naming your objects e.g. viw for views, trg for triggers etc. that will make it much easier to locate the desired sql text.

Drop temp table

By definition, when a batch completes in SQL Server, any local temp tables created are automatically dropped. So then, if your stored procedure creates a local temp table such as #temp it will be dropped when the sp completes, and you don't need to include the statement DROP TABLE #temp to release resources in tempdb.

However, when you are developing a stored procedure, and the batch fails due to some type of error, the temp table will remain; if you re-run the batch you will get a message that #temp already exists, and you need to drop the temp table before you can run it again.

You can include a statement like this at the beginning of your batch, to prevent that from happening:

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #temp

ODBC without a DSN

For years, I have been using MS-Access to connect to SQL Server databases using DSN's, and the process has always been painful & frustrating: do you create a User, System, or File DSN? Each has its advantages, but User / System requires a visit to each PC to config the DSN; In some cases having to log in with Admin credentials to make that happen. And a File DSN sits out there somewhere on the network which makes me nervous.

I had read about "DSN-less" connections but never explored that option until I used the SQL Server Data Migration Assistant (SSMA) to migrate the data from an Access database into SQL Server. After reviewing the wizard output. this approach is definitely easy to implement using a connection string.

Now, connection strings can also be scary; writing one from scratch can be a challenge because of so many options. But if we narrow the focus to MS-Access 2000-2003 connecting to SQL 2005, after you have one actually working you can easily modify it.

The basic connection string looks like the following. I am using a Global Constant My_Connection, and I've broken out each portion for clarity, but the actual string should be all on one line:

Global Const My_Connection =
"ODBC;
DRIVER=SQL Native Client;
SERVER=my_server;
UID=my_username;
PWD=my_password;
APP=my_appname;
WSID=my_workstation;
DATABASE=my_database;"

There is some code, not shown here, which refers to the My_Connection string & then loops thru the tables to update the connections.

The approach works just fine if you are using SQL Server Authentication. We decided that this would be the easiest to manage since we don't have to maintain all the various windows ID's; plus, when we grant permissions we can simply say "GRANT SELECT ON dbo.my_tablename TO my_username". The application calls a Windows API to get the network ID and this controls who gets to do what within the app itself.

Now, if you have multiple SQL instances installed, e.g. if the development instance is called DEV you only need to change SERVER = my_server\DEV and it works fine; just remember to also change the my_username and my_password since those are usually different for the production servers.

One more little trick - if you are working remotely using a VPN connection, because Windows uses nslookup to find the IP address for the server name, this can cause a timeout & prevent you from connecting. The solution here is to replace the my_server_name with the actual IP address, e.g. if your server name is my_server and the IP adress is 192.168.1.2 then you can substitute that IP address for my_server. For a named instance it would be 192.168.1.2\DEV.

In our case, each of the possible connection strings to the various production, QA, DEV, and local testing database are written in plain text in a standard module; so after we run the "relink code" we delete all those strings and add a line Global Const My_Connection = "" so all the sensitive link data is removed, being replaced by a do-nothing statement that only exists to prevent compile errors.

Office 2010, part 3

In the Access 2010 Beta, if you open a file from an Explorer window or a shortcut, you get the dreaded

The command or action '' isn't available now.

But the app works perfectly after dismissing that message. This one had me stumped; tried decompile, compile & save, compact & repair; nothing fixed it. Luckily a few minutes of Googling revealed the answer: if you open that same file from within Access you don't get that message.

So then, to prevent that message all you have to do is create a shortcut that launches Access and opens the file, like this (watch for line breaks):

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" C:\mydatabase.mdb

Office 2010, part 2

Today I decided to resolve the Access calendar issue, which is: I have a popup form that uses the Calendar 2.0 control, but Access 2010 does not support ActiveX controls. Yes, I know that starting with version 2007, Access provides a date picker icon that displays wherever you have a text box that has a date format (you can turn that off), but navigating is more cumbersome compared to the old calendar control.

Fortunately the old Access 97 Developer's Handbook has a calendar form which does not use ActiveX, and it does work with Access 2007 and 2010; better still, I emailed the author Ken Getz, and he quickly replied that his code is free to use without any licensing, provided that the credits remain in the modules. His solution is comprised of two forms (the embeddable calendar form, and a calendar test form) and one standard module.

I modified the test form with OK and Cancel buttons & a line of code to replace the form caption with a custom message from a global String variable. Works like a charm! Thanks Mr. Getz.

Now I can retrofit my applications with the new version of my date popup form & have them compatible with Access 2010 when it is released.

Scripted Mass Updates

This article describes a method to generate scripts to perform mass-updates of data. The purpose is to document the updates that are done, for future reference.

Let's say you have a table of customers, and each is assigned to a region based on the state being east or west of the Mississippi River. Therefore, Illinois is assigned to the East. However, in order to balance the workload, the users have asked us to move all Illinois cities to the West region. The table looks like this:

ID int identity
Customer_Name nvarchar(50)
ST char(2)
Region nvarchar(50)

You could write a simple update query like this:

UPDATE Customer
SET Region = 'West'
WHERE ST = 'IL'

However there will be no record of exactly what was updated; if new Illinois cities are added they might be incorrectly placed in the old region, and we might wonder if the update failed for that city. Or, the users might ask which cities had been moved. The solution is to build a SELECT statement that generates one update statement for each record, like this:


SELECT
'UPDATE Customer
SET Region = "West"
WHERE ID = ' +
CAST(ID AS varchar) +
' AND ST = "IL"
AND Region = "East" '
FROM
Customer
WHERE
ST = 'IL'
AND Region = 'East'

..when you run this query, you will get one "record" per city to be updated; each line being similar to this:

UPDATE Customer SET Region = "West" WHERE ID = 2266403 AND ST = "IL" AND Region = "East"

Now, you would copy the output from the results window into a new query window, replace the double-quotes " with single-quotes ' and you can execute the update & save the code to have a permanent record of precisely what was changed.

This technique can be applied to more complex scenarios. For example, you might export a spreadsheet so users can easily back fill missing data, and then import that back into the database. In that situation, you would not be able to reconstruct the changes made unless you keep a copy of the imported data in an extra table somewhere. By scripting the updates you can document precisely what changed.

One final note - this technique will work reliably only if you execute the "SELECT" query to the results window; if you select Results to Text there is a limit to how many characters are displayed, so longer statements may be truncated.

Office 2010, part 1

I have been working with the Office 2010 Beta for about a week now. I downloaded the Professional Plus version on January 17 and installed on my test PC which is an old Pentium III with 512 RAM running Windows Server 2003. The install was quite similar to the Office 2007 install.

The program launches faster than 2007. Gone is the Office Button, in favor of a File tab on the ribbon which brings up the Backstage area which has basically the same content as the button. For the beta there are two smiley icons in the system tray, Send a Smile and Send a Frown (I am not kidding) for feedback to Microsoft.

My first real test today was with Access 2010, using a time & billing program that I use for consulting work. The front-end is Access 2000 format, and the back-end is Access 97. I run the app using Access 2003.

When I first opened the file, even while holding the Shift key, I got an error message

The command or action '' isn't available now.

I went back to 2003 and decompiled the app & then compiled & saved, since this is oftentimes a quick fix for unexplained errors; that did not help. The app has an AutoExec macro that opens a form and then maximizes the window; once the file is open, I can run the macro, or open the form, without any error message. Obviously Access 2010 is doing something I don't know about. I visited the Trust Center & enabled the macros, and also set the ActiveX to be wide open but the error message still shows upon opening the file. This test PC does not have any anti-virus software installed, so that is not a factor either.

The app has a popup form with a Calendar 2.0 control, but alas 2010 does not support it. Whilst browsing the dev info for hints, I noted that data access pages (DAP's) are on the way out - never used them myself - 2010 does not support creation but apparently still runs them.

There are a couple of other things that don't seem right. When you have the Navigation Pane open and the ribbon is at the Home tab, you can select a form or report & the Design button shows, but it does not show for the other objects tables queries etc. But you can right-click on the object to get into design view.

Also, if the ribbon is minimized & you preview a maximized report, there is no close button or "x" so you need to either right-click and Close, or press Ctrl-F4 which still works. You can add the "Close Preview" button to the Quick Access Toolbar, but it takes 2 clicks to close the report - 1st click shows a red Close Preview button, you have to click that to close the report.

Otherwise the app seems to work OK. Next I will try some design changes & see how that all shakes out. . .