Wednesday, December 22, 2010

Access Form Data Updates

When using a form in Microsoft Access, you can typically have a gateway directly to your database. Once you make an update, it is updated in the database, sometimes without having to press a save button or the like. So what happens if you make a mistake and add, delete or mangle what you have in a field? You might go to the next database record and not know what you did. Worse, it can create a problem for your users or others who rely on your data.


The best solution is to add a confirmation yes or no type box that presents itself to you. Below is a good example of code that will help keep you save and ask you before the changes are committed. Add this code by right clicking in the field in design mode, then clicking over to event and choose "Before Update."



If MsgBox("You have made changes to this record." _
& vbCrLf & vbCrLf & "Do you want to save the changes?" _
, vbYesNo, "Changes Made") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If



Now you are set, but at a client site recently, they asked that the message box only pop up if the field was not null before. I agreed because having to confirm on every field could slow you down. Here is the code if you only want to display if you updated a non-null or even an empty field. Otherwise, it will come up every time. Add the code here, based on the previous example:



If IsNull([Data].OldValue) Then
DoCmd.Save
Else
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If
End If

Friday, October 29, 2010

Date and Time Conversions from int using dbo.sysjobhistory

I was charged with developing a T-SQL query to list backup job histories, found in dbo.sysjobs and dbo.sysjobhistory. One of the requirements was to have the end date of the job. In the dbo.sysjobhistories table, there is an integer start date (run_date), time (run_time) and duration (run_duration) field, but no end date or end time data. I came across some code to create those end date and end time fields, plus convert them and the others into a usable datetime field.

Anyway, if you need a way to track backup job histories or even make it into a view, here is some helpful code:

SELECT
--Server
CONVERT(char(100), SERVERPROPERTY('Servername')) AS hostname,
--Instance
@@SERVICENAME As instance,

CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS startdatetime,
--EndDate
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS enddatetime,
--Job Name
sj.name as "job name",
--Error
CASE WHEN run_status = '0' THEN message
WHEN run_status = '0' THEN ''
END AS "error message",
--Duration
LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) as duration,
--Status
CASE WHEN run_status = '1' THEN 'Success'
WHEN run_status = '0' THEN 'Failure'
END AS "status"

FROM dbo.sysjobs sj

INNER JOIN dbo.sysjobhistory sh on sh.job_id = sj.job_id

WHERE sh.step_id = '1'
--Backups Only
AND sj.name like '%Backup%'
--Order by Start Time Descending
ORDER BY CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) DESC

Tuesday, August 17, 2010

SQL Server Reporting Services (SSRS) & .NET

When using Reporting Services on the Server for the first time, you might find that reports won’t run in Visual Studio or on the web.

The error you get is: “Execution of user code in the .NET Framework is disabled. Enable clr enabled configuration option.”

By default .NET Framework is disabled in SQL2005 and SQL2008.

To enable it in 2005:
Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu.
2. Select "Surface Area Configuration for Features"
3. For the "CLR Integration" option, activate it and save.

To enable it in 2008:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Wednesday, August 4, 2010

SSIS and an Oracle OLE DB Connection

Here is an interesting situation I came across working with SSIS and Oracle.

I have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2005(x64 bit) installed on it. I accidently installed the Oracle 10.2 client twice. I promptly removed the second Oracle client and verified I could still connect to Oracle databases in SQLPlus.

I have a SQL Server Integration Services (SSIS) solution that extracts from SQL Server and sends to an Oracle database. After my client install misadventure, when checking the Oracle Provider for OLE DB connection, I noticed something was wrong.

When trying to test connection to get an error, I got:
"oraoledb.oracle.1 provider is not registered"

Yikes, this was working before. How can I fix this and save the 13 packages that will run tonight? Surely, they will all error!

When trying to run the package anyway, I would get this error:
Code: 0xC020801C Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle Provider for OLE DB" failed with error code 0xC0202009

After looking through a myriad of other posts, it appears that the Oracle OraOLEDB10.dll file became unregistered at some point.

The fix:
START/Run then,
regsvr32 C:\oracle\product\10.2.0\client_1\BIN\OraOLEDB10.dll

Yes! The connection worked in the packages and the jobs all ran to success!

Thursday, March 25, 2010

Business Objects Administration

My environment is one where users access reports on an IIS (Internet Information Services) based InfoView web application There was a Business Objects user that runs a particularly large report every day. Due to the size and activity on Business Objects lately, the report will repeatedly time out after quite a few tries.

The error says:

"An error has occurred. Request timeout".

This is basically as generic of an error text that you can get. Me and my users were not very happy with these occurrences but one day I found the answer.

When searching the actual Business Objects Support site for the exact error text, I found the attached resolution. It increases the timeout from two to 15 minutes. I tried this in Development and the user no longer got the report to error with the time out.

The details of the change were to :

Update the connection timeout from 120 to 900 seconds for that particular site in the IIS manager.









Then update the machine.config file in C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG. (or wherever or whatever version of .Net you use) as follows:

Locate the following section:

executionTimeout="90"

maxRequestLength="4096"

useFullyQualifiedRedirectUrl="false"

minFreeThreads="8"

minLocalRequestFreeThreads="4"

appRequestQueueLimit="100"

enableVersionHeader="true"

/>

Change executionTimeout="90" to executionTimeout="900".

Restart IIS.

I tested this process while logged in to InfoView in DEV. Whether you run a report or are navigating during the IIS restart, there is no difference in service. Therefore, there is no outage associated. However, I would bounce the Business Objects servers for good measure.

The back out plan is to change the IIS setting back to 120 and backup and restore the machine.config file.

Friday, March 19, 2010

First Post

Welcome to the blog for After-Hours Coders, LLC. We look forward to sharing some of the various tips and tricks we encounter in the field to benefit the greater Information Technology community. We’ll have most entries about SQL Server Database administration, Business Intelligence and other miscellaneous topics.