{"id":202,"date":"2012-05-14T07:13:56","date_gmt":"2012-05-14T07:13:56","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/05\/14\/msg-8180-level-16-state-1-line-1-statements-could-not-be-prepared\/"},"modified":"2012-05-14T07:13:56","modified_gmt":"2012-05-14T07:13:56","slug":"msg-8180-level-16-state-1-line-1-statements-could-not-be-prepared","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/05\/14\/msg-8180-level-16-state-1-line-1-statements-could-not-be-prepared\/","title":{"rendered":"Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared."},"content":{"rendered":"

Error:<\/strong>
\n
\nOLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Deferred prepare could not be completed.”.
\nMsg 8180, Level 16, State 1, Line 1
\nStatement(s) could not be prepared.
\nMsg 102, Level 15, State 1, Line 1
\nIncorrect syntax near ‘AS’.
\n<\/font><\/p>\n

In the above error the second error Msg 102<\/strong> is thrown by the destination linked server, and the error 8180 is thrown by the local(source) server.<\/p>\n

Reason for this error:<\/strong>
\nThere could be synatax error in the query that is passed to the Linked Server(Other Server).<\/p>\n

FIX\/Resolution:<\/strong>
\nExecute the query that is passed to the Target server on the target server and check if there are any Syntax\/Runtime errors.<\/p>\n

[sql]
\nSELECT * FROM OPENROWSET(‘SQLOLEDB’,
\n ‘Server=(local);Trusted_Connection=Yes;Database=Master’,
\n ‘SELECT * fromsys.tables’)
\n[\/sql]<\/p>\n


\nOLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Deferred prepare could not be completed.”.
\nMsg 8180, Level 16, State 1, Line 1
\nStatement(s) could not be prepared.
\nMsg 102, Level 15, State 1, Line 1
\nIncorrect syntax near ‘fromsys’.
\n<\/font><\/p>\n

Now when I try to execute the query (shown below) on the target server, I came to know the syntax error in the statement.
\n[sql]
\nSELECT * fromsys.tables
\n[\/sql]<\/p>\n

Fixed code:
\n[sql]
\nSELECT * from sys.tables
\n[\/sql]<\/p>\n

Now I update the Original Code as follows. Now the code runs without error.
\n[sql]
\nSELECT * FROM OPENROWSET(‘SQLOLEDB’,
\n ‘Server=(local);Trusted_Connection=Yes;Database=Master’,
\n ‘SELECT * from sys.tables’)
\n[\/sql]<\/p>\n

Applicable to SQL Server 2008,SQL Server 2005,SQL Server 2008 r2,SQL Server 2012<\/p>\n","protected":false},"excerpt":{"rendered":"

Error: OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Deferred prepare could not be completed.”. Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘AS’. In the above error the second error Msg 102 is thrown by the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1932,128],"tags":[1933,1934],"class_list":["post-202","post","type-post","status-publish","format-standard","hentry","category-msg-8180","category-sql-errors","tag-msg-8180-level-16-state-1-line-1-statements-could-not-be-prepared-msg-102-level-15-state-1-line-1-incorrect-syntax-near-as","tag-ole-db-provider-sqlncli10-for-linked-server-null-returned-message-deferred-prepare-could-not-be-completed"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=202"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/202\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}