Is it possible to use Microsoft Query to access the RE database? If so, how?

 We would like to query the database (inquiry only), but using MS/Q don't seem to have access and/or authority.

Any help appreciated!

Seattle Aquarium

 Hi Jeff,

 It's definitely possible. I just connected through Microsoft Query (via Excel 2007) and was able to pull gift information out of my RE database.

From Excel, choose "Get External Data" then "From Microsoft Query". You'll need to know which DSN points at your RE database (it's probably called "RE_1"), and you'll want to use the same username/password to connect that you use to log in to RE. (If you're using Windows Auth for your RE user account, then check the 'Use Trusted Connection' box.)

I could be more helpful if I knew what version of Office you had, what version of RE, and what your database platform is (SQL Express, SQL Server 2005, etc). Also, could you shed a little more light on what information you're trying to retrieve?

Hi Ken -- thanks for the response.

Not sure what's going on here, except I think it's an authority problem associated with the OS and/or server. As a programmer who know's SQL, if I can get access to the database with an SQL application, I know I can automate some of the work being done manually here.

We have SQL Server 2005, and Raiser's Edge Version 7.91.40.0. Unfortunately, I am not an expert on either -- just doing some volunteer work for the Aquarium.

The two errors we get are:

Login failed for user 'xxxx\xxxx'

and

Connection failed: SQLState: '28000' SQL Server Error: 18456 [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'xxxx\xxxx'

All of my own research has come up with nothing.  Ironic that we can't seem to get past this problem -- this is Seattle, and Bill Gates' house is within spitting distance!

Any help or suggestions greatly appreciated!

Seattle Aquarium

Yup, those are authentication failures. It's authenticating against the users stored within SQL Server, which gets a little dicey. Do you have direct access to the SQL Server box? Could you create a user account within SQL Server if you wanted to? If so, I can talk you through how to do that and how to grant it read-only access to your RE data.

Or, if you can get an RE user to give you their valid user/pass, that should work here. If nothing mentioned is an option, then your best bet is to connect as "sa" for the username. You'll need to contact the server administrator to get the SA password, and they're not going to want to give that out, since it's "God" rights to the database. The other option would be if you know the user/pass of anyone in the local Administrators group on the SQL box, that should work too.

You might have the best luck trying to persuade the server admin to create a read-only user in SQL Server for you.

I'm very familiar with the backend of RE, and write SQL statements all day long, so once you get in if you need help with your queries, feel free to ask!

Hi Ken -- your input is greatly appreciated!

We've decided that before we do anything with the live database, we want to experiment on the sample database. I've downloaded a copy of the sample database to a hard disk on a PC. It consists of the following files: RE7.mdf, RE7_bio.mdf, RE7_gft.mdf, RE7_index.mdf, RE7_log.ldf, and RE7_temp.mdf.

I don't seem to be able to access any of these with MS/Q, either -- although the error I receive is not the same as before (says "can't open the file.")

I'm at the end of my knowledge and experience with this, and haven't found any resources through my own research to resolve the problem.

Can you tell me what I'm doing wrong? Or recommend a good book?! ;-D

Seattle Aquarium

 Hi Jeff,

You need SQL Server. Or SQL Server Express.

Let's back up a little: the Raiser's Edge database, at the file system level, has very little to do with Access or Excel. It's true that the database "lives" inside a few files on the file system, but those files are not designed to be accessed from outside the SQL Server program. Ever. I don't know of any way to pull the data out of an .mdf file using MSQuery or Crystal Reports or any other relational database reporting tools.

The RE database is designed to run within the SQL Server environment. The program "SQL Server 2005 Express Edition" is a free download from Microsoft, and can run fairly easily on your local machine. Make sure to remove it when you're done with your test environment, though, since it runs in the background all the time. It'll slow down your machine even if you're not using it.

Anyway, SQL Server needs keeps two kinds of files: the MDF and the LDF. The MDF file -- called the "data file" -- contains all the data that is stored in RE. Blackbaud has created multiple MDFs, but that's normal. The only MDF file you'll ever work with directly (which we'll get to in a minute) is RE7.mdf. The other kind of file you'll see is an LDF, and this stores a log file, which is officially known as the "transaction log". (If you want to sound like a cool database guy, call it a "t-log". Sounds techy!) The transaction log stores a real-time list of everything that happens in the database, and is used to "roll back" the database to a point in time. If you accidentally delete a batch of 50 gifts, you can restore the database to the moment in time in the transaction log just before you deleted the batch. Cool stuff.

Anyway, here's the basic steps. You may need to google a few of these, but they're all very straightforward:

1. Download SQL Server 2005 Express from the link above 
2. Install SQL Express using the default options for everything
3. Copy the RE MDF/LDF files into c:\program files\microsoft sql server\mssql1\data*
4. Download the SQL 2005 Express Management Studio (called the "SSMSE")
5. Install the SSMSE
   
6. Open the SSMSE and look for how to "attach a database"
7. When it asks you what to attach, browse to the RE7.MDF file in c:\program files\blah blah blah...
   
8. Complete the attach process, and pay attention to the name of the database. You'll need that in MS Query.
   
9. Once you get that far, you should be able to open MS Query, and as long as you tell it to look for a SQL Server running on your local machine, it should figure out the rest.
   

Another idea, which I've implemented, is to restore a backup of your live database to a standalone machine running RE (and EE, if applicable; you can even configure integration ).  That way you can do whatever you need without risking the production database (either in terms of data integrity or performance).

If the file structure of your standalone machine doesn't mimic the file structure on the server you may need to tweak the placement of the files.

Ron and Ken -- thanks for your help. Sorry for the late reply. I will try your ideas today and let you know the results. Thanks again!

Seattle Aquarium