Problem when moving index with included columns to another filegroup

Hi,

This may be a GUI bug. When I manually create and then execute a script to move an index with included columns to another filegroup, it works.

When I try to edit the storage property of the index through the GUI, index properties, then when I ask to generate the script for the change to the clipboard, I get an error

Could not add [FirstIncludedColumn] to the collection because it already exist. Microsoft.sqlserver.Smo

it works if the index does not includes addtl columns.

Is that a bug? It really looks it works when done manually.

Thanks, Philippe

Moving this thread to the Tools forum since this is not a TSQL/DDL issue.|||

This sounds like a bug to me. Please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

If possible, please include the T-SQL to create the index that causes problems. It also helps if you can include really detailed instructions for what you clicked/typed in the index dialog to make the storage change.

Thanks,
Steve

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx