get data from an SQL database

Hi Folk

I would like to get some data from an SQL database using a VBScript.
It would work along the following lines

..define array of sql statements (e.g. select * from customers,
selec...)
..connect to databsae
..run sql statements
..ftp extracted files (csv) to webserver

Can someone tell me
a. if this can be done
b. provide some coding hints on how to go about it
c. tell me how to connect to the server

Thanks a million

Nicolaas

Bob
Mon Jul 17 07:03:10 CDT 2006

windandwaves wrote:
> Hi Folk
>
> I would like to get some data from an SQL database using a VBScript.
> It would work along the following lines
>
> ..define array of sql statements (e.g. select * from customers,
> selec...)
> ..connect to databsae
> ..run sql statements
> ..ftp extracted files (csv) to webserver
>
> Can someone tell me
> a. if this can be done
> b. provide some coding hints on how to go about it
> c. tell me how to connect to the server
>
SQL Server has builtin utilities for exporting data to text files. Assuming
you are talking about SQL 2000 (always tell us the version upfront - it's
almost always relevant), you should look into using DTS for this.
www.sqldts.com can help you get started. If SQL 2005, the SSIS (SQL Server
Integration Services) is the new utility.

Is this a one-time extraction or something that will need to be repeated

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

windandwaves
Mon Jul 17 07:24:43 CDT 2006

Bob Barrows [MVP] wrote:
> windandwaves wrote:
> > Hi Folk
> >
> > I would like to get some data from an SQL database using a VBScript.
> > It would work along the following lines
> >
> > ..define array of sql statements (e.g. select * from customers,
> > selec...)
> > ..connect to databsae
> > ..run sql statements
> > ..ftp extracted files (csv) to webserver
> >
> > Can someone tell me
> > a. if this can be done
> > b. provide some coding hints on how to go about it
> > c. tell me how to connect to the server
> >
> SQL Server has builtin utilities for exporting data to text files. Assuming
> you are talking about SQL 2000 (always tell us the version upfront - it's
> almost always relevant), you should look into using DTS for this.
> www.sqldts.com can help you get started. If SQL 2005, the SSIS (SQL Server
> Integration Services) is the new utility.
>
> Is this a one-time extraction or something that will need to be repeated

It is a daily extraction and I'd prefer to stay out of the SQL database
itself as it is a huge POS system and I do not want to write anything
that interferes with its daily operation. right now, I dont even know
what version it is, but I will find out...

Thanks

Nicolaas

> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Bob
Mon Jul 17 07:53:29 CDT 2006

windandwaves wrote:
> Bob Barrows [MVP] wrote:
>> windandwaves wrote:
>>> Hi Folk
>>>
>>> I would like to get some data from an SQL database using a VBScript.
>>> It would work along the following lines
>>>
>>> ..define array of sql statements (e.g. select * from customers,
>>> selec...)
>>> ..connect to databsae
>>> ..run sql statements
>>> ..ftp extracted files (csv) to webserver
>>>
>>> Can someone tell me
>>> a. if this can be done
>>> b. provide some coding hints on how to go about it
>>> c. tell me how to connect to the server
>>>
>> SQL Server has builtin utilities for exporting data to text files.
>> Assuming you are talking about SQL 2000 (always tell us the version
>> upfront - it's almost always relevant), you should look into using
>> DTS for this. www.sqldts.com can help you get started. If SQL 2005,
>> the SSIS (SQL Server Integration Services) is the new utility.
>>
>> Is this a one-time extraction or something that will need to be
>> repeated
>
> It is a daily extraction and I'd prefer to stay out of the SQL
> database itself as it is a huge POS system and I do not want to write
> anything
> that interferes with its daily operation. right now, I dont even know
> what version it is, but I will find out...
>

Whatever you do is going to "interfere" with its "daily operation". I'd be
willing to bet that using the builtin extraction tools will have a smaller
impact than attempting to do it via a vbscript script.

If you insist on doing it via vbscript, you will need to learn how to use
ADO. Start here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoprogrammersguide.asp

Someone else wll need to get you going with scripting ftp since I'm not
familiar with that and really have neither time nor need to learn about it.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Richard
Mon Jul 17 08:01:07 CDT 2006


"windandwaves" <nfrancken@gmail.com> wrote in message
news:1153139083.176567.274070@s13g2000cwa.googlegroups.com...
> Bob Barrows [MVP] wrote:
>> windandwaves wrote:
>> > Hi Folk
>> >
>> > I would like to get some data from an SQL database using a VBScript.
>> > It would work along the following lines
>> >
>> > ..define array of sql statements (e.g. select * from customers,
>> > selec...)
>> > ..connect to databsae
>> > ..run sql statements
>> > ..ftp extracted files (csv) to webserver
>> >
>> > Can someone tell me
>> > a. if this can be done
>> > b. provide some coding hints on how to go about it
>> > c. tell me how to connect to the server
>> >
>> SQL Server has builtin utilities for exporting data to text files.
>> Assuming
>> you are talking about SQL 2000 (always tell us the version upfront - it's
>> almost always relevant), you should look into using DTS for this.
>> www.sqldts.com can help you get started. If SQL 2005, the SSIS (SQL
>> Server
>> Integration Services) is the new utility.
>>
>> Is this a one-time extraction or something that will need to be repeated
>
> It is a daily extraction and I'd prefer to stay out of the SQL database
> itself as it is a huge POS system and I do not want to write anything
> that interferes with its daily operation. right now, I dont even know
> what version it is, but I will find out...
>
> Thanks
>
> Nicolaas
>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so I
>> don't check it very often. If you must reply off-line, then remove the
>> "NO SPAM"
>

I use ADO to extract info from SQL Server databases. Assuming Windows
Integrated Authentication, the connection string is similar to the example
below. If using the default instance, omit "\MyInstance" in connection
string. The trick is assigning the SQL query to a string variable, with
commas, spaces, and embedded ' in the right places. Requires MDAC on the
client.
=================
Option Explicit
Dim adoRecordset, strSQL, strConnect
Dim strID, strFirst, strLast

' Connection string.
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=MyDatabaseName;" _
& "SERVER=MyServer\MyInstance"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = strConnect

' SQL query.
strSQL = "SELECT FirstName, LastName, MemID " _
& "FROM ClassTable " _
& "WHERE MemID = 3 AND LastName = 'Smith'"

adoRecordset.Source = strSQL
adoRecordset.Open

' Enumerate recordset and output values.
Do Until adoRecordset.EOF
strID = adoRecordset.Fields("MemID").Value
strFirst = adoRecordset.Fields("FirstName").Value
strLast = adoRecordset.Fields("LastName").Value
Wscript.Echo strID & ";" & strName & ";" _
& strLast & ";" & strFirst
adoRecordset.MoveNext
Loop

adoRecordset.Close
Set adoRecordset = Nothing

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net