cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tparks
Level 3

TextSubSetValue method and SQL Script text replacement

I’m calling a function TextSubSetValue ( "", svValue, TRUE );
Where svValue is the location of a data folder for SQL Server 2005.

I wanted to use Text Replacement tab in Sql Scripts to update occurrences of the key DatabaseDataDir in my sql add script when creating a new database.

I’ve set “Find What” to DatabaseDataDir and “Replace with” to

But my script fails. Should it be possible to use Text Replacement in an SQL Script based on the label used in TextSubSetValue()?

This is an Installscript MSI project.

Thanks
T.
Labels (1)
0 Kudos
(4) Replies
DebbieL
Level 17

For InstallScript MSI projects, you'll want to use Windows Installer properties instead of InstallScript text subs. So, in the SQL Scripts view, you'll enter something like [MYPROPERTY]. In your script, use MsiSetProperty to set the value of the property.

The following links might be helpful:
Using Windows Installer Properties to Dynamically Modify XML Files
Using Windows Installer Properties to Dynamically Modify IIS Settings
I hope that helps.
0 Kudos
tparks
Level 3

Hi
these worked a charm thanks, but I mistakenly said we were using an InstallScript MSI project where in fact it's an InstallScript project. So MsiSetProperty is not availible. So I'm trying to use TextSubSetValue ( "[DATAMDF]", svValue, TRUE ) in place of the other method below, but there seems to be an issue for the sql script running.

thanks
T.


if(MsiSetProperty( ISMSI_HANDLE, "DATAMDF", svValue )=ERROR_SUCCESS) then
MessageBox ("GOOD set of property string", SEVERE);
else
MessageBox ("FAILED to set of property string", INFORMATION);
endif;



Text Replacement tab for the sql script has an entry
Find SPC_DB_PATH replace with [DATAMDF]


And in my SqlScript I have
CREATE DATABASE [Tim] ON PRIMARY
( NAME = N'Tim', FILENAME = N'SPC_DB_PATH\Data\Tim.mdf' , SIZE = 1131520KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Tim_log', FILENAME = N'SPC_DB_PATH\Data\Tim_log.ldf' , SIZE = 1219712KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
0 Kudos
tparks
Level 3

Hi
I found the solution for an InstallScript is to use angle brackets, so I now have the following; changes from what worked for an InstallScript MSI shown in red

if(TextSubSetValue ( "<DATAMDF>", svValue, TRUE )=ISERR_SUCCESS) then
MessageBox ("GOOD set of property string", SEVERE);
else
MessageBox ("FAILED to set of property string", INFORMATION);
endif;



Text Replacement tab for the sql script has an entry
Find SPC_DB_PATH replace with <DATAMDF>


And in my SqlScript I have
CREATE DATABASE [Tim] ON PRIMARY
( NAME = N'Tim', FILENAME = N'SPC_DB_PATH\Data\Tim.mdf' , SIZE = 1131520KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Tim_log', FILENAME = N'SPC_DB_PATH\Data\Tim_log.ldf' , SIZE = 1219712KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
0 Kudos
DebbieL
Level 17

Ah, I'm glad that you figured it out. Thanks for the update!
0 Kudos