how to bypass report size limitation

I want to build a super long report that is organized by a train of
rectangles, and one rectangle follows another. Each rectangle has a page
break at end, so the report can be rendered in many pages, one rectangle
falls in one page. The problem is the size limit of a report is 160 inches.
That means, if I make 8.5 by 11 inches a rectangle, I can have only 14.5
pages in maximum.
How can I overcome this issue?I also have the same issue. Have you found a way around the size limit of 160
inches?
"how to bypass report size limitation" wrote:
> I want to build a super long report that is organized by a train of
> rectangles, and one rectangle follows another. Each rectangle has a page
> break at end, so the report can be rendered in many pages, one rectangle
> falls in one page. The problem is the size limit of a report is 160 inches.
> That means, if I make 8.5 by 11 inches a rectangle, I can have only 14.5
> pages in maximum.
> How can I overcome this issue?

How to build this query

Hi,
Please Help me to build this query.

I have got a "User" Table
-------
UserID UserName
-------

1 Tuffy

Another Table "Groups" Table
-------
GroupID GroupName
-------
1 Manager
2 Employee
3 Sales

I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this

-------
UserID GroupID
-------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
-------

Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"

So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"

Please help me how to write this query.

RegardsTo do this you can do it as -

declare @.roles varchar(100)

SET @.roles = ''

SELECT @.roles = @.roles + '|' + GroupName
FROM UserGroup JOIN GROUPS ON UserGroup.GroupID = Groups.GroupID
Where UserID = @.userID --PAssed userID to the stored procedure

SET @.roles = @.roles + '|'

SELECT @.roles|||...although this will give you a starting and ending pipe.

You might also try:

declare @.roles varchar(200)
SELECT @.roles = COALESCE('|'+G.Groupname, G.GroupName)
FROM UserGroup UG
INNER JOIN GROUPS G ON UG.GroupID = G.GroupID
WHERE UG.UserID = @.userID
SELECT @.Roles

...or simply create @.roles as an output parameter and you won't need to perform that final SELECT.|||Thanks a lot guys. I really appreciate this.
Is there any way of getting the UserID AS WELL

The select query should return

UserID, Groups(Pipe seperated)

The Example I mentioned above, according to that example, the return should be

UserID, Groups
---------
1, Manager|Employee|Sales|
----------------

Many, Many Thanks and Regards|||pjmcb -

How are you going to get one row with all the groupnames with your SQL? I tried it and it just gets the first record.

Am I missing something here?

Thanks,

AP|||No, it was I who was missing something.

Try:

declare @.roles varchar(200)
SELECT @.roles = COALESCE(@.roles+'|'+G.Groupname, G.GroupName)
FROM UserGroup UG
INNER JOIN GROUPS G ON UG.GroupID = G.GroupID
WHERE UG.UserID = @.userID
SELECT @.Roles

With the big difference being in the COALESCE parameter:
COALESCE(@.roles+'|'+G.Groupname, G.GroupName)
instead of
COALESCE('|'+G.Groupname, G.GroupName).

Sorry. My mistake, Was typing quickly. I still haven't tested it yet, but this should work for you...

How to build this expression?

Greetings friends,

I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail.

case
when f.etypeid < 10 then '000' + cast(f.etypeid as varchar)
when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar)
else
'0' + cast(f.etypeid as varchar)
end

Many thanks for your help in advance.

Hi again guys,

Finally I managed to work it out. Silly me!

The solution to the above is as follows :

etypeid < 10 ? "000" + (dt_str,1,1252)etypeid : etypeid > 10 && etypeid < 100 ? "00" + (dt_str,2,1252)etypeid : "0" + (dt_str,3,1252)etypeid

Sorry for the bother SSIS friends

|||Mark your post as an answer, please.

how to build such a report with include unkown amount of subreports?

I have spent much time but still have no idea.
I need to dynamic create the report.especially the report (A) is made up of unknown amount of one composite report(B,which include serveral subreports).
An idea is to place subreports of B directly in A.

