myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


Export SQL to TXT

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Scripting Technologies] >> VB Script >> Export SQL to TXT Page: [1]
Login
Message << Older Topic   Newer Topic >>
Export SQL to TXT - 6/15/2008 10:21:09 PM   
mstoten

 

Posts: 50
Score: 0
Joined: 9/2/2007
Status: offline 		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
Post #: 1
RE: Export SQL to TXT - 6/16/2008 3:48:25 AM   
Tom_Watson

 

Posts: 92
Score: 4
Joined: 9/13/2006
Status: offline 		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

(in reply to mstoten)
Post #: 2
RE: Export SQL to TXT - 6/16/2008 10:44:44 AM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to mstoten)
Post #: 3
RE: Export SQL to TXT - 6/16/2008 7:26:27 PM   
mstoten

 

Posts: 50
Score: 0
Joined: 9/2/2007
Status: offline 		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

(in reply to jnelson993)
Post #: 4
RE: Export SQL to TXT - 6/17/2008 7:20:27 AM   
Tom_Watson

 

Posts: 92
Score: 4
Joined: 9/13/2006
Status: offline 		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

(in reply to mstoten)
Post #: 5
RE: Export SQL to TXT - 6/17/2008 6:27:50 PM  1 votes
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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



_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to mstoten)
Post #: 6
RE: Export SQL to TXT - 6/19/2008 2:58:09 AM   
mstoten

 

Posts: 50
Score: 0
Joined: 9/2/2007
Status: offline 		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?

< Message edited by mstoten -- 6/19/2008 2:59:46 AM >

(in reply to jnelson993)
Post #: 7
RE: Export SQL to TXT - 6/19/2008 9:00:48 AM  1 votes
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		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


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to mstoten)
Post #: 8
RE: Export SQL to TXT - 6/19/2008 7:30:01 PM   
mstoten

 

Posts: 50
Score: 0
Joined: 9/2/2007
Status: offline 		Thanks so much for your help John, it works perfect.

(in reply to jnelson993)
Post #: 9
Page:   [1]
All Forums >> [Scripting Technologies] >> VB Script >> Export SQL to TXT Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.453