Create stored procs in new DB from within a stored proc?

In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:

> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:

> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql

Create SQL Server Objects from Command Prompts

Hi

Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??

If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts

How Can I do that .. ??

And thanks with my regarding

FraasHave a look at OSQL in SQL Server BOL

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

create relationship between tables in different databases??

Hi!
I have two databases. They represent two different systems. Now the two
systems will be merged in many ways. This means that there will now be
need for relations between tables in the two databases. I have existing
database diagrams for both, but is there a way to add tables in a
diagram from another database on the same sql server' Or should I just
have all the tables in one database' Any suggestions would be
appretiated!
Thanks
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You cannot diagram over several databases, nor can you define relationships
(foreign keys) cross
database. If you want to enforce referential integrity cross database, you c
an do it using triggers.
Or, as you say, another option is to put all objects into one database. Whet
her that is the best
thing to do or not is difficult to asses over a newsgroup post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kongsballa" <kongsgballa@.devdex.com> wrote in message news:eET3QYFBFHA.1396@.tk2msftngp13.p
hx.gbl...
> Hi!
> I have two databases. They represent two different systems. Now the two
> systems will be merged in many ways. This means that there will now be
> need for relations between tables in the two databases. I have existing
> database diagrams for both, but is there a way to add tables in a
> diagram from another database on the same sql server' Or should I just
> have all the tables in one database' Any suggestions would be
> appretiated!
> Thanks
> Henning :-)
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi Tibor, and thanks for the quick response!
I guess there are a lot of pros and cons wether too have one or two
databases. The total size of the databases are no more than 500 MB, and
I would really like to see the day it reaches 1 GB. Having said that,
can you give me a hint or some place where this has been discussed'
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hej Henning,
These are small databases, so that is IMO not a factor. I'd like to say as a
rule of thumb that if
the data is related, then go for one database. One practical thing to consid
er is backup. Having
several databases mean that backup of all data will not be a snapshot in tim
e (you first do one
database, then next). I'd search the Google archives (see my web-site) as th
is topic has been
discussed here before.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kongsballa" <kongsgballa@.devdex.com> wrote in message news:uMeUPNGBFHA.3592@.TK2MSFTNGP09.p
hx.gbl...
> Hi Tibor, and thanks for the quick response!
> I guess there are a lot of pros and cons wether too have one or two
> databases. The total size of the databases are no more than 500 MB, and
> I would really like to see the day it reaches 1 GB. Having said that,
> can you give me a hint or some place where this has been discussed'
> Henning :-)
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Tjna Tibor!
Thanks again. I will look around for information on the subject. Have a
nice day in Sweden!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Create new database based on a template using SMO

Hi All,

I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.

The relevant code (so far) is:

Dim sqlServerAs New Server()With sqlServer.ConnectionContext .ServerInstance ="(local)" .Connect() .Disconnect()End WithFor Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)NextDim newDatabaseAs New Database(sqlServer, DbName.Text.ToString)newDatabase.Create()

This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?

Thanks & regards,

Paul

One general question first, will the server be running nothing but these databases? If so you may well be able to simplify the process by creating a template database within the model database. When a new database is created, it will be populated using objects in model.

The second issue is one of security as effectively sa permissions are required to create a new database. Your security concerns may be insufficient for this to be an issue, however you would be well advised to employ a level of indrection. Instead of letting the users directly trigger the create process, set up a queue table in a suitable location and have a windows service monitor this queue and create a database as required.

To find out what is required in the way of TSQL, just generate a database create script for an existing database inside (Enterprise Manager for SQL2000 and SQL Server Management Studio for SQL2005).

|||

Hi,

Thank you for your reply. I appreciate any help as I've struggled on this whole problem for a couple of days and making very little progress...

Anyway, at the moment the SQL Server is only being used for the client databases in this application, but I don't know how long that will continue to be the case.

As for the security issue, only Administrators on the Active Directory account will ultimately be able to access the page for creating databases. At the moment I'm just using site security, but will be changing this later to Active Directory.

I had already created a script file, but as this was several thousand lines, I'm rather hoping for a more manageable solution!

Thanks again,

Paul

|||Are you able to use multiple SQL Instances on that server? If so create an instance just for this application and you could use the model approach. I am glad that you have already considered security - for many applications, secuirity is an afterthought if it is thought of at all.|||

Hi,

I know that I should know, but I have no idea if I can create multiple instances of the server or not. Assuming that I can, what exactly is the model approach? How do I make fresh copies of the amended 'Model' database?

Thanks again,

Paul

create login alias DBO

Hi:

When I set up databases for my users, I create them a user and alias it as the DBO. They can then use this user in their code.

The problem is that if a user is aliased as a DBO, they can change the size of their database, create jobs, backup their database, set up replication (from their own sql server), etc...

Is there a different way to create their login (with minimal administrative effort) to give them access to create objects in their database, without allowing them access to these other tasks?

Thanksyou can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).|||Originally posted by jora
you can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).

And all objects will belong to this user: like tom.newtable. It needs to be careful - there is possibility to have couple tables with the same name but with different owners.

BOL:For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.|||I agree about the owner of the objects. As I understood the mail though (and I could be wrong offcourse) tommy only used the dbo user so the login was able to create objects. Thefore I see no problem, the user can still use his login in his/her applications.|||Thanks to all of you for your input. I love this forum, everyone is so helpful!

Ok, moving forward, may be able to use that type of login. However, I can see where it would mess up object owners.

For now, I will start by denying users to certain functions I want to lock out. For example, DENY BACKUP loginname.

Does anyone know how to deny a user from creating a job? I can not find it in BOL.|||I just remove the guest user from msdb, myself. So long as no one needs to save DTS packages there, it should work out ok.|||MCrowley, you rock! Great idea, I actually have denied access to create DTS packages on the server. One question, however, if I remove the quest user from the MSDB, will users still be able to DTS from their OWN machine? For example, they schedule a package on their own machine that pulls down updates, or whatever. Also exporting tables from their own database?

I'm going to test now.

Thanks!|||I removed the Guest login from the MSDB database, and the user (aliased as DBO of their own database), and still change the size of their database, log, and can still backup their database. It throws errors, that it can't write to the MSDB database, and the user is not a "user" in the MSDB, but it still works. It does, however, disallow them from creating jobs on the server!

Any other ideas on how to deny users changing the size of their database and backing it up? I tried:
USE DBNAME
GO
DENY BACKUP DATABASE TO LoginName
But since the login is aliased as a DBO, I get the error:
There is no such user or group LoginName.|||I just ran a test. You can take the users out of the db_owner role and put them in the db_ddladmin and db_securityadmin roles. You can even create your own special "db_owner" role that includes these for ease of administration. This will grant them the ability to create tables as dbo, but as Snail pointed out they will more than likely create tables in their own schemas only. The developers are going to have to be trained to specify the owner while creating objects. As this is possible when you add the user to the db_owner role, anyway, there should be only a few problems to start that gradually go away.
Hope this helps you in your problem.|||MCrowley, thanks for the info, I will experiement with that moving forward. I do have a bunch of databases that are already in place, and I can't make a drastic change like that right now.

Remember that these users are aliased as the DBO, they are not the DBO, sa is the DBO. I need to figure out how to stop users from backing up, restoring, and changing the size of their db.

Thanks|||In the MSDB I denied the public role access to the sp_add_job, and this made it so they can not add jobs to the server! Great!

Now, I just have to figure out how to prevent them from backing up and changing the size (or recovery model) or their database.|||Preventing them from changing the recovery model should be easy enough.
revoke exec on sp_dboption from public

This may have some uninteded consequences, as Enterprise Manager most likely uses this procedure to display information as well as change it. If your users are using EM for all their DBAdmin needs, then they will be getting all sorts of errors when the look for database properties. This may not be a problem for you,a s you are trying to restrict this stuff, anyway.

In order to prevent the backing up of databases/transaction logs, you are going to have to take these guys out of db_owner. I do not know of any other way of restricting that functionality (which, naturally does not mean a way doesn't exist...).

Create local cube from remote AS server

Hello

I try the following:

I
1) connect to a remote server and browse its AS databases and cubes
2) display one cube in a OWC 10/11 pivottable (by setting .connectionstring and .datamember
3) try to create a local cube from this server database/cube using "CREATE GLOBAL CUBE...") giving a unc path as target

Steps 1 and 2 are successful (I use a named user in the format domain\username).

Step 3 gives an error: either a connection cannot be made to server ... or AS is not running on this computer

When I try this with my local AS, 1 to 3 works fine (assumed I use localhost and integrated security)

Questions:

1) Is there any rule for user accounts to be able to create local cubes?

2) I get an error (no. -1056899072) in the sql profiler when I try step 3, but do not know how to go any further. Is there any description for this error?

Regards
Klaus Wiesel

Try to make sure the path you specify in your command is valid path for remote Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Create Duplicate SQL Server with Databases

Hi,
Is there a way to copy a SQL Server along with its databases and all other
configuration to other servers? Please keep in mind that all the other
servers are in different Active Directory/domains.
Thank you.Hi,
Thank you for your reply.
Reinstalling SQL is what I would like to avoid. I have to create SQL/Win2K
servers on a regular basis for our clients. I was trying to find a way so I
can simply ghost the server. The base configuration is the same for all our
server so the database do not change until it's been installed. Even then,
only data changes, not the DBs themselves.
Thank you.
"Stressed" <k@.c.co.uk> wrote in message
news:%23I5LeawYDHA.652@.TK2MSFTNGP10.phx.gbl...
> You may be able to install a new copy of sql server, so all the
> settings/registry keys are up to scratch on the new server, and
> then copy everything to the new server, so long as the other server
> has the same configuration and disk letters.
> I've not tried this, though, HTH.
>
> "Dragon" <nopam_baadil@.hotmail.com> wrote in message
> news:eK9VI4oYDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there a way to copy a SQL Server along with its databases and all
other
> > configuration to other servers? Please keep in mind that all the other
> > servers are in different Active Directory/domains.
> >
> > Thank you.
> >
> >
>

Create DTS with two databases

Hi,
I need to get some selected columns of two tables in TWO DATABASES , AND get those data to an excel file. Think the best way is to create a DTS. But dont know how to create it for this situation (having two databases) . Please help me to solve this problem.

ThanksNot sure if it is 2 databases on one server or two servers and need to be linked first but anyway create a view with all data you need in one databases and then use this view as a source for your data.

Good Luck.|||Hi,

Thanks a lot for your advice. Yes that method works fine.

Sudantha

Create databases in a cluster

I am trying to create a database but it failes when creating the log file.
Q:\ quorum drive
H:\data
I:\log
The message I get on the I:\
Cannot use file 'I:\Log\rentclicks_Log.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
From the cluster admin tool:
Stop the SQL server service.
Move the I: drive into the SQL resource group.
Add the new disk resource as a dependency of the SQL Service.
Start the SQL Service.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:04407CD9-0DC4-4859-9546-B1F461955ADE@.microsoft.com...
>I am trying to create a database but it failes when creating the log file.
> Q:\ quorum drive
> H:\data
> I:\log
> The message I get on the I:\
> Cannot use file 'I:\Log\rentclicks_Log.ldf' for clustered server. Only
> formatted files on which the cluster resource of the server has a
> dependency
> can be used.
> Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
|||Thanks that worked. A outside source configured the Windows Cluster so I am
learning as I go in working with clustering.
"Geoff N. Hiten" wrote:

