Datamodel behind System Tables

L.S.,
How can I find out what the datamodel is behind the System Tables
(SysColumns, SysObjects, SysDatabases, etc.). I once came across a question
about finding out what the default value for a certain column in a certain
table was. There was talk about doing some heavy parsing of the result set
after using stored procedure sp_help(text). And even then it was not sure
that the desired result would be achieved, they said, leaving one to the
choice of looking up the actual SQL code.
However, after looking up some documentation on the System Tables and
guessing from there on where I might find the desired information, I found
out that one may find the default value for a certain column in a certain
table (or any other object for that matter) in the SysComments table. It
would have been a lot easier to find this out if I had had a datamodel of
those System Tables. Now, before using ER Studio, I was wondering if this
datamodel exists and if so, if it could be shared with the community at large.
Many thanks in advance,
Wilfred Damhuis
P.S.: replies may be send to wdyttg@.rubycon.demon.nl
Does this help you:
http://www.microsoft.com/sql/techinf.../systables.asp
"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> L.S.,
> How can I find out what the datamodel is behind the System Tables
> (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
question
> about finding out what the default value for a certain column in a certain
> table was. There was talk about doing some heavy parsing of the result set
> after using stored procedure sp_help(text). And even then it was not sure
> that the desired result would be achieved, they said, leaving one to the
> choice of looking up the actual SQL code.
> However, after looking up some documentation on the System Tables and
> guessing from there on where I might find the desired information, I found
> out that one may find the default value for a certain column in a certain
> table (or any other object for that matter) in the SysComments table. It
> would have been a lot easier to find this out if I had had a datamodel of
> those System Tables. Now, before using ER Studio, I was wondering if this
> datamodel exists and if so, if it could be shared with the community at
large.
> Many thanks in advance,
> Wilfred Damhuis
> P.S.: replies may be send to wdyttg@.rubycon.demon.nl
|||Adam,
thanks, not only for your swift response, but also for the indeed very
helpfull info. But is it just me or is this little known?
Regards,
Wilfred Dmahuis
"Adam Machanic" wrote:

> Does this help you:
> http://www.microsoft.com/sql/techinf.../systables.asp
>
> "Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
> news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> question
> large.
>
>
|||"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:EE0D57A7-EF61-4856-99B1-66EFEA8CFD0B@.microsoft.com...
> helpfull info. But is it just me or is this little known?
I have no idea how well known it is (according to the text in the link,
it's "very popular") but I only discovered it last week

Datamodel behind System Tables

L.S.,
How can I find out what the datamodel is behind the System Tables
(SysColumns, SysObjects, SysDatabases, etc.). I once came across a question
about finding out what the default value for a certain column in a certain
table was. There was talk about doing some heavy parsing of the result set
after using stored procedure sp_help(text). And even then it was not sure
that the desired result would be achieved, they said, leaving one to the
choice of looking up the actual SQL code.
However, after looking up some documentation on the System Tables and
guessing from there on where I might find the desired information, I found
out that one may find the default value for a certain column in a certain
table (or any other object for that matter) in the SysComments table. It
would have been a lot easier to find this out if I had had a datamodel of
those System Tables. Now, before using ER Studio, I was wondering if this
datamodel exists and if so, if it could be shared with the community at large.
Many thanks in advance,
Wilfred Damhuis
P.S.: replies may be send to wdyttg@.rubycon.demon.nlDoes this help you:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> L.S.,
> How can I find out what the datamodel is behind the System Tables
> (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
question
> about finding out what the default value for a certain column in a certain
> table was. There was talk about doing some heavy parsing of the result set
> after using stored procedure sp_help(text). And even then it was not sure
> that the desired result would be achieved, they said, leaving one to the
> choice of looking up the actual SQL code.
> However, after looking up some documentation on the System Tables and
> guessing from there on where I might find the desired information, I found
> out that one may find the default value for a certain column in a certain
> table (or any other object for that matter) in the SysComments table. It
> would have been a lot easier to find this out if I had had a datamodel of
> those System Tables. Now, before using ER Studio, I was wondering if this
> datamodel exists and if so, if it could be shared with the community at
large.
> Many thanks in advance,
> Wilfred Damhuis
> P.S.: replies may be send to wdyttg@.rubycon.demon.nl|||Adam,
thanks, not only for your swift response, but also for the indeed very
helpfull info. But is it just me or is this little known?
Regards,
Wilfred Dmahuis
"Adam Machanic" wrote:
> Does this help you:
> http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
>
> "Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
> news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> > L.S.,
> >
> > How can I find out what the datamodel is behind the System Tables
> > (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
> question
> > about finding out what the default value for a certain column in a certain
> > table was. There was talk about doing some heavy parsing of the result set
> > after using stored procedure sp_help(text). And even then it was not sure
> > that the desired result would be achieved, they said, leaving one to the
> > choice of looking up the actual SQL code.
> >
> > However, after looking up some documentation on the System Tables and
> > guessing from there on where I might find the desired information, I found
> > out that one may find the default value for a certain column in a certain
> > table (or any other object for that matter) in the SysComments table. It
> > would have been a lot easier to find this out if I had had a datamodel of
> > those System Tables. Now, before using ER Studio, I was wondering if this
> > datamodel exists and if so, if it could be shared with the community at
> large.
> >
> > Many thanks in advance,
> >
> > Wilfred Damhuis
> >
> > P.S.: replies may be send to wdyttg@.rubycon.demon.nl
>
>|||"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:EE0D57A7-EF61-4856-99B1-66EFEA8CFD0B@.microsoft.com...
> helpfull info. But is it just me or is this little known?
I have no idea how well known it is (according to the text in the link,
it's "very popular") but I only discovered it last week :)

Dataflow with Workflow

I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.

I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.

Is this achievable?

Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?

Does anyone have a better solution?

I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!

Philip Coupar

Unfortunately, setting execution precendences in between branches in the data flow is not supported today. Until we redesign the engine to support this feature, I would consider other possibilities to solve the problem, such as using a combination of task flow and data flow.

|||A simple strategy is to use raw files. Stage the updates in a raw file and use a second Data Flow Task to load them and run the updates. Raw Files are very efficient.|||

Philip Coupar wrote:

I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.

I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.

Is this achievable?

Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?

Does anyone have a better solution?

I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!

Philip Coupar

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

|||

Raw files may well be very efficient, and space may be cheap but I already have a stage table with 4 million transaction rows which breaks out to 2 million start events and 2 million end events. I don't really want to read 4 million rows from a set of text files into a SQL Server table to read back out all the rows while applying some transformation logic to it, to split on one of the transformed columns to put half the data back out to a file, so that at some later point I can pick up that file again and update my 2 million start events with their end time.

That is just the incremental data, to rebuild the entire model would involve reprocessing 250 Million transaction rows, the pipeline uses buffers to manage the number of rows in memory, the raw file in this case would require 125 Million rows to be written out. I know I could batch up the rebuild into 4 Million rows sets, but I quite like the "Read Once, Transform Many, Write Once" mentality.

|||

Jamie Thomson wrote:

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

That looks great, I hope anyone else following this tread will have a look and vote. My only additional request to your proposal would be to include the OLEDB Command Component as well as the destinations so that precendence could be specified over inserts and updates.

As well as the scenario above we have a number of data feeds which have data columns split across multiple source files and not all sets of file contain distinct data sets, sometime a file can contain data items that relate to data from a previous set of files. This introduces exactly the same scenario where I would like to insert all new rows and after that use other data in the data flow to update these or existing rows.

|||

Philip Coupar wrote:

Jamie Thomson wrote:

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

That looks great, I hope anyone else following this tread will have a look and vote. My only additional request to your proposal would be to include the OLEDB Command Component as well as the destinations so that precendence could be specified over inserts and updates.

Agree with that totally.

Note to anyone else. Don't just vote - that is useless. Add a use case for why you want this functionality (as Phil has done).

Philip Coupar wrote:

As well as the scenario above we have a number of data feeds which have data columns split across multiple source files and not all sets of file contain distinct data sets, sometime a file can contain data items that relate to data from a previous set of files. This introduces exactly the same scenario where I would like to insert all new rows and after that use other data in the data flow to update these or existing rows.

Hmmm...the scenario I have in mind is that you either insert a row or update it. But I guess there is no reason why this "Intrinsic Flow Priority" feature couldn't be used to both insert a record and then update it. Interesting....

One more thing Phil, can you make sure you add all the details from your use case on this thread to the connect posting? They'll take more notice of it if you give them more reasons to.

Ta

Jamie

dataflow error

This error seem to be very silly.did anyone come across this error.

I have been transferring data from textfile to a table using oledb destination.

The number of records in the text file are 2,091,650

Its was running just fine couple of days ago when the incoming data was little small then this...(arround 300,000).Now it seem to have a problem.

Here is the flow

1.File System task ->I copy the file to different location

2.Execute sql task->truncate tables

3.DataFlow task->I check for only the error files in this data flow.and all valid rows i transfer to a different text file.

4.Dataflow->filesource i connect to new text file created earlier.Here i convert fields to repective datatype and i insert if new or update record.

I dont know whats going on...

When i run my package it runs through the first three perfectly fine.When it comes to fourth step it sits there.....it dosent go to the tasks within this dataflow at all...and begining i have flat files source...

What could be the reason...

when i look at progress tab...i was able to look at the progress of other tasks but when it comes to this task it shows start>>>>>time and it sits there...

Are you using the same file connection manager for the "new" file created in step 3 and used in step 4? If so, try creating a second file connection manager for the file and use that connection manager in step 4.|||Do you have step three hooked up to step four via a precedence constraint?|||

I checked it out using a same file connection and different connection manager as well...but it didnt work...seems to really silly problem....

And all my flows are perfectly connected with precedence constraint as well.

Then only change which was made to this package was increase in volumn of data.

How can i fix this?Is there any other solution?

I also checked executing the last dataflow task alone as the file had data in it.

But it sits there and dosent proceed.

|||

Suresh,

could you troubleshoot this by making a simple package to only process your flat file? If it fails, try cutting the file in pieces to find out if it has some formatting errors.

HTH.

DataBinding: System.Data.DataRowView does not contain a property with the name

Here's a new one I came across that stumped me for a bit.

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xxxx'.

It appears that one of my dropdown menus was referencing the old SqlDataSource1for its data source id.

I had already changed the name of the of the id to something else though.

To fix it, I just updated the reference to the new name.

Thought I'd share, just in case

Hi eterry,

Is this issue already resolved?

|||Yes it is. Sorry about that.