Hi all :)
My apologies if I posted in the wrong groups, but I just jumped
in MS SQL waters, so any guidance will be appreciated.
What I'm trying to do is the following process:
[1] present operator with a web page (script)
[2] once filled with db name and username, script will create
..sql file
[3] osql.exe will be called with, i presume, -i file.sql and
create a database
I have limited SQL knowledge, but I got the information from
Enterprise Manager when I ran 'All Tasks -Generate SQL
Script' on how the .sql file should look like.
I realized what are the commands that would create a fresh DB
(I ran this for newly created DB), and figured my .php script
should create such a file.
It's fairly basic, and I'm almost sure all of you know how
outputed .sql file looks like, but anyway here it is:
Script is called with parameters 'six' as database name and
'magarac' as user name:
---------------------------------------------------------------
IF EXISTS (SELECT name FROM master.dbo.sysd atabases WHERE name
= N'six')
DROP DATABASE [six]
GO
CREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:
\Databasepath\s ix_Data.MDF' , SIZE = 1, MAXSIZE = 20,
FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:
\Databasepath\s ix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH
= 10%)
COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
GO
exec sp_dboption N'six', N'autoclose', N'false'
GO
....
use [six]
GO
if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbacces s N'guest'
GO
if not exists (select * from dbo.sysusers where name =
N'sinisam')
EXEC sp_grantdbacces s N'magarac', N'magarac'
GO
exec sp_addrolemembe r N'db_owner', N'magarac'
GO
---------------------------------------------------------------
I managed to get an exact replica of .sql file that Enterprise
Manager created. It leads me to believe that this way of
automated database creation is indeed possible.
Really sorry for making you to go through all this text, but
after I get a green light on this from you guys, I'll have a
bit more problematic question.
Is there any reason why this should not be used, or would fail?
Thanks in advance :)
P.S.
Just as a heads-up, next part of my problem is automated
creation of new MS SQL server login to use with new DB. 3 2024
Limunski Magarac (li************ **@yahoo.com) writes:
CREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:
\Databasepath\s ix_Data.MDF' , SIZE = 1, MAXSIZE = 20,
FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:
\Databasepath\s ix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH = 10%)
COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
GO
The SIZE and MAXSIZE values, from where did you get these? Out of
thin air? Or is there is a limit of 20 MB in the terms of service?
20 MB is quite small size. Not the least for the log file. And if
20 MB is indeed the size, there is little reason to fiddle with
autogrowth - create it to 20 MB directly.
Also, I would question the choice of collation. Given your name and
that you appear from post from Germany, I think you should pick a
different collation.
exec sp_dboption N'six', N'autoclose', N'false'
GO
Good!
if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbacces s N'guest'
GO
Eh? Any particular reason you enable guest? Particularly at a web
host, I would not recommend this.
if not exists (select * from dbo.sysusers where name =
N'sinisam')
EXEC sp_grantdbacces s N'magarac', N'magarac'
GO
exec sp_addrolemembe r N'db_owner', N'magarac'
GO
Hm, wouldn't be better to make the user the owner of the database?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
-Erland Sommarskog ->
Hi Erland,
Thank you for your reply.
Apologies for not responding sooner, but I had a lot on my mind
during weekend time.
The SIZE and MAXSIZE values, from where did you get these? Out of
thin air? Or is there is a limit of 20 MB in the terms of service?
20 MB is quite small size. Not the least for the log file. And if
20 MB is indeed the size, there is little reason to fiddle with
autogrowth - create it to 20 MB directly.
Actually, I created one database just the way I usually do and
then started 'All Tasks -Generate SQL Script' to see how .sql
file looks for the newly created databases.
20 MB is just a default size. Many times it's not needed to
increase the size, but if it's necessary, I can always do it
manually.
Also, I would question the choice of collation. Given your name and
that you appear from post from Germany, I think you should pick a
different collation.
It is the correct one, but I'm glad you noticed :)
if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbacces s N'guest'
GO
Eh? Any particular reason you enable guest? Particularly at a web
host, I would not recommend this.
Access is granted via group membership. It has 'public' role
membership. But I'll look into this further, thanks for the
heads-up.
exec sp_addrolemembe r N'db_owner', N'magarac'
GO
Hm, wouldn't be better to make the user the owner of the database?
Isn't this the exact thing this line does? Sets the user as the
owner?
So in the end, I guess this can be used to script a database
creation?
When the script is called with DBname and username, it should
create the database and a new SQL server login (and set default
database for this login to DBname).
I'm afraid I really do not know how to script a creation of a
new login. I've managed to go this far with scripted creation
of database, and with your help I went even further.
But if you have any advice on adding new SQL logins, link or
any other hint, I'd be more than happy to do more researching.
Thank you for your time,
Six
-Erland Sommarskog ->
I have no words to thank you!
I think I'm gonna stalk you now and see if there's anything you
need help with :)
Seriously, thanks for pointing out stuff I took for granted,
too.
Much appreciated,
Six This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dim St Thomas |
last post by:
I am a developer working on a database client program. I am testing this
program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk)
This machine has Oracle 9.2.0.1.0 and RedBrick database software installed.
I am testing the software by creating small test databases.
If I create an Oracle database using the Database Configuration Assistant,
it takes forever just to create the database. If I choose to create a
new database...
|
by: Warren Wright |
last post by:
I have production SQL Server database that must be moved to a new
machine. There is a fairly complex DTS package on the original server
that is used to handle the weekly updates to the database.
Is there a way to export this DTS package in order to set it up on the
new machine as well?
Best Regards,
Warren Wright
|
by: Bernd Koehler |
last post by:
Hi:
I am a EE prof currently teaching an IT course.
One the assignments students recently completed was designing a small
MS Access Database.
I have two submissions that are remarkably similiar..<sigh> the
structure (number of tables, field names, table names) is virtually
identical. Some of the data in each table is different.
|
by: Christian Nunciato |
last post by:
Hi there:
I'm a developer, and one of my clients operates a small Web site that with a
simple contact form, written in ASP.NET, that posts back to itself and saves
data to a database. The site actually consists of only one page displaying
product information, with a link to complete the contact form, which pops up
in a Javascript-controlled window.
Up until a couple of weeks ago, the database received a steady stream of
submissions,...
|
by: James Radke |
last post by:
Hello,
I have a multithreaded windows NT service application (vb.net 2003) that I
am working on (my first one), which reads a message queue and creates
multiple threads to perform the processing for long running reports. When
the processing is complete it uses crystal reports to load a template file,
populate it, and then export it to a PDF.
It works fine so far....
| |
by: mark2kay |
last post by:
the assignment was to make list of 7 days starting with curent only
that red was supposed to be in red. ive creted code only problem is its
automatically makes 1st day red, which would be ok if day is sunday but
6 days out of week assignment will be wrong, heres code.
"<html><title>Marks JS</title>
<body> <font size = "4">
<script language = "JavaScript">
|
by: shsandeep |
last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development
database while it took 2 hours to load in a UAT database.
UAT db is partitioned.
Dev db is not partitioned.
the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.
The table is pretty large, around 6.5 million rows.
|
by: rcamarda |
last post by:
I have SQL Server 2005 x64. I found in the studio I can generate all
sorts of scripts, including the creation of indexes.
I works, however the script contains the CREATE TABLE statements as
well. I want the scripts to recreate the scripts on another filegroup
(testing a theory from previous post).
Am I missing a switch in the wizard to exclude CREATE TABLE?
I did create the script, but then I manually deleted all the CREATE
TABLE...
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |