SSDT : External Database Reference Error

Today I face a challenge with SSDT (SQL Server Data Tools). I encounter some errors when I have multiple database references in my SQL Server Database Project in Visual Studio 2013.

The errors that I am facing now:

SQL71561: View: [dbo].[View_1] has an unresolved reference to object [DatabaseB].[dbo].[Table_1].
SQL71501: View: [dbo].[View_1] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [DatabaseB].[dbo].[Table_1].[Column1] or [dbo].[Table_1].[t2]::[Column1].

After googling around, the suggested cause of the errors is I am using 3 part name for the same database and then using the table which comes from different database in my query without having database reference in my project. The actual root cause is the database project itself perform database object validation at the background, and it cannot find the other database.

SELECT t1.*

FROM Database1.dbo.Table_1 AS t1

LEFT JOIN Database2.dbo.Table_1 AS t2

ON t1.Column1 = t2.Column1

Therefore, I have added other database projects that I need into my solution like the following screenshot.

Then, add the required database reference. Note that I do not need database variable, so I left the field empty. The example usage is correctly showing how I should and would use the database reference.

After that, I rebuild my project but I am still getting the same error. I have no choice but to remove the 3 part name for current database and remain the 2 part name as highlighted red in above query.

Finally, no more error has occur and my project is able to be built. Happy! But, later discover that there are more challenges await me.

See my solutions explorer screenshot above, I have more than two databases. I have a lot more complicated queries that need to deal with multiple databases. For example:

In Database1:

SELECT t1.*

FROM dbo.Table_1 AS t1

LEFT JOIN Database2.dbo.Table_1 AS t2

ON t1.Column1 = t2.Column1

In Database2:

SELECT bla bla bla

FROM dbo.Table_1

UNION ALL
SELECT bla bla bla
Database1.dbo.Table_2

So, as you can see Database1 need to add Database2 as database reference and then Database2 need to add Database1 as database reference too. If you go and do that in Visual Studio, you will get the following error:

A reference to library 'Database' cannot be added. 

Adding this project as a reference would cause a circular dependency.

It seem like Visual Studio treat the database reference as assembly reference. Having both database projects referred to each other is considered as circular reference. So, how am I suppose to do now?

I found a workaround but not everyone may accept it. I realize that by adding Data-tier Application Package (*.dacpac) as database reference, Visual Studio will not complain about circular dependency. Therefore, I try to extract dacpac for all the related databases by using SQL Server Management Studio (SSMS).

Just click Next button all the way until the Finish. By default, the Data-tier Application is extracted and stored at C:\Users\[UserName]\Documents\SQL Server Management Studio\DAC Packages\[DBName].dacpac. I would recommend to extract the DAC package files to a centralized location, so that it is easier to retrieve, track and manage the packages later.

While extracting the DAC package file, you may encounter the same error SQL71561 and SQL71501 again.

You have to extract the package manually by using SQLPackage.exe.

The location of the SQLPackage.exe is C:\Program Files (x86)\Microsoft SQL Server\[version]\DAC\bin

Below is the command line that I use to extract dacpac:

sqlpackage.exe /Action:Extract /ssn:. /sdn:Database1 /tf:"E:\DAC Packages\Database1.dacpac"

/ssn = Source server name
/sdn = Database name
/tf = Target file

More parameters info can be found HERE.

After you have all the DAC packages ready in one centralized location, now back to Visual Studio to add them as database reference.

My current setup is:
Database1 has Database2 as reference.
Database2 has Database1 as reference.

No more circular dependency complaint and no more database reference not found error. Also, another advantage of using DAC package as database reference is you need not to create or add other database project which is not developed by you to be included into the solution.

File System Watcher Integrate with Windows Workflow Foundation

Today I want to share how to integrate file system watcher in workflow. There are a few ways to do it. Option 1 (simple way) is whenever there is a file is dropped into a folder, the file system watcher will kick in and spawn a workflow instance to process your file. Option 2 (hard way) is to integrate file system watcher into the workflow which mean you need just 1 workflow instance to wait for file watcher event fire then process the file. I want to share the hard way.

Concept

Here is the flow of the concept. Picture is worth a thousand words.

Challenges

In order to implement the above flow, I need a custom workflow activity to handle the logic. I chose to use NativeActivity is because I need to use the bookmarking feature. Bookmarking can induce idle, I want to keep my workflow instance idle state whenever there is no file exists in the folder.

