Tony’s Weblog

ASP.NET, MVC, AJAX, SilverLight, C#, WCF Web Service, SQL 2005 and other interesting stuff.

How to Debug Stored Procedure from VS 2008?

Posted by Tony on August 1, 2008

Developers are an inherently finicky group. Most have their favorite tools to complete everyday tasks. This includes text editors as well as IDE’s and even operating systems. While I have favorite tools as well, I am surprised at how little developers know about some of the tools they use everyday. One tool can often replace the functionality provided by numerous smaller programs. A good example is VS.NET (Visual Studio .NET). It provides the ability to develop .NET-based code via languages like C# and VB.NET, but it also facilitates the development of CSS (Cascaded Style Sheets), HTML, XML, and SQL. SQL seems to be the most often overlooked feature, so let’s take a closer look at using VS.NET to work with SQL.

Database connectivity
VS.NET’s Server Explorer feature makes it easy to connect to a database server. While this connection is not restricted to SQL Server, it will be the focus of this article. Figure A shows the Server Explorer on the left side of the VS.NET workspace. It is accessible from the View drop-down menu or the tab on the left edge of the workspace. Right-clicking on the Data Connections node in the Server Explorer provides the option to add a new connection. Figure B shows the dialog presented when this option is selected. The Test Connection button in Figure B allows the connection to be easily tested and verified to be correct.

Figure A
Visual Studio .NET Server Explorer
Figure B
Adding a new server via Server Explorer

Once a connection has been added via Server Explorer, it is available within the Servers node in Server Explorer. The server name appears with its options available under it. The SQL Servers node under the server provides access to the SQL Server and its elements. You are now one step away from debugging SQL code, but additional setup is necessary.

SQL Server setup
SQL Server contains an extended stored procedure that is used to control access to debugging its stored procedures. This stored procedure is named sp_sdidebug, and the following T-SQL may be used to allow a user to debug stored procedures:
USE Master
GRANT EXECUTE
ON sp_sdidebug
TO username

In addition, the user must have rights to the both the database and stored procedure. Once the user has been properly set up, the actual debug process is as simple as right-clicking on the stored procedure within VS.NET’s Server Explorer and selecting Step Into Stored Procedure. Figure C shows this option with an example stored procedure from the Northwind database highlighted. The stored procedure returns the top ten most expensive products from the database.

Figure C
Debugging a stored procedure via Server Explorer

Once the debugging process begins, it is no different than debugging C# or VB.NET code. Figure D shows the previous stored procedure running in debug mode. This option is available via the Debug drop-down menu or with the keyboard shortcut. Here is an overview of the debug-related keyboard shortcuts:

  • F5: Continue
  • Shift + F5: Stop debugging
  • F11: Step into
  • F10: Step over
  • Shift + F11: Step out
Figure D
Stored procedure in debug mode

In addition, breakpoints and watches may be set to monitor the stored procedure in action. The output of the process is available via the lower window within VS.NET (once it has finished running). Figure E shows the VS.NET workspace after running a stored procedure with debug information visible. It says the stored procedure ran and finished with an exit code of zero, which means success.

Figure E
Debug information after running stored procedure

The output may be viewed by selecting Database Output from the drop-down list above the debug window as shown in Figure F. The actual output is visible in Figure F as well.

Figure F
Viewing stored procedure output

Parameters
The stored procedure used up to this point is very simple but not appropriate in the real world. Most real-world stored procedures accept and possibly return parameters. Utilizing these procedures within the debugger is simple; it allows you to add parameter values to see how the code may perform. When a stored procedure that accepts input parameters is debugged, a window is presented to accept the values.

Figure G shows the dialog box that accepts the input parameters. I selected a stored procedure (Sales by Year) that accepts a beginning and ending date value to return sales data. In addition to the dates you may enter, NULL and DEFAULT options may be selected. This allows you to test error handling (with NULL values) and the default settings.

Figure G
Debugging with parameters

A central location
Using VS.NET to develop, maintain, and debug stored procedures makes other programs such as SQL Server’s Query Analyzer unnecessary. This leads to a more focused developer that doesn’t have to switch applications and mindsets during their day. Debugging is a critical aspect of development, so applying a strong developing tool to T-SQL code makes it easier to build robust applications

Posted in Debugging | 1 Comment »

How to remove duplicate rows from a table in SQL Server

Posted by Tony on August 1, 2008

Here is what you need to do:

1. First, run the above GROUP BY query to determine how many sets of duplicate PK values exist, and the count of duplicates for each set.
2. Select the duplicate key values into a holding table. For example:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
3. Select the duplicate rows into a holding table, eliminating duplicates in the process. For example:

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
4. At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query:

SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

should return a count of 1 for each row. If yes, proceed to step 5 below. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the holddups table.

5. Delete the duplicate rows from the original table. For example:

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
6. Put the unique rows back in the original table. For example:

INSERT t1 SELECT * FROM holddups

Posted in SQL 2005 | Leave a Comment »

How to Send Email from SQL 2005

Posted by Tony on August 1, 2008

Use the “xp_smtp_sendmail” system procedure.  Here is a sample:

EXEC master..xp_smtp_sendmail

@TO = ‘tony@alorica.com‘,

@BCC = ‘tony@alorica.com‘,

@from = ‘someone@somewhere.com‘,

@subject = ‘Happy Birthday to you!’,

@message = ‘<B>You belong in a zoo!</b>’,

@server = ‘yourMailServerName’,

@type       = ‘text/html’

Posted in SQL 2005 | Leave a Comment »

Query Table Structure Methods

Posted by Tony on August 1, 2008

Method 1: Query the System tables as follows:

SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype

FROM sysobjects, syscolumns, systypes

WHERE sysobjects.id = syscolumns.id AND

syscolumns.xtype = systypes.xtype AND

sysobjects.name = ‘ServiceRequests’

Method 2: Use the SP_help command

sp_help ServiceRequests

Posted in SQL 2005 | Leave a Comment »

How to convert XML file in to a class file using c#?

Posted by Tony on August 1, 2008

The quick way is to use tools such as xsd.exe or XsdObjectGen.exe.

These tools allow you to quickly generate C# or VB classes from an xsd file.

Here’s what to do:
1) Open an xml file in Visual Studio
2) Create an xsd schema. (‘XML\Create Schema’)
3) If necessary, edit the schema file
4) Open a Visual Studio command window and run one of the tools
5) Edit the generate classes (if necessary)

Example:

Code:
C:\projects\myproject\xsd.exe MyXmlClass.xsd /classes /n:CG.MyNamespace

The above command will generate a MyXmlClass.cs file that you include in your project.

To load up the class, it’s as easy as:

Code:
XmlSerializer serializer = new XmlSerializer( typeof( MyXmlClass ) ); using( XmlReader reader = XmlReader.Create( file ) ) { MyXmlClass myXmlClass = ( MyXmlClass )serializer.Deserialize( reader ); }

Saving data from the class back to an Xml file is equally as easy.

NOTE: Feel free to hand edit the generated files (but understand if you regenerate them, your changes will be lost). I generally use the xsd.exe tool first and then hand edit. Xsd.exe will generate any collections using raw arrays (e.g. myField[]) which I don’t like to use because users of the class need to always check for null in order to loop the items. So I hand edit and replace these arrays with generic List<>’s.

Posted in C# | Tagged: , , | Leave a Comment »

How to Make a copy of a table in SQL Server 2005?

Posted by Tony on August 1, 2008

Use the following script:

select * into newtable from oldtable

Posted in SQL 2005 | Tagged: | Leave a Comment »

June Lake

Posted by Tony on July 28, 2008

Posted in Uncategorized | 1 Comment »

Using a SQL Adapter in BizTalk Server 2004

Posted by Tony on July 28, 2008

Introduction

One of the greatest difficulties I found when I started working with BizTalk 2004 was the lack of documentation about the SQL Adapter. In this article, I’m going to demonstrate how we can use this adapter in an Orchestration of BizTalk.

The Example

To build this example, we’re going to use the Northwind database. We’re going to simulate a hypothetical situation where we receive an XML message as a file, containing the order number, a customer ID, and the date of the order. In the orchestration, we will use SQL Server to search the additional information about the customer, using the SQL Adapter.

Creating the Project

We’ll start this article by creating a new BizTalk Server project in Visual Studio. In the Visual Studio .NET menu, select the “New Project” option, and for the type of project, select “BizTalk Projects”. Select the template “Empty BizTalk Project” and create a project named OrderManager.

Creating the Schemas

Now that we have the project, we’re going to create two maps that we’ll use in the project, one for the input message and one for the output message.

Right-click on the project in the Solution Explorer, and select the “Add New Item” option, then select the “Schema” item. Create a schema named “IncompleteOrder“.

Click on the root element of the schema and change the property “NodeName” to “Order“. After that, right-click on this node and select the “Insert Schema Node” option. Inside this option, select “Child Field Element”. Change the NodeName property of this new node to OrderId. Repeat this operation to create two additional elements named “OrderDate” and “CustomerId“. Your schema should look like this:

Now, we’re going to create the schema with the complete information of the order. Right-click on the project in the Solution Explorer and select the “Add New Item” option. Then, select the item “Schema” and name it “CompleteOrder”.

When the schema shows up, rename the “Root” element to “CompleteOrder“. After that, create the child elements: “OrderId“, “OrderDate“, “CustomerID“, “CustomerName“, “CustomerAddress“, “CustomerCity“, “CustomerCountry“. The complete schema can be viewed in the image below:

Creating the test messages

In order to test our solution, we need to create some test messages. BizTalk Server is capable of creating these messages for us. Right-click on the “IncompleteOrder.xsd” schema in the Solution Explorer and select the “Properties” option. In the Properties, select the property “Output Instance Filename”. In this field, type the value “C:\IncompleteOrder.XML“. Click OK to close the window. Right-click on the schema file again in Solution Explorer and select the “Generate Instance” option. Open Windows Explorer and check if the file was created in the indicated place.

If you check the file created in Visual Studio, you’ll see that the values generated are random. We’re going to modify these values to use some valid information. Replace the OrderId field to the number 1. In the OrderDate field, type the value “2005-03-01”, and in the field “CustomerId“, the value “ALFKI”. The XML file should have a similar structure as the file below:

<ns0:Order xmlns:ns0="http://OrderManager.IncompleteOrder">
  <OrderId>1</OrderId>
  <OrderDate>2005-03-01</OrderDate>
  <CustomerId>ALFKI</CustomerId>
</ns0:Order>

Save the file, we’ll need it later.

Creating the structure to have access to the database

In order to use the SQL Server resources, we’ll need to create a Stored Procedure capable of returning the data from the customer that we will place in the order.

Open the Enterprise Manager of SQL Server and select the Northwind database. Select the “Stored Procedures” applet, and right-click it, select the option “New Procedure”. A new procedure should be created as follows:

CREATE PROCEDURE proc_GetCustomer
(@CustomerId char(5))
AS

SELECT * FROM Customers 
WHERE CustomerId=@CustomerId FOR XML AUTO, XMLDATA
GO

Don’t forget to include the XMLDATA parameter in the end of the procedure, this will generate the necessary information for the SQL adapter. This parameter will be removed later.

Adding the structure to call SQL Server

Now that we’ve created all the necessary structures for the solution, we’re going to create the structure to call SQL Server. For this, we will create a new item generated from the Solution Explorer. Right-click on the project in the Solution Explorer and select the “Add Generated Items” option. In the list of items, select the Add Adapter option and click on the Open button. The following screen will show up:

In this screen, select the adapter of type “SQL” and click Next (the other options can stay with default values, unless the database of your BizTalk server is in a remote server).

In the first screen, click on the Set button and provifde the information to connect to your SQL Server instance. Select “Northwind” as the initial catalog. When the connection string is set, click Next.

Th next screen shows information about the schemas that will be used to transport the information to and from SQL. In the Target Namespace option, type “http://nwtraders“. In the Port-Type, select “Send Port”, since we’re going to send a request to SQL Server and receive a response. In the property “Request root element name”, type “InCustomer”, and in the property “Response root element name”, type “OutCustomer”. The screen should be as in the picture below:

Click on the Next button. In the screen “Statement type information”, select the option “Stored Procedure”. Click Next. In the combo box for selecting the procedure, select proc_GetCustomer. The stored procedure parameters will show up. Click on the first parameter (near the check box… do not check the check box, just click near it until the prompt appears) to enter the parameter information. Type “ANTON”, that is a valid customer ID. Click on the Generate button and you will see that the script used to execute the procedure will show up in the bottom of the screen. The result can be observed as in the image below:

This information will be used by the SQL adapter to generate the initial schema, they are not used later in the project. Click on the Next button, and in the next screen, click the Finish button. A new schema and a new orchestration will be created in your project.

The created schema (SQLService) contains the request and response information for the stored procedure. The orchestration contains some types (port type) used to call the SQL adapter.

Updating the orchestration

Right-click on the orchestration in Solution Explorer and select the “Rename” option. Rename it to “ProcessOrder.odx“.

Open the orchestration file and click on the white area in the orchestration designer (between the red and green indicators that indicate the end and beginning of the process). Check the property windows, and change the TypeName property from Orchestration_1 to ProcessOrderOrch.

Creating the necessary messages

