Reading a long string

Hi there,
How can i see what a long string value is? I have a stored procedure which
creates a dynamic xml string and when i select it at the end of the SP the
result is about 100 chars long although i know it is about 7000 long and I
need to inspect the whole string, put it in an xml reader interface etc to
play around with. Even if i send the result to file, it is still truncated.
How can I read the whole string?
thanksWhat version of SQL are you using? XML data needs an XML client.
In SQL 2005 there's a built-in reader, that can display the XML in humanly
readable form, but for SQL 2000 you'd need a client application - look into
using SQLXML.
ML
http://milambda.blogspot.com/|||"louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> Hi there,
> How can i see what a long string value is? I have a stored procedure which
> creates a dynamic xml string and when i select it at the end of the SP the
> result is about 100 chars long although i know it is about 7000 long and I
> need to inspect the whole string, put it in an xml reader interface etc to
> play around with. Even if i send the result to file, it is still
> truncated.
> How can I read the whole string?
> thanks
If this is SQL 2k and you are using Query Analyser, ensure that you have
set your results length long enough.
Look at Tools->Options Choose Results tab, then put 8000 in the box for
Maximum Characters Per Column
Rick Sawtell
MCT, MCSD, MCDBA|||THANKS!!! i didnt realise you could set this option.
In response to ML - I am creating my own custom xml string. I wont go into
reasons but in a nutshell it is completely dynamic, not as simple as return
this resultset as xml. I then use that as an xml datasource in a .net page.
Regards
"Rick Sawtell" wrote:

> "louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
> message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> If this is SQL 2k and you are using Query Analyser, ensure that you have
> set your results length long enough.
> Look at Tools->Options Choose Results tab, then put 8000 in the box for
> Maximum Characters Per Column
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
>> Reads is generally a good thing to focus on, but it really depends on what
>> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
>> I/O-bound, focus on reads.
> My indicators for the performance monitor are showing huge lock counts, and
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess when
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read source data without waiting for possible locks

hi!

I wonder if anyone can tell me how we can run select queries in an OLE DB data flow task and tell the target SQL 2000 server it should allow reads at all time. Currently when a lock is on the source table our SSIS package will sit and wait untill the lock on the source table is gone.

Thanks.

Marc

Try issueing the READUNCOMMITTED or NOLOCK switch.

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

|||

Hi,

When I simulate a lock on a table with:

begin transaction
update table set column = 'test' where columnkey = 1

and I execute the package reading from this table it will wait untill I commit the transaction. If I change the select statement in the package in select ....... with (nolock) and try running it again it still keeps waiting. However, when I execute the same SQL statement in management studio I will get the results.

Can you explain this?

Thanks

|||

Use NOLOCK on your SSIS query.

Management Studio may be issuing that behind the scenes. (You're deep into Transact-SQL territory here, and there is a better forum for this discussion.)

Read only user and not seeing Tables, views etc

I would like to be able to setup a user that can only select information
from a View.
But when I enable security for this user, the user can see all the
tables, Views, Store Proc etc. I know it is only the names, but I don't
want the user to be able to do this, as he is only allowed to view the
view I have enabled for him.
The user can log in using Enterprise Manager and flick through the
tables, views etc, but can't read them, but he can get the names which
is what I don't want to happen.
Is there anyway of stopping the user from seeing all the other
information?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
In Enterprise manager you cant control that.
Thanks
Hari
MCDBA
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:ObOG040BEHA.892@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to setup a user that can only select information
> from a View.
> But when I enable security for this user, the user can see all the
> tables, Views, Store Proc etc. I know it is only the names, but I don't
> want the user to be able to do this, as he is only allowed to view the
> view I have enabled for him.
> The user can log in using Enterprise Manager and flick through the
> tables, views etc, but can't read them, but he can get the names which
> is what I don't want to happen.
> Is there anyway of stopping the user from seeing all the other
> information?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Read one field in every row during SELECT

Hello

I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.

We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.

ORIGINAL TABLE

USERID | TIMEOFACCESS | TYPE OF ACCESS

2323 | 12/15/2007 03:52:54 | CLOCKIN

2323 | 12/15/2007 04:32:54 | CLOCKOUT

2323 | 12/15/2007 05:42:54 | CLOCKIN

2323 | 12/15/2007 07:53:54 | CLOCKOUT

2323 | 12/15/2007 09:18:54 | CLOCKIN

2323 | 12/15/2007 10:24:54 | CLOCKOUT

TEMPORARY TABLE

USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME

2323 | 03:52:54 | 04:32:54 | 0:40:54

OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.

I create my temporary table

CREATE #TempTable

(

UserID int,

ClockIN DateTime null,

ClockOUT DateTime null,

ElapsedTime DateTime null,

)

I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.

Does anyone know how to read in a SELECT statement one field as it's been read?

you can do that using a cursor in your stored procedure.

See: http://www.sqlteam.com/item.asp?ItemID=553

If you dont want to use a cursor, its possible as well:

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

hi,

you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)

you just have to drop the timepart of the date on the join. you dont even need the temp table.

here's the pseudocode: you just need to imporve this.

select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1

where [type of access]='clockin'

join

(select userid, convert( varchar(20),dateaccess,102) as dateaccess,

convert( varchar(20),dateaccess,108) as clockout, from table1

where [type of access]='clockout')

as t2

on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess

regards,

joey

|||

Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.

Code Snippet

;with cte

as

(

select

userid,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout,

datediff(

seconds,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout

) as elapsed_time_sec

from

dbo.t1

group by

userid

)

select

userid,

clockin,

clockout,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte;

AMB

|||

None of this solutions actually work.

I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.

|||

Try:

Code Snippet

;with cte_1

as

(

select

userid,

[TIME OF ACCESS],

[TYPE OF ACCESS],

row_number() over(partition by userid order by [TIME OF ACCESS]) as rn

from

dbo.t1

),

cte_2

as

(

select

a.userid,

a.[TIME OF ACCESS] as CLOCKIN,

b.[TIME OF ACCESS] as CLOCKOUT,

datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec

from

cte_1 as a

inner join

cte_1 as b

on a.userid = b.userid

and a.rn = b.rn - 1

and a.[TYPE OF ACCESS] = 'CLOCKIN'

and b.[TYPE OF ACCESS] = 'CLOCKOUT'

)

select

userid,

CLOCKIN,

CLOCKOUT,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte_2

order by

userid,

CLOCKIN;

go

BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".

AMB

|||

This is it.

It works perfectly fine.

Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?

Jose

|||

I will suggest the serie "Inside SQL Server 2005".

Inside Microsoft SQL Server 2005: T-SQL Querying

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3

Inside Microsoft SQL Server 2005: T-SQL Programming

http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

AMB

P.S. Your alias exposed you.

PP --> Pepe (Jose)

CUBAN --> from Cuba

Oye chico, vinistes a bailar en casa del trompo.

|||

AMB

P.S. Your alias exposed you.

As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?

Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?

|||

good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.

AMB? Tu eres cubano?

|||

Jose,

Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).

Saludos,

Alejandro Mesa

Read Consistency

What is the mechanism used by select statements to return point in time data?

I have a test setup - table t1 has 1000000 rows. A query (call q1) that selects all the rows (in NOLOCK mode and process them) takes 10 minutes. At the same time another process inserts another 1000000 rows into the same table t1. As expected, client that issued query q1 sees just 1000000 rows.

My understanding is that NOLOCK does not hold any locks. So, how did SQL Server know that it should not return the rows that are inserted after I issued the query q1? Some explanation or link to some whitepapers would be helpful.

Thanks

Unless you use the row-level versioning that was introduced in SQL Server 2005, there is no notion of 'point-in-time' data for the locking-based database engines for a certain fixed time in the past, until the transaction commits and only for the precise subset of the data read or written by the transaction and of the data that was intended to be read but it didn't exist when the transaction tried to access it.

Instead, the engine can provide an illusion of a serialized execution as if during the processing of transactions the view of the data accessed and touched by the transaction was 'frozen', i.e. the things that other transactions did, either occurred in the past, will occur in the future, or they don't matter if other transaction never read or wrote the data accessed and touched by our transaction. As a result the data that is seen by a single transaction ultimately can be viewed as a consistent slice of some relevant subset of entire database as of 'now' while the transaction is active and as of commit time after the transaction commits.

