December 24, 2007 at 7:49 am
I have a script (below) that will run a script from an input file on severs located w/in another input file. When I run a SP, I'm having issue w/ the output. When I run a SP that has a table based output, it works correctly(ex. sp_help). But, if I run an SP that has only a update statement(ex. 'command ran successful'(sp_adduser)), it does not display any results.
Any comments, suggestions, input, on how to resolve this....
Thanks!!!!!!!!!!!
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
Loop
'MsgBox("Stored Proc is done " & fname)
'objrs.close
objconn.Close
Set objShell = CreateObject("Wscript.Shell")
objShell.Run ("c:\scripts\testfile.csv")
End Sub
December 24, 2007 at 9:54 am
Nice code
I think the messages like you describe show up in the errors collection as opposed to the resultset. . Errors can be generated by a method call or property of the Connection, Command, or Recordset object but are always retrieved from the Connection object. By using the Error object, you can retrieve the error description and source of the error. The SQLSTATE and database-native error information is also available from the Error object when working with the ODBC Provider.
untested sample:
Dim Errs As ADODB.Errors
Set Errs = objconn.Errors
For Each errLoop In Errs
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
Francis
December 24, 2007 at 9:59 am
If you need a result, send it back from the SP. A proc with an update statement doesn't generate a result set.
you can force one
create proc myproc
as
begin
update xx set yyy = zzz
if @@error = 0 then
select 0 'returnval'
else
select 1 'returnval'
return
This will give me a one column, one row table returned.
December 24, 2007 at 1:28 pm
Steve,
I'm kind of confused on how I would go about doing so...? I apologize for my confusion!!!!
I appreciate the help!!!!!!!
December 28, 2007 at 2:49 pm
In the example above, if there was an error, a one row, one column result set would be returned. The column would be named "returnval" and would have a 1. If no error, then a 0 is returned.
That's a table.
What are you trying to return from this script?
December 28, 2007 at 2:55 pm
Right. And this method would be created a new SP which is a DBA function which I would not have the ability to do.
December 28, 2007 at 3:34 pm
If you're trying to check for success, then you can execute the batch as
sp_adduser 'aaa' ; select @@error 'error'
and that would return the error level. 0 would indicate no error.
December 28, 2007 at 3:47 pm
No go. When I simple run this exec sp_grantdbaccess 'cbtestdbo';select @@error 'error', no results are displayed.
I can see it try to display somethign when I test this in query, but then the error message pops in quickly after.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply