Best Way for A Non-Tech to Build/Run A Query

[CTekGirl]

I understand that this is probably not a simple task but I am open to listen and learn about any ideas that you may have.

I have 4 queries that I have combined to run as one query. I can run the SQL code through query analyzer and get all the results for each one of the queries after the period end date is chosen; in a single report(result set).

Instead of the user going through Query Analyzer I would like to create a standalone database login window that runs this script. I would like to make this for a user that is more comfortable in the point and click world.

Is there a code I need to learn or are there some development tools available to build this type of GUI?
-thanks in advance

 

[cjlarue]

Depending on the DB you are using. If MS SQL 2000 you might want to take a look at SQL Server reporting services

http://www.microsoft.com/sql/reporting/default.asp

. That will deliver the information to your end users after they enter the parameter.

 

[CTekGirl]

I went to the link you mentioned on the website. I attempted to install the 120 Trial Version of this product, but it does not complete the installation process. It gets 1/2 through an errors out after it installs .NET it does not provide any other information besides the word Error! I tried downloading it again with the same results.

In general, the user needs to change the period end date monthly, without editing it directly in SQL code, if possible. I understand your point about a stored procedure, I am looking into adding that to the code, but I wondered if you had any other ideas.

 

[ArtieChoke]

Depending on your expertise, you can:
1. Write a VB program using ADO to take the input and run the SP.
2. Write a web page with ASP and run the SP (you might run into additional security issues)
3. Write a VB script routine - that might be the easiest way to do it. Here's an excerpt from one I've used - it runs a job, but all you would have to do is add an input routine, change the command type, put the name of the SP in the CommandText field and modify the CreateParameter to use the proper values. You can find the info in the ADO help.

    strConnect = "Driver={SQL Server};SERVER=" + serverName + ";DATABASE=databaseName;"
    set oConn = CreateObject("ADODB.Connection")

    on error resume next
    oConn.Open strConnect
    if oconn.state <> 1 then
        msgbox "Could not connect to database"
        exit sub
    end if

    on error goto 0
    set dcmd = CreateObject("ADODB.Command")
    dcmd.ActiveConnection = oConn
    dcmd.CommandType = 4
    dcmd.CommandText = "sp_start_job"
    set dparam = dcmd.CreateParameter("task",129,,len(taskName),taskName)
    dcmd.Parameters.Append dparam
    dcmd.execute

"I think we're all Bozos on this bus!" - Firesign Theatre