SQL Server: How to read from a stored procedure like a table

I wrote a really spiffy stored procedure for my client. It returns a bunch of rows, just like a table. Today the client asked if I could run the stored procedure with a WHERE clause (a perfectly reasonable request). I didn't know how to do that. But now I do:

It was actually quite simple. As far as I can see, the only trick here was I needed to have the user name and password of a SQL Server user:

-- Parameters that take single quotes
-- need two single quotes
Select * from Openrowset(
'SQLOLEDB.1'
,'MYSERVER';'joe_user';'password1'
,'exec mydatabase.dbo.my_stored_procedure ''7/1/2001'')
where address like '%elm%'
order by 7


This takes advantage of the OPENROWSET function. OPENROWSET lets you do magical things, like one-time ad hoc queries from Access and Excel and CSV. The way I'm using it is probably a bastardization, but so is everything that works well in SQL Server. As we said in the Marines, if it looks stupid but it works, it's not stupid.

Three things that can go wrong:
1) Don't forget to prefix the stored procedure name with the database name and owner (in my example, this is "mydatabase.dbo.".
2) The user you select has to have execute rights on the stored procedure.
3) this worked great in SQL 2000. It also worked great in SQL 2005, however, when I first went to do it, it failed with the following (horrible) error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server...

I had to go directly to the server, and run the "SQL Server Surface Area Configuration" utility (Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration). From there I added myself as an administrator, then clicked on "Surface Area Configuration for Features". Under "Database Engine" I clicked "Ad Hoc Remote Queries", then a checkbox appeared for "Enable OPENROWSET and OPENDATASET support". I checked this. After that, the error stopped happening and I was able to run my SQL statement.

How to import dBase tables into SQL 2005 using SSIS

Ah, SQL Server Integration Services (SSIS). Just when I'd learned to love Data Transformation Services (DTS) in SQL 2000, they completely changed how Extraction, Transformation, and Loading (ETL) is done in SQL Server 2005.

That's okay. Something else to throw on the resume.

At my current position I have to read in dBase files (DBF) a lot. We use ESRI ArcMap and everyone here uses ESRI shape files, which put data in DBFs. So I had to learn how to read in DBFs in SSIS.

I was going to go into all the troubles I had doing this, but the fact is there is just so much that can go wrong here. Here is one way to make things go right:

1. Change the name of the DBF file you want to read in to be 8.3 format. That is, 8 characters before the dot and 3 after. e.g. If the data file is named "2007 Data Load for Finance.DBF", change it to FIN2007.DBF or some such. SSIS will reckognize the DBF if the file name is not in 8.3 format, but will not be able to read data from it. This caused me much heartache before I figured it out.
2. In SSIS, set up an OLE DB Source. Hit the "New..." button for the OLE DB connection manager". Choose "Native OLD DB\Microsoft Jet 4.0 OLE DB Provider" as the provider.
3. For the Database File Name, put in the path to the DBF, but not the DBF file name itself. You will not be able to browse for this, because SSIS is looking for an MDB file at this point, which is not what you want. e.g. if your DBF is in c:\databases\FIN2007.DBF, put in "C:\databases\".
4. Click the "All" button, scroll up to Extended Properties, and put in "dbase 5.0". If you don't do this, SSIS will try to read your dBase file as an Access file, which will fail. While you're here, you can hit "Test Connection" and it should work.
5. Hit OK until you are back at the OLE DB Source Editor screen. Choose the name of the database file from "Name of the table or the view".
6. At this point you should be able to hit "Preview..." and see your data. You can now use this DBF connection as a data source.

SQL Server: SSIS error: Cannot create connector...

I've been using SQL 2005 SSIS (SQL Server Integration Services) more and more lately. It is a big departure from SQL 2000 DTS (Data Transformation Services), but actually grows on you after a while.

I get the following error pretty often:

Cannot create connector.
The destination component does not have any available inputs for use in creating a path.

If you get this, happy you. This simply means that you have tried to use an OLE DB Source as an OLE DB Destination. The Fix: Simply delete the OLE DB Source and replace it with an OLE DB Destination block, being careful to set all of the connection parameters correctly. Wish they were all this easy.

SQL Server: "All Values" parameters in the WHERE clause

Recently, I put a parameter in a WHERE clause in a stored procedure so my client could select values from a list in SQL Reporting Services:

projectID	projectTypeID	projectManagerID	projectName
1	100	200	TPS Reports
2	100	201	Interest rounding exploit
3	101	200	FLARE evaluation
4	101	201	B.E. Channel Referral

select *
from projects
where projectTypeID = @projectTypeID

The client liked it a lot, but then he wanted to add an "All Project Types" option to the list. When the "All Project Types" option was selected, the parameter would be set to -1, and the WHERE clause would essentially go away.

I initially solved this with a temporary table and an IF clause, but found that SQL Reporting Services is squirrely about temporary tables. So I found this sweet little solution:

select * from projects
where @projectTypeID = -1
or projectTypeID = @projectTypeID

Which is really quite smart, isn't it? When the parameter is -1, the first part of the WHERE clause is TRUE, and TRUE OR anything is TRUE. When the WHERE clause evalutuates to TRUE always, then all rows are returned. If @projectTypeID is not -1, for example 100, then the first part of the WHERE clause is FALSE, but FALSE ORed with TRUE is still TRUE. So in our example of @projectTypeID = 100, two rows will be returned.

There is one last wiggle. My client wanted to be able to specify "All Project Types" and "All Project Managers". The same logic works, but you have to write the WHERE clause correctly:

select * from projects
where (@projectTypeID = -1 or projectTypeID = @projectTypeID)
and (@projectManagerID = -1 or projectManagerID = @projectManagerID)


See if you can trick out the logic your self on this.

Special thanks to Ilo at Ilo's Quest for this trick.