Post reply

Excel report issue after Datawarehouse migration

  • June 27, 2024 at 5:37 pm

    #4431948

    Hello,

    I inherited data warehouse which was running on SQL server 2012 and dbs in 2008 compatibility mode. After sincere efforts of recreating all packages/cubes/jobs we went live this morning. Everything worked great except we found business user ( Finance director  ) has set up some key reports on his laptop that connects to tabular db.

    Details:

    This is a names instance. We have changed the instance name. So, even though we updated DNS to connect to new instance, because of different instance names, these reports have stopped working. Developers from the old team are long gone and I do not have expertise in Excel reports. So, I thought of creating alias with old instance name on 2022 instance with the hope that connection strings may start working. SQL server 2022 now goes for OLE DB and not TCP/IP.

    Has anyone successfully created alias on 2022? I wish Microsoft wouldn't have taken away this ability, but it is what it is. Any help or guidance on creating alias will be appreciated. or if anyone knows how we can update the connector to use new instance name, taht would be great as well. Waiting for approvals to share screenshots here of connection. Will share as soon as I can. But if you know a way to create an alias with old instance name to divert report to new instance will be great.

  • June 27, 2024 at 6:12 pm

    #4431951

    Provide error message as text masking any identifying information.

  • June 28, 2024 at 2:17 am

    #4432042

    @Ashini:

    1. Define the DNS record.

    It looks like step 1 is already completed.

    2. Run MMC.exe from the Run command. It opens console.

    Go to File, Add remove Snap-in, Select Sql server configuration Manager.

    Define your alias now.

    SQL-Alias

    Once the defining process completes, Restart the SQL Instance service including Browser service.

    3. Use the command prompt, ping the SQL server with its alias name and not IP. (this should respond with the IP address of the SQL server instance)

    Also go to the user machine and open the hosts file and add the IP address and alias name of the SQL server. Reboot the machine once, test if s/he can run the reports thru MS Excel.

    Hope this helps.

    =======================================================================

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply