a way to have more than 8060 bytes per row?

Having a table with 5 columns that are varchar(2000).
This exceeds the maximum bytes per row and will fail
whenever someone adds more than 8060bytes in these columns.
Client whines about this and cant believe his eyes cause
he cant understand this since the maximum per column is
set to 8000 and how come the maximum is set to 8060 for
the whole row?
Anyways, do anyone of you guys out there have a workaround
for this problem or should i just tell the client to
rethink his model..
/RisunSorry, you will have to rethink your model - split the big table into two
smaller with one-to-one relation.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Risun" <risun@.wmdata.com> wrote in message
news:032401c37b69$e97cab30$a001280a@.phx.gbl...
> Having a table with 5 columns that are varchar(2000).
> This exceeds the maximum bytes per row and will fail
> whenever someone adds more than 8060bytes in these columns.
> Client whines about this and cant believe his eyes cause
> he cant understand this since the maximum per column is
> set to 8000 and how come the maximum is set to 8060 for
> the whole row?
> Anyways, do anyone of you guys out there have a workaround
> for this problem or should i just tell the client to
> rethink his model..
> /Risunsql

a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

A question on Isolation level

Whenever the update statment is executed the patient table will be
locked even for the querying. Is there a way
where in the select locks the table so that the other transaction will
not even able to select.
If change the isolation level to repeatable read or higher it will not
allow update and insert by other transaction
but select will be allowed which I want to stop
SQL
--
BEGIN TRAN
SELECT * FROM PATIENT WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
COMMIT TRANI don't understand why you have the waitfor statements. My understanding of
how the lock manager works is that it will escalate the number of locks it
has to place based on the indexes in place and the perceived numbers of rows
it has to update.
So if you have an index in place on the status column and only a few rows of
a large table have a value of A row level locking should occur. If there are
no indexes or depending on the number of rows, it could be page level,
extent locks or a table lock.
I would update statsitics, evaluate how many rows are affected to see if you
can't change this behavior.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156937237.329228.258100@.m73g2000cwd.googlegroups.com...
> Whenever the update statment is executed the patient table will be
> locked even for the querying. Is there a way
> where in the select locks the table so that the other transaction will
> not even able to select.
> If change the isolation level to repeatable read or higher it will not
> allow update and insert by other transaction
> but select will be allowed which I want to stop
>
> SQL
> --
> BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> COMMIT TRAN
>|||On 30 Aug 2006 04:27:17 -0700, "shiju" <shiju.samuel@.gmail.com> wrote:
>Whenever the update statment is executed the patient table will be
>locked even for the querying. Is there a way
>where in the select locks the table so that the other transaction will
>not even able to select.
>If change the isolation level to repeatable read or higher it will not
>allow update and insert by other transaction
>but select will be allowed which I want to stop
try "select * from patient with (updlock) where status = 'A'"
>
>SQL
>--
>BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
>COMMIT TRAN

A question on Isolation level

Whenever the update statment is executed the patient table will be
locked even for the querying. Is there a way
where in the select locks the table so that the other transaction will
not even able to select.
If change the isolation level to repeatable read or higher it will not
allow update and insert by other transaction
but select will be allowed which I want to stop
SQL
--
BEGIN TRAN
SELECT * FROM PATIENT WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
WAITFOR DELAY '00:00:10'
COMMIT TRANI don't understand why you have the waitfor statements. My understanding of
how the lock manager works is that it will escalate the number of locks it
has to place based on the indexes in place and the perceived numbers of rows
it has to update.
So if you have an index in place on the status column and only a few rows of
a large table have a value of A row level locking should occur. If there are
no indexes or depending on the number of rows, it could be page level,
extent locks or a table lock.
I would update statsitics, evaluate how many rows are affected to see if you
can't change this behavior.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156937237.329228.258100@.m73g2000cwd.googlegroups.com...
> Whenever the update statment is executed the patient table will be
> locked even for the querying. Is there a way
> where in the select locks the table so that the other transaction will
> not even able to select.
> If change the isolation level to repeatable read or higher it will not
> allow update and insert by other transaction
> but select will be allowed which I want to stop
>
> SQL
> --
> BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> COMMIT TRAN
>|||On 30 Aug 2006 04:27:17 -0700, "shiju" <shiju.samuel@.gmail.com> wrote:

>Whenever the update statment is executed the patient table will be
>locked even for the querying. Is there a way
>where in the select locks the table so that the other transaction will
>not even able to select.
>If change the isolation level to repeatable read or higher it will not
>allow update and insert by other transaction
>but select will be allowed which I want to stop
try "select * from patient with (updlock) where status = 'A'"

>
>SQL
>--
>BEGIN TRAN
> SELECT * FROM PATIENT WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
> UPDATE PATIENT SET STATUS = 'I' WHERE STATUS = 'A'
> WAITFOR DELAY '00:00:10'
>COMMIT TRAN