SQL Script Substitutions

[Jack Suddards]

I am using the SQL Import function to create a script for generating my sql database/tables on the target sql server. The problem I am having is that the script generator is including some options that are unacceptable to the Query Analyser and therefore the scripts will not run. The Option that is unacceptable is TEXTIMAGE_ON [PRIMARY] which can not be used for tables with no large text fields but the generator is putting it in anyway.

So my solution would be to substitute a 'blank' string for that particular string in the generated script. I can do it manually using Search/Replace but then I can't use the option to regenerate the script. I tried to use Text Substitution with the second string 'blank' (nothing in the field) and the substitution doesn't happen. I tried substituting a variable from the string table with no value but that woldn't work either.

Any suggestions would be appreciated.

Jack

[ddeward]

The SQL Script text substitution does work for me either. I'm usgin 11.5, and was wondering if there is a way to debug this? I mean that when I turn on logging, I don't see any statements that indicate that text replacement happens.

Thanks,
Dan

[Jack Suddards]

I was able to get around this problem by substituting a comment as follows:

The original line generated by the import was

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I need to get rid of the part that begins TEXTIMAGE so I made a substitution searching for

TEXTIMAGE_ON [PRIMARY]

and a replace string of

-- TEXTIMAGE_ON [PRIMAR] removed to prevent syntax problems

the -- characters turn whatever follows on the line into a comment

This seems to work just fine although I have never seen the script with the substitutions - I undertand that they take place at runtime ??

Jack.

[ddeward]

What I thought was not working, is working now. Apparently when the SQL scripts ran, it was creating my databases in the default location. After unchecking the "Create catalog if not present" option, the text substition to create my MDF/LDF files in the [INSTALLDIR] worked. Sorry for the false alarm.

dan