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
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 30, 2012
Reading a record without placing a lock
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.<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.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||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:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 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 =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<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
> > 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
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<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
> > 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
> <ckk...@.my-deja.com> wrote in message
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.<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.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||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:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 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 =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<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
> > 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
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<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
> > 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
> <ckk...@.my-deja.com> wrote in message
Wednesday, March 21, 2012
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
sqlRead 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
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.|||
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
|||
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?|||
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.
|||
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.|||Can you please point me any examples or blogs
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.
|||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.|||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.|||
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
|||
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?|||
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.
|||
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.|||Can you please point me any examples or blogs
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.
|||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
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
Read Backup File Directly?
Is there a way to read a backup file to see if it contains a file without
restoring the backup file? We are missing a record from a table, and I need
to find out when it was deleted. We have nightly backup files of the
database. Is it possible, knowing the database name, the table name, and the
primary key value of the record, to determine if the record exists in a
certain backup file? If not, is there an alternate way to determine when a
particular record was deleted?
You can use RESTORE FILELISTONLY to list the backups within a backup file.
You need to restore the backup. But you could restore it under a different
name.
>
You can use RESTORE FILELISTONLY to list the backups within a backup file.
>
>
You need to restore the backup. But you could restore it under a different
name.
>
>
--
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
restoring the backup file? We are missing a record from a table, and I need
to find out when it was deleted. We have nightly backup files of the
database. Is it possible, knowing the database name, the table name, and the
primary key value of the record, to determine if the record exists in a
certain backup file? If not, is there an alternate way to determine when a
particular record was deleted?
Thank you!
NeilNeil (nospam@.nospam.net) writes:
Quote:
Originally Posted by
Is there a way to read a backup file to see if it contains a file
without restoring the backup file?
You can use RESTORE FILELISTONLY to list the backups within a backup file.
Quote:
Originally Posted by
We are missing a record from a table, and I need to find out when it was
deleted. We have nightly backup files of the database. Is it possible,
knowing the database name, the table name, and the primary key value of
the record, to determine if the record exists in a certain backup file?
You need to restore the backup. But you could restore it under a different
name.
--
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|||Right. I was just that, if I wanted to find out *when* it was deleted, I'd
have to restore the backups one at a time, and look at the database after
each restore to see if it were there.
Thanks,
Neil
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955EDFCA1AF8Yazorman@.127.0.0.1...
Quote:
Originally Posted by
Neil (nospam@.nospam.net) writes:
Quote:
Originally Posted by
>Is there a way to read a backup file to see if it contains a file
>without restoring the backup file?
>
You can use RESTORE FILELISTONLY to list the backups within a backup file.
>
Quote:
Originally Posted by
>We are missing a record from a table, and I need to find out when it was
>deleted. We have nightly backup files of the database. Is it possible,
>knowing the database name, the table name, and the primary key value of
>the record, to determine if the record exists in a certain backup file?
>
You need to restore the backup. But you could restore it under a different
name.
>
>
--
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
Tuesday, March 20, 2012
Re: Any one help me out!
Hi
I have a requirement in SSRS where in i need to generate a report in which initially there will be one record (say, a manager of a department and some values corresponding to him ) now i need to provide a drill-down on this employee(mamager) such that the next level of employees reporting to him should be displayed with all the parameters and this has to continue till the leaf level employee.
Thanks in advance.
will the number of levels be known in advance or is it parent-child?
RS is not very good at dealing with an arbitary number of grouping levels.
One way to do it would be define a report with a manager parameter that renders a table of subordiantes. Then in a master report, nest the subreport in a table for all expected levels passing the employee id to the subreport. Problem with that is that it wouldn't export very well.
The other ooption would be to PIVOT the parent-child table i.e. flatten it out into columns per each level, then build your table based on that.
Either way you'll need to know the number of levels in advance.
|||Yes the number of levels will be known in advance.
i'm a novice in SSRS and does not much about it so can you throw some light on what you have just said.
|||Please tell me the structure of the data and we'll take it from there.
Is it SQL or OLAP? A table or a dimension? what are the columns or levels?
|||Hi adam,
I'll get an employee id as a parameter to the stored procedure and then i'll get a dataset which will contain all the data for me for all the employees.
Then from this data set i need to generate the drill down report as
(the root level employee)
(then his subordinates)
(then for each employee in the above level we need to get the lower levels here.)
............................................
And this hierarchy goes on till the last employee.
Thanks in advance.|||
ok, I'll ask the question again.
In format does the data come back? What are the columns?
is it employee_id, manager_id (where manager _id is the employee_id of the manager)
OR
is it employee_level_1_id, employee_level_2_id, employee_level_3_id,......
The latter is what you want to end up with to structure your report.
|||Hi adam,
The data is in the following format
EmployeeID ManagerID Level(the level of the employee)
Thanks in advance.
|||OK what about the top level manager, is his manager_id = employee_id or is it NULL?
Does the Level column representative of the number of levels in the hierarchy? In it numeric or a textual descriptor?
I assume you also have employee_name an well as some other employee information.
Can you post a little bit of dummy data?
|||hi
top level managertop level manager is NULL and you will have columns like
EMP_NO , EMP_NAME , MGR_NO , LEVEL(int) , and some other fields............................
12345 xyz 32141 1
level ranges from 1-some known value
|||The following is a code example of my implementation. Copy the xml into a file and save as .RDL.
You may need to modify the connection as locally I have SQL 2005 installed as a named instance.
The code flattens a table by performing a join onto itself for every level in the hierarchy. I'll try to come up with a neater solution because it feels like I should be able to use the new PIVOT functionality of SQL.
[code]
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.\sql2005;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>008a7b74-651a-4e19-ad0d-a1af34c884ef</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dst_employee</DataSetName>
<Top>0.91429cm</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_level">
<rd:DefaultName>l1_employee_level</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_id">
<rd:DefaultName>l1_employee_id</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_name">
<rd:DefaultName>l1_employee_name</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l1_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l1_employee_level">
<GroupExpressions>
<GroupExpression>=Fields!l1_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l1_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_level">
<rd:DefaultName>l2_employee_level</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SlateGray</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_id">
<rd:DefaultName>l2_employee_id</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_name">
<rd:DefaultName>l2_employee_name</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l2_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l2_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l2_employee_id.Value, CStr(Fields!l2_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l2_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l2_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_level">
<rd:DefaultName>l3_employee_level</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#8fa0b0</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_id">
<rd:DefaultName>l3_employee_id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_name">
<rd:DefaultName>l3_employee_name</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l3_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l3_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l3_employee_id.Value, CStr(Fields!l3_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l3_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>7.61904cm</Width>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
<Height>2.15872cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.91429cm</Height>
<Value>Report3</Value>
</Textbox>
</ReportItems>
<Height>3.60635cm</Height>
</Body>
<rd:ReportID>0b7fa61b-6ddf-4332-bf17-5b42a10a7437</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_employee">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH employee AS
(
SELECT employee_id = 1, employee_name = 'Manager 1', manager_id = NULL, level_no = 1
UNION ALL
SELECT employee_id = 2, employee_name = 'Sub Manager 1', manager_id = 1, level_no = 2
UNION ALL
SELECT employee_id = 3, employee_name = 'Leaf Employee 1', manager_id = 2, level_no = 3
UNION ALL
SELECT employee_id = 4, employee_name = 'Leaf Employee 2', manager_id = 1, level_no = 2
)
SELECT l1_employee_id = l1.employee_id
, l1_employee_name = l1.employee_name
, l1_employee_level = l1.level_no
, l2_employee_id = l2.employee_id
, l2_employee_name = l2.employee_name
, l2_employee_level = l2.level_no
, l3_employee_id = l3.employee_id
, l3_employee_name = l3.employee_name
, l3_employee_level = l3.level_no
FROM employee l1
LEFT JOIN employee l2
ON l1.employee_id = l2.manager_id
LEFT JOIN employee l3
ON l2.employee_id = l3.manager_id
WHERE ISNULL(l1.level_no, 1) = 1
AND ISNULL(l2.level_no, 2) = 2
AND ISNULL(l3.level_no, 3) = 3</CommandText>
<DataSourceName>ds_master</DataSourceName>
</Query>
<Fields>
<Field Name="l1_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_id</DataField>
</Field>
<Field Name="l1_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l1_employee_name</DataField>
</Field>
<Field Name="l1_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_level</DataField>
</Field>
<Field Name="l2_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_id</DataField>
</Field>
<Field Name="l2_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l2_employee_name</DataField>
</Field>
<Field Name="l2_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_level</DataField>
</Field>
<Field Name="l3_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_id</DataField>
</Field>
<Field Name="l3_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l3_employee_name</DataField>
</Field>
<Field Name="l3_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_level</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>22.85714cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
[/code]
Monday, February 20, 2012
Raw File Source issue
I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).
In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.
But here is my problem.
This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...
When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.
I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)
And of course with that type of solution my package could never "clean up" after itself...
Try setting DelayValidation to TRUE for all source / destination components
Thanks,
Sankaranarayanan MG
Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.
HTH,
Matt
Subscribe to:
Posts (Atom)