How to insert ten rows in one insert statement

Dear all,
I want to insert 10 rows data as follows
column-A column-B column-C
A A
1
A A
2
A A
3
A A
4
A A
5
In each row, the Column-A and column-B are the same. While column-c
will increase from 1 to 10.
How can I do that in one insert statement . Or I need to loop in VB
Program.
Thanks.If column C is an identity problem, then you just insert 'a', 'a' 10 times
(like your other question).
But otherwise, you could use rowcount and row_number like this:
set rowcount 10
insert into newtable
select 'a', 'a', row_number() over (order by quantity)
from sometable
where sometable has at least 10 rows.
Or, using a CTE instead of rowcount:
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable
)
insert into newtable select * from rownums where rownum <= 10
"hon123456" wrote:

> Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
> In each row, the Column-A and column-B are the same. While column-c
> will increase from 1 to 10.
> How can I do that in one insert statement . Or I need to loop in VB
> Program.
> Thanks.
>|||Thanks Rob.
When I try to run
select 'a', 'a', row_number() over (order by quantity) from sometable.
The query analyzer complains 'row_number' is not a recognized function
name.
By the way, can I have the complete sql statement for the follows
statement
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable )|||Thanks Rob. sorry to post again. But I am wondering what does the
variable "quantity" come from in the following statement:
select 'a', 'a', row_number() over (order by quantity) from sometable.|||Haha - sorry... that's just one of the columns in 'sometable'. I meant to
change that.
If it's not recognising row_number(), then perhaps you're not running
SQL2005 ? :(
It's somewhat harder to do in SQL2000. Off the top of my head, the easiest
way to do it is to create a temporary table with an identity field, or
perhaps use a while loop and do 10 single inserts (after all, if it's only
10...).
One nice way would be to use an auxiliary table of numbers. You'll find it
useful for all kinds of other reasons too... but most of all, you can easily
just say "select 'a', 'a', num from nums where num <= 10"
Hope this helps,
Rob
"hon123456" wrote:

> Thanks Rob. sorry to post again. But I am wondering what does the
> variable "quantity" come from in the following statement:
> select 'a', 'a', row_number() over (order by quantity) from sometable.
>|||If you already had a table of sequential numbers this would be
simpler. You would simply replace derived table A with that table,
and limit the values to the range 1 to 10 in a WHERE clause.
INSERT SomeTable
SELECT A.column_a, 'A' as column_b, 'A' as column_c
FROM (select 1 as column_a UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9 UNION
select 10) as A
Roy Harvey
Beacon Falls, CT
On 18 Apr 2006 21:48:17 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
>In each row, the Column-A and column-B are the same. While column-c
>will increase from 1 to 10.
>How can I do that in one insert statement . Or I need to loop in VB
>Program.
>Thanks.

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

how to insert more than once when processing one row in script component

I am trying use call DirectRowToOutput0 more than once to achieve the goal, but it gives an error of calling COM. I can create more than one destination to achieve it, but I need create 8 destinations for my situation. Is there any easy way?

Thanks

Is this any help to you: http://blogs.conchango.com/jamiethomson/archive/2005/09/05/2113.aspx

-Jamie

|||no, that is not what I am trying to do. In fact, I need insert to the same destination more than once.|||

In that case this will be an asynchronous component. You will then be able to do what you want with the rows.

-Jamie

|||That is it. Thank you so much.

How to insert a row with lowest possible key?

Hi, this seems to be an easy question but I can't find a quick answer to it.

I have an asp page that communicates with a database and I want the following example to work...

Insert 5 records into the DB with primary keys 1-5.

Remove record with key number 2.

Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.

Does auto increment work like this?

If not, how do I do it?

Thanks for any replies!

Niklas

Auto-increment doesn't work like this.

I'm not sure that you reasons for doing this are sound. I don't think it'll save space really. An INT is an INT so you will only be storing 4bytes no matter what the size of the number. And while they will increase forever, its got to be a pretty big system to hit the limit of 2,147,483,647!!

However, i guess you could do it, but you'll need to run a check like SELECT MIN(Value) FROM Table and then subtracting 1 from that to get your value before running your INSERT statement.

|||

Auto numbers won't fill the gap. You have to write your own logic for this. It might degrade your query performance.

Because,

1. You need to find if there is any gap in existing data

2. You have to find the minimum gap to fill

3. If you already use the auto increment on the current column you have to disable it to fill the gap.

4. If step-1 & step-2 fails then you have to continue with auto increment

Finally,

5. If the concurrent users (let say 2 users) try to insert same id, one will be pass. Another user's transaction

will fail, you have to take care this failure and again you have to find the gap from step1. If n users try to

access this logic the Nth users may need to spend more time to insert his record.

Do you want to continue still?

|||This looks really strange, but it's working for me. The query in red is where the magic happens; everything else is just creating a test environment (run the whole thing in AdventureWorks/Northwind/pubs to see it in action).

Code Snippet

--Make a test table
IF object_id('junk') IS NOT NULL
DROP TABLE junk
GO
CREATE TABLE junk (
id int identity(1, 1),
data char(4)
)
GO

--Put in some test data
SET NOCOUNT ON
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 100
BEGIN
INSERT INTO junk (data) VALUES (@.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

--Remove some records
DELETE FROM junk WHERE id IN (4, 5, 56, 17, 9, 82)
GO

--Find the lowest available number
SELECT MIN(j1.id) + 1 AS [next]
FROM junk j1
LEFT OUTER JOIN junk j2
ON j1.id + 1 = j2.id
WHERE j2.id IS NULL

|||Another issue is that if your database does not have cascading deletes, the new records may have foreign key usage in other tables that is not intended for the current record.sql

How to insert a row in a table with 1 identity column

HI,

I have an SQL Server table with only 1 column. That column is an
identity column. How can I insert a row in this table using SQL
syntax?

I tried insert
into T_tableName () values ()
and a few other options, but I can't seem to get it to insert.

Thanks

Alain"Alain Filiatrault" <alainf@.humaprise.com> wrote in message
news:45d95216.0312230604.328d0c8a@.posting.google.c om...
> HI,
> I have an SQL Server table with only 1 column. That column is an
> identity column. How can I insert a row in this table using SQL
> syntax?
> I tried insert
> into T_tableName () values ()
> and a few other options, but I can't seem to get it to insert.
> Thanks
> Alain

CREATE TABLE T
(
col INT IDENTITY NOT NULL PRIMARY KEY
)

INSERT INTO T
DEFAULT VALUES
INSERT INTO T
DEFAULT VALUES

SELECT col FROM T

col
1
2

Regards,
jag

How to insert a lookup table row aftre lookup fails

I want to do a a look-up, then if that lookup does not find a record I want to insert a new record into the lookup table, so that effectively the lookup now succeeds.

How can I do that? I can't figure that one out.

TIAI have solved this problem.

I am using a two pass mechanism in the Control Flow. In the first pass (first Data Flow) I do a lookup and then on failure redirct to insert the records.

I have a number of these to do - so in the first pass I multicast the source data to each of the lookups.
You see, I am loading survey data entered into Excel spreadsheets by humans. :)|||FYI it is often more performant not to redirect the error rows but to just do a filter on the rows that don't have the lookup value. This is all down to the redirect creating a new row, and the associated costs of assigning memory etc.
where as processing the normal flow no extra data has to be created in the buffer.

Does that make sense?|||It certainly does Simon. Thank-you very much ... i did not think of that. :)|||