And the subreports include some tables(not the real table in database) which columns is not fixed.|||Put the SubReport under a Group instead of under a Page header, that way you will get a subreport each time you change groups.|||Would you please explain more detail?
And the other problem is that some tables(not the real table in database) which columns is not fixed.|||I'll try to explain in more detail, but I need to know more about what you're trying to do. What version of Crystal are you using, how are you building the report (using rpt files or RDC, etc...). Give me as much details as you can about what you have already done, what's not working, and what you're trying to do.|||My crystal reports' version is 9.2.

I dont mind using RDC or RPT.

For example there are 4 database tables name (The order information of garment)OrderStyle,ColorGroup,ClothSizeCode,OrderDetails
the sub reports is
sub report A
--------------
OrderCode | StyleCode | StyleName//In OrderStyle
--------------

sub report B
-------------------
|ColorGroupCode | S | M | L | XL | XXL | ...| Summary
|-----------------
|001 |1 |5 |1 | 5 | 9 | ...|21
|002 |1 |6 |2 | 6 |1 | ...|16
|.....................................................
-------------------
|summary |2 |11|3 |11 |10 | ...|37
------------------

The S,M,L,XL,XXL are come from table ClothSizeCode and which size is selected is decided by customer. I think that to use crosstab may be a good idea.
My problem is that how to fixed these 2 subreports in one report.
In power builder it is simple but the efficiency is so poor that it may take 2 minutes to get such a report with only 6 subreport A and 6 subreport B with a few rows.|||OK, here's my guess, tell me if it's close to what you need...

Group Header #1: Customer

... SubReport1:

... ... OrderCode | StyleCode | StyleName//In OrderStyle

... SubReport2:

... ... CrossTab with ClothSizeCode as column, ColorGroupCode as row, If you choose to display Totals, it will automatically summarize it for you

Group Footer #1

Remember that each Subreport is like a whole separate report, it doesn't have to be based on the exact same criteria as your main report. Put both SubReport1 and SubReport2 in the Group Header #1 section.

How to build SQL Commands in a Remote Database Component

Hello,

I'm trying to develop a remote database component for the server that interacts with the database directly. I would like to pass in my object to the corresponding add, update, delete methods and have the database component generate the script to do the corresponding transaction to the database. However, I cannot find a viable solution yet. The only method that I have found so far is passing the exact SQL command to the database component for it to execute. This would require making a 3 SQL Statement (one for add, update, and delete) for each class.

Thanks,

Kiet Quach

I would back up a bit here. I think your approach is flawed.

your objects should be MarshalByValue/serializable. the object should be passed across the wire to a service, the object then would process itself as it should know how to manipulate its own contents.

I highly recommend Lhotka's Expert C# business objects

"object-oriented" means orient to the object.

What you are proposing is a "service-oriented" data access solution and I wouldn't recommend that for data processing. On the other hand, A hybrid achitecture - a solution that services self contained data objects object is quite appropriate.

|||Thanks Blair for the input. However, there is a reason why I wanted to only pass the object, I want to maintain database independence since my customers use a different database platform. Therefore, it would be a lot more scalable if I could create the SQL commands in the database component.

Kiet Quach|||thats what I am saying. . . read lhotka!

how to build share dimension

in a project ,i built two cube,each cube need time dimension,i want the time dimension be a share dimension.how to do it?

What version of Analysis Services are you using?

In AS 2005 open your project in BI Dev Studio. Open your cube. Right click in the dimensions tab and select "Add cube dimensions".

Make sure you go into Dimension usage tab later and specify how your dimension is assosiated with the measure group.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to build search at website?

Hi! Maybe Iask simple question. But for me it's not so. The matter's I plan organizesearch at my website. It has catalog of firm's and good's in SQL Server. I planto build dictionary table with two fields' search_item and link. But problem isthat I should put all search variant's in field search_item(name of firm, nameof boss, name of good etc.) Maybe there's another concept to build search?Thank's for any answer.

Andrey.

