Month: February 2014

Simple sample in how to use Table Valued Parameter which is a new feature in SQL Server 2008. I found it very useful to pass bulk data from one SP to another SP

CREATE TYPE JobQueueBroker AS TABLE (JobID INT NOT NULL, UpdateDate DATETIME DEFAULT(GETDATE()))
GO

CREATE PROCEDURE [dbo].[Jobs_JobX_SubmitQueueBulk]
@Jobs JobQueueBroker READONLY
AS

BEGIN
DECLARE @Message XML

SELECT @Message = ( SELECT * FROM @Jobs
FOR XML PATH(‘Job’),
TYPE
);

— Above will fomulate valid XML message
DECLARE @Handle UNIQUEIDENTIFIER ;

— Dialog Conversation starts here
BEGIN DIALOG CONVERSATION @Handle FROM SERVICE ServiceJobXJobFinishedProcessing TO SERVICE ‘ServiceJobXJobUpdate’ ON CONTRACT [JobContract] WITH ENCRYPTION = OFF ;
SEND ON CONVERSATION @Handle MESSAGE TYPE JobDetails (@Message) ;

END
GO

DECLARE @Jobs JobQueueBroker
INSERT @Jobs VALUES (1, GETDATE())
INSERT @Jobs VALUES (2, GETDATE())
INSERT @Jobs VALUES (3, GETDATE())
EXEC dbo.[Jobs_JobX_SubmitQueueBulk] @Jobs
GO

Just in case if in your development environment has a hardcoded drive mapping path in your config file and you don’t have the drive exists in your local

The workaround is run the command below

C:\Windows\System32\SUBST D: C:\

And run SUBST after to confirm

And then you can access it in command line and mapped it in TFS will work as well BUT it won’t appear on Windows Explorer.

Shutdown your computer will remove this virtual mapping. So I recommend you to create a BATCH file and put it as startup (you can put the batch file on this location "C:\Users\{User Profile}\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup")