SQL

Refreshing with production databases

By: Chris Whisenhunt

Posted on: January 26, 2016

One of the things that organizations tend to want or need during and after implementation are database refreshes. Right now we have 3 database servers: production, staging, and test. Full backups are taken of every user database on the servers every night. I setup a job that would automatically move them nightly after the backups have been taken. I then wrote a generic script that would restore them when needed. Therefore if I'm out sick and support or users need something refreshed it can be done with as few clicks as possible, may 4 after you have remote desktoped in? I added a new folder wh... Read More
Categories: ,

Writing Efficient Search Lists

By: Enterprise Singularity Team

Posted on: September 4, 2013

When you create a search list with the Infinity SDK, it is important to write an efficient SQL stored procedure so that the search list returns results as quickly as possible. To do so, you must adhere to a set of performance principles: Choose highly selective filters Use covering indexes to retrieve results Return only the columns that you absolutely need Limit the number of tables referenced by a query Additionally, consider the following advice when writing search lists: Avoid “or” statements – If you use an OR statement to link filters from different tables, the optimizer cannot use index... Read More

BBDW 3.0 – Ad-Hoc Query

By: Ron Frum

Posted on: April 5, 2013

I’m very excited about the release of Blackbaud CRM 3.0. My favorite new feature is the ability to build ad-hoc queries against the Blackbaud Data Warehouse using query views. Query views against BBDW can do most of the things that query views against the Infinity database can do including: Save queries for later re-use Export directly from the query results grid Create an export process Create an export definition Create a smart query based on an ad-hoc query Create a report using Report Builder Save a static selection (note that dynamic selections are not supported at this time) Use a static... Read More

Using SQLCMD to execute large SQL batches

By: Joseph Styons

Posted on: February 8, 2013

Have you ever had a very large SQL script in a .SQL file, and you wanted to run it against your favorite database? You may have naively thought (as I did) that you could just crack that baby open in SQL Server Management Studio, and hit F5. If you did that, you may have found that SSMS doesn’t take well to those.  It will sometimes give you an “out of memory” error. That can be overcome by running the script from inside SQLCMD.   This is an example script for doing that 1: set sqlcmdexe=C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe 2: set domain=BLACKBAUDHOST 3: set user... Read More

Converting a standard datetime to a UDT_FUZZYDATE

By: Joseph Styons

Posted on: December 18, 2012

CRM comes with a user-defined type called “UDT_FUZZYDATE”. If you look at that datatype, you’ll notice that it’s just a CHAR(8) field. Converting from a standard datetime to a UDT_FUZZYDATE can be done pretty easily using native SQL. Anybody reading this blog could figure this out on their own, but here it is just to save you the typing. if exists(select * from information_schema.routines where routine_name = 'usr_ufn_datetime_to_fuzzydate') begin drop function usr_ufn_datetime_to_fuzzydate; end; GO create function usr_ufn_datetime_to_fuzzydate ( @dt as datetime ) returns udt_fuzzydate as begi... Read More
Categories: ,

Varchar(Max) May Not Be As Big As You Think

By: Dempsey Williams

Posted on: December 10, 2012

So I'm all set to begin creating this really long dynamic SQL statement. I know it's going to be thousands of characters long so I think to myself "This is the perfect time to use a varchar(max)." I diligently get to work writing out all my SQL statements and storing them in a ginormous (checkit, that's a real world) string variable with a varchar(max) datatype. When I execute the string for the first time though, I get a syntax error. Well, I think to myself "That's to be expected. I did just write out a ton of SQL so I had to have a typo or something." As I dig into and debug my SQL I soon r... Read More
Categories:

Using SQL Server Profiler Templates To Capture Infinity SQL Statements

By: Bob Stefkovich

Posted on: November 9, 2012

How often do you run SQL Server Profiler without looking at the Trace Properties window (see below)? I do it almost every time… but, I have it customized to show what I want to see. I use Profiler templates to store my trace settings. You can select from a number of built-in templates, or you can create your own template to organize what is captured and how it is displayed. You can configure many properties when you start a new trace in SQL Server Profiler. You can select a file or table where your results are saved. You can select events, define filters and organize columns. The default trace... Read More
Categories:

The "Numbers" table

By: TripOtt

Posted on: September 5, 2012

I recently overheard the question at Blackbaud the other day, “What is the “Numbers” table used for?.  We’ll let me try to relay an answer to you good people.  The numbers table can be a pretty powerful thing to use.  Often times in SQL, you find yourself needing to generate numbers or finding gaps in numbered sequences.  You can use it to order things sequentially when querying for data.  For example, you can use them to help generate date/time in your queries: DECLARE @STARTDATE as DATETIME = '1/1/2012' DECLARE @ENDDATE as DATETIME  = '12/31/2012' select DATEADD(d,NUM,@STARTDATE) from NUMBER... Read More
Categories: , ,

How to build a data list against the Blackbaud Data Warehouse

By: Ron Frum

Posted on: August 23, 2012

In version 2.94, we added a new capability which allows a developer to author a data list that uses the Blackbaud Data Warehouse as a data source. Before we dig into the code, let’s take a look at some reasons why you may choose to use the Blackbaud Data Warehouse in your data list as opposed to the Infinity database: The database is smaller. Although results may vary depending on the makeup of the data, the SQL Server database for the data warehouse is generally 10-30% of the total size of the transactional Infinity database. If the Infinity database is 200 GB, I would expect the data warehou... Read More
Categories:

Using the BypassProcessorUpdate flag on smart fields

By: Ron Frum

Posted on: May 25, 2012

Smart fields are a great way to speed up the end user experience by performing complex calculations over night or on a scheduled basis so that the results are cached and available to users when needed. For information on getting started with smart fields, I would recommend reviewing the Smart Field SDK documentation. For some general guidelines on improving smart field performance, I would also recommend my previous post.   In version 2.91, we added the BypassProcessorUpdate flag to smart field specs. In doing some performance evaluations of our existing smart fields, we found that the pr... Read More
Categories:

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean