Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.

> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set

> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:

> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are n
ot
> directly dependent, just somewhat later the log begins growing, but no err
or
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written t
o
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
>
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
it[vbcol=seagreen]
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field ntext in the table only stores 256 characters

declare @.mensagem varchar(8000)
CREATE TABLE #Mensagem (mensagem text)
set @.mensagem = 'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
insert into #Mensagem select @.mensagem
select * from #MensagemHi Frank.
I ran that under SQL2KEE & it produced a perfect result..
The Query Analyser truncates column output to 256 characters by default, so
try setting your "Maximum characters per column" to something higher (eg
8000) under Query Analyser's "Tools/Options" menu, "Results" tab.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:#ZTWHAgjDHA.2592@.TK2MSFTNGP10.phx.gbl...
>
>
> declare @.mensagem varchar(8000)
> CREATE TABLE #Mensagem (mensagem text)
> set @.mensagem =>
'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
> insert into #Mensagem select @.mensagem
> select * from #Mensagem
>

Field not found: 'System.Collections.Generic.KeyValuePair`2.Value'

I get a Field not found: 'System.Collections.Generic.KeyValuePair`2.Value' when i try to open up a table in SQL Server 2005 Managment Studio. I have the June CTP of SQL 2005 and I have the July CTP of Visual Studio 2005 Professional. Can anyone help me cause i'm bout to lose my mind. I'm hoping to find the real problem because i'm thinking that this error message does not lead to exactly the problem. I would appreciate any help that you all can give me.
Thanks,

MatthewHello Mulktide!
I encountered the same error. Did you manage to solve the problem?

Thanks.
pax

Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Field limits

Simple question, I think.
What is the # of fields (columns) SQL Server 2000 is
limited to in one table ?1024 columns.
-Sue
On Mon, 11 Aug 2003 16:30:28 -0700, "Michael forer"
<mykiv@.hotmail.com> wrote:
>Simple question, I think.
>What is the # of fields (columns) SQL Server 2000 is
>limited to in one table ?sql

field in an embedded sql

