0

I have a cursor which gives parameters to columns to executes the stored procedure in order to update my table. Cursor statement is following:

declare @ColumnName nvarchar(30)
declare @ColumnID int

declare UpdateCurs cursor
for 
select ColumnName , ColumnID from MyTable

open UpdateCurs 

fetch next from UpdateCurs into @ColumnName , @ColumnID 

while @@FETCH_STATUS = 0 
BEGIN

  execute dbo.UpdateMyTable @ColumnName , @ColumnID 

fetch next from UpdateCurs into @ColumnName , @ColumnID 

END

close UpdateCurs 
deallocate UpdateCurs

When I execute the statement directly, it works fine, but when I create maintenance plan via "Execute T-SQL statement" and schedule job, the job results with following error:

failed with the following error: "Invalid column name 'ColumnID'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

One thing I can`t understand that, if there is something wrong with my statement, why it is executed without problems directly and gives error on Maintenance plan ? I will appreciate your thoughts on this

  • 1
    Is your maintenance plan pointing to the correct database? Have you tried fully qualifying your table objects? <Database>.<Schema>.TableOrView to make sure you are referencing the correct objects? – Scott Hodgin Nov 24 '16 at 12:12
  • I just have paid attention to database connection after your comment. It is so silly, but I didnt spell "use databasename" in my query, thats why my job wasn`t executing. Sorry, and thank you for pointing to the issue. – Nizami Khalilbayli Nov 24 '16 at 12:41
3

Make sure you are either connecting to the correct database (USE <database>) or using fully qualified names in your maintenance plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.