Next challenge is whenever an instance has gone idle, the activity context will be different when the file system watcher event kick in to wake the instance to resume the process. Therefore, I need a custom workflow instance extension to handle the bookmark resume by implementing IWorkflowInstanceExtension.

Implementation

First, prepare the custom workflow instance extension which can support the resume bookmark functionality.

public class FileReceiveExtension : IWorkflowInstanceExtension

{

    private WorkflowInstanceProxy _proxy;

    public IEnumerable<object> GetAdditionalExtensions()

    {

        return null;

    }

    public void SetInstance(WorkflowInstanceProxy instance)

    {

        _proxy = instance;

    }

    public void ResumeBookmark(Bookmark bookmark, FileInfo file)

    {

        IAsyncResult result = _proxy.BeginResumeBookmark(bookmark, file,

            (asyncResult) =>

            {

                    

            }, null);

    }

}

Next, create a custom NativeActivity then add the custom workflow instance extension into the activity cache metadata.

protected override void CacheMetadata(NativeActivityMetadata metadata)

{

    metadata.AddDefaultExtensionProvider<FileReceiveExtension>(() => new FileReceiveExtension());

    base.CacheMetadata(metadata);

}

Set the CanInduceIdle property value to true.

protected override bool CanInduceIdle

{

    get

    {

        return true;

    }

}

Write the custom activity implementation to create file system watcher and register the necessary event handler.

protected override void Execute(NativeActivityContext context)

{

    //Get the folder path from the activity parameter

    string monitoringPath = context.GetValue(this.MonitoringPath);

    FileSystemWatcher watcher = new FileSystemWatcher();

    watcher.Path = monitoringPath;

    watcher.NotifyFilter = NotifyFilters.FileName | NotifyFilters.LastWrite;

    watcher.Created += watcher_Created;

    watcher.Error += watcher_Error;

    watcher.EnableRaisingEvents = true;

    _extension = context.GetExtension<FileReceiveExtension>();

    _bookmark = context.CreateBookmark(BookmarkResumed);

}

Write the implementation of file system watcher created event.

private void watcher_Created(object sender, FileSystemEventArgs e)

{

    //Resume the bookmark and pass the FileInfo object as activity parameter

    _extension.ResumeBookmark(_bookmark, new FileInfo(e.FullPath));

}

Write the implementation of bookmark resume event.

private void BookmarkResumed(NativeActivityContext context, Bookmark bookmark, object value)

{

    FileInfo file = (FileInfo)value;

    //Call your own file processor method

    //Note: I am using async for better performance (Optional)

    Task task = _fileProcessor.ProcessAsync(file);

    task.ContinueWith((e) =>

    {

        if (e.IsFaulted)

        {

            Console.WriteLine(e.Exception);

        }

    });

    //Create a new bookmark immediately after a file is being processed

    _bookmark = context.CreateBookmark(BookmarkResumed);

}

Draw the workflow with the new custom activity. The Start and Stop receive activities are used to control my file system watcher process by making service call.

Start hosting the workflow service, then call the Start web method to test it out. You only need to call the method once to spawn one workflow instance only. Since I had created the custom file receive activity that accept monitoring path, you can spawn another workflow instance to have another instance of file system watcher which monitor different file path.

If you are interested with the source code, feel free to download it from HERE.

How to Limit Transaction Per Second (tps) in WCF?

Service throttling is one of the very useful features in WCF. You can simply configure your service with a custom service behavior to limit the number of service call, session and instance by using the following configuration:

      <serviceBehaviors>
        <behavior name="MyBehavior">
          <serviceThrottling 
            maxConcurrentCalls="1" 
            maxConcurrentSessions="1" 
            maxConcurrentInstances="1"
          />
        </behavior>
      </serviceBehaviors>

However, the WCF service throttling is affecting service level only. What if you have a requirement that your service should be limited to accept only 3 requests per second? You cannot precisely do the restriction and controlling with service throttling. Therefore, we have to control the transaction limit at the code level instead, the idea comes from Serena Yeoh.

First, we have to create a WCF service. My service setup is as follow:

[ServiceContract]

public interface ISampleService

{

    [OperationContract]

    string GetData(string value);

}

The GetData is one very simple and dumb operation. It just accept whatever string value and then return the same value back to client.

