but he mentioned he's pulling from oracle...so joining a linked server table to your local table will threaten performance, because the command will copy the entire oracle table to tempdb,...

not like you are asking, no ...you cannot dynamically create a table by calling your procedure.... have to create a table with the appropriate columns and datatypes first.

a work around...

the issue is to NOT use the INSERT...VALUES syntax, but INSERT...SELECTinstead:

insert into sbsincome (incomekey,flightkey)

select

MAX(incomekey)+ 1 AS incomekey,

116040 AS flightkey

from sbsincome

mick i'm afraid i'll end up sending you to do some reading; i don't have a handy SQL 2000 solution to paste here for you;

as i remember it, if your...

you posted in SQL2000; are you limited to using sql 2000 for this solution?

sql 2005 introduced the row_number() function, which makes it really easy to do; your page would just...

not with openquery, but the linked server has an EXECUTE AT command that allows you to do some things;

i've created real tables, bu tnot temp tables(except inside a full set...

I believe that by default an installed instance does not listen to the default port of 1433.

take a look at your configuration and see which ports it is currently listening...

CREATE TABLE #temp (

NEWVALUE varchar(100),OLDVALUE varchar(100) )

Update MYTABLE

SET COLUMNA = COLUMNB

OUTPUT

INSERTED.COLUMNA,

DELETED.COLUMNA

INTO #temp(NEWVALUE,OLDVALUE)

where COLUMNA <> COLUMNB

SELECT * FROM #temp

drop...

a foreign key can only be created on a a unique or primary key.

that unique constraint or primary key constraint can span multiple columns.

so if you changed your table to...

the other option isusing locally declared variables. then the compiler cannot use parameter sniffing;

something like

CREATE PROCEDURE AVOIDSNIFFING(@Param1 int=0,@Param2 datetime = NULL)

AS

DECLARE @localParam1 int,

...

i might consider doing it this way...

migrate all the data except the blob/images themselves, and then migrate the images 1000 at a time so i can see progress and have...

you should be able to attach the mdl/ldf files as a new database name. I'm a little afraid that if the database was in use by SQL server, that the...

nope; you'll have to grab a backup, restore it as a different db, and compare the two tables, migrating the good data to replace the unwanted changes;

SSMS uses implicit transactions,...

definitely my first instinct, the parameter sniffing issue, is what i would check first. what you are describing is a classic symptom.

does your stored procedure have default values for any...

it really depends on the application that is connecting;

For example, I have a dozen tabs open in SSMS to various databases;each tab shows up as a sessionID; they all...