Difficulty on inserting data into several tables from XML file using Bulk Insert

Hi, I am a newbie for SQL's XML section and need some help from you guys. I just go through some simple articles with simple sample record. However, I found confusing when I want to inserting data into several tables from a prefer XML file.

My scenario is like that :

Tables :

1) trade ( id int)

2) trans ( id int , startdate datetime , enddate datetime , status bit ,comment varchar(50), tranCode int)

* tranCode = id from trade table

3) transInfo ( id int , userlogin int , amount money , transID int ) * transID = id from trans table

4) trandetails ( id int, transID int ) * transID = id from trans table

Preferred XML :

<?xml version='1.0' encoding='utf-8'?>

<ROOT>

<trade code="1">

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Alex" amount="1000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Shipping</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>

</trans>

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Ken" amount="20000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Transportation</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>
</trans>

</trade>

</ROOT>

I am confusing on how to arrange up the elements based on the tables which were created. Or have to re-fix my table ? Any suggestion based on my scenario. Hope able to get any ideas/opinions from here asap. Thanks alot.

Best Regards,

Hans

Problem solved although kinda confusing at the early stage when doing the XML schema.

Difficulties getting @@identity to work in transaction

I am having some problems wit a form where i need to insert a record into a table and then get the id of the record i just inserted and use it to insert a record in another table for a many to many relationship. The code is below (I cut out as much as i could to make it more readable).

The erro message i get is this:

Error saving file ATLPIXOFC.txt Reason: System.Data.SqlClient.SqlException: Prepared statement '(@.FileName varchar(13),@. i' expects parameter @.FileID, which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microvit_Document_Product_Document_Add_v2.insertDocumentToDB() in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 127 at Microvit_Document_Product_Document_Add_v2.btnInsert_Click(Object sender, EventArgs e) in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 37

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute 1st Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName); SELECT @.FileID = @.@.Identity;"

myCommand.ExecuteNonQuery()

'****************************************************************************

' Execute 2nd Command

'****************************************************************************

'myCommand.Parameters("@.FileID").Value = ddlProduct.SelectedValue

myCommand.CommandText ="INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, @.FileID)"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

Has nothing to do with transactions.

You defined the @.FileID parameter as such:

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

Note, you said output. The second command expects it as input.

You can fix this a number of ways. Use two different mycommand objects with different parameters, or set variables to the parameters, clear and rebuild the parameters, and reset the parameter values from your variables, change your first query to accept the same parameters, or just use a single command.

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName) INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY())"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||

Or if you really need the identity back in your program:

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="SET NOCOUNT ON INSERT INTO [Files] ([FileName) VALUES (@.FileName) SET NOCOUNT OFF SELECT SCOPE_IDENTITY() SET NOCOUNT ON INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY()) SET NOCOUNT OFF"

dim MyIdentity=myCommand.ExecuteScaler()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||Oops, I guess I should mention that you can of course, create a stored procedure to do the insert as well which takes all the things you want to insert into your various tables as parameters.sql

Different result in SP vs Query Analyzer

I'm getting some very odd behaviour which I can currently
duplicate. One of my SPs fails when it can't find a
record. I copy the SQL from the SP and execute it via
query analyzer and ta-da, the record appears.
Further investigation shows that the sql checks that a
record doesn't have a "completed" status. Comment this
out and the SP works fine.
Continuing the investigation, the developers run this SP
through debug and find the value being returned
= 'COMPLETED'. Run the *same* code in query analyzer and
the value being returned is 'PLANNED'... How is this
possible' The sql returns the correct value when run
through query analyzer, but the incorrect value when run
as a part of the SP!!
Below is the 'where' clause:
.
.
.
Where
(vtt.Trip_Id = @.FCTripId)
And (eqm.eqm_sequence_no = 2)
And (cst.cst_consign_status_desc <> 'COMPLETED')
TIA,
SJT> I'm getting some very odd behaviour which I can currently
> duplicate.
Can you give us enough information so we can try to duplicate? Table
schema, sample data, the code for the procedure maybe, desired results. See
http://www.aspfaq.com/5006
> Further investigation shows that the sql checks that a
> record doesn't have a "completed" status.
What datatype is this? How does sql "check"? What is the exact syntax you
are using? What data is actually stored in the column?
> Continuing the investigation, the developers run this SP
> through debug and find the value being returned
> = 'COMPLETED'. Run the *same* code in query analyzer and
> the value being returned is 'PLANNED'... How is this
> possible'
You're looking at a different row? The stored procedure is being executed
against the test database, and query analyzer is connected to production?
> And (cst.cst_consign_status_desc <> 'COMPLETED')
I'm going to guess that (shudder) "cst_consign_status_desc" is a CHAR
column. You should use VARCHAR so that trailing spaces are ignored, and
ensure that ANSI_PADDING is set the same in both environments to ensure that
you get consistent results.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Different images based on condition...

