ADO -ms-sql - xharb 1.0 - create database user schema?

[don lowenstein]

I have to create a database in MS-Sql server automatically from my install program.

Is it possible, using ADO, to issue direct native "sql-string" calls to a sql-server to create the following?
- users
- databases
-schemas?

[Manuel Valdenebro]

I am doing with Oracle + ADO (users + schemas)

[Rick Lipkin]

Don

Generally I have to get our DBA's involved to set up a database for me and set up a userid for my connection.

Once that is done .. I control the application ( users ) thru tables .. Hopefully you will be given 'create' and 'drop' rights ..

I have my own 2005 SQL server that I use as my development box .. and I control that and do everything I need with SQL Studio 2005.

Hope that helps .. I don't think you can create a database in a schima with scripts...

Rick

[nageswaragunupudi]

Once a database is created, we can create tables, indexes, even procedures through ADO. Both on Oracle and MSSQL.

One way is by sending the create scripts as commands in execute method and another way is to use ADOX.

In any case the user logged in should have create permissions.

I do not know if we can create database itself though ADO.

[Eugeniusz Owsiak]

It works for me on MS SQL server 2005

Code: Select all  Expand view

#INCLUDE "FIVEWIN.CH"
#INCLUDE "xbrowse.CH"

//----------------------
Function main()

   LOCAL oRs1,oRs2,oRs3,oErr,cSQl1,cSql2, cSQL,cTitle,oBrw,oWnd
   msgInfo("Start")
   cSQL :=  "create database jbc"
   cSql1:=   "CREATE LOGIN jbc2  WITH PASSWORD = 'hhhh'; ";
            +"USE jbc; ";
            +"CREATE USER jbc2 FOR LOGIN jbc2 "
   cSQl2   :=   "CREATE SCHEMA jbc AUTHORIZATION jbc2 ";
             +"CREATE TABLE test (source int, cost int, partnumber int) ";
             +"GRANT SELECT TO jbc2 "
   oRs1 := TOleAuto():New( "ADODB.Recordset" )
   oRs1:CursorType := 1                          // opendkeyset
   oRs1:CursorLocation := 3                      // local cache
   oRs1:LockType := 3                            // lockoportunistic
   TRY
      oRS1:Open( cSql,'Provider=SQLOLEDB;Data Source=OGNEW;Initial Catalog=;User Id=sa1;Password=qqqe' )
      CATCH oErr
      MsgInfo( "Error in creating database" )
      RETURN(.F.)
   END TRY
   oRs3 := TOleAuto():New( "ADODB.Recordset" )
   oRs3:CursorType := 1                          // opendkeyset
   oRs3:CursorLocation := 3                      // local cache
   oRs3:LockType := 3                            // lockoportunistic
   TRY
      oRs3:Open( cSql1,'Provider=SQLOLEDB;Data Source=OGNEW;Initial Catalog=;User Id=sa1;Password=qqqe' )
      CATCH oErr
      MsgInfo( "Error in creating user" )
      RETURN(.F.)
   END TRY
   
   oRs2 := TOleAuto():New( "ADODB.Recordset" )
   oRs2:CursorType := 1                          // opendkeyset
   oRs2:CursorLocation := 3                      // local cache
   oRs2:LockType := 3                            // lockoportunistic
   
   TRY
      oRs2:Open( cSql2,'Provider=SQLOLEDB;Data Source=OGNEW;Initial Catalog=jbc;User Id=sa1;Password=qqqe' )
      CATCH oErr
      MsgInfo( "Error in crating  table" )
      RETURN(.F.)
   END TRY
Return nil


That scripts generate database, user and schema

regards Eugeniusz

[nageswaragunupudi]

Mr Eugeniusz Owsiak

Thanks for the info.

[don lowenstein]

I will try the test for creating databases provided by Mr Owsiak. This may be just what I was looking for.

I would like to deliver our applications to clients with complete seamlessness for end users. Many of our end users are not very computer savy.

MS-Sql server is becoming quite popular and dependable and many small businesses (banks in my case). These clients are leary of .dbf files and want the MS-Sql database. Most of these clients don't have a DBA.

This is very useful for me - I want to deliver a solution as seamless as when I did it with .dbf files. I would package the sql-server engine with our distribution CD and build/maintain the databases 100% within code.

Most likely, I would be the only one ever using the Visual Studio / Enterprise Manager for actual administration of the end databases. Most of my users want to be insulated from these tasks.

Thanks again to all who replied.

[Perry Nichols]

Eugeniusz,

I wanted to take a moment to thank you for your examples of creating a MS-SQL database, login, user, and schema. Your examples were very helpful, and your response is greatly appreciated.

I did modularize the calls to allow calling each create individually. Also for flexibility, we capture the varaibles(sa password, database name, login...) via a dialog.

One other modification I made is adding an execute to change the login/user to a "db_owner" as follows:

MDB is the DataBase name from the dialog
MLOGIN is the MS-SQL Login from the dialog
cUSER is the DataBase User from the dialog

LOCAL cSQLCdbu := "USE " + MDB + "; " ;
+"CREATE USER "+cUSER+" FOR LOGIN "+MLOGIN+ "; " ;
+"EXEC sp_addrolemember 'db_owner', '" +cUSER+"'"

We certainly appreciate your assistance.

Regards,
Perry Nichols

[Eugeniusz Owsiak]

Mr Lowenstain
You can also from management studio
1. make setup of database, users, logins schemas etc
2. creating backap to file,
3. copying backap file to your didrtibution media
4.restoring database with this script:

Code: Select all  Expand view


#INCLUDE "FIVEWIN.CH"

//----------------------
Function main()

   LOCAL oRs1,oErr,cSQL
   msgInfo("Start")

   cSQL:="RESTORE DATABASE jbc ";
      +"FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\jbc.bak' "

   oRs1 := TOleAuto():New( "ADODB.Recordset" )
   oRs1:CursorType := 1                          // opendkeyset
   oRs1:CursorLocation := 3                      // local cache
   oRs1:LockType := 3                            // lockoportunistic
   TRY
      oRS1:Open( cSql,'Provider=SQLOLEDB;Data Source=OGNEW;Initial Catalog=;User Id=sa1;Password='qqqe' )
      CATCH oErr
      MsgInfo( "Error in restoring database" )
   END TRY
Return nil


This is easiest way to solve your problem

regards Eugeniusz