asked on

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

Aneesh

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.

Fay A

ASKER

Can you show me an example please?

Aneesh

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

Fay A

ASKER

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fay A

ASKER

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

Aneesh

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

Fay A

ASKER

I am running it now, seems like it is working

Fay A

ASKER

Thank you so much