OPENQUERY

[mayu03]

here is basic OPENQUERY statement, however if I use where close with conditional statement
syntax is incorrect. How do I fix this issue?
SELECT *
FROM OPENQUERY(IMGSPE, 'SELECT * from table1)
My query:
SELECT *
FROM OPENQUERY(IMGSPE, 'SELECT * from table1 where name='test'' )

 

[dan1967]

If you want to put single quotes inside of your OPENQUERY SELECT statement, you need to put double single quotes:

SELECT *
FROM OPENQUERY(IMGSPE, 'SELECT * from table1 where name=''test''' )

This tells SQL that the first single quote does not close the string, but rather it should insert a single quote in this space. (Could I have typed "single quote" more?)


Dan.

 

[mayu03]

I am getting this error after I change to be a correct syntax
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'net\sqlexec'. Reason: Not associated with a trusted SQL Server connection.

 

[dan1967]

That tells me that you don't have adequate permissions to access the server mentioned in you OPENQUERY statement.

Dan.

 

[mayu03]

I do have appopriate permission. The problem exists only when I run query from my desktop(query analyzer). However when I run the same qeury on a server (using my user id) it is running fine.
Thanks

 

[dan1967]

Make sure when you are running from your desktop in Query Analyzer that you are connected to a server that has the OPENQUERY database server linked to it. I was able to run a simple query using this method when connected to a SQL server that was linked to the server I was querying.

Dan.

 

[mayu03]

I have a link server set, however when I am trying to see the tables in that server I get that error:
Login failed for user 'net\sqlexec'. Reason: Not associated with a trusted SQL Server connection.

 

[dan1967]

Try fully qualifying the table you are trying to see:

SELECT *
FROM OPENQUERY(LinkedServer, 'SELECT * from ServerName.Database.Owner.Table1 where name=''test''' )

Dan.

 

[mayu03]

Here is my query.
SELECT *
FROM OPENQUERY(LinkedServer, 'SELECT * from LinkedServer.test.dbo.Table1 where name=''test''' )
Still have this issue:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'server\sqlexec'. Reason: Not associated with a trusted SQL Server connection.

 

[dan1967]

This is my last idea. Try giving your table an alias:

SELECT *
FROM OPENQUERY(LinkedServer, 'SELECT * from LinkedServer.test.dbo.Table1 t
where t.name=''test''' )

Dan.

 

[mayu03]

Did not work... :O(

 

[dan1967]

Sorry I couldn't help more. Maybe someone else will add their ideas.

Dan.

 

[TonyKhela]

I think this is a security account mapping issue.
When you setup a Linked Server, you have to specify how the local login maps with the remote login (see security tab in the Linked Server properties). Ensure that the login you are using when connecting from your Desktop maps correctly to a valid login on the remote server.

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, then self mapping will not work for Windows Authenticated logins. Therefore, you need to set up a local login mapping from a Windows Authenticated login to a specific login on the linked server. In this case, the remote login will be a SQL Server Authenticated login.

Your remote server may be in 'Windows Only' authentication mode.

Hope this helps. Do let me know if it doesn't.

TK