Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

FileShareProvider delivery extension credential encoding

I receive the following error when I try to provide a username and password
to the FileShareProvider delivery extension through the data-driven
subscription query:
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
DBUnProtectData failed: System.FormatException: Invalid character in a
Base-64 string.
at System.Convert.FromBase64String(String s)
at
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
g data)
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
user: secret
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
Error occured processing notification. Invalid character in a Base-64
string.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
saved.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
character in a Base-64 string.
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: Notification
951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
Invalid character in a Base-64 string., DeliveryExtension: Report Server
FileShare, Report: Invoice, Attempt 0
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
NotificationPolling finished processing item
951ca046-a4a7-4657-be85-04512f3e8787
It seems that the extension is trying to decode the username and password
from the query, but can't since they aren't encoded. If I hard-code the
username and password values in the subscription itself, everything works
fine.
I tried to use
Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
encode the username and password before storing them in the database, but am
getting the following error:
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed..
What do I need to do to make the extension happy?
JayIt's a know issue, the user name and password are not supported by the file
share delivery extension, can you make them not data driven - i.e. constant?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay Hackney" <jayh@.nospam.intellinet.com> wrote in message
news:#wIsGe2aEHA.3804@.TK2MSFTNGP10.phx.gbl...
> I receive the following error when I try to provide a username and
password
> to the FileShareProvider delivery extension through the data-driven
> subscription query:
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
> NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
> DBUnProtectData failed: System.FormatException: Invalid character in a
> Base-64 string.
> at System.Convert.FromBase64String(String s)
> at
>
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
> g data)
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
> user: secret
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
> Error occured processing notification. Invalid character in a Base-64
> string.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
> Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
> saved.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
> character in a Base-64 string.
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23::
Notification
> 951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
> Invalid character in a Base-64 string., DeliveryExtension: Report Server
> FileShare, Report: Invoice, Attempt 0
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
> NotificationPolling finished processing item
> 951ca046-a4a7-4657-be85-04512f3e8787
> It seems that the extension is trying to decode the username and password
> from the query, but can't since they aren't encoded. If I hard-code the
> username and password values in the subscription itself, everything works
> fine.
> I tried to use
> Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
> encode the username and password before storing them in the database, but
am
> getting the following error:
> System.Security.SecurityException: Request for the permission of type
> System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed..
> What do I need to do to make the extension happy?
> Jay
>

File transfer

I have to make a process/stored procedure that will either send or receive from a specific location (parameters: ftp server, username, password, filename, etc). Ne direction/help in order to do this. Or if anyone has done this before please help.
thxCould you supply some more specifics?|||The specifies are:

I have to make a sp that will send/receive files to/from an ip address or ftp site.
The sp will query the information such as (filename, send_time, receive/send, servername, usernid, password, size_of_file, etc).
Once this info is queried, the script should transfer the file to respective location using, either exec xp_cmdshell or ne other way.

Since it's critical that the file being sent/received, must succesfully be sent/received. The script must also handle that.

This script will be running on SQL server. Any sample code?
thx|||I guess you're going to use ftp...

OK...build an ftp script...and execute it from a bat file with xp_cmdshell...don't forget to redirect the output...maybe load it to a log table...

I guess you'll interogate a directory..

Use xp_cmdshell 'DIR D;\whatever\*.*

and load that to a table to interogate that...

something like that?