Help with inserting data into a remore SQL server table

[WyldeCarde]

Good afternoon

I have a need to run a succession of SQL scripts from a client PC to update a series of tables on a SQL server running on a VMware session on the PC in question.

The client PC doesn't have SQL server installed, and I need to run the SQL scripts via a VB script or similar, can anyone give a bit of guidance?

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[PHV]

Have you tried to play with ADODB ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WyldeCarde]

this is what I have so far, based on what this guys over here was working on -

http://tek-tips.com/viewthread.cfm?qid=1577819&page=2

Option Explicit
Dim objFSO, strTextFile1, strTextFile2, strTextFile3, strTextFile4, strData, strLine, arrLines, mConnection, filestream1, filestream2, filestream3, filestream4

CONST ForReading = 1

strTextFile1 = "C:\scripts\deletetemp.sql"
strTextFile2 = "C:\scripts\updatetemp.sql"
strTextFile3 = "C:\scripts\delete.sql"
strTextFile4 = "C:\scripts\update.sql"

Set objFSO = CreateObject("Scripting.FileSystemObject")

set filestream1 = objFSO.OpenTextFile(strTextFile1,ForReading,false,-1)
set filestream2 = objFSO.OpenTextFile(strTextFile2,ForReading,false,-1)
set filestream3 = objFSO.OpenTextFile(strTextFile3,ForReading,false,-1)
set filestream4 = objFSO.OpenTextFile(strTextFile4,ForReading,false,-1)

Function WriteToDatabase(sUserName)

Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1;Data Source=sqlserver\instance;Initial Catalog=mydb","user","password"

do while not filestream1.atendofstream
    strData = filestream1.readline
    mConnection.Execute strData
loop
do while not filestream2.atendofstream
    strData = filestream2.readline
    mConnection.Execute strData
loop
do while not filestream3.atendofstream
    strData = filestream3.readline
    mConnection.Execute strData
loop
do while not filestream4.atendofstream
    strData = filestream4.readline
    mConnection.Execute strData
loop

Set mConnection = Nothing

I don't think he got it working and no one answered his post as yet. I'm more of a data guy than a VB guy so I'm a trifle lost.

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[mrmovie]

are you missing a "End Function" and a call to the actual function?


Set objFSO = CreateObject("Scripting.FileSystemObject")

set filestream1 = objFSO.OpenTextFile(strTextFile1,ForReading,false,-1)
set filestream2 = objFSO.OpenTextFile(strTextFile2,ForReading,false,-1)
set filestream3 = objFSO.OpenTextFile(strTextFile3,ForReading,false,-1)
set filestream4 = objFSO.OpenTextFile(strTextFile4,ForReading,false,-1)
'call to the function..
Call WriteToDatabase("username_x_which_isnt_actually_used_in_the_function")

 

[tektipGuyInWhistler]

Hello.

If you have the sql tools (I think the osql.exe referenced below is in the SQL2005 free download from MS) then you can run your sql scripts with a .cmd or .bat file.

Here is an example; note the paths being set at the top of the batch and adjust as required.

Hope this helps.
Eric

[BEGIN BATCH]
set ePath="C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"

goto Done

%ePath% -S serverName\sqlexpress -d MIT_DEV -E -i "F:\Database\File1.sql"
%ePath% -S serverName\sqlexpress -d MIT_DEV -E -i "F:\Database\File2.sql"
%ePath% -S serverName\sqlexpress -d MIT_DEV -E -i "F:\Database\File3.sql"

goto Done

one
echo %Date% %Time% - Done
echo.

 

[WyldeCarde]

Just realised I totally failed to say thanks for this, although I couldn't get it running remotely due to firewall crap, I got it all runnign from the desktoop of the VPC

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************