Running update Stored Procedure from VBS

• Phil Factor

  SSC-Insane

  Points: 20244

  More actions

  January 6, 2008 at 2:36 am

  #765142

  Cor. I remember SQL Server 2000

  You can still use the same trick, but the code ain't quite as pretty. In fact it isn't at all pretty. Ken Henderson showed how in one of his books. It relies on the fect that the Exec () command will take any number of varchar(8000) variables added together. Actually, I don't know why we're worried as Varchar(8000) should be fine- you'd need a hell of a lot of databases on your server to blow that. you can put in a check to see if you have overflow.

  Best wishes,
  Phil Factor

• Jeff Moden

  SSC Guru

  Points: 1004253

  More actions

  January 6, 2008 at 8:32 am

  #765150

  Heh... that's exactly what sp_MSForEachDatabase does... it's nothing but a huge cursor...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Topher

  SSCommitted

  Points: 1763

  More actions

  January 7, 2008 at 7:34 am

  #765333

  Some simple SP that displays a message rather than result. Say, sp_addlogin. That wold be a good example.

  Also, from a previous post, I have used tsql command to do so. I have this done by two input files, one for servers, one for SQL code. Only thing is, it displays seperate output for every server it runs on. Ugly!

• Phil Factor

  SSC-Insane

  Points: 20244

  More actions

  January 7, 2008 at 7:51 am

  #765341

  if you just run it on a list of servers and databases in SQLCMD. Then all your errors and status messages can be diverted into one file each. No problems.

  e.g.

  --connect to production

  :CONNECT myServer\MyInstance

  use payroll --or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use accounts--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use HR--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use manufacturing--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  --connect to test server

  :CONNECT myServer\MyInstance

  use payroll --or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use accounts--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use HR--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  use manufacturing--or whatever the database name is

  :r $(workpath)$(FileToExecute)

  --and so on for all your 200 servers!

  /*

  You can use …

  :d payroll

  …instead of

  Use payroll

  We save this lot as ‘MyDatabaseList.SQL’ (or whatever)

  So now all you have to do is to set all your variables, and execute the file

  --the user to do

  :setvar rolename "sales"

  :setvar membername "Kilgore"

  :setvar login "SimpleTalk\KilgoreTrout"

  --the file names and paths

  --1/ the actual script to execute

  :setvar FileToexecute "adduser.sql"

  --the list of databases and servers

  :setvar ListOfDatabases "MyDatabaseList.sql"

  -- the file that any errors go into

  :setvar Errorfile "Errors.txt"

  --and the directory we keep this lot in

  :setvar workpath "s:\work\programs\sql\"

  --specify the name of the error file

  :Error $(workpath)$(Errorfile)

  --now we specify the output data file which we'll use to collect the data

  :OUT $(workpath)$(Datafile)

  --and just execute the list of databases and servers

  :r $(workpath)$(ListOfDatabases)

  Best wishes,
  Phil Factor

• Topher

  SSCommitted

  Points: 1763

  More actions

  January 8, 2008 at 11:47 am

  #765839

  I'm still confused on how to grab the message. Obviously it can't be in the rs.value.... And, I do not have the ability to create tables as some can be production boxes.

• Phil Factor

  SSC-Insane

  Points: 20244

  More actions

  January 8, 2008 at 12:32 pm

  #765865

  There is no message. What you see is generated by SSMS/QA. you can check the return code from the stored procedure in VBS if you want to check for the success of the sp.

  You don't need to create a temporary table unless you want to call the sp in several databases. It is a hard DBA who stops you creating temporary tables in TEMPDB. if you dont' have access to tempDB, how do you do a prepared statement I wonder.

  Best wishes,
  Phil Factor