Having said that I realize that it might sound really cryptic and confusing - but this is how the things are done in the locking-based transaction processing systems. If you come from the Oracle world it will take some time to adjust to a different paradigm.

|||

Thanks Tengiz but ...

In my scenario, let us say that query q1 started at 10 AM and finished at 10:10 AM. At 10 AM it had 1000000 rows and by 10:05 AM other transactions inserted 1000000 rows more making it a total of 2000000 rows. Why did not SQL server return 2000000 rows to q1 client? Somehow SQL server knew to provide the rows that existed at 10 AM (that I call point in time data; may be wrong terminology?) and ignore the rows that were added after that point. What is the internal mechanism used by the engine to give that illusion? I am inclined to think that even though it does not create locks it might create some sort of semaphores, latches or tables in memory to keep track of the rows that need to be returned to q1 client. As you guessed, I am from Oracle background; may be you answered my question and it might take little longer to get it.

|||

Could you please be more specific describing you scenario? The fact that the query only returned the initial set of rows and didn't see the rows that were inserted after the query started does not really mean that the server somehow knows or takes into account the time when the rows were inserted.

Again, the things are different if you use the row-level versioning - you either do it be switching to the snapshot isolation (after enabling it for the database) or if you allow versioning-based read-committed isolation.

But assuming the you don't use the row-level versioning, depending on the existing indexes, the actual query plans, the key values that existed in the table before the insert and the key values inserted, the select query with the right timing could easily skip the newly inserted records, but it would have nothing to do with the read-consistency provided to the row-level versioning.

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

The database is not setup to use row versioning. Also, this is a data warehouse system. So, DML statements can come only from ETL. No other concurrent user is touching this table while this program is running. However my SSIS program that maintains this table opens two sessions (a reader and a writer) as I explained in step 4. My concern is that these two sessions stepping on each other.

-

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

-

Step 1:

CREATE TABLE Table1 (

Column1[int] NOT NULL,

Column2[int] NOT NULL,

Column3[bit] NOT NULL,

Column4[datetime] NOT NULL,

Column5[int] NOT NULL,

Column6[int] NOT NULL,

Column7[varchar](255) NULL,

Column8[int] NULL,

Column9[int] NULL,

Column10[int] NULL

CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (

[Column1] ASC,

[Column2] ASC,

[Column4] ASC,

[Column5] ASC,

[Column6] ASC )

)

-

Step 2:

INSERT INTO Table1

SELECT *

FROM Source_Table1

10,556,214 rows inserted.

-

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads

delete

from SourceStage.RealDB_StatusHistory

where Column1 % 2 = 0

5,288,119 rows deleted

-

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted

5,268,095 rows updated

-

I repeated the above steps without the clustered index and I get the same row counts. The row counts show no surprises which leads me to believe that there is some kind of lock. I have to make sure this works 100% before I put this code into production. The documentation leads me to believe that it does not work 100% of the time. If that is the case, I should be able to simulate a scenario where I get whacky row counts.

Here is what I think the reason for not getting whacky row counts in my setup. (a) when the clustered index is in place, it rebalances the tree during the delete operation. So the inserted rows go into new pages and SQL Server somehow knows to ignore them. (b) when there is no clustered index, then it sorts the data in Temp database before it is being fed to the SSIS. So data is essentially is sourced from the Temp database during pipeline operation of SSIS. Am I thinking in the right direction?

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

A quick answer to your question "can I use NOLOCK without any bad effects in this scenario?" is NO. The NOLOCK hint relaxes certain concurrency-related guarantees in the engine and essentially nullifies the notion of transactional consistency. It doesn’t mean that you will never get any consistency if you use NOLOCK, but you will not in general have predictable results.

I'm not an SSIS expert, so I'm not sure how SSIS really performs the 'insert else update' operation and I still don't quite get what you actually do in this scenario. But from the description of it looks like the plan does include spooling in tempdb for sort - the fast load option normally feeds data in through the BCP API which if the destination table is a clustered index assumes that that data needs to be sorted before it gets delivered to the destination. Hence, if the input provided by the SSIS is not sorted (there is a special hint that SSIS can specify in order to avoid extra sort) the then query optimizer adds the sort operator.

