declare @strDB varchar(200) set @strDB='DBname' exec ('use '+@strDB+' GO' /* */ CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey')
declare @strDB varchar(200) set @strDB='DBname' exec ('use '+@strDB)EXEC('CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey')
我记得这些写是无效的: declare @strDB varchar(200) set @strDB='DBname' exec ('use '+@strDB)不会出错,但是没有任何效果。
将两句分开写不就行了吗?declare @strDB varchar(200) set @strDB='DBname' exec ('use '+@strDB) exec(' /* */ CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey')
如果是在存储过程中,下面这个是无效的. declare @strDB varchar(200) set @strDB='DBname' exec ('use '+@strDB)
declare @strDB varchar(200) set @strDB='test' exec ('use '+@strDB) exec(' CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey')
这是限制,不行的。CREATE时要在本数据库。 且go不是SQL也不能这样动态执行。exec 只在执行中有效: use master EXEC('use test') 结果还在master楼主要改变思路才是解决方法。
我是错的。。J9988,J老师的答案:declare @strDB varchar(200) set @strDB='test' declare @sql varchar(1000) set @sql=' osql -U sa -P ruirui -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey"'
我是错的。。J9988,J老师的答案:declare @strDB varchar(200) set @strDB='test' declare @sql varchar(1000) set @sql=' osql -U sa -P ruirui -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey"'
哇,楼主是pretty boy!大力那加一句: exec master.dbo.xp_cmdshell @sql =================================declare @strDB varchar(200) set @strDB='test' declare @sql varchar(1000) set @sql=' osql -U sa -P yourpassword -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey"'
贴错了。 declare @strDB varchar(200) set @strDB='test' declare @sql varchar(1000) set @sql=' osql -U sa -P yourpassword -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2 AS SELECT d.nodekey,t.mc FROM dbo.bDocFlow d INNER JOIN dbo.BTree t ON d.nodekey = t.nodeKey"'exec master.dbo.xp_cmdshell @sql
set @strDB='DBname'
exec ('use '+@strDB+' GO' /* */ CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey')
set @strDB='DBname'
exec ('use '+@strDB)EXEC('CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey')
declare @strDB varchar(200)
set @strDB='DBname'
exec ('use '+@strDB)不会出错,但是没有任何效果。
set @strDB='DBname'
exec ('use '+@strDB)
exec(' /* */ CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey')
declare @strDB varchar(200)
set @strDB='DBname'
exec ('use '+@strDB)
set @strDB='test'
exec ('use '+@strDB)
exec(' CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey')
且go不是SQL也不能这样动态执行。exec 只在执行中有效:
use master
EXEC('use test')
结果还在master楼主要改变思路才是解决方法。
set @strDB='test'
declare @sql varchar(1000)
set @sql=' osql -U sa -P ruirui -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey"'
set @strDB='test'
declare @sql varchar(1000)
set @sql=' osql -U sa -P ruirui -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey"'
exec master.dbo.xp_cmdshell @sql
=================================declare @strDB varchar(200)
set @strDB='test'
declare @sql varchar(1000)
set @sql=' osql -U sa -P yourpassword -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey"'
declare @strDB varchar(200)
set @strDB='test'
declare @sql varchar(1000)
set @sql=' osql -U sa -P yourpassword -d '+@strdb+' -q " CREATE VIEW dbo.viewDbsy2
AS
SELECT d.nodekey,t.mc
FROM dbo.bDocFlow d INNER JOIN
dbo.BTree t ON d.nodekey = t.nodeKey"'exec master.dbo.xp_cmdshell @sql