SimonSa wrote:

FYI it is often more performant not to redirect the error rows but to just do a filter on the rows that don't have the lookup value. ...
Does that make sense?

Simon,

Maybe you can provide some insight on a similar scenario. In my control flow, I do a bunch of processing over millions of rows and finally I want to insert a subset into an xyz_master table. However, I want to check if the record already exists or not. I am using the error row redirecting mechanism and it is painfully slow. As per your suggestion, I should do some kind of filter. How do I do it?

Let me know if I need to explain further.

TIA,
Nitesh|||So you have you output from the lookup which is the input to the lookup along with a value that was looked up, i.e the lookup value. For rows found in the lookup this column will contain the key and rows not found will be empty.

This extra column is added to the buffer so the whole row doesn't have to be copied to a new buffer, which is what happens with the redirect, only the new column value is populated in the existing buffer

You then use the conditional split to only output those rows with no value in the lookup key column. The key here is that the conditional split is a synchronous transform and so again uses the same buffer so no data is copied.

You can then use the output of the conditional split (the one where lookup column is null) in any other component.

One thing I will say is that if you are using the lookup make sure you are only selecting the columns you need from the lookup tables, i.e. just the lookup value and any other values needed to be added to the flow.|||Great ... that is exactly what I did :) Glad to see I am on the right track.

I love SSIS!

How to insert a column in Excel by using OleDB

Hi all, I need to add a new column in an excel file, as I jus know the
query to add a new row in excel.
string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
" values ('"+name+"', '"+id+"')";
Anyone know the query to add a new column? Thankz...
Message posted via http://www.webservertalk.comYou might try the ALTER TABLE command. Not sure if it will work for a
linked Excel file, but it's standard SQL DDL to modify your table
structure - including adding a column.
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
> --
> Message posted via http://www.webservertalk.com|||Ya...but I duno the syntax of the ALTER TABLE....I have tried
string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
but it doesn't seems to work...can you please give an example of the Alter
Table SQL? Thankx...
Message posted via http://www.webservertalk.com|||> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
In SQL Server, you should omit COLUMN. I don't know if this will work with
Excel:
string strCom = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:14c9b951bfd14ffc93557f7c6d03b877@.SQ
webservertalk.com...
> Ya...but I duno the syntax of the ALTER TABLE....I have tried
> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
> but it doesn't seems to work...can you please give an example of the
> Alter
> Table SQL? Thankx...
> --
> Message posted via http://www.webservertalk.com|||I'm using C# with OleDB, here is my code
========================================
===============================
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
fileName + ";Extended Properties=Excel 8.0" ; ;
OleDbConnection myConn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
myConn.Open();
myCommand.Fill (myDataSet, "[Sheet1$]");
myConn.Close();
========================================
===============================
I got an error of "Invalid operation"...anyway, it works fine with
UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
ALTER TABLE syntax?
Message posted via http://www.webservertalk.com|||As I said, I'm not even sure ALTER TABLE is supported when accessing XLS
files via OLEDB. That was just something to try based on standard SQL DDL.
Here's a link that has a tip, although they say it's a little complex:
http://www.eggheadcafe.com/ng/micro...490.asp

"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||> anything wrong with the ALTER TABLE syntax?
This syntax is valid for Microsoft SQL Server. I don't know what syntax Jet
expects or if it is even possible to add a column to an existing sheet using
the Jet OleDb provider
You might try posting your question to the OleDb forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
>
are you forced to use "oledb"?
object automation is very flexible way of interfacing excel:
set oxls=createobject("excel.application")
set owbk=oxls.workbooks.open("mytable.xls")
set owsht=owbk.activesheet
owsht.activecell.entirecolumn.insert
...
with such object you may use any construct supported by excel macro.|||Thanks for the suggestion, so how could I know wherether the ALTER TABLE is
supported or not?
Message posted via http://www.webservertalk.com|||Ops...I just saw the ODBC and JDBC...may I know where is the OleDB forum ?
Thankz.
Message posted via http://www.webservertalk.com

How to increase the default row size in sql server 2000 only

Hi, Guys.
I am in bit trouble.I am importing the table from mysql to sql server which has having nearly 65000 records.
I starts the import of table by import/export utility.But when the records reaches 65000, the error message came.."THIS TABLE HAVING ROW SIZE 8190 WHICH IS EXCCEDING THE DEFAULT SIZE OF 8060".
So please let me know how to over come this problem..

I CAN'T UPGRADE SQL SERVER SO GIVE SOLUTION FOR SQL SERVER 2000 ONLY...

Thanks in Advance to all of you.

pchadha20For my knowledge it isn't possible to change that limit. You should split your table or in some cases use Ntext-type but that would generate a lot of other troubles.

how to increase maximum rowsize in sql server 2000

Is there any ways to increase the default maximum row size
of 8060 in SQL server 2000?
Thanks in advance
kiranNo you cannot.
You can however split your table into multiple tables and maintain
relationships between them.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"kiran kumar joseph" <kiran_joseph82@.yahoo.com> wrote in message
news:00f201c3aff4$742ea1c0$a001280a@.phx.gbl...
> Is there any ways to increase the default maximum row size
> of 8060 in SQL server 2000?
> Thanks in advance
> kiran|||Another option is to use text columns instead of varchars.
--
Andrew J. Kelly
SQL Server MVP
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:uLq%23gd$rDHA.1996@.TK2MSFTNGP09.phx.gbl...
> No you cannot.
> You can however split your table into multiple tables and maintain
> relationships between them.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "kiran kumar joseph" <kiran_joseph82@.yahoo.com> wrote in message
> news:00f201c3aff4$742ea1c0$a001280a@.phx.gbl...
> > Is there any ways to increase the default maximum row size
> > of 8060 in SQL server 2000?
> >
> > Thanks in advance
> > kiran
>

