READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

read data from log

hi all

how i can read all the querys that send to the server

exmple

insert , update statement need all this statement at the end of the day how can i do that by sqlserver

thanks alot

SQL Server Profiler would be one way. TSQL events.|||Start up SQL Server Profiler. Create a new trace. Specify the desired events. Run the trace. You can collect the events into either a flat-file or a table (useful for issues additonal queries against it for integoration purposes).|||Thanks very much friends|||

If you are satisifed please mark an answer.

Thanks,

D

Reached nvarchar(4000) limit in building SQL statement.

SQL Server 2000 SP3a
Im using a sproc to make a sql statement. The statement is built up and
assigned to @.SQL1 nvarchar(4000), then at the end of the sproc its runs:
exec sp_executesql @.SQL1
The problem is that I've reached the 4000 limit!! How do other people get
round this, bearing in mind that its full of inner joins with sub selects, s
o
I dont think I can split it out. And because the sproc can create a view
which is updateable, I can't turn those sub selects into views as this makes
the newly created view non-updateable.
How can I build up a SQL string that is bigger than 4000 please.Split it up into multiple char/varchar variables and do:
EXEC( @.var1 + @.var2 + ...+ @.varn )
Anith|||Off the top of my head, can you incrementally build this using temp
tables and/or table variables?
Maybe something like...
procedure MyProc
as
-- do something with #temp1
-- do something with #temp2
select * from #temp1
join #temp1 on #temp2.something = #temp1.something
go
Just a thought.
Bryce|||If that means I can link several statements, which each on their own don't
make sense, together and execute in one go that would be perfect!!!
I won't be able to check til Monday, so many thanks in advance, I was
getting REALLY worried that many hours of trying to get get a one sproc does
all approach was going to fall at the last hurdle.
I shall read some more on exec / sp_executesql as it sounds like more can be
done than I had assumed.
Many thanks!
"Anith Sen" wrote:

> Split it up into multiple char/varchar variables and do:
> EXEC( @.var1 + @.var2 + ...+ @.varn )
> --
> Anith
>
>

Re: Left function causing Sum function not working??

Hi all,

My query won't add up the quantities after adding LEFT function in SQL statement can anyone see why?

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, SUM(dbo.tblBag_data.bag_quantity) AS qty_on_hand,
dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num,
dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
LEFT(dbo.tblShipping_sched.work_ord_num, 6) = LEFT(dbo.tblBag_data.work_ord_num, 6)
WHERE (dbo.tblShipping_sched.work_ord_num LIKE '343024%')
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num

''this s/b one record w/ summed qty
Work_order line_num qty_on_hand cust_num cust a_p_n a_c_n
343024-01 001 16540 32246 Davol Inc R1082061 R00295-059-70EPF-HM+
343024-01 001 27344 32246 Davol Inc R1082061 R00295-059-70EPF-HM+

Thanks for your input.because your GROUP BY specifies the individual dbo.tblBag_data.bag_quantity values

Re: What is the syntax for isnull in the IIF function in the query statement

Hi all,

What is the syntax for isnull in the IIF function in the query statement for SQL Server2K?

I'm trying to create a view to get a total in a field. If the quantity is null I want to display 0 in that field.

Thanks!!

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, IIf(dbo.tblBag_data.bag_quantity IS NULL, 0,
dbo.tblBag_data.bag_quantity) AS qty_on_hand, dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name,
dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
dbo.tblShipping_sched.work_ord_num = dbo.tblBag_data.work_ord_num
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_numsql server doesn't support IIF, you have to use CASE

incorrect --

IIf(dbo.tblBag_data.bag_quantity IS NULL, 0, dbo.tblBag_data.bag_quantity)

correct --

case when dbo.tblBag_data.bag_quantity IS NULL then 0 else dbo.tblBag_data.bag_quantity end

RDA Select statement...

I have attempted to perform the pull operation by using a select statement that references more than one table.... Unfortunately I get an error saying I can't do this....?

I guess I don't understand....I'm pulling full rows from only one table in the statement....I'm only using a join and where clauses to partition the number of rows that are returned. It would seem to me that RDA shouldn't care how I'm requesting the rows...just that I'm asking for entire rows from one table...?

So my select would be something like....

Select Table1.col1, Table1.col2, ...Table1.colx From Table1 Left Outer Join Table2 On Table1.col1=Table2.col3 WHERE Table1.Colx=username

I guess this type of thing can't work with RDA? If it can....how do I go about it?

Thanks

I would be inclined to try a stored procedure or view in SQL Server

For the view this would be:

SELECT col1, col2, col3 from myview where col1=myval1

Brian|||I tried the view...but still no luck with tracking on.