You could take a look at FREETEXT in SQL Server. Also SQL 2005 has some % "closeness" matching (sorry I can't remember its name off the top of my head).|||

Thank you. It'suseful for me. But I'm interesting more in concepts how to build search in mycase.
Andrey.

|||

There is a recent post where someone is trying to do something kinda similiar, it's titled "No result from stored procedure". It's a stored procedure that basically allows that person to search almost any field in their row of things and get back what they want.

Unless you really want to build a search engine, where you put in a word, and it comes back with all kinds of stuff you really didn't want (Like most search engines). The above approach is better in many cases where you know what it is you want to search (Like title, author, price), rather than search everything for a specific word.

|||I would say it depends on how and what you're going to search. If you've got a limited number of fields then going along the lines you've suggested is probably going to be ok, although it can soon get messy when you consider OR or AND, sounds-like, wildcards etc. I know in the past I've used XML to form up the search condition. That way you can create a nice generic search engine, save you favourite searches, etc. In SQL you then process the SQL and support the different types of search logic.

how to build properly

Hi,

I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:

Package 1 has been modified outside the source editor. Do you want to reload it?

When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.

What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?

Thanks!

cherrie

Yes, reloading means loading the version from outside VS - and that will not be the version you are editing in VS, so you lose changes.

I wonder if you have more than copy open? Or do you have some other process such as backup or offline file synchronization which may be touching the files while they are open?

You can work round the problem by not reloading the file when you get this message.

Donald

|||

Ic. Thanks a lot!

cherrie

How to build my SQL querry.

Hi.
I know this querry:

"SELECT TOP 10 * FROM MyTable ORDERBY ..."

will give the top 10 rows.

But how can I build a querry that can get the rows from 40-50 (ie).
Thanks.Try below code, it could helps you!

select top 10 * from (Select distinct top 50 * From TBLNAME order by Fieldname desc) a order by Fieldname asc|||This is a good example

http://www.4guysfromrolla.com/webtech/062899-1.shtml

How to build good Data Warehouse Structure

I'm new to OLAP, and just tried build OLAP & Data Warehousing using
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advance
There is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegr oups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>

How to build good Data Warehouse Structure

I'm new to OLAP, and just tried build OLAP & Data Warehousing using
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advanceThere is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegroups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>

How to build FROM clause dynamically

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.A SQL Server stored procedure is a poor option for giving users ad-hoc query capability. You would need to write a routine that parsed their input statement (very difficult considering that users have little understanding of relational databases) and then would create a logic execution plan from the statement given known relationships among tables.

Wait a minute...that's what Query Analyzer does! Why not just allow the user to submit adhoc query statements? (Make sure your security is tight and you have a query governor active!)

Either that, or check into some of the data-mining software packages such as DI Diver or Cognos.

Where do you live in Melbourne? I spent two years as a kid in Box Hill.|||Thaks for your reply,

but I am not writing the store procedure and I do not have an option using any packages, like Cognos, Crystal etc. I am writing the procedure in VB (it is not up to me). And the main idea, that uses SHOULD NOT HAVE any idea about relational database. Uses just say: I want SQL Server database(could be others - like Oracle, Sybase or MS Access), I supply them names of available servers, they choose the server, I supply names of availabe databases, then tables, then fields, they choose whatever the want, then they point at the related fields of the tables they chose before, and "magic" happened - they have a report. I've done almost everything, just bits and pieces left. AND a FROM clause! It works for simple queries, but for more complicated it works sometimes, which obviousy is not good enough. So I nee an algorithm and stuck with that.

I live at Moorabbin and I know Box Hill - very nice area. Where do you live now?|||*ack* the whole joining thing is the problem...

I have tried this before... basically unless you can query the db to find out what the foreign keys are you are kinda stuffed...

Otherwise the users have to know enough about the database to be able to define the relationships themseleves...|||it sounds to me that the best solution for your problem
which is:
dynamic sql statements
ad hoc queries
no sql knowledge at the end user

you are an excellent candidate for ENGLISH QUERY
There are sample apps available for this product
code samples and etc.

English Query (http://www.microsoft.com/sql/evaluation/features/english.asp)|||I think Ruprect's English Query suggestion is your best shot. The problem is that a user who does not know anything about relational database is more likely than not to get the WRONG ANSWER to a problem due to not understanding relational set manipulation. This is what DBAs and SQL developers are for.

Think about it. Basic SQL is not that complicated. If a user can't understand "Select columna, columnb from sometable where columnc = somevalue" they shouldn't be mucking about in a database anyway. I mean, the syntax is practically an English sentence anyway. Hey, how about a procedure that lets them submit it in Australian?

"Grab beer, prawns, lamington from cooler where label = 'Fosters'"

By the way, now I am back in the United States (Midwest), but I still remember my old address down under 25 years ago: 17 Simmons Street, Box Hill.|||if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think|||Depending on how many tables the users are going to want to access and how fluid the ad-hoc queries are, you could work this in two other ways - which aren't elegant but might be enough to serve your purpose:

Either, restrict the queries that the users can create by offering them a list of possible query options that you have already generated the SQL for, or create a table that holds the correct joining criteria for your tables so that you can select the required code.

These won't work if you do want it to be a completely open ended query tool - but I would have thought that if you're getting to the point of ad-hoc queries using OUTER JOINS, then your users will probably have the SQL knowledge already...

Just a thought...|||If you just want to give them slice, dice, and filter capability on defined recordsets. then consider a pivot table linked to a view from either a spreadsheet or a web page.|||Originally posted by r937
if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think

That is exactly what our company is trying to do and I am aware that is not easy task, but I have to do it somehow.|||in that case i would suggest investigating INFORMATION_SCHEMA views to see if you can create queries that can access the tables, column, and especially primary/foreign keys

i think with MS access you are up the creek, but i believe the other databases all support INFORMATION_SCHEMA

good luck and let us know how your project turns out

How to build dynamic Xquery

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.

How to build database to support user-specified entities and attributes?

I have a database that tracks players for children's sports clubs. I have
included representative DDL for this database at the end of this post.

A single instance of this database supports multiple clubs. I would like to
add support for letting each club define and store custom information about
arbitrary entities. Basically, allows the clubs to define custom entities
(i.e tables) and associated custom attributes (i.e. fields) that may be
related to existing tables (such as Player and FootballClub) or existing
entities. For instance, a club may define a PlayerAssessment entity that
records all player assessments.

To do this, I plan to support the following use case:
1. FootballClub admin creates a new entity and gives it a name and
description (Entity is only accessible to this FootballClub).
2. FootballClub admin indicates that the new entity has a M:1 relationship
with the Player table (this will add Player_ID as a FK attribute).
- {An entity may have no relationships.}
- {Relationships are also supported to other entities.}
3. FootballClub admin specifies the names and domain/types of any data
attributes (i.e. fields) of the entity.
- {An attribute's type may be constrained to a few allowable types like
Relationship, Integer, Float, Currency, Date, Time, DateTime, Name,
Description and Memo.}
4. System creates entity as specified.

A few constraints:
1. Any entity defined is "private" to the defining club. Other clubs aren't
aware of it although they may define custom entities of their own
with the same name and attributes. [Perhaps there is a way to share
definitions of identical entities?]
2. A club doesn't have to define any custom entities.

Ideas I've considered:
1. Generate DLL and create actual tables
- Restrict such customizations such that while admin is setting up entities,
no other user is allowed to use the system.
- Once entity definition is complete, generate an actual table using DLL.
Table and column names might be changed to enforce uniqueness/validity
constraints - this suggests a need for table/column name mapping.
- PROS: Easy to implement.
- CONS: Doesn't scale since only a limited number of tables can be created.
DDL on a live, shared system?. Scary!!
All users for all clubs will be locked out while entity is
created.

2. Generate DDL and create actual tables in secondary database(s)
- Same as above except that the user tables are created in secondary [,
shared] databases.
- PROS: Reassurance that DDL is never run on the "core" data
All users don't have to be locked out.
- CONS: Doesn't scale since only a limited number of tables can be created.
{ Unless I start creating additional databases too!. }
Still needs to DDL on a live, shared system.

Has anyone done anything similar?. Any ideas on how it might be done?. In
particular, is this possible without having to execute DDL on the live
database?

Kunle

=================== BEGIN DDL ===================
CREATE TABLE FootballClub (
Club_ID int IDENTITY,
Name char(80) NOT NULL,
Area char(4) NOT NULL,
League char(4) NOT NULL,
City char(30) NOT NULL,
PRIMARY KEY (Club_ID)
)
go

exec sp_primarykey FootballClub,
Club_ID
go

CREATE TABLE Player (
Player_ID int IDENTITY,
First_Name char(30) NOT NULL,
Initials char(30) NULL,
Last_Name char(30) NOT NULL,
Date_Of_Birth datetime NOT NULL,
Position char(4) NULL,
Club_ID int NULL,
PRIMARY KEY (Player_ID),
FOREIGN KEY (Club_ID)
REFERENCES FootballClub
)
go

exec sp_primarykey Player,
Player_ID
go

CREATE TABLE UserAccount (
User_ID int IDENTITY,
Club_ID int NOT NULL,
FullName char(80) NOT NULL,
Logon char(20) NOT NULL,
PWD_Hash char(60) NOT NULL,
PRIMARY KEY (User_ID, Club_ID),
FOREIGN KEY (Club_ID)
REFERENCES FootballClub
)
go

exec sp_primarykey UserAccount,
User_ID,
Club_ID
go

exec sp_foreignkey Player, FootballClub,
Club_ID
go

exec sp_foreignkey UserAccount, FootballClub,
Club_ID
go
=================== END DDL ===================First, I would limit capabilities to the following:
1. Custom player attributes.
2. Custom detail types with custom attributes.

Next, you can support multiple data types at the user interface level, but
don't try to do it with actual field types. Use 255 character text
(varchar(255) unless you're in Access).

Now, so support customization, let each club have their own label names for
each custom player attribute, and let them have their own detail type records
where each detail type defines the label names for the custom detail
attributes.

On Thu, 17 Mar 2005 15:42:28 +0000 (UTC), "Kunle Odutola"
<noemails@.replyToTheGroup.nospam.org> wrote:

>I have a database that tracks players for children's sports clubs. I have
>included representative DDL for this database at the end of this post.
>A single instance of this database supports multiple clubs. I would like to
>add support for letting each club define and store custom information about
>arbitrary entities. Basically, allows the clubs to define custom entities
>(i.e tables) and associated custom attributes (i.e. fields) that may be
>related to existing tables (such as Player and FootballClub) or existing
>entities. For instance, a club may define a PlayerAssessment entity that
>records all player assessments.
>To do this, I plan to support the following use case:
>1. FootballClub admin creates a new entity and gives it a name and
>description (Entity is only accessible to this FootballClub).
>2. FootballClub admin indicates that the new entity has a M:1 relationship
>with the Player table (this will add Player_ID as a FK attribute).
> - {An entity may have no relationships.}
> - {Relationships are also supported to other entities.}
>3. FootballClub admin specifies the names and domain/types of any data
>attributes (i.e. fields) of the entity.
> - {An attribute's type may be constrained to a few allowable types like
>Relationship, Integer, Float, Currency, Date, Time, DateTime, Name,
>Description and Memo.}
>4. System creates entity as specified.
>A few constraints:
>1. Any entity defined is "private" to the defining club. Other clubs aren't
>aware of it although they may define custom entities of their own
>with the same name and attributes. [Perhaps there is a way to share
>definitions of identical entities?]
>2. A club doesn't have to define any custom entities.
>Ideas I've considered:
>1. Generate DLL and create actual tables
>- Restrict such customizations such that while admin is setting up entities,
>no other user is allowed to use the system.
>- Once entity definition is complete, generate an actual table using DLL.
>Table and column names might be changed to enforce uniqueness/validity
>constraints - this suggests a need for table/column name mapping.
>- PROS: Easy to implement.
>- CONS: Doesn't scale since only a limited number of tables can be created.
> DDL on a live, shared system?. Scary!!
> All users for all clubs will be locked out while entity is
>created.
>2. Generate DDL and create actual tables in secondary database(s)
>- Same as above except that the user tables are created in secondary [,
>shared] databases.
>- PROS: Reassurance that DDL is never run on the "core" data
> All users don't have to be locked out.
>- CONS: Doesn't scale since only a limited number of tables can be created.
>{ Unless I start creating additional databases too!. }
> Still needs to DDL on a live, shared system.
>Has anyone done anything similar?. Any ideas on how it might be done?. In
>particular, is this possible without having to execute DDL on the live
>database?
>Kunle
>
>=================== BEGIN DDL ===================
>CREATE TABLE FootballClub (
> Club_ID int IDENTITY,
> Name char(80) NOT NULL,
> Area char(4) NOT NULL,
> League char(4) NOT NULL,
> City char(30) NOT NULL,
> PRIMARY KEY (Club_ID)
>)
>go
>exec sp_primarykey FootballClub,
> Club_ID
>go
>CREATE TABLE Player (
> Player_ID int IDENTITY,
> First_Name char(30) NOT NULL,
> Initials char(30) NULL,
> Last_Name char(30) NOT NULL,
> Date_Of_Birth datetime NOT NULL,
> Position char(4) NULL,
> Club_ID int NULL,
> PRIMARY KEY (Player_ID),
> FOREIGN KEY (Club_ID)
> REFERENCES FootballClub
>)
>go
>exec sp_primarykey Player,
> Player_ID
>go
>CREATE TABLE UserAccount (
> User_ID int IDENTITY,
> Club_ID int NOT NULL,
> FullName char(80) NOT NULL,
> Logon char(20) NOT NULL,
> PWD_Hash char(60) NOT NULL,
> PRIMARY KEY (User_ID, Club_ID),
> FOREIGN KEY (Club_ID)
> REFERENCES FootballClub
>)
>go
>exec sp_primarykey UserAccount,
> User_ID,
> Club_ID
>go
>exec sp_foreignkey Player, FootballClub,
> Club_ID
>go
>exec sp_foreignkey UserAccount, FootballClub,
> Club_ID
>go
>=================== END DDL ===================|||"Kunle Odutola" <noemails@.replyToTheGroup.nospam.org> wrote in message
news:d1c8h4$me4$1@.hercules.btinternet.com...
> I have a database that tracks players for children's sports clubs. I have
> included representative DDL for this database at the end of this post.

This is a breeze using Ingres. Create a user for each club. That user will
own the club's supplimentary tables. Create your core tables in the DBA
schema so they are visible to all users, and grant them to all relevant
users. Then allow the user who owns each club's tables to create the
supplimentary tables in their schema (this is allowed by default). That
user can then grant those tables to whichever other users he/she likes.
Because the supplimentary tables are in separate schemas for each club there
can be no naming collisions. (i.e. every club can have its own version of a
table called peanut_sales if it wants.)

Because DDL is transactional in Ingres, allowing users to create tables at
will is no less safe on a running system than any other kind of SQL.

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:av9j311ieqaob5f3hb5oivjlsfps39pmc6@.4ax.com...

Hi Steve,

Please expand on your message, I'm not sure I fully understand your proposed
solution.

> First, I would limit capabilities to the following:
> 1. Custom player attributes.
> 2. Custom detail types with custom attributes.

What would be the "master" of the custom detail types. Please note that the
entity may not be directly related to a player. It might be about football
boots for instance.

> Now, so support customization, let each club have their own label names
for
> each custom player attribute, and let them have their own detail type
records
> where each detail type defines the label names for the custom detail
> attributes.

I'm not sure I'm follow this bit at all. What are label names and where are
they stored?. Ditto "detail type records". How would you express the essence
of your proposal in DDL/DML?

Kunle

How to build cubes from Oracle database?

Hi, can anyone show me the steps to use SQL Server

to perform OLAP analysis for data from Oracle relational database?

Thanks,

Chris

Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?

Bryan

|||

Bryan C. Smith wrote:

Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?

Bryan

Not yet.

Should I build the dimensional model in SQL Server or Oracle, and how?

Is there any document to show the steps?

Thanks,

Chris

|||

Traditionally, the OLAP database/cube exists as a cached layer on top of a series of fact and dimension tables (referred to as a dimensional model) in a relational database. SSAS 2005 can support dimensional models built in both Oracle and SQL Server (as well as other relational database technologies).

Technically speaking, you can skip the implementation of the dimensional model in the relational database. You do this by assembling sets in the DSV of your cube through named queries. Some folks with smaller data marts have successfully pulled this off, but it is not recommended.

If you are new to dimensional modeling, I highly recommend Ralph Kimball's "The Data Warehouse Toolkit". It will give you a solid foundation in this stuff and SSAS 2005 is aligned with the design principle he expouses.

Good luck,
Bryan

|||I agree that this is a good book to start with. You can also try the tutorial included with SQL Server Anlaysis Services 2005. All the same concepts apply regardless of whether your getting your data from a SQL Server relational database or an Oracle relational database. The only difference will be whithin the Data Source object which abstracts the connection to the source data.

How to build AS model in report builder?

Is anyone can figure out that how many ways to use AS cube as the datasouce
of report builder?
One of the way that I knew is use SQL Server Management Studio to create AS
Model for report builder.
So, can I use report designer to create?
I'm so confused on report designer for create data source views when using
Cubes.
Data source view wizard just can see relational data source and can't select
AS cube?
Thanks for any advice!
AngiAS-based models can only be generated from Management Studio or Report
Manager
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:O3B4yA9AGHA.608@.TK2MSFTNGP12.phx.gbl...
> Is anyone can figure out that how many ways to use AS cube as the
> datasouce of report builder?
> One of the way that I knew is use SQL Server Management Studio to create
> AS Model for report builder.
> So, can I use report designer to create?
> I'm so confused on report designer for create data source views when using
> Cubes.
> Data source view wizard just can see relational data source and can't
> select AS cube?
> Thanks for any advice!
> Angi
>

How to build application

Dear Sir/ Madam,

I used to use the clients/server application in two tier, but now i thinks the two tier of application is cannot support for the user as well.

That's why we try to build the three tier system in SQL Server. In our first time, we cannot build it at all and we don't know how to do it.

Is it SQL Server support three tier system?

If SQL Server can support, How can we build it?

I hope you will reply me soon.

Ratana Ky
Cambodia.

Ratana,

Take a look at this web site and spend some time witching the following webcasts. The presenter shows how easy it become to create n-tier applications. He also gives you a link to source code.

http://www.microsoft.com/events/series/teched2005.mspx

MSDN Webcast: ASP.NET 2.0: Overview of ASP.NET 2.0 (Part 1 of 2) (Level 300)

MSDN Webcast: ASP.NET 2.0: Overview of ASP.NET 2.0 (Part 2 of 2) (Level 300)

HTH
-w|||This question is pretty generic, like how to build a car. The answer is yes. You can do a web search for terms "three tier architecture sql server" to find articles on the subject.

How to build an string alias in T-SQL

I have the following stored procedure:

SELECT

SERVER_NAME,

SERVICE_PORT,

SERVER_NAME + ',' + SERVICE_PORT

ASSERVER_AND_PORT

FROM

DEF_SERVICE_SETTINGS

I want the 3rd column to be in format: SERVER_NAME + "," + SERVICE_PORT

But SQL gives error. Sees "," as column.

How can I fix this?

Which database and language you are using?|||

SQL-server

Column SERVER_NAME is varchar type
Column SERVICE_PORT is int type

|||

My code works when both type of columns are of varchar, but the SERVICE_PORT column is of int type.

Can I cast the int type to a varchar type in some way?

|||

Found it:

SERVER_NAME +','+CAST(SERVICE_TCP_PORTAs varchar(1000))ASSERVER_AND_PORT

|||

Yes you have to Cast.

SERVER_NAME + ',' + Cast(SERVICE_PORT as varchar) AS SERVER_AND_PORT

|||convert(varchar(50), SERVICE_PORT)

HTH

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> > Pieter,
> >
> > Do you now need Bella because you was yesterday to much involved with
> > Stella.
> > I count 4 newsgroups, not 2.
> >
> > However your problem sounds not difficult for me, what I not direct see,
> is
> > if the tblMycows is a datatable or that it is a table in a database?
> >
> > Cor
> >
> >
>|||Pieter,
> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
http://groups-beta.google.com/group/microsoft.public.dotnet.languages.vb/msg/470d93378c5467f8?hl=en
Cor

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> is
>|||Pieter,

> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
3378c5467f8?hl=en" target="_blank">http://groups-beta.google.com/group...78c5467f8?hl=en
Cor