In order to use our messages within the orchestration, it’s necessary to create message variables. To do this, we’ll need the Orchestration View window. Click on the “View” menu, select “Other Windows”, and select the “Orchestration View” window.

In the orchestration view, right-click on “Messages” folder and select the New Message option. In the identification, type “msgIncompleteOrder” and select the “OrderManager.IncompleOrder” and its schema (the schema is available in the schemas item).

Create three additional messages with the following identifiers/schemas:

Identifier Type
msgCompleteOrder OrderManager.CompleteOrder
msgGetCustomerReq OrderManager.procedureRequest (no item multipart messages)
msgGetCustomerResp OrderManager.procedureResponse (no item multipart messages)

Creating orchestration elements

Now, we will create the elements used in the orchestration. In the toolbox, search for the “Scope” shape and drag it to just below the green indicator in the designer area. Rename the scope shape to “Do Updates”. Change its transaction type to “None”.

Now, drag a “Receive” shape from the toolbox to the area inside the scope shape. In the shape properties, set the properties below:

  • NameReceive Incomplete Order
  • MessagemsgIncompleteOrder
  • ActivateTrue

Just below the receive shape, drag a “Construct Message” shape. Use the following properties:

  • Name – Create SQL Request
  • Messages ConstructedmsgGetCustomerReq

Now, drag a “Transform” shape inside the empty area inside the “Construct Message” shape. Select the “Input Messages” property and click the (…) button. A new window will open up with the mapping options. In the “Fully Qualified Map Name” box, type “ProcessOrder.IncompleteOrder_To_SQLRequest”. Click on the “Source” option and select the “msgIncompleOrder” as the source message. Click on “Destination” and select the “msgGetCustomerReq” as the destination message. Your “Transform Configuration” screen should look like the one below. When you finish, click OK.

In the map editor, drag the CustomerID field from the IncompleteOrder schema to the CustomerID field on the GetCustomerReq schema. Note that the destination schema represents the parameters used to call the stored procedure. Your map should look like the picture below:

Save the map and go back to the orchestration file. Change the name of the “Transform” shape to “Create Request”.

Now, we’ll create a “Send” shape that will send our request to the SQL Server Adapter. Drag a new “Send” shape just below the construct message shape and use the following properties:

  • NameSend SQL Request
  • MessagemsgGetCustomerReq

After the Send Shape, create a new Receive Shape with the following properties:

  • NameReceive SQL Resp
  • MessagemsgGetCustomerResp

After the Receive shape, drag a new construct message with the following properties:

  • NameConstruct Response
  • Messages ConstructedmsgCompleteOrder

Drag a new “Transform” shape to our newly created “Construct” shape. Select the property “Input Messages” and click on the (…) button. The transform configuration window will show up again. In the fully qualified name field, type ProcessOrder.SQL_To_CompleteOrder. In the Source option, select the “msgGetCustomerRep” and the “msgIncompleteOrder” (that is, two messages as source). Select the “Destination” option and select the “msgGetCustomerReq” option. The screen should look like the one below:

Click OK and in the map editor, create a map with the following links:

Close and save the map, and go back to the orchestration. Change the name of the “Transform” shape to “Create Response”.

Now, we’ll create a new “Send” shape that will send the final response to our customer. Create a new “Send” shape below the Construct Message shape, with the following properties:

  • NameSend Response
  • MessagemsgCompleteOrder

At this point, your orchestration should look like the one shown below:

Creating the logical ports

Now that we’ve created the shapes for our orchestration, we will create the logical ports. Right-click the “Port Surface” (on the left) of the orchestration and select the “New Configured Port” option. The “New Port Wizard” will show up to create the logical port and port types for us, click Next on the first screen. On the next screen, type “rpReceiveIncomple” at the Port Name box and click “Next”. In the port information screen, make sure the “Create New Port Type” option is selected, and type rpReceiveIncompleteType as the port type name. Leave the other options in their default values and click Next. In the communication direction options, leave the default “I’ll always be receiving messages on this port” option selected, click Next, and the Finish.

Click on the port surface again (now on the right side) and select the “New Configured Port” option again. The wizard will show up again, click Next. On the next screen, type srpGetCustomer as the port name, and click Next. On the next screen, select the “Use Existing Port Type” option and select the “OrderManager.SQLExec” port type. This port-type is created by the SQL adapter and contains the necessary configuration to call our stored procedure. Click Next. In the communication direction option, select the “I’ll always be sending a request and receiving a response” option, click Next, and then Finish.

