1. Fernando Santos
  2. Valentina Studio
  3. Вторник, Февраль 18 2020, 12:45 PM
  4.  Подписаться через email
Problems with MSSQL driver.

Hi, there.
I'm facing an issue with MSSQL's databases. A can connect normaly, but I can't seed de DB with the tables. Error: Can't open database(s): DATABASE. You can see from the image error-valentina-studio-mssql-serveradmin.png


It's wired 'cause I can get the the dbo schema and database, also tables using Sqlectron. You can see from the image mssql-server-sqlectron.png

Version: 9.4 and 10.
SO.: Windows, MacOs X and Linux

That's definitely a bug.

Need some help here.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Fernando,

You attached the Server Admin screenshot.
Could you please add a screenshot of the Schema Editor after you connect to the server, with this connection selected, so we can see the instance details? Like on my screenshot.
Вложения
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Hey, thanks for the reply Sergey.

Sharing two screenshots as follow below.


  • Connection


  • Schema editor
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
What if you open the Query Log on the top toolbar (or Tools->Query Log from menu), do you see an error for a query like "SELECT database_id .... "?
Вложения
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Well, got an error: Invalid column name 'database_id'.

But I can run a query to show data, like the image attached. That is a Bug. The server is running SQL Server 2008r2.
Вложения
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Sharing the Query Log and Query Text.
Вложения
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Sorry, my mistake. That query was incomplete.

Sharing the correct one as follow below.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Thank you, unfortunately, can't reproduce it in my environment for now.

Could you please do the last test - execute an exact query? Maybe some problems with access to the referenced system tables.


SELECT database_id AS ID,
CONVERT(NVARCHAR(128), DB.name) AS 'Database',
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFilesCount,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataSize,
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS CreationDate,
-- last backup
ISNULL((SELECT TOP 1
CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS LastBackup,
DB.user_access_desc AS 'AccessMode'
FROM sys.databases DB
WHERE DB.name <> 'master' AND DB.name <> 'msdb' AND DB.name <> 'model' AND DB.name <> 'resource' AND DB.name <> 'tempdb'
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Got another error Sergey, "Collation Conflit"

As you can see from the image below.

:o :o :o :o :o :o :o
Вложения
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
So any solution for this: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.


?????
:( :( :( :( :( :( :(
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hi Fernando,

Could you please try to execute the following?


SELECT database_id AS ID,
CONVERT(NVARCHAR(128), DB.name) AS 'Database',
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFilesCount,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataSize,
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS CreationDate,
-- last backup
ISNULL((SELECT TOP 1
CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name COLLATE DATABASE_DEFAULT = DB.name COLLATE DATABASE_DEFAULT ORDER BY backup_set_id DESC),'-') AS LastBackup,
DB.user_access_desc AS 'AccessMode'
FROM sys.databases DB
WHERE DB.name <> 'master' AND DB.name <> 'msdb' AND DB.name <> 'model' AND DB.name <> 'resource' AND DB.name <> 'tempdb'


It contains COLLATE operation to make collation equal in this query. If it works we can make such adjustment in Valentina Studio code.
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Yeah!! It works Sergey.


;) ;) ;) ;) :D :D :D :D :p :p

Hi Fernando,

Could you please try to execute the following?


SELECT database_id AS ID,
CONVERT(NVARCHAR(128), DB.name) AS 'Database',
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFilesCount,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataSize,
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS CreationDate,
-- last backup
ISNULL((SELECT TOP 1
CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name COLLATE DATABASE_DEFAULT = DB.name COLLATE DATABASE_DEFAULT ORDER BY backup_set_id DESC),'-') AS LastBackup,
DB.user_access_desc AS 'AccessMode'
FROM sys.databases DB
WHERE DB.name <> 'master' AND DB.name <> 'msdb' AND DB.name <> 'model' AND DB.name <> 'resource' AND DB.name <> 'tempdb'


It contains COLLATE operation to make collation equal in this query. If it works we can make such adjustment in Valentina Studio code.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Thank you, then we'll apply this fix in the next version
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Awesome!!
:D :D :D :D

Thank you, then we'll apply this fix in the next version
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Fernando,

A new version is available for download. Could you please try it?
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Yes, sure!!!

Hello Fernando,

A new version is available for download. Could you please try it?
Комментарий
There are no comments made yet.
Fernando Santos Ответ принят
Sergey, thanks for the fix.

Everything is working. I can list all database from SQL Server 2008r2.

Thank again.

:D :D :D :D :D :D :D
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories