FIS Connection String

Hi Naomi

Been having a lot of fun with this myself recently!

What version of SQL have you got? the "Data Source=(local);" bit of the string only works if you've got a "proper" SQL server instance running on your PC. I know in the past, where people are running SQL Express (ie the free version!), updating the string to Data Source=[[PC NAME]]\SQLEXPRESS; has worked (you can find your PC NAME by going Control Panel > System & Security > System), but definitely keen to hear anyone else's advice!!!

We are using SQL express, just tried that new string you supplied and its all working thanks for your help :)  

I am writing this here for my own benefit for when I forget in six month's time.

If you're definitely without SQL, and are therefore having problems with PDSAT as well, here are the steps:

1. Attempt to install PDSAT first
2. You will get an error message:
3. Which will take you to the PDSAT SQL Settings page:
4. Go to the download link https://download.microsoft.com/download/7/c/1/7c14e92e-bdcb-4f89-b7cf-93543e7112d1/SqlLocalDB.msi
5. Install
6. Set the connection string in the above to (localdb)\MSSQLLocalDB, click the little round arrows next to Status and it should connect
7. Load an XML ILR into PDSAT to create all the folders (I'm not sure if this is mandatory, but it's what I did and it worked)
8. Go into FIS and set the connection string to Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ILR2021;Integrated Security=True and test

This worked for me at least!!!

Similar headaches for me the past 2 weeks trying to get this set up on multiple machines.

I was using the Developer version of SQL 19 which FIS was fine with when i changed from (Local) to "Machinename\DatabaseInstanceName" but PDSAT just wouldn't play ball no matter what i did.

the key in the end was to install the 2019 localDB from the link that PDSAT suggests and ensure i restarted the machine after the SQL install so that the SQL instance actually booted up and was visible to PDSAT when the application launched.

I thne just replaced the connection string with (localdb)\MSSQLLocalDB as suggested and it all worked.

the bit that really stumped me was that (localdb)\MSSQLLocalDB wasn't visible in SQL management studio (SSMS or in the Windows Services so it didn't actually look like anything had installed, but connecting directly to (localdb)\MSSQLLocalDB in SSMS allowed me to see the data that PDSAT was using.

Hope that helps,

Ben F.

Thanks Ben! Every little helps I think? [starts humming "you are not alone"]

a bit late to the party but you can also have the sql connection to point to any server that has sql server installed, providing you have access to that server.   SQL server doesn't have to be a local installation on your pc. Our goes direct onto our data warehouse server and all our reports run from there without having to extract data from the fis into another db.

Hi,

Slightly different question, but can you change the database name for the SQL server. I use to be able to specify my own name for the database, but now it seems fixed to ILR2021

Cheers

Simon

Hi Simon,   I have enquired to the developers about this.  I too used to specify the database name, having to deal with 3 ukprns, and now it is fixed and so undoes everything that i had set up this year.  Currently waiting for a response

Response from the SLD team.

You CAN override the database name, the change we’ve made is to support basic connectivity options that a significant number of users struggled with.

We will add a change in to make the ‘change the database name’ simpler by adding back into the new database configuration dialog screen.

In the meantime please do the following:

• Setup your database settings so that it is accessing the correct server.
• Test your connection
• Find the file ESFA.DC.ILR.Desktop.WPF.exe.Config
• Find the line that looks like this: <add key="IlrDatabaseConnectionString" value="Data Source=dct-hal-dev\sqlexpress;Initial Catalog=ILR2021;Integrated Security=True" />
  1. Your server name and connection method may be different
• Where it says Initial Catalog change the value to what ever you wish
• <add key="IlrDatabaseConnectionString" value="Data Source=dct-hal-dev\sqlexpress;Initial Catalog=ILR2021_override;Integrated Security=True" />
• Save the file.
• Run FIS
  1. Your new database name will be present in the connection string
• Run and the new database will be created by the process

Thank you Rhys,

That's really Helpful.

Cheers

Simon

So just received a new computer and setting up the PDSAT. The above worked on my previous computer but it's no longer working on the new one. Trying to work out what's going wrong.

Have installed SQL server 2019 Local DB other programs are able to connect to the server so I know it's running. However PDSAT is not able to make a connection as per step 6 in Steveh's excellent unofficial PDSAT guide. Anyone have any tips?

So just started working. Not sure where I'd gone wrong, I uninstalled all SQL server packages. Then installed

Sql server express basic non developer

https://www.microsoft.com/en-gb/sql-server/sql-server-downloads?rtc=1

Then the SQL server 2019 Localdb linked to in the PDSAT and it came to life with a green tick.

Not sure what step I missed.

Just because I'm about to send this thread to someone, worth pointing out that, since they updated FIS, you only have to put the server name in the box, not all the other stuff