not sure why you cannot use dynamic SQL, but another alternative is to use synonyms (which I'd normally avoid).

An example is:

create schema foo1

go

create schema foo2

go

create table foo1.mytable(a int)

go

insert into foo1.mytable...

You could use Management Studio to script the table (in the object explorer click on the table, and select SCRIPT Table as ...) (it will include the extended properties, indexes,...

Have a look at the trustworthy database property. This is needed on 2005 for impersonating stored procedures to access items outside the database. To set it:

alter database foo set trustworthy...

And another good article I like recommending on temporary tables vs table variables:

http://support.microsoft.com/?kbid=305977

In summary, depends on the size of the table and whether you need indexes.

Regards,

Andras

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx contrasts the advantages/disadvantages...

Change to bulk recovery mode and use "Select into". Select into is minimally logged (see http://msdn2.microsoft.com/en-us/library/ms191244.aspx).

Regards,

Andras

You could either use row_number() or you can use identity in select into clauses. E.g.:

select

identity(int, 1,1) as id, *

into

myNewTable

from

myOtherTable

Regards,

Andras

One solution is to change to simple recovery mode:

alter database mydatabase set recovery simple

then shrink the logfile (usually 2 is the log file id, check by looking into sysfiles)

DBCC...

One solution is to use row_number() with a name based partitioning, like:

SELECT ID, DateStamp, Fname, MI, Lname, FixedBy

FROM ( SELECT ID, DateStamp,...

Hmmm, Jason managed to post his response between me refreshing to see if someone answered, and pasting my answer in. Witchcraft? 🙂

Andras

Use:

DECLARE @TableName nvarchar(Max),@Abbreviation nvarchar(100)

SET @Abbreviation='HH'

select @TableName = CASE @Abbreviation

WHEN 'HH' THEN 'Hotel_History'

WHEN 'HA' THEN 'Hotel_Accomodation'

WHEN 'HD' THEN 'Hotel_Dining'

WHEN 'HB' THEN 'Hotel_Banquet_Conference'

WHEN 'HF' THEN 'Hotel_Facilities_Services'

WHEN 'HV'...

In SQL Server 2000 and 2005 there is no date datatype (2008 introduces this, but it is not yet out). So dates can be stored together with a time part...

If you must do this inside the SQL statement (and would not like to use a top level if else statement, you

could do something like:

SELECT SUM(EnrollCnt)

FROM ...

jghali (12/12/2007)

---

Hi all,

I have created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).

I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]…...

Consider the data in your tables as sets (or bags, if there are duplicates).

In your first query you order the set (the sequences will be 3;2;1, and you take the...