About rj

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.
Apr 14

New Web-based SQL Query Tool

I had an idea at work a while ago that would allow an authorized user (based on the Intranet) to dynamically execute a stored procedure in a designated schema.

The need arose when I discovered that our network/system administrator was searching for saved queries, changing some variables, connecting to the production server, and then executing them.

I thought it would be nice to put these into their own schema that a page could find these, and prepare a nice and easy way for them to be executed.

Development is still in process, and I’m hoping to have version 1 released in the next few weeks.

If you or your company would like to stay up to date on this, or if you have any questions or input, please ship me an email in the form below.

What will come in these versions:

Free Paid Ultimate
Get a list of procedures based on a schema X X X
Display meta-data about procedure X X X
Display meta-data about parameters X X X
Data input – Textbox X X X
Data input – Checkboxes X X
Data input – Radio Buttons X X
Data input – Drop Down List X X
Populate input with Data (SQL) X X
Populate input with Data (String) X X
Populate cascading input with Data (SQL) X
Data Validation – More Than/Equal X X
Data Validation – Less Than/Equal X X
Data Validation – Required X X
Data Validation – Is Date X X
Data Validation – Is Numeric X X
Data Validation – Regular Expression X X
Visual way to manage Proc X

[contact-form-7 id=”204″ title=”SQL Tool”]

Dec 06

MS SQL Server List to Table Function

I need this for work real quick, and hopefully I’ll get some time to update the content later on.

This User Defined Function will turn a comma separated list (or any single character, for that matter), and turn it into a table so you can join on it. My recommendation is to shove it into a table variable first, rather than join on the function. It may be an old wives tale, but I’d rather do it this way once, than go through all of the procedures that use it to extract it out ūüôā

CREATE FUNCTION dbo.udf_ListToTable
(
	@Input VARCHAR(8000),
	@Delimiter VARCHAR(1)
)
RETURNS @Return TABLE (ID INT IDENTITY, [Value] VARCHAR(1000))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    DECLARE @String VARCHAR(1000)

    WHILE LEN(@Input) > 0
    BEGIN
        SET @String = LEFT(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input) - 1, -1),
			LEN(@Input)))
        SET @Input = SUBSTRING(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input), 0),
			LEN(@Input)) + 1, LEN(@Input))

        INSERT INTO @Return ([Value])
			VALUES (@String)
    END

    RETURN
END

To use, do the following:

DECLARE @Fruit TABLE (ID INT, Fruit VARCHAR(50))
INSERT INTO @Fruit
	SELECT	ID, [Value]
	FROM	dbo.udf_ListToTable('Apple,Banana,Kiwi,Strawberry', ',')
Posted in SQL
Sep 17

Connection Strings

Every time I want to create a new project, I either have to look into previous projects, or go to ConnectionStrings.com. It’s a wonderful site that you can use to find the specific connection string that you may ever need.

For me, however, I typically use 1 of 2 connection strings. It either has a username/password, or it uses integrated security (currently logged in user).

SQL Server 2008 Connection String with User name and Password:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQL Server 2008 Connection String with Integrated Security:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Sep 05

Stay The Hell Out Of Other Peoples Code – Russ Olsen

I just read an interesting blog post about The Best Programming Advice To Ever Get.

It’s a great and short read about a developers ego, and how you need to let it go to make improvements. My favorite line was at the end, many years after realizing that the advice given was dumb; it kept current systems slow because developers didn’t want to cross the barrier of someone else’s code. “But the best way to have a future is to be part of a team that values progress over politics, ideas over territory, and initiative over decorum.”

I urge you to take a quick read, and remember to check your ego at the door. It could be your once brilliant concept that is slowing the progress of the project. Suck it up, and learn from it.

Sep 02

Windows 7 Alt-Tab

I’ve been using Microsoft Windows since 3.11.

I didn’t get thoroughly familiar with keyboard shortcuts until Windows 98 and ME. My two favorites were Alt-Tab (switch) and Alt-F4 (close).

After getting a new laptop for my birthday, I shortly discovered that the Alt-Tab was a huge pain in my ass. And it was one I wasn’t willing to just get used to; I had no idea where I was at in any of the applications that would just flip in front of me.

A quick google search resulted in me finding my answer.

Control Panel
System
Advanced System Settings
Advanced (Tab) -> Performance (Section) -> Settings (Button)
Visual Effects (Tab)
Uncheck “Enable Aero Peek”
Apply/OK all the way out.

Problem solved, and blood pressure can resume normal 120/80.

Enjoy.

source

Aug 29

MS SQL Server Convert DateTime to String

I regularly use a SQL statement to extract a datetime, as I feel like it formats it faster than code. Plus, if you update the source, you only have to do it once, as opposed to doing it in every place that it gets displayed.

Here is the conversion number to use in the CONVERT statement.

CONVERT(VARCHAR(30), [DateValue], [Style])
Style Output
0 Sep 11 2001  8:52AM
1 09/11/01
2 01.09.11
3 11/09/01
4 11.09.01
5 11-09-01
6 11 Sep 01
7 Sep 11, 01
8 08:52:21
9 Sep 11 2001  8:52:21:000AM
10 09-11-01
11 01/09/11
12 010911
13 11 Sep 2001 08:52:21:000
14 08:52:21:000
20 2001-09-11 08:52:21
21 2001-09-11 08:52:21.000
22 09/11/01  8:52:21 AM
23 2001-09-11
24 08:52:21
25 2001-09-11 08:52:21.000
100 Sep 11 2001  8:52AM
101 09/11/2001
102 2001.09.11
103 11/09/2001
104 11.09.2001
105 11-09-2001
106 11 Sep 2001
107 Sep 11, 2001
108 08:52:21
109 Sep 11 2001  8:52:21:000AM
110 09-11-2001
111 2001/09/11
112 20010911
113 11 Sep 2001 08:52:21:000
114 08:52:21:000
120 2001-09-11 08:52:21
121 2001-09-11 08:52:21.000
126 2001-09-11T08:52:21
127 2001-09-11T08:52:21
130 23 ????? ??????? 1422  8:52:21
131 23/06/1422  8:52:21:000AM

Also, if you happened to notice the date: Yes, it is the time that the first plane hit the first world trade center… Never Forget.

I’m still coding, so we are winning!

You can also view more details on the MSDN site here.

Posted in SQL
Jun 22

Fun Code Friday – SQL Binary Generator

I was daydreaming about some SQL code the other day, and wrote a note to myself to investigate.

I’ve always been interested in writing loops to output sequential data. I don’t know why, I just do.

So, here is some SQL that will generate some binary.

DECLARE @b TABLE (c VARCHAR(1))
INSERT INTO @b VALUES (0)
INSERT INTO @b VALUES (1)

SELECT	b1.c + b2.c + b3.c + b4.c + b5.c + b6.c + b7.c + b8.c
FROM	@b b1, @b b2, @b b3, @b b4, @b b5, @b b6, @b b7, @b b8
ORDER BY 1
Jun 20

Invalid Verizon Wireless Bill – Phishing Scam

Hello,

You are no doubtedly here because you clicked a link in an email in regards to something about your verizon bill.

This was a phishing attempt, and was thwarted (hopefully) before too many people got caught.

At about 12:45pm EST, I received a phone call from the Titan America Help Desk, informing me of the offense. Unfortunately, that only gave me 15 minutes to find the issue and thwart it, as I had an important client meeting to get to.

I was not able to find the problem within this time frame, as it wasn’t visible in the WordPress utility. During the meeting, however, I thought of another way this could be occurring. Once the meeting was over, I verified that it was indeed the cause, and removed access to it.

Somehow, and I’ll be investigating how for a little bit, 2 files were created on my server in the root directory. Both of them were identical. The root file was “wless.html” (how you got here), and the other was “zion.html”. The zion file had zero access permissions, which leads me to believe it’s the master copy.

The HTML file contained a basic (ugly) layout, but quite an advanced javascript algorithm that loaded an iframe element into the code. This, no doubt, would have made it appear that I was behind the phishing attack.

Let me assure you that I was not.

I have reported back to the representative who contacted me about the issue, and hopefully he was able to stop it at the root.

You’re safe for the moment, but please use caution when dealing with sensitive information.

You can read about how to detect and prevent phishing scams in the future.

So, take a deep breath and hold it… hold it… keep holding it… and exhale. You can relax.

Stay cool in this warm weather. And if you’re in need of some cool shades, check out these really cool and popular Wayfarer Sunglasses by Ray Ban.

Ray Ban Wayfarer Sunglasses