Not so much of a question... more of an answer - SQL Report Generation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Not so much of a question... more of an answer - SQL Report Generation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-11 : 10:14:22

Mierk writes "I was being innudated with requests for reports out of multiple databases spread across multiple SQL Servers. My typical response would be to whip up the query, use a small script to output the results in a delimited text file and deliver it to the user(s) who import it easily enough into Excel. This became so frequent that I decided to minimize the work I had to do each time. The following code (vbs) will allow you to create a pipe delimited text file from any sql query on any SQL Server. Call it from the command line with it's parameters like so:

report.vbs <server> <database> <query> <filename>

It will hit the specified database on the specified server with the specified query and generate the specified file as a pipe delimited text file. Several of my frequent requestors have now written small batch files that create these reports for them whenever they need them.

Here is the code, please let me know what you think:


if wscript.arguments.count <> 4 then
	msgbox "Usage: report.vbs <server> <database> <select statement> <output file>" & vbcrlf & "Encapsulate each argument within quotes and separate each argument with a single space."
	wscript.quit
end if

if wscript.arguments.count = 4 then
	strServer = wscript.arguments(0)
	strDatabase = wscript.arguments(1)
	strSQL = wscript.arguments(2)
	strReportFile = wscript.arguments(3)
end if

set oFileSystem = wscript.createobject("scripting.filesystemobject")
set cn = createobject("adodb.connection")
set rs = createobject("adodb.recordset")

'cn.connectionstring = "Provider=SQLOLEDB.1;Initial Catalog=" & strDatabase & ";Data Source=" & strServer & ";Integrated Security=SSPI"
cn.Provider = "SQLOLEDB"
cn.Properties("Prompt") = 1
cn.open "Data Source=" & strServer & ";Initial Catalog=" & strDatabase

rs.open strSQL,cn,3
set objReportFile = oFileSystem.createtextfile(strReportFile)

if rs.recordcount <> 0 then
	WriteReport
else
	objReportFile.writeline("0 records matched your query")
end if

wscript.echo strReportFile & " created."

function WriteReport()
	strTemp = ""
	iCount = 0
	for each oField in rs.fields
		iCount = iCount + 1
		if iCount < rs.fields.count then
			strTemp = strTemp & oField.name & "|"
		else
			strTemp = strTemp & oField.name
		end if
	next
	objReportFile.writeline(strTemp)
		
	do while not rs.eof
		strTemp = ""
		for i = 0 to rs.fields.count - 1
			if i < rs.fields.count -1 then
				strTemp = strTemp & rs(i) & "|"
			else
				strTemp = strTemp & rs(i)
			end if
		next
		objReportFile.writeline(strTemp)
		rs.movenext
	loop
end function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources