ISQL script for Creating user, database and tables

Question

ISQL script for Creating user, database and tables

msregalla

Say Hey Kid

Points: 712
More actions
January 21, 2002 at 11:23 pm

#158147

Dear All,
I am writing a script for SQL6.5 for auto generating a database and a user for that database and few tables within the database. This is for packaging and distribution purposes of my application related database to the clients.
I am struck at changing the user from 'sa' to the newly created user (say 'sharan') so that i can create a database and table under him.
In Oracle I can use a command in SQL script file for this as "connect sharan/abcd123" but how to do in SQL server?
Presently, I am changing the ownership from SA to sharan after creating the database and tables.But, still the owner would be DBA not sharan for database and tables.
Or are there any other alternatives for this? than what I am doing for packaging and distribution????
Can you please help me? Thanks in advance.
Thanx n Rds

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

what is your script like?

I would use isql, which is what the SQL Setup uses. In this you can specify the user to connect with using the -U option. I would create the user and db using sa with a single script. Then in a second script create the tables (connecting as sharen).

Steve Jones

steve@dkranch.net

msregalla Say Hey Kid Points: 712 More actions · Answer 2

Thanx Steve,

I could solve the problem using SETUSER command within a single script for creating user as well as database and tables for that user.

The script is like this

---- SCRIPT starts here --

GO

set nocount on

set dateformat mdy

GO

declare @dttm varchar(55)

select @dttm=convert(varchar,getdate(),113)

raiserror('Beginning eCleanse.SQL at %s ....',1,1,@dttm) with nowait

GO

if exists (select * from sysdatabases where name='ecleanse')

DROP database ecleanse

GO

CHECKPOINT

GO

CREATE DATABASE ecleanse

on master = 10

GO

CHECKPOINT

GO

sp_addlogin 'ecleanse','abcd123','ecleanse'

GO

--sp_adduser 'ecleanse','dbo'

--GO

use ecleanse

GO

sp_changedbowner ecleanse

GO

SETUSER "ecleanse" WITH NORESET

GO

if db_name() = 'ecleanse'

raiserror('''ecleanse'' database created, and context now in use.',1,1)

else

raiserror('Error in eCleanse.SQL, ''USE ecleanse'' failed! Killing the SPID now.'

,22,127) with log

GO

execute sp_dboption 'ecleanse' ,'trunc. log on chkpt.' ,'true'

execute sp_dboption 'ecleanse','select into/bulkcopy','true'

GO

raiserror('Now at the create table section ....',1,1)

--- Tables creation starts here ---

Now that problem is solved.

But, when I am trying to query the same database from my VB application with ADO connection I am getting the following error:

I am getting one more error message like:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cursor open failed because the size of the keyset row exceeded maximum allowed row size.

The statement I am using like this:

---------------------------------------------

table_name="myTable"

If rsRecords.State = adStateOpen Then rsRecords.Close

rsRecords.Open "select * from " & table_name, con, adOpenStatic, adLockOptimistic, adCmdText

MsgBox Err.Description

If rsRecords.RecordCount <> 0 Then

If (MsgBox(table_name & " Already contains " & rsRecords.RecordCount & " Records! Add to existing Records??", vbYesNo, "Confirm") = vbYes) Then

insert_data

Else

exit sub

End if

--------------------------------------------

I tried with other connection types than Static one for those I am getting '-1' in the recordcount. I should get the positive or zero recorcount for checking.

Is there any possible way or alternative??

Thanx in advance

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Can you change the select * to a select with specific column names.

Steve Jones

steve@dkranch.net

msregalla Say Hey Kid Points: 712 More actions · Answer 4

I could solve the problem by specifying the cursor location before opening the recordset in combination with ForwardOnly.

But, still I could not get why the hell that error comes????????? Any Ideas???

Solution code is like following: