469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Script for Transfering Database Diagram

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
#PersistedVariables.

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 definedimmediately
-- 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.
--
-- @DropExistingDiagrams 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 @DropExistingDiagrams bit
Declare @DiagramSuffix varchar (50)

-- Initialize User Settable Options
-----------------------------------
SET @TargetDatabase = 'CABIN2'
SET @DropExistingDiagrams = 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 (@DropExistingDiagrams = 1)
TRUNCATE TABLE dtproperties
-- Creating Temp Table to persist specific variables
-- between Transact SQL batches (between GO statements)
-------------------------------------------------------
IF EXISTS(SELECT 1
FROM tempdb..sysobjects
WHERE name like '%#PersistedVariables%'
AND xtype = 'U')
DROP TABLE #PersistedVariables
CREATE TABLE #PersistedVariables (VariableName varchar (50)NOT NULL,
VariableValue varchar (50)NOT NULL)
ON [PRIMARY]
ALTER TABLE #PersistedVariables ADD CONSTRAINT
PK_PersistedVariables PRIMARY KEY CLUSTERED
(VariableName) ON [PRIMARY]
-- Persist @DiagramSuffix
-------------------------
INSERT INTO #PersistedVariables VALUES ('DiagramSuffix',
@DiagramSuffix)
GO
-- Insert a new dtproperties row
--------------------------------
INSERT INTO dtproperties (objectid,
property,
value,
uvalue,
lvalue,
version)
VALUES (0,
'DtgSchemaOBJECT',
null,
null,
null,
0)
DELETE #PersistedVariables
WHERE VariableName = 'NextObjectid'
INSERT INTO #PersistedVariables VALUES ('NextObjectid',
Convert(varchar(15),
@@IDENTITY))
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVariables
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 #PersistedVariables
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 #PersistedVariables
WHERE VariableName = 'DiagramSuffix'
Declare @NextObjectid int
SELECT @NextObjectid = Convert(int, VariableValue)
FROM #PersistedVariables
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 #PersistedVariables
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 #PersistedVariables
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----
Jul 20 '05 #1
1 3375

"Tim Pascoe" <ti********@cciw.ca> wrote in message
news:19**************************@posting.google.c om...
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
#PersistedVariables.

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


<snip>

I'm not familiar with this code, but it appears to start out as a procedure,
and then turns into a series of TSQL batches. When the first GO command is
reached, this executes the CREATE PROCEDURE statement with the code up to
that point as the procedure text. Subsequent sections between GO commands
are executed as independent batches, however since the #PersistedVariables
table is created in the procedure, not in a batch, it doesn't exist when the
batches try to find it.

I can't really say more than that, as I don't know how this procedure is
intended to be used - you may want to review the original posting again. By
the way, a procedure name with a space in it is probably a bad idea, as many
tools (and many human beings) don't always handle that very well.

Simon
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Ian | last post: by
3 posts views Thread by Nolan Madson | last post: by
9 posts views Thread by Wayne Wengert | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.