Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Scripted Server Snapshot Expand / Collapse
Author
Message
Posted Wednesday, June 29, 2005 4:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 8:36 AM
Points: 28, Visits: 6
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp


Post #195541
Posted Thursday, June 30, 2005 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 8:36 AM
Points: 28, Visits: 6
Regarding the problem with outputing to Word.  The DBName has nulls.  We used an If..Then to detect the Null, and output "no db" else the name of the database.  If anyone is interested I can e-mail the code.


Post #195663
Posted Tuesday, July 12, 2005 12:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

And adding a short inputbox for the server is cool if your centrally managing multiple servers.

[...]
Dim i
Dim srvname

srvname = InputBox ( "Enter the Server Name", "Server", "xxxxxx")

' making the connection to your sql server
' change yourservername to match your server
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

' this is using the trusted connection if you use sql logins
' add username and password, but I would then encrypt this
' using Windows Script Encoder
objConnection.Open _
    "Provider = SQLOLEDB;Data Source=" & srvname & " ;" & _
        "Trusted_Connection=Yes;Initial Catalog=Master;"
[...]

And this little addition formats the CPUTime column so that you can sort the column afterwards:

[...]
objExcel.Cells(i, 7).Value = FormatNumber(objRecordset.Fields.Item("CPUTime"), 0, -1, 0, 0)
[...]

Otherwise a cool piece of code. I don't have the time to get into coding much these days, but that might change.

Thanks

hot2use




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Post #199712
Posted Tuesday, July 12, 2005 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:28 AM
Points: 9, Visits: 509
I called objRecordSet rs and objWorksheet ws,
but it seems like you could just do something like this

For CurCol = 0 To rs.Fields.Count - 1: ws.Cells(1, CurCol + 1).Value = rs.Fields(CurCol).Name: Next
ws.Range("A2").CopyFromRecordset rs



Post #199831
Posted Tuesday, July 12, 2005 1:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1
Why do you have the banner (row 1) cells defined in the loop?
Post #200189
Posted Tuesday, July 12, 2005 4:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:28 AM
Points: 9, Visits: 509
Just as the column names, for use with pivot tables. I use this code to automate reporting to excel. A few lines of code hoses the data to excel & refreshes all the pivot tables & graphs in the workbook...


Post #200248
Posted Wednesday, July 13, 2005 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 8:36 AM
Points: 28, Visits: 6

Great ideas.  Thanks.

 




Post #200419
Posted Wednesday, July 13, 2005 6:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 8:36 AM
Points: 28, Visits: 6

You can remove the titles from the loop. 

 




Post #200422
Posted Tuesday, July 19, 2005 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 1:27 AM
Points: 116, Visits: 59

Hi,

This is really a nice script.  I made a few changes.  Now it works with any SELECT statement or stored proc.

I posted the code on my blog:

http://users.telenet.be/frederik.vandeputte/blog/2005/07/anyquery2excelvbs.html

 

Kind regards,

 

Frederik

Post #202397
Posted Tuesday, July 19, 2005 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:28 AM
Points: 9, Visits: 509
you can replace the entire second loop with 1 command:

objWorksheet.Range("A2").CopyFromRecordset objRecordSet



Post #202442
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse