sqlsrv install

[leighboz]

Hi,

I'm very new to Winter CMS, so I'm just trying to get it installed and running properly. I'm using Microsoft SQL Server 2019 Express Edition (64-bit).

I've ran into this issue when logging into the backend and clicking on 'Administrators' (url = /backend/backend/users):

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GROUP_CONCAT_D", or the name is ambiguous."

This seems to be caused by a missing custom function to replicate the MySQL group_concat function (dbo.GROUP_CONCAT_D), which I presume should be created during install?

I've manually created the function, which just uses STRING_AGG, and the page is now working, but I'm not sure what caused this error.

My install process:

composer create-project wintercms/winter <directory>

php artisan winter:env

I had already created an empty SQL server DB, so overwrite DB settings in .env with:

DB_CONNECTION=sqlsrv
DB_HOST=<my host name>
DB_PORT=
DB_DATABASE=<my db name>
DB_USERNAME=
DB_PASSWORD=

And finally:

php artisan winter:up

[rnrkrft]

No user , no password?

[MegaMindVisionary]

Any Update on this issue?? I also Found same error when try to change password.

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GROUP_CONCAT_D", or the name is ambiguous. (SQL: select top 20 [backend_users].*, (select dbo.GROUP_CONCAT_D(name, ', ') from [backend_user_groups] inner join [backend_users_groups] on [backend_user_groups].[id] = [backend_users_groups].[user_group_id] where [backend_users].[id] = [backend_users_groups].[user_id]) as [groups], (select name from [backend_user_roles] where [backend_users].[role_id] = [backend_user_roles].[id]) as [role] from [backend_users] where [backend_users].[deleted_at] is null order by [login] desc)

[bennothommo]

This seems to be a holdover from the October version we forked from - our docs state:

When using the SQL Server database engine, you will need to install the group concatenation user-defined aggregate.

However, that link no longer exists. I assume, however, that it was doing exactly what @leighboz did and used STRING_AGG as a replacement.

We'll have to fix this in core, as we use GROUP_CONCAT for lists as well. @leighboz - would you be able to post your custom function as a workaround in the mean time? That would be awesome if so :)

[MegaMindVisionary]

STRING_AGG need to add inside database or inside coding query???

[bennothommo]

I'm not sure unfortunately, I've never used SQL Server myself. I would imagine that a user aggregate can be defined in the database config or globally in the database - I wouldn't think you'd have to define it every query.

[bennothommo]

@MegaMindVisionary I've found the following repo which seems to contain the group concatenation aggregate that our original link had:
https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

Hopefully that will be able to give you some idea on what to do to install the command for SQL Server.