Spooling can certainly make it look like there indeed was some kind of 'read consistency' provided, but, again, depending on the actual query and specific conditions the optimizer is free to choose other options as well.

|||Thanks for the reply. Thinking about it further, when I don't use the NOLOCK hint, my SSIS package just waits forever. When I use NOLOCK hint, it seems to work fine. However, what if SQL Server does not honour the NOLOCK hint? My package might wait forever. So, I decided use the Lookup Transformation of SSIS rather than the Merge Join Transformation. Lookup Transformation can cache the data upfront before the Data Flow Task starts prosessing source rows. This way there is no contention.

Read Consistency

What is the mechanism used by select statements to return point in time data?

I have a test setup - table t1 has 1000000 rows. A query (call q1) that selects all the rows (in NOLOCK mode and process them) takes 10 minutes. At the same time another process inserts another 1000000 rows into the same table t1. As expected, client that issued query q1 sees just 1000000 rows.

My understanding is that NOLOCK does not hold any locks. So, how did SQL Server know that it should not return the rows that are inserted after I issued the query q1? Some explanation or link to some whitepapers would be helpful.

Thanks

Unless you use the row-level versioning that was introduced in SQL Server 2005, there is no notion of 'point-in-time' data for the locking-based database engines for a certain fixed time in the past, until the transaction commits and only for the precise subset of the data read or written by the transaction and of the data that was intended to be read but it didn't exist when the transaction tried to access it.

Instead, the engine can provide an illusion of a serialized execution as if during the processing of transactions the view of the data accessed and touched by the transaction was 'frozen', i.e. the things that other transactions did, either occurred in the past, will occur in the future, or they don't matter if other transaction never read or wrote the data accessed and touched by our transaction. As a result the data that is seen by a single transaction ultimately can be viewed as a consistent slice of some relevant subset of entire database as of 'now' while the transaction is active and as of commit time after the transaction commits.

Having said that I realize that it might sound really cryptic and confusing - but this is how the things are done in the locking-based transaction processing systems. If you come from the Oracle world it will take some time to adjust to a different paradigm.

|||

Thanks Tengiz but ...

In my scenario, let us say that query q1 started at 10 AM and finished at 10:10 AM. At 10 AM it had 1000000 rows and by 10:05 AM other transactions inserted 1000000 rows more making it a total of 2000000 rows. Why did not SQL server return 2000000 rows to q1 client? Somehow SQL server knew to provide the rows that existed at 10 AM (that I call point in time data; may be wrong terminology?) and ignore the rows that were added after that point. What is the internal mechanism used by the engine to give that illusion? I am inclined to think that even though it does not create locks it might create some sort of semaphores, latches or tables in memory to keep track of the rows that need to be returned to q1 client. As you guessed, I am from Oracle background; may be you answered my question and it might take little longer to get it.

|||

Could you please be more specific describing you scenario? The fact that the query only returned the initial set of rows and didn't see the rows that were inserted after the query started does not really mean that the server somehow knows or takes into account the time when the rows were inserted.

Again, the things are different if you use the row-level versioning - you either do it be switching to the snapshot isolation (after enabling it for the database) or if you allow versioning-based read-committed isolation.

But assuming the you don't use the row-level versioning, depending on the existing indexes, the actual query plans, the key values that existed in the table before the insert and the key values inserted, the select query with the right timing could easily skip the newly inserted records, but it would have nothing to do with the read-consistency provided to the row-level versioning.

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

The database is not setup to use row versioning. Also, this is a data warehouse system. So, DML statements can come only from ETL. No other concurrent user is touching this table while this program is running. However my SSIS program that maintains this table opens two sessions (a reader and a writer) as I explained in step 4. My concern is that these two sessions stepping on each other.

-

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

-

Step 1:

