sql job

I have a table that populates automatically with create, alter and drop view values. How can I execute these values, it needs to be executed on a different database. I can copy and paste and execute it manually but I would like to automate the process.
file.PNG
Fay ADBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
If the table contain actual statements, then you can loop thru it and execute those statements thru the job. Once they execute, update a flag on the original table.
0
Fay ADBAAuthor Commented:
Can you show me an example please?
0
Aneesh RetnakaranDatabase AdministratorCommented:
1. Alter Table TableContainingScriptsOnOtherDB add IsExecuted bit default 0
2. Now update the value for the "IsExecuted"  for the statements you ran manually.
3. Create a stored procedure with this logic

declare @SQL varchar(1000) , @id int
select  top 1  @SQL =statement,  @id = id  from otherdb..TableContainingScriptsOnOtherDB where IsExecuted = 0
While @@rowcount > 0
BEGIN TRY
  Execute (@SQL)
  update otherdb..TableContainingScriptsOnOtherDB set isExecuted  = 1 Where id =1
 select  top 1  @SQL =statement,  @id = id  from otherdb..TableContainingScriptsOnOtherDB where IsExecuted = 0
END TRY
BEGIN CATCH
select Error_number()
END CATCH

4. test this sp,  call it on sql job
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Fay ADBAAuthor Commented:
So, I changed the job accordingly and I am getting this back, any idea what 208 is?

(No column name)
208


here's the script, any idea what's wrong here
declare @SQL varchar(1000) , @id varchar(1000)
select  top 1  @SQL = script ,  @id = rowid  from PipelineTrueUp.dba.viewCreationQueue where completed = 0
While @@rowcount > 0
BEGIN TRY
  Execute (@SQL)
  update PipelineTrueUp.dba.viewCreationQueue set completed  = 1 Where rowid =1
 select  top 1  @SQL = script,  @id = rowid  from PipelineTrueUp.dba.viewCreationQueue where completed = 0
END TRY
BEGIN CATCH
select Error_number()
END CATCH
0
Aneesh RetnakaranDatabase AdministratorCommented:
You can add more details in case of the error

declare @SQL varchar(1000) , @id varchar(1000)
select  top 1  @SQL = script ,  @id = rowid  from PipelineTrueUp.dba.viewCreationQueue where completed = 0
While @@rowcount > 0
BEGIN
      BEGIN TRY
        Execute (@SQL)
        update PipelineTrueUp.dba.viewCreationQueue set completed  = 1 Where rowid =1
      END TRY
      BEGIN CATCH
            SELECT Error_number(), ERROR_MESSAGE(), @SQL
            GOTO ed;  
      END CATCH
   SELECT  top 1  @SQL = script,  @id = rowid  from PipelineTrueUp.dba.viewCreationQueue where completed = 0
END
Ed:
PRINT 'done'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fay ADBAAuthor Commented:
208      Invalid object name 'PipelineTrueup.dbo.viewtest'.       CREATE VIEW pipeline.vw_viewtest as SELECT a.* From PipelineTrueup.dbo.viewtest a WITH (NOLOCK)


makes sense
 I guess 208 is the error # here
0
Aneesh RetnakaranDatabase AdministratorCommented:
are you able to run the same  create statement on the same sql window ? it should be ran on the target database.  Ensure that there are corresponding schema and ensure that the sql login has sufficient permission.  Also change the variable declaration like this  
declare @SQL varchar(max) , @id int
0
Fay ADBAAuthor Commented:
I am running it now, seems like it is working
0
Fay ADBAAuthor Commented:
Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.