1

The problem I have is that I can not visualize any row with the following query:

SELECT * FROM OPENQUERY ([LINK_MYSQL], 'SELECT * FROM mysql.vw_bd_date;')

The query did not show any error except that it did not throw me any lines either. The consulted view found in MySQL is the following:

create view vw_bd_date
as
SELECT `information_schema`.`TABLES`.`TABLE_SCHEMA` AS `base_de_datos`,MIN(`information_schema`.`TABLES`.`CREATE_TIME`) AS `FECHA_CREACION` 
FROM `information_schema`.`TABLES` 
WHERE ((`information_schema`.`TABLES`.`TABLE_SCHEMA` NOT IN ('information_schema','sys','mysql','performance_schema','test','test\\_%')) 
AND (`information_schema`.`TABLES`.`DATA_LENGTH` IS NOT NULL) AND (`information_schema`.`TABLES`.`INDEX_LENGTH` IS NOT NULL)) 
GROUP BY `information_schema`.`TABLES`.`TABLE_SCHEMA`

This is the use in a much larger query where I do the join with other views to perform a database inventory, but also as commented, the fields in this particular view throw me NULL.

We tried to do all kinds of cast without success.

Any idea would be very helpful.

Thank you

10
  • The default schema in MySQL is mysql? I'm not a MySQL DBA, but figured I'd ask since it's not specified in the view definition. Feb 13, 2019 at 1:50
  • The view is created in the database mysql, but as you can see, said view queries the information_schema.
    – Fran.J
    Feb 13, 2019 at 2:31
  • Could it be that the NULLs come from partitioned tables?
    – Rick James
    Feb 13, 2019 at 2:37
  • 1
    @JohnEisbrener - MySQL does not have a .dbo.. The syntax is dbname.tablename.colname (with suitable defaults in some cases). So information_schema is the "database name" aka "schema" in MySQL terminology.
    – Rick James
    Feb 13, 2019 at 2:40
  • As for FROM mysql.vw_bd_date -- You should not declare anything (eg, this VIEW) in the mysql database.
    – Rick James
    Feb 13, 2019 at 2:42

2 Answers 2

0

As I'm obviously not even close to proficient in MySQL, my answer won't really focus on that side of your question. If the view returns what you are expecting from within MySQL, I suspect the issue is on the SQL Server side. What driver are you using? If it's the built-in Microsoft driver for MySQL, I would strongly recommend downloading the MySQL Driver from their site and installing that as a new provider on your SQL Server. When connecting to other RDBMS systems, I have had significantly better luck using the driver provided by the vendor of that application rather than using the generic driver provided by Microsoft.

0

I have found that using openquery requires using UPPERCASE for the object names and field names for the remote database.

This does not work for me:

select * From openquery(kace,' select *  FROM hd_ticket 
        where ID in (93,105,124,146,148) ')

While this query does:

select * From openquery(kace,' select *  FROM HD_TICKET 
        where ID in (93,105,124,146,148) ')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.