Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mflammia
Starting Member
44 Posts |
 Posted - 2008-07-31 : 13:53:30
|
| Hi,Recently created a linked server that has connected fine, the problem I am getting is that running queries only returns the table headings but not data (0 row(s) affected) – any ideas?Thinking it maybe a login / security role issue but have set to Public, Serveradmin and Sysadmin.Many thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-31 : 13:55:52
|
| Dont think its a security issue if you're getting table headings. Is there some accompanying error? also have you checked if data exists in table you're querying against? also are you sure you're loking at correct table? there can be chance that table is of different owners.have you checked that as well? |
 |
|
|
mflammia
Starting Member
44 Posts |
Posted - 2008-07-31 : 14:13:13
|
| No error, it executes without a problem.I have basically linked MySQL on my website with a local MS SQL Server, so I know there is definitely data there because the website returns it.Maybe for that reason I do have different owners but I have very limited knowledge and not sure how to check or even how to correct? |
|
|
|
alanhuro
Starting Member
34 Posts |
Posted - 2008-07-31 : 15:25:26
|
| Check your syntax make sure the date is correct. It is very picky with the quote so you need to really pay attention to those. Here is an exampleDeclare @OracleCall varchar(8000)Declare @Date1 as varchar(20)Declare @Date2 as varchar(20)set @OracleCall = 'SELECT * FROM OPENQUERY(TEST , ''SELECT To_char(START_DT, ''''YYYY-MM''''),SUM(DURATION)/3600, count(distinct(To_char(START_DT, ''''YYYY-MM-dd'''')))*24 FROM TestTable WHERE (START_DT'set @OracleCall = @OracleCall + ' BETWEEN TO_DATE (''''' + @Date1 + ''''',''''YYYY-MM'''') AND TO_DATE (''''' + @Date2 + ''''',''''YYYY-MM''''))'set @OracleCall = @OracleCall + ' group by To_char(START_DT, ''''YYYY-MM'''') order by To_char(START_DT, ''''YYYY-MM'''') asc'')'PRINT @OracleCallEXEC (@OracleCall) |
|
|
|
|
|
|
|
|
Linked Server Issue