> From the cluster admin tool:
> Stop the SQL server service.
> Move the I: drive into the SQL resource group.
> Add the new disk resource as a dependency of the SQL Service.
> Start the SQL Service.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:04407CD9-0DC4-4859-9546-B1F461955ADE@.microsoft.com...
>
>
|||Not to put too fine a point on it, but if your consultant didn't include
training, then someone screwed up. Whether it was the consultant who didn't
do the training or your company who didn't insist on it is not important.
The important thing is you have a system you cannot support. That kind of
kills the whole "high availability" reason for a cluster in the first place.
Just my opinion.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:FAE23EF2-929B-442A-98B1-C7CD191F4185@.microsoft.com...[vbcol=seagreen]
> Thanks that worked. A outside source configured the Windows Cluster so I
> am
> learning as I go in working with clustering.
> "Geoff N. Hiten" wrote:
|||The company didn't insist. Myself and the system admin were support to
closely work with Dell when implementing the Windows cluster but there was
some confusion on who was do what and the training got left to the waist
side.
So I left to train myself and seek advice from experienced cluster
professionals. Thanks for your help.
"Geoff N. Hiten" wrote:

> Not to put too fine a point on it, but if your consultant didn't include
> training, then someone screwed up. Whether it was the consultant who didn't
> do the training or your company who didn't insist on it is not important.
> The important thing is you have a system you cannot support. That kind of
> kills the whole "high availability" reason for a cluster in the first place.
> Just my opinion.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:FAE23EF2-929B-442A-98B1-C7CD191F4185@.microsoft.com...
>
>
|||Dell is notorious for plug -n- play -n- leave. Unless you contracted for
specific training classes, the implementation consultants usually don't go
out of their way to teach. Not their fault, they are under severe time
pressure to get the systems up and move on.
If you can find a place teaching the MS course 2788, Designing High
Availability Database Solutions using Microsoft SQl Server 2005, I would
advise signing up. I am very familiar with that course and it should cover
exactly what you need.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:AB66AB78-6AD8-46AB-9210-719A1C18CB17@.microsoft.com...[vbcol=seagreen]
> The company didn't insist. Myself and the system admin were support to
> closely work with Dell when implementing the Windows cluster but there was
> some confusion on who was do what and the training got left to the waist
> side.
> So I left to train myself and seek advice from experienced cluster
> professionals. Thanks for your help.
>
> "Geoff N. Hiten" wrote:

Create Database Failed - Primary file must be at least 3 MB ...

I am trying to create a database with the following command

CREATE DATABASE [db1] ON PRIMARY
( NAME = N'db1', FILENAME = N'C:\Databases\Main\db1.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'db1_log', FILENAME = N'C:\Databases\Main\db1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

However, I always get the following error:

CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.

I don't have a clue what's going wrong? The very same command is working fine on other machines. Its only one particular machine where it fails with the error message. Strangely enough, it worked on the "problem machine" too until I had to format the hard disk of the machine and re-install everything from scratch.

Any idea what could be going wrong? Please note that I am using SQL Server 2005.

On the server where this fails, verify the size of the Model database. I suspect it has inadvertently become larger than expected.

All newly created databases use the Model database as a 'template'. The new databases will start out no smaller than the Model database.

As this message indicates, since the Model database is about 3 MB in size, you MUST have a minimum size of 3 MB.

And you can reduce the size of the Model database. See Books Online, Topics: DBCC Shrinkdatabase, DBCC Shrinkfile

|||Thanks for your response. I checked the size of model database and found that it has a size of 3MB on that particular server as you said.
Actually I am creating a installation package which has to run on different machines. I am new to SQL Server so have a lesser understanding of many features of SQL Server.
Since, size of model database may just vary from machines to machines, I decided to entirely remove the SIZE=2048KB option from the CREATE DATABASE command. I tried it on my test machines and seems to work fine.
It seems that removing the SIZE option should not be a problem as system seems to pick up the size of model database on the server where is is executing.
Please correct if I am wrong or you see any negative impacts of this.
|||

You are correct, removing the [SIZE] parameter will ensure that you don't run into a similar problem.

I would suggest that immediately after the CREATE DATABASE statement, if your needs dictate a certain size to start with, that your code check the defined size and issue a ALTER DATABASE statement to increase if necessary.

|||I do not have any such requirement as of now so I might not be needing an 'alter database'
Its just that I am new to SQL server and I exported this script through SQL Server management studio. I wasn't aware that SIZE has a dependency on model database.

Anyway, thanks a lot for your suggestions.