1. Fernando Santos
  2. Valentina Studio
  3. Tuesday, February 18 2020, 12:45 PM
  4.  Subscribe via 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.
Attachments (2)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
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.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Fernando Santos Accepted Answer
Hey, thanks for the reply Sergey.

Sharing two screenshots as follow below.


  • Connection


  • Schema editor
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
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 .... "?
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Fernando Santos Accepted Answer
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.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Fernando Santos Accepted Answer
Sharing the Query Log and Query Text.
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Fernando Santos Accepted Answer
Sorry, my mistake. That query was incomplete.

Sharing the correct one as follow below.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Sergey Pashkov Accepted Answer
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'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Fernando Santos Accepted Answer
Got another error Sergey, "Collation Conflit"

As you can see from the image below.

:o :o :o :o :o :o :o
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Fernando Santos Accepted Answer
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.


?????
:( :( :( :( :( :( :(
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
Fernando Santos Accepted Answer
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.
Attachments (1)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Thank you, then we'll apply this fix in the next version
Comment
There are no comments made yet.
Fernando Santos Accepted Answer
Awesome!!
:D :D :D :D

Thank you, then we'll apply this fix in the next version
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Fernando,

A new version is available for download. Could you please try it?
Comment
There are no comments made yet.
Fernando Santos Accepted Answer
Yes, sure!!!

Hello Fernando,

A new version is available for download. Could you please try it?
Comment
There are no comments made yet.
Fernando Santos Accepted Answer
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
Comment
There are no comments made yet.
  • Page :
  • 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