Osql Usage with MSDE

Question

Post reply

Osql Usage with MSDE

Dave L. Clark I

SSC Veteran

Points: 256
More actions
December 27, 2005 at 11:56 am

#89092

I've worked with MS Access databases for years and am only now turning to SQL Server. To that end, I've installed MSDE and I have a little Osql script for recreating an empty database whenever I need to restart from scratch. However, once I get some sample tables created in this database, via ADO, I get unexplainable errors when I rerun my script. Unexplainable, that is, unless my first USE statement is not being completely honored. Help?
[script]
USE NEWTdatabase;
DROP TABLE Software;
DROP TABLE OperatingSystem;
DROP TABLE System_MainTable;
REVOKE ALL FROM xxxxxxxx;
EXEC sp_revokedbaccess 'xxxxxxxx';
USE master;
DROP DATABASE NEWTdatabase;
EXEC sp_droplogin 'xxxxxxxx';
EXEC sp_addlogin 'xxxxxxxx','xxxxxxxx';
CREATE DATABASE NEWTdatabase;
USE NEWTdatabase;
EXEC sp_grantdbaccess 'xxxxxxxx';
GRANT ALL TO xxxxxxxx;
[/script]
[result]
C:\>osql -E -i "C:\Documents and Settings\Dave\My Documents\Dave's\MS_VisualBasic\NEWTdatabase\Osql.sql"
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16>
Msg 3701, Level 11, State 5, Server DIMENSION2350, Line 2
Cannot drop the table 'Software', because it does not exist in the system catalog.
Msg 3701, Level 11, State 5, Server DIMENSION2350, Line 3
Cannot drop the table 'OperatingSystem', because it does not exist in the system catalog.
Msg 3701, Level 11, State 5, Server DIMENSION2350, Line 4
Cannot drop the table 'System_MainTable', because it does not exist in the system catalog.
Msg 15183, Level 16, State 1, Server DIMENSION2350, Procedure sp_MScheck_uid_owns_anything, Line 17
The user owns objects in the database and cannot be dropped.
name                                                                                               type
-------------------------------------------------------------------------------------------------- ----
System_MainTable                                                                                   U
OperatingSystem                                                                                    U
Software                                                                                           U
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\NEWTdatabase_log.LDF'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\NEWTdatabase.mdf'.
Login dropped.
New login created.
The CREATE DATABASE process is allocating 0.63 MB on disk 'NEWTdatabase'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'NEWTdatabase_log'.
Granted database access to 'xxxxxxxx'.
[/result]
Also, I'm getting an error message (when attempting to create a particular table) to the effect that the row length is too long for the allocated database. How do I change this allocation?
Thanks,
— Dave

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

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

USE master

IF DB_ID('NEWTdatabase') > 0

DROP DATABASE NEWTdatabase

IF EXISTS (SELECT loginname FROM syslogins

WHERE loginname = 'xxxxxxxx')

EXEC sp_droplogin 'xxxxxxxx'

GO

IF NOT EXISTS (SELECT loginname FROM syslogins

WHERE loginname = 'xxxxxxxx')

EXEC sp_addlogin 'xxxxxxxx','xxxxxxxx'

CREATE DATABASE NEWTdatabase

USE NEWTdatabase

EXEC sp_grantdbaccess 'xxxxxxxx'

GRANT ALL TO xxxxxxxx AS dbo

GO

Is all you need, no need to drop objects and then drop the database. Note the error handling by not dropping or creating an object if it does/does not exist. GO excutes the batch read so far, so the database is dropped and then created in seperate batches, execution of a single batch fails as the database exists when the DROP is read. Also could the problem be that you are not using the same -E account that originally created the database, or does not have the sysadmin server role?

SQL Server 2000 has a table column width limit of 8060 for all columns (less overhead), so if you are creating the table and seeing the error, then split the table into 2 and relate them.

Andy

Dave L. Clark I SSC Veteran Points: 256 More actions · Answer 2

OK, I understand that the revised script presented is the best arrangement for the circumstance of recreating a database from scratch. Thank you for that.

However, would someone please address the issue of being able to drop an existing table from a database -- as I was trying to do? It was the same -E account (system administrator) every time this script was executed. However, the tables were created using ADO connection string logon -- not Windows security logon. The connection string logon utilized the SQL Server user id and password as they were set up by this script.

Thanks,

— Dave

Dave L. Clark I SSC Veteran Points: 256 More actions · Answer 3

Also... Note that I had to add another GO immediately after the CREATE DATABASE statement -- because the following USE statement was failing to find the database. I went with the following:

[script]

PRINT ' '

USE master

PRINT 'Dropping database...'

IF DB_ID('NEWTdatabase') > 0

DROP DATABASE NEWTdatabase

PRINT 'Dropping login...'

IF EXISTS (SELECT loginname FROM syslogins WHERE loginname = 'xxxxxxxx')

EXEC sp_droplogin 'xxxxxxxx'

GO

PRINT ' '

USE master

PRINT 'Creating login...'

EXEC sp_addlogin 'xxxxxxxx','xxxxxxxx'

PRINT 'Creating database...'

CREATE DATABASE NEWTdatabase

GO

PRINT ' '

USE NEWTdatabase

PRINT 'Granting access to database...'

EXEC sp_grantdbaccess 'xxxxxxxx'

PRINT 'Granting user permissions...'

GRANT ALL TO xxxxxxxx AS dbo

GO

[/script]

Thanks,

— Dave

Dave L. Clark I SSC Veteran Points: 256 More actions · Answer 4

I still need someone to address the issue of being able to drop an existing table from a database -- as I was trying to do. i.e., via Osql.

Thanks,

— Dave