Drop All Tables on SQL Server

by | Oct 6, 2006 | BizTalk Community Blogs via Syndication

I’ve recently been working on getting Community Server installed for another website
and had the web installer get a Connection Timeout half way through the database creation. 
As such, I was looking for a quick way to scrub a database of all tables (dropping
the whole database was not a convenient option).  I looked around a bit on the
web and came up with this
handy script:

IF DB_NAME() IN (‘master’, ‘msdb’, ‘model’, ‘distribution’)
BEGIN
RAISERROR(‘Not for use on system databases’, 16, 1)
GOTO Done
END
SET NOCOUNT ON
DECLARE @DropStatement nvarchar(4000)
DECLARE @SequenceNumber int
DECLARE @LastError int
DECLARE @TablesDropped int
DECLARE DropStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
–views
SELECT
1 AS SequenceNumber,
N’DROP VIEW ‘ +
QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N’VIEW’ AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME)),
‘IsSchemaBound’) = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME)),
‘IsMSShipped’) = 0
UNION ALL
–procedures and functions
SELECT
2 AS SequenceNumber,
N’DROP PROCEDURE ‘ +
QUOTENAME(ROUTINE_SCHEMA) +
N’.’ +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N’FUNCTION’ AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N’.’ +
QUOTENAME(ROUTINE_NAME)),
‘IsSchemaBound’) = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N’.’ +
QUOTENAME(ROUTINE_NAME)),
‘IsMSShipped’) = 0
UNION ALL
–foreign keys
SELECT
3 AS SequenceNumber,
N’ALTER TABLE ‘ +
QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME) +
N’ DROP CONSTRAINT ‘ +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N’FOREIGN KEY’
UNION ALL
–tables
SELECT
4 AS SequenceNumber,
N’DROP TABLE ‘ +
QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N’BASE TABLE’ AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N’.’ +
QUOTENAME(TABLE_NAME)),
‘IsMSShipped’) = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @SequenceNumber, @DropStatement
IF @@FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR(‘%s’, 0, 1, @DropStatement) WITH NOWAIT
–EXECUTE sp_ExecuteSQL @DropStatement
SET @LastError = @@ERROR
IF @LastError > 0
BEGIN
RAISERROR(‘Script terminated due to unexpected error’, 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO

How Pipeline Component Assemblies are Loaded

How Pipeline Component Assemblies are Loaded

by | Oct 6, 2006 | BizTalk Community Blogs via Syndication

One topic that sometimes causes confusion when developing custom pipeline components
in BizTalk Server is how, and where, should their assemblies should be deployed and
how they are loaded by BizTalk. Let’s talk a little bit about it.

In BizTalk Server 2004, there was only one place to deploy your custom pipeline components:
The “Pipeline Components” folder on your BizTalk installation directory. Some conflicts arose
because of this requirement, and made it “uncomfortable” to have components that had
external dependencies on other assemblies.

In BizTalk Server 2006, this restriction was lifter, and now the recommendation is:
Deploy to the Pipeline Components folder on development machines only; deploy to
the GAC. The former is really only necessary so that the design time infrastructure
(i.e. the pipeline designer) works.

But let’s look at what actually happens when you use a custom pipeline component in
a BizTalk pipeline:

  1. When you add the component to the pipeline, it is referenced in the .BTP file using
    the complete class name that implements the component as well as the partial assembly
    name that contains it (i.e. name only, no version/culture/publictoken). Usually, a
    project reference might be added to the assembly as well, but this is not used during
    compilation. (Thanks to Scott
    Colestock     for pointing this out).
  2. When the BizTalk project is compiled, a strongly types class is generated representing
    your custom pipeline. That class will dynamically load all pipeline components used
    it in, and does so by using the complete, assembly-qualified name of the component
    class, which does include full assembly name information.

    Each component is loaded by a call to PipelineManager.CreateComponent() which eventually
    loads the component using reflection.

In BizTalk Server 2006, the components are loaded internally by the CreateManagedInstance()
method of the internal ComponentLoader class, which does the following steps:

  1. If it has an explicit assembly path, it will try to use Assembly.LoadFrom() to load
    it. This usually doesn’t happen, btw.
  2. It will then try to use Type.GetType() to get to the class type. If the assembly is
    GAC’ed or in the probing path, then this should cause the assembly to be probed and
    loaded in the Load context without problems. This is the case you’ll usually prefer
    to happen in servers, as it will give you the least trouble.
  3. If that fails, BizTalk will then extract the assembly name from the qualified type
    name, append “.dll” to the base name and then again try to do a LoadFrom() trying
    to load it manually from the Pipeline Components folder.

One could think that perhaps a better option for the BizTalk design would’ve been
to make the Pipeline Components folder part of the probing path for BizTalk and simply
let the runtime do its thing. This is not the case, however. In any case, this wouldn’t
have worked because pipelines are not only used inside the BizTalk Application Hosts
(i.e. BTSNTSvc.exe), but also in isolated hosts like IIS where having the Pipeline
Components folder as part of the probing path is not an option.

Faxing BizTalk Adapter Sample – WITH source

by | Oct 5, 2006 | BizTalk Community Blogs via Syndication

Just got an email from Andrew (great all round good guy) about a cool Adapter example
he came across…..very nice.

Enabling Faxing of messages from BTS using the Win2K3 FaxServices API and the Office2003
Document Imaging Library.

Enough said – BizTalk
Fax Adapter Project

Nice work!

————- snippet from the Project Page ————–
What the BTS Fax Adapter Does


When the FaxMessage Arrives to the Incomming Archive. The Fax
Adapter Copies the Tiff Image (FaxMessage) to the temporary folder and runs OCR on
the Tiff Image and Extracts the Text and submits to BizTalk as a message, or takes
messages from BizTalk Server and Sends to the FaxConsole. It provides code to build
either a dynamic or a static adapter; however, the following procedure only outlines
the static adapter. A static adapter is an adapter with a static set of schemas and
no custom user interface. A dynamic adapter has a custom user interface and potentially
a dynamic set of schemas. Both static and dynamic adapters use the Add Adapter Wizard
to add their schemas to a BizTalk project