If the file is fixed format (not delimited between columns) you could load the file into a table with a single column, and then create a view on the table...

Since the deadlock victim is the select, there is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will prevent the deadlocks and it will not require any coding changes.

You could add this to the proc to make sure all the tables are created by the dbo user.

ALTER PROCEDURE [dbo].[pGetItemLot] @item char(5),

@lot char(20),

@desc char(50)output

with execute as 'dbo'

as

...

rest of proc

...

For the dynamic SQL approach:

declare @sqlnvarchar(4000)

set @sql =

'

INSERT INTO '+

case

when @PeriodType = 'Monthly'then N'Monthly_Summary_Data'

when @PeriodType = 'Daily'then N'Daily_Summary_Data'

else null end

+' (Column_1, Column_2, etc.)

SELECT

Source_col_1,

Source_col_2

FROM

SourceTable

'

exec sp_executesql @sql

Little late to point it out, but any good backup plan should have backups to tape to make sure you have some place to go back to.

Lynn Pettis (1/27/2010)

---

Michael Valentine Jones (1/27/2010)

---

WayneS (1/27/2010)

---

Michael Valentine Jones (1/27/2010)

---

There are two datetime string formats that are interpreted correctly with with any language setting.

With the first format, only...

WayneS (1/27/2010)

---

Michael Valentine Jones (1/27/2010)

---

There are two datetime string formats that are interpreted correctly with with any language setting.

With the first format, only the YYYYMMDD part is required,...

There are two datetime string formats that are interpreted correctly with with any language setting.

With the first format, only the YYYYMMDD part is required, but with the second...

declare @FirstPayDate datetime

set @FirstPayDate= '20100101'

select

*,

FirstPayDateOfCurrentQuarter =

dateadd(dd,(datediff(dd,@FirstPayDate,dateadd(dd,13,

dateadd(qq,datediff(qq,@FirstPayDate,Date),@FirstPayDate)))/14)*14,@FirstPayDate)

from

(

select Date = convert(datetime,'20100727')union all

select Date = convert(datetime,'20100827')union all

select Date = convert(datetime,'20101001')

) a

order by

a.Date

Results:

Date ...

Create the view using "WITH SCHEMABINDING" in the other database.

Create the view using the "WITH SCHEMABINDING" option.

Trailing digits would not cause an Arithmetic Overflow error. See example code below.

create table #t ( MyNumber numeric(19,6) not null)

insert into #t select 10.641111111111 * 10.64373737469083459

select * from...

Stay away from #2. That's an EAV (Entity/Attribute/Value) model.

It saves a little work up front in data modeling by allowing "open ended" insertion of new attributes at the cost...

crainlee2 (1/16/2010)

---

Michael and Jeff,

Thank you for your contributions on using CHECKSUM as a way of searching for equalities on VARCHAR(MAX) datatypes.

I think there are a few places in our...

If you really need to do an exact match lookup on a varchar(max) column, you can add a computed column that contains a checksum of the varchar(max) column, and index...