Advantage of Temp Table

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?Not much difference between a temp table and permenant table. Security,
automatic distruction, and one or the other may faster depending on the
disk/file layout. I would ask why not just push it directly into the
destination? Also if the text is coming from another SQL available source,
why not skip both the text file and the temporary table?

It also may be faster depending on need for availability, space, and
indexing to create a new table and do select into based on a union all of
the two tables. Then rename the table.

<shumaker@.cs.fsu.edu> wrote in message
news:1114111315.338927.231880@.g14g2000cwa.googlegr oups.com...
> When I import data I first import it from a text file into a table of
> it's own, then using some logic insert some of the records into a
> permanent table.
> I am considering having the table that the data from the text file is
> placed in being there all the time and just clearing it out after I do
> the import, or creating it and after using it then drop it, or using a
> temporary table.
> What are the advantages of a temporary table as opposed to creating a
> regular table and dropping it after use?|||Thanks.

"I would ask why not just push it directly into the
destination?"

Because I have to do some logic based on what is not in the imported
data. If a certain record exists in my table, but not in the data
being imported, then I need to modify some flags in the existing
record. So I import into an empty table, and do updates "if not in"
imported table then update my existing table.|||>> What are the advantages of a temporary table as opposed to creating
a regular table and dropping it after use? <<

A regular table will port, can have constraints, DRI actions, etc. But
do not drop it after use; just clean it out before and after you load
the data for scrubbing.

ADS "System timed out waiting for lock" error

I am attempting to modify the ADS sample to work with my own application and am running into a snag. I have a simple sqlce database that has two tables in it. I have everything working up until the point where I try to "Pull" the data. At this point in time this is what happens.

1.) I successfully Drop Table1 from the SQLCE database and then Pull "Table1" from the desktop database

2.) When attempting to Pull Table2. It runs the DoesTableExist Function, identifies that the table does exist and therefore tries to run the "DropTable" routine before Pulling the desktop Table2 data. As soon as the sqlCmd.ExecuteNonQuery of the DropTable routine runs I get the following error....

"The system timed out waiting for a lock. [ Session id = 1,Thread id = 1318094114,Process id = 1318068802,Table name = Table2,Conflict type = x lock (s blocks),Resource = DDL ][Table2]"

Of Note...I converted the sample to VB.NET so it's possible something didn't convert right? My guess is that it probably has something to with my SQLCE Database but I haven't the slightest clue where to go from there. Anyone have any ideas what might be going on?

Thanks,

Mike
I figured out my problem. Just as the error suggests, there was a lock on my database. But what was causing the lock is confusing me. When my main form opens I do a quick query on the database to retrieve some information. I open my connection to the SQLCE database, I execute a ResultSet, retrieve my data, close my ResultSet and close my connection. The datatable that I perform the ResultSet on is now "locked". If I comment out this code, the datatable is no longer locked.

Why is my datatablle still locked? Is there a way that I can let SQLCe know that I'm done with it?

Thanks!|||

Unless you explicitly Dispose the objects you will have the DB locked as Finalize/Dispose may get scheduled any time depending on GC's love on the object.

Thanks,

Laxmi

|||

Laxmi, why if he has explicitly closed the connection is there still a lock on the db?

ADS "System timed out waiting for lock" error

I am attempting to modify the ADS sample to work with my own application and am running into a snag. I have a simple sqlce database that has two tables in it. I have everything working up until the point where I try to "Pull" the data. At this point in time this is what happens.

1.) I successfully Drop Table1 from the SQLCE database and then Pull "Table1" from the desktop database

2.) When attempting to Pull Table2. It runs the DoesTableExist Function, identifies that the table does exist and therefore tries to run the "DropTable" routine before Pulling the desktop Table2 data. As soon as the sqlCmd.ExecuteNonQuery of the DropTable routine runs I get the following error....

"The system timed out waiting for a lock. [ Session id = 1,Thread id = 1318094114,Process id = 1318068802,Table name = Table2,Conflict type = x lock (s blocks),Resource = DDL ][Table2]"

Of Note...I converted the sample to VB.NET so it's possible something didn't convert right? My guess is that it probably has something to with my SQLCE Database but I haven't the slightest clue where to go from there. Anyone have any ideas what might be going on?

Thanks,

Mike
I figured out my problem. Just as the error suggests, there was a lock on my database. But what was causing the lock is confusing me. When my main form opens I do a quick query on the database to retrieve some information. I open my connection to the SQLCE database, I execute a ResultSet, retrieve my data, close my ResultSet and close my connection. The datatable that I perform the ResultSet on is now "locked". If I comment out this code, the datatable is no longer locked.

Why is my datatablle still locked? Is there a way that I can let SQLCe know that I'm done with it?

Thanks!|||

Unless you explicitly Dispose the objects you will have the DB locked as Finalize/Dispose may get scheduled any time depending on GC's love on the object.

Thanks,

Laxmi

|||

Laxmi, why if he has explicitly closed the connection is there still a lock on the db?

ADO.NET parameterized query security

I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.

Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?

From your description, you are relying on ownership chaining to access tables only from SPs; this is possible only because the SP owner is the same as the underlying tables and the security checks are bypassed; but sqlcommand will not be able to use any chaining directly.

In any case, we would like to understand your needs in order to give better advice as well as to understand our customer needs. For example, is the reason behind not granting permissions directly on the table to protect from ad-hoc queries, or to help in managing permissions? If it is for managing permissions, what may be the obstacle from using the existing permission model?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

You don′t need to grant permissions on the tables as long as the owner of the tables are the same as those for the stored procedure. This is called owner ship chaining, there is more to read about that in the BOL. Don′t breaking the ownership chain means that the permission is checked once at the procedure level. SQL Server assumes then lateron during the access of the table that if the grantee has access to the proc and the grantor created the stored procedure accessing the base tables th grantor also wanted the grantee to access the base tables. Permissions are not checked twice then. If the ownership chain is broken (Another owner of the base tables than the stored procedure) permissions are checked for every underlying base object. Using SQL Server 2005 you can also use impersonation within your stored procedures, acessing data / base tables using the WiTH EXECUTE AS syntax.

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thank you all for your quick response and comments!

The reason to avoid granting access directly on the tables is to protect from ad-hoc queries, if an unauthorized user gains access through the login used by the website. To minimize the damage if that were to happen, we want this login to have only minimal rights to the database.

From a wider perspective, I normally prefer using stored procedures, but sometimes we need the flexibility of building a parameterized query in the Web application. I would like to find a way of doing that without giving up the security advantage of using stored procedures. What is the best way to accomplish this?

|||

I would recommend following Jens’ suggestion and use either EXECUTE AS (or digital signatures) in order to change the execution context before accessing the tables. Here are a few good starting points in BOL for this topic:

· Context switching (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

· Module signing (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

If you decide to use this mechanism, I would also like to strongly recommend following the least privilege principle. For example, if for this application the application (impersonated) context only needs to have SELECT on a couple of tables, make sure that the permissions are limited only to the proper tables.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine