DTS Transformation Task: point

Showing posts with label point. Show all posts

Wednesday, March 21, 2012

Newbie Problems Configuring Report Server

I have SQL 2005 with SP 1. I am in the reporting services configuration
manager and I am at the point where the following options have green circles
with checkmarks:
Server Status
Report Server Virtual Directory
Report Manager Virtual Directory
Windows Service Identity
Web Service Identity
Database Setup
Encryption Keys is a blue circle with an exclamation point with the choices
Backup, Restore, Change and Delete. I choose Backup and gave the key a
password and clicked ok but the applied button at the bottom stayed grayed
out.
Below the Encryption Keys button is Initialization and that is in a gray
circle with a checkmark. Since it is gray I can not select it.
What is it that the configuration manager is waiting for me to do?
I tried going into the SQL Server Management Studio to see if I could
connect to the Report Server from there. I get an error message saying
"Cannot connect to RBDS01. The request failed with HTTP Status 400: Bad
Request. (Microsoft.SqlServer.Management.UI.RSClient)
I would appreciate any ideas as to what I need to do so that I can connect
to the Report Server?
Thanks.
Rick Bellefond
RB Data Services
www.rbdata.comIf the "server status" shows green then it is ok that your server is
connecting. encryption is optional it creates automatically so if you want
to change or take backup this can be done using this option.
otherwise it should work seeing the green check mark on "server status" Hope
you are connecting to the same server you configured. ie (RBDS01)
Go to surface area and check for the network enabled in which you are
accesing ie tcp/ip.
Amarnath
"Rick" wrote:
> I have SQL 2005 with SP 1. I am in the reporting services configuration
> manager and I am at the point where the following options have green circles
> with checkmarks:
> Server Status
> Report Server Virtual Directory
> Report Manager Virtual Directory
> Windows Service Identity
> Web Service Identity
> Database Setup
> Encryption Keys is a blue circle with an exclamation point with the choices
> Backup, Restore, Change and Delete. I choose Backup and gave the key a
> password and clicked ok but the applied button at the bottom stayed grayed
> out.
> Below the Encryption Keys button is Initialization and that is in a gray
> circle with a checkmark. Since it is gray I can not select it.
> What is it that the configuration manager is waiting for me to do?
> I tried going into the SQL Server Management Studio to see if I could
> connect to the Report Server from there. I get an error message saying
> "Cannot connect to RBDS01. The request failed with HTTP Status 400: Bad
> Request. (Microsoft.SqlServer.Management.UI.RSClient)
> I would appreciate any ideas as to what I need to do so that I can connect
> to the Report Server?
> Thanks.
>
> Rick Bellefond
> RB Data Services
> www.rbdata.com
>

Monday, March 12, 2012

newbie help--restore to point in time

