I've been trying to get the scripts posted earlier to the group by
Clay Beatty to work properly. Using the three components he posted, I
have managed to get a new SP generated. However, when I paste this
scrip into a new copy of the original database (different name, same
structure as a test), I get an Invalid Object Name for the temp table
#PersistedVaria bles.
Has anyone run into something like this before? Or used the scripts I
mentioned successfully? Help would be appreciated. The resulting
section of code causing the error is posted below. This is running on
SQL-Server 2000.
Tim Pascoe
CREATE PROCEDURE [dbo].[Create Diagrams] AS
------------------------------------------------------------------------
-- Database Diagram Reconstruction Script
------------------------------------------------------------------------
-- Created on: 2004-07-19 11:54:43.327
-- From Database: CABIN
-- By User: dbo
--
-- This SQL Script was designed to reconstruct a set ofdatabase
-- diagrams, by repopulating the system table dtproperties, inthe
-- current database, with values which existed at the timethis
-- script was created. Typically, this script would be createdto
-- backup a set of database diagrams, or to package up thosediagrams
-- for deployment to another database.
--
-- Minimally, all that needs to be done to recreate the target
-- diagrams is to run this script. There are several options,
-- however, which may be modified, to customize the diagrams tobe
-- produced. Changing these options is as simple as modifyingthe
-- initial values for a set of variables, which are definedimmediat ely
-- following these comments. They are:
--
-- Variable Name Description
-- -------------------------------------------------------------------
-- @TargetDatabase This varchar variable will establishthe
-- target database, within which thediagrams
-- will be reconstructed. This variableis
-- initially set to database name fromwhich
the
-- script was built, but it may bemodified as
-- required. A valid database namemust be
-- specified.
--
-- @DropExistingDi agrams This bit variable is initially setset to a
-- value of zero (0), which indicatesthat any
-- existing diagrams in the target
-- to be preserved. By setting thisvalue to
-- one (1), any existing diagrams inthe target
-- database will be dropped prior to
-- reconstruction. Zero and One are theonly
-- valid values for the variable.
--
-- @DiagramSuffix This varchar variable will be usedto append
-- to the original diagram names, asthey
-- existed at the time they werescripted. This
-- variable is initially set to take onthe
-- value of the current date/time,although it
-- may be modified as required. Anempty
string
-- value would effectively turn off thediagram
-- suffix option.
--
------------------------------------------------------------------------
SET NOCOUNT ON
-- User Settable Options
------------------------
Declare @TargetDatabase varchar (128)
Declare @DropExistingDi agrams bit
Declare @DiagramSuffix varchar (50)
-- Initialize User Settable Options
-----------------------------------
SET @TargetDatabase = 'CABIN2'
SET @DropExistingDi agrams = 0
SET @DiagramSuffix = ' ' + Convert(varchar (23), GetDate(),121)
-------------------------------------------------------------------------
-- END OF USER MODIFIABLE SECTION - MAKE NO CHANGES TO THELOGIC BELOW
--
-------------------------------------------------------------------------
-- Setting Target database and clearing dtproperties, ifindicated
------------------------------------------------------------------
Exec('USE ' + @TargetDatabase )
IF (@DropExistingD iagrams = 1)
TRUNCATE TABLE dtproperties
-- Creating Temp Table to persist specific variables
-- between Transact SQL batches (between GO statements)
-------------------------------------------------------
IF EXISTS(SELECT 1
FROM tempdb..sysobje cts
WHERE name like '%#PersistedVar iables%'
AND xtype = 'U')
DROP TABLE #PersistedVaria bles
CREATE TABLE #PersistedVaria bles (VariableName varchar (50)NOT NULL,
VariableValue varchar (50)NOT NULL)
ON [PRIMARY]
ALTER TABLE #PersistedVaria bles ADD CONSTRAINT
PK_PersistedVar iables PRIMARY KEY CLUSTERED
(VariableName) ON [PRIMARY]
-- Persist @DiagramSuffix
-------------------------
INSERT INTO #PersistedVaria bles VALUES ('DiagramSuffix ',
@DiagramSuffix)
GO
-- Insert a new dtproperties row
--------------------------------
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (0,
'DtgSchemaOBJEC T',
null,
null,
null,
0)
DELETE #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
INSERT INTO #PersistedVaria bles VALUES ('NextObjectid' ,
Convert(varchar (15),
@@IDENTITY))
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
UPDATE dtproperties
SET Objectid = @NextObjectid
WHERE id = @NextObjectid
GO
-- Insert a new dtproperties row
--------------------------------
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (@NextObjectid,
'DtgSchemaGUID' ,
'{EA3E6268-D998-11CE-9454-00AA00A3F36E}',
'{EA3E6268-D998-11CE-9454-00AA00A3F36E}',
null,
0)
GO
-- Insert a new dtproperties row
--------------------------------
Declare @DiagramSuffix varchar (50)
SELECT @DiagramSuffix = Convert(varchar (50), VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'DiagramSuffix'
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (@NextObjectid,
'DtgSchemaNAME' ,
'CABIN Detail' + @DiagramSuffix,
'CABIN Detail'+ @DiagramSuffix,
null,
1)
GO
-- Insert a new dtproperties row
--------------------------------
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (@NextObjectid,
'DtgDSRefBYTES' ,
'2502',
'2502',
null,
12)
GO
-- Insert a new dtproperties row
--------------------------------
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVaria bles
WHERE VariableName = 'NextObjectid'
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (@NextObjectid,
'DtgDSRefDATA',
null,
null,
cast('0' as varbinary(10)),
12)
GO
---SNIP----
