December 28, 2007 at 2:36 pm
I posted this previously w/ my code displayed in a VB statement so I did not get the answer that would assist me. So, I'm posting again w/ my code from the actually VBS file.
The issue is, I cannot recieve output from a stored procedure that simple generates a status update; it only displays output that is displayed in a table results.
Meaning, if I run sp_addrolemember from this, it will not display any results as this output would not be in a table form. I'm looking how to display this output as well as table resuults (ex sp_help).
I have my purpose half completed. When I need to pull results from 200 servers, I can simple list the servers in one text file, the sql statement in the other, and it will list my results..
Now, if I need to create the exact same account on 200 servers, I want to be able to run the sp to do so. But, I need the results from running this to ensure the account was created.
Thanks!!!!!
Dim objrs, objconn
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")
January 1, 2008 at 9:02 pm
There is no way to do this directly. It doesn't return anything.
Run a query to look for the user after it's created. If it's there, then it worked.
January 2, 2008 at 2:32 am
If you simply want to add a role to 200 (or so) servers using sp_addRoleMember, then you'll find that SQLCMD will do this very easily for you. It will also do it for SQL 2000. SQLCMD is great for doing any sort of routine work with a number of different servers. Let me know if you want the details of how to do it, but I think you'll find it obvious from the documentation. As far as I remember, it will also allow you to capture the string returned from sp_addRoleMember to a file.
SQLCMD is a highly neglected utility that should be the first port of call for any routine admin scripting.
When SQLCMD gets a bit strained, then I'd use SMO/DMO to do the job you want.
Best wishes,
Phil Factor
January 3, 2008 at 3:19 pm
I would make them as linked servers, dump the output ( of sp_help)into temp table. Run everything in the loop, move the results of each iteration from temp table on remote server into perm table on local server...
January 3, 2008 at 3:22 pm
I'm not concerned about the output of a stored procedure that provides results. I need the output of ones that do not.
Example would be, I need to create a user on 100 servers. I need to be able to view the message it returns.
For example, if you run sp_adduser in Query Analyzer, you will recieve completed successsful, a syntax error, or user already exist to name a few. These are the error I need to be able to view.
January 3, 2008 at 4:06 pm
Well,
So you could get whatever output you want from the perm table on you local server...
January 3, 2008 at 6:54 pm
..would you happen to have an example or able to explain. The script above I wrote fairly quickly and haven't had too much time to play with it. I just added a statement that list the column headings in regards to the output it sends back from running a SP. Other than that, the script is the same.
January 4, 2008 at 5:15 pm
I dont have scripts handy, it was over a year ago on a different job,
but can't you do smth like that: ( quick and dirty)
you go thru your list of linked servers using while loop. ( You could create a table on local server with Linked Servers Names and other info)
On each server
begin
create table #temp (....)
set @sql='insert #temp exec master.dbo.sp_xxxxxx'
print @sql
exec (@sql)
Begin
another loop against #temp to find what you are looking for
Insert tbl_YourPermanentTable_onyourLocaLServer (.........................)
Select ..............
from #temp
WHERE ...........
loop
End
drop table #temp
loop
End
January 5, 2008 at 2:42 am
I've just done it in SQLCMD-mode of SSMS, and it works a treat. Normally, doing a script against a number of servers, getting the non-result stuff in SQLCMD is a nuisance, but here it is exactly what you want, according to the original posting.
Best wishes,
Phil Factor
January 5, 2008 at 9:07 am
Phil,
Nothing attached and no code visible... dunno if that was your intent, or not...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2008 at 10:55 am
Jeff,
It. requires three files and thought it was a bit too long to write up for a Forum entry. The reason I did it was that, by coincidence, Robyn and I are doing a SQLCMD workbench on Simple-Talk, and so we thought we'd add the task of adding a windows login as a user to all (specified) databases on a whole lot of servers, and then assigning them to a database role, as an example. If it doesn't get into the final Workbench I'll post it here. If it goes in, I'll add a link.
I'm not sure that the example really solved Topher's problems, as sp_AddRoleMember doesn't actually return anything except a zero return code if it succeeds. (nor should it, dammit)
What I do is this....
[font="Courier New"]
DECLARE @ret INT
PRINT 'added windows login $(login) to '+ DB_NAME()+' on '+@@Servername + ' as "$(membername)"'
EXEC @ret=sp_grantdbaccess '$(login)', '$(membername)'
IF @ret=0 PRINT 'successfully' ELSE PRINT 'with errors!'
PRINT 'added $(membername) to $(rolename) on '+ DB_NAME()+' on '+@@Servername
EXEC @ret=sp_addrolemember '$(rolename)', '$(membername)'
IF @ret=0 PRINT 'successfully' ELSE PRINT 'with errors!'[/font]
...which returns a nice little report. If you get an error, then you collect stuff by the bucketload
[font="Courier New"]
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'SimpleTalk\KilgoreTrout' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'Kilgore' does not exist in this database.[/font]
As you know, you can pitch all your errors into a different output file to the print statements and results so checking for errors should be dead easy.
Best wishes,
Phil Factor
January 5, 2008 at 2:50 pm
Thanks Phil...
Folks, I don't understand why anyone needs VBS for this... why not just do it directly in T-SQL? Heh... everyone gripes about me coming down hard on cursors, but this is one of the few places I can think of where one (or a loop) should actually be used... shoot, what about sp_MSForEachDataBase?
All Topher wants to do is run some form of T-SQL on 200 databases and get an output from what was done so he can eye-ball it to make sure everything worked as expected.
C'mon all you procedural guru's and cursor/loop users!!! Help Topher out!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2008 at 4:09 pm
No Jeff. You don't need a cursor for this if you have varchar(MAX). Here is some code that puts all the database users for all the databases in a server into a table. Dear me no sp_MSForEachDataBase either. (I put it in a tab-delimited form at the end so as to save the results for all the servers so that you can then maintain a complete database of all the users, for all the databases, for all the servers you have).
[font="Courier New"]SET NOCOUNT ON
CREATE TABLE #temp
(
SERVER_name SYSNAME NULL,
Database_name SYSNAME NULL,
UserName SYSNAME,
GroupName SYSNAME,
LoginName SYSNAME NULL,
DefDBName SYSNAME NULL,
DefSchemaName SYSNAME NULL,
UserID INT,
[SID] VARBINARY(85)
)
DECLARE @command VARCHAR(MAX)
--this will contain all the databases (and their sizes!)
--on a server
DECLARE @databases TABLE
(
Database_name VARCHAR(128),
Database_size INT,
remarks VARCHAR(255)
)
INSERT INTO @databases--stock the table with the list of databases
EXEC sp_databases
SELECT @command = COALESCE(@command, '') + '
USE ' + database_name
+ '
insert into #temp (UserName,GroupName, LoginName,
DefDBName, DefSchemaName,UserID,[SID])
Execute sp_helpuser
UPDATE #TEMP SET database_name=DB_NAME(),
server_name=@@ServerName
where database_name is null
'
FROM @databases
EXECUTE ( @command )--execute the code to get all the users from all the databases
SELECT [users] =
COALESCE(LEFT(SERVER_name,80),'NULL')+CHAR(09)+
COALESCE(LEFT(Database_name,80),'NULL')+CHAR(09)+
COALESCE(LEFT(UserName,80),'NULL')+CHAR(09)+
COALESCE(LEFT(GroupName,80),'NULL')+CHAR(09)+
COALESCE(LEFT(LoginName,80),'NULL')+CHAR(09)+
COALESCE(LEFT(DefDBName,80),'NULL')+CHAR(09)+
COALESCE(LEFT(DefSchemaName,80),'NULL')+CHAR(09)+
COALESCE(CONVERT(VARCHAR(5),[UserID]),'null')
FROM #temp
[/font]
Best wishes,
Phil Factor
January 5, 2008 at 4:35 pm
Heh... C'mon Phil... 😛 this is a 7/2k Forum... there is no VARCHAR(MAX).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2008 at 5:26 pm
Topher, please post the command you want to execute on all of the databases and what you'd like the output to look like.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply