Error when installing

[jclausius]

OK. Someone posted back a plausible scenario. If your SQL Server 2000 is in SQL Server 6 mode (which Mike pointed out did not have the ENCRYPTION option), that might explain things.


New Databases are usually created with the compatibility mode based off the "model" database.

You can check any database compatibility mode with the following syntax:
sp_dbcmptlevel 'db_name_goes_here'

For example, check your basic DBs:

Code: Select all

sp_dbcmptlevel 'msdb'
GO
sp_dbcmptlevel 'model'
GO
sp_dbcmptlevel 'master'
GO
sp_dbcmptlevel 'tempdb'
GO

What are the values?

[koolworld]

Hi, got this back for each query:

The current compatibility level is 80.

[jclausius]

Code: Select all

-- a test db
CREATE DATABASE testdb
GO
-- check compatibility level
sp_dbcmptlevel 'testdb'
GO
-- switch to the db
USE testdb
GO
-- re-check compatibility level
sp_dbcmptlevel 'testdb'
GO
-- a stored proc
CREATE PROCEDURE dbo.sourcegeartestsp WITH ENCRYPTION
AS
BEGIN
SELECT count(*) from sysusers
END
GO
-- triple-check compatibility level
sp_dbcmptlevel 'testdb'
GO
-- execute the stored proc
EXEC dbo.sourcegeartestsp
GO
-- switch db context
USE master
GO
-- drop database
DROP DATABASE testdb
GO

Does this work?

[koolworld]

Hi, this is the output, seems to fail on the encryption:

Code: Select all

The CREATE DATABASE process is allocating 0.75 MB on disk 'testdb'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'testdb_log'.
The current compatibility level is 80.
The current compatibility level is 80.
Server: Msg 195, Level 15, State 1, Procedure sourcegeartestsp, Line 2
'ENCRYPTION' is not a recognized option.
The current compatibility level is 80.
Server: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.sourcegeartestsp'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\testdb_log.LDF'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\testdb.mdf'.

[jclausius]

Hmm.. Can you log into the SQLServer\Instance again, run the following xp_msver, and then post the results.


Also, did you make any headway with SQL Server Express/MSDE?

[koolworld]

Haven't managed to get another SQL instance installed yet, will look into that tomorrow:

Code: Select all

1	ProductName	NULL	Microsoft SQL Server
2	ProductVersion	524288	8.00.2039
3	Language	1033	English (United States)
4	Platform	NULL	NT INTEL X86
5	Comments	NULL	NT INTEL X86
6	CompanyName	NULL	Microsoft Corporation
7	FileDescription	NULL	SQL Server Windows NT
8	FileVersion	NULL	2000.080.2039.00
9	InternalName	NULL	SQLSERVR
10	LegalCopyright	NULL	© 1988-2004 Microsoft Corp. All rights reserved.
11	LegalTrademarks	NULL	Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12	OriginalFilename	NULL	SQLSERVR.EXE
13	PrivateBuild	NULL	NULL
14	SpecialBuild	133627904	NULL
15	WindowsVersion	248381957	5.2 (3790)
16	ProcessorCount	2	2
17	ProcessorActiveMask	3	00000003
18	ProcessorType	586	PROCESSOR_INTEL_PENTIUM
19	PhysicalMemory	1023	1023 (1072664576)
20	Product ID	NULL	NULL

[jclausius]

Thanks.

If you want to follow along, I started a thread on the microsoft.public.sqlserver.server newsgroup.

[jclausius]

koolworld:

An odd thought. Can you pull up Query Analyzer? From the Help's Index, look at "CREATE PROCEDURE" for "Transact-SQL".

In the help, there should be section on the "WITH" options. For example, my Books-Online for SQL Server 2000 has the following options:

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

including a whole section on "ENCRYPTION".



Does your help return the same information?

[koolworld]

Hi, yes it shows the same as yours.

Dan.

[jclausius]

Hmm... If it is available within Help, I'm confused why it doesn't actually work.

Yet another option would be to try a different "test" machine. Would something in the OS or SQL Server 2000 install cause this kind of problem?