1. Fernando Santos
  2. Valentina Studio
  3. 火, 2月 18 2020, 12:45 PM
  4.  メールで購読
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