I was wondering if there is a way to display a particular image into a table
record based on a condition. For example, I have a list of questions that
come in 3 different types, each type has a different image associated with
it, so it would look like this:
<Image1.bmp> First Question (type is 1)
<Image3.bmp> Second Question (type is 3)
<Image3.bmp> Third Question (type is 3)
<Image2.bmp> Forth Question (type is 2)
<Image1.bmp> Fifth Question (type is 1)
...and so forth.
So basically what I am wanting to do is (at the record level) display the
different images based on what type of question I want. But, I can't seem
to figure out how to insert three different images into one cell and supress
two of them based on the 'question_type'. Which was how I accomplished it
in crystal reports.
I think I could probably do this by storing the images in a column in the
database, then call that with my stored procedure. Although, I am afraid
that this would take up tons of space in the database and take forever for
the report to run.
Any thoughts? Thanks!
LisaThe sample report at the end of this posting shows how to conditionally
display an image.
This report conditionally displays an image based on a detail row value. In
this case Germany = Green, Purple = USA, and Canada = Red. The technique is
to place a rectangle in the table detail row and place all images at the
same location in the rectangle. The images should be the same size. Next is
to use an expression to set the image visibility:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true). False = Not
hidden and True = Hidden.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:O4L73T9eEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I was wondering if there is a way to display a particular image into a
table
> record based on a condition. For example, I have a list of questions that
> come in 3 different types, each type has a different image associated with
> it, so it would look like this:
> <Image1.bmp> First Question (type is 1)
> <Image3.bmp> Second Question (type is 3)
> <Image3.bmp> Third Question (type is 3)
> <Image2.bmp> Forth Question (type is 2)
> <Image1.bmp> Fifth Question (type is 1)
> ...and so forth.
> So basically what I am wanting to do is (at the record level) display the
> different images based on what type of question I want. But, I can't seem
> to figure out how to insert three different images into one cell and
supress
> two of them based on the 'question_type'. Which was how I accomplished it
> in crystal reports.
> I think I could probably do this by storing the images in a column in the
> database, then call that with my stored procedure. Although, I am afraid
> that this would take up tons of space in the database and take forever for
> the report to run.
> Any thoughts? Thanks!
> Lisa
>
>
ConditionallyDisplayAnImage.RDL
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.625in</Height>
<Width>6.375in</Width>
<CanGrow>true</CanGrow>
<Value>This report conditionally displays an image based on a detail
row value. In this case Germany = Green, USA = Purple, and Canada = Red. The
technique is to all images in a rectangle at the using the same origin and
size. Next is to place the rectangle in the table detail row cell. Finally,
is to set each images initial visibility using an expression:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true).</Value>
</Textbox>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<ZIndex>2</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Germany",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greenbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image2">
<ZIndex>1</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "USA",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greybullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image3">
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Canada",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>redbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
</ReportItems>
<Style />
</Rectangle>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.75in</Top>
<Width>2.375in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>0.20833in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.625in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>69fa60f9-f434-44ed-a70d-2fc41b5d0cb3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Country
FROM Customers
WHERE (Country = N'Germany') OR
(Country = N'USA') OR
(Country = N'Canada')</CommandText>
</Query>
</DataSet>
</DataSets>
<EmbeddedImages>
<EmbeddedImage Name="greenbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFISoIKDMKLjoLLzUbMT0MMzscNUMNO0oPO0UePUIvRFURU101TWAVU2gVW3EYYHcdZ
XskT1NDYGNYcnlcdXhtbogeaoIidY0te5UufIZfgZ0shZ82h6cojawuh6Mzi6c4lLUyiJpSlK5En
r1EkqFlmKN2nqp4pcc+psJTrMlVsc5YuNtPvt9ZwONYyetfyOZv0O9y1vR12vd33vt05P19hoiBj
5ODoKKYpamZrrSbrK2rs7Wstrmt9v+U//+Z////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAadF2SAAAAMFJREFUKFNNj9cagjAUgxFQtLZQlSWIRVCR4QQXMt7/
rURo1XOTk//LRcLV9J6r7SanP9dpQRzXWWr40boOPhdBvI8DoqHXFxYkOGRZuvctDO4suXaStKrK
c0h0OGPQcJOsgZeI6GhM4Ut3o0tZZsfQxlBkScXyk9P5GHmGDHoMYm3ph+HOMzGS+gzOZc20bcvA
CPDqtydACtaxDIEofFZ15W8D2ByQeOH6W1TnQ3Eg8tyoW0+313WuTqZt7B9S38ob8DI2JkkuGrgA
AAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="greybullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFOC04XkxeemV6pYmlyazJ9tn2//X/////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAABATwmQAAAHRJREFUKFNNz1kSwCAIA1CWqPe/cROgrflR3yCinQky
E7O3XkWZEc2NSGABGV5aSFtMab7IbmvvvYTxYgC0Rh9EXJXqVz2JasrC8B+lfIa37cLoMd3te+i4
h0IzDdrDp7liZhpz8IBn5f6mfsXLVfZXzmmWB4hABapTVcWWAAAAAElFTkSuQmCC</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="redbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFAAAAKgAIMwAKOgALPQAMNRUbNhUcOxUcAEBAQQANRgAOSgAPTAAPRRUeUwESVQARW
QESXQMVQiovXSo1YQIVYgUXZQIVZAQXaQEWaAYZcQIYdwYdcQgdeAIZfAQcegwiew4kfQsifg0jf
A0kdxYpexYqQABAQEAAQEBAU0BDY1VYeVVceGptAAD/AP8AAICAAMDAAP//gQMciAQeggoihwoji
gYhjRcunw8skBYulhQunBEtkBgwlhs0mR01nhw2ogwqpwgorAsrrA8uoxczpBs3px05qBMxsRc2t
RIytRY2oCA5pS9GripEvSVEuChFhlVfmkBSoVZlo2t2qmx4xxs+/wAAyR5AxSVFxDdTwjhTzDVTy
ThVyTtYzjtY2yxP0TFR2DBR3DBS3zdZ4zZY6ztfzkBd3EBf5EFh5lJv6FJw71Ny9VV28ll49lt6+
lF0/FJ2/Vt9/lx/gACAwADA/wD//2qJ/2yN/26S/3OU/3SV/3aZ/3+hgIAAwMAA//8AgICAiICBk
4CDopWYqZWZrpWatJWbraqrtaqsuaqtvKuu////////wMDA///D////////wMDA////AAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAeTG9CwAAAM1JREFUKFNj6IaCtsCQ4FYomwFCd/k4eTo7GKu2gHkQ
wTbH0PiUxAh3M4kOuGCna3hqQVF+hp+1HH8zTGWQc1J+RXV5TrSbiYg2TNDGK7mwuqYqL87NREwY
Kthh5JGQU15ZlhVpryjCAlMpa+GXnp2bmeZrKSPABBNUMXPxj4mN8rVVFudhhwkGSJla2XvbmStI
8jHrwN3JLyGtqCQvI8rHwtYOF2ziEhAREeTnZmZtRPiou52XhZOTmVEIpA7mTSCjXUdTC6wMWRDK
B1MA/Td3eObvA7wAAAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
</EmbeddedImages>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>e66e2b26-0660-4802-920d-211083d62c86</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

