Could Someone Help me to Config SQL2005 to Connect to my Host Database Server

Hi

I'm new this .I' using SQL 2005 VWD05.Could some body tell me how to upload to my host server database to to run a membership user account.I dont know what are the procedure to do on my pc in order to transfer the file on the database.

Thanks

I'm not sure what's your exact meaning, but if you want to connect to a remote SQL Server (that's a SQL Server on a different machine rather than the client website application machine), you need to configure the connection string used by the membership provider. For more information, you can refer to this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

And if you want to upload a existing database file which is being used by your website to the remote SQL Server, you'd better copy the database file to the local disk on the remote SQL machine, and then have the database file attached to the SQL intance.

|||

I'm trying run a membership page using the createuserwizard.So how do you upload to the database on

the host server inorder to run the createusewizard to accept new account.

|||Lori could you give me a walk through example on how to create a blank instance?|||

bigmike40:

Lori could you give me a walk through example on how to create a blank instance?

ScottGu made clearly answer to this question on the link I post The blank instance here just mean a new database without database objects required by membership management--this can be a newly created database or some existing database. So you can use Enterprise Manager/Management Studio to create a new database, and then use aspnet_regsql.exe to create database objects on the newly created database. If you want to use existing database on the remote SQL Server, you can move the database to the remote SQL Server using Backup/Restore or Detach/Attach. Here are some useful links for moving database:

Move database with Backup/Restore:http://msdn2.microsoft.com/en-us/library/ms190436.aspx

Move database using Detach/Attach:http://msdn2.microsoft.com/en-us/library/ms187858.aspx

|||Ok i have a question.Are you suppose to upload the data explorer files... in vwd in order for the createuserwizard to work, or just the app data folder|||Sorry I'm not familiar with what you said "upload data explorer files". However to use CreateUserWizard control you need to connect to an available SQL database. So if you want to use the existing database file on the remote SQL database, you'd better copy the database file to the local disk on the remote SQL machine so that the database file can be attached to the SQL instance.|||

I'm sorry lori but i'm a newbei and some to the term i don't understand ,so can be a little more specifc on what u mean byif you want to use the existing database file on the remote SQL database, you'd better copy the database file to the local disk on the remote SQL machine so that the database file can be attached to the SQL instance.

|||OK, when you connect to a SQL database, first you need to login to the SQL instance (specified in Data Source property in connection string), right? And then you can use Database property to access an existing database on the SQL instance (for example pubs, tempdb); or use AttachDBFilename to attach a database and then access the attached database (note currently AttachDBFilename must points to a local database file, UNC, network path, HTTP are not supported), this is what I mean "using an existing database file". Apparently, no matter which database you're going to use, the database file must be on the local disk of the SQL machine, that's why I say "copy database file to the local disk on the SQL machine"|||

Would this help my CreateUserWizard to configure and create new user account ? Because i cannot

Get the CreateUserWizard to work on my host server whenever... i hit the submit button it give me an error

But it work fine on my local machine

thanks

|||What's the error you got? Have you configure the membership database connection according to theScottGu's article?|||

Iori i'd like to thank you for taking the time on helping me.For the past month i've trying to get my project to work, but so far no luck.I did try setting up VWD the way scott did , but for some reason when i ran the wizard PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT, doesn't shows up in the drop down box ..But the others components are listed.I do belive that my problem is that my database is not settup properly. In order to settup my database, do i need to download any other software in order for all the missing componets to show up in the drop down box. I think u did explain it another thread that you did that precedure before. So can you tell me how you went about doing setting up the database.

Thanks you

|||

bigmike40:

but for some reason when i ran the wizard PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT, doesn't shows up in the drop down box ..But the others components are listed.I do belive that my problem is that my database is not settup properly.

Nevermind, it's my pleasure to share your issue Did you mean you can connect to the remote SQL Server in the aspnet_regsql.exe wizard, but some existing databases (PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT) on the remote SQL instance don't show up? Then what's the meaning of "others components are listed"? Did you mean other databases on the remote SQL can be listed? If so, that's really strange, as I know there is no such database setting to control the listed databases in the aspnet_regsql wizard. Are you sure the permission setting of the account you're using is proper on the remote SQL instance? Can you connect to the remote SQL instance using this account?

|||

I'm talking about the page where it say to select server and database on Scott blog.In the dropdown list he as

appservers,

ASPNET

Issue Tracker Kits

Master

model

msdb

Northwind

pubs

Those are the componets that are listed under database in Scott blogs

I did settup my config, but northwind, pubs, aspnet and the tracker kit didnt show up.

is it suppose to show up in the dropdown menu like how scott as it listed ?

And as far as can i connect to a remote SQL instance the answer is yes

On my host server i notice that there is nothing listed there in the database.

But my database name is listed in vwd and when hit test connection it said connected, so it seems that i can connect to myhost database, but i think that the info some how is not transfering over there some how.My project works

fine on my local machine but once i uploaded it on the server the CreateUserWizard doesn't work but all the other pages work fine. I belive that all the server control componets doesnt work on the server

I'm using WEB.com as my Host provider and the tech support don't seem to understaand anything about ASPNET 2.0

I think i might just get a better hosting company that don't out source their tech support

Thanks

|||

After trying to follow Scott config i cannot get this screen.

Step 3: Point your web.config file at the new SQL Database

ASP.NET 2.0 now supports a new section in your web.config file called “<connectionStrings>” which (not too surprisingly) are used to store connection strings. One nice thing from an administration perspective is that the new ASP.NET Admin MMC Snap-in now provides a GUI based way to configure and manage these:

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

private Job GetJobByName(string jobname)

{

try

{

ServerConnection cnn = new ServerConnection("localhost");

Server svr = new Server(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

private Job GetJobByName(string jobname)

{

try

{

ServerConnection cnn = new ServerConnection("localhost");

Server svr = new Server(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

privateJob GetJobByName(string jobname)

{

try

{

ServerConnection cnn = newServerConnection("localhost");

Server svr = newServer(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

privateJob GetJobByName(string jobname)

{

try

{

ServerConnection cnn = newServerConnection("localhost");

Server svr = newServer(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

private Job GetJobByName(string jobname)

{

try

{

ServerConnection cnn = new ServerConnection("localhost");

Server svr = new Server(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

OK

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

private Job GetJobByName(string jobname)

{

try

{

ServerConnection cnn = new ServerConnection("localhost");

Server svr = new Server(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

Could not Create Acceptable cursor

I have sql server 2005 to which a sql 2000 server is added as a linked server.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.

"Could not generate acceptable cursor."

Thanks in advance for the help

See KnowledgeBase article:

http://support.microsoft.com/kb/302477

|||

I ran into the same thing just today.

I was running an update query on a linked server.

I added a primary key on the destination table and that handled the problem.

Could not Create Acceptable cursor

I have sql server 2005 to which a sql 2000 server is added as a linked server.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.

"Could not generate acceptable cursor."

Thanks in advance for the help

See KnowledgeBase article:

http://support.microsoft.com/kb/302477

|||

I ran into the same thing just today.

I was running an update query on a linked server.

I added a primary key on the destination table and that handled the problem.

Could install two Instance in a SQL Server

I have install Sql2005 in my Server, and it use the default instance.
Could we install another Instance in the some Sqlserver.
For instance, I add Instance2 to the SqlServer?Sure. See:
http://msdn2.microsoft.com/en-us/library/ms143531.aspx
HTH
Jerry
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eTsboMseGHA.4948@.TK2MSFTNGP04.phx.gbl...
>I have install Sql2005 in my Server, and it use the default instance.
> Could we install another Instance in the some Sqlserver.
> For instance, I add Instance2 to the SqlServer?
>

Could install two Instance in a SQL Server

I have install Sql2005 in my Server, and it use the default instance.
Could we install another Instance in the some Sqlserver.
For instance, I add Instance2 to the SqlServer?Sure. See:
http://msdn2.microsoft.com/en-us/library/ms143531.aspx
HTH
Jerry
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eTsboMseGHA.4948@.TK2MSFTNGP04.phx.gbl...
>I have install Sql2005 in my Server, and it use the default instance.
> Could we install another Instance in the some Sqlserver.
> For instance, I add Instance2 to the SqlServer?
>

Could anyone test this on SQL2005 AdventureWorks

SELECT TerritoryId,
[2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],
[2004-07]
from ( select TerritoryId,
CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
pivot (
sum(LineTotal) for theDate in ([2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],[2004-07])
) pvt
order by TerritoryId

The code is based on a sample from Pro SQL2005 (Apress). The book is great BTW.

While modifying it for a testdrive I think I made an error but where. Inner select executes in less than a second. However applying the pivot it takes 5-6 seconds.

Total row count is around 121K. All it does with PIVOT is to sum and crosstab. If I do this taking raw data from inner select outside and do pivoting myself it's much faster. So is it my code here or PIVOT is really an unoptimized addition?

TIA

Some more statistics on this process. If execute this pivot and then just inner select (after a few times) here is client statistics from SSMS:

*********************************************************************

Client Execution Time 18:32:27 18:31:33

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements 0 0 0.0000

Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0.0000

Number of SELECT statements 2 2 2.0000

Rows returned by SELECT statements 121318 11 60664.5000

Number of transactions 0 0 0.0000

Network Statistics

Number of server roundtrips 3 3 3.0000

TDS packets sent from client 3 3 3.0000

TDS packets received from server 635 41 338.0000

Bytes sent from client 538 1528 1033.0000

Bytes received from server 2591244 157727 1374486.0000

Time Statistics

Client processing time 375 0 187.5000

Total execution time 390 5187 2788.5000

Wait time on server replies 15 5187 2601.0000

*********************************************************************

To summarize it, SQL server selects the raw data in 390 milliseconds then spends considerable time on pivoting for a total time of 5187 milliseconds.

Here is the timings if I instead just ask for the inner select data (121317 rows) via ODBC, sum and pivot myself externally, again creating the same result table:

For same period range:

Got data-elapsed:457

XTabbed-total elapsed:729

For all 37 months:

Got data-elapsed:455

XTabbed-total elapsed:741

Timings are in milliseconds.

I believe I'm using PIVOT syntax wrong.

|||

I don't see that much performance difference between PIVOT and custom query. The PIVOT operator translates to pretty much same expressions. There is little bit additional cost but not significant. And the IO is identical. I tried this on SQL Server 2005 build# 2153 (with cumulative hotfix for SP1).

use AdventureWorks

go

set statistics io on

set statistics time on

go

SELECT TerritoryID,
[2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],
[2004-07]
from ( select TerritoryID,
CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
pivot (
sum(LineTotal) for theDate in ([2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],[2004-07])
) pvt
order by TerritoryID;

SELECT TerritoryID
,sum(case theDate when '2003-01' then LineTotal end) as [2003-01]
,sum(case theDate when '2003-02' then LineTotal end) as [2003-02]
,sum(case theDate when '2003-03' then LineTotal end) as [2003-03]
,sum(case theDate when '2003-04' then LineTotal end) as [2003-04]
,sum(case theDate when '2003-05' then LineTotal end) as [2003-05]
,sum(case theDate when '2003-06' then LineTotal end) as [2003-06]
,sum(case theDate when '2003-07' then LineTotal end) as [2003-07]
,sum(case theDate when '2003-08' then LineTotal end) as [2003-08]
,sum(case theDate when '2003-09' then LineTotal end) as [2003-09]
,sum(case theDate when '2003-10' then LineTotal end) as [2003-10]
,sum(case theDate when '2003-11' then LineTotal end) as [2003-11]
,sum(case theDate when '2003-12' then LineTotal end) as [2003-12]
,sum(case theDate when '2004-01' then LineTotal end) as [2004-01]
,sum(case theDate when '2004-02' then LineTotal end) as [2004-02]
,sum(case theDate when '2004-03' then LineTotal end) as [2004-03]
,sum(case theDate when '2004-04' then LineTotal end) as [2004-04]
,sum(case theDate when '2004-05' then LineTotal end) as [2004-05]
,sum(case theDate when '2004-06' then LineTotal end) as [2004-06]
,sum(case theDate when '2004-07' then LineTotal end) as [2004-07]
from ( select TerritoryID,
CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
group by TerritoryID
order by TerritoryID;

go

set statistics io off

set statistics time off

go

|||

Thanks for trying. I thought my PIVOT syntax was wrong.

The point is that I really didn't expect PIVOT to be only a syntax enhancement over all those many sum(case ....) coding but as "how it should be done" logic as well. In other words I expected it would create a more clever algorithm behind the scenes and return the results fast. I'm not good at T-SQL but even with my limited knowledge check this one:

use AdventureWorks
go
with myPivot as
(
select territoryID, theDate, sum(LineTotal) as LineTotal
from ( select TerritoryID,
CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
group by TerritoryID, theDate
)
SELECT TerritoryID
,sum(case theDate when '2003-01' then LineTotal end) as [2003-01]
,sum(case theDate when '2003-02' then LineTotal end) as [2003-02]
,sum(case theDate when '2003-03' then LineTotal end) as [2003-03]
,sum(case theDate when '2003-04' then LineTotal end) as [2003-04]
,sum(case theDate when '2003-05' then LineTotal end) as [2003-05]
,sum(case theDate when '2003-06' then LineTotal end) as [2003-06]
,sum(case theDate when '2003-07' then LineTotal end) as [2003-07]
,sum(case theDate when '2003-08' then LineTotal end) as [2003-08]
,sum(case theDate when '2003-09' then LineTotal end) as [2003-09]
,sum(case theDate when '2003-10' then LineTotal end) as [2003-10]
,sum(case theDate when '2003-11' then LineTotal end) as [2003-11]
,sum(case theDate when '2003-12' then LineTotal end) as [2003-12]
,sum(case theDate when '2004-01' then LineTotal end) as [2004-01]
,sum(case theDate when '2004-02' then LineTotal end) as [2004-02]
,sum(case theDate when '2004-03' then LineTotal end) as [2004-03]
,sum(case theDate when '2004-04' then LineTotal end) as [2004-04]
,sum(case theDate when '2004-05' then LineTotal end) as [2004-05]
,sum(case theDate when '2004-06' then LineTotal end) as [2004-06]
,sum(case theDate when '2004-07' then LineTotal end) as [2004-07]
from myPivot
group by TerritoryID
order by TerritoryID
go

Or this one (that I based on how you wrote the comparison T-SQL):

with myPivot as
( select territoryID, theDate, sum(LineTotal) as LineTotal
from (
select TerritoryID,
CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
group by TerritoryID, theDate
)
SELECT TerritoryID,
[2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],
[2004-07]
from myPivot
pivot (
sum(LineTotal) for theDate in ([2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],[2004-07])
) pvt
order by TerritoryID;

They both now have closer timing to my "get the raw data via ODBC and pivot yourself" approach (those 2 execute in 511 and 500 milliseconds vs to my 470 milliseconds which I accept to be the same). That leaded me to think why I need to specify the aggregate in PIVOT if I have to do aggregation myself outside for it to perform faster. Actually I think it would be way cooler if PIVOT didn't need an aggregate function but simply made a row to columns transposion.

However I checked my version and it's built 1399. Maybe it was corrected in 2153. I'll update and retest.

|||

I applied SP1 and now my version reads 2047 (not 2153).

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

No improvement with this one. Probably pivot was only meant to be a somewhat less typing syntax. OK I was concerned if my syntax was the culprit.

|||Yes, currently PIVOT only provides syntax level simplification. It does pretty much the same as hand coded SQL statement. However, this may change in a future version of SQL Server. So if you have a situation where you can use PIVOT then use it. Currently, you cannot pivot on multiple expressions so for that you can go the traditional SQL route.|||Thank you very much. Knowing how it works is sufficient:)|||

BTW somewhere I saw you were collecting enhancement feature ideas. Even if the PIVOT stays same I'd like a version that looks like:

-- rowColumnList is typically group by columns (like TerritoryID) except the one that would be new columns (like theDate)

PIVOT ( (rowColumnList) (valueColumn) for filterColumn in (filter_columnNameList) )

and/or:

-- Same as above but doesn't need a columnnames list ( all unique values of theDate would be new columns)

PIVOT ( (rowColumnList) (valueColumn) on (columnNamesColumn) )

with no aggregation function in pivot_clause. PIVOT then merely do a row to column transposition only. Aggregation would be in select that pivot uses if need be (so users would not need tricks like max() on character/datetime data when there is really nothing to aggregate).

Thanks.