• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 34
  • Last Modified:

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
0
Fay A
Asked:
Fay A
  • 5
  • 4
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now