Apr 25

How to Get Rid of Cursors for Inserting Bulk Data

This is pseudo code, but you should be able to get the idea from here…

Will develop more as requested…

DECLARE @t TABLE (Columns Def, repeat ...)

insert into @t
  select from flat_file

insert into parent
  select parent, fields from @t

insert into child
  select p.parentid, c.child, c.fields
  from	@t c
    inner join parent p
      on c.parentkey = p.parentkey
Apr 15

Why I love .NET – Reason 314 – Config Files

While working the day job, I am porting over some sites from one virtual site to another.

Our clients will typically have their own external domain, which will get http redirected to a client specific sub-domain. Unfortunately, each external website has pretty much the exact same redirect page, under construction page, landing page, etc. The only difference is the redirect URL. Which, luckily in our case, can be found in the database (though, not really used).

During a deployment, we must manually turn each of the default pages to use the Under Construction page in IIS. This will prevent the redirect from occurring. However, you can see how the success of our company could lead to 4 hour long deployments, just in switching the default page.

“Why not script it?” you might be asking… That’s really the equivalent of saying “buy a nail gun if you’re nailing things” and not asking the question “is this implementation theĀ right implementation?” In this case, it’s not.

I wrote an app that will determine the exact same thing, and do it in a fraction of the footprint, and will let us grow with little production deployment pains.

Unfortunately, we will still need to implement the updates in the production environment to make this a reality. No small task if you’re looking at doing this through the IIS manager.

But, let’s not forget that this is a server application, and therefore, should have itself a command line interface. IT DOES! It’s called appcmd, and is found in theĀ %windows%\System32\inetsrv directory. But after doing some research, it looks like it may have some limitations when retrieving the data.

Fortunately, there is another way, and it’s found in the %windows%\System32\inetsrv\config\applicationHost.config file. I will be playing with this more in the next few hours, and so I hope to report some success.

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”]