Mark Mintoff My superpower is common sense

5Nov/130

Microsoft Excel Mod Problem

There exists a problem in Microsoft Excel's Mod function, whereby performing modulo on decimal numbers results in incorrect remainders.

For instance, MOD(0.17, 0.17) results in 0 (as expected), whereas MOD(0.51, 0.17) results in 0.17 (incorrect).

In fact, a modulo on the following multiples of 0.17 has resulted in many erroneous reports of remainders:

modulo1

The reasoning behind this is that internally a float data type is used which results in an incorrect modulo to be calculated. In fact, if you try this out in C# using float or double, you will get the exact same results.

The solution, I have found is to use the formula for modulo:
    x - (y * INT(x/y))

To this end, I have created a simple function through VBA which achieves a correct result by enforcing the usage of the decimal data type.

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
2May/120

Adding Regular Expression Support to SQL Server

First and foremost, it is worth noting that SQL Server supports .Net assemblies, allowing us to create methods which can be used from SQL.

.Net assemblies (dll files) can be loaded into the below folder.

With that out of the way, we can get started on creating a .Net assembly. SQL Server does not support regular expressions natively, so I thought it would be useful to add this functionality as my first assembly.

To start, we can create a Class Library Solution (I called mine 'TextFunctions') and add a single class (I called mine 'StringUtility'). The following is the code I wrote for my StringUtility class:

Very simple bit of code, which allows me to check for Regex matches. No big deal. Now we must install this assembly into the database. First we must check to ensure that the database is CLR enabled, so we run:

Next we will create the assembly. Please note that the dll file must be on the same machine as the database is. You might also want to specify the database you will be adding this to. I added a simple "use master;" to my script.

Now we can install the RGX function I created.

Note that the syntax for EXTERNAL NAME is SolutionName.[Namespace.ClassName].MethodName

That is all! You now have a brand new function which executes C# code and returns a boolean value back! Let us try some simple examples:

This function can also be used in WHERE clause conditions as follows:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.3/5 (3 votes cast)
27Apr/120

Useless SSIS Error Message

Had to investigate an SSIS error code to figure out what was causing it. The error code was -1073450910. I pasted that code into Windows Calculator and converted to hex.

You have to trim the Fs from it, getting me C0047062. I then navigated to C:Program FilesMicrosoft SQL Server100SDKInclude and opened dtsmsg.h to search for the error. What followed was an error which explained absolutely nothing.

Insert sad face here

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
27Apr/122

Simple Way To Check For Rows

I would like to share a very simple technique for checking for rows in your table. Before developing this, I used to make the mistake of doing a SELECT COUNT(1) script.

On tables with a large number of rows (millions), this proved to be inefficient (even with indexing). So instead, I developed this efficient way to check for rows, which makes a difference:

It's very simple. All it does is select the top one row from your table, based on your condition in a subquery (X). Then select count on that subquery and convert the result to a bit. The result will always be 1 or 0, which translates to True and False respectively!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
27Mar/120

A string literal in the expression exceeds the maximum length of 4000 characters.

In SSIS, the error "A string literal in the expression exceeds the maximum length of 4000 characters." has proved to be a stumbling block for me in the past because the error message is misleading. In SSIS, when a string variable is set to Evaluate As Expression, then the string being put together by that expression is not actually a string, but rather the SSIS specific DT_WSTR.

DT_WSTR has a limit of 4000 characters whereas string does not, in spite of what the error message appears to suggest.

I personally ran into this error on attempting to build a large query through Expression, but there is an alternative solution to using EvaluateAsExpression.

If instead, we create a Script Task, providing it with all the necessary variables; we could construct a script which exceeds 4000 characters and output it to the string variable. Contrary to expectations, the string is accepted in it's entirety and it can be used normally.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
1Mar/120

SSRS Report Parameters via Query String URL

In order to pass report parameters via querystring URL in SSRS, use the following syntax:

http://[servername]/Reportserver/Pages/ReportViewer.aspx?[folder]/[reportname]&rs:Command=Render&[parameter]=[value]

Please note, this is different from the usual syntax when viewing a report:

http://[servername]/Reports/Pages/Report.aspx?ItemPath=%2f[folder]%2f[reportname]

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
27Sep/110

SSIS Deployment

Failed to decrypt protected XML node "DTS:Password" with error...This issue occurs mainly due to security related issues. Follow the steps below in order to rectify the issue:

  • In Visual Studio, go to the Control Flow Tab
  • Right-click on an empty area inside the window, select properties.
  • Set Protection Level to EncryptSensitiveWithPassword
  • Set PackagePassword to "myPassword"
  • From the Connection Managers:
    • Double click connection
    • Click "All"
    • Under Security Area:
    • Set Password to your connection's password
    • Set Persist Security Info to True
  • Save the package
  • Connect to SQL Integration Services in SQL Manager
  • Under Stored Packages:
    • Right-Click MSDB
    • Select Import Package
    • Set Package Location to File System
    • Set Package Path to the location of your dtsx file
    • Leave the rest as defaults
    • Click OK
    • Enter "myPassword" into the Package Password dialog
    • You have now successfully imported the package.
  • In order to create an SQL Job:
    • In the job step:
    • Set the Type as SQL Server Integration Services Package
    • Set the Package Source as SSIS Package Store
    • Set the Server as the database server where you stored your package in the steps above
    • Click the button for the package and choose your package.
    • Click OK
    • Enter "myPassword" into the Package Password dialog
Success. You may now schedule and execute the job.
VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
16Aug/111

Monitor Running Processes using T-SQL

At times, monitoring processes which are running on your database is necessary; particularly if you suspect that a particular script is taking too long, or clogging everything up. You might have to kill one or two processes in order to ensure that everything keeps running smoothly. In order to monitor my processes and ensure that everything is well, I put together the following script:

This produces an interesting little 'dashboard', allowing you to identify problematic scripts. Hope this serves you well.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
12Aug/110

Drop Procedures using T-SQL

Quite a specific thing to do, but here's how you can drop all your stored procedures in one go using T-SQL. Simply put, all you need to do is run a cursor on a select from the information schema and create an EXEC script.

For this example, I left everything as a PRINT statement in order to minimize the "Oops I destroyed the database" en-masse factor. Leave the PRINT if you want to see what it puts together. Uncomment the EXEC to run it. If you went ahead and ran the script above with EXEC and feel you ruined your life, you might as well drop all your tables. Here, I'll save you some time:

Happy coding and I hope you don't ruin a database.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
6Jun/110

Getting Column Information using T-SQL

Thought I would share a simple T-SQL query in order to get column information about a table using T-SQL. I recently needed a "dump" of specific tables in order to copy paste the column name and data type into a small application I built to auto-generate my stored procedures. This simple script will query the information schema giving you the details:

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tagged as: , , , No Comments