title: SQL Server linked server to sqlite database - how to set pragmas?
body: <p>I have SQL Server linked server pointing to a sqlite database. I can execute select statements, but can't do inserts. Seems like I have to turn off journaling (journal_mode=off) and/or temp store. What is the proper command to do it? What I have currently is:</p>
  
  <pre><code>select * 
  from openquery([name of my linked server], 'PRAGMA temp_store=2') 
  </code></pre>
  
  <p>The error is: </p>
  
  <blockquote>
    <p>Cannot process the object "PRAGMA temp_store=2". The OLE DB provider "MSDASQL" for linked server "name of the server" indicates that either the object has no columns or the current user does not have permissions on that object.</p>
  </blockquote>
  
  <p>Command that fails to insert data is:</p>
  
  <pre><code>insert openquery ([name of my linked server], 'select * from table1')
  
  select top 1 * from table1 -- table 1 in sql server has the same structure as table 1 in sqlite.
  </code></pre>
  
  <p>The error is:</p>
  
  <blockquote>
    <p>OLE DB provider "MSDASQL" for linked server "[name of my linked server]" returned message "unable to open database file (14)".</p>
  </blockquote>
  
  <p>I will appreciate the help / advise. Thanks.</p>