How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

sql

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

how to empty a stored procedure in ms sql server management studio express

hi everyone,

I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id′s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?

Thanks in advance, best regards

bg

hi bg,

stored procedures can only be executed and not "filled" like "tables"... so they can not be emptied (is that english? )

so what are you trying to do? if you want to empty a specific table, you can open SQL Server Management Studio Express, select the table, access the "Open table" feature, select all rows and delete them (if not referenced in foreign key constraint) or do the same in a query window executing

TRUNCATE TABLE schema_name.table_name

or

DELETE FROM schema_name.table_name WHERE filter_criterion..

regards

sql

How to drop database table if it exits in sql mobile version?

Hi,

I am using VB.net and Visual Studio 2005 to compile a program for a Pocket PC with Microsoft? Windows Mobile? 2003 Second Edition. I am using sql mobile also.

What I want to complete is like one of the following two queries. But when I tried them in the sql mobile, errors happened. So how can I complete the same function in sql Mobile? Thanks.

USE DBa

if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[Table_a]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop Table Table_a

or

USE DBa

IF OBJECT_ID('dbo.Table_a) IS NOT NULL

DROP TABLE dbo.Table_a

You should issue a query to check if table exists and if it does issue another query to delete it.

Please see SQL CE/Mobile books online for queries (hint: look for Information_Schema.Tables and Drop Table).

|||Thanks a lot, and your suggestion is great