March 5, 2003 at 9:08 am
Hi all,
I am trying to automate a process by creating or Altering a view dynamically using a stored procedure.
1. I tried creating a view in the following manner
Declare @sql Varchar(4000)
SET @Sql = ' Create View dbname.dbo.Viewname As
Select * from tablename'
EXEC ExecuteSql @SQL
It is not allowing me to create the View in the remote database.
2. Then I tried the other way
Declare @sql Varchar(4000)
SET @Sql = 'Use Dbname Go Create View dbo.Viewname As
Select * from tablename'
EXEC ExecuteSql @SQL
It is not working either. I have to use the dynamic SQL as I am using Distributed Database .
I am using SQLServer 2000. I am using now osql utility to accomplish the task. Is there any other workaround for this issue.
Any help will be really appreciated.
March 5, 2003 at 10:42 am
Create the stored procedure on the remote server and call it from your local server.
Steve Jones
March 5, 2003 at 2:04 pm
Dude,
Right now I have that method only. Is there anyother way we can accomplish that task. The reason is even I create the database dynamically in the procedure (as part of a automation process). It is not allowing me to create the procedure either in the remote database. Right now I have a generic procedure in Model database, so that it gets created when ever new Db is created. The only limitation here is I am limited to width of the SQL string.
Please advise if you have any other solutions. Thanks for you help ......
March 5, 2003 at 5:34 pm
Try using xp_cmdshell and osql to log into the remote db with your sql command.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply