Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
Add a reply View Replies (16)

Resolved BUG - Problems with MSSQL driver [9.4 and 10]

  1. Fernando Santos
    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)
error-valentina-studio-mssql-serveradmin.png error-valentina-studio-mssql-serveradmin.png
mssql-server-sqlectron.png mssql-server-sqlectron.png
  1. SOLVED!
  2. FIXED
  3. valenta studio
Comment
There are no comments made yet.
Responses (16)
Sergey Pashkov
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)
Screenshot 2020-02-18 at 10.31.19 PM.png Screenshot 2020-02-18 at 10.31.19 PM.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Fernando Santos
Fernando Santos Accepted Answer
Hey, thanks for the reply Sergey.

Sharing two screenshots as follow below.


  • Connection


  • Schema editor
Attachments (2)
mssql-remote-connection.png mssql-remote-connection.png
db-mssql-schema-editor.png db-mssql-schema-editor.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov
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)
Screenshot 2020-02-20 12.20.37.png Screenshot 2020-02-20 12.20.37.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Fernando Santos
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)
running-queries.png running-queries.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Fernando Santos
Fernando Santos Accepted Answer
Sharing the Query Log and Query Text.
Attachments (2)
query-log.png query-log.png
query-text.png query-text.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Fernando Santos
Fernando Santos Accepted Answer
Sorry, my mistake. That query was incomplete.

Sharing the correct one as follow below.
Attachments (1)
query-datanase_id.png query-datanase_id.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Sergey Pashkov
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
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)
collation-conflit.png collation-conflit.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Fernando Santos
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
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
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)
query-worked-mssql2008r2.png query-worked-mssql2008r2.png
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
Sergey Pashkov
Sergey Pashkov Accepted Answer
Thank you, then we'll apply this fix in the next version
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
Fernando Santos
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Sergey Pashkov
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Fernando Santos
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 15
Fernando Santos
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 16
  • Page :
  • 1


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