CREATE TABLE Table1 (

Column1[int] NOT NULL,

Column2[int] NOT NULL,

Column3[bit] NOT NULL,

Column4[datetime] NOT NULL,

Column5[int] NOT NULL,

Column6 [int] NOT NULL,

Column7[varchar](255) NULL,

Column8[int] NULL,

Column9[int] NULL,

Column10[int] NULL

CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (

[Column1] ASC,

[Column2] ASC,

[Column4] ASC,

[Column5] ASC,

[Column6] ASC )

)

-

Step 2:

INSERT INTO Table1

SELECT *

FROM Source_Table1

10,556,214 rows inserted.

-

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads

delete

from SourceStage.RealDB_StatusHistory

where Column1 % 2 = 0

5,288,119 rows deleted

-

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted

5,268,095 rows updated

-

I repeated the above steps without the clustered index and I get the same row counts. The row counts show no surprises which leads me to believe that there is some kind of lock. I have to make sure this works 100% before I put this code into production. The documentation leads me to believe that it does not work 100% of the time. If that is the case, I should be able to simulate a scenario where I get whacky row counts.

Here is what I think the reason for not getting whacky row counts in my setup. (a) when the clustered index is in place, it rebalances the tree during the delete operation. So the inserted rows go into new pages and SQL Server somehow knows to ignore them. (b) when there is no clustered index, then it sorts the data in Temp database before it is being fed to the SSIS. So data is essentially is sourced from the Temp database during pipeline operation of SSIS. Am I thinking in the right direction?

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

A quick answer to your question "can I use NOLOCK without any bad effects in this scenario?" is NO. The NOLOCK hint relaxes certain concurrency-related guarantees in the engine and essentially nullifies the notion of transactional consistency. It doesn’t mean that you will never get any consistency if you use NOLOCK, but you will not in general have predictable results.

I'm not an SSIS expert, so I'm not sure how SSIS really performs the 'insert else update' operation and I still don't quite get what you actually do in this scenario. But from the description of it looks like the plan does include spooling in tempdb for sort - the fast load option normally feeds data in through the BCP API which if the destination table is a clustered index assumes that that data needs to be sorted before it gets delivered to the destination. Hence, if the input provided by the SSIS is not sorted (there is a special hint that SSIS can specify in order to avoid extra sort) the then query optimizer adds the sort operator.

Spooling can certainly make it look like there indeed was some kind of 'read consistency' provided, but, again, depending on the actual query and specific conditions the optimizer is free to choose other options as well.

|||Thanks for the reply. Thinking about it further, when I don't use the NOLOCK hint, my SSIS package just waits forever. When I use NOLOCK hint, it seems to work fine. However, what if SQL Server does not honour the NOLOCK hint? My package might wait forever. So, I decided use the Lookup Transformation of SSIS rather than the Merge Join Transformation. Lookup Transformation can cache the data upfront before the Data Flow Task starts prosessing source rows. This way there is no contention.sql

rdlc show querystring parameter in page header

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

JIM_LANGDON wrote:

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

Are you using a report generator?

If not, then isn't this is as simple as setting the expression of the textbox to:

=Fields!FiscalYear.Value

|||

I don't know what you mean by "Report Generator"..isn't it SSRS?

I pass in the parameter via the call to the webform that contains the

reportviewer control -- webform.aspx?fy=2007.

The reportviewer control that is bound to the report.rdlc.

The RDLC has several datasources which show up as objectdatasource controls

on the web form. The objectdatasource controls are configured to use the appropriate

datasource method and the parameters are defined to use the querystring and "fy" as

the parameter name.

Doesn't the Fields! collection just have the datasource columns? I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

|||

JIM_LANGDON wrote:

I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

This is probably because you need to specify the dataset on the textbox. Right click the textbox and select properties. In the "containing group or dataset" textbox, you need to either select or enter the dataset that contains 'fy'.

Basically, you can treat a textbox similarly to a table/matrix. It can have a dataset associated with it and you can populate the textbox with any value from the parameters or fields list.

|||

But the querystring does not belong to a "dataset"....so I can't reference it with Fields!fieldname. Hmmm.

|||

Isn't fiscal year one of your fields in a dataset? Aren't you trying to get fiscal year in a textbox?

