locked
Database test with Cross database requests RRS feed

  • Question

  • Hello,

    I'm trying to write database test for stored procedure with cross database query. In Pre-test I'm tyring to fill in required data but it seems that I can't access tables in not target database. So I have store procedure in Target_Database and need to prefill some data in Another_Database.dbo.My_Table. I'm getting following error:
    System.Data.SqlClient.SqlException: Incorrect syntax near '$(Another_Database)'.

    I checked if I can use direct name like SELECT * [Another_Database] but got the same error.

    So is there any way how I can do this?

    Monday, June 1, 2015 6:07 PM

Answers

All replies

  • Hi g.Andrew,

    Thank you for posting in MSDN forum.

    According to your description, you mean that you create a Test Project for Database Unit Testing like the following MSDN document, am I right?

    https://msdn.microsoft.com/en-us/library/aa833169(v=vs.100).aspx

    If yes, from the error message:" System.Data.SqlClient.SqlException: Incorrect syntax near '$(Another_Database)'.

    Could you please share us detailed code about how you write database test for stored procedure with cross database query?

    If no, please share more detailed message about your issue so that we will further help you support it.

    Thanks for your understanding!

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 3, 2015 6:35 AM
  • Hello,

    you are right I'm creating unit test following steps from link you provided.

    I spent some more time playing with unit test but still no success. Now I have following code:

    USE [Another_Database]
    DISABLE TRIGGER dbo.trMyTable_History ON dbo.MyTable
    GO
    INSERT INTO Another_Database.dbo.MyTable ...
    And I'm getting following errors:

    Executing pre-test script...
    Sql Error: 'Incorrect syntax near 'DISABLE'.' (Severity 15, State 1)
    Sql Error: 'Incorrect syntax near 'GO'.' (Severity 15, State 1)

    Thursday, June 4, 2015 2:46 PM
  • Hi g.andrew,

    Thanks for your reply.

    I am trying to involve some one familiar this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 5, 2015 8:00 AM
  • Hi,

    Do you add any test conditions in your pre-test? For your current scenario you can add some test condition based on your requirement and set the Configuration of the test condition to target to another database. Then in your Test file you can call objects from that database.

    Please check the following articles (although they are written for VS2010, works for VS2012 or VS2013 as well) on how to work with Test Conditions:

    https://msdn.microsoft.com/en-us/library/aa833423(v=vs.100).aspx
    https://msdn.microsoft.com/en-us/library/aa833242(v=vs.100).aspx

    Also a MSDN walkthrough on how to create an database unit test: https://msdn.microsoft.com/en-us/library/aa833283(v=vs.100).aspx

    Thanks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Tina-Shi Wednesday, June 10, 2015 5:39 AM
    • Marked as answer by Tina-Shi Thursday, June 11, 2015 8:26 AM
    Monday, June 8, 2015 7:54 AM