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?
I am doing with Oracle + ADO (users + schemas)
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
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.
#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 nilMr Eugeniusz Owsiak
Thanks for the info.
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.
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
#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