December 17, 2012 at 7:20 pm
Select * from openquery([connection],'
-- Forum Messages by User
use 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.name
from jiveMessage m inner join
jiveUser u on m.userID = u.userID inner join
jiveForum f on m.forumID = f.forumID
where 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 5
Incorrect syntax near '+'.
Any help would be awesome.
December 18, 2012 at 10:42 am
Single quotes within single quotes may be causing issues. Try this instead:
select *
from openquery([connection], '
-- Forum Messages by User
use 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.name
from jiveMessage m inner join
jiveUser u on m.userID = u.userID inner join
jiveForum f on m.forumID = f.forumID
where from_unixtime(m.modificationDate/1000) > date_sub(now(), interval 1 week)
and u.name like ''Luke''
order by m.modificationDate desc ')
December 18, 2012 at 3:37 pm
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!
December 19, 2012 at 1:14 am
Brad Marsh (12/18/2012)
Perfect - now gettingDuplicate 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!
Presume you got this sorted via use of column aliases on the 'name' columns?
December 19, 2012 at 1:28 pm
dont 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.
December 19, 2012 at 1:53 pm
Phil 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.
December 19, 2012 at 2:28 pm
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.
Correct.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply