Showing posts with label current. Show all posts
Showing posts with label current. Show all posts

Wednesday, March 28, 2012

Problems with SqlDataAdapter

I'm working on a project to dynamically create PDFs with content from an SQL server. My current approach is to get the data I need into a DataSet using SqlDataAdapter, write the DataSet to a stream as XML, transform the XML into FO and then output a pdf using FOP. For some reason I get the following exception "System.NullReferenceException - Object reference not set to an instance of an object" when I try to set the SelectCommand property of my data adapter. Code for the project follows:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim IDlist As String
Dim keyidary As Array
Dim query As String
Dim tempxml As Stream

query = "*****query is valid and very long, so it's not included here*****"

IDlist = Request.Form("chosenIDs")

keyidary = Split(IDlist, ",")
Dim makequery As New StringBuilder(query)

For Each ID As String In keyidary
makequery.Append(" OR (KeyID = '" & ID & "')")
Next

query = makequery.ToString()

'Response.Write(query) When uncommented this prints the query just fine
'Response.End()

Try
con = New SqlConnection
con.ConnectionString = "****Connection String is valid****"

cmd = New SqlCommand
cmd.CommandText = query
cmd.CommandType = CommandType.Text
cmd.Connection = con

Dim adpt As SqlDataAdapter
adpt.SelectCommand = cmd

'Response.Write(query) When these are above or between the previous 2 statements, query is printed,
'Response.End() otherwise I get the error described above.

Dim profiles As New DataSet
adpt.Fill(profiles)
profiles.WriteXml(tempxml)

Dim step1 As XslTransform = New XslTransform
Dim step2 As XslTransform = New XslTransform
step1.Load(Server.MapPath("TransAttmpt1.xslt"))
step2.Load(Server.MapPath("formatXML.xsl"))

Dim xml, pdf As String
xml = "profiles.xml"
pdf = "Profiles.pdf"

Dim temp2xml As New XPathDocument(tempxml)

Response.Write(query)
Response.End()
Dim midstream As Stream
Dim finalxml As StreamWriter = New StreamWriter(xml)

step1.Transform(temp2xml, Nothing, midstream, Nothing)
Dim xpathdoc2 As XPathDocument = New XPathDocument(midstream)

step2.Transform(xpathdoc2, Nothing, finalxml, Nothing)
GeneratePDF(xml, pdf)

'There's a lot more but it doesn't seem relevant now...

I'm somewhat at a loss on how to proceed and any help is very greatly appreciated.

Thanks!

You are not initializing the dataadpater. You should initialize like this:

Dim adptAs SqlDataAdapter =New SqlDataAdapter()
|||

Figured this out probably 3 minutes before you posted but thanks!

Friday, March 23, 2012

Problems with ProcessAdd and secondary DataSource

We have a SSAS 2005 cube in place and have been using ProcessAdd XMLA for some time with success. The current environment has a single datasource called 'Sales' and a single DSV called 'Sales'. The sales datasource is a DB2 database. Everything works great.

In preperation for adding some new data sources, we have built a version in our test environment that is configured with two data sources and a single DSV. 'Master' is the primary DS and it points to an empty DB on SQL Server. 'Sales' is a secondary DS and it points to the original DB2 database. There is a single DSV that is called 'Master'. Everything works well through SSMS, but I can't seem to build the correct XMLA to get ProcessAdd working again.

If I build it with the 'Master' datasource and DSV it gives me a bunch of errors that arent specific enough for me to take action. Things like

Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM IBU HIER VER', Name of 'IBU' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile=""

If I build the xmla for ProcessAdd with the 'Sales' datasource and 'Master' DSV and then rename 'Master' to 'Sales' it appears to work and executes successfuly.. However, when you watch the activity on the profiler, it is not using the query from the out-of-line DSV query but instead it goes back to SSAS DB and uses the query stored there.

All of the ProcessAdd examples I have seen seem to deal with a single DSV and DS.

Can there be multiple datasources in the ProcessAdd xmla?

Is it possible to make this scenario work?

Thanks,

Marty

Have you seen Greg's post on ProcessAdd at http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx ?

Apparently the out-of-line dsv needs to have the same ID as the datasource that was used to orginally build the object and the data sources need to be included in the xmla Process element (this is a bug) - see the notes in his process dimension example.

|||Darren, Thanks for the reply. I still couldnt get it to work. Something about the two data sources seemed to throw it off. What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query. In our environment this actually provides a simpler and easier to maintain solution.

Thanks again,
Marty

Problems with ProcessAdd and secondary DataSource

We have a SSAS 2005 cube in place and have been using ProcessAdd XMLA for some time with success. The current environment has a single datasource called 'Sales' and a single DSV called 'Sales'. The sales datasource is a DB2 database. Everything works great.

In preperation for adding some new data sources, we have built a version in our test environment that is configured with two data sources and a single DSV. 'Master' is the primary DS and it points to an empty DB on SQL Server. 'Sales' is a secondary DS and it points to the original DB2 database. There is a single DSV that is called 'Master'. Everything works well through SSMS, but I can't seem to build the correct XMLA to get ProcessAdd working again.

If I build it with the 'Master' datasource and DSV it gives me a bunch of errors that arent specific enough for me to take action. Things like

Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM IBU HIER VER', Name of 'IBU' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile=""

If I build the xmla for ProcessAdd with the 'Sales' datasource and 'Master' DSV and then rename 'Master' to 'Sales' it appears to work and executes successfuly.. However, when you watch the activity on the profiler, it is not using the query from the out-of-line DSV query but instead it goes back to SSAS DB and uses the query stored there.

All of the ProcessAdd examples I have seen seem to deal with a single DSV and DS.

Can there be multiple datasources in the ProcessAdd xmla?

