1. Fernando Santos
  2. Valentina Studio
  3. Dienstag, Februar 18 2020, 12:45 PM
  4.  Abonnieren via E-Mail
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.
Anhänge
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Yes, sure!!!

Hello Fernando,

A new version is available for download. Could you please try it?
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Hello Fernando,

A new version is available for download. Could you please try it?
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Awesome!!
:D :D :D :D

Thank you, then we'll apply this fix in the next version
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Thank you, then we'll apply this fix in the next version
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
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.


?????
:( :( :( :( :( :( :(
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Got another error Sergey, "Collation Conflit"

As you can see from the image below.

:o :o :o :o :o :o :o
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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'
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Sorry, my mistake. That query was incomplete.

Sharing the correct one as follow below.
Anhänge
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Sharing the Query Log and Query Text.
Anhänge
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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 .... "?
Anhänge
Kommentar
There are no comments made yet.
Fernando Santos Akzeptierte Antwort
Hey, thanks for the reply Sergey.

Sharing two screenshots as follow below.


  • Connection


  • Schema editor
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
  • Seite :
  • 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