Reading a record without placing a lock

Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.

> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row Xwhile Xyou are reading.You
> are about to get an inconsistent data. For example
> Xyou have tree pages with data Xlike a) 10,40,60 b) 80,100,90 Xc)110,70,85 ,
> so while Xyou read page A Xanother connection inserst the value let mesay
> 50, but you have already read data Xfrom the page A , so it moves the all
> data to a new created page so now that data looks like Xa)10,40 ,50,
> b) 80,100,90 Xc)110,70,85 ,d)60 ... and Xas you keep reading Xyou get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint Xor if you use SQL Server 2005 Xtake a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) ?
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
>
>

read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) '> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
>
>

Read, modify table (locking) question

I am quite new to MS SQL, and I want to read rows from a todo table, and
when a row has been processed, I want to delete that row.

Ages ago in MySQL I would probably have locked the table, select, process
a row, delete a row, unlock the table.

I have been reading through the documentation from MS SQL, but it's not
clear what exactly I should do.

Since I want to lock only one table in the select (the others just provide
data, and are not modified), what's a good solution?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.htmlJohn Bokma <john@.castleamber.com> wrote:

> I am quite new to MS SQL, and I want to read rows from a todo table,
> and when a row has been processed, I want to delete that row.
> Ages ago in MySQL I would probably have locked the table, select,
> process a row, delete a row, unlock the table.
> I have been reading through the documentation from MS SQL, but it's
> not clear what exactly I should do.
> Since I want to lock only one table in the select (the others just
> provide data, and are not modified), what's a good solution?

what I came up with:

BEGIN TRANSACTION
SELECT TOP 10 .... FROM A WITH(ROWLOCK,HOLDLOCK), B, C WHERE ...
....
... delete each row in A in TOP 10
END TRANSACTION

what I want to prevent is that more then one process selects 10 rows, and
starts to delete rows (from A) that are selected by any of the other ones.

to me, a rowlock is sufficient, and fine grained enough, and the hold lock
holds it to the end of transaction.

Am I right?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html|||John Bokma (john@.castleamber.com) writes:
> John Bokma <john@.castleamber.com> wrote:
>> I am quite new to MS SQL, and I want to read rows from a todo table,
>> and when a row has been processed, I want to delete that row.
>>
>> Ages ago in MySQL I would probably have locked the table, select,
>> process a row, delete a row, unlock the table.
>>
>> I have been reading through the documentation from MS SQL, but it's
>> not clear what exactly I should do.
>>
>> Since I want to lock only one table in the select (the others just
>> provide data, and are not modified), what's a good solution?
> what I came up with:
> BEGIN TRANSACTION
> SELECT TOP 10 .... FROM A WITH(ROWLOCK,HOLDLOCK), B, C WHERE ...
> ...
> ... delete each row in A in TOP 10
> END TRANSACTION
> what I want to prevent is that more then one process selects 10 rows, and
> starts to delete rows (from A) that are selected by any of the other ones.
> to me, a rowlock is sufficient, and fine grained enough, and the hold lock
> holds it to the end of transaction.
> Am I right?

Difficult to say with the small amount of information, but it does not
seem quite right.

The smallest change you should do is to is to remove ROWLOCK, but insert
UPDLOCK instead. ROWLOCK is sort of meaningless. Either you have a good
index to locate the rows, and you will get rowlocks. Or you don't have
good indexes, and SQL Server will have to lock the entire table.

UPDLOCK is a shared lock that does not block other readers, but it
blocks others that try to use UPDLOCK. With only HOLDLOCK and you have
two processes coming to the place at the same time, will lock the same
10 ten rows, and then when they to delete, they will block each other.

But there may be other things you could consider. It could be the case
that application locks are a better choice. An application lock is a
lock on a user-defined resource (that is a text string) which is handled
by the Lock Manager. But I know too little about your application to
tell whether it would fit it here.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Read the transaction log (ldf) file. My first post ever :)

Hi!

The purpose is to se history of which user updated, inserted or deleted a row in the database.

Can't seem to find any publications from microsoft on how to parse and interpret the log file.

Any documentation on how the log file is structured?
Is it possible to obtain this information through the system views

I don't want to use triggers.

Thanks

Consider the use of a third-party application for this job. That is money that's well spent.

|||

Thanks for the reply.

I know there exist som applications that can read log files, like Lumigent Log Explorer.

But it would be nice with an freeware version

|||

