Export SQL to TXT

Export SQL to TXT (Full Version)

All Forums >> [Scripting Technologies] >> VB Script

Message

mstoten -> Export SQL to TXT (6/15/2008 10:21:09 PM)
I have been looking on the net for a way to export SQL to TXT but I have not found anything as yet. Does anyone have some sample code or any information that may help me do this? Thanks, Michael

Tom_Watson -> RE: Export SQL to TXT (6/16/2008 3:48:25 AM)
Are you talking about exporting the results of an SQL query to text? It will depend on which version of SQL you are using. Also, are you looking to do this via a script (e.g. possibly to schedule it) or via a GUI? Tom

jnelson993 -> RE: Export SQL to TXT (6/16/2008 10:44:44 AM)
Yeah, I think you'll need to clarify a little what exactly SQL to TXT means to you and under what circumstances you want it converted.

mstoten -> RE: Export SQL to TXT (6/16/2008 7:26:27 PM)
Hi guys, Sorry for being a bit vague. I am using SQL 2005 and I would ideally like to do this via VB Script if possible. What I am trying to do: - Have an input box to enter Netbios name or serial number of a PC - Retrieve data from a custom table I use for SMS which has warranty information in it (has MachineID in it to, which relates to the rest of the SMS Database) - Store the data from the custom table in a text file for that one PC, data row by row if possible If it can not be done via Script is there a better way? VB Program maybe? Thanks, Michael

Tom_Watson -> RE: Export SQL to TXT (6/17/2008 7:20:27 AM)
If you are looking to query SQL and save results from the command line, you could check out OSQL.EXE or SQLCMD.EXE. All this could be wrapped in VBS to give you the prompts etc. Tom

jnelson993 -> RE: Export SQL to TXT (6/17/2008 6:27:50 PM)
Oh yeah, you can easily query the SMS DB from a VBScript. Here's a little sample: Change the variables at the top to be what you're looking for. ' Setup some variables SMSServer = "SMSSERVER" SMSUID = "" 'leave blank if pass-thru SMSPWD = "" SMSDB = "SMS_DB" WSID = "My Script" OutputFile = "c:\results.txt" ' Setup stuff needed to write to text file Set oFSO = CreateObject("Scripting.FileSystemObject") Set oOutFile = oFSO.OpenTextFile(OutputFile, 2, True) ' Prompt for netbios name/serial number var = inputbox("Please enter a netbios name or a serial number...","Enter Netbios/Serial Number") ' Create a SQL query that uses the netbios name/serial number we just asked for SQLQuery = "SELECT * FROM dbo.sampleTable WHERE (NetbiosName LIKE '" & var & "' OR SerialNumber LIKE '" & var & "')" ' ADODB Connection to SMS. Pull list of discovered users. Set oConn = CreateObject("ADODB.Connection") oConn.Open "Driver={SQL Server};Server=" & SMSServer & ";UID=" & SMSUID & ";PWD=" & SMSPWD & ";DATABASE=" & SMSDB & ";WSID=" & WSID Set oRS = oConn.Execute(SQLQUERY, 3) ' Get the whole result set as a string and write it out If Not oRS.EOF then resultString = oRS.GetString oOutFile.Write(resultString) End If ' Loop through records and write line-by-line 'Do While Not oRS.EOF ' strLine = oRS.Fields("ComputerName") ' ' oOutFile.WriteLine strLine ' oRS.MoveNext 'Loop ' Or, if you just want to save the results as XML 'If Not oRS.EOF Then ' oRS.Save OutputFile, 1 'End If

mstoten -> RE: Export SQL to TXT (6/19/2008 2:58:09 AM)
Thanks John that seems to work pretty good actually. I have modified it alittle, this is what I have: ' Setup some variables SMSServer = "ServerName" SMSUID = "" 'leave blank if pass-thru SMSPWD = "" SMSDB = "SMS_XXX" WSID = "WSCAddData2" OutputFile = "c:\WSCExport.txt" ' Setup stuff needed to write to text file Set oFSO = CreateObject("Scripting.FileSystemObject") Set oOutFile = oFSO.OpenTextFile(OutputFile, 2, True) ' Prompt for netbios name/serial number var = inputbox("Please enter a netbios name or a serial number...","Enter Netbios/Serial Number") ' Create a SQL query that uses the netbios name/serial number we just asked for SQLQuery = "SELECT distinct PurchaseDate00, LeaseDate00, WarrantyDate00 , Name0, b.MachineID from dbo.WSCAdditionalData_DATA a , dbo.Computer_System_DATA b where a.MachineID = b.MachineID and b.MachineID LIKE '" & var & "' or b.Name0 LIKE '" & var & "'" ' ADODB Connection to SMS. Pull list of discovered users. Set oConn = CreateObject("ADODB.Connection") oConn.Open "Driver={SQL Server};Server=" & SMSServer & ";UID=" & SMSUID & ";PWD=" & SMSPWD & ";DATABASE=" & SMSDB & ";WSID=" & WSID Set oRS = oConn.Execute(SQLQUERY, 3) ' Loop through records and write line-by-line Do While Not oRS.EOF strLine = oRS.Fields("PurchaseDate00") strLine1 = oRS.Fields("LeaseDate00") strLine2 = oRS.Fields("WarrantyDate00") strLine3 = oRS.Fields("Name0") strLine4 = oRS.Fields("MachineID") oOutFile.WriteLine strLine oOutFile.WriteLine strLine1 oOutFile.WriteLine strLine2 oOutFile.WriteLine strLine3 oOutFile.WriteLine strLine4 oRS.MoveNext Loop Msgbox("Done") I am having some trouble though, if the PC does not have an entry for Purchase, Lease or Warranty then I receive an error because it cannot retrieve the data 'NULL' from the database. Is there a way I could display a msgbox if one of these entries are NULL?

jnelson993 -> RE: Export SQL to TXT (6/19/2008 9:00:48 AM)
You could put this line somewhere before your loop ON ERROR RESUME NEXT Which will trap your error. Then just use IsNull or something to popup a messagebox. Something like this: On Error Resume Next ' Loop through records and write line-by-line Do While Not oRS.EOF strLine = oRS.Fields("PurchaseDate00") strLine1 = oRS.Fields("LeaseDate00") strLine2 = oRS.Fields("WarrantyDate00") strLine3 = oRS.Fields("Name0") strLine4 = oRS.Fields("MachineID") If IsNull(strLine) Or IsNull(strLine1) Or IsNull(strLine2) Then MsgBox("Missing Data...") End If oOutFile.WriteLine strLine oOutFile.WriteLine strLine1 oOutFile.WriteLine strLine2 oOutFile.WriteLine strLine3 oOutFile.WriteLine strLine4 oRS.MoveNext Loop

mstoten -> RE: Export SQL to TXT (6/19/2008 7:30:01 PM)
Thanks so much for your help John, it works perfect.

Page: [1]

Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.203125