The current fee schedule is an ASP.NET Web application with a SQL Server back end.
At the end of the hackathon, my team had put together a JavaScript-based site that used JSON to read in the data for the new site.
Since the current infrastructure was based on SQL Server, I sought a way to convert the two SQL tables into JSON Objects so the current back end would not have to be changed. As it turns out, I found a good starting point on Stack Overflow. The only problem with this stored procedure was that it would not work properly if there were single quotes in the results, or if the column names had spaces.
I was able to modify the stored proc to allow for these changes as well as a few others, such as accounting for NULL values. The modified SP is posted below for your reading pleasure!
In migrating data from a legacy budget application to its replacement, my requirement was to take the first and last years in a budget from the old application and update the corresponding table in the “new” app table with those dates as the beginning and ending of the project. One possible solution is to use a SQL cursor to loop through each record. When there are relatively few records, and the databases/tables are in the same location, this is an easy choice as the performance hit will be minimal.
However, in my case, there were 10000 records and the servers were on separate LANs. The cursor below did produce the desired result in terms of data, but it was horribly slow. It would have taken hours to completely copy all of the data, given the locations of the servers and possibly not the best indexing on the databases. (I didn’t design the indices! 😀 )
DECLARE WidgetBudgetCursor CURSOR
FOR SELECT [PSProjectID],[WidgetID] FROM [LinkedServername].[DbName].[Owner].[TableName]
OPEN WidgetBudgetCursor
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
WHILE @@FETCH_STATUS = 0
BEGIN
-- ...
DECLARE @BudgetYearMin INT
DECLARE @BudgetYearMax INT
SELECT @BudgetYearMin=MIN([BudgetYear]), @BudgetYearMax=MAX([BudgetYear]) FROM [tWidgetbudgetbreakdown]
WHERE [RequestId] = @WidgetID
UPDATE [LinkedServername].[DbName].[Owner].[TableName]
SET [ProjectStartDate] = CONVERT(DATETIME, '01-01-' + CAST(@BudgetYearMin AS CHAR(4)) + ' 00:00:00')
WHERE [ProjectID] = @ProjectID
UPDATE [LinkedServername].[DbName].[Owner].[TableName]
SET [InServiceDate] = CONVERT(DATETIME, '12-31-' + CAST(@BudgetYearMax AS CHAR(4)) + ' 23:59:59')
WHERE [ProjectID] = @ProjectID
-- ...
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
END
CLOSE WidgetBudgetCursor
DEALLOCATE WidgetBudgetCursor
After some initial attempts to copy small portions of the data took much longer than I expected it to, I redesigned the queries to be updates on joined tables (see below). This was much faster than using the cursor. While I was not able to eliminate the cursor for some other required functions, moving these update queries out of the cursor made the data move use less resources and take much less time.
UPDATE pm2
SET [ProjectStartDate] =
a.MinBudgetYear FROM
(SELECT CONVERT(DATETIME, '01-01-' + CAST(MIN(Widget.[BudgetYear]) AS CHAR(4)) + ' 00:00:00') AS MinBudgetYear, pm.[WidgetID] AS WidgetID
FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MIN(Widget.[BudgetYear]))=4) a INNER JOIN
[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]
UPDATE pm2
SET [InServiceDate] =
a.MaxBudgetYear FROM
(SELECT CONVERT(DATETIME, '12-31-' + CAST(MAX(Widget.[BudgetYear]) AS CHAR(4)) + ' 23:59:59') AS MaxBudgetYear, pm.[WidgetID] AS WidgetID
FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MAX(Widget.[BudgetYear]))=4) a INNER JOIN
[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]
One of my colleagues sent me a very handy bit of code this morning that I thought would be helpful to post.
Below are two options which, when run against the database, should return the names of all stored procedures that reference the named table.
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
I am told that the functionality of the first option has been verified.