Now, let’s create our last port (whew!). Click on the “Port Surface” again (right side now) and select “New Configured Port” option. Our “already-known” wizard will show up again… click Next. In the port name box, type spSendCompleteOrder, click Next. In the port information screen, select the “Create New Port Type” option, and in the port type (on the next screen), type spSendCompleteOrderType. On the “Communication Direction” option, select the “I’ll always be sending messages on this port” option. Click Next, Finish, and we’re ready! 🙂

Connecting the ports to the receive/send shapes

To finish our orchestration we need to connect the receive and send shapes to our ports. To do this, simply drag the connectors from each shape to each logical port on the port surface. The orchestration should look like in the image below:

Configuring the assembly

Now that our orchestration is ready, we need to configure our assembly so that it can be used by BizTalk. To do this, we need to generate a pair of keys for it, using the SN.EXE application. In the Start menu, look for the Visual Studio 2003 folder, and select the “Visual Studio .NET 2003 command prompt”. In the command prompt, change the current directory to C:\ and type “sn – k key.snk”. This command should generate our key-pair. In the Solution Explorer, right-click on the project in the Solution Explorer and select “Properties”. In the properties of the project, select the “Assembly” tab, and in the item Assembly Key File, type C:\key.snk, as in the image below:

Now, right-click on the project and select the “Build” option. If there are no errors, right click on the project again and select the “Deploy” option. Our project should be deployed to BizTalk.

Creating the physical ports

Now, we’re going to configure BizTalk to receive the messages and send them to our orchestration. In Visual Studio, select the View menu and select the “BizTalk Explorer” option, a new window will show up with the BizTalk Explorer. Expand your BizTalk server instance name and right-click on “Receive ports”, selecting the “Add Receive Port” option. On the Port Type, select “One-way Port”. In the Port Name box, type rpReceiveOrder and click Ok. You’ll see that our new port will be created.

Now, we’ll create our receive location for this port. Expand the port you have created in the previous step and right-click on “Receive Locations”. In “Transport Type” property, select “FILE”. In the “Address (URI)” option, click on the (…) button and set the receive folder as “C:\Receive” (or the folder of your choice). Click OK. In the Receive Handler property, select “BizTalk Application”, and in the “Receive Pipeline” property, select “Microsoft.BizTalk.DefaultPipelines.XMLReceive“. Click OK to create the receive location. It will be necessary to create the C:\Receive folder, in order to get the solution working. After you create the receive location, right-click on it and select “Enable”.

Now, we’ll create the send port to send information to SQL Server. Right click on the “Send Port” and select the “Add Send Port” option. On the Port Type, select “Static Solicit-Response” port. On the Port Name, type spSQL, and in the Transport Type, select SQL. In the Address (URI), click on the (…) button. Complete the “SQL Transport Properties” screen with the information below. Take care of the “Document Namespace” property and the “Response Root Element Name”. These properties should contain the same namespaces used by the generated schema in the earlier steps.

On the Send Port properties, change the Send Pipeline property to Microsoft.BizTalk.DefaultPipelines.XMLTransmit and the Receive Pipeline property to Microsoft.BizTalk.DefaultPipelines.XMLReceive. Click OK and Finish.

Now, we’ll create the last port. Right-click on “Send Ports” and select the “Add New Port” option. Select “Static One-way port” as the port type, and name it spSendCompleteOrder. In the Transport Type option, select FILE. In the Address (URI) option, click on the (…) button and set C:\Output as the destination (this folder should be created manually). Click OK and set the “Send Pipeline” property to “Microsoft.BizTalk.DefaultPipelines.XMLTransmit“. Click OK to create the port.

Now, right-click on each one of the ports and select the “Start” option.

Binding the Orchestration

Now, we need to bind our orchestration to the physical ports. In the BizTalk Explorer, expand the Orchestrations applet and right-click on our orchestration, selecting the “Bind” option. Set the binding information as in the image below:

On the Host configuration, select the default host (BiztalkApplicationHost). Click OK.

Right-click on our orchestration and click “Start”. Click OK on the Express Start window as well.

Testing the Orchestration

Before we start testing our orchestration… do you remember that stored procedure we created in the earlier steps? Well, in order to get our solution working, we need to change some things on it. Go to the Enterprise Manager and edit the procedure, removing the XMLDATA option in the SELECT statement. This option is used to generate the schemas for BizTalk. Since our schemas are already created, we don’t need this option anymore.

Now, get the input file created in the earlier steps and put it in the C:\Receive folder. The file should be extracted. Go to the C:\Output folder and wait until the complete order message appears. Hope you like the article! 🙂

Posted in BizTalk Server | Tagged: | Leave a Comment »