How to lock a particular Row in a sql server

Hi! Group,
this is shiva shanker,
i have a problem , while insert a record in a table , i want to lock
the particular row. so that no one can insert the record during that
period of time.
plz help me,
thanks in adv.
shiva shanker.insert by definition is adding new data into a table. Your insert should not
affect others from inserting into the same table if the table is not
exclusive locked. Take a look at 'tablock or tablockx' in book online.
-oj
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
> Hi! Group,
>
> this is shiva shanker,
> i have a problem , while insert a record in a table , i want to lock
> the particular row. so that no one can insert the record during that
> period of time.
>
> plz help me,
>
>
> thanks in adv.
> shiva shanker.
>|||Yes oj is right,
Shiva your requirement is not clear, is it osmething like when you insert no
other person should insert as you need a sequence of identity number specifi
c
to your insert. In that case go for table lock.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"oj" wrote:

> insert by definition is adding new data into a table. Your insert should n
ot
> affect others from inserting into the same table if the table is not
> exclusive locked. Take a look at 'tablock or tablockx' in book online.
>
> --
> -oj
>
> "shiva" <bany.shanker@.gmail.com> wrote in message
> news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
>
>

How to locate last occurrence of character in string

charindex() function lets you find first occurrence of a particular
character in a string.
Is there similar function to find last occurrence of character in
string.
I could use reverse() or some script to find that, but it will be
inefficient.
Could anybody suggest me efficient solution for this.
Thanks and Regards,
AvinAvi, please do not multipost as I have already answered in.programming
group.
"Avi" <avimandale@.gmail.com> wrote in message
news:271710bf-a8dd-4a82-8bc2-076b920d6919@.k39g2000hsf.googlegroups.com...
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin|||On Jan 28, 5:25=A0pm, Avi <avimand...@.gmail.com> wrote:
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin
Hi Avin,
declare @.string1 varchar(50), @.string2 varchar(50)
select @.string1 =3D 'abc', @.string2=3D 'oooabcooabco'
select (datalength(@.string2)-1)-
charindex(reverse(@.string1),reverse(@.string2))
HTH

how to list all tables used in a database?

Hi,
I want to list all the tables used in a particular database during the
execution of a lot of DTS packages.
I think the profiler could help me, but what is the configuration to
accomplish this?
event, columns, filters...
thanks for your guide
Jerome.
Hi
The below script find an emtpy tables , modify it for your needs
use pubs
-- create table and do not populate it
create table EmptyTable (c1 int)
go
-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @.SQLString nvarchar (255),
@.ParmDefinition nvarchar (255)
DECLARE @.tablename sysname, @.Empty char (1)
DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN FindEmptyTables
FETCH NEXT FROM FindEmptyTables INTO @.tablename
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
@.Empty = ''N'' ELSE set @.Empty = ''Y'''
SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.tablename = @.tablename,
@.Empty = @.Empty OUTPUT
IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
FETCH NEXT FROM FindEmptyTables INTO @.tablename
END
CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
GO
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> Hi,
> I want to list all the tables used in a particular database during the
> execution of a lot of DTS packages.
> I think the profiler could help me, but what is the configuration to
> accomplish this?
> event, columns, filters...
> thanks for your guide
> Jerome.
>
|||I don't want to list tables availables, I want to list table REALLY used
during the loading process.
I mean tables which is part of a Select statement.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Hi
> The below script find an emtpy tables , modify it for your needs
> use pubs
> -- create table and do not populate it
> create table EmptyTable (c1 int)
> go
> -- create a cursor to go through each table in a database
> -- and print the name of any empty tables
> DECLARE @.SQLString nvarchar (255),
> @.ParmDefinition nvarchar (255)
> DECLARE @.tablename sysname, @.Empty char (1)
> DECLARE FindEmptyTables CURSOR READ_ONLY
> FOR SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> OPEN FindEmptyTables
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> WHILE (@.@.fetch_status = 0)
> BEGIN
> SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> EXECUTE sp_executesql
> @.SQLString,
> @.ParmDefinition,
> @.tablename = @.tablename,
> @.Empty = @.Empty OUTPUT
> IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> END
> CLOSE FindEmptyTables
> DEALLOCATE FindEmptyTables
> GO
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>
|||Ok, run SQL Server Profiler to identify those tables , save the data into a
table in SQL Server and make a query to get report.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> I don't want to list tables availables, I want to list table REALLY used
> during the loading process.
> I mean tables which is part of a Select statement.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
>
|||yes, I know...
but what filters, events and columns I have to select to accomplish this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUm3IlxSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Ok, run SQL Server Profiler to identify those tables , save the data into
> a
> table in SQL Server and make a query to get report.
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
>

how to list all tables used in a database?

Hi,
I want to list all the tables used in a particular database during the
execution of a lot of DTS packages.
I think the profiler could help me, but what is the configuration to
accomplish this?
event, columns, filters...
thanks for your guide
Jerome.Hi
The below script find an emtpy tables , modify it for your needs
use pubs
-- create table and do not populate it
create table EmptyTable (c1 int)
go
-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @.SQLString nvarchar (255),
@.ParmDefinition nvarchar (255)
DECLARE @.tablename sysname, @.Empty char (1)
DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN FindEmptyTables
FETCH NEXT FROM FindEmptyTables INTO @.tablename
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
@.Empty = ''N'' ELSE set @.Empty = ''Y'''
SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.tablename = @.tablename,
@.Empty = @.Empty OUTPUT
IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
FETCH NEXT FROM FindEmptyTables INTO @.tablename
END
CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
GO
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> Hi,
> I want to list all the tables used in a particular database during the
> execution of a lot of DTS packages.
> I think the profiler could help me, but what is the configuration to
> accomplish this?
> event, columns, filters...
> thanks for your guide
> Jerome.
>|||I don't want to list tables availables, I want to list table REALLY used
during the loading process.
I mean tables which is part of a Select statement.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Hi
> The below script find an emtpy tables , modify it for your needs
> use pubs
> -- create table and do not populate it
> create table EmptyTable (c1 int)
> go
> -- create a cursor to go through each table in a database
> -- and print the name of any empty tables
> DECLARE @.SQLString nvarchar (255),
> @.ParmDefinition nvarchar (255)
> DECLARE @.tablename sysname, @.Empty char (1)
> DECLARE FindEmptyTables CURSOR READ_ONLY
> FOR SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> OPEN FindEmptyTables
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> WHILE (@.@.fetch_status = 0)
> BEGIN
> SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> EXECUTE sp_executesql
> @.SQLString,
> @.ParmDefinition,
> @.tablename = @.tablename,
> @.Empty = @.Empty OUTPUT
> IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> END
> CLOSE FindEmptyTables
> DEALLOCATE FindEmptyTables
> GO
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>|||Ok, run SQL Server Profiler to identify those tables , save the data into a
table in SQL Server and make a query to get report.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> I don't want to list tables availables, I want to list table REALLY used
> during the loading process.
> I mean tables which is part of a Select statement.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
>|||yes, I know...
but what filters, events and columns I have to select to accomplish this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUm3IlxSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Ok, run SQL Server Profiler to identify those tables , save the data into
> a
> table in SQL Server and make a query to get report.
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
>

how to list all tables used in a database?

Hi,
I want to list all the tables used in a particular database during the
execution of a lot of DTS packages.
I think the profiler could help me, but what is the configuration to
accomplish this?
event, columns, filters...
thanks for your guide
Jerome.Hi
The below script find an emtpy tables , modify it for your needs
use pubs
-- create table and do not populate it
create table EmptyTable (c1 int)
go
-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @.SQLString nvarchar (255),
@.ParmDefinition nvarchar (255)
DECLARE @.tablename sysname, @.Empty char (1)
DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN FindEmptyTables
FETCH NEXT FROM FindEmptyTables INTO @.tablename
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
@.Empty = ''N'' ELSE set @.Empty = ''Y'''
SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.tablename = @.tablename,
@.Empty = @.Empty OUTPUT
IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
FETCH NEXT FROM FindEmptyTables INTO @.tablename
END
CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
GO
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> Hi,
> I want to list all the tables used in a particular database during the
> execution of a lot of DTS packages.
> I think the profiler could help me, but what is the configuration to
> accomplish this?
> event, columns, filters...
> thanks for your guide
> Jerome.
>|||I don't want to list tables availables, I want to list table REALLY used
during the loading process.
I mean tables which is part of a Select statement.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Hi
> The below script find an emtpy tables , modify it for your needs
> use pubs
> -- create table and do not populate it
> create table EmptyTable (c1 int)
> go
> -- create a cursor to go through each table in a database
> -- and print the name of any empty tables
> DECLARE @.SQLString nvarchar (255),
> @.ParmDefinition nvarchar (255)
> DECLARE @.tablename sysname, @.Empty char (1)
> DECLARE FindEmptyTables CURSOR READ_ONLY
> FOR SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> OPEN FindEmptyTables
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> WHILE (@.@.fetch_status = 0)
> BEGIN
> SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> EXECUTE sp_executesql
> @.SQLString,
> @.ParmDefinition,
> @.tablename = @.tablename,
> @.Empty = @.Empty OUTPUT
> IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> END
> CLOSE FindEmptyTables
> DEALLOCATE FindEmptyTables
> GO
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>> Hi,
>> I want to list all the tables used in a particular database during the
>> execution of a lot of DTS packages.
>> I think the profiler could help me, but what is the configuration to
>> accomplish this?
>> event, columns, filters...
>> thanks for your guide
>> Jerome.
>>
>|||Ok, run SQL Server Profiler to identify those tables , save the data into a
table in SQL Server and make a query to get report.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> I don't want to list tables availables, I want to list table REALLY used
> during the loading process.
> I mean tables which is part of a Select statement.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> > Hi
> > The below script find an emtpy tables , modify it for your needs
> > use pubs
> >
> > -- create table and do not populate it
> > create table EmptyTable (c1 int)
> > go
> >
> > -- create a cursor to go through each table in a database
> > -- and print the name of any empty tables
> > DECLARE @.SQLString nvarchar (255),
> > @.ParmDefinition nvarchar (255)
> >
> > DECLARE @.tablename sysname, @.Empty char (1)
> >
> > DECLARE FindEmptyTables CURSOR READ_ONLY
> > FOR SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > OPEN FindEmptyTables
> >
> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
> > WHILE (@.@.fetch_status = 0)
> > BEGIN
> > SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> > @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> > SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> >
> > EXECUTE sp_executesql
> > @.SQLString,
> > @.ParmDefinition,
> > @.tablename = @.tablename,
> > @.Empty = @.Empty OUTPUT
> >
> > IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
> > END
> >
> > CLOSE FindEmptyTables
> > DEALLOCATE FindEmptyTables
> > GO
> >
> > "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> > news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> >> Hi,
> >>
> >> I want to list all the tables used in a particular database during the
> >> execution of a lot of DTS packages.
> >>
> >> I think the profiler could help me, but what is the configuration to
> >> accomplish this?
> >> event, columns, filters...
> >>
> >> thanks for your guide
> >> Jerome.
> >>
> >>
> >
> >
>|||yes, I know...
but what filters, events and columns I have to select to accomplish this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUm3IlxSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Ok, run SQL Server Profiler to identify those tables , save the data into
> a
> table in SQL Server and make a query to get report.
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> I don't want to list tables availables, I want to list table REALLY used
>> during the loading process.
>> I mean tables which is part of a Select statement.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
>> > Hi
>> > The below script find an emtpy tables , modify it for your needs
>> > use pubs
>> >
>> > -- create table and do not populate it
>> > create table EmptyTable (c1 int)
>> > go
>> >
>> > -- create a cursor to go through each table in a database
>> > -- and print the name of any empty tables
>> > DECLARE @.SQLString nvarchar (255),
>> > @.ParmDefinition nvarchar (255)
>> >
>> > DECLARE @.tablename sysname, @.Empty char (1)
>> >
>> > DECLARE FindEmptyTables CURSOR READ_ONLY
>> > FOR SELECT TABLE_NAME
>> > FROM INFORMATION_SCHEMA.TABLES
>> > WHERE TABLE_TYPE = 'BASE TABLE'
>> >
>> > OPEN FindEmptyTables
>> >
>> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
>> > WHILE (@.@.fetch_status = 0)
>> > BEGIN
>> > SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
>> > @.Empty = ''N'' ELSE set @.Empty = ''Y'''
>> > SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
>> >
>> > EXECUTE sp_executesql
>> > @.SQLString,
>> > @.ParmDefinition,
>> > @.tablename = @.tablename,
>> > @.Empty = @.Empty OUTPUT
>> >
>> > IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
>> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
>> > END
>> >
>> > CLOSE FindEmptyTables
>> > DEALLOCATE FindEmptyTables
>> > GO
>> >
>> > "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> > news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>> >> Hi,
>> >>
>> >> I want to list all the tables used in a particular database during the
>> >> execution of a lot of DTS packages.
>> >>
>> >> I think the profiler could help me, but what is the configuration to
>> >> accomplish this?
>> >> event, columns, filters...
>> >>
>> >> thanks for your guide
>> >> Jerome.
>> >>
>> >>
>> >
>> >
>>
>