I have a customer who's cart has been deleting orders incorrectly. I
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> >From various sources this is the script I'm using:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:
> > I have a customer who's cart has been deleting orders incorrectly. I
> > backed up and restored the database from the remote server to my local.
> > Would like to restore to point in time before the delete operation.
> > >From various sources this is the script I'm using:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > 11:13:04'
> > GO
> >
> > Ok, no errrors are thrown, but when I select from a table, I'm still
> > finding orders with dates past July 16. Am I trying to do something not
> > allowed? Both the database bu and the log bu contain transactions and
> > data through August 2nd. Can someone point me in the right direction?|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:
> > Why are you using the NOUNLOAD option when recovering from a file?
> >
> > Also,
> > What is the "N" right before the date/time in your STOPAT ?
> >
> > Your first statement here says to backup log? I think you would want:
> >
> > RESTORE DATABASE
> > (FROM ...)
> > WITH NORECOVERY
> >
> > --this one for every full log you want to roll in:
> > RESTORE LOG
> > (FROM ...)
> > WITH NORECOVERY
> >
> > RESTORE LOG
> > (FROM ...)
> > WITH RECOVERY, STOPAT = 'Date/time'
> >
> > Also,
> >
> > check out the RESTORE topic in Books Online.
> >
> > I hope this is helpful.
> >
> >
> > medusa wrote:
> > > I have a customer who's cart has been deleting orders incorrectly. I
> > > backed up and restored the database from the remote server to my local.
> > > Would like to restore to point in time before the delete operation.
> > > >From various sources this is the script I'm using:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > 11:13:04'
> > > GO
> > >
> > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > finding orders with dates past July 16. Am I trying to do something not
> > > allowed? Both the database bu and the log bu contain transactions and
> > > data through August 2nd. Can someone point me in the right direction?|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:
> > Well, that was the latest round of code. The n is put in there if you
> > tell management studio to script the action. The backup log is in there
> > because if it's not an error is thrown about not backing up the log
> > tail. Here, this doesn't work either--contains transactions through Aug
> > 2:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > GO
> >
> >
> > SGCSNA wrote:
> > > Why are you using the NOUNLOAD option when recovering from a file?
> > >
> > > Also,
> > > What is the "N" right before the date/time in your STOPAT ?
> > >
> > > Your first statement here says to backup log? I think you would want:
> > >
> > > RESTORE DATABASE
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > --this one for every full log you want to roll in:
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH RECOVERY, STOPAT = 'Date/time'
> > >
> > > Also,
> > >
> > > check out the RESTORE topic in Books Online.
> > >
> > > I hope this is helpful.
> > >
> > >
> > > medusa wrote:
> > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > backed up and restored the database from the remote server to my local.
> > > > Would like to restore to point in time before the delete operation.
> > > > >From various sources this is the script I'm using:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > 11:13:04'
> > > > GO
> > > >
> > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > finding orders with dates past July 16. Am I trying to do something not
> > > > allowed? Both the database bu and the log bu contain transactions and
> > > > data through August 2nd. Can someone point me in the right direction?|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:
> > Are you restoring from a full backup that falls *before* your STOPAT
> > date and all of the subsequent logs, up to the one that contains the
> > STOPAT date?
> > medusa wrote:
> > > Well, that was the latest round of code. The n is put in there if you
> > > tell management studio to script the action. The backup log is in there
> > > because if it's not an error is thrown about not backing up the log
> > > tail. Here, this doesn't work either--contains transactions through Aug
> > > 2:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > GO
> > >
> > >
> > > SGCSNA wrote:
> > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > >
> > > > Also,
> > > > What is the "N" right before the date/time in your STOPAT ?
> > > >
> > > > Your first statement here says to backup log? I think you would want:
> > > >
> > > > RESTORE DATABASE
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > --this one for every full log you want to roll in:
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > >
> > > > Also,
> > > >
> > > > check out the RESTORE topic in Books Online.
> > > >
> > > > I hope this is helpful.
> > > >
> > > >
> > > > medusa wrote:
> > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > backed up and restored the database from the remote server to my local.
> > > > > Would like to restore to point in time before the delete operation.
> > > > > >From various sources this is the script I'm using:
> > > > >
> > > > > BACKUP LOG plebesummercom
> > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > WITH NORECOVERY, No_truncate
> > > > > Go
> > > > > RESTORE DATABASE plebesummercom
> > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > WITH NORECOVERY;
> > > > > GO
> > > > >
> > > > > RESTORE LOG [plebesummercom]
> > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > >
> > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > 11:13:04'
> > > > > GO
> > > > >
> > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > data through August 2nd. Can someone point me in the right direction?|||Thanks a lot for you help.
SGCSNA wrote:
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
> > The full backup goes through Aug 2. I want to stop at July 16.
> >
> > SGCSNA wrote:
> > > Are you restoring from a full backup that falls *before* your STOPAT
> > > date and all of the subsequent logs, up to the one that contains the
> > > STOPAT date?
> > > medusa wrote:
> > > > Well, that was the latest round of code. The n is put in there if you
> > > > tell management studio to script the action. The backup log is in there
> > > > because if it's not an error is thrown about not backing up the log
> > > > tail. Here, this doesn't work either--contains transactions through Aug
> > > > 2:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > > GO
> > > >
> > > >
> > > > SGCSNA wrote:
> > > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > > >
> > > > > Also,
> > > > > What is the "N" right before the date/time in your STOPAT ?
> > > > >
> > > > > Your first statement here says to backup log? I think you would want:
> > > > >
> > > > > RESTORE DATABASE
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > --this one for every full log you want to roll in:
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > > >
> > > > > Also,
> > > > >
> > > > > check out the RESTORE topic in Books Online.
> > > > >
> > > > > I hope this is helpful.
> > > > >
> > > > >
> > > > > medusa wrote:
> > > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > > backed up and restored the database from the remote server to my local.
> > > > > > Would like to restore to point in time before the delete operation.
> > > > > > >From various sources this is the script I'm using:
> > > > > >
> > > > > > BACKUP LOG plebesummercom
> > > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > > WITH NORECOVERY, No_truncate
> > > > > > Go
> > > > > > RESTORE DATABASE plebesummercom
> > > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > > WITH NORECOVERY;
> > > > > > GO
> > > > > >
> > > > > > RESTORE LOG [plebesummercom]
> > > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > >
> > > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > > 11:13:04'
> > > > > > GO
> > > > > >
> > > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > > data through August 2nd. Can someone point me in the right direction?

Monday, February 20, 2012

newbe question: need a way to read in large query with .net

Could someone please point me in the right direction on how to read in
a large query with .net.

I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:

Select * from invoices where year 1995

the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.

And if at all possible I would like to lock one row at a time as the
row is read in.(troy@.makaro.com) writes:

Quote:

Originally Posted by

Could someone please point me in the right direction on how to read in
a large query with .net.
>
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
>
Select * from invoices where year 1995
>
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.

It sounds like you should use ExecuteReader and loop through the rows.
That is, do not use DataAdapter.Fill. Furthermore, to make it possible
to update the rows as you have read them in, you need to enable MARS,
Multiple Active Result Sets, which I believe you do in the connection
string.

However, if your plan is to read one row at a time and update back,
I wonder from where you get the information to update. It's much much
efficient to perform the update in the database on all rows in one
go.

--
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|||Sounds like you need to take a look at this URL:
http://msdn2.microsoft.com/en-us/li...594(VS.80).aspx though
I doubt a newb will understand it much. This is just in case there are
other "MVP's" like myself who are in need of a good reference for a
task like large query input.

HTH,

Carl Tegeder
Master MS-SQL Administrator
MS-SQL MVP

On Feb 26, 12:36 pm, t...@.makaro.com wrote:

Quote:

Originally Posted by

|||Great information on MARS and the ExecuteReader. What I am trying to
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.

Here is one an example of what I have to emulate:

get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next

The converted code looks something like:

' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next

My question now is:
How do I lock one row at a time?

One thought I had was to make all connections go through my own
service. That service could keep track of locks. The problem with that
is that future products will want to use sql properly which would
bypass the locking.|||(troy@.makaro.com) writes:

Quote:

Originally Posted by

Great information on MARS and the ExecuteReader. What I am trying to
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.

I can't escape asking what's the point? You get the legacy product
converted to .Net, but it will still have the architecutre of the
old product, and risk is that you get a compromise with the worst from
both.

Quote:

Originally Posted by

Here is one an example of what I have to emulate:
>
get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next
>
The converted code looks something like:
>
' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next
>
My question now is:
How do I lock one row at a time?

I take it that the other product was using another data store than
SQL Server?

There are a couple of ways to do this, but it is important to understand
that locking a row is nothing you don't really do actively in SQL Server.
This is left to the lock manager.

And it's even less possible in ADO .Net, since ADO .Net uses client-
side cursors only. That is data is read from SQL Server and buffered.
Something like ExecuteReader may not read all million rows at once,
but it will not fetch one row at a time.

One way is to wrap the entire reader in a transaction with the isolation
level REPEATABLE READ. But then rows will remained locked until you
commit.

However, the only reasonable approach is optimistic locking. That is,
don't lock, but check for concurrent updates when you update. This
can be done in two ways:

1) Add a timestamp column: a timestamp column is automatically updated when
the row is updated. If you include the timestamp column in the WHERE
clause, and you see that @.@.rowcount is 0, then you know that the row
was changed since you last read it. 2) Without a timestamp column just
add all columns to the WHERE clause. I believe that the Update commands
that comes with the CommandBuilder includes this.

I can think of a third way: first read all keys into local array. Then
iterate over the array, and read one row at a time as 1) Start transaction
with REPEATABLE READ, 2) read row 3) update and 4) commit. But this
will be slow as I don't know what.

All and all, I think you are fighting an uphiil battle.

Quote:

Originally Posted by

>
I take it that the other product was using another data store than
SQL Server?

Yes, unfortunately :(

Quote:

Originally Posted by

However, the only reasonable approach is optimistic locking.

ya, this is the best method for sure, its just that I need to emulate
the old system as accurately as possible. The legacy software does not
expect concurrency errors on the updates.

Quote:

Originally Posted by

Fortunately the resultsets that require a lock on each row will
probably be fairly small. Larger resultsets are typically reports
which don't require locks. I can see I am going to have to write a
performance test to see the actual speed of reading say 500 rows one
at a time with a lock on them.

Wednesday, March 21, 2012

Newbie Problems Configuring Report Server

Monday, March 12, 2012

newbie help--restore to point in time

newbie help--restore to point in time

Monday, February 20, 2012

newbe question: need a way to read in large query with .net

DTS Transformation Task

Blog Archive

About Me