从同一服务器上不同数据库上的存储过程创建Sybase ASE数据库上的视图？

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:

我试图使用存储过程从第二个数据库创建数据库的视图。如果我在目标数据库上运行它,我可以用下面的SQL创建存储过程:

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:

从DBViz返回的错误是:

 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]

...执行1条语句,0行受影响,执行/获取时间:0.000 / 0.000秒[0成功,0警告,1错误]

Any ideas or suggestions please?

有什么想法或建议吗?

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

ASE 15.0,用于MS SQL Server和Sybase的jTDS Type 4 JDBC驱动程序,1.2.7

1 个解决方案

#1

Create the following stored procedure and put it in sybsystemprocs:

创建以下存储过程并将其放在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:

然后在我的case testdb中切换你的起始数据库并执行:

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.

这将在tempdb中创建视图'object_name'。

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

这是在Sybase ASE 15.7上测试的

#1