I added full text indexing on the title field in a table but when i reference
the same field in an embedded sql it says cannot use contain on a field that
is not full text indexed.
example
SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script, SUBSTRING(i.
text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS
material_id
FROM
(
SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
edit_duration, p.title,
CONVERT(char(10), p.air_date, 101) AS air_date, SUBSTRING(CONVERT(varchar(10),
p.edit_start_time, 114), 1, 8) AS edit_start_time
FROM dbo.na_rundown_tbl p
WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
) AS u
INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
contains (u.title ,'%midlothian%')
error message received:
Msg 7601, Level 16, State 3, Line 1
Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it is
not full-text indexed.
what does sp_help_fulltext_tables 'catalogname','title' return?
make sure you replace catalogname with the name of your catalog.
Hilary Cotter
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
"ARPREET" <u31535@.uwe> wrote in message news:6d68cc930bce5@.uwe...
>I added full text indexing on the title field in a table but when i
>reference
> the same field in an embedded sql it says cannot use contain on a field
> that
> is not full text indexed.
> example
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id
> AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
> contains (u.title ,'%midlothian%')
> error message received:
> Msg 7601, Level 16, State 3, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it
> is
> not full-text indexed.
>
|||title is a field name . I type sp_help_fulltext_tables 'catalogname',
'tablename' it returns one row
I had created the index using the below script
create fulltext catalog cat1
create unique index ui_rundown_tbl on na_rundown_tbl (rundown_id)
create fulltext index on na_rundown_tbl (title)
key index ui_rundown_tbl on cat1 with change_tracking auto
Hilary Cotter wrote:[vbcol=seagreen]
>what does sp_help_fulltext_tables 'catalogname','title' return?
>make sure you replace catalogname with the name of your catalog.
>[quoted text clipped - 34 lines]
|||Hello ARPREET,
Move your contains inside the derived table.
u resolves to a derived table and not the underlying table na_rundown_tbl.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname,
> i.ar_material_id AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id
> where
> contains (u.title ,'%midlothian%')

field in a table that stores my IN condition (was "In")

Hello,

Alright here is the issue. I have a field in a table that stores my IN condition.

S0 I have a field that contains data like this: 'a','b','c'

basically I now just want that to be my IN clause...any ideas?

I tried something like this
declare @.in as varchar(10)
set @.in = (select field from table)

select * from table
where field in (@.in)

of course that does not work...and many variations of that also.

Let me know if you can lead me down the right path on this.You may consider modifying your design to store 'a', 'b', 'c' as separate rows in your table instead of a string of possible values. That makes the SQL easier and you can do things like joins instead of the IN clause.

Field Description Won't Save in MSDE Table

I've been working with numerous tables (creating them, that is) in
MSDE.
Everything's working fine aside from the fact that I can't seem to get
any descriptive information on any field in any table to save.
I add the descriptive text to the field, click to save the table with
the newly-entered description, the hourglass displays and the table is
being saved, and then when the design view of the table refreshes, the
descriptions I've just entered on the various fields are gone.
Anyone else have problems with this?
Thanks!
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
What application are you using to design the table?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405070905.9a523cb@.posting.google.co m...
> I've been working with numerous tables (creating them, that is) in
> MSDE.
> Everything's working fine aside from the fact that I can't seem to get
> any descriptive information on any field in any table to save.
> I add the descriptive text to the field, click to save the table with
> the newly-entered description, the hourglass displays and the table is
> being saved, and then when the design view of the table refreshes, the
> descriptions I've just entered on the various fields are gone.
> Anyone else have problems with this?
> Thanks!
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com
|||I'm using MSDE Manager by Vale Software and haven't found anything
dealing with this issue on their website.
Thanks for any additional information/assistance/recommendations you
might be able to offer.
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#DZOEnFNEHA.808@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> What application are you using to design the table?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
> news:52031869.0405070905.9a523cb@.posting.google.co m...
|||hi Brad,
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> ha scritto nel messaggio
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
>
coul'd be a "feature" of MSDE Manager, as both Enterprise Manager and my own
management tool, available at the link following my sign., provide the
expectet behaviour...
please contact the application provider...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You should contact the vendor directly. I imagine it's not supported
because column descriptions are kind of superfluous. I tend to shy away
from storing column descriptions in the database, and instead opt for good
documentation on my tables. A user can always look at the documentation but
shouldn't be forced to read a (max 255?) character description by connecting
to the database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com

Field Default Value

I'm copying MyDbTable to TempdbTable. There are 12 fields and I'm ignoring 11 of them when copying the one table to another with DTSWizard.

Once the TempdbTable is there, I'm adding 2 fields along with the one that is there already.

1) field1 int, Nullable, Default value ((3))

2) field2 tinyint, Nullable, Default value ((1))

The reason why I'm creating these two fields and giving them a value so they will match up when I import TempdbTable to MyDb2ndTable.

The problem I'm having is... field1 and field2 have a Null value intead of the value I'm giving them.

I'm new at this, so maybe the cause of the problem is obvious, but if anyone can help me I'd really appreciate it.

Thanks,

Bill

When you add the new columns, you MUST make them NOT NULL if you wish to set a DEFAULT value and assign that default value to the existing rows.

ALTER TABLE #MyTable
ADD MyNewColumn varchar(30) NOT NULL DEFAULT 'N/A'

|||

Thanks for responding Arnie.

I Ran This...

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3'
ADD isconfirmed tinyint NOT NULL DEFAULT '1'

I Got this Error...

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'isconfirmed'.

When I added one field only I got something like...

The table doesn't exist or you don't have permission.

Thanks,

Bill

|||

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3',
isconfirmed tinyint NOT NULL DEFAULT '1'

(without the additional ADD)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

This it the error I'm getting when running that, and the table is definitely there.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "#xlaANLsubscribers" because it does not exist or you do not have permissions.

Thanks,

Bill

|||Was the #Temp table created by the same user in the same session/connection?|||

Thanks Arnie,

I imported the records into the table of the database with DTSWizard authenicated in Windows. I then copied this table to Temp with the same authenication, but I've been running your script and doing the editing of the Temp table in SQL authenication. So I tried running your script and editing in Windows authenication and the same error happened. How that's not to confusing.

Thanks,

Bill

|||Unless you specify the ## as a prefix instead of a single #, the table is only available to the session where it was created in. Even other connected session might have no access to the table.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||I'm getting the same error even when I specify ## as the prefix of the table.|||

Bill,

Does this code execute without error.

Code Snippet

SET NOCOUNT ON

CREATE TABLE #MyTable
( RowID int IDENTITY,
Name varchar(20)
)

INSERT INTO #MyTable VALUES ( 'Bill' )

SELECT *
FROM #MyTable

ALTER TABLE #MyTable
ADD Address varchar(30) NOT NULL DEFAULT 'N/A'

SELECT *
FROM #MyTable

DROP TABLE #MyTable

|||

Arnie,

No errors, it worked perfectly.

|||Then I assume that your executing user is another one than the one which was uses to execute Arnies script, right ? Try to create explicitly dbo.#Something and select also with the full qualified name.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I can only suggest walking back throught the rest of your code (all code that touches the #xlaANLsubscribers table), and verify that there isn't a DELETE TABLE stuck in there somewhere...

If you are creating the #Temp table, and populating it in the same session, it 'should' be there. So I'm baffled at the moment and will have to give this some thought...

|||

Jens,

I thought that schema/owner was ignored in TempDb...

|||I'm using DTSWizard to copy the table from the Main Database to Temp... am I wrong in doing it that way?

Field by Position

Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?

My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.

Bye and thanks...

:D :DYou will need to use dynamic SQL (constructing your SQL Statements at runtime) by referencing the database Schema or the sysobjects and syscolumns tables.|||Uhmmm...why do you need to do this...?|||Often I have duplicate row in table with a lot of columns ... and i wanted a statement to avoid to call each time columns by name ...

The real scope was to find a statement to say : "Get all the columns except primary key ... that I provide by constant expression ...."

Maybe the unique solution is to prepare a program to write statement how i need

Thanks|||why not just write the query with all the column names except the primary key?

that would've taken no more than a minute or two, a lot faster than writing a query to go against the system tables, and a lot faster than writing a program to do that

Field Aliases

I don't know if that is the correct term or not but here is what I want to do:

I have a table in a sql server db that has generic field names (form1, form2, etc). I want the users to be able to define specific names for each field unique to them. Then when the 'table' is displayed to them(via a web page and asp/ado), I want them to see 'their' names for each field rather than the generic ones. Any ideas?

Thanks!!Maybe you can create a users table and a field names table. In the field names table, you create a foreign key to your original generic table and the users table. In your field names table, you create the same number of columns as in your generic table. For every user you can now store the field names.|||I'd recommend against doing this in the first place, but if it is absolutely necessary then the problem falls under data presentation, and is thus best handled by the interface (using cookies, config files, or whatever). The database server is not best suited to perform this task.

blindman|||Thanks for your input. What I did was simply create a table of 'Aliases' that each client could determine. Then, when they opened the original table, instead of seeing the actuall table headings, they see their customized version.sql

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field

I want to have a list of the fileds of a table on the basis of field type.Means only fileds of int type or varchar type this way.Is it possible?You probably want something like:SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'dba_holiday' = TABLE_NAME
AND 'datetime' = DATA_TYPE-PatP

Fetching XML data from Oracle CLOB data field

I have one Oracle Table space which hav one CLOB type table field -- this field have XML data. Now What I doing with:
I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for XML data. Data is comming properly into MS sql 2000 DB.
Now I would to seprate XML field into another table in MS SQL TABe
Example
XML name,city,age,price field will copy to SQL field name,city..
is this possiable iwith MS SQL.
Arvind
Yes. You can use the OpenXML functionality to shred it (see books Online for
information). One caveat: You need to pass the document to T-SQL via a
stored proc using an NTEXT typed parameter if the data is larger than 8kB.
In SQL Server 2000 you can only do so by calling the stored proc from the
client/midtier and not within the server.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>I have one Oracle Table space which hav one CLOB type table field -- this
>field have XML data. Now What I doing with:
> I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for
> XML data. Data is comming properly into MS sql 2000 DB.
> Now I would to seprate XML field into another table in MS SQL TABe
> Example
> XML name,city,age,price field will copy to SQL field name,city..
> is this possiable iwith MS SQL.
> --
> Arvind
|||HI Michael,
Coould you please give one example so I can user OPEN XML. I'd already setup SQL package to get data from Oracle to SQL table. After that I would like to split XML data from SQL TEMP table to another SQL table.
Arvind
"Michael Rys [MSFT]" wrote:

> Yes. You can use the OpenXML functionality to shred it (see books Online for
> information). One caveat: You need to pass the document to T-SQL via a
> stored proc using an NTEXT typed parameter if the data is larger than 8kB.
> In SQL Server 2000 you can only do so by calling the stored proc from the
> client/midtier and not within the server.
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>
>
|||If you have the data inside a SQL table, then you unfortunately cannot
easily pass it through to sp_xml_preparedocument due to no support for
variables of type TEXT/NTEXT. So you either have to get the data out to
OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
the data from the Oracle database passed into the stored proc directly.
There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...[vbcol=seagreen]
> HI Michael,
> Coould you please give one example so I can user OPEN XML. I'd already
> setup SQL package to get data from Oracle to SQL table. After that I
> would like to split XML data from SQL TEMP table to another SQL table.
>
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote:
|||How DO I pased XML data from oracle to MS SQL thru store proc.
My Oracle Table space have 5 field and 1 clob filed for XML.
--
Arvind
"Michael Rys [MSFT]" wrote:

> If you have the data inside a SQL table, then you unfortunately cannot
> easily pass it through to sp_xml_preparedocument due to no support for
> variables of type TEXT/NTEXT. So you either have to get the data out to
> OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
> the data from the Oracle database passed into the stored proc directly.
> There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...
>
>
|||You would write a midtier program (for example using the Oracle OLEDB
provider) and retrieve the information and pass it through ADO to a stored
proc where you have a parameter per field and an NTEXT parameter for the
XML.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:2F32C6BE-BD3E-4F29-AE93-66DA5CEC914A@.microsoft.com...[vbcol=seagreen]
> How DO I pased XML data from oracle to MS SQL thru store proc.
> My Oracle Table space have 5 field and 1 clob filed for XML.
>
> --
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote:

fetching unique pins...

Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
...
100 1864678198
101 7862517189
102 6356178381
...

10 users request a pin at the same time. What is the easiest/best way
to ensure that the 10 users will get 10 different unacquired pins?

Thanks for any help...Bobus wrote:
> Hi,
> I have a table which contains a bunch of prepaid PINs. What is the
> best way to fetch a unique pin from the table in a high-traffic
> environment with lots of concurrent requests?
> For example, my PINs table might look like this and contain thousands
> of records:
> ID PIN ACQUIRED_BY
> DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 different unacquired pins?

Place a Primary Key or Unique constraint on the PIN column. When a
duplicate error occurs generate a new PIN & try to save the new user row
again. Repeate until success.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Unless you're only using the PIN for a one-time operation - somewhere
you are going to save that PIN (in a table). That table is where you'd
put the Primary Key/Unique constraint.

I don't know what the LIMIT function does. If you want to just update
one row you'd indicate which row in the WHERE clause:

UPDATE table_name SET foo = bar WHERE foo_id = 25

foo_id would be a unique value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/GTdYechKqOuFEgEQLJ/wCgxLHQiPaeDWXwsi5BxBpg6tlKmFoAn0tv
KM3PLa2qdl2KzW3Lp/XFHbiv
=gfzL
--END PGP SIGNATURE--

Bobus wrote:
> Thanks, however, we do not generate the PINs ourselves. We simply
> maintain the inventory of PINs which are given to us from a 3rd party.
> Is there a way in SQL to update a single row ala the LIMIT function in
> MYSQL? Something like:
> update tablename set foo = bar limit 1|||Maybe this

select top 1 ID, PIN from pin_table where acquired_by = <not acquired
value> (NOTE: this could be expensive if you use null to signify Not
Acquired, perhaps a non-null value with an index would help).

update pin_table set acquired_by = <acquired value> where ID = <ID from
select
commit

--or --

set up one table containing the unused pins and one containing the used
pins

then
select top 1 ID, PIN from unused_pin
insert into used_pin values (ID, PIN)
delete from unused_pin where ID = ID

commit|||I successfully used a transactional message queue for a similar
scenario.

Besides, try this:

create table #pins(id int identity, PIN decimal(10));
insert into #pins(PIN)values(1000000000);
insert into #pins(PIN)values(1000000001);
insert into #pins(PIN)values(1000000002);
go
create table #point_to_pins(id int identity)
go
--to get a PIN
insert into #point_to_pins default values
select @.@.identity

use @.@.identity to get the PIN, you will not get any collisions ever|||On 13 Feb 2006 20:00:07 -0800, Bobus wrote:

