Create database from client. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Create database from client.

Author

Topic

kennon2000
Starting Member

7 Posts

Posted - 2003-04-12 : 12:08:31

Dear all,
What is the normal way to create a SQL database in server just by pressing a button in the client? I am using c#.
If I am working in the server, I can use "OSQL -E -i CreateDB.sql" because CreateDB.sql can be easily obtained. However, how can I prevent the command mode window from coming out? Instead, I want is a progress bar in my program.

Thanks a lot.
Ken

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-12 : 12:37:12

Not sure why you are doing this, but can't you just have the client execute a command of:

CREATE DATABASE name

on the server using an ADO connection? Then, just re-connect to that new database?

Again, I don't know if I'd want my clients creating databases (tables or other objects are bad enough, but entire databases!?) on the SQL server .... but if it's part of an install routine or something that might make some sense I guess.

- Jeff

kennon2000
Starting Member

7 Posts

Posted - 2003-04-12 : 23:54:13

Do you means I have to run the T-SQL commands one by one using commandtext in ADO.NET command object? My command are:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_checkAccountDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_checkAccountDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Account]
GO

CREATE TABLE [dbo].[Account] (
[AccountID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountRef] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountClass] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsDebit] [bit] NULL ,
[IsBankAccount] [bit] NULL ,
[BeginningBalance] [money] NULL ,
[CurrencyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LatestEditBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_checkAccountDetail
(
@TermMonth [int],
@TermYear [int]
)

AS

SELECT COUNT(*)
FROM AccountDetail
WHERE (TermMonth = @TermMonth) AND (TermYear=@TermYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Can these command be executed in commandtext? By the way, some command like @@Identity, If...Else..etc cannot work in commandtext. So, is there any definition that what kind of command is supported in commandtext?
The creation of database is limited for administrator only.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-13 : 02:56:25

Taking a look at your T-SQL, it appears that you are installing the database objects. Why would you want to do this from the client side?

Tara

kennon2000
Starting Member

7 Posts

Posted - 2003-04-14 : 20:50:47

Why you keep asking me the purpose of my script instead of answering the question? If you don't know the answer, please don't block the thread.

Ken

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-14 : 21:17:46

Replace "GO" with a semicolon ";" and execute the entire string through the ADO.NET Command

DavidM

"SQL-3 is an abomination.."