You wanted to use the effect of long exposures.
What effects of long exposure are visible in your image?
Lights are blown out.
For the street lamp on the right, it seems to be ok.
But the neon sign is rather ugly. The name of the restaurant is hard to read.
So the long exposure doesn't work on lights close to you.
The fountain becomes a closed curtain, which is interesting.
There aren't many parts that are affected by long exposure and on some parts it even has an unpleasant effect.
What other things are in the image that could be interesting, beautiful, ...?
There's a statue on the fountain which looks good with the highlights on the dark material.
There's a symmetry to the basin of the fountain, but with the neon lights and the bright ugly building in the back, it will be hard to incorporate it into the image.
You can blur out the background, but it will still be visible.
What other image could be taken at this fountain that uses effects of long exposure?
Get closer. Most of the surroundings should not be part of the image
as they tend to look ugly under these circumstances. Getting closer to the fountain will show less of those surroundings. Place the camera on the edge of the basin. Pointing up, framing the statue and filling the bottom of the image with the curtain of water. The background should be mostly night sky, which is nice and neutral.
If the angles work out, you can try and get the street light in your shot as well, acting as a wrong sun in the background, giving the statue a nice side/back light.
What mode&settings can be used for the fountain shot?
S is good as it let's you choose a shutter speed (a long one), right? Well, yes, it adjusts the aperture (and maybe even iso) so the exposure is "right". But the problem is that a "right" exposure for a camera means a grey one (not too dark, not too bright). Say there's a lot of black night sky in your image. The camera will try to find the average grey exposure and as a result it will probably over expose the rest of the image.
This will also brighten up the sky and it will look mushy from all the surrounding lights from the city.
In your case, a "right" exposure could be an image consisting of a lot of pure black and that's not what the camera is looking for.
In manual mode, you are in control of all the settings. This however requires a bit of knowledge about what those settings do. You say "S prevented me from picking f2.8". This implies that you want to use f2.8 on this shot. This won't work, because f2.8 means the aperture is wide open (probably as wide as your lens can go), which means a lot of light can go through the lens. With that much light, it won't take long before enough light reached the sensor for a good exposure, but you want a long one. That's why the automatic S mode picked f22, which means the aperture is closed and only a bit of light goes through the lens. So the camera takes quite a while untile enough light is captured -> this allows for a long shutter speed.
If this is new to you, it is a great opportunity to learn.
Go ahead and use Aperture priority mode and dial in the f2.8 that you apparently want to use and see what your camera picks as a shutter speed, it will not be a long exposure.
Now dial in f22 and see how much the shutter speed changes.
Both: Long shutter speeds and small f-numbers will make the image brighter, you need something to balance. that's what the S mode did by dialing in f22.
If you want to use f2.8 anyway, you have to darken the image by some other means. Often an ND filter is used to reduce the amount of light, but this is a story for another day.
This is a good stationary subject to experiment with all this. Set up the tripod. Keep two values constant (aperture and iso, for example) and play with the other one. Don't be surprised to see all white or all black images, just keep going until something shows up. You will get a feel for what these values do.
Good luck and have fun