Excel Macro: Returning Results for SP

Question

Post reply

Excel Macro: Returning Results for SP

Topher

SSCommitted

Points: 1763
More actions
December 19, 2007 at 12:30 am

#185168

I'm looking to create a macro that will return the results for running a stored procedure...
My overall goal would be to create a form that will be able to create multiple users that are listed on what sheet and export the resutls to another sheet.
Would anyone be able to show how to return results from SPs? Say something simple like SP_Help. I believe I know how to actually run the SP, but can not return results. I did so but runnign such as rs.open "sp_help".
Also, to go into more detail along w/ other things. I need to pull info from hundreds of severs. I have an osql statement but I would like to clean that up in a macro or vbscript w/ output. Example for this would be to grab all IDs on all servers.
Thanks in advanced!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

hb21l6 SSCommitted Points: 1647 More actions · Answer 1

this is the one i use..

Sub thisname()

Dim strDate As String

Dim strSite As String

Dim strOrder As String

Dim strRows As String

strDate = Sheets("Control").Range("D3").Value

strSite = Sheets("Control").Range("D5").Value

strOrder = Sheets("Control").Range("C11").Value

Sheets("RollCall").QueryTables("RollCall").Connection = "ODBC;DRIVER=SQL Server;SERVER=172.20.1.1;UID=Username;PWD=Password;DATABASE=DatabaseName"

Sheets("RollCall").QueryTables("RollCall").Sql = "Exec RPT_sp_RollCallHours4 '" + strDate + "', " + strSite + ", " + strOrder

Sheets("RollCall").QueryTables("RollCall").Refresh

Sheets("SectorCount").PivotTables("RollCallTable").RefreshTable

Sheets("SectorHour").PivotTables("WorkHours").RefreshTable

Sheets("SectorPaid").PivotTables("PayEstimate").RefreshTable

Sheets("DeptHour").PivotTables("WorkHours").RefreshTable

Sheets("DeptPaid").PivotTables("PayEstimate").RefreshTable

strRows = Sheets("RollCall").Range("AG1").Value + 5

Sheets("RollCall").Select

ActiveSheet.PageSetup.PrintArea = "$B$5:$T$" + strRows

Range("A1").Select

End Sub

Hope this helps..

Dave

Topher SSCommitted Points: 1763 More actions · Answer 2

I did it slightly different (and maybe more brutal!!). But, I did get it to work the other day, but, how have two very small issues.

My display portion of the command grabs info from the table it displays (say SP_HELP). But, when I ran SP_addsrvrole member, it will not display results as that statement is not w/in a table, simple just a text stating of the command run successfully or not.

Also, issue two is simple the headers of the columns. I'm sure that will not take long to resolve, just haven't had time to work on it as much as I'd liked to!!

Here's my (possible) ugly code for all that care 🙂

'created by cb68818

Dim objrs, objconn

Sub test()

'On Error Resume Next

Const adOpenStatic = 3

Const adUseClient = 3

Dim ifso, ofso, tf, countfields, fname

Set ofso = CreateObject("Scripting.FileSystemObject")

Set ifso = CreateObject("Scripting.FileSystemObject")

Set tf = ofso.CreateTextFile("c:\scripts\testfile.csv", True)

Set ifile = ifso.OpenTextFile("c:\scripts\inputfile.txt")

Set isqlfile = ifso.OpenTextFile("c:\scripts\sql.txt")

'SP=InputBox("Enter sp:")

On Error Resume Next

Do Until ifile.AtEndOfLine

sServer = ifile.readline

Set isqlfile = ifso.OpenTextFile("c:\scripts\sql.txt")

Set objconn = CreateObject("ADODB.Connection")

Set objrs = CreateObject("ADODB.Recordset")

objconn.CommandTimeout = 300

strConn = "Provider=SQLOLEDB.1;Trusted_Connection=yes;Initial Catalog=master;Data Source=" + sServer + ";"

objconn.Open strConn

objrs.CursorLocation = adUseClient

objrs.ActiveConnection = objconn

objrs.CursorType = adOpenStatic

'sp = "sp_help"

'objRS.Source

Do Until isqlfile.AtEndOfLine

sSql = isqlfile.readline

objrs.Open sSql

tf.writeline sServer

'countfields = objRS.Fields.count

If objrs.RecordCount > 0 Then

For iRow = objrs.AbsolutePosition To objrs.RecordCount

resultz2 = objrs.fields.Count

For i = 0 To objrs.fields.Count - 1

'if objrs.fields(i).type <> 204 then

'resultz = objs.fields(i).Value

tf.write objrs.fields(i).Value

tf.write ","

Next

tf.writeline

objrs.MoveNext

Next

tf.writeline

objrs.Close

'Set isqlfile = Nothing

End If

Loop

'MsgBox("Stored Proc is done " & fname)

'objrs.close

objconn.Close

Set objShell = CreateObject("Wscript.Shell")

objShell.Run ("c:\scripts\testfile.csv")

End Sub