Re: Running T_SQL script from Access (I can't get it right)

Tech-Archive recommends: Speed Up your PC by fixing your registry

I was not familiar with the difference, but after some reading I now do
understand. GO is for Query Analyzer.

When you generate a SQL script from Enterprise Manager it will have multiple
GO's for each step in the script. Since I like to use the generated script
without manual updating, I came across an interesting post by Tibor Karaszi:

"Loop through the stuff that you want to execute, insert each row into a
variable until
you reach GO. Execute that variable. Continue looping. Don't send the GO."

This approach looks pretty good to me and I will try it out using VBA in
Access.

Do you have an idea about my second question: How should fs, f and ts be
DIMmed?

Many thanks for your reply.

Bonno Hylkema


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
schreef in bericht news:Oqn0jtRzGHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
Remove the GO, this is a batch command, not a T-SQL command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Bonno Hylkema" <bonno.hylkema@xxxxxxxxx> wrote in message
news:44f705c3$0$2019$ba620dc5@xxxxxxxxxxxxxxxxxxxxxx
It might be an old subject, Running T_SQL script froms Access, but I
can't get it right.

I am running Access 2003 and SQL Server 2000.

I generate a SQL script from Enterprise Manager through Generate SQL
script, options International Text (Unicode).

The script Table1.sql looks like follows:

CREATE TABLE [dbo].[Table1] (
[Field1] [int] NOT NULL ,
[Field2] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[Field3] [char] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

In the Access adp I have a function that should read the contents of the
Table1.sql script file and execute the T_SQL command on SQL Server. The
function has the following code:

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs
Dim f
Dim ts
Dim SQL As String
Dim cnn As ADODB.Connection

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\Temp\Table1.SQL")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
SQL = ts.ReadAll
MsgBox SQL
ts.Close

Set cnn = CurrentProject.Connection
cnn.Execute SQL, , adCmdText
cnn.Close

When I run the code I get the error: Line 6: Incorrect syntax near 'GO'.

What is wrong with my setup? The MsgBox shows the proper commands, as far
as I can tell.

How should fs, f and ts be DIMmed?

Any help is welcome. Thanks in advance.

Bonno Hylkema








.

Relevant Pages

  • Re: Converting Perl Web Report to Python
    ... lets say that your sql script looks like this: ... This capability would allow each sql script to be extendable. ... How would you execute ...
    (comp.lang.python)
  • Re: New Window Kills Browser When Connected to Oracle
    ... IIRC an ASP session can only have a single script processing at a given ... > simulating activity using SQL Server is the Provider. ... > I've copied an example maintenance job page below. ... > Dim sql ...
    (microsoft.public.inetserver.asp.general)
  • Re: Scope_Identity returning Decimal for an Integer field
    ... This example uses a couple of different ways to execute SQL and return a value. ... Dim objIdentity As Object ... Private Function GetIdentity() As Integer ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VB.NET using inline sql
    ... Wendy - if your definition of "inline SQL" is to execute a string like ... Dim strInfo as string ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Executing a SQL script on server
    ... > How can I using ADO send a SQL script to the server to be executed there? ... An example VBScript program to execute a stored procedure: ... ' Specify the SQL Server and Instance. ...
    (microsoft.public.vb.database.ado)