We are a group of users of Blackbaud products and are not affiliated with Blackbaud. We'd love to have you join our community to help and be helped in getting the most from your Blackbaud software.
Register now to join us to get independant advice on your system, connect with 3rd party consultants to help you maximize your database and have a real alternative to the official Blackbaud website.
View/Access Backend Tables, etc without purchasing additional module?
All-
I may have posted this before, but, is there a way to do this? I'd like to have read-only acess to the back-end tables, data. Is there a way to do this without purchasing an additional module? Thanks!
Go to administrative tools and setup an ODBC connection to your server. Go to MS Access (on that machine), go to new database, name it, go to File -> Import -> Link Tables. Change the type at the bottom to ODBC Databases, select your ODBC connection, put in the username/password if it asks for it (windows auth won't ask for this if you're an administrator), then select your tables and enjoy...
Note, if you do it on the server, the ODBC connection is already created and is called RE7_1...
Doug
__________________ ~~~~~~~~~~~~~~~~~~~
Doug Creek
RE Database Administrator
University of Alaska Foundation sndgc@email.alaska.edu
Actually, as long as Raiser's Edge is installed on the machine, you'll already have the ODBC connection. One thing that Doug didn't mention is that you will need an SQL Server userid. SQL Server does not recognize your RE userid, unless you are using Windows only authentication, which is not typical, because the default is mixed mode authentication.
I actually prefer using Query Analyzer which is part of the MS SQL Tools. This is partly because I'm a touch typist and the MS Access GUI slows me down. I can also do complex joins without having the Access GUI complaining that it doesn't know how to represent them.
The MS SQL Tools come with MS SQL Server, but not MSDE. If you have the disks from another instance of MS SQL Server, you can connect to an MSDE database, though.
Sometimes I will use Crystal as a way to view the back end, particularly if I want to look at various summaries for particular fields. MS SQL has problems with multiple levels of distinct counts, but Crystal handles these easily.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
We operate in a Citrix environment, so I always forget that people will already have the ODBC connection...
However, the reason I suggested Access instead of SQL Server Manager/Query Analyzer is to look at the data (its how I found my way thru the tables when first starting). If you're trying to write complex queries/code/reports that you can't do in query/export within RE, Query Analyzer/Crystal reports are the way to go!
Doug
__________________ ~~~~~~~~~~~~~~~~~~~
Doug Creek
RE Database Administrator
University of Alaska Foundation sndgc@email.alaska.edu
Thank you! I think I will start with the "beginners" step and use access, and then move on to MS SQL server. How would you acess the tables in Crystal?
Also, out of curiousity what types of things have you done accessing the data this way? What are the advantages/benefits?
Got it, thanks! I guess I mean the advantages of using Crystal or MS Query Analyzer as opposed to what's available in RE. I'm a bit of a "newbie" to this...just wondering if there are advantages to working "outside of Raiser'd Edge" so to speak on some things and/or if there are any cool/timesaving things you can do. Thanks for all the advice/tips!
The biggest advantage is 100% control over what you want to see, instead of what BB "thinks" you want to see... Run times are significantly faster over custom reports based on exports(tho there can be problems distributing the reports, as you mentioned in another post. Our solution for our org was to purchase 5 licenses for Crystal Reports Server, if you can find another solution, more power to you ). Also, you have more options available from outside of RE. I've written scripts and reports that you CANNOT do from within RE no matter how good you are with query and export... For example, our annual recognition used to take 3 of us approximately 6 to 8 weeks to pull together to send to the campuses to review, now it takes less then 2 hours to get the information out of RE, another 1 or 2 days for 1 person to do some minor cleanup, then it goes for review.
One of the more nagging problems to be aware of if you move towards doing letters from outside of RE thru crystal reports (as I'm trying to) is that RE doesn't always update the addy/sals in the table. Don't ask me why, its a dump problem that I haven't figured out an easy solution to fixing. So you might run into some bad addy/sals on your letters. That's my current gripe about how BB does some things with the backend, so
The downside for me is I can't afford to buy the API module in order to write my own import module (I abhor what's there and am not willing to pay for the "Advanced Import" module that doesn't seem to work any better), and you aren't allowed to make "backend" changes without invalidating your maintenance contract. I've gotten around some of this by writing views and some functions against a copy of the database, but I can't actually load the information with some added checking of information that you can't do from the front end (for example, comparing multiple addresses and various combinations of the name all at once instead of having to run it once, go change it in duplicate information, then running it again, etc).
Anyways, I'll quit rambling (or preaching, depending on who you ask ).
Doug
__________________ ~~~~~~~~~~~~~~~~~~~
Doug Creek
RE Database Administrator
University of Alaska Foundation sndgc@email.alaska.edu
The advantages are speed, flexibility, speed, power, and speed.
I once did a test of consecutive years of giving using SQL. I got the results for our entire database (250,000+ constituents) in about 27 seconds.
I just finished an export of 250,000 records with about 50 fields per record including summaries for all giving and giving for each of the last five fiscal years. It took 7 minutes. My colleague used RE Export to export information on 2,550 records and it took almost 7 hours. That's 60 times longer for 1% of the number of records.
I can create views and report off the views.
I can always use fiscal years instead of just the times that BB thinks that I should be able to use them.
I can compare two fields.
I can create joins on any expression rather being limited to {field1} = {field2}. This is particularly important when creating outer joins.
I could go on, but I need to get back to work.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
That sounds wonderful! What training tools/tutorials would you recommend for SQL Server/Query Analyzer?
Well, I'm mostly self-taught. I created queries in MS Access and then looked at how it was written. MS Access SQL and MS SQL Server's T-SQL are similar enough that this works. From there I studied MS SQL Server Books On Line (BOL) extensively. I also found SQL Server magazine to be quite helpful, especially the column "T-SQL Black Belt".
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
That sounds wonderful! What training tools/tutorials would you recommend for SQL Server/Query Analyzer?
I'll let others chime in on books/documentation about the MS Query Analyzer program itself, but below are some SQL-related resources that may be helpful:
If you don't have Microsoft's Query Analyzer tool available, here's a Query Analyzer-like open source application (may not be in active development anymore, but I used it as recent as 2007 and it worked moderately well): SQL Buddy Sourceforge Site
This access is read-only correct? So no worries of overriding data?
Yes, you select RE_1 unless you have multiple databases. RE_50 is the sample database.
As I mentioned earlier.
Quote:
Originally Posted by DrewAllen
you will need an SQL Server userid. SQL Server does not recognize your RE userid, unless you are using Windows only authentication, which is not typical, because the default is mixed mode authentication.
Your DBA should be able to set you up with an SQL userid. Mine are set up with Windows Authentication, so that I can just click on the "Trusted Connection" and log in.
The rights that you have depend on how this SQL userid is set up. Mine is set up as dbo, but anyone else I set up as just data_reader.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