>Hi,
>I have a table which contains a bunch of prepaid PINs. What is the
>best way to fetch a unique pin from the table in a high-traffic
>environment with lots of concurrent requests?
>For example, my PINs table might look like this and contain thousands
>of records:
> ID PIN ACQUIRED_BY
>DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
>10 users request a pin at the same time. What is the easiest/best way
>to ensure that the 10 users will get 10 different unacquired pins?
>Thanks for any help...

Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK, READPAST)
> WHERE Acquired_By IS NULL
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION
> And to get "first row in line", use:
> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK)
> WHERE Acquired_By IS NULL
> ORDER BY Fill in the blanks
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION

Yet a variation is:

SET ROWCOUNT 1
UPDATE PinsTabel
SET @.ID = ID,
@.Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0

It is essential to have a (clustered) index on Acquired_By.

Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.

Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the responses everyone!

MGFoster: I was asking about the "limit" clause so that I could
implement a solution similar to what Erland recommended. This
guarantees no collisions.

Randy: in your solution, I believe there is a chance that two
concurrent requests will end up grabbing the same pin.

Alexander: clever! It's like an Oracle sequence. But, in our
particular case, we could have a problem of unused pins for
transactions which rollback.

Hugo: that should definintely do the trick.

Erland: yours too! I will try them both out.

Thanks for the help!|||Bobus,

When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> When I was solving a similar problem, I did try out the approaches
> suggested by Hugo and Erland. I hate to say that, but I was always
> getting a bottleneck because of lock contention on PinsTable. Maybe I
> was missing something at that time. I had a requirement to produce
> hundreds of PINs per minute at peak times, so I decided to allocate a
> batch of PINs at a time, instead of distrributing them one at a time -
> that took care of lock contention

Bobus said "But, in our particular case, we could have a problem of unused
pins for transactions which rollback."

This would call for a design where you get a start a transaction, get a
pin, use it for whatever purpose, and then commit. But as you say, you
will get contentions on the PINs here, although it's possible that READPAST
hint could help. I did some quick tests, and it seem to work.

The other option as you say is just to grab a PIN or even a bunch of
them. A later job would then examine which PINs that were taken, and
which were never used, and then mark the latter as unused.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Alexander/Erland: thanks for the tips. I will let you know what issues
we run into, if any.

Best wishes.|||Erland,

I think you are right. I looked up that project, and in fact I did not
try READPAST at all. Having observed poor performance, I implemented
batches, which reduced amount of database calls and as a side effect
took care of lock contention. I immediately got good performance and
did not drill down any furhter.|||Erland's solution seems to work great from our initial tests!

Unrelated question, and probably should be another thread, but have any
of you SQL Server geniuses tried PostgreSQL? Any comments, positive or
negative?|||In 25 words or less: It is nice but has the feel of a college project
where grad students kept addinf things to it based on the last academic
fad or thesis topic. I would go with Ingres, which has a "commercial
feel" and a great optimizer.

Fetching sungle record from join

Hello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul JhaHello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul Jha

Are they 3 identical records, or is there something different about them ? If they are identical you can cheese it with a distinct or a group by.|||which one do you want?|||I've never heard of a sungle record|||Rudy asks the correct question here - which of the 3 corresponding records do you want to return? And the answer "it doesn't matter/any of them" doesn't cut it ;)|||And the answer "it doesn't matter/any of them" doesn't cut it ;)Why? He could simply using MAX() or MIN() to get only one record|||MAX or MIN will of course return only one value out of the joined row

what about "the row with the max value"|||Just checking in, pulling up a chair, putting my feet up on the ottoman, leaning back, opening a beer, putting my 3-D glasses on, and waiting for the show...|||BTW, Brett, a "sungle row" is simply a Single row from amongst a Jungle of rows.|||Or he's from New Zealand|||I just might take a stab at this one.

It sounds like rows from t2 are different in some way. If you had data in t2 having to do with say a person and all of the phone numbers they could possibly have, you would get a different row for every phone number.

This is of course, if I am understanding the question correctly.

Fetching Record From Table

Hi,
I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.

Below is the error that I get when tryong to open the record from SSMS

SQL Execution Error.

Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
--------
OK Help
--------

Thanks,
Rahul Jhawhere's the TOP in that query?|||there is no top in the query......... the system wil generate the query when you try to open the table from ssms...........