Count

Hi,

I need to write an SQL query/code to count say E's from a certain table. I am working in SQL Server 2005 (sp2).

For example, consider the table below;

ID From 12 D 12 D 12 C 12 E 12 D 13 D 13 E 13 D 13 E 13 D 14 H 14 D 14 D 14 D 14 D

After the SQL code, I need a result like this table below: In otherwords, I wish to have E's counted if theres E else 0 for that ID.

ID Count E's 12 1 13 2 14 0

Thanks in advance.

try this

SELECT a.[ID]
, COUNT(b.[From]) AS [Count E]
FROM YourTable a LEFT OUTER JOIN
(
SELECT [ID]
. [From]
FROM YourTable
WHERE [From] = 'E'
) b ON a.[ID] = b.[ID]
GROUP BY
a.[ID]|||

This should work reliably:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int,
[From] char(1)
)

INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 12, 'C' )
INSERT INTO @.MyTable VALUES ( 12, 'E' )
INSERT INTO @.MyTable VALUES ( 12, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 13, 'E' )
INSERT INTO @.MyTable VALUES ( 13, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'H' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )
INSERT INTO @.MyTable VALUES ( 14, 'D' )

SELECT DISTINCT
m.[ID],
[From] = isnull( dt.[From], 0 )
FROM @.MyTable m
LEFT JOIN ( SELECT
[ID],
[From] = count( [From] )
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY [ID]
) dt
ON m.[ID] = dt.[ID]

ID From
-- --
12 1
13 2
14 0

I highly recommend that you avoid using Reserved words for table/column names. FROM is a HIGHLY reserved word, and can only be used by enclosing in double quotes or square brackets.

|||LOL, i haven't tested my post. arnie's post is correct.|||Depending on the distribution of data and what indexes there are on the table, this may be a better solution:

Code Snippet

