VBS script with textcopy command

Posted on 2012-09-05
Last Modified: 2012-09-10
Hi Experts,
I need following script using VBS.

1. Read values on database db01.tbl01.id01 and db01.tbl01.img01
2. I want to create sub-folder id01\sup_doc\ and need to store img01.jpg file on that directory.

I tried with attached dos script, but it seems I can't get multiple records using textcopy command on dos. (AppID is hard coded, and I need to loop multiple AppIDs and should create folders/files accordingly)
set PATH="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"<wbr ></wbr><wbr ></wbr><wbr ></wbr>;%PATH%
set DPath="D:\temp"

set AppIDD="1234567"

IF NOT EXIST %DPath%\%AppIDD% md %DPath%\%AppIDD%\supp_doc

textcopy.exe /S server01 /U user01 /P pwd01 /D db01 /T tbl01 /C field01 /W "where id=%AppIDD%" /Z /O /F %DPath%\\%AppIDD%\supp_doc\img01.jpg


Open in new window

I'm not much good in VBS, but I know we can connect to database and could get data to a record set and could take ids to a array and could create folder/images one by one.
 Please help me to create similar VBS script.
Question by:Dushan911
    LVL 35

    Accepted Solution

    I'm not sure if you wanted to use textcopy.exe from the vbs script but you can do it like this:
    Option Explicit
    Const C_DPath = "D:\temp"
    Const C_IMG_NAME = "img01.jpg"
    Const C_SRV = "server01"
    Const C_DTB = "db01"
    Const C_USR = "user01"
    Const C_PWD = "pwd01"
    Const C_TBL = "tbl01"
    Const C_FLD_ID = "id01"
    Const C_FLD_IMG = "img01"
    Dim oConn, sAppIDD, sSQL, oRst, oFldImg, f, oFSO ' , oFile, n
    Set oConn = CreateObject("ADODB.Connection")
    oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & C_SRV & ";Initial Database=" & C_DTB & ";User ID=" & C_USR & ";Password=" & C_PWD & ";"
    For Each sAppIDD In Array("1234567", "1234568", "1234569")
    	sSQL = "SELECT " & C_FLD_IMG & " FROM " & C_TBL & " WHERE " & C_FLD_ID & " = " & sAppIDD
    	Set oRst = oConn.Execute(sSQL)
    	While Not oRst.EOF
    		oFldImg = oRst.Fields(0).Value
    		If Not IsNull(oFldImg) Then
    		End If
    	Set oRst = Nothing
    Set oConn = Nothing
    Sub SaveFile()
    	Set oFSO = CreateObject("Scripting.FileSystemObject")
    	If Not oFSO.FolderExists(C_DPath & "\" & sAppIDD) Then
    		oFSO.CreateFolder C_DPath & "\" & sAppIDD
    		oFSO.CreateFolder C_DPath & "\" & sAppIDD & "\supp_doc"
    	End If
    	' save file without external object, slower
    	'Set oFile = oFSO.CreateTextFile(C_IMG_NAME, True)
    	'For n = 1 to LenB(oFldImg)
    	'	oFile.Write Chr(AscB(MidB(oLOB, n, 1)))
    	'Set oFile = Nothing
    	SaveBinaryData C_DPath & "\" & sAppIDD & "\supp_doc\" & C_IMG_NAME, oFldImg
    	Set oFSO = Nothing
    End Sub
    ' faster file save, from
    Function SaveBinaryData(FileName, ByteArray)
    	Const adTypeBinary = 1
    	Const adSaveCreateOverWrite = 2
    	'Create Stream object
    	Dim BinaryStream
    	Set BinaryStream = CreateObject("ADODB.Stream")
    	'Specify stream type - we want To save binary data.
    	BinaryStream.Type = adTypeBinary
    	'Open the stream And write binary data To the object
    	BinaryStream.Write ByteArray
    	'Save binary data To disk
    	BinaryStream.SaveToFile FileName, adSaveCreateOverWrite
    	Set BinaryStream = Nothing
    End Function

    Open in new window

    EDIT: replaced my local db settings with examples given.
    LVL 17

    Author Comment

    Yeah Thanks a lot  robert_schutt!
    I should be able to manage within vb without using external textcopy command.

    I tried the code which you provided and it says following error.
    Script   : D:\test2.vbs
    Line     : 28
    Error    : Invalid object name 'tbl01'.
    Code    : 80040E37
    Source :  Microsoft OLE DB Provider for SQL Server

    tbl01 is exists on the database and I tried and can't find exact reason.. Please help me..
    LVL 17

    Author Comment

    Original developer's poor naming for DB column fields as "id", "file"....
    Above error is coming for
    sSQL = "SELECT [file] FROM tbl01 WHERE [id] = " & sAppIDD

    and below error is coming for ....
    sSQL = "SELECT file FROM tbl01 WHERE [id] = " & sAppIDD

    Script   : D:\test2.vbs
    Line     : 28
    Error    : Incorrect syntax near the keyword 'file'.
    Code    : 80040E14
    Source :  Microsoft OLE DB Provider for SQL Server
    LVL 17

    Author Comment

    it works I have to use
    sSQL = "SELECT [file] FROM db01.dbo.tbl01 WHERE [id] = " & sAppIDD
    LVL 17

    Author Closing Comment

    Thanks a lot!
    LVL 35

    Expert Comment

    by:Robert Schutt
    Sorry I was out this morning. Great that you got it to work!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now