Page 1 of 1
ADO -ms-sql - xharb 1.0 - create database user schema?
Posted: Mon Feb 25, 2008 4:27 pm
by 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?
Posted: Mon Feb 25, 2008 5:15 pm
by Manuel Valdenebro
I am doing with Oracle + ADO (users + schemas)
Posted: Tue Feb 26, 2008 1:23 am
by 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
Posted: Tue Feb 26, 2008 6:27 am
by 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.
Posted: Tue Feb 26, 2008 9:38 am
by Eugeniusz Owsiak
It works for me on MS SQL server 2005
Code: Select all
#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
Posted: Tue Feb 26, 2008 1:07 pm
by nageswaragunupudi
Mr Eugeniusz Owsiak
Thanks for the info.
Posted: Tue Feb 26, 2008 2:06 pm
by 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.
Thank you for your Help
Posted: Fri Feb 29, 2008 3:33 pm
by 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
Posted: Sat Mar 01, 2008 11:31 am
by 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
#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