Dear SQLers:

I am trying to get around some of the limitations of T-SQL.

I wish to run a preprocessor over script files much as the C
preprocessor does for C programs. File inclusion and symbol
definition commands are about all I need. Does anyone know of one
that will work well with SQL Server script files?

I want to be able to define symbols like
#define ERRSTL N'50001: String %s is too long.'
in a secondary file and have statements like
raiserror(ERRSTL,16,1,N'ACUK)
expand to
raiserror(N'50001: String %s is too long.',16,1,N'ACUK)
This would allow me to freely reorganise error codes without having to
hunt manually, and it would allow me to have consistency over multiple
scripts.

I have done some searching and apparently one such is m4, but I
have been unable to download it. (I only get a partial download and
do not know how to proceed.)

Any ideas?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
So this is a shameless plug, but you asked for it, didn't you? :-)
http://www.sommarskog.se/AbaPerls/index.html will give you everything you
need and probably too much you don't need, at least for the moment.

For a more modest effort - and with a more complete preprocessor - get
Visual C++ and use that preprocessors. I don't have any samples, but I
believe I've heard people who have done it.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 24 May 2011 07:21:36 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Yes, I did.

I tried and ran into some trouble. The tests for
Win32::SqlServer failed. I got:

SQL Server message 2, Severity 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Message 08001 from 'Microsoft SQL Server Native Client 10.0',
Severity: 16
A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is
configured to allow remote connections. For more information see SQL
Server Books Online.
Message HYT00 from 'Microsoft SQL Server Native Client 10.0',
Severity: 16
Login timeout expired
Terminating on fatal error at t\testsqllogin.pl line 59

My guess is that it is trying to use the wrong instance name. How
do I specify the instance?

Or is it something else?

I will try hunting down some docs for this.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Which means that you were not able to contact SQL Server.

If you were running the test suite for Win32::SqlServer, you need to set up
connection details with OLLEDBTEST as described in Tests.html. If you are
trying to use an AbaPerls tool, use the -S switch to specify a server.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 23/05/11 23:24, Gene Wirchenko wrote:
This is a simple substitution. Have you considered using sed?



--
Bernard Peek
bap (AT) shrdlu (DOT) com

On Wed, 25 May 2011 12:39:20 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

[snip]

Ah, missed that bit. All of the test work (or are skipped). Now,
the next part is actually getting AbaPerls set up. I put the commands
in a batch file and get:

C:\cbs2dev\SQLServer\AbaPerls\Perl>go

C:\cbs2dev\SQLServer\AbaPerls\Perl>set
path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\Sys tem32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\v 1.0;c:\Program
Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft
SQL Server\100\DTS\Binn\;c:\Program Files\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Prog ram Files\Microsoft
Visual Studio
9.0\Common7\IDE\PrivateAssemblies\;c:\batch;c:\uti l;c:\util\Sysinternals;c:\Program
Files\MiKTeX\miktex\bin;C:\Perl\site\bin;C:\Perl\b in;c:\cbs2dev\SQLServer\AbaPerls\Perl

C:\cbs2dev\SQLServer\AbaPerls\Perl>set
perllib=c:\cbs2dev\SQLServer\AbaPerls\Perl

C:\cbs2dev\SQLServer\AbaPerls\Perl>dbbuild -Server LOOP\SQLEXPRESS
-database Banking
defined(%hash) is deprecated at
c:\cbs2dev\SQLServer\AbaPerls\Perl/AbaPerls/AbaS
ql.pm line 433.
(Maybe you should just omit the defined()?)
Use of uninitialized value $opt_subsystem in uc at dbbuild.bat line
458.
DECLARE @name sysname,
Cannot change directory to NAMELESS\sql: No such file or directory

C:\cbs2dev\SQLServer\AbaPerls\Perl>

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
They added more warnings to Perl in 5.12, so you get a lot of these
with AbaPerls. I have address these, but I have not made a public
release of those changes yet. And it will probably take some time
before I do, since I've recently completed a major overhaul to add
support for TFS. I think I want to see it stabilise first...

I think Perl 5.10 is a better choice for the time being.

Just a word of warning: if you found Microsoft's documentation difficult
to get through, AbaPerls is definitely rougher. Now, I don't know
what your current directory is, but let's say you have your SQL files
in Banking\SQL in an AbaPerls directory structure. Then you need to be
in the folder above Banking and specify -subsy BANKING.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Wed, 25 May 2011 23:33:14 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Warnings or errors? By a warning, I mean emit error message and
continue. By an error, I mean emit error and stop.

Groan! That will be three Perl downloads and installations. I
suppose I need the practice.

And this? Where is "NAMELESS\sql" coming from?

No, c:\cbs2dev\SQLServer is the directory name. I have not
gotten into organising my SQL Server code into different directories
yet. I do not have very much.

I do not find Microsoft's documentation difficult to get through.
I find it difficult to find anything in it above the tactical level.
(The syntax of a command is tactical. Why one might use it or the
approach that one uses it in is strategic and often of much greater
concern for me.)

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Warnings. Had I meant errors, I would have said errors. :-)

Bzzt! I have six Perl installations on my machine. And since they have
released 5.14, I will need two more. But, OK, I build Perl modules and
you don't (I assume).


Well, to use DBBUILD you will have no choice to adhere to the AbaPerls
Directory structure. Which means that stored procedures goes into
the SP directory, and they need to have the .sp extension. And so
on, see http://www.sommarskog.se/AbaPerls/doc/structure.html. (The
manual is also in your download.)

Furthermore, with the version of AbaPerls you downloaded, when you build
from disk without SourceSafe, you need to be in a directory two levels
above the SQL directory. You did not specify a subsystem name, why
AbaPerls assumes the name NAMELESS. Whence NAMELESS\sql.

What is silly is that you should be able to run DBBUILD in your SQL
directory or in, say, SQL\Scripts, and I have fixed this, but it's
in the public release.

I'm afraid that using AbaPerls without reading the manual can be a rough
ride, since there are some things you may not expect at first. And the
rules are complex - Hey, I started the develop AbaPerls in 1996, so it
has acquired some complexity by time.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thu, 26 May 2011 23:44:20 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

[snip]

I give up. I can not even tell if anything useful is getting
done.

Sincerely,

Gene Wirchenko