Generate script views and EXEC

• Flávio Gaspar

  Old Hand

  Points: 332

  More actions

  February 25, 2008 at 8:16 am

  #154148

  Hi folks

  Well im trying and trying to do something like this, every month my client needs to generate a new database will especific info. My actual arquithecture uses two databases for replication purpouses, that means that there are views in DB1 pointing to tables in DB2 and this cause problems when i use the wizard scripts to generate the views, i need to uncheck about to 70 views, because some names generete conflits when executing the final script in the new database.

  The first part is donne, of selecting only the views i whant it, my problem is generate execute the osql command to add the views syntax and finally execute that file to the new database DB3.

  Can anyone help me with this, i can't get this working straite.

  Here is the following script:

  SET NOCOUNT ON

  DECLARE @strSQL nvarchar(500),

  @strSQLinput nvarchar(100),

  @strView nvarchar (50)

  DECLARE view_cursor CURSOR FOR

  SELECT c.name

  FROM DB1.dbo.sysobjects c

  WHERE c.type = 'V'

  and c.name not in(

  SELECT a.name

  FROM DB1.dbo.sysobjects a, DB2.dbo.sysobjects b

  WHERE a.name = b.name

  and a.type = 'V')

  OPEN view_cursor

  FETCH NEXT FROM view_cursor

  INTO @strView

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @strSQL = 'osql -U webuser -P WebPass -S ServerName -d DB1 -h -w 5000 -Q "sp_helptext ' + @strView + '" > c:\MyViews.sql'

  PRINT @strSQL

  EXEC master..xp_cmdshell @strSQL

  SET @strSQLinput = 'osql -U webuser -P WebPass -S ServerName -d DB3 -h-1 -w 1000 < C:\MyViews.sql'

  EXEC master..xp_cmdshell @strSQLinput

  FETCH NEXT FROM view_cursor

  INTO @strView

  END

  CLOSE view_cursor

  DEALLOCATE view_cursor