Can not find Microsoft.SqlServer.InstApi when trying to run a SMO trace

I install the follwing components

1.Microsoft SQL Server 2005 Management Objects Collection

2.Microsoft Core XML Services (MSXML) 6.0

3.Microsoft SQL Server Native Client

(available from http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en)

on a clean machine with no SQL Server 2005 client tools.

I get the following error.

Failed to initialize object as reader.
Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

A gacutil tells me that this assembly is not installed.

Is there are way to run SMO trace without having to install the 2005 client tools and only the Management Objects Collection and necessary components?

Sample code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;

namespace SMOTraceTestProgram
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void bStart_Click(object sender, EventArgs e)
{

TraceServer smoTraceServerReader;

ConnectionInfoBase cInfoBase = new SqlConnectionInfo();
SetupConnectionInfoBase((cInfoBase as SqlConnectionInfo));

smoTraceServerReader = new TraceServer();

try
{
smoTraceServerReader.InitializeAsReader(cInfoBase, "2005NOSPIDFilter.tdf");
smoTraceServerReader.Stop();
MessageBox.Show("Trace Started and stopped successfully");
}
catch (Exception exception)
{
StringBuilder sb = new StringBuilder();
sb.Append(exception.Message);

Exception ex = exception.InnerException;
while(ex != null)
{
sb.Append(ex.Message);
ex = ex.InnerException;
}
MessageBox.Show(sb.ToString());
}

}

private void SetupConnectionInfoBase(SqlConnectionInfo sqlConnectionInfo)
{
sqlConnectionInfo.ServerName = eSeverName.Text;

if (eUserName.Text.Length > 0)
{
sqlConnectionInfo.UserName = eUserName.Text;
sqlConnectionInfo.Password = ePassword.Text;
sqlConnectionInfo.UseIntegratedSecurity = false;
}
else
{
sqlConnectionInfo.UseIntegratedSecurity = true;
}

}

}
}

I have reproduced this issue in house and can confirm that this is a problem with the Microsoft SQL Server 2005 Management Objects Collection. We will consider this issue for inclusion in Service Pack 2.

As for workarounds, I don't have one. I tried Gac'cing the missing DLL, but there is apparently more registration required, as evidenced by the new error message I got. ("Failed to initialize object as reader.Failed to get SQL Tools directory path from InstAPI.")

|||

Thanks James,

Appreciate the feedback. For the time being I will get the users of my profiling tool to install management tools from the SQL Server 2005 Install. This seems to install the necessary components for SMO Trace to work.

Look forward to a fix though, as this installs a lot more than is required.

sql

can not drop user from database

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

sql

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

Hi

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

Technical details

===================================

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can mirroring be implemented using SMO.

I wanted know that is it possible to implement mirroring in C#.NET framework using SMO (I need specifically a web applicaton). which willl provide a naive user the facilities integrated in SSMS. I am using VS.NET 2005 and SQL Server 2005 SP1.
Thanks and Regards,
Adii

Can you please clarify your question.

Database mirroring is a feature provided in "SQL Server 2005 SP1". Can you please elaborate what you mean by "implementing" it?

|||Thanks Sanjay,
I wanted to know that are there any classes or APIs provided, so that I can programmatically achieve mirroring in my application. Does SMO namespace can provide any assistance in this? Please do let me know if you know or find some solution for the same. I have SQL Server 2005 SP1 installed, but I don't want to use SSMS for doing mirroring. I want to provide this facility in my application to the user programmatically.|||I too am very interested in this answer. We are looking to automate some of the setup and configuration and it would be nice to be able to use SMO or some other exposed APIs to do it with.

Can mirroring be implemented using SMO.

I wanted know that is it possible to implement mirroring in C#.NET framework using SMO (I need specifically a web applicaton). which willl provide a naive user the facilities integrated in SSMS. I am using VS.NET 2005 and SQL Server 2005 SP1.
Thanks and Regards,
Adii

Can you please clarify your question.

Database mirroring is a feature provided in "SQL Server 2005 SP1". Can you please elaborate what you mean by "implementing" it?

|||Thanks Sanjay,
I wanted to know that are there any classes or APIs provided, so that I can programmatically achieve mirroring in my application. Does SMO namespace can provide any assistance in this? Please do let me know if you know or find some solution for the same. I have SQL Server 2005 SP1 installed, but I don't want to use SSMS for doing mirroring. I want to provide this facility in my application to the user programmatically.|||I too am very interested in this answer. We are looking to automate some of the setup and configuration and it would be nice to be able to use SMO or some other exposed APIs to do it with.