SQL Server Database Build

FinalBuilder Discussion
1

All,

I'm trying to figure out how to use FB to manage a set of database that have frequent changes and promotion cycles.  I have some ideas, but I would really like to see an example where  a set of stored procedures stored in a directory in VSS or TFS get dynamically loaded in a stack (or the like) and then get executed.

Anyone else out there using FB for something like this.

Sean

2

Same here. I need to execute SQL Server 2008 SQL DML scripts such as “CREATE DATABASE …” via FinalBuilder. These scripts run fine in SQL Server Managemenet Studio 2008 queries. They run fine as Stored Procedures in the Master database (a very bad idea). But they do not work from a FB ADO.NET Execute SQL task as either text or file, which is what we need to do.

Could someone from VSoft help us out here, thanks…

3

Hi,

Their shouldn’t be a problem with running DDL (CREATE, ALTER, DROP, etc…) scripts from the either the ‘ADO Execute SQL’ or the ‘MSSQL Execute SQL’ actions. Does the action report an error, or does the table never get created?

Could you provide us with the exact query you’re attempting to execute, as well as the connection string being used (removing any sensitive information first)? If you’d prefer you can email it to support [at] finalbuilder.com instead of posting on the public forums.

Regards,
Paul.

4

Doesn't seem to parse...

"Error expanding SQL Text : Missing % after"

-------------------------------------------------------------------------------------------------------------------------

use master
go

CREATE DATABASE [test_design1] ON PRIMARY
( NAME = N'test_design1', FILENAME = N'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_design1.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_design1_log', FILENAME = N'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_design1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [test_design1] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test_design1].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [test_design1] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [test_design1] SET ANSI_NULLS OFF
GO

ALTER DATABASE [test_design1] SET ANSI_PADDING OFF
GO

ALTER DATABASE [test_design1] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [test_design1] SET ARITHABORT OFF
GO

ALTER DATABASE [test_design1] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [test_design1] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [test_design1] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [test_design1] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [test_design1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [test_design1] SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [test_design1] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [test_design1] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [test_design1] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [test_design1] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [test_design1] SET DISABLE_BROKER
GO

ALTER DATABASE [test_design1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [test_design1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [test_design1] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [test_design1] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [test_design1] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [test_design1] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [test_design1] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [test_design1] SET READ_WRITE
GO

ALTER DATABASE [test_design1] SET RECOVERY FULL
GO

ALTER DATABASE [test_design1] SET MULTI_USER
GO

ALTER DATABASE [test_design1] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [test_design1] SET DB_CHAINING OFF
GO

5

Hi,

You’ll need to escape the % in the query so that FinalBuilder doesn’t attempt to expand it , I.E:

Change "FILEGROWTH = 10%) "

To "FILEGROWTH = 10%%) "

Regards,
Paul.

6

Thanks…

7

Well, that didn’t fix it as it turns out. Now just getting the ubiquitous “Error " Incorrect syntax near ‘go’” …

8

The ‘GO’ separators are not actually valid T-SQL, you’ll either need to separate each command out and execute them one-by-one in the ‘ADO Execute Query’ action, or you can use the ‘MSSQL Execute Query’ with SQLCMD which will handle the batch processing automatically.

9

Posted By Paul Samways on 10 Dec 2008 08:26 PM
The 'GO' separators are not actually valid T-SQL
Right. GO is what the client (SQL Studio or whatever) uses to separate commands. It is plastic, and client configurable.
(That said, FB could be set up to emulate SQL Studio in that respect... I know we had a similar need at one point and coded it ourselves in our client.)