CODEWITHSUNDEEP
sqlstored-proceduresviewsybasesap-ase
user2579685
user2579685

Reputation: 429

Creating Views on a Sybase ASE database from a stored procedure on a different database on the same server?

I am trying to create a view on a database from a second database using a stored procedure. I can create the stored procedure with the SQL below if I run this on the target database:

IF OBJECT_ID ('databasename..v_MyView') IS NOT NULL
    DROP VIEW v_MyView
GO

CREATE VIEW v_MyView
AS      

    select FirstName, LastName, Address
    from databasename..t_UserTable
    where SalesPersonId = 21563 

GO

I was hoping I could just prefix the line:

CREATE VIEW v_MyView

with:

CREATE VIEW databasename..v_MyView

and then call this from inside a stored procedure on a second database on the same server but this does not work.

The error back from DBViz is:

 16:26:28  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 166, SQL State: S1000]  CREATE        VIEW does not allow specifying the database name as a prefix to the object name.

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Any ideas or suggestions please?

ASE 15.0, jTDS Type 4 JDBC Driver for MS SQL Server and Sybase, 1.2.7

Upvotes: 2

Views: 2723

Answers (1)

Richard Crossley
Richard Crossley

Reputation: 586

Create the following stored procedure and put it in sybsystemprocs:

create procedure sp_user_create_view 
    @a_view_name        varchar(255),
    @a_view_query       varchar(16000)    
as
begin
    if object_id(@a_view_name) is not null
    begin
        exec ('drop view ' + @a_view_name) 
    end

    exec ('create view ' + @a_view_name + ' as ' + @a_view_query) 
end
go
grant execute on sp_user_create_view to <user/group/role as appropriate>
go

Then switch your starting database, in my case testdb and execute:

use testdb
go
exec tempdb..sp_user_create_view object_name, 'select name from sysobjects'
go

This will create the view 'object_name' in tempdb.

use tempdb
go
set rowcount 5
go
select * from object_name
go
name
----------------
object_name      
sysalternates
sysattributes
syscolumns
syscomments

(5 rows affected)
set rowcount 0
go

This was tested on Sybase ASE 15.7

Upvotes: 3

Related Questions