If you really want to learn, here the docuemnt on basic level.. I found few months back very unexpectedly... Nice one....

But this document is not sufficient to start programing with Tx Log files.. As Frank says you can use the 3rd party tools.. there are lot of products available on the market...

https://www.blackhat.com/presentations/bh-usa-07/Fowler/Presentation/bh-usa-07-fowler.pdf

|||

J-A.G. wrote:

I know there exist som applications that can read log files, like Lumigent Log Explorer.

But it would be nice with an freeware version

TANSTAAFL

But in order to learn you can always use something like the undocumented

DBCC LOG (Northwind, -1)

|||

good frank!

This undocumented command works on sql2000?

|||

Yes it does. But as with all undocumented features the use is at your own risk.

|||Tks!

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 locks

What is the proper way of locking a row for read? Here's what I'm trying to
do:
1. Transaction 1 finds a row it likes. It locks it from being read by
Transaction 2
2. Transaction 2 looks for a row, but does not get blocked and does not find
the row that is being processed by Transaction 1. It simply picks another
suitable row.
I imagine it involves the READPAST hint, but what else should I do? Am I
stuck with having a field in the row to mark that a transaction "owns" a
particular row at a given instance? My transaction gets a "select" followed
by an "update"Hi Val
If T1 wants to make sure T2 can't even read the row, T1 will have to make
sure the row has a X lock, using the XLOCK hint and perhaps also the ROWLOCK
hint.
You are correct, that if T2 uses the READPAST hint, it wil skip over the row
that T1 has locked.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Val P" <ValP@.discussions.microsoft.com> wrote in message
news:DCEE6BF0-EE47-48AD-A46E-438AB382D1D5@.microsoft.com...
> What is the proper way of locking a row for read? Here's what I'm trying
> to do:
> 1. Transaction 1 finds a row it likes. It locks it from being read by
> Transaction 2
> 2. Transaction 2 looks for a row, but does not get blocked and does not
> find
> the row that is being processed by Transaction 1. It simply picks another
> suitable row.
> I imagine it involves the READPAST hint, but what else should I do? Am I
> stuck with having a field in the row to mark that a transaction "owns" a
> particular row at a given instance? My transaction gets a "select"
> followed
> by an "update"
>
>|||Thanks!
A question though. Does the XLOCK disallow ROWLOCK? If I read BOL, I see the
following, which seems to apply that XLOCK cannot be combined ROWLOCK, but i
t
doesn't really come out and say it.
"XLOCK Use an exclusive lock that will be held until the end of the
transaction on all data processed by the statement. This lock can be
specified with either PAGLOCK or TABLOCK, in which case the exclusive lock
applies to the appropriate level of granularity. "
"Kalen Delaney" wrote:

> Hi Val
> If T1 wants to make sure T2 can't even read the row, T1 will have to make
> sure the row has a X lock, using the XLOCK hint and perhaps also the ROWLO
CK
> hint.
> You are correct, that if T2 uses the READPAST hint, it wil skip over the r
ow
> that T1 has locked.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Val P" <ValP@.discussions.microsoft.com> wrote in message
> news:DCEE6BF0-EE47-48AD-A46E-438AB382D1D5@.microsoft.com...
>
>|||Val
This is very easy to test for yourself.
use pubs
begin tran
select * from titles (xlock, rowlock)
where title_id= 'ps2091'
exec sp_lock
commit tran
The BOL entry is unclear. ROWLOCK and XLOCK can be specified together.
I think what BOL means is that if you don't specify the granularity, the
default is ROWLOCK. I wasn't sure of that, which is why I said you 'might'
need to add the ROWLOCK hint. You'd need to actually test this on a much
bigger table to be sure that XLOCK by itself always gave you rowlocks.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Val P" <ValP@.discussions.microsoft.com> wrote in message
news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6@.microsoft.com...
> Thanks!
> A question though. Does the XLOCK disallow ROWLOCK? If I read BOL, I see
> the
> following, which seems to apply that XLOCK cannot be combined ROWLOCK, but
> it
> doesn't really come out and say it.
> "XLOCK Use an exclusive lock that will be held until the end of the
> transaction on all data processed by the statement. This lock can be
> specified with either PAGLOCK or TABLOCK, in which case the exclusive lock
> applies to the appropriate level of granularity. "
>
> "Kalen Delaney" wrote:
>|||It looks to me like it got promoted to page locks on a larger table... (I am
assuming I'm reading this correctly)
71 5 1117247035 1 PAG 1:249 IX GRANT
71 5 1117247035 1 PAG 1:248 IX GRANT
71 5 1117247035 1 PAG 1:324 IX GRANT
...
in which case this solution is not suitable for me, since it's locking too
many potential candidates. :( If this is so, is there a pattern for
implementing queue tables that I should read up on? I imagine this is a
common task.
"Kalen Delaney" wrote:

> Val
> This is very easy to test for yourself.
> --
> use pubs
> begin tran
> select * from titles (xlock, rowlock)
> where title_id= 'ps2091'
> exec sp_lock
> commit tran
> --
>
> The BOL entry is unclear. ROWLOCK and XLOCK can be specified together.
> I think what BOL means is that if you don't specify the granularity, the
> default is ROWLOCK. I wasn't sure of that, which is why I said you 'might'
> need to add the ROWLOCK hint. You'd need to actually test this on a much
> bigger table to be sure that XLOCK by itself always gave you rowlocks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Val P" <ValP@.discussions.microsoft.com> wrote in message
> news:6575CA85-8106-416D-9DAA-4D0A2C8DE3E6@.microsoft.com...
>
>|||IX is an intent lock, which is always acquired on the larger units. If there
is an X lock on a row, the page and table will always have IX locks to prev
ent anyone from locking at the larger unit. You shouldn't ever have IX locks
without X locks. Where are your X locks? What hints did you use?
When I ran the code below on the little tiny titles table, I got:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 5 0 0 DB S GRANT
51 5 2121058592 1 PAG 1:99 IX GRANT
51 5 2121058592 1 KEY (ba008abb131c) X GRANT
51 1 85575343 0 TAB IS GRANT
51 5 2121058592 0 TAB IX GRANT
This shows an X lock on the row, and IX locks on the page and table containi
ng the row.
IX locks do not prevent other IX locks. Please read "Understanding Locking i
n SQL Server" in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Val P" <ValP@.discussions.microsoft.com> wrote in message news:6C5CDDF1-4CCC-43B3-951B-61B7
41B24E53@.microsoft.com...
> It looks to me like it got promoted to page locks on a larger table... (I
am
> assuming I'm reading this correctly)
>
> 71 5 1117247035 1 PAG 1:249 IX GRANT
> 71 5 1117247035 1 PAG 1:248 IX GRANT
> 71 5 1117247035 1 PAG 1:324 IX GRANT
> ...
>
>
> in which case this solution is not suitable for me, since it's locking too
> many potential candidates. :( If this is so, is there a pattern for
> implementing queue tables that I should read up on? I imagine this is a
> common task.
>
>
>
>
> "Kalen Delaney" wrote:
>|||Be very careful using the XLOCK. You can end up with it not doing its job. T
ry below (two
connections):
--Connection 1
BEGIN TRAN
SELECT au_lname FROM authors WITH(XLOCK)
WHERE au_lname = 'White'
--Connection 2
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'W%'
See? Connection 2 is *not* blocked even though c1 has an exclusive lock and
c2 doesn't do a dirty
read. This is an optimization in SQL Server 2000 where a scan take a look to
see if the contents is
older than the oldest open transaction (applies to READ COMMITTED only). See
KB 324417 for more
info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Val P" <ValP@.discussions.microsoft.com> wrote in message
news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53@.microsoft.com...
> It looks to me like it got promoted to page locks on a larger table... (I
am
> assuming I'm reading this correctly)
> 71 5 1117247035 1 PAG 1:249 IX GRANT
> 71 5 1117247035 1 PAG 1:248 IX GRANT
> 71 5 1117247035 1 PAG 1:324 IX GRANT
> ...
>
> in which case this solution is not suitable for me, since it's locking too
> many potential candidates. :( If this is so, is there a pattern for
> implementing queue tables that I should read up on? I imagine this is a
> common task.
>
>
> "Kalen Delaney" wrote:
>|||Kalen,
the query included xlock and rowlock on a table that had only about 12000
rows, and the ResourceID in the collection below is not an indexed key.
Readpast doesn't make any difference to the result below
begin tran
select * from AuditLog (xlock, rowlock) where ResourceID= 'xxx'
exec sp_lock
commit tran
I am thinking that I just need to reengineer this process in another way,
since this approach does not seem to be working. But I can't think of a
foolproof way of doing this that does not involve retries in some rare cases
.
In the real-world using the current method I'm running out of results even
though many are available but they are page-locked by a concurent query...
"Kalen Delaney" wrote:

> IX is an intent lock, which is always acquired on the larger units. If there is an
X lock on a row, the page and table will always have IX locks to prevent anyone fro
m locking at the larger unit. You shouldn't ever have IX locks without X locks. Wher
e a
re your X locks? What hints did you use?
> When I ran the code below on the little tiny titles table, I got:
>
> spid dbid ObjId IndId Type Resource Mode Status
> -- -- -- -- -- -- -- --
> 51 5 0 0 DB S GRANT
> 51 5 2121058592 1 PAG 1:99 IX GRANT
> 51 5 2121058592 1 KEY (ba008abb131c) X GRANT
> 51 1 85575343 0 TAB IS GRANT
> 51 5 2121058592 0 TAB IX GRANT
> This shows an X lock on the row, and IX locks on the page and table contai
ning the row.
> IX locks do not prevent other IX locks. Please read "Understanding Locking
in SQL Server" in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Val P" <ValP@.discussions.microsoft.com> wrote in message news:6C5CDDF1-4C
CC-43B3-951B-61B741B24E53@.microsoft.com...|||Well your problem is that you need an index on ResourceID. Without the
index sql server must scan the table and you are seeing this behavior. If
you have a proper index you should be able to use:
begin tran
select TOP 1 * from AuditLog (xlock, HOLDLOCK, READPAST) where ResourceID=
'xxx'
commit tran
Andrew J. Kelly SQL MVP
"Val P" <ValP@.discussions.microsoft.com> wrote in message
news:385F426F-BE2D-489E-859C-83666C52A605@.microsoft.com...
> Kalen,
> the query included xlock and rowlock on a table that had only about 12000
> rows, and the ResourceID in the collection below is not an indexed key.
> Readpast doesn't make any difference to the result below
> begin tran
> select * from AuditLog (xlock, rowlock) where ResourceID= 'xxx'
> exec sp_lock
> commit tran
>
> I am thinking that I just need to reengineer this process in another way,
> since this approach does not seem to be working. But I can't think of a
> foolproof way of doing this that does not involve retries in some rare
> cases.
> In the real-world using the current method I'm running out of results even
> though many are available but they are page-locked by a concurent query...
>
> "Kalen Delaney" wrote:
>|||Tibor,
I checked out the kb article, and if I'm reading it correctly, the problem
appears with two queries at different transaction isolation levels
(serializable vs. read-committed, with the second one optimizing away the
locks). In my case, concurent connections have the same isolation level, so
unless I'm missing something in the article, it should work.
Thanks for the input though, it's a good thing to keep in mind.
"Tibor Karaszi" wrote:

> Be very careful using the XLOCK. You can end up with it not doing its job.
Try below (two
> connections):
> --Connection 1
> BEGIN TRAN
> SELECT au_lname FROM authors WITH(XLOCK)
> WHERE au_lname = 'White'
>
> --Connection 2
> SELECT au_lname
> FROM authors
> WHERE au_lname LIKE 'W%'
> See? Connection 2 is *not* blocked even though c1 has an exclusive lock an
d c2 doesn't do a dirty
> read. This is an optimization in SQL Server 2000 where a scan take a look
to see if the contents is
> older than the oldest open transaction (applies to READ COMMITTED only). S
ee KB 324417 for more
> info.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Val P" <ValP@.discussions.microsoft.com> wrote in message
> news:6C5CDDF1-4CCC-43B3-951B-61B741B24E53@.microsoft.com...
>
>

Read CSV file - Save Columns into rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Amar:

I think for what you are describing I would use SSIS; if you are running SQL Server 2000, that would be DTS instead of SSIS. Other alternatives include the use of OPENROWSET, BULK INSERT, or BCP. Read about these alternatives in books online and choose the alternative that you think best fits.

Dave

|||

Hi Amar,

refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx also.

Hemantgiri S. Goswami

sql

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on
Customer count(2nd record in each row of CSV file) and save to SQL
table
--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
Total_3..can go upto 350
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
Brown',17.00,'Rick Davis',18.00
Data in SQL table from csv file should look like this
State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00
I have multiple CSV files with millions of records.What is the best and
fastest way to achieve this?
Throw me anything
Hi
"pintoo" wrote:

> I want to import CSV file and convert columns into rows depending on
> Customer count(2nd record in each row of CSV file) and save to SQL
> table
> --CSV file format
> State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
> Total_3..can go upto 350
> GA,2,'John Doe',14.00,'Roger Smith',15.00
> FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
> SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
> Brown',17.00,'Rick Davis',18.00
> Data in SQL table from csv file should look like this
> State,Name,Total
> GA,John Doe,14.00
> GA,Roger Smith,15.00
> FL,John Doe,14.00,
> FL,Roger Smith,15.00
> FL,Sally Cox,16.00
>
If you are moving these into rows the data is not normalized! You will only
be able to have a finite number of rows that can be converted, therefore you
could self join if each row is allocated a sequence number or the methid
described by Erland in
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/250f0c68596ce22e?&hl=en

> I have multiple CSV files with millions of records.What is the best and
> fastest way to achieve this?
> Throw me anything
>
If you are using SQL 2005 you could use the PIVOT command.
John

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Perhaps you should look at creating a VB/C# application to handle this situation.|||

I am trying do something very similar to what you are asking...I found import wizard by right clicking on the database itself. Under the "Tasks" option. I just got this administrators handbook which is somewhat helpful. First, you will have to import so it seems the column names first there is a check box on the screen you need to select to get the column names to appear this helps out with the data import too. Then go back from the top and do the same thing to get the data. You need to arrive at the copy or query screen to handle this. And choose the option to write a query. For, me however when I go back for the second pass through. It doesn't seem to let me get to the screen again, I saw it on the initial pass. Let me know if this worked for you?

|||

Once you have imported the rows to a temp. table then you might need to run a self-join to extract the required columns like:

insert into TableB(column1,column2,column3)
select A.column1 , B.column1, C.column1
from
(select column1 from TableA where column1 like 'A%') A
cross join (select column1 from TableA where column1 like 'B%') B
cross join (select column1 from TableA where column1 like 'C%') C
order by A.column1 , B.column1, C.column1

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Perhaps you should look at creating a VB/C# application to handle this situation.|||

I am trying do something very similar to what you are asking...I found import wizard by right clicking on the database itself. Under the "Tasks" option. I just got this administrators handbook which is somewhat helpful. First, you will have to import so it seems the column names first there is a check box on the screen you need to select to get the column names to appear this helps out with the data import too. Then go back from the top and do the same thing to get the data. You need to arrive at the copy or query screen to handle this. And choose the option to write a query. For, me however when I go back for the second pass through. It doesn't seem to let me get to the screen again, I saw it on the initial pass. Let me know if this worked for you?

|||

Once you have imported the rows to a temp. table then you might need to run a self-join to extract the required columns like:

insert into TableB(column1,column2,column3)
select A.column1 , B.column1, C.column1
from
(select column1 from TableA where column1 like 'A%') A
cross join (select column1 from TableA where column1 like 'B%') B
cross join (select column1 from TableA where column1 like 'C%') C
order by A.column1 , B.column1, C.column1

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

You can use something like this..

Create Table #DataIntoRows
(
State Varchar(10),
Name Varchar(100),
Total Int
)
SELECT * INTO #Data FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir={PATH}','SELECT * FROM Data.csv')

Declare @.I as Int;
Declare @.Count as Int;
Select
@.I = 1,
@.Count=Count(Colid)
From
tempdb..Syscolumns
Where
id = Object_Id('tempdb..#Data') and Colid > 2;

While @.I < @.Count
begin
Declare @.SQL as Varchar(300);
Select @.SQL = 'Insert Into #DataIntoRows Select State,'
Select @.SQL = @.SQL + name From tempdb..Syscolumns Where id = Object_Id('tempdb..#Data') and colId= @.I + 2
Select @.SQL = @.SQL + ',' + name + ' From #Data Where ' + name + ' Is NOT NULL' From tempdb..Syscolumns Where id = Object_Id('tempdb..#Data') and colId= @.I + 3
Exec(@.SQL)
Select @.I = @.I + 2;
End

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

See if my post in this thread helps get you started. I know it's not quite the same, but it may be a good foundation for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880588&SiteID=1

Phil|||

Thanks Phil for your reply

I am new to SSID can you please provide some more detail

|||You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.|||

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.|||

Phil Brammer wrote:

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

|||

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?|||

Phil Brammer wrote:

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?

No it won't But then the complexity here is in the source file - there's no getting around that. The original poster will just have to accept that he has a ridiculously complex source file and bite the bullet. There are still lots of ways to achieve this.

-Jamie

|||

Can you guys please point me to any examples or blogs.

|||

Amar Khaira wrote:

Can you please point me any examples or blogs

One way is to search the forums for examples. On the main page of this forum is a search box on the left-hand side of the screen. Type in unpivot and you'll get a few examples/topics that discuss that transformation.|||

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

|||

Brian Knight wrote:

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

How can the variable columns not be a problem? Because the flat file source won't account for the variability, you'll have to read in each record as one column, and then split it with a script transformation. In doing that, you might be able to set a fixed number of columns and fill them with NULLs if no data exists. I'm trying to understand how you think you can expose all of the columns to the unpivot transformation when the metadata can't be pre-populated with certainty. (That is, based on my previous example, there's a certain row limit that the flat file connector uses to assess how many columns there are in the file.)|||

I am getting following error while trying Unpivot Transform:

PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

See if my post in this thread helps get you started. I know it's not quite the same, but it may be a good foundation for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880588&SiteID=1

Phil|||

Thanks Phil for your reply

I am new to SSID can you please provide some more detail

|||You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.|||

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.|||

Phil Brammer wrote:

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

|||

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?|||

Phil Brammer wrote:

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?

No it won't But then the complexity here is in the source file - there's no getting around that. The original poster will just have to accept that he has a ridiculously complex source file and bite the bullet. There are still lots of ways to achieve this.

-Jamie

|||

Can you guys please point me to any examples or blogs.

|||

Amar Khaira wrote:

Can you please point me any examples or blogs

One way is to search the forums for examples. On the main page of this forum is a search box on the left-hand side of the screen. Type in unpivot and you'll get a few examples/topics that discuss that transformation.|||

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

|||

Brian Knight wrote:

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

How can the variable columns not be a problem? Because the flat file source won't account for the variability, you'll have to read in each record as one column, and then split it with a script transformation. In doing that, you might be able to set a fixed number of columns and fill them with NULLs if no data exists. I'm trying to understand how you think you can expose all of the columns to the unpivot transformation when the metadata can't be pre-populated with certainty. (That is, based on my previous example, there's a certain row limit that the flat file connector uses to assess how many columns there are in the file.)|||

I am getting following error while trying Unpivot Transform:

PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.

Read CSV file - Save Columns into rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Amar:

I think for what you are describing I would use SSIS; if you are running SQL Server 2000, that would be DTS instead of SSIS. Other alternatives include the use of OPENROWSET, BULK INSERT, or BCP. Read about these alternatives in books online and choose the alternative that you think best fits.

Dave

|||

Hi Amar,

refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx also.

Hemantgiri S. Goswami

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on
Customer count(2nd record in each row of CSV file) and save to SQL
table
--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
Total_3..can go upto 350
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
Brown',17.00,'Rick Davis',18.00
Data in SQL table from csv file should look like this
State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00
I have multiple CSV files with millions of records.What is the best and
fastest way to achieve this?
Throw me anythingHi
"pintoo" wrote:

> I want to import CSV file and convert columns into rows depending on
> Customer count(2nd record in each row of CSV file) and save to SQL
> table
> --CSV file format
> State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
> Total_3..can go upto 350
> GA,2,'John Doe',14.00,'Roger Smith',15.00
> FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
> SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
> Brown',17.00,'Rick Davis',18.00
> Data in SQL table from csv file should look like this
> State,Name,Total
> GA,John Doe,14.00
> GA,Roger Smith,15.00
> FL,John Doe,14.00,
> FL,Roger Smith,15.00
> FL,Sally Cox,16.00
>
If you are moving these into rows the data is not normalized! You will only
be able to have a finite number of rows that can be converted, therefore you
could self join if each row is allocated a sequence number or the methid
described by Erland in
http://groups-beta.google.com/group...r />
2e?&hl=en

> I have multiple CSV files with millions of records.What is the best and
> fastest way to achieve this?
> Throw me anything
>
If you are using SQL 2005 you could use the PIVOT command.
John

RDC user info

i want to write user info to a table when row deleted on
delete trigger.how can i get user info ?this user is using
query analyzer and connect server via remote desktop
connection. how can i get this user machine name or ip,
thanksThere are system functions that will return that info. Be aware that the
logged in application can optionally set the host information in its
connection string. SQL just reads what the lower levels tell it.
If you want the machine info, use host_name or host_id.
If you want the logged in user info, use system_user or current_user.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Rate calculation with matrix reports, How?

I have a report like this, and I would need to implement drilldown on
both column group and row group, and i am running into problems:
2003
Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
Total_Rate
West 10 10% 20 20% 50 50% 20 20% | 100
100%
East 20 10% 30 15% 20 10% 130 65% | 200
100%
North 20 20% 20 20% 40 40% 20 20% | 100
100%
South 30 30% 20 20% 10 10% 20 20% | 100
100%
----
Total 80 16% 90 18% 120 24% 190 38% 500
100%
I can do sum on the numbers, but the rate calculation is difficult.
Does anyone know how to do this?You can use the scope argument to aggregate functions to define a scope over
which to calculate a total.
For a percent-of-total calculation like you describe, you would want
something like this:
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
where "matrix1_Region" is the name of your row group
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407261518.4e5582b6@.posting.google.com...
> I have a report like this, and I would need to implement drilldown on
> both column group and row group, and i am running into problems:
> 2003
> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> Total_Rate
> West 10 10% 20 20% 50 50% 20 20% | 100
> 100%
> East 20 10% 30 15% 20 10% 130 65% | 200
> 100%
> North 20 20% 20 20% 40 40% 20 20% | 100
> 100%
> South 30 30% 20 20% 10 10% 20 20% | 100
> 100%
> ----
> Total 80 16% 90 18% 120 24% 190 38% 500
> 100%
> I can do sum on the numbers, but the rate calculation is difficult.
> Does anyone know how to do this?|||Where exactly within the matrix would you put the
calculation? Thanks.
>--Original Message--
>You can use the scope argument to aggregate functions to
define a scope over
>which to calculate a total.
>For a percent-of-total calculation like you describe,
you would want
>something like this:
>=Sum(Fields!Sales.Value)/Sum(Fields!
Sales.Value,"matrix1_Region")
>where "matrix1_Region" is the name of your row group
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"Nick" <deadlocklegend@.gmail.com> wrote in message
>news:313b74d.0407261518.4e5582b6@.posting.google.com...
>> I have a report like this, and I would need to
implement drilldown on
>> both column group and row group, and i am running into
problems:
>> 2003
>> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
Q4_Rate | Total
>> Total_Rate
>> West 10 10% 20 20% 50 50% 20
20% | 100
>> 100%
>> East 20 10% 30 15% 20 10% 130
65% | 200
>> 100%
>> North 20 20% 20 20% 40 40% 20
20% | 100
>> 100%
>> South 30 30% 20 20% 10 10% 20
20% | 100
>> 100%
>> ----
--
>> Total 80 16% 90 18% 120 24% 190
38% 500
>> 100%
>> I can do sum on the numbers, but the rate calculation
is difficult.
>> Does anyone know how to do this?
>
>.
>|||As the value of the second data cell.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Terry" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c47417$66c96140$a301280a@.phx.gbl...
> Where exactly within the matrix would you put the
> calculation? Thanks.
>
> >--Original Message--
> >You can use the scope argument to aggregate functions to
> define a scope over
> >which to calculate a total.
> >For a percent-of-total calculation like you describe,
> you would want
> >something like this:
> >
> >=Sum(Fields!Sales.Value)/Sum(Fields!
> Sales.Value,"matrix1_Region")
> >where "matrix1_Region" is the name of your row group
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"Nick" <deadlocklegend@.gmail.com> wrote in message
> >news:313b74d.0407261518.4e5582b6@.posting.google.com...
> >> I have a report like this, and I would need to
> implement drilldown on
> >> both column group and row group, and i am running into
> problems:
> >>
> >> 2003
> >> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
> Q4_Rate | Total
> >> Total_Rate
> >> West 10 10% 20 20% 50 50% 20
> 20% | 100
> >> 100%
> >> East 20 10% 30 15% 20 10% 130
> 65% | 200
> >> 100%
> >> North 20 20% 20 20% 40 40% 20
> 20% | 100
> >> 100%
> >> South 30 30% 20 20% 10 10% 20
> 20% | 100
> >> 100%
> >> ----
> --
> >> Total 80 16% 90 18% 120 24% 190
> 38% 500
> >> 100%
> >>
> >> I can do sum on the numbers, but the rate calculation
> is difficult.
> >> Does anyone know how to do this?
> >
> >
> >.
> >|||Thanks much. That worked.
But the requirement has changed slightly because sometimes the Quarter
numbers don't add up to the "supposed" yearly number for several
reasons. Instead of dividing by the sum of all quarter numbers, I
need to get from proc a yearly number and calculate the percentage by
the yearly number and then tally up. I am running into problems
because when I use sum(Q_No)/First(Year_No) the rates work right, but
the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
right but the individual rates are wrong.
Any help very much appreciated.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> You can use the scope argument to aggregate functions to define a scope over
> which to calculate a total.
> For a percent-of-total calculation like you describe, you would want
> something like this:
> =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> where "matrix1_Region" is the name of your row group
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > I have a report like this, and I would need to implement drilldown on
> > both column group and row group, and i am running into problems:
> >
> > 2003
> > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > Total_Rate
> > West 10 10% 20 20% 50 50% 20 20% | 100
> > 100%
> > East 20 10% 30 15% 20 10% 130 65% | 200
> > 100%
> > North 20 20% 20 20% 40 40% 20 20% | 100
> > 100%
> > South 30 30% 20 20% 10 10% 20 20% | 100
> > 100%
> > ----
> > Total 80 16% 90 18% 120 24% 190 38% 500
> > 100%
> >
> > I can do sum on the numbers, but the rate calculation is difficult.
> > Does anyone know how to do this?|||If you need a different calculation in the total cells than in the detail
cells, that's where the InScope function comes in.
You can do something like this:
=iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
In your case, it would be:
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407271256.179a0f98@.posting.google.com...
> Thanks much. That worked.
> But the requirement has changed slightly because sometimes the Quarter
> numbers don't add up to the "supposed" yearly number for several
> reasons. Instead of dividing by the sum of all quarter numbers, I
> need to get from proc a yearly number and calculate the percentage by
> the yearly number and then tally up. I am running into problems
> because when I use sum(Q_No)/First(Year_No) the rates work right, but
> the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> right but the individual rates are wrong.
> Any help very much appreciated.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > You can use the scope argument to aggregate functions to define a scope
over
> > which to calculate a total.
> > For a percent-of-total calculation like you describe, you would want
> > something like this:
> >
> > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > where "matrix1_Region" is the name of your row group
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > I have a report like this, and I would need to implement drilldown on
> > > both column group and row group, and i am running into problems:
> > >
> > > 2003
> > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > Total_Rate
> > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > 100%
> > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > 100%
> > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > 100%
> > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > 100%
> > > ----
> > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > 100%
> > >
> > > I can do sum on the numbers, but the rate calculation is difficult.
> > > Does anyone know how to do this?|||thanks Chris.
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
didn't work, but this did,
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
However, there is a problem when the year was collapsed, the sum of
yearly number gets multiplied by the number of Q, which is 4
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value) *
CountDistinct(Fields!Q_No.Value))
Thanks a lot for your help.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<OLHBCbCdEHA.592@.TK2MSFTNGP11.phx.gbl>...
> If you need a different calculation in the total cells than in the detail
> cells, that's where the InScope function comes in.
> You can do something like this:
> =iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
> In your case, it would be:
> =iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
> Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407271256.179a0f98@.posting.google.com...
> > Thanks much. That worked.
> >
> > But the requirement has changed slightly because sometimes the Quarter
> > numbers don't add up to the "supposed" yearly number for several
> > reasons. Instead of dividing by the sum of all quarter numbers, I
> > need to get from proc a yearly number and calculate the percentage by
> > the yearly number and then tally up. I am running into problems
> > because when I use sum(Q_No)/First(Year_No) the rates work right, but
> > the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> > right but the individual rates are wrong.
> >
> > Any help very much appreciated.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > > You can use the scope argument to aggregate functions to define a scope
> over
> > > which to calculate a total.
> > > For a percent-of-total calculation like you describe, you would want
> > > something like this:
> > >
> > > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > > where "matrix1_Region" is the name of your row group
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach of
> > > children under 3.
> > > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > > I have a report like this, and I would need to implement drilldown on
> > > > both column group and row group, and i am running into problems:
> > > >
> > > > 2003
> > > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > > Total_Rate
> > > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > > 100%
> > > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > > 100%
> > > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > > 100%
> > > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > > 100%
> > > > ----
> > > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > > 100%
> > > >
> > > > I can do sum on the numbers, but the rate calculation is difficult.
> > > > Does anyone know how to do this?