Simple Examples of SQLCLR by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express
I want to do SQLCLR by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express and I have read the following articles and books:
(i) Connecting to SQL Express User Instances in Management Studio in http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-insta...
(ii) Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition in http://www.microsoft.com/technet/sql/2005/mgsqlexpwssmse.mspx
(iii) Chapter 16 - Going Beyand Transact-SQL: Using the SQL Common Language Rutime (SQLCLR) in Microsoft SQL Server 2005 Express Edition for Dummies
(iv) Chapter 21 - Working with the Common Language Runtime in Microsft SQL Server 2005 Programming for Dummies
(v) Chapter 4 - Introduction to Common Language Runtime (CLR) Integration in Programming SQL Server 2005 by Bill Hamilton.
I want to create an SQLCLR project "HelloWorld" by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express. But I am still not sure how to get it started, because I do not understand the following things:
(1) Pipe Name for a User Instance, (2) Enabling (or Disabling) the CLR by using Transact-SQL, (3) Creating a Transact-SQL script, (4) Creating an Assembly, (5) Creating a backup device, etc. I need to see some simple examples of SQLCLR by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express. Please help and tell me where in the websites I can find them.
Thanks in advance,
We'll have to take this one step at a time because you post basically says that you've already read all the documentation that explains how to accomplish what you're asking about and you still can't do it. Let's start at the beinging.
What specific problems are you having connecting to a User Instance using the instructions at http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx? The pipe name for the User Instance is stored in the field instance_pipe_name, you need to copy this value and then use it to open a new connection in management studio to that specific named pipe.
Once you can get this open we can proceed to the next step.
Hi Mike, Thanks for your response.
This morning, I read your http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx and I still do not know what "field instance, parent instance and main instance" are, and how to follow your instruction "The pipe name for the User Instance is stored in the field instance_pipe_name, you need to copy this value and then use it to open a new connection in management studio to that specific named pipe". So I studied the article "SQL Server 2005 Express Edition User Instances" by Roger Wolter (Nov., 2005) in http://msdn2.microsoft.com/en-us/library/bb264564.aspx again and I downloaded SQL Server Express Utility (Version 1.02130) for me to use in order to get the pipe name, etc. Frankly speaking, I am still not sure how I can get the field instance_pipe_name and the pipe name for the User instance. Could you please kindly give me the detailed instructions to get the field instance_pipe_name and the pipe name for the User instance?
I studied "Dynamic Management Views and Funtions" and "How to: Connect to a User Instance" in SQL Server 2005 Books Online (September 2007) and figured out what you instructed me about getting the pipe name for the User Instance. In SQL Server Management Studio Express, I created the following:
SELECTowning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances;
After I executed it, I got: \\.\pipe\164781XY-C73E-4C\tsql\query.
In Object Explorer, I highlighted my ComputerName\SQLEXPRESS(SQL Server 9.0.1399) and right clicked it and clicked Connect, and the 'Connect to Server' dialog box showed up, I pasted the named pipe value, as shown:
I clicked on the "Connect" button, I got the following error: Cannot connect to np:\\pipe\164781XY-C73E-4C\tsql\query.
Additional information: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 2).
I am lost now. Please help and tell me what I did wrong and how I should do to get it right.
In the Step 5 of "How to: Connect to a User Instance", it states: Create a new database connection, In Object Explorer, click Connect, and then click Database Engine. In the 'Connect to Server' dialog box, past the named pipe value, preceded by np:, as shown: np:\\.\pipe\3C3DF6B1-2262-47\tsql\ query. How can I create a new connection, before I pasted the named pipe value into the 'Connect to Server' dialog box?
One of the things I tried to explain in the blog post, but apparently not clearly enough, is that you can only connect to a User Instance from SSMS if it is already running. SSMS is not capable of starting a User Instance, you must do that using some other method. The two methods I recomend are:
If you're working with a database that is embedded in a VS project, just open your project in VS, open the Database Explorer and then refresh the connection to your database, which should be listed. Once your project database is shown as connected, the User Instances has been started and you will be able to connect to it in SSMS.
Alternately, you can download SSEUtil and use that. Open a command prompt into the directory where you have SSEUtil stored and run the command "SSEUtil -l". This will start the User Instance and list all the databases currently attached to that instance. Once the User Instance is started, you will be able to connect to it in SSMS.
Do either step, you don't need both, and then you should be able to connect in SSMS. The problem you are running into is that User Instances are automatically shut down after 60 minutes without any activity in order to release system resources.
Hi Mike, Thank you very much for your response and valuable information.
In the last several weeks, I learned how to use SSEUtil, Database Explorer in VB Express and Object Explorer in Database Engine of SQL Server Mangement Studio Express. I think I am more familiar with SSUtil and its usage in attaching databases for User Instances. But, I am still confused with the databases attached to the Database Explorer in VB Express and the Object Explorer in Database Engine of SQL Server Mangement Studio Express: (1) I just learned from Marcin Policht's articles in http: //www.databasejournal.com/features/mssql/articles.php/3706081/ and /3709171/ how to use the Data Source Configuration Wizard to put the AdventureWorksLT_Data.mdf file to the Database Explorer of my VB Express project. (2) Some time ago, I used to attach the Nortwind database to the Object Explorer in Database Engine of SQL Server Mangement Studio Express, and I practiced some examples of SqlConnection programming from some books (such as "the "Northwind" database that is embedded in my VB Express project) and I found the content of the "Northwind" database is not in the Database Engine of SQL Server Mangement Studio Express. Why did I get this strange thing of missing "Northwind" content? Why did the databases from the Object Explorer of Database Engine of SQL Server Mangement Studio Express have the different contents? Please help and advise me to understand this matter.
I am still not able to get a simple "Hello-World" SQLCLR started in SQL Server Express User Instance in the Parent Instance via VB 2005 Express. The complicated Assembly, Stored Procedure, etc. for SQLCLR that I saw from a couple of books are too hard for me to follow. Is any simple tutorial example in the internet I can use for learning SQLCLR? Please enlighten me in this area.