Is it possible to make this scenario work?

Thanks,

Marty

Have you seen Greg's post on ProcessAdd at http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx ?

Apparently the out-of-line dsv needs to have the same ID as the datasource that was used to orginally build the object and the data sources need to be included in the xmla Process element (this is a bug) - see the notes in his process dimension example.

|||Darren, Thanks for the reply. I still couldnt get it to work. Something about the two data sources seemed to throw it off. What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query. In our environment this actually provides a simpler and easier to maintain solution.

Thanks again,
Marty

Wednesday, March 7, 2012

Problems with cursor and update where current of

I'm having a problem ... obviously or I wouldn't be here. Anyway ... There is a table containing a line number field (called ln_num) that needs to be renumbered based on the order of other certain fields for certain accounts. The table in question is called parcel_rel and here are the fields:

strap varchar(13); ln_num int; tp varchar(2); rel_strap varchar(13)

PKey is on strap, ln_num

I load into a "temp" table the accounts that need renumbering (called renum_parcel_rel_table) and on this particular run, I have only one: strap = 0935310000111, which has two records (one with tp=TB and another with tp=PA. Here is the code that I am using. It ran PERFECTLY on SQL2000, but since we upgraded to SQL2005, it gets caught in a loop -- thus the reason for me posting this question.

set nocount on

declare @.strap varchar (13) ,
@.old_strap varchar (13) ,
@.ln_num int ,
@.c_ln_num int ,
@.tp varchar (10) ,
@.rel_strap varchar (13)

declare cRecords__parcel_rel cursor for
select r.strap, ln_num, tp, rel_strap
from r_2006..parcel_rel r
join h_2006..renum_parcel_rel_table h
on r.strap = h.strap
order by r.strap, tp, rel_strap
for update of ln_num

set @.old_strap = ''

open cRecords__parcel_rel
fetch next from cRecords__parcel_rel into @.strap, @.c_ln_num, @.tp, @.rel_strap

while (@.@.FETCH_STATUS = 0) begin

print 'pre-update: @.strap = ' + @.strap + ' @.c_ln_num = ' + cast(@.c_ln_num as varchar(10)) + ' @.tp = ' + @.tp + ' @.rel_strap = ' + @.rel_strap
if @.strap = @.old_strap begin
set @.ln_num = @.ln_num + 1
end else begin
set @.ln_num = 1
set @.old_strap = @.strap
end
update r_2006..parcel_rel
set ln_num = @.ln_num
where current of cRecords__parcel_rel -- key to it all!!!!!

print 'updated ln_num to: @.ln_num = ' + cast(@.ln_num as varchar(10))
fetch next from cRecords__parcel_rel into @.strap, @.c_ln_num, @.tp, @.rel_strap
print '@.@.FETCH_STATUS = ' + cast(@.@.FETCH_STATUS as varchar(10))
end

close cRecords__parcel_rel
deallocate cRecords__parcel_rel

set nocount off

The problem is that I get output like this and the loop never dies (@.@.fetch_status bolded for reference):

pre-update: @.strap = 0935310000111 @.c_ln_num = 1661 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 1
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1662 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 2
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 3
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 2 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 4
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 3 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 5
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 4 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 6
@.@.FETCH_STATUS = 0
.... etc

As you can see, even though this account has only two entries in the parcel_rel table (one tp=PA and one tp=TB) the cursor keeps getting a @.@.fetch_status = 0 even after the last (second) record is passed.

Interestingly enough, if I remove (comment out) the update...where current of portion of the code, look:

pre-update: @.strap = 0935310000111 @.c_ln_num = 1883 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 1
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1884 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 2
@.@.FETCH_STATUS = -1

Notice that after the second record is read in and processed, and the following fetch next is executed, @.@.FETCH_STATUS correctly returns a -1. Is the update...where current of causing the cursor to revert back to the first record, or causing the following fetch next to function like a fetch first? I'm puzzled that this functionality would so greatly change between SQL2000 and SQL2005.

Any comments would be greatly appreciated. And if you could also email replies or notification of replies to bscrivener@.hcad.org I would greatly appreciate that, too.

Thanks in advance!

Your cursor problem (other than using a cursor :)) is probably due to the cursor not being static. I think changing to a fast_forward or static cursor will fix your problem. However, it would be far easier to use the new row_number function to do the reorder. Here is a sample:

create table orderMe
(
orderMeId int primary key,
groupName varchar(10),
value varchar(20),
orderBy int,
unique(groupName, value)
)
insert into orderMe
select 1,'beatles','john',10
union all
select 2,'beatles','paul',12
union all
select 3,'beatles','george',12
union all
select 4,'beatles','ringo',12
union all
select 5,'rat pack','frank',1
union all
select 6,'rat pack','sam',50
union all
select 7,'rat pack','dean',1
go
update orderMe
set orderBy = orderUs.newOrderMeId
from orderMe
join (select orderMeId,
row_number() over (partition by groupName order by value) as newOrderMeId
from orderMe) as orderUs
on orderMe.orderMeId = orderUs.orderMeId
go
select *
from orderMe

Now the orderBy column is order by their name... Much easier than a cursor (and likely a lot faster)

orderMeId groupName value orderBy
-- - -- --
1 beatles john 2
2 beatles paul 3
3 beatles george 1
4 beatles ringo 4
5 rat pack frank 2
6 rat pack sam 3
7 rat pack dean 1

|||

You can simplify the UPDATE statement by using a CTE like:

with o
as
(
select orderBy, row_number() over (partition by groupName order by value) as newOrderMeId
from orderMe
)
update o
set orderBy =newOrderMeId;

|||You gotta admire the work. Nice.|||Oooh, cool. Very nice...