Creating a table with a dual primary key

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!Does it matter that the destination needs to start at 1 and be sequential? If not, just use the Identity value. Probably too simple.

If you do it in a cursor, you could use a counter and move each record one at a time and use your counter to create the line number.

You could create the line number field in your source and pre-populate it with a process to loop through and assign the line numbers just prior to dumping it to the destination.

Doing it within one SQL statement to move it from one to the other might be impossible.|||Yes, you can create primary key that consists of two columns, or composite primary key.
It is not possible to create dual promary key in SQL server. You may create multiple uniqe index/constraints. But for one talbe, there is only one primary key.|||It is not quite clear what you exactly want to do.

Do you want to add a new column for an existing table as a primary key or is it a whole new table?|||I think we are on the right track (sort of).

I my example, the Order ID's are already assigned (they basically use an Identity Key).

What I need is some sort of query (or activeX script) that will loop through each record, and assign the Order_Line_ID (By the way, I don't care how many SQL statements this takes, as long as it works).

I am querying the Order_Line table, and sorting it by the Order#, so the table will look like this at first:

LineID-Identity | Order#
1 | 12345
2 | 12345
3 | 12345
4 | 12346
5 | 12347
6 | 12347
7 | 12348

The idea is, an order may have multiple lines (you've probably ordered more than one item at a time from some site online)

The counter needs to start over at 1 when it comes to a new order. For example, if the first three records in the Order_Line table are from Order# 12345, the first three rows would be numbered 1, 2 and 3, respectively (that should make sense). Now the tricky part: If the next Order # is 12346, the counter would start at 1 again, like this.

LineID-Identity | Order# | OrderLine#
1 | 12345 | 1
2 | 12345 | 2
3 | 12345 | 3
4 | 12346 | 1
5 | 12347 | 1
6 | 12347 | 2
7 | 12348 | 1

As you can see, this is a new column, so no data is being removed. How do you do this?|||Within SQL, use a cursor and loop through a sorted list of the line items. Use a "current" and "previous" variable to hold the order ID and a counter to hold the new line number. Create the field (null at first), loop through the cursor and using the logic of comparing the last order ID to the current one, assign the line number from the counter, increment the counter, etc. Look up the cursor options you'll need to use to make the cursor editable.

Creating a Report Model - any doc other than Books Online?

Is there any documentation available on building a report model other than
the SQL Books Online? The subject is usually given about 3-5 pages max in
any Reporting Services book. I'd like to see a whole book on the subject,
with lots of examples.
--
LehrSJOn Sep 12, 6:18 pm, LehrSJ <lehr...@.noemail.nospam> wrote:
> Is there any documentation available on building a report model other than
> the SQL Books Online? The subject is usually given about 3-5 pages max in
> any Reporting Services book. I'd like to see a whole book on the subject,
> with lots of examples.
> --
> LehrSJ
This link might be helpful.
http://www.databasejournal.com/features/mssql/article.php/10894_3598931_1
Regards,
Enrique Martinez
Sr. Software Consultant

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a drill-through report

I'm building my report using the Report Designer in the SQL Server Business Intelligence Development Studio. At the moment I've got a single report which consists of 2 tables. The top table shows some summary data and the bottom table shows more detail.

What I'd like to do is design the report so that when the summary data (in the top table) appears, the user can click on a row in the table and this takes them to a new screen which shows more detail about that item, extracted from the bottom table.

Is this possible? I know it's possible to create a drill-through report if you have the high-level summary data in one report and the low-level detailed data in a separate report, but is it possible to create a drill-through report if all the data is in one single report (albeit in 2 separate tables)?

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

|||

??€?§Q? wrote:

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

The bottom table doesn't contain the same number of rows as the top one. The top table has one row for each member of staff, which consists summary data about how many pieces of work they have completed. What I want is for the user to be able to click on the name of a member of staff, and then they will be taken to a separate screen showing the full details for that particular staff-member (this data should be taken from what is currently the bottom table in the report). The bottom table contains one row for each piece of work that a particular staff-member has completed, so the number of rows would vary depending on whose name has been selected.

I hope this makes sense; it's always hard to explain what you're trying to do on a forum.

|||

Ok, so why not just create a drilldown report and enable navigation on the top report to that drilldown?

Why does the bottom table play into this at all?

|||

You could have a hidden parameter in your report got StaffID. Setup Visibility on the bottom table to hide if staffid is null, and show when it isnt. Do the opposite with the top table, if is hidden if staffid is not null, and visible if it is null.

Setup the navigation on the top table to go to the same report, passing the StaffID that was clicked on.

Since StaffID will not be null, the report will hide the top and only show the bottom.

Now, all that being said, the better way to do that is to have 2 reports, one for the list of staff and one for the details.

HtH

BobP

|||

And to open the report in the new window, you will have to use javascript in the Navigation window.

Select Jump to URL and add this code:

="javascript:void window.open('http://server/reportserver/pages/reportviewer.aspx?%2fFOLDER%2fREPORT&StaffID=" & Fields!StqaffID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

BobP

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.