Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

script to add users

Hi I have to add a lot of users to sql server with datareader access I have
created the script below but this does not work do you know what I would hav
e
to cahnge to get it working?
thanks for any help
Sammy
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.SQLString =
N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
use ''@.db''
EXEC sp_grantdbaccess ''@.user'',''@.user''
EXEC sp_addrolemember ''db_datareader'',''@.user''
'
SET @.user = 'clairep'
set @.pwd = 'london'
set @.db= 'pubs'Try this
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.user = 'clairep'
set @.pwd = 'london'
set @.db= 'pubs'
SET @.SQLString = 'EXEC sp_addlogin ' + @.user + ', ' + @.pwd + ', ' + @.db
SET @.SQLString = @.SQLString + ' USE ' + @.db
SET @.SQLString = @.SQLString + ' EXEC sp_grantdbaccess ' + @.user + ', ' + @.us
er
SET @.SQLString = @.SQLString + ' EXEC sp_addrolemember db_datareader, ' + @.us
er
EXEC (@.SQLString)
Andy
"Sammy" wrote:

> Hi I have to add a lot of users to sql server with datareader access I hav
e
> created the script below but this does not work do you know what I would h
ave
> to cahnge to get it working?
> thanks for any help
> Sammy
>
> DECLARE @.user varchar (8)
> DECLARE @.SQLString NVARCHAR(500)
> DECLARE @.pwd NVARCHAR(8)
> DECLARE @.db NVARCHAR(10)
>
> SET @.SQLString =
> N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
> use ''@.db''
> EXEC sp_grantdbaccess ''@.user'',''@.user''
> EXEC sp_addrolemember ''db_datareader'',''@.user''
> '
> SET @.user = 'clairep'
> set @.pwd = 'london'
> set @.db= 'pubs'|||Try,
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.user = N'clairep'
set @.pwd = N'london'
set @.db= N'pubs'
SET @.SQLString = N'use master' + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_addlogin ''' + @.user + N''', ''' +
@.pwd + N''', ''' + @.db + N'''' + char(13)
SET @.SQLString = @.SQLString + N'use ' + quotename(@.db) + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_grantdbaccess ''' + @.user + N''','''
+ @.user + N'''' + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_addrolemember ''db_datareader'','''
+ @.user + N''''
execute sp_executesql @.SQLString
go
AMB
"Sammy" wrote:

> Hi I have to add a lot of users to sql server with datareader access I hav
e
> created the script below but this does not work do you know what I would h
ave
> to cahnge to get it working?
> thanks for any help
> Sammy
>
> DECLARE @.user varchar (8)
> DECLARE @.SQLString NVARCHAR(500)
> DECLARE @.pwd NVARCHAR(8)
> DECLARE @.db NVARCHAR(10)
>
> SET @.SQLString =
> N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
> use ''@.db''
> EXEC sp_grantdbaccess ''@.user'',''@.user''
> EXEC sp_addrolemember ''db_datareader'',''@.user''
> '
> SET @.user = 'clairep'
> set @.pwd = 'london'
> set @.db= 'pubs'

Script Question

Is there any easy way to create a change script as illustrated below for all tables within a database?

Right now I would have to create a seperate script for each table.

I would like to be able to update the customers database while preserving their exisiting data.

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

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Deposits
(
DEPSYSID int NOT NULL IDENTITY (1, 1),
DEPBANKID int NULL,
DEPUSER varchar(50) NULL,
DEPSTATUS tinyint NULL,
CHECKORCASH varchar(10) NULL,
CHECKNUMBER varchar(10) NULL,
DEPOSITSOURCE varchar(50) NULL,
DEPAMOUNT decimal(9, 2) NULL,
CASHDRAWERID int NULL,
DEPOSITDATE datetime NULL,
ACTUALDEPOSITUSER varchar(50) NULL,
ACTUALDEPOSITDATE datetime NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Deposits ON
GO
IF EXISTS(SELECT * FROM dbo.Deposits)
EXEC('INSERT INTO dbo.Tmp_Deposits (DEPSYSID, DEPBANKID, DEPUSER, DEPSTATUS, CHECKORCASH, CHECKNUMBER, DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID, DEPOSITDATE, ACTUALDEPOSITUSER, ACTUALDEPOSITDATE)
SELECT DEPSYSID, DEPBANKID, DEPUSER, DEPSTATUS, CHECKORCASH, CHECKNUMBER, DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID, DEPOSITDATE, ACTUALDEPOSITUSER, ACTUALDEPOSITDATE FROM dbo.Deposits TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Deposits OFF
GO
DROP TABLE dbo.Deposits
GO
EXECUTE sp_rename N'dbo.Tmp_Deposits', N'Deposits', 'OBJECT'
GO
ALTER TABLE dbo.Deposits ADD CONSTRAINT
DEP_SYSID PRIMARY KEY CLUSTERED
(
DEPSYSID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX DEP_STATUS ON dbo.Deposits
(
DEPSTATUS
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX DEP_Date ON dbo.Deposits
(
DEPOSITDATE
) ON [PRIMARY]
GO
COMMIT

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

--
Tim Morrison

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

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comHi

In general this task would not be needed if you kept the database objects in
a source code control system. You can do it by comparing two different
databases (pre changes and post changes) and compare the system schemas
using three part naming convensions and cursors.
Alternatively get something like red gate compare http://www.red-gate.com to
do it for you. This question does get asked quite frequently in the news
groups so you may want to check Google for other solutions.

It may also be possible to do this from VISIO if you are modelling the
database.

John

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:dFALb.7634$xy6.17977@.attbi_s02...
Is there any easy way to create a change script as illustrated below for all
tables within a database?

Right now I would have to create a seperate script for each table.

I would like to be able to update the customers database while preserving
their exisiting data.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Deposits
(
DEPSYSID int NOT NULL IDENTITY (1, 1),
DEPBANKID int NULL,
DEPUSER varchar(50) NULL,
DEPSTATUS tinyint NULL,
CHECKORCASH varchar(10) NULL,
CHECKNUMBER varchar(10) NULL,
DEPOSITSOURCE varchar(50) NULL,
DEPAMOUNT decimal(9, 2) NULL,
CASHDRAWERID int NULL,
DEPOSITDATE datetime NULL,
ACTUALDEPOSITUSER varchar(50) NULL,
ACTUALDEPOSITDATE datetime NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Deposits ON
GO
IF EXISTS(SELECT * FROM dbo.Deposits)
EXEC('INSERT INTO dbo.Tmp_Deposits (DEPSYSID, DEPBANKID, DEPUSER,
DEPSTATUS, CHECKORCASH, CHECKNUMBER, DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID,
DEPOSITDATE, ACTUALDEPOSITUSER, ACTUALDEPOSITDATE)
SELECT DEPSYSID, DEPBANKID, DEPUSER, DEPSTATUS, CHECKORCASH, CHECKNUMBER,
DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID, DEPOSITDATE, ACTUALDEPOSITUSER,
ACTUALDEPOSITDATE FROM dbo.Deposits TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Deposits OFF
GO
DROP TABLE dbo.Deposits
GO
EXECUTE sp_rename N'dbo.Tmp_Deposits', N'Deposits', 'OBJECT'
GO
ALTER TABLE dbo.Deposits ADD CONSTRAINT
DEP_SYSID PRIMARY KEY CLUSTERED
(
DEPSYSID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX DEP_STATUS ON dbo.Deposits
(
DEPSTATUS
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX DEP_Date ON dbo.Deposits
(
DEPOSITDATE
) ON [PRIMARY]
GO
COMMIT

--
Tim Morrison

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

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com|||The problem is that this is a commercial application. Say my initial
database I call version 1000, then I make some changes, then version 1001
then version 1002, etc.

When I send an update, some users may have 1000, some 1001, etc.

If I have a change script for all tables, then it doesent matter what
version they have.

Tim

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:btna9g$o8$1@.sparta.btinternet.com...
> Hi
> In general this task would not be needed if you kept the database objects
in
> a source code control system. You can do it by comparing two different
> databases (pre changes and post changes) and compare the system schemas
> using three part naming convensions and cursors.
> Alternatively get something like red gate compare http://www.red-gate.com
to
> do it for you. This question does get asked quite frequently in the news
> groups so you may want to check Google for other solutions.
> It may also be possible to do this from VISIO if you are modelling the
> database.
> John
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:dFALb.7634$xy6.17977@.attbi_s02...
> Is there any easy way to create a change script as illustrated below for
all
> tables within a database?
> Right now I would have to create a seperate script for each table.
> I would like to be able to update the customers database while preserving
> their exisiting data.
>
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Deposits
> (
> DEPSYSID int NOT NULL IDENTITY (1, 1),
> DEPBANKID int NULL,
> DEPUSER varchar(50) NULL,
> DEPSTATUS tinyint NULL,
> CHECKORCASH varchar(10) NULL,
> CHECKNUMBER varchar(10) NULL,
> DEPOSITSOURCE varchar(50) NULL,
> DEPAMOUNT decimal(9, 2) NULL,
> CASHDRAWERID int NULL,
> DEPOSITDATE datetime NULL,
> ACTUALDEPOSITUSER varchar(50) NULL,
> ACTUALDEPOSITDATE datetime NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Deposits ON
> GO
> IF EXISTS(SELECT * FROM dbo.Deposits)
> EXEC('INSERT INTO dbo.Tmp_Deposits (DEPSYSID, DEPBANKID, DEPUSER,
> DEPSTATUS, CHECKORCASH, CHECKNUMBER, DEPOSITSOURCE, DEPAMOUNT,
CASHDRAWERID,
> DEPOSITDATE, ACTUALDEPOSITUSER, ACTUALDEPOSITDATE)
> SELECT DEPSYSID, DEPBANKID, DEPUSER, DEPSTATUS, CHECKORCASH,
CHECKNUMBER,
> DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID, DEPOSITDATE, ACTUALDEPOSITUSER,
> ACTUALDEPOSITDATE FROM dbo.Deposits TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Deposits OFF
> GO
> DROP TABLE dbo.Deposits
> GO
> EXECUTE sp_rename N'dbo.Tmp_Deposits', N'Deposits', 'OBJECT'
> GO
> ALTER TABLE dbo.Deposits ADD CONSTRAINT
> DEP_SYSID PRIMARY KEY CLUSTERED
> (
> DEPSYSID
> ) ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX DEP_STATUS ON dbo.Deposits
> (
> DEPSTATUS
> ) ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX DEP_Date ON dbo.Deposits
> (
> DEPOSITDATE
> ) ON [PRIMARY]
> GO
> COMMIT
>
>
> --
> Tim Morrison
> -----------------------
--
> --
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
> related website.
> http://www.vehiclewebstudio.com|||Hi

This is not the case, the script to change from version 1 to version 2 is
not necessarily the same as the script to change from version 1 to version
3. You can either have two scripts version 1 to version 2 and version 2 to
version 3, which will mean fewer scripts but potentially longer
installations. Alternatively you can have one for each combination of
versions, which will quicker installations but will soon become unmanagable.
Another alternative you can write/buy a tool/process which compares a
specific version (call it a template database) with what is currently in
production, this is fine until you want to do something that is not
"standard"! e.g split a column or table into two different ones, you will
then have resorted to the first solution!

John

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:DtGLb.9796$xy6.22683@.attbi_s02...
> The problem is that this is a commercial application. Say my initial
> database I call version 1000, then I make some changes, then version 1001
> then version 1002, etc.
> When I send an update, some users may have 1000, some 1001, etc.
> If I have a change script for all tables, then it doesent matter what
> version they have.
> Tim
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:btna9g$o8$1@.sparta.btinternet.com...
> > Hi
> > In general this task would not be needed if you kept the database
objects
> in
> > a source code control system. You can do it by comparing two different
> > databases (pre changes and post changes) and compare the system schemas
> > using three part naming convensions and cursors.
> > Alternatively get something like red gate compare
http://www.red-gate.com
> to
> > do it for you. This question does get asked quite frequently in the news
> > groups so you may want to check Google for other solutions.
> > It may also be possible to do this from VISIO if you are modelling the
> > database.
> > John
> > "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> > news:dFALb.7634$xy6.17977@.attbi_s02...
> > Is there any easy way to create a change script as illustrated below for
> all
> > tables within a database?
> > Right now I would have to create a seperate script for each table.
> > I would like to be able to update the customers database while
preserving
> > their exisiting data.
> > BEGIN TRANSACTION
> > SET QUOTED_IDENTIFIER ON
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDABORT OFF
> > SET CONCAT_NULL_YIELDS_NULL ON
> > SET ANSI_NULLS ON
> > SET ANSI_PADDING ON
> > SET ANSI_WARNINGS ON
> > COMMIT
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.Tmp_Deposits
> > (
> > DEPSYSID int NOT NULL IDENTITY (1, 1),
> > DEPBANKID int NULL,
> > DEPUSER varchar(50) NULL,
> > DEPSTATUS tinyint NULL,
> > CHECKORCASH varchar(10) NULL,
> > CHECKNUMBER varchar(10) NULL,
> > DEPOSITSOURCE varchar(50) NULL,
> > DEPAMOUNT decimal(9, 2) NULL,
> > CASHDRAWERID int NULL,
> > DEPOSITDATE datetime NULL,
> > ACTUALDEPOSITUSER varchar(50) NULL,
> > ACTUALDEPOSITDATE datetime NULL
> > ) ON [PRIMARY]
> > GO
> > SET IDENTITY_INSERT dbo.Tmp_Deposits ON
> > GO
> > IF EXISTS(SELECT * FROM dbo.Deposits)
> > EXEC('INSERT INTO dbo.Tmp_Deposits (DEPSYSID, DEPBANKID, DEPUSER,
> > DEPSTATUS, CHECKORCASH, CHECKNUMBER, DEPOSITSOURCE, DEPAMOUNT,
> CASHDRAWERID,
> > DEPOSITDATE, ACTUALDEPOSITUSER, ACTUALDEPOSITDATE)
> > SELECT DEPSYSID, DEPBANKID, DEPUSER, DEPSTATUS, CHECKORCASH,
> CHECKNUMBER,
> > DEPOSITSOURCE, DEPAMOUNT, CASHDRAWERID, DEPOSITDATE, ACTUALDEPOSITUSER,
> > ACTUALDEPOSITDATE FROM dbo.Deposits TABLOCKX')
> > GO
> > SET IDENTITY_INSERT dbo.Tmp_Deposits OFF
> > GO
> > DROP TABLE dbo.Deposits
> > GO
> > EXECUTE sp_rename N'dbo.Tmp_Deposits', N'Deposits', 'OBJECT'
> > GO
> > ALTER TABLE dbo.Deposits ADD CONSTRAINT
> > DEP_SYSID PRIMARY KEY CLUSTERED
> > (
> > DEPSYSID
> > ) ON [PRIMARY]
> > GO
> > CREATE NONCLUSTERED INDEX DEP_STATUS ON dbo.Deposits
> > (
> > DEPSTATUS
> > ) ON [PRIMARY]
> > GO
> > CREATE NONCLUSTERED INDEX DEP_Date ON dbo.Deposits
> > (
> > DEPOSITDATE
> > ) ON [PRIMARY]
> > GO
> > COMMIT
> > --
> > Tim Morrison
> -----------------------
> --
> > --
> > Vehicle Web Studio - The easiest way to create and maintain your vehicle
> > related website.
> > http://www.vehiclewebstudio.com|||Tim Morrison (sales@.kjmsoftware.com) writes:
> The problem is that this is a commercial application. Say my initial
> database I call version 1000, then I make some changes, then version 1001
> then version 1002, etc.
> When I send an update, some users may have 1000, some 1001, etc.
> If I have a change script for all tables, then it doesent matter what
> version they have.

Maybe. It depends on how wild your changes are.

I may have some experience to share, as I work in a company that develop
a commercial application, and I am responsible for data model for the
core part of the database. So I have written a few change scripts...

First, as John said, instrumental is to use a version-control system.
Since you are developing an application which you distribute commercially,
this is an absolute must.

I generate my change script with a tool, DBUPDGEN, that I have written
myself. (Available as freeware on http://www.abaris.se/abaperls/.) DBUPDGEN
reads the contents of two SourceSafe labels, and generate an update script
in Perl with instructions to read all changed objects from SourceSafe or
disk and load them in the database. You can feed DBUPDGEN an existing
script, and it will update the script. This is good if you set a new label.
DBUPDGEN still permits manual changes to the script.

For changed tables, it produces a template which works if all I've done is
to add a column which accepts NULL or a default value, but for other
changes I have to change the template. The philosophy is that a tool can
not understand all complicated changes anyway, so I found no reason to
spend time on making it smart.

I should add that the template works by renaming the old table, creating
the new table and then copy the data. Partly, this is due to legacy; the
tool is from SQL 6.0 days, but also I think column order is important when
I look at SELECT * queries in Query Analyzer.

The tool uses its internal system tables, so that it can verify that an
update script is permissible. If you try to load a script that is supposed
to upgrade the database from 7.10.0001 to 7.10.0212, but the current
database is at 6.20.0230 you will get an error.

As for the case that a customer may need to move several versions at
one time, we don't have any handling for this. If a customer moves from
6.20 to 7.30, we will have to run the update scripts for 7.10, 7.20 and
7.30, even if this means reloading the same big table more than once.

Leaving my own tool behind, there are probably quite a few third-party
tools out there, that can generate update scripts for. John mentioned SQL
Compare, which works from databases. I would expect that all the major
data-modelling tools - PowerDesigner, ErWin and Embrocadero - has such a
feature. (I use PowerDesigner, and I know that it has this feature,
although I don't use it.)

Lately, I have seen several promotional postings for a couple of
version-control systems, specifically aimed at SQL stuff. Some are
probably add-ons to Visual SourceSafe (Microsoft's low-budget alternative
to version-control), while others may use their own database. I don't
have any names handy, but a Google search should find a few.

Whichever way you go, you will need to invest some time to learn the
tool you choose, and to understand the process. Configuration Management
is no easy task to get started with, but once you have a robust process,
you are on safe ground.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the post...

In my case, the changes are not drastic. In most cases, the SQL database is
automatically updated via the development environment I use (Clarion) which
handles MOST changes, like adding tables, modifying fields (CHAR(10) to
CHAR(11), etc).

The reason this came up is I had a table that did not have the IDENTITY
parameter set on my PK, which is one issue that is not automatically set. So
the idea of using change scripts came to mind. This way I can send one
script that will both create the tables if they dont exist and update the
tables if they do exist.

Tim Morrison

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns946D1FE6C8DAYazorman@.127.0.0.1...
> Tim Morrison (sales@.kjmsoftware.com) writes:
> > The problem is that this is a commercial application. Say my initial
> > database I call version 1000, then I make some changes, then version
1001
> > then version 1002, etc.
> > When I send an update, some users may have 1000, some 1001, etc.
> > If I have a change script for all tables, then it doesent matter what
> > version they have.
> Maybe. It depends on how wild your changes are.
> I may have some experience to share, as I work in a company that develop
> a commercial application, and I am responsible for data model for the
> core part of the database. So I have written a few change scripts...
> First, as John said, instrumental is to use a version-control system.
> Since you are developing an application which you distribute commercially,
> this is an absolute must.
> I generate my change script with a tool, DBUPDGEN, that I have written
> myself. (Available as freeware on http://www.abaris.se/abaperls/.)
DBUPDGEN
> reads the contents of two SourceSafe labels, and generate an update script
> in Perl with instructions to read all changed objects from SourceSafe or
> disk and load them in the database. You can feed DBUPDGEN an existing
> script, and it will update the script. This is good if you set a new
label.
> DBUPDGEN still permits manual changes to the script.
> For changed tables, it produces a template which works if all I've done is
> to add a column which accepts NULL or a default value, but for other
> changes I have to change the template. The philosophy is that a tool can
> not understand all complicated changes anyway, so I found no reason to
> spend time on making it smart.
> I should add that the template works by renaming the old table, creating
> the new table and then copy the data. Partly, this is due to legacy; the
> tool is from SQL 6.0 days, but also I think column order is important when
> I look at SELECT * queries in Query Analyzer.
> The tool uses its internal system tables, so that it can verify that an
> update script is permissible. If you try to load a script that is supposed
> to upgrade the database from 7.10.0001 to 7.10.0212, but the current
> database is at 6.20.0230 you will get an error.
> As for the case that a customer may need to move several versions at
> one time, we don't have any handling for this. If a customer moves from
> 6.20 to 7.30, we will have to run the update scripts for 7.10, 7.20 and
> 7.30, even if this means reloading the same big table more than once.
> Leaving my own tool behind, there are probably quite a few third-party
> tools out there, that can generate update scripts for. John mentioned SQL
> Compare, which works from databases. I would expect that all the major
> data-modelling tools - PowerDesigner, ErWin and Embrocadero - has such a
> feature. (I use PowerDesigner, and I know that it has this feature,
> although I don't use it.)
> Lately, I have seen several promotional postings for a couple of
> version-control systems, specifically aimed at SQL stuff. Some are
> probably add-ons to Visual SourceSafe (Microsoft's low-budget alternative
> to version-control), while others may use their own database. I don't
> have any names handy, but a Google search should find a few.
> Whichever way you go, you will need to invest some time to learn the
> tool you choose, and to understand the process. Configuration Management
> is no easy task to get started with, but once you have a robust process,
> you are on safe ground.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Script

Hi All,

When I run the script below I get the error "Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#table'".

CREATE TABLE #table (Check_Log VARCHAR(1000), Log_Time datetime default GetDate())

INSERT #table(Check_Log)
EXEC master..xp_cmdshell 'osql -S server -U user -P password -d db -Q"DBCC CHECKDB"'

EXEC master..xp_cmdshell 'bcp dbname.user.#table out Z:\Test\CheckDBRes.txt -S server -U user -P password'

SELECT * FROM #table

IF EXISTS (SELECT * FROM #table
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')

PRINT 'No errors'

ELSE

RETURN

DROP TABLE #table

Can you please tell what I am doing wrong?

ThanksDidn't we do this laready?

All you had to do was curt and paste my origina;|||This is a little bit different. Your script doesn't put the results of checkdb into the text file. But this is one of the requirements.|||You will need to make this a permanent table

Also, make sure you put the batch column back in|||What is the purpose of the batch column?|||So that multiple processes can use trhe samme table, you can retain all of the logs, and then only ftp out 1 "batch" dbcc process at a time.

Trust me it will make life a lot easier

http://www.dbforums.com/showthread.php?t=1612711|||Thanks for your help.

Scoped Calculations and Aggregate

I have a measure which is defined as 0 and then Scope'd for certain dimensions to be a percent of the total as below:

CREATE MEMBER [Measures].[Revenue Percent] AS 0;

SCOPE ([Measures].[Revenue Percent]);

THIS = ([Measures].[Gross Revenue]) / ([Measures].[Gross Revenue], [Area].[Sales Area].[All], [Direct Response].[DRS].[All])

SCOPE (Descendants([Area].[Sales Area].&[2], 1, SELF_AND_AFTER));

THIS = ([Measures].[Gross Revenue])

/ ([Measures].[Gross Revenue], [Area].[Sales Area].&[2], [Direct Response].[DRS].[All]);

END SCOPE;

END SCOPE;

This calculation works fine when I am browsing the cube as I would expect. I can create calculated members on my date dimension and the measure is calculated correctly using Aggregate({date1, date2, date3, etc}) function. However as soon as I change this calculation to use "relative" sets of members it simply returns 0.

So if I have the calcuation,

MEMEBER [Measures].[YTD Revenue Percent] AS AGGREGATE(YTD(date4), [Measures].[Revenue Percent])

it will always return 0. Even if I create the YTD portion as a set first and then aggregate that it still returns 0. However change the ytd(Date4) to something like {date1ate4} and the calculation will work.

Has anyone else seen this sort of behaviour, or can point me in the direction of information as to why it doesn't work?

Thanks in advance

Mike

PS: The date dimension is correctly figured and the member names date1 etc are just syntax for shortening the full date in the post.

{date1ate4} Should read {date1 : date4} - Sorry just seen that once it is posted.