Multi-Table Update?

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working...I would have suggested a view. What kind of problems are you getting with this approach? Are you trying to update a key value?|||
Another option is to use a stored procedure. You can update multiple tables, one after the other, using a stored procedure. Within the stored procedure you can use variables to hold values retrieved from one table and insert them into the next one.

|||you tend to lose update capabilities with a join when its a many to many join.

if your joining with a primary key it should work|||I'm going to have to learn some more about stored procedures because that would be excellent to do multiple updates like that..

Is there a code sample or link where I can learn more about doing the updates in that manor?

thx|||
Probably your best bet is to find a good book on the topic. In the meantime, you can hava look at the books online:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp?frame=true

You can also download some of theStarter Kits from this website for lots of great samples (see the tab above).

Hope this helps.

Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

Multiple Zip Search

I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.

Can someone clue me in to what the commandtext of my query should look like?

Thanks!

EXEC( 'SELECT * FROM YourTable where YourZip IN (''' + @.z + ''')' )

where @.z = list of zip codes.

hth|||Well if you are inclined to avoid D-SQL and are using SQL Server 2000, you can use a UDF
or many other techniques which would prove much better than D-SQL.|||My problem is that I'm a web guy with just enough SQL 2000 experience to be dangerous. I'm not exactly sure that the first answer given would help me because the zip codes are different for every query. I have a list of zip codes and I have a parameter set up in my dataadapter (@.zip), but I can't get any results. It's probably an issue with single or double quotes, but i'm not sure. I'm putting all the zip codes together into a session variable, trying to get something like this:

WHERE ZIP LIKE '%80000%' OR ZIP LIKE '%72322%' etc. There can be up to 75 zips in one query.

I really appreciate the help!|||A user defined function to create an query with IN ('zip,zip,etc') is your best bet.

Here is someting I wrote a long time ago but it was similar to what you could do.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = N'fnConvertListToStringTable')
DROP FUNCTION [dbo].[fnConvertListToStringTable]
GO
CREATE FUNCTION [dbo].[fnConvertListToStringTable] (
@.list varchar(1000)
)
RETURNS @.ListTable table (ItemID varchar(250) NULL)
AS
BEGIN
IF(SUBSTRING(@.list,LEN(@.list),1) <> ',')
BEGIN
SET @.list = @.list + ','
END
DECLARE @.StartLocation int
DECLARE@.Length int
SET @.StartLocation = 0
SET @.Length = 0
SELECT @.StartLocation = CHARINDEX(',',@.list,@.StartLocation+1)
WHILE @.StartLocation > 0
BEGIN
IF(LOWER(RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))) = 'null'
INSERT INTO @.ListTable(ItemID) VALUES (Null)
ELSE
BEGIN
INSERT INTO @.ListTable(ItemID)
VALUES (RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))
END
SET @.Length = @.StartLocation
SET @.StartLocation = CHARINDEX(',', @.list, @.StartLocation +1)
END
RETURN
END
GO
select * from dbo.fnConvertListToStringTable(' yjyj, 1345, 1234,NUll')

The code above actually put the values in a table and then used a where in (Select from that table) but in your case you can juse use the part that does the parsing at the beginning.sql

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005

I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.

Update Race:

IFNotUPDATE(Edited)

UPDATE Patient

SET Race =(SELECTCASE Racecode

WHEN'API'THEN'Asian, Pacific Islander'

WHEN'BH'THEN'Black, Hispanic'

WHEN'BNH'THEN'Black, Non-Hispanic'

WHEN'HIS'THEN'Hispanic'

WHEN'OTH'THEN'Other, Black & White'

WHEN'WH'THEN'White, Hispanic'

WHEN'WNH'THEN'White, Non-Hispanic'

END)

WHERE id# IN(SELECT id# FROM inserted)

END

Update Religion:

IF Not UPDATE (Edited)

Update Patient

SET Religion = (SELECT CASE Religioncode

WHEN 'C' THEN 'Catholic'

WHEN 'J' THEN 'Jewish'

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Protestant'

WHEN 'U' THEN 'Unknown'

END)

WHERE id# IN (SELECT id# FROM Inserted)

END

why not combining them into one update.

IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!

Multiple time stamp values in MSDE

Hi - I am a bit of an amatuer, but I am trying to use the EMS data Pump to get all of the data out of an Interbase 6 DB into an MSDE DB. I have managed to instal an Interbase ODBC driver, and I ca connect to it and then automatically create the tables in my MSDE DB, but I get an error stating that there cannot be multiple Timestamp columns in the MSDE databse. Is this true ?Are you sure you need TIMESTAMP fields? Could it be that you only need multiple DATETIME fields? Besides, the reason you can't have more than 1 true TIMESTAMP field per table is because ... it wouldn't make sense to have more than 1.|||I will look into that. I am just converting an old interbase databse to MSDE so that I can write another app to use its data.

Why whould it mkae no sense to have more than one timestamp field ?

I will try to convert the column to a datetime field. Thanks|||TIMESTAMP is often confused for GETDATE(). The value of TIMESTAMP field is used by the engine as a row version identifier, rather then as a DATE and TIME. It's actually an auto-generated binary number that stays unique within a database, and as per definition has nothing to do with neither date nor time.

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple time dimensions and inventory measures.

I got 2 time-dimentions:

[Week]
Hierarchised dimension based on Year - Halfyear - Week - Day !

The supply calculated based on the Week-dimension
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)

[Posting Period]
Hierarchised dimension based on Year - Quarter - Month - Day !

The supply calculated based on the Posting Period-dimension
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)

My question:

iif(Is the Week-time-dimension used in the pivottable or cube-browse ?,
--Yes
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)
,
--No, the posting period time-dimension will be used probably
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)
)

Is there a way to merge the 2 time-dimensions without having duplicates in my measures ?

Kind regards,
GG

Here is an example from Adventure Works that shows how you can detect whether a user has selected members from the "Calendar" or "Fiscal" hierarchy. You should be able to adapt this to the problem you are trying to solve.

WITH

MEMBER Measures.[Time Hierarchy Used]

AS

CASE

WHEN [Date].Fiscal.CurrentMember.Level.Ordinal > 0 THEN

"Fiscal"

ELSE

"Calendar"

END

SELECT

{[Date].Calendar.DefaultMember.Children} ON ROWS,

--{[Date].Fiscal.DefaultMember.Children} ON ROWS,

{Measures.[Time Hierarchy Used]} ON COLUMNS

FROM

[Adventure Works]

HTH,

Steve

Multiple threads or not?

SQL 7.0/NT 4.0 latest SPs
Performance question.
One application will be writing to two tables. 1000s of
records at a time, in bursts, and at the same time, the
tables will be serving up data to end-users from a
separate application.
Question: Should there be a separate thread written to
each table that the appliation is writing to? Would that
help performance getting the records in faster and reduce
the amount of over-head so that the users making their
requests aren't slow in getting their data.
Thank you for your help.
DonHow are you inserting them? Have you tried using Bulk Insert?
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> SQL 7.0/NT 4.0 latest SPs
> Performance question.
> One application will be writing to two tables. 1000s of
> records at a time, in bursts, and at the same time, the
> tables will be serving up data to end-users from a
> separate application.
> Question: Should there be a separate thread written to
> each table that the appliation is writing to? Would that
> help performance getting the records in faster and reduce
> the amount of over-head so that the users making their
> requests aren't slow in getting their data.
> Thank you for your help.
> Don
>|||The developer is using INSERT. Looks like one record at
at time from C++.
Bulk insert would be better?
Don
>--Original Message--
>How are you inserting them? Have you tried using Bulk
Insert?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Don" <ddachner@.yahoo.com> wrote in message
>news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
>> SQL 7.0/NT 4.0 latest SPs
>> Performance question.
>> One application will be writing to two tables. 1000s of
>> records at a time, in bursts, and at the same time, the
>> tables will be serving up data to end-users from a
>> separate application.
>> Question: Should there be a separate thread written to
>> each table that the appliation is writing to? Would
that
>> help performance getting the records in faster and
reduce
>> the amount of over-head so that the users making their
>> requests aren't slow in getting their data.
>> Thank you for your help.
>> Don
>
>.
>|||A Bulk Insert would generally be many times faster than individual inserts.
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >|||Bulk insert techniques are the fastest way to load large volumes of data
into SQL Server. You can use SQLOLEDB IRowserFastLoad or ODBC Bulk Copy
to load data directly from a C++ program. To load data from files, you
can use T-SQL BULK INSERT, the BCP command-line utility or DTS.
Parallel loading (e.g. multiple threads) can improve throughput further.
However, you may not need to resort to that since you can probably load
thousands of rows in a few seconds.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >

Multiple stored procedures ,same tables

Hi,

I am using multiple stored procedures which are using same set of tables at a time .

As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.

My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins

where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you

Raj Deep.A

Create a parent stored procedure then create your temp tables there. Then you just call your other stored procedures. They should then be able to use the temp tables you've created in the parent stored procedure.

e.g.

Code Snippet

create procedure usp_parent

as

select *

into #tmp

from sysobjects

exec usp_child1

exec usp_child2

go

create procedure usp_child1

as

select 'usp_child1',*

from #tmp

go

create procedure usp_child2

as

select 'usp_child2',*

from #tmp

go

exec usp_parent

go

drop procedure usp_child1,usp_child2,usp_parent

go

|||

If you have SQL 2005 and your application is critical I should mirror the database (with database mirroring feature); i'll try creating a database snapshot too.

Then i should divide the execution some of sp's on a database some of other.

|||

Hi Oj,

If you know about the reports,for each select statement i want to show i need a seperate stored procedures or same stored procedure should be called multiple times with different parameters which returns different select statement based on the parameters i passed.

i cant avoid using multiple stored procedures. i think i should have an option where can i use some feature of sql server for not creating locks.

Thnk you

Raj Deep.A

|||

You can take a look at NOLOCK hint in book online.

e.g.

Code Snippet

select *

from tb with(nolock)

where col1=123

Multiple stored procedures ,same tables

Hi,

I am using multiple stored procedures which are using same set of tables at a time .

As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.

My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins

where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you

Raj Deep.A

Create a parent stored procedure then create your temp tables there. Then you just call your other stored procedures. They should then be able to use the temp tables you've created in the parent stored procedure.

e.g.

Code Snippet

create procedure usp_parent

as

select *

into #tmp

from sysobjects

exec usp_child1

exec usp_child2

go

create procedure usp_child1

as

select 'usp_child1',*

from #tmp

go

create procedure usp_child2

as

select 'usp_child2',*

from #tmp

go

exec usp_parent

go

drop procedure usp_child1,usp_child2,usp_parent

go

|||

If you have SQL 2005 and your application is critical I should mirror the database (with database mirroring feature); i'll try creating a database snapshot too.

Then i should divide the execution some of sp's on a database some of other.

|||

Hi Oj,

If you know about the reports,for each select statement i want to show i need a seperate stored procedures or same stored procedure should be called multiple times with different parameters which returns different select statement based on the parameters i passed.

i cant avoid using multiple stored procedures. i think i should have an option where can i use some feature of sql server for not creating locks.

Thnk you

Raj Deep.A

|||

You can take a look at NOLOCK hint in book online.

e.g.

Code Snippet

select *

from tb with(nolock)

where col1=123

Multiple Search

Does anyone know how to search for more than one key word
at a time in MS Access 2000?
Example: Cars or Trucks (in a Memo field)Please post this to an MS Access newsgroup.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Mike" <mike@.countrymedic.com> wrote in message
news:037e01c33f3a$6e3695f0$a101280a@.phx.gbl...
Does anyone know how to search for more than one key word
at a time in MS Access 2000?
Example: Cars or Trucks (in a Memo field)

Multiple rows inserations

A normal insert statement allows us to insert one row at a time. But I would like to know if it is possible to insert many rows in a single SQL statement.I am NOT asking about the BCP or the Bulk insert option, because my data is not in any kind of file. My requirement is to insert 6 rows into a table at one shot.
Is this possible?

Quote:

Originally Posted by sajithamol

A normal insert statement allows us to insert one row at a time. But I would like to know if it is possible to insert many rows in a single SQL statement.I am NOT asking about the BCP or the Bulk insert option, because my data is not in any kind of file. My requirement is to insert 6 rows into a table at one shot.
Is this possible?

In what format is you data? You could probably write a procedure to insert more rows