WorldWideImporters – Must declare the scalar variable “@Crlf”

0

I’ve been doing some testing lately with SSAS, SSIS, SSRS, SQL Server, Azure & Power BI and wanted to use the WorldWideImporters (WWI) sample database as to build an automatically updating analytics dashboard.

After following the instructions posted with the WWI files, I had all the data and was able to build a few reports, however the last date of existing data in the downloaded files was 31 May 2016.  After a little research I found the stored procedure to automatically update the database to the current date.  Unfortunately when I ran the code, an error was thrown from the DataLoadSimulation.DeactivateTemporalTablesBeforeDataLoad procedure.

Must declare the scalar variable "@Crlf".

When I opened the procedure I found that the variable was declared, but for some reason doesn’t work when the procedure is called.  Here is a workaround to enable the procedure to run:

  1. Find the DataLoadSimulation.DeactivateTemporalTablesBeforeDataLoad procedure in SSMS under WorldWideImporters > Programmability > Stored Procedures and right click on it.  Then select Script Stored Procedure as > ALTER To > New Query Editor Window.
  2. Then hit CTRL + F to find  @CrLf, click the arrow in the upper left to enable replace and type in NCHAR(10) + NCHAR(13)  Then click the button to replace all on the right middle of the find dialog.
  3. Lastly, we need to remove or comment out the DECLARE statement for the @CrLf variable which is around line 23.
  4. Then Execute(F5) the query to update the stored procedure.

After these steps, you can run the Code to update the database and the error will be gone.  Be patient, it may take a while.

EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
        @AverageNumberOfCustomerOrdersPerDay = 60,
        @SaturdayPercentageOfNormalWorkDay = 50,
        @SundayPercentageOfNormalWorkDay = 0,
        @IsSilentMode = 1,
        @AreDatesPrinted = 1;

Note: I like to change the IsSilentMode variable to 0 so I can see the progress in the Messages pane.

Leave a Reply