Home › Programming & Web Design

Databases

Dynamic SQL Cursors

Tue, 2007-05-15 15:10 — hagrin

Today, at work, I was working on creating an application that would perform validated, bulk updates to our accounting system software (Deltek Vision). So, I create a very simple Windows Form, create a real quick GUI, write all the necessary validation and then started writing the stored procedures needed to perform each of the functions. I had already written the SQL syntax as before this application I was manually changing the variables to fit the needs of our business units. However, as I was trying to "generalize" the statements to allow for different types of queries, I started to run into a SQL problem. I was attempting to use dynamic cursors that were being fed by dynamic SQL statements. Yikes.

In English, I had created two cursors - one to read through all the "target" projects and one for all the "source" information. I also gave the user the ability to specify whether or not the target was "equal to", "starts with" or "ends with" a certain criteria. This causes a problem since if you know general cursor syntax you know that a cursor is fed with a select statement as such:

declare wbs_cursor cursor for
select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
open wbs_cursor
declare @w1 varchar(30)
fetch next from wbs_cursor into @w1
while @@fetch_status = 0

Now, by giving users the ability to dynamically change the entire WHERE clause (including the operator), you suddenly run into a syntax issue because you can't just store the entire clause in a varchar variable and then have it EXEC SQL (@stmt) because of later references to the cursor. Since that eliminates one major way of preparing dynamic SQL statements, I developed a very dirty solution that revolved around IF statements -

if @targetcondition = 'starts with'
begin
      set @targetproject = @targetproject + '%'
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
end
if @targetcondition = 'is equal to'
begin
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 = @targetproject
end
if @targetcondition = 'ends with'
begin
      set @targetproject = '%' + @targetproject
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR where wbs1 LIKE @targetproject
end

Pretty dirty right?

Most of the time when I post code here, it's generally the non-dirty version, but I really couldn't find a non-dirty version to this problem and would really like to see if someoone has any ideas on a better, cleaner solution. It seems that all of the sample code I saw online when searching "dynamic cursors" only made table names or WHERE criteria (not the operator) dynamic and therefore fails to appropriately address my issue. I got away with this solution since the problem was simplistic, but what about if I had to deal with 1000 conditions - I wouldn't want to write 1000 IF statements.

Thoughts?

P.S. - As I read through the SQL Books Online, it seems that the PREPARE statement might be able to solve this problem, but I don't have much experience with PREPARE.

VB.Net 2.0, SQL Server 2000 & Windows Application SQL Error

Fri, 2007-05-11 15:14 — hagrin

Today, I started working on an application to perform bulk changes to our Deltek Vision database to change tasks, sub-tasks, budgets and billing terms. So, as I go to write the SQL connection string, something I have done 100s of times, I kept getting the following error from my app:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

While the error is pretty descriptive, there is one slight problem - I'm not trying to access a 2005 SQL Server, but a SQL Server 2000 box. So, I do what any good programmer does - he searches the major search engines for a solution, but most of the examples are of users trying to 1) build a Web application and have a web.config error or 2) are actually trying to access a SQL Server 2005 database. Yikes, now what?

I can't stress this enough - check your connection string. I was actually passing a blank Data Source (I was using SelectedValue as opposed to SelectedItem) and since I have SQLExpress running on my development machine, I assume that's why I was generating a 2005 error when trying, in theory, to connect to a SQL 2000 machine. The lesson is - verify your connection string when getting this error and trying to access a SQL 2000 machine. If you're trying to access a SQL 2005 machine, follow the enabling remote connections info that you'll find on the hundreds of pages that come up in he search engines for this error.

SharePoint v3: tp_DeleteTransactionID

Sun, 2007-02-25 13:09 — hagrin

When working with SharePoint v3 list data, it's important to understand the data structure used to store list items. List data is stored in the AllUserData table and the name should be your first indication that you might run into a problem if trying to pull or manipulate data stored in this table. Why? Well, "all" user data is stored in that table including deleted data and you will need to know how to filter out deleted items and that is where the tp_DeleteTransactionID column comes into focus.

The tp_DeleteTransactionID column is a varbinary field so you cannot readily read the contents of that column in SQL Server Enterprise Manager. This coulmn acts as a "deleted flag" and you will need to be able to know what items you have removed from your list, but you forgot to remove from your Recycle Bin. List items that you delete from your list, but that remain in your Recycle Bin will have the value of the tp_DeleteTransactionID column updated from the default value of 0x. Once you remove the items from your Recycle Bin, the item's corresponding rows will be deleted from the AllUserData, but until you do so those item's rows will remain in the AllUserData table.

So, if you want to filter out deleted content for the purpose of data manipulation or transformation, but you don't want to have to worry that the user has remembered to empty the Recycle Bin, add the following WHERE clause to your SQL statements -

WHERE tp_DeleteTransactionID = 0x

Notice, that the value 0x is not surrounded by value defining apostrophes since the value is not a true string, numeric, date, etc. value, but a hex representation for the varbinary field.

MySQL Replication

Mon, 2007-02-12 22:08 — hagrin

Hynek wrote a great article on MySQL replication and exactly how easy replication is to setup. I definitely enjoyed this article as I was only familiar with replication in MS SQL Server 2000 and 2005 and this article solves one more problem for database admins. Now, with stored procedures and replication introduced to MySQL, MySQL provides another database option for the corporate environment and for IT managers to avoid high priced licensing agreements.

Database Love Today

Fri, 2006-08-04 23:00 — hagrin

Today was a slower day than most due to the fact my work VPN seems to be down. Currently, I am in MS SQL 2000 Admin training and it's going extremely well so far. I've definitely learned a few tricks to bring back to work with me, but not enough to take the cert test I don't think. To accomplish that, looks like I will be reading the text on my own.

In other database news, MSFT has released a Community Technology Preview of SQL Server 2005. They released 32 and 64-bit versions which are encompassed over two CDs - one for the actual server and one for the tools. In addition, you can download a MSI for the September 2005 release of the SQL Books Online (easily MSFT's best help) for this specific SQL 2005 release. I'm looking forward to trying it out and providing some feedback here.

Syndicate content