none
Cannot access destination table exception on temporary table RRS feed

  • Question

  • Hello ,

    I have a project to convert data table to database table .

    The project support the creation of local temporary table and gloabl temporary table,

    but for some reason after I create a temporary table , I can't access to it to fill it with data .

    The following error is raised : Cannot access destination table '##Employee'.

    The funny thing is when I debug the project and execute a select query in the sql server 2005 I'm able to access the table.

    I tried to use also tempdb..'##Employee but it didn't work.

    This is the project code:

     public void TransferDataToDB(DataTable sourceTbl, string targeTblName)
        {
            _connection.Open();
            //Open bulkcopy connection.
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(_connection))
            {
                //Set destination table name
                //to table previously created.
                bulkcopy.DestinationTableName = "tempdb..##Employee";

                try
                {
                    bulkcopy.WriteToServer(sourceTbl);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    _connection.Close();
                }
            }
        }

        #endregion


        #region Private Methods

        /// <summary>
        /// Build the database table schema according to the datatable schema
        /// </summary>
        private void BuildDBTableSchema(Smo.Table dbTargetTbl, DataTable dtSourceTbl)
        {
                    //Loop on each columInfo and insert a new column to the table
            foreach (DataColumn dtCol in dtSourceTbl.Columns)
            {
                //Create new column
                Smo.Column column       = new Smo.Column(dbTargetTbl, dtCol.ColumnName);
              
                //Set the column attributes
                column.DataType         = ConvertTypes(dtCol.DataType.ToString());
                column.Nullable         = dtCol.AllowDBNull;
          
                //Add column to the list
                dbTargetTbl.Columns.Add(column);

                if (dtCol.Unique)
                {
                    // Add a new index to the column
                    string indexName     = dtSourceTbl.TableName + "-" + dtCol.ColumnName;
                    Smo.Index sIndex     = new Smo.Index(dbTargetTbl,indexName);
                    sIndex.IndexKeyType  = Smo.IndexKeyType.DriPrimaryKey;                                        
                    sIndex.IndexedColumns.Add(new Smo.IndexedColumn(sIndex,dtCol.ColumnName));

                    // Add the new index to the table.
                    dbTargetTbl.Indexes.Add(sIndex);
                }
            
            }
        }

        /// <summary>
        /// Return the table prefx according to the the table tpye
        /// </summary>
        /// <param name="tblType"></param>
        /// <returns></returns>
        private string GetTablePreFix(TBL_TYPES tblType)
        {
            string prefix = string.Empty;
            //Determine the table name according to the tblType
            switch (tblType)
            {

                case TBL_TYPES.TEMP_LOCAL:
                    prefix = "#";
                    break;
                case TBL_TYPES.TEMP_GLOABL:
                    prefix = "##";
                    break;
                case TBL_TYPES.PERMANENT:
                    break;     //Stay on empty prefix
            }
            return prefix;
        }

     

    Any help will be appreciated .

    Kind regards

    • Moved by Bob Beauchemin Friday, October 21, 2011 7:57 PM Moving to a more relevent forum for better response (From:.NET Framework inside SQL Server)
    Friday, October 21, 2011 7:47 PM

All replies

  • Hi Tal,

    This might be caused because the account you used to run BULK INSERT operation(bulkcopy.WriteToServer) do not have required permission. Please check the Premissions section of http://msdn.microsoft.com/en-us/library/ms188365(SQL.90).aspx

    And I will suggest you try BULK INSERT(T-SQL) directly in SQL Server management studio to make sure the user you used can do it without any problem. Or try to use a SQL sysadmin account(for example: SA) for testing.

    Then try again using your code.


    Alan Yao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 3:13 PM