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 ')
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply