Re: Setting file permissions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can use the Run method of the wshShell object to run the osql command
line utility. This is for SQL Server databases. This utility runs the sql
statements in a text file. For example, at a command prompt I can run:

osql -S MyServer\MyInstance -d MyDatabase -E -i MyQuery.sql

osql has syntax help at a command prompt. The -E means a Trusted Connection.
If you don't use Windows Integrated Authentication you must pass username
and password. The sql statements are in MyQuery.sql. The Query Analyzer tool
can be used to create the *.sql file. In VBScript you can code (watch line
wrapping):

Set objShell = CreateObject("Wscript.Shell")
strCmd = "%comspec% /c osql -S MyServer\MyInstance -d MyDatabase -E -i
MyQuery.sql"
objShell.Run strCmd

If the sql statements output results, you will need to capture that. One way
is to redirect the output to a txt file. For example:

strCmd = "%comspec% /c osql -S MyServer\MyInstance -d MyDatabase -E -i
MyQuery.sql > report.txt"

If the SQL Server database is in the default instance, use "-S MyServer",
with no instance.
--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Ross" <Ross@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:83BE957C-5D48-448E-B445-F757664910B4@xxxxxxxxxxxxxxxx
Thanks for that Richard, but is it possible to have the sql in a text file
and to run that text file rather than have the sql hardcoded into the
vbscript.
--
Thanks Ross


"Richard Mueller" wrote:

An example of a VBScript program that runs an SQL query against an SQL
Server database and returns a recordset. The connection string will
depend
on your DBMS. I use Windows integrated authentication:
=========
Option Explicit

Dim strConnect, adoConnection, adoRecordset
Dim strSQL, strFirst, strLast, strGrade

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

' Connect to database.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.Open

' Create ADO Recordset object.
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection

' Specify T-SQL statement to execute.
strSQL = "SELECT FirstName, LastName, Grade " _
& "FROM dbo.MyTable " _
& "WHERE StudentID = 54"

' Run the query.
adoRecordset.Source = strSQL
adoRecordset.Open

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values and output.
strFirst = adoRecordset.Fields("FirstName").Value
strLast = adoRecordset.Fields("LastName").Value
strGrade = adoRecordset.Fields("Grade").Value
Wscript.Echo strLast & "," & strFirst & "," & strGrade
adoRecordset.MoveNext
Loop
adoRecordset.Close

' Clean up.
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing

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





.

Relevant Pages

  • Re: Recordset Not Updatable
    ... > Server database, ... I was correcting and enhancing the ADP front-end to SQL ... updates by executing individual SQL statements for each control/field that ... But, as Sylvain says, there are many reasons why a recordset is not ...
    (microsoft.public.access.adp.sqlserver)
  • jtds driver and SQL Server performance problem
    ... I am using the opensource jtds driver to retreive information from an SQL ... The java application and the SQL Server database both ... Both tables are fairly large (approx. ...
    (comp.lang.java.databases)
  • Re: SQL Server 2005 Restore Error
    ... then the SQL Server Express edition only support ... Export data from the client database(Place the exported file to FTP ... location.)and import it in server database using SMO. ... and execute the sql file from server database using SMO. ...
    (microsoft.public.sqlserver.server)
  • Re: Suggestions to reduce memory use when splitting a string
    ... I have written and utility to convert our DOS COBOL data files to a SQL ... Server database. ... We are parsing roughl 81 files and range in size 1 kb to 65 ...
    (microsoft.public.dotnet.languages.vb)
  • Who is calling my SP?
    ... I have to find out where an old stored procedure A is being used in our SQL ... Server database. ... Is there any command which can tell which other stored ...
    (microsoft.public.sqlserver.programming)