Why do you want to touch a querystring?

|||

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

The goal is to display a specific querystring parameter in the page header of the report.

|||

JIM_LANGDON wrote:

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned

Yes, and you could conditionally specify what the Fiscal Year should default to if no records are returned.

If you'd like to pursue the querystring path to resolve this, feel free. I'm just somewhat confused with your reasoning.

|||

The Fiscal Year that is passed to the web form is 2006. If no Fiscal Year is passed, it will default to 2007. The records selected are based on the year that is passed in.

If I pass in the year 2006, I want the page header to display "Fiscal Year: 2006" and the body to display "No records found."

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

|||

JIM_LANGDON wrote:

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

Maybe I just don't know what you mean by "querystring".

I think everything that you have mentioned so far can be accomplished by using a dataset (as either text or stored procedure).

|||

Sounds like what you have to do is one of the following:

add a new parameter to the report (with no valid values) and set the value of this parameter from the querystring. In your report textbox use Parameters!<your param name>.value to display the value

OR

create a new dataset in your report and load the querystring parameters into it in code, pass this additional dataset to the report

|||

The report page is initiated by a link <a>, html anchor tag.

<a href="report_web_form.aspx?fy=2006">Run This Report</a>

The href property assignment is what I mean by "querystring".

The dataset that is generated uses the 2006 value to determine what

records to select. If no records are returned, you cannot use the dataset to

get the value 2006. I don't see how this can be accomplished using a dataset.

|||

Thanks Adam..

From your first solution how do I "set the value of this parameter from the querystring". Where is that done. Are we talking about an assignment in the page load event of the report web form?

From your second solution how do I "load the querystring parameters into it in code"?

Sorry, I just need a bit more explanation.

|||

OK, here we go. The solution. Thanks to the exhausting yet always productive discussion today.

1. Define a report parameter in the report.rdlc:

Name: FISCAL_YEAR

Data Type: String

"Hidden" checkbox checked.

Default Values: "null" checkbox checked.

2. Place a text box in the page header of the report.rdlc with the value "=parameters!FISCAL_YEAR.Value".

3. Place the following code in the report web form:

Imports Microsoft.Reporting.WebForms

Partical Class reportWebForm

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim _fiscalYearParameter As New ReportParameter("FISCAL_YEAR", "2007")

_fiscalYearParameter.Values(0) = Request.QueryString("fy")

ReportViewer.LocalReport.SetParameters(New ReportParameter() {_fiscalYearParameter})

End Sub

RDL file not maintaining Extended File Properties

Please try this if you want...
1 For a .RDL file, right click on the file its self and select
"properties"
2 Click the "Summary" tab and enter information in any of the text
fields (Subject, author, ...)
3 Save it and then copy the file on another computer, is your metadata
missing just like mine?
It seems to be saving it somehow locally, but does not transfer with
the file. What gives?
We were originally trying to add some of the install info in here, but
seems to only be local.
Weird thing is... if you look at the same file over the network from
another computer, the information looks to be there, it looses it when
copied.oh well. I just found out why. This data is actually stored in NTFS
and is not in the file. Any time the file is copied into source safe
or email all properties are lost.

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.

Rates of Change...

Hi,

Is there an easy way to determine the rate of change for table? I realize
that I could do periodic select count(*) from XXX, but that would only
determine the size of the table. It would ignore updates.

What I'd really like to do would be able to determine the number of non-read
operations over a table so that I can determine the rate of change for that
table.

Thanks for any help..You could also create INSERT and UPDATE triggers on the table in question
that would INSERT a record into a separate table. Everytime a record is
inserted and updated a new record would be inserted into a history table.
This may create performance issues which Erland's solutions do not.

CJ
"Madison Pruet" <mpruet@.attbi.com> wrote in message
news:wZkLa.42831$R73.7095@.sccrnsc04...
> Hi,
> Is there an easy way to determine the rate of change for table? I realize
> that I could do periodic select count(*) from XXX, but that would only
> determine the size of the table. It would ignore updates.
> What I'd really like to do would be able to determine the number of
non-read
> operations over a table so that I can determine the rate of change for
that
> table.
> Thanks for any help..