read file and exec contents

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
w. :)
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]
GOWhy 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/
"Andre" <no@.spam.com> wrote in message news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...darkred">
>I have a text file that contains "create index" statements - thanks to thos
e of you here who helped
>me get past my hurdles with this step earlier in the
> w. :)
> Now I need to read the file and execute it - basically execute the content
s 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 a
s 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 c
reate 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
>|||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/d.../>
xecutex.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.
"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
> w. :)
> 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
>|||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