1

This seems basic, but I am very new to SQL Server. Probably so green that I am not even sure how to find this answer as I may not even be asking it correctly.

I've been tasked with moving an Access database to SQL Server to allow scalability. I have created a local instance, and moved tables without issue.

Now I have to convert the action queries that populated those tables. Access had an ODBC connection to 2 remote SQL servers, and it's own tables locally. The queries that I am moving were easy to design/execute using in Access, but I am challenged with scripting them in SQL. I am connected with the 2 remote servers in SQL Server Management Studio, but trying to reference remote tables for local insert queries has me confused.

I've been attempting the Selects and Joins with this syntax:

[server name].[database name].[schema].[table name]

What I am getting is "incorrect syntax near the keyword 'FROM'.

Again, if this is covered elsewhere, even a link would be helpful. As I said, I am just starting to learn this and may not be searching correctly.

Much appreciated!

Update - Using SQL Server Management Studio 2012. I did manage to add a link to the 2 other servers in the object browser. Now I need to find out how to be able to use both local and remote tables in one query.

INSERT INTO dbo.QUES_ANSWER_LABEL ( ques_data_id, label_id )

SELECT QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label.ques_data_id, QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label.label_id

FROM EVAL_CORE INNER JOIN EVAL_QUES_LNK ON EVAL_CORE.eval_id = EVAL_QUES_LNK.eval_id INNER JOIN QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label ON EVAL_QUES_LNK.ques_data_id = QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label.ques_data_id

WHERE EVAL_CORE.quality_date = CONVERT(DATETIME, '2013-12-02 00:00:00', 102);

This shows my attempt, and the error that I get is:

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label.ques_data_id" could not be bound.

BumpML
  • 105
  • 1
  • 2
  • 6
  • Is the remove server linked to the *local* server, or the one on which you are running the query? – Yuck Dec 20 '13 at 17:26
  • 2
    Show us all of a query that is throwing that error. The error is coming from something around the reference/name. – RBarryYoung Dec 20 '13 at 17:27
  • 'SELECT [eform_id]' 'FROM [DBWS0289].[QF_PLATFORM].[dbo].[eval_forms];' While I am connected to the remote server (in the Object Browser), I may not have linked the server, and it may be my conceptual understanding that is in the way. Does linking require heightened permissions on the remote server? – BumpML Dec 20 '13 at 18:47
  • @BumpML maybe, but that wouldn't have anything to do with a syntax error. I cannot tell what is going on with those quotes/apostrophes in your reply. Please edit your question post and add a complete query, *exactly* as it appears in whatever tool or code you are using to edit and execute the query (and let us know what that tool is). If you can give us enough info to reproduce the problem we can probably solve it in seconds. – RBarryYoung Dec 21 '13 at 16:33
  • @RBarryYoung, thank you. I've added more to the question above to show where I am at. I appreciate your insight! – BumpML Dec 23 '13 at 01:04
  • Well I'm confused. The new query doesn't appear to have any relation to your previous descriptions. Is it still returning '*syntax error near FROM*'? Because there's no way that this should be able to return that error. – RBarryYoung Dec 23 '13 at 04:52
  • @RBarryYoung, I've updated again. I think I am getting closer. The error that I am getting is above with the updated query. I have tried with and without brackets with the same result. – BumpML Dec 23 '13 at 23:15

2 Answers2

0

You can use linked servers to join tables in different instances, take a look at the documentation for more info

http://technet.microsoft.com/en-us/library/ff772782.aspx

or a similar SO answer

SQL Server Linked Server Example Query

Community
  • 1
  • 1
Phil
  • 2,315
  • 2
  • 18
  • 26
  • Thank you, Phil. Does this require increased permissions on the remote server? My attempts to add a linked server have not been successful. – BumpML Dec 20 '13 at 20:00
  • Ideally you would be able to create an account on both servers, or use the SA account if available. – Phil Dec 22 '13 at 18:49
  • I did figure out how to link to the servers. Is there a function that needs to be called in the script? – BumpML Dec 23 '13 at 02:19
0

Try it like this:

INSERT INTO dbo.QUES_ANSWER_LABEL ( ques_data_id, label_id )

SELECT eval_question_answer_label.ques_data_id, eval_question_answer_label.label_id

FROM        EVAL_CORE 
INNER JOIN  EVAL_QUES_LNK ON EVAL_CORE.eval_id = EVAL_QUES_LNK.eval_id 
INNER JOIN  QFINITI.Qfiniti_Platform.dbo.eval_question_answer_label ON EVAL_QUES_LNK.ques_data_id = eval_question_answer_label.ques_data_id

WHERE EVAL_CORE.quality_date = CONVERT(DATETIME, '2013-12-02 00:00:00', 102);

You should not use the full table path in the column references, just the local table name should be enough. You should only need the full table path in the FROM clause's table references.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137