catalog views

Is there any way to access catalog views, such as sys.backup_devices, using a select statement from inside a sored procedure?

This works fine for me;

use adventureworks

go

create procedure usp_test as

select * from

sys.backup_devices

go

exec usp_test

go

|||

This did not work. Following is the actual code i executed(Dont have adventureworks installed so i executed against my database-EstMaster:

use estmaster

go

create procedure usp_test as

select * from

sys.backup_devices

go

exec usp_test

go

This is the output received::

Changed database context to 'EstMaster'.
Msg 2714, Level 16, State 5, Server HOME\ESTIMATEMASTER, Procedure usp_test, Line 6
There is already an object named 'usp_test' in the database.
Msg 208, Level 16, State 1, Server HOME\ESTIMATEMASTER, Procedure usp_test, Line 2
Invalid object name 'sys.backup_devices'.
Also have tried the following stored procedure:

ALTER PROCEDURE dbo.GetData

AS

SET NOCOUNT ON

SELECT * FROM sys.backup_devices

DECLARE @.var1 nvarchar(30);

SET @.var1 = 'No Name';

SELECT var1=Name FROM sys.backup_devices

Print @.var1

SET @.var1 = 'No Name';

SELECT @.var1=recovery_model_desc FROM sys.databases

Print @.var1

Stored procedure output is almost identical- "Invalid object name 'sys.backup_devices". My entire problem appears to be that the view name "sys.backup_devices" ( or "sys.databases") is not being recognized.

|||

I've noticed that

select * from

sys.backup_devices

only seems to work with SQL Server 2005 and not with SQL Server 2000.

Gary

|||

Yes this view only exists in sql2005, is that the version you are running?

In terms of the error you are getting, the first part is because you have created the proc once already, you need to delete it by using the "drop procedure usp_test" command.

I would get this simple version working first as it might be a permissions problem, before going to the more complex one. Can you issue a simple select outside of the stored proc, while in another database?

|||The database in question is an sql server express database(converted from MSDE).|||

Hmmm I'm somewhat perplexed.

Can you just run a simple select * from sys.backup_devices in query editor please?

Next can you go into object explorer in SSMS, browse to the database, then views, then system views and look and see if it is there.

|||

1 SqlServer Express Is The Version being used. The server/database in question was originally an MSDE database and was converted when SqlServer Express was installed. The Database is being used in a VS2003.Net olution and is, as far as i can tell, running fine. The solution uses stored procedures almost exclusively, with numerous fairly complicated select procedures.

2.The duplication is not of concern. I can delete the stored proc from VS2003 very easily.

3. Since I had no other database on hand to try this, I downloaded the AdventureWorks Sample Db and attached it to the Default instance of SqlServer Express that was created during installation of SqlServerExpress. The first test was to try and run the select statement from a script file. This appeared to work ok. The ouput is shown below:

Changed database context to 'AdventureWorks'.
name type type_desc physical_name
-- - --

(0 rows affected)

In a second test I ran the original script you sent in your first contact. This also Appeared to work fine and the output from that test was identical to that shown above. In a third test, I tried to access sys.databases. That also appears to work fine, the output from that test is shown below:
Changed database context to 'AdventureWorks'.
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on
-- -- - -- - -- -- - -- -- - - - -- -- -- -- -- -- - - - -- -- - -- - -- -- -- - -- - -- -- -
master 1 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0
tempdb 2 NULL 0x01 2007-05-22 20:00:19.110 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 0 NONE 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 029F6CF2-9B6D-41CA-ADD1-743BA9B9963F 1 0 NOTHING 0
model 3 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0
msdb 4 NULL 0x01 2005-10-14 01:54:05.240 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 F302CF13-753E-4539-8C5A-569F65D00B95 1 0 NOTHING 0
AdventureWorks 5 NULL 0x010500000000000515000000D576CC6AB43555CE66695A87F4010000 2007-05-22 21:26:23.523 90 Latin1_General_CS_AS 0 MULTI_USER 0 1 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 1 1 1 1 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 93576EF7-2297-4F84-9842-4E6854D3FD10 0 0 NOTHING 0

(5 rows affected)
4.I've tried several other things to see if there is anything wrong with the Estmaster database(where I'm getting these errors), Including a Backup and Restore, but nothing seems to cure the problem.

|||Would be delighted to comply with both requests, however, the only query editor on this sytem is in Visual Studio and does not show that view as available. I'm not familiar with SSMS, is this a tool available with SqlServerExpress? I can't find it on my system.|||You need to download and install SQL Server Management Studio Express Edition from Microsoft|||Since my previous reply, I found an SSMS download and have installed same. I ran the simple query you asked for and it failed with the following error message: Msg 208,level16,state1. I checked the object explorere out and ther are only 2 system views: dbo.sysconstraints and dbo.syssegments. I did the same for adventureworks with the following results: query runs, object explorer contains numerous system views including sys.backup_devices and sys.databases. I begin to suspect this problem is related to the fact that it was converted from an MSDE(Sqlserver2000) database. There are numerous differences between the system views, system tables, and system stored procedures present in adventureworks and in my converted MSDE database. Could my database be reconstructed by detaching it, Then running CreateDatabase?|||have done so. Another post was just sent detailing the results of your requests.|||How did you convert the database?|||

The database I'm working with, EstMaster, was originally a an MSDE database(SQL2000 Server Desk Top Engine). When I installed SqlServerExpress 2005, The installation procedure converted the database without interaction on my part. After The Express installer completed, MSDE was uninstalled for me. After a lot of work on this problem, I' m convinced that it is a permissions problem. The converted database is OK. I can select, update delete, add entries to all my tables. I just cant access the system views, such as sys.databases and sys.backup devices.. Those views do not exist in the EstMaster database. Have been unsuccessful in determining how to install those catalog views on my database. I also Have AdventureWorks in a separate instance and am considering creating a new database in the default instance identical to the current database. I can then copy the tables over. Not desireable, but it is a last resort option. I'd much rather understand why I can't create those views in the Estmaster database.

|||

This should resolve this issue. The reason the sys catalog views are not available for my database is that SqlServer Express does not convert an MSDE database as I originally thought. The sys catlaog views do not exist in an MSDE instance, hence they are not available.The MSDE database was converted to an Sql Server Express Db using the following procedures. These procedures assume a named MSDE instance containing a database, a default Sql Server Express instance and MSSMS=E.

1- Detach the database in the MSDE instance using sp_detach_db.

2- Attach the database in the default Sql Server Express instance using CREATE DATABASE......FOR ATTACH.

3- Move the --.mdf and --.ldf files from the MSDE instance to the DATA folder in the default Sql Server Express instance using the "Planned Relocation Procedure" In the help topic "Moving User Databases". Follow the procedure exactly.

4- Uninstall the MSDE instance using ADD/Remove Programs in Control Panel.

5- Run Sql Server Express setup and create an instance with the same instance name as the old MSDE instance.

6- Back up the data base in the default Sql Server Express instance using BACKUP DATABASE.

7- Copy the --.mdf and --.ldf data files from the backup using Example E in the RESTORE help topic.

8- Create the database in the new instance using using CREATE DATABASE.

9- Delete the database form the default instance using MSSMS-E.

At this point you have an Sql Server Express database created from the data in the MSDE database in an Sql Server Express Instance with the same name as the original MSDE instance. My Visual Studio 2003.Net solution accessing this database required no changes to have full access to the database. All of the sys catalog views and System stored procedures are available. I can manipulate the database from MSSMS-E or from VisualDatabase VisualStudio 2003..