Next, your service has to be singleton. Why? Because I can only control the limit with 1 instance only.

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Single)]

public class SampleService : ISampleService

The following is the method I used to control the transaction limit:

//Lock an object to prevent multiple instance access / execute the following code at the same time

lock (countLock)

{

    //StopWatch timer started in constructor
    //Check if the timer elapsed time is less than 1 second

    if (watch.ElapsedMilliseconds <= 1000)

    {

        //If there are 3 hits in less than 1 second

        if (counter >= 3)

        {

            //Prepare to sleep or wait until the next second time up
            //Calculate the sleep time by using 1 second time minus the elapsed time

            int sleepTime = (int)(1000 - watch.ElapsedMilliseconds);

            counter = 1; //reset back the hit counter

            Debug.WriteLine("Sleep : " + sleepTime + " ms");

            if (sleepTime >= 0)

                Thread.Sleep(sleepTime);

            watch.Restart();

        }

        else

        {

            //The hit is still within the limit, let it pass

            counter++;

        }

    }

    else

    {

        //The elapsed time has exceed 1 seconds

        //Reset the counter and timer

        //Proceed to process

        counter = 1;

        watch.Restart();

    }

}

In order to test my service, I have created a console program to call my WCF service.

class Program

{

    static void Main(string[] args)

    {

        //Parallelly or concurrently hitting my service

        //to test whether the service process more than 3 transactions per second

        Parallel.For(0, 100, i =>

        {

            SampleServiceClient proxy = new SampleServiceClient();

            Console.WriteLine(string.Format(

                "{0} Service call {1} : {2}",

                DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.fff"),

                i,

                proxy.GetData("Test")));

        });

        Console.WriteLine("Press any key to continue...");

        Console.ReadKey();

    }

}

This is the result that I got, notice that there are only 3 transaction per second only even though I used parallel for loop to call my WCF service.

If you are interested with my source code, feel to download it from HERE.

Data Binding in Web Test (Web Service Test)

Today I would like to share how to perform parametrized functional test on WCF web service by using Visual Studio 2012.

Note: Only for Visual Studio Test Professional or Ultimate Edition.

First, create a new Web Performance and Load Test Project.

Then, add a new web service request in your web test.

Open up the properties window for the newly created web service request. Enter your WCF service URL in the Url property.

Add a new HTTP header for your web service request. 

Open the properties window for your newly added header. Select the name as SOAPAction.

Then enter the value for the SOAP action. The value is depending on which service operation that you would like to test. The information can be obtained from WSDL.

Open the properties window for String Body. Select the content type as text/xml. Then, enter the SOAP message into the String Body property. If you do not know how to form a proper SOAP message, here is a shortcut tip for you.

Open up WCF Test Client. It is located at C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\wcftestclient.exe. Or, you can fire up Developer Command Prompt for VS2012 from your all programs list, then enter wcftestclient in the command prompt.

At the My Service Projects, right click it then select Add Service. Enter your WCF service Url, then hit the OK button.

Double click the service operation that you want to test. Fill in all the required values. Then hit the Invoke button.

Click the XML tab at the bottom of the window.

Copy the XML from the Request field. That is the SOAP message that you need to put into the String Body in the web test in Visual Studio.

Go back to Visual Studio, paste the copied XML from the WCF Test Client into the String Body property field. Remove the whole <Action> element from the SOAP Header if the WCF service endpoint is configured with basicHttpBinding which is using SOAP version 1.1.

Hit the OK button, then click the Run Test button to test whether the SOAP message is accepted by the WCF service. You should expect the return is HTTP200 status and expected SOAP message response.

Now, prepare test data for parametrization. Open Microsoft Excel or Notepad to create a comma delimited text file (*.csv). The first row always is the column name, the subsequent rows are the test data.

Go back to Visual Studio, right click the Data Sources folder, click Add Data Source. Enter a data source name, select CSV file, then click Next button. Click the Browse button and choose the CSV file which you had just created with test data.

Open up the String Body property field editor. Replace the value that you want to parametrize with {{DataSourceName.TableName.ColumnName}}.

Open up the Local.testings from the Solution Explorer.

Go to Web Test, choose the radio button "One run per data source row". Click the Apply button, then close the window.

In the web test window, click the Run Test button. You should expect to see multiple run with different request SOAP message.