The Environment: Solaris 10, /bin/sh script using sqsh and freetds to talk to an MS SQL Server
The (TLDR) Problem: I need sqsh to ignore a dollar sign and pass it through to MS SQL.
The Background: I'm writing some code that dynamically builds SQL to alter existing indexes but when it runs, I get errors:
Msg 2727, Level 11, State 1
Server 'HOST\SERVER', Line 1
Cannot find index 'foo'.
I dig around and see that there is no index named foo but there is one named foo$bar.
The built-up input SQL input looks fine...
% grep 'foo' input.sql
alter index [foo$bar] on ...
...and running this SQL through a New Query session or a third party app succeeds. However, the results when passed through sqsh don't see past that dollar sign:
% sqsh -e -i input.sql -o output.sql
% grep 'foo' output.sql
1> alter index [foo] on ...
...which suggests it's interpreting $bar as a variable or something.
Anyone know how to get sqsh to escape a dollar sign or allow one to pass through? I've tried various combinations of quotes and backslashes.
Help me, stackoverlow. You're my only hope.
Reference manual on page 5 states: "Note that in order to prevent the expansion of a variable use either single quotes, or two \’s, like thus:
1> \echo \\$name
$name
So, I believe that to prevent sqsh to substitute $bar with an empty string, you have to write something like:
alter index [foo\\$bar] on ...
alter index ['foo$bar'] on ...
To test it you can try first with SELECT 'foo\\$bar' = 1 or something similar.