title: Sql Server temporary table disappears
body: <p>I'm creating a temporary table, #ua_temp,  which is a subset of regular table. I don't get an error, but when I try to SELECT from #ua_temp in the second step, it's not found. If I remove the #, a table named ua_temp is created.</p>
  
  <p>I've used the exact same technique from created the table with SELECT INTO elsewhere. It runs fine, so I don't think it has anything to do with database settings. Can anyone see the problem?</p>
  
  <pre><code>        // Create temporary table 
          q = new StringBuilder(200);
          q.Append("select policy_no, name, amt_due, due_date, hic, grp, eff_dt, lis_prem, lis_grp, lis_co_pay_lvl, ");
          q.Append("lep_prem, lapsed, dn_code, [filename], created_dt, created_by ");
          q.Append("into #ua_temp from elig_ua_response ");
          q.Append("where [filename] = @fn1 or [filename] = @fn2 ");
          sc = new SqlCommand(q.ToString(), db);
          sc.Parameters.Add(new SqlParameter("@fn1", sFn));
          sc.Parameters.Add(new SqlParameter("@fn2", sFn2));
          int r = sc.ExecuteNonQuery();
          MessageBox.Show(r.ToString() + " rows");
  
          // Rosters
          q = new StringBuilder(200);
          q.Append("select policy_no,name,amt_due,due_date,hic,grp,eff_dt,");
          q.Append("lis_prem,lis_grp,lis_co_pay_lvl,lep_prem,lapsed,dn_code,[filename] ");
          q.Append("from #ua_temp where (lis_prem &gt; 0.00 or lep_prem &gt; 0.00) ");
          q.Append("and [filename] = @fn order by name");
          sc.CommandText = q.ToString();
          sc.Parameters.Clear();
          sc.Parameters.Add(new SqlParameter("@fn", sFn));
          sda = new SqlDataAdapter(sc);
          sda.Fill(ds, "LIS LEP Roster");
  </code></pre>
  
  <p>To answer some of the obvious questions: This program was running fine using the source table, elig_ua_response. The reason for introducing the temp table was that I want to delete some of the rows for this particular report. I put brackets around the column [filename] while testing to be sure it's not a key word issue. The second SELECT works fine if you replace #ua_temp with elig_ua_response. I've tried different names for the temp table. The MessageBox showing the number of rows was just for debugging purposes; it doesn't affect the problem.</p>