differences in record counts

Hello all,

I have a problem concerning differences in record counts between the measure group and the fact table.
for debugging such a case in Analysis Services 2000, I would have run process on the cube, copy the SQL statement that was generated and debug it using Query Analyser. in sql 2005, I tried doing that but got the sql statement without the joins to the dimensions so no much help in that...

does anyone have a suggestion on how to debug such a case?

Thanks,

Momo

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

|||

Bryan C. Smith wrote:

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

So can anyone explain why the poster is observing dropped rows?

In my case I am finding the cubes are not showing any financials. Fact tables are populated correctly. Dimensions appear to be joined correctly - but I understand this wouldnt matter anyway?

Is there a way to see how the cube process is getting its data? Is there a way to check the integrity of a data source view? What is the consequence of incorrectly joining tables in your data source view?

Debugging in AS 2000 seemed much easier!

|||

When you process the cube, you have access to the queries submitted. Take a look at those and test the row counts for each.

B.,

differences in record counts

Hello all,

I have a problem concerning differences in record counts between the measure group and the fact table.
for debugging such a case in Analysis Services 2000, I would have run process on the cube, copy the SQL statement that was generated and debug it using Query Analyser. in sql 2005, I tried doing that but got the sql statement without the joins to the dimensions so no much help in that...

does anyone have a suggestion on how to debug such a case?

Thanks,

Momo

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

|||

Bryan C. Smith wrote:

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

So can anyone explain why the poster is observing dropped rows?

In my case I am finding the cubes are not showing any financials. Fact tables are populated correctly. Dimensions appear to be joined correctly - but I understand this wouldnt matter anyway?

Is there a way to see how the cube process is getting its data? Is there a way to check the integrity of a data source view? What is the consequence of incorrectly joining tables in your data source view?

Debugging in AS 2000 seemed much easier!

|||

When you process the cube, you have access to the queries submitted. Take a look at those and test the row counts for each.

B.,