How to Increase maximum row size

Hi,
I have an database migration project wherein I need to migrate data from
FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and there
are more than 300 fields in one table. Now when I try to create the same
table structure in SQL, I get the error saying it has reached the maximum ro
w
size of 8060 bytes.
Can anyone let me know is there a way to increase the row size limit. If
yes, then how? Also it would be great if you can let me know whether it is a
good suggestion to increase the row size limit.
Regards,
Sudhakara.T.P.Hello,
In SQL 2005 it is possible and older version its a limitation. See the below
article.
http://searchsqlserver.techtarget.c...1149592,00.html
Thanks
Hari
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
> Regards,
> Sudhakara.T.P.|||"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
There is no way to increase this limit. Given that your table has 300
columns, perhaps now is a good time to revisit the database design. It is
likely that this table/database is not properly normalized. At a minimum,
you will need to split the table to overcome the limit.

How to Increase maximum row size

Hi,
I have an database migration project wherein I need to migrate data from
FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and there
are more than 300 fields in one table. Now when I try to create the same
table structure in SQL, I get the error saying it has reached the maximum row
size of 8060 bytes.
Can anyone let me know is there a way to increase the row size limit. If
yes, then how? Also it would be great if you can let me know whether it is a
good suggestion to increase the row size limit.
Regards,
Sudhakara.T.P.Hello,
In SQL 2005 it is possible and older version its a limitation. See the below
article.
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149592,00.html
Thanks
Hari
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
> Regards,
> Sudhakara.T.P.|||"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
There is no way to increase this limit. Given that your table has 300
columns, perhaps now is a good time to revisit the database design. It is
likely that this table/database is not properly normalized. At a minimum,
you will need to split the table to overcome the limit.

How to Increase maximum row size

Hi,
I have an database migration project wherein I need to migrate data from
FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and there
are more than 300 fields in one table. Now when I try to create the same
table structure in SQL, I get the error saying it has reached the maximum row
size of 8060 bytes.
Can anyone let me know is there a way to increase the row size limit. If
yes, then how? Also it would be great if you can let me know whether it is a
good suggestion to increase the row size limit.
Regards,
Sudhakara.T.P.
Hello,
In SQL 2005 it is possible and older version its a limitation. See the below
article.
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149592,00.html
Thanks
Hari
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
> Regards,
> Sudhakara.T.P.
|||"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:1B5C4FB3-DB61-4E30-A3F6-8EE6113C623B@.microsoft.com...
> Hi,
> I have an database migration project wherein I need to migrate data from
> FileMaker Pro to SQL. The FileMaker Pro stores files as flat files and
> there
> are more than 300 fields in one table. Now when I try to create the same
> table structure in SQL, I get the error saying it has reached the maximum
> row
> size of 8060 bytes.
> Can anyone let me know is there a way to increase the row size limit. If
> yes, then how? Also it would be great if you can let me know whether it is
> a
> good suggestion to increase the row size limit.
There is no way to increase this limit. Given that your table has 300
columns, perhaps now is a good time to revisit the database design. It is
likely that this table/database is not properly normalized. At a minimum,
you will need to split the table to overcome the limit.

how to include an apostrophy inside a string

I am doing a row update in a table and the text contains a ' character, just
wondering how to do this?
for example
UPDATE table
SET field2 = 'text to enter to table's but does not work'
WHERE field1 = 133
the second apostrophy I want to not end the text section with.
Thanks.
--
Paul G
Software engineer.YOu have to double quote that:
UPDATE table
SET field2 = 'text to enter to table''s but does not work'
WHERE field1 = 133
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> I am doing a row update in a table and the text contains a ' character, just
> wondering how to do this?
> for example
> UPDATE table
> SET field2 = 'text to enter to table's but does not work'
> WHERE field1 = 133
> the second apostrophy I want to not end the text section with.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks. One other question, when I use select from query analizer to
retreive a long text field and then copy and paste it to a word doc it looks
like it only returns the first portion of the large text string. Also is
there anyway to view a large text field from query analyzer when you open the
table? thanks again.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> YOu have to double quote that:
> UPDATE table
> SET field2 = 'text to enter to table''s but does not work'
> WHERE field1 = 133
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > I am doing a row update in a table and the text contains a ' character, just
> > wondering how to do this?
> >
> > for example
> > UPDATE table
> > SET field2 = 'text to enter to table's but does not work'
> > WHERE field1 = 133
> > the second apostrophy I want to not end the text section with.
> > Thanks.
> >
> > --
> > Paul G
> > Software engineer.|||QA is limited to a maximum outpur of 8000 characters, if you don´t have even
that you should look in Tools--> Options --> Results --> Maximum charcters
per column
to increase it to 8000
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> ok thanks. One other question, when I use select from query analizer to
> retreive a long text field and then copy and paste it to a word doc it looks
> like it only returns the first portion of the large text string. Also is
> there anyway to view a large text field from query analyzer when you open the
> table? thanks again.
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > YOu have to double quote that:
> >
> > UPDATE table
> > SET field2 = 'text to enter to table''s but does not work'
> > WHERE field1 = 133
> >
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > I am doing a row update in a table and the text contains a ' character, just
> > > wondering how to do this?
> > >
> > > for example
> > > UPDATE table
> > > SET field2 = 'text to enter to table's but does not work'
> > > WHERE field1 = 133
> > > the second apostrophy I want to not end the text section with.
> > > Thanks.
> > >
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks that should do it as it is way less than 8000 characters.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Just wondering if you know how to direct the results to a file?
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
Results in File
or you use OSQL on the commandline with the -o <outputfile >switch
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> Just wondering if you know how to direct the results to a file?
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > that you should look in Tools--> Options --> Results --> Maximum charcters
> > per column
> >
> > to increase it to 8000
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > ok thanks. One other question, when I use select from query analizer to
> > > retreive a long text field and then copy and paste it to a word doc it looks
> > > like it only returns the first portion of the large text string. Also is
> > > there anyway to view a large text field from query analyzer when you open the
> > > table? thanks again.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Jens Sü�meyer" wrote:
> > >
> > > > YOu have to double quote that:
> > > >
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table''s but does not work'
> > > > WHERE field1 = 133
> > > >
> > > >
> > > > --
> > > > HTH, Jens Suessmeyer.
> > > >
> > > > --
> > > > http://www.sqlserver2005.de
> > > > --
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > wondering how to do this?
> > > > >
> > > > > for example
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > WHERE field1 = 133
> > > > > the second apostrophy I want to not end the text section with.
> > > > > Thanks.
> > > > >
> > > > > --
> > > > > Paul G
> > > > > Software engineer.|||ok thanks this seems very useful.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
> Results in File
> or you use OSQL on the commandline with the -o <outputfile >switch
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > Just wondering if you know how to direct the results to a file?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > > that you should look in Tools--> Options --> Results --> Maximum charcters
> > > per column
> > >
> > > to increase it to 8000
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > ok thanks. One other question, when I use select from query analizer to
> > > > retreive a long text field and then copy and paste it to a word doc it looks
> > > > like it only returns the first portion of the large text string. Also is
> > > > there anyway to view a large text field from query analyzer when you open the
> > > > table? thanks again.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Jens Sü�meyer" wrote:
> > > >
> > > > > YOu have to double quote that:
> > > > >
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table''s but does not work'
> > > > > WHERE field1 = 133
> > > > >
> > > > >
> > > > > --
> > > > > HTH, Jens Suessmeyer.
> > > > >
> > > > > --
> > > > > http://www.sqlserver2005.de
> > > > > --
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > > wondering how to do this?
> > > > > >
> > > > > > for example
> > > > > > UPDATE table
> > > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > > WHERE field1 = 133
> > > > > > the second apostrophy I want to not end the text section with.
> > > > > > Thanks.
> > > > > >
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.sql

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
Thanks
On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.
|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>
|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>
|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.
4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
>>Take for example ticketmaster where they lock a certain section, row and
>>even seat numbers for a duration..
>>I am assuming all this info may be in one table . How can one ensure row
>>level locking without SQL Server escalating it to some high level lock.
>>And even if a particular row is locked, does that mean that one can update
>>rows that are not locked
>>Trying to find a solution to do locking at a row level while still leaving
>>other rows for DML i.e select, updates,inserts,deletes
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Take for example ticketmaster where they lock a certain section, row and
>> even seat numbers for a duration..
>> I am assuming all this info may be in one table . How can one ensure row
>> level locking without SQL Server escalating it to some high level lock.
>> And even if a particular row is locked, does that mean that one can
>> update rows that are not locked
>> Trying to find a solution to do locking at a row level while still
>> leaving other rows for DML i.e select, updates,inserts,deletes
>> Thanks
>

