InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:35:33 GMT20http://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspx[quote]In relation to the convert, it actually did work. I thought this would work as my perception of running a linked server to MySQL still means I am making the query on MySQL directly.[/quote]Correct.Wed, 19 Dec 2012 14:28:53 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspxPhil I used as xxx, that did the trick.In relation to the convert, it actually did work. I thought this would work as my perception of running a linked server to MySQL still means I am making the query on MySQL directly. My use case is j need to pull metrics and when I try and import the database into execl it crashes so I had to link the server and use SQL server to prevent a crash.Wed, 19 Dec 2012 13:53:40 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspxdont forget to use dateadd(s, 875996580, '1/1/1970') for from_unixtime.convert_tz function does not exist in sql server so best bet is using aux table or datetimeoffset datatype.Wed, 19 Dec 2012 13:28:14 GMTAlexSQLForumshttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspx[quote][b]Brad Marsh (12/18/2012)[/b][hr]Perfect - now getting Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "name" is a duplicate.Owh the joys of mySQL and MSSQL....** No need for a reply to this just passing comment... ranting really.Thanks for the help![/quote]Presume you got this sorted via use of column aliases on the 'name' columns?Wed, 19 Dec 2012 01:14:31 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspxPerfect - now getting Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "name" is a duplicate.Owh the joys of mySQL and MSSQL....** No need for a reply to this just passing comment... ranting really.Thanks for the help!Tue, 18 Dec 2012 15:37:23 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspxSingle quotes within single quotes may be causing issues. Try this instead:[code="sql"]select *from openquery([connection], '-- Forum Messages by Useruse databasebname;select u.name, convert_tz(from_unixtime(m.modificationDate/1000),''+00:00'',''-08:00'') as TimePST, m.subject, m.body, m.messageID, f.forumID, f.namefrom jiveMessage m inner join jiveUser u on m.userID = u.userID inner join jiveForum f on m.forumID = f.forumIDwhere from_unixtime(m.modificationDate/1000) > date_sub(now(), interval 1 week)and u.name like ''Luke''order by m.modificationDate desc ')[/code]Tue, 18 Dec 2012 10:42:14 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1397502-338-1.aspxSelect * from openquery([connection],'-- Forum Messages by Useruse databasebname;select u.name, convert_tz(from_unixtime(m.modificationDate/1000),'+00:00','-08:00') as TimePST, m.subject, m.body, m.messageID, f.forumID, f.namefrom jiveMessage m inner join jiveUser u on m.userID = u.userID inner join jiveForum f on m.forumID = f.forumIDwhere from_unixtime(m.modificationDate/1000) > date_sub(now(), interval 1 week)and u.name like 'Luke'order by m.modificationDate desc ')The error I am getting is:Msg 102, Level 15, State 1, Line 5Incorrect syntax near '+'.Any help would be awesome.Mon, 17 Dec 2012 19:20:50 GMTBrad Marsh