read file and exec contents

24 Mar 2006 6:29 AM

Andre

I have a text file that contains "create index" statements - thanks to those
of you here who helped me get past my hurdles with this step earlier in the
week. :)

Now I need to read the file and execute it - basically execute the contents
of the file. I'm looking for suggestions on how to do it. I thought about
using the code below to read the contents into a global var but it seems as
if it only reads so many chars into a global var. My thought was to read it
into a global var then use an execute sql task to execute the global var.

Here is what I have so far. Any suggestion on the best way to apply the
create index statements in the text file?

Thanks, Andre

Function Main()

Set fso = CreateObject("Scripting.FileSystemObject")
Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)

' msgbox textStreamObject.ReadAll

DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll

Set textStreamObject = Nothing
Set fso = Nothing

Main = DTSTaskExecResult_Success
End Function

Sample of my TableIndexes.txt file
ALTER TABLE [MYTABLE] ADD
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[RowID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

24 Mar 2006 7:36 AM

Tibor Karaszi

Why don't you just use OSQL or SQLCMD to do this?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Andre" <no@spam.com> wrote in message news:eufKUxwTGHA.1728@TK2MSFTNGP11.phx.gbl...
>I have a text file that contains "create index" statements - thanks to those of you here who helped
>me get past my hurdles with this step earlier in the
> week. :)
>
> Now I need to read the file and execute it - basically execute the contents of the file. I'm
> looking for suggestions on how to do it. I thought about
> using the code below to read the contents into a global var but it seems as if it only reads so
> many chars into a global var. My thought was to read it
> into a global var then use an execute sql task to execute the global var.
>
> Here is what I have so far. Any suggestion on the best way to apply the create index statements
> in the text file?
>
> Thanks, Andre
>
> Function Main()
>
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
>
> ' msgbox textStreamObject.ReadAll
>
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
>
> Set textStreamObject = Nothing
> Set fso = Nothing
>
> Main = DTSTaskExecResult_Success
> End Function
>
>
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
>

24 Mar 2006 3:00 PM

JT

Since you are reading the file from VBScript, you could simply store the
string in a local variable and execute as a statement using
ADODB.Command.Execute method call from the same script:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthexecutex.asp
It seems there is no need to store the string in a DTS variable, just a
local variable. For example:

Dim strIndexes

If this task does not need to be schedules, then perhaps instead of DTS, use
Windows Scripting Host or OSQL from the command line.

Show quote

"Andre" <no@spam.com> wrote in message
news:eufKUxwTGHA.1728@TK2MSFTNGP11.phx.gbl...
>I have a text file that contains "create index" statements - thanks to
>those of you here who helped me get past my hurdles with this step earlier
>in the
> week. :)
>
> Now I need to read the file and execute it - basically execute the
> contents of the file. I'm looking for suggestions on how to do it. I
> thought about
> using the code below to read the contents into a global var but it seems
> as if it only reads so many chars into a global var. My thought was to
> read it
> into a global var then use an execute sql task to execute the global var.
>
> Here is what I have so far. Any suggestion on the best way to apply the
> create index statements in the text file?
>
> Thanks, Andre
>
> Function Main()
>
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
>
> ' msgbox textStreamObject.ReadAll
>
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
>
> Set textStreamObject = Nothing
> Set fso = Nothing
>
> Main = DTSTaskExecResult_Success
> End Function
>
>
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
>

24 Mar 2006 4:59 PM

Andre

It does need to be scheduled, and is actually part of a large DTS package.
I did change it to be in a local var rather than a global var - thanks for
that tip. It's working great now, thanks for your help.

Andre