SELECT
ID,
SUM(

CASE WHEN [From] = 'E'

THEN 1 ELSE 0 END

) AS [Count E's]
FROM @.MyTable
GROUP BY ID

Steve Kass
Drew University
http://www.stevekass.com
|||

You can also use "GROUP BY ALL"

SELECT
[ID],
COUNT(*) AS [Count E's]
FROM @.MyTable
WHERE [From] = 'E'
GROUP BY ALL [ID]

|||

Try using a CASE expression.

select [ID], sum(case when [From] = 'E' then 1 else 0 end) as [Count E's]

from dbo.t1

group [ID]

go

AMB

|||

Thank all.

I tried all the replies and the one that well with my data was Steve's post.

Cheers

Could not start mirroring on a single laptop with two instances of SQL Server 2005

I tried to set up mirroring on my laptop.

I have got two instances of SQL Server 2005 SP2 on my laptop (the first one is the default instance).

Checked configuration running the following scripts

SELECT type_desc, port FROM sys.tcp_endpoints;

GO

SELECT state_desc FROM sys.database_mirroring_endpoints

go

SELECT role FROM sys.database_mirroring_endpoints;

GO

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

GO

Everything is OK on both instances. Tried to start mirroring and I have got this error message:

TITLE: Database Properties

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://kookaburra.sydney.ssw.com.au:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

I could connect to both instances:

C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra,5022
1> exit
C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra\sydney2005,5023
1> exit

I could ping my laptop

C:\Documents and Settings\SergeiTchernykh.SSW2000>ping -a kookaburra

Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:

Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 10.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Thank you.

Problem solved.

"Database Mirror Wizard" does not show the correct error.

This error was displayed - "Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)" after executing T-SQL statement "ALTER DATABASE SET PARTNER .."

After running SQL Server Profiler I found out that the first error was: Database Northwind wasn't configured for mirroring.

Mirror database should be in RECOVERING mode.

Dropped and restored Northwind database again with NORECOVERY and mirroring started working.

|||

Hi Sergei,

How did you create the endpoints? Since you were able to connect to the sql server instances listening on ports 5022 and 5023, I suspect that you created them for T-SQL payload and not DB mirroring.

Try using something similar to this to create the endpoints:

CREATE ENDPOINT [dbm] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

Thanks,

Kaloian.

|||

Hi Kaloian,

As long as I have got 3 instances of SQL Server 2005 (later on) on my laptop (default, instance_1 and instance_2 (witness)) I manually configured endpoint for witness using statement "CREATE ENDPOINT ..." on port 5024.

But it wasn't the problem. Problem in my case was that to set up a mirror you need a mirroring database in RECOVERING mode (Ididn't configure that) but the error returned didn't specify that.

Thanks,

Sergei

sql

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 establish trust relationship with remote server.

I am trying to setup SQL Server 2000 Reporting services with an SSL
connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
have tried setting up Certificates from both and Stand-Alone and Enterprise
CA. I have tried several combinations of the Issue To name (server |
server.company | server.company.local) I have modified the
rsWebApplication.config and rsReportServer.config to match the certificateâ's
Issue to exactly. I have tried installs with both Domain Accounts and NT
AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
to work but I have had no luck with the report manager
https://server/reports. How do I fix this error?
ThankyouThe most likely reason this is failing is that your Report Server does not
trust the Root Certificate Authority that created the certificate you are
using.
If you go to https://machine/ReportServer and IE says "this certificate is
not valid are you sure you want to accept it?" then you will know that the
certificate is not 100% trusted and that is why you are receiving the error.
You will need to export the Trusted Root Authority certificate from the
issuing server and import it on the Report Server.
--
SQL Server Developer Support Engineer
"vbchewie" wrote:
> I am trying to setup SQL Server 2000 Reporting services with an SSL
> connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
> Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
> have tried setting up Certificates from both and Stand-Alone and Enterprise
> CA. I have tried several combinations of the Issue To name (server |
> server.company | server.company.local) I have modified the
> rsWebApplication.config and rsReportServer.config to match the certificateâ's
> Issue to exactly. I have tried installs with both Domain Accounts and NT
> AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
> to work but I have had no luck with the report manager
> https://server/reports. How do I fix this error?
> Thankyou
>|||I went to the stand alone root certificate authority and exported the
certificate for that server. I then imported it into both the SQL Server and
Reporting Server. Now when I type the FQDN
(https://machine.domain.local/Reports ) I no longer get a certificate error,
but I do still get â'The underlying connection was closed: Could not establish
trust relationship with remote server.â'
Is there something else I can do? Does it have to be an Enterprise Root
Certificate Authority in order to work?
Thank You.
"Chris Alton [MS]" wrote:
> The most likely reason this is failing is that your Report Server does not
> trust the Root Certificate Authority that created the certificate you are
> using.
> If you go to https://machine/ReportServer and IE says "this certificate is
> not valid are you sure you want to accept it?" then you will know that the
> certificate is not 100% trusted and that is why you are receiving the error.
> You will need to export the Trusted Root Authority certificate from the
> issuing server and import it on the Report Server.
> --
> SQL Server Developer Support Engineer
>
> "vbchewie" wrote:
> > I am trying to setup SQL Server 2000 Reporting services with an SSL
> > connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
> > Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
> > have tried setting up Certificates from both and Stand-Alone and Enterprise
> > CA. I have tried several combinations of the Issue To name (server |
> > server.company | server.company.local) I have modified the
> > rsWebApplication.config and rsReportServer.config to match the certificateâ's
> > Issue to exactly. I have tried installs with both Domain Accounts and NT
> > AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
> > to work but I have had no luck with the report manager
> > https://server/reports. How do I fix this error?
> >
> > Thankyou
> >|||You also need to make sure that the SSL certificate you are using matches
the machine name you are accessing it by EXACTLY. You will also need to
make sure that in the Reporting Services configuration tool that you have
checked the "Require Secure Socket Layer (SSL) Connections" and put the
same name you are accessing the server by in the "Certificate Name" field.
So in your example you would put "machine.domain.local" in the Certificate
Field and the certificate should be issued to "machine.domain.local".
The easiest way to tell if the certificate is valid is to open up
https://machine.domain.local/ReportServer from the web server and if IE
complains about the certificate at all then it will not work.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.|||In RSReportServer.config I have my "SecureConnectionLevel" Value="3" and my
<UrlRoot>https://machine.domain.local/ReportServer</UrlRoot>
in RSWebApplication.config
I have
<ReportServerUrl>https://machine.domain.local/ReportServer</ReportServerUrl>
When I go to IIS on the Reporting Server and right click on Default Web Site
> Properties > Directory Security.
And click on 'View Certificate...' it says Issued to: machine.domain.local I
also checked the friendly name it also has machine.domain.local.
When I click 'Edit...' under 'Secure communications' both Require secure
channel(SSL) and Require 128-bit encryption are checked.
When I click 'Edit...' Under 'Authentication and access control' Enable
anonymous access is unchecked.
The same is true for my Virtual Directories 'Reports' and ReportServer'
When I go to https://machine/domain.local/Reports there are no certificate
errors. It goes straight though to a page that says
"The underlying connection was closed: Could not establish trust
relationship with remote server."
You mentioned Reporting Services Configuration Tool. This is Reporting
Services for SQL Server 2000. Is there a Reporting Services Configuration
Tool for this version? I thought that was for 2005.
Iâ'm not sure what I am missing. Any other ideas?
Thank you
"Chris Alton [MSFT]" wrote:
> You also need to make sure that the SSL certificate you are using matches
> the machine name you are accessing it by EXACTLY. You will also need to
> make sure that in the Reporting Services configuration tool that you have
> checked the "Require Secure Socket Layer (SSL) Connections" and put the
> same name you are accessing the server by in the "Certificate Name" field.
> So in your example you would put "machine.domain.local" in the Certificate
> Field and the certificate should be issued to "machine.domain.local".
> The easiest way to tell if the certificate is valid is to open up
> https://machine.domain.local/ReportServer from the web server and if IE
> complains about the certificate at all then it will not work.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I didn't know you were using SQL 2000. The only thing I can think of is
that you need to install the Root CA Certificate in the Certificate Store
of the Machine account. What account do you have the SRS Windows Service
and the IIS Application Pool running under?
If it is LocalSystem or Network Service you can install the certificate by
following these steps:
a. Click Start->Run
b. Type mmc and hit enter.
c. Click File->Add/Remove Snap-in
d. Click the "Add" button.
e. Select "Certificates" from the list
f. Click the "Add" button.
g. Click the "Computer Account" radio button.
h. Click "Next"
i. Make sure "Local Computer" is selected and click "Finish"
j. Click "Close"
k. Click "Ok"
l. Branch down on Certificates.
m. Right click Trusted Root Certification Authorities->All
Tasks->Import...
n. Click "Next" and browse to the certificate you are importing.
o. Click "Next" and make sure the "Place all certificates in the
following store" is selected and "Trusted Root Certification Authorities"
is listed in the "Certificate Store" box. If not browse to it by clicking
the "Browse" button.
p. Click "Next" and then click "Finish"
q. The Trusted Root Authority Certificate should now be imported and
available to web application/service.
Hopefully that should get it now.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.|||It works!!!
SRS is running under a domain\account. In order to get Kerberos to function
properly I used setspn to allow the domain\account to use the http service.
So I am not running under LocalSystem or Network Service.
I logged into the computer with the domain\account that is running both the
SRS Service and the ReportingServices Application Pool. I followed your
directions exactly with only one variation. Instead of picking "Computer
Account" at g. I chose "My Account". It looks like it is working now.
To recap for anyone else that runs into this issue:
SQL Server 2000 is on machine1
SharePoint and Reporting Services are on machine2 and are running under a
domain\account (very limited rights).
SSL Certificate was issued from a Stand-Alone Root Certificate Authority.
Thank you very much for your help,
"Chris Alton [MSFT]" wrote:
> I didn't know you were using SQL 2000. The only thing I can think of is
> that you need to install the Root CA Certificate in the Certificate Store
> of the Machine account. What account do you have the SRS Windows Service
> and the IIS Application Pool running under?
> If it is LocalSystem or Network Service you can install the certificate by
> following these steps:
> a. Click Start->Run
> b. Type mmc and hit enter.
> c. Click File->Add/Remove Snap-in
> d. Click the "Add" button.
> e. Select "Certificates" from the list
> f. Click the "Add" button.
> g. Click the "Computer Account" radio button.
> h. Click "Next"
> i. Make sure "Local Computer" is selected and click "Finish"
> j. Click "Close"
> k. Click "Ok"
> l. Branch down on Certificates.
> m. Right click Trusted Root Certification Authorities->All
> Tasks->Import...
> n. Click "Next" and browse to the certificate you are importing.
> o. Click "Next" and make sure the "Place all certificates in the
> following store" is selected and "Trusted Root Certification Authorities"
> is listed in the "Certificate Store" box. If not browse to it by clicking
> the "Browse" button.
> p. Click "Next" and then click "Finish"
> q. The Trusted Root Authority Certificate should now be imported and
> available to web application/service.
> Hopefully that should get it now.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||If the Windows Service and Application Pool all run under a domain account
then you really don't have to go through all those convoluted steps to
import the cert. That is only if you are using LocalSystem/Network Service
since those are considered the "Machine" account.
If you can log on to the account in an interactive session all you really
have to do to import the certificate is logon and then double click the
cer file and follow the prompts :)
Glad its working for you now though, those SSL issues with SRS can be quite
complicated sometimes.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Could not establish trust relationship with remote server.
> From: <vbchewie@.discussions.microsoft.com>
> Subject: RE: Could not establish trust relationship with remote server.
> Date: Tue, 2 Oct 2007 14:18:01 -0700
> It works!!!
> SRS is running under a domain\account. In order to get Kerberos to
function
> properly I used setspn to allow the domain\account to use the http
service.
> So I am not running under LocalSystem or Network Service.
> I logged into the computer with the domain\account that is running both
the
> SRS Service and the ReportingServices Application Pool. I followed your
> directions exactly with only one variation. Instead of picking "Computer
> Account" at g. I chose "My Account". It looks like it is working now.
> To recap for anyone else that runs into this issue:
> SQL Server 2000 is on machine1
> SharePoint and Reporting Services are on machine2 and are running under a
> domain\account (very limited rights).
> SSL Certificate was issued from a Stand-Alone Root Certificate Authority.
> Thank you very much for your help,
>
> "Chris Alton [MSFT]" wrote:
> > I didn't know you were using SQL 2000. The only thing I can think of is
> > that you need to install the Root CA Certificate in the Certificate
Store
> > of the Machine account. What account do you have the SRS Windows
Service
> > and the IIS Application Pool running under?
> >
> > If it is LocalSystem or Network Service you can install the certificate
by
> > following these steps:
> >
> > a. Click Start->Run
> > b. Type mmc and hit enter.
> > c. Click File->Add/Remove Snap-in
> > d. Click the "Add" button.
> > e. Select "Certificates" from the list
> > f. Click the "Add" button.
> > g. Click the "Computer Account" radio button.
> > h. Click "Next"
> > i. Make sure "Local Computer" is selected and click "Finish"
> > j. Click "Close"
> > k. Click "Ok"
> > l. Branch down on Certificates.
> > m. Right click Trusted Root Certification Authorities->All
> > Tasks->Import...
> > n. Click "Next" and browse to the certificate you are importing.
> > o. Click "Next" and make sure the "Place all certificates in the
> > following store" is selected and "Trusted Root Certification
Authorities"
> > is listed in the "Certificate Store" box. If not browse to it by
clicking
> > the "Browse" button.
> > p. Click "Next" and then click "Finish"
> > q. The Trusted Root Authority Certificate should now be imported
and
> > available to web application/service.
> >
> > Hopefully that should get it now.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
>