How to ignore error and continue trasactional replication

Hi,
I have a trasactional replication setup in SQL 2000 with SP3. Accidentally,
I deleted a row in the suscriber table. When I deleted the same row in the
publisher database, I have error in the replication monitor "the row was not
found at the Subcriber when applying the replicated command".
Please let me know how do I get rid of this error and continue replication.
I don't want to reinitialize the subscriber again because the table is big
and takes lot of time.
I appreciate your help!!
Thanks
Chinna.
gonzo - cowboy- to hell with database consistency approach - right click on
your failed agent, select agent profiles, and then select the continue on
data consistency profile.
more cautious approach. enable logging for your distribution agent, and
restart it. Find out the row which you deleted. Get this row from teh
publisher and manually construst the insert statement and put it back in the
subscriber. Restart your distribution agent.
check out this kb article for more info on how to do the logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Hi,
> I have a trasactional replication setup in SQL 2000 with SP3.
Accidentally,
> I deleted a row in the suscriber table. When I deleted the same row in the
> publisher database, I have error in the replication monitor "the row was
not
> found at the Subcriber when applying the replicated command".
> Please let me know how do I get rid of this error and continue
replication.
> I don't want to reinitialize the subscriber again because the table is big
> and takes lot of time.
> I appreciate your help!!
> Thanks
> Chinna.
>
|||Thanks to everyone who helped me. It worked!!. I have changed back to default.
I appreciate your help!!
Thanks
Chinna.
"Hilary Cotter" wrote:

> gonzo - cowboy- to hell with database consistency approach - right click on
> your failed agent, select agent profiles, and then select the continue on
> data consistency profile.
> more cautious approach. enable logging for your distribution agent, and
> restart it. Find out the row which you deleted. Get this row from teh
> publisher and manually construst the insert statement and put it back in the
> subscriber. Restart your distribution agent.
> check out this kb article for more info on how to do the logging.
> http://support.microsoft.com/default...&Product=sql2k
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
> message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Accidentally,
> not
> replication.
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:
> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> > Hi,
> >
> > I want to identify, when the values are modified/updated on the particular
> > row in a table, i am not using triggers in this table.
> >
> > Please advise me, any options in SQL Profiler Trace to find out this.
> >
> > rgds,
> > Soura
>
>

How to hide/Remove the first row of the CSV file?

I have created one report and exported in CSV file. But in CSV file, it
shows the Textbox names, table names etc in the first row. i dont want
to show the first row of the CSV result. any idea how to hide/Remove
the first row?
Regards,
VinodHmmm...tough one...
I would say you should setup a link to print another report that is the
same data but w/o the headers...There is probably a better solution but
this one would be fairly easy. If its something that you are scheduling
to be created on a share or sent out w/o headers, why include them on
the report at all?
Hope this helps!
--
Ben Sullins
http://bensullins.com

How to hide subtotals if only 1 row in group?

Hello,

I have established a group so that I can provide subtotals following the presentation of the group's detail rows. I'm finding that many of the groups have only one row and my subtotals just echo what was already presented on the detail line. The overall effect is confusing to the user and a real vertical space-waster.

Can someone give me a technique or expression for hiding subtotals when the group size is 1 but showing subtotals when the group size is >= 2? If I hide the group when the group size is 1 row then the one detail row is hidden as well, no?

Thanks!

BCB

You can give your visibility the expression to count rows in that group and then False if more than 1|||

But I just want to hide the subtotal line. Won't your approach hide the one detail line that belongs to the group as well as the group footer that contains the redundant subtotals?

Thanks.

|||

Gotcha, another technique would be to make the font white or transparent by using the sae kind of expression....in this case you can determine exactly which text blocks you want to use

only problem is spacing issues

|||

I'm, essentially, trying to do the same thing.

In my case, my report is a list of employees that can be generated for either full timers, part timers, or both.

I've set up a group on the full/part time field FT_PT. There is no group header, just a group footer.

When I generate the report for both I get:

Department Header
Full-Time Detail
FT_PT Group Footer
Part-Time Detail
FT_PT Group Footer
Department Footer

This is perfect.

When I generate it for, say, just Full-Time, I get:

Department Header
Full-Time Detail
FT_PT Group Footer
Department Footer

I don't want to see the FT_PT group footer. What I want is:

Department Header
Full-Time Detail
Department Footer

Gotta be a way to do this without having a blank line where the Full-Time Group Footer would be, isn't there?