Data Structures

(Wow; surfing this site has really illuminated what a lowly hack-programmer I am to this field of SQL and relational processing :S )

I am creating a temp table, doing an Bill of Material explosion for a single Order Line Item.

(note: SQL Server 2000)

I used blindman's "accumulator method (click here) (http://www.dbforums.com/showpost.php?p=6239109&postcount=6)" to generate the entire potential BOM tree.

So; step 1 works wonderfully! :beer: Cheers blindman!

Now; I want to remove unwanted nodes (and all their children) from the temp-table. I have a bunch of functions (0 to 1 for each node) that return a Yes or No. If "No", then flag-to-eliminate immediately that branch and don't revisit anything on it (and therein lies my problem). This will leave me with a temp table of only valid nodes.

Since this is recursive, and since it will involve Dynamic SQL (the function name varies), all I can come up with is using a Cursor in a WHILE loop. Even at that; since a CURSOR is point-in-time (ie: values don't change once selected), I'll have to re-check the current temp table values (or create of 2nd temp table of only deleted nodes and repeat a SELECT with NOT EXISTS in it, hmmm).

Since blindman's method generates a table ordered by level, the sequence of processing is pre-determined - unless I can re-order it into a more traditional hierarchy (1 entire branch at a time) and number the levels, in which case the cursor could just skip to the next branch of equal or higher level.

Note: The thought does occur to me I could have an intermediary function (static name) that in turn does the Dynamic SQL. These functions contain the Business Logic that looks at a myriad of column values and relationships in the database and there's no one-size-fits-all decision tree so Dynamic SQL is necessary.

The max cursor size will be maybe 300, and on average 100. Number of levels will normally be 3 or 4, but conceivably could be up to 10. Given the average 100 potential components/sub-assemblies, the final assembly will be about 30. As a periodic background process; it will do 3,000 Order Line Items a day, so I'm figuring 1 second response time per build is adequate (ie: the user's not waiting on it so it doesn't have to be blinding fast) - however why waste?

Anyhow; I thought this might be a fun problem for some Data Structure genius who wants to give a lesson in Relational Programming.

Thanks for looking.

Here's what I have so far:

CREATE PROCEDURE dbo.sp_ExplodeTest1 (@.recID int = 1) AS
/* tbTestH is a table containing an assembly hierarchy.
Assemblies with no parent are Builds.
Assemblies with no children are Components.
It's columns:
MyID int,
ParentID int,
(other descriptive columns)
*/

declare @.t table (
TempNodeID int identity(1,1)
,MyID int
)

-- Seed the tree with the Build's ID.
insert into @.t (MyID) values (@.recID)

/* This populates the temp table with the entire
Assembly for the given build.
It is Ordered By the level in the assembly.
Number of assembly levels is infinite.

For example:
Level 1 = the Build. It comes first
Level 2 = all parents are level 1. Is next (no particular seq)
Level 3 = all parents are level 2. Is next (no particular seq)
etc.

*/
while @.@.Rowcount > 0
insert into @.t (MyID)
select tbTestH.MyID
from tbTestH
inner join @.t rList on tbTestH.ParentID = rList.MyID
where not exists (
select *
from @.t CurrentList
where CurrentList.MyID = tbTestH.MyID)

-- now to display the results so far.
select t.*, tbTestH.*
from tbTestH inner join @.t t on tbTestH.MyID = t.MyID
order by t.TempNodeID

GOOk, well you base code looks fine. But I don't understand what you are trying to do next.

Do you want to use one or more functions to eliminate a branch and all of its children from the dataset you have created? Why not just embed the function in the code you use to populate the table, filtering out all and excluding all those that fail your function tests?

And thanks for the beer.|||Ok, well you base code looks fine. But I don't understand what you are trying to do next.

Do you want to use one or more functions to eliminate a branch and all of its children from the dataset you have created? Why not just embed the function in the code you use to populate the table, filtering out all and excluding all those that fail your function tests?

And thanks for the beer.
You're welcome and well deserved.

I'm not sure about the ultimate possibilities, but if I'm forced with the cursor approach then you are right, I could just skip them. Deleting from the list (or flagging for deletion, since that would have less overhead) only has value if there will be multiple passes, particularly using cursors. In other words, I don't want to repeat work.

I'm realizing that if I have 3000 Order Line Items to process, that I may be able to do them all in one swoop (attaching the OrderLineItemID to each row of course).

Each node of the final build will have a quantity and size that also require calculation. I've defined 15 different methods for calculating quantity and size (example: same as height, same as width, xref-lookup based on height, xref-lookup based on width, xref-lookup based on height/width combo, hard coded at 1, etc.). As well; each component translates (either directly or via a color-xref lookup) into an actual Product ID.

In cases where the node is not a component (ie: has children), it's quantity is used as a multiplier for it's branch. Example; if there's 4 "ladder assemblies", and each ladder has 2 plastic plugs, then the "plastic plug" quantity will be 8. Therefore; the heirchy probably needs to be traversed in sequence - unless I can think of a fancy single update with group-by statement that zooms it all together.

I'm afraid my brain is incapable of looking at the problem and knowing just how to best solve it. I plan to use this remainder of this week to work that out. If someone here is inclined to help out, I'll be most grateful (hey; I'll hand out a whole page of beers for an elegant solution).

Not sure if I've given enough info for the next step. I guess that would be either
1. Ordering this into hierarchical sequence and assigning level numbers
2. Ordering this into hierarchical sequence and assigning high-low node numbers.
Hopefully; with a nice single-SQL update statement (or series of statements like Blindman's WHILE ... INSERT).

My goal isn't the most elegant solution in the world, but one that'll work pretty well for the problem at hand (BOM, MRP, Forcasting system for 35m/yr custom build-to-order manufacturer). By "well", I mean modestly fast, scalable, maintainable, modular, flexible, and accurate.

Thanks!|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.

I believe it's not 1 level of children.....|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.
Each node, regardless of it's status (Tree, Branch, or Leaf) may be either:
1. Always present, given that it's parent is present.
or
2. Optionally present, dependent upon the Y/N result of a custom function.

The fuction for each node is custom only to that node.

Example:

CREATE FUNCTION fn_YN_10 (parms...)
RETURNS bit AS
BEGIN
DECLARE @.MyAnswer bit
SET @.MyAnswer = 1
IF (SELECT MySpecialFeature from tbLineItem where LineItemID = @.parmLineItemID) = "S"
SET @.MyAnswer = 0

RETURN @.MyAnswer

END

The name "fn_YN_10" is a value on the tbAssy. How can I execute that except within a Cursor? Please illuminate?

Also; I need to skip the entire branch if the answer is 1 (ie: No). One method to do that is have the Cursor order everything in hierarchical sequence(everything for a branch together) and process it in traditional loop fashion and with the levels numbered (tree = 1, next branch = 2, leafs = some higher number). If the answer is 1 (false) then it skips everything until the next node at the same or a lower level.

In skipping a branch; I could possibly add to a 2nd table of "Excluded Branches" and have a select with "NOT EXISTS". I believe there's a way to do that HOWEVER; if I have to use a cursor to get the Dynamic SQL anyway, then the first approach makes the most sense.

Note: If I do process 3000 Line items simultaneously, with avg 100 nodes each, we're talking 300,000 nodes. Now it's time to address performance. If I can avoid a Dynamic SQL Cursor, then by all means, that's the way to go. Not sure if doing all at once will make things faster or just cause indexing problems.

One possible solution: I could create an intermediate fuction to which the Function Name (and applicable parms) is passed. That would have a static name. It could then do the Dynamic SQL. That would avoid forcing the processing to use a CURSOR just because of the need for Dynamic SQL. But then it's back to how I can skip a branch without doing a Cursor Loop.|||I believe it's not 1 level of children.....
Correct:

Level 1 = tree
Level 2 or higher = branch
Level 2 or higher = leaf

Children always have higher number so by definition cannot be 1.|||but are we talking about children, grand children, great grandchildren, ect|||but are we talking about children, grand children, great grandchildren, ect
Yes.

Example of the tree representation:
Tree = no parent
Branch = has parent and child
Leaf = no child

01 (tree's root - level 1)
..02 (leaf - level 2)
..03 (branch - level 2)
...04 (branch - level 3)
.....05 (leaf - level 4)
......06 (leaf - level 5)
..07 (branch - level 2)
...08 (branch - level 3)
.....09 (leaf - level 4)
.....09 (leaf - level 4)
..10 (leaf - level 2)
..11 (branch - level 2)
...12 (leaf - level 3)

The child, father, grandfather, great grandfather traverses in the other direction and as such, has limitations
01 (great grandfather)
..02 (child) oops, this doesn't really work ...
..03 (grandfather)
...04 (father)
.....05 (child) oops, this doesn't work either
......06 (child) grandchild? doesn't fit.
etc

So; Child, Father, Grandfather notation works when referencing the lineage of a single elementry component, but not for representing a jagged hierarchy from it's highest level on down.

I see where child needs to be level 01 and so on using this notation method, ergo the confusion.

My bad for using the Child/Parent notation. I should have stuck with the Tree notation. Sorry about the confusion.

Data security/filtering on field values

My team is developing a new software system that is replacing several
existing applications. The applications being replaced focus on billing and
customer relationship management.
Our company is an international corporation that serves a large number of
customers (hundreds of thousands) spread throughout the US and Canada. For
administrative and accounting reasons, we have divided the customers into
groups based on geographic region (Eastern, Central, and Western US, Canada)
.
Customers are also grouped within their Region into Service Areas.
Our new system requires that authorization be performed based on both the
roles of the user (basic RBAC) and on the set of data that the user is
authorized to view and manipulate.
For example, a customer service rep for the West region of the country
should only be able to see and work with customers that are located in the
West region – to this user, the rest of our customers are irrelevant. The
system should behave as though the only customers that exist are those
located in the region with which they work.
In addition, we need to set up non-geographic data filters, such as the
customer classification (similar to SIC code) and value fields such as
Salesperson.
The filtering of data must allow for both unions (“Or”) and intersection
s
(“And”) between different security settings; for instance, we must be ab
le to
support the following overlapping sets of data filters:
1) All customers in the “East” region and all customers with the
classification “Hospital”
2) All “East Hospital” customers (but no Hospitals outside of the East
region or East customers that are not Hospitals)
3) All customers in either the “East” or “West” regions, regardless
of
classification
We also need to support the concept of “Data Restrictions”, where the
default access to customers with a specific attribute is False unless a gran
t
is assigned.
We need a solution, whether it is a proven architectural approach or a
third-party technology, which will allow us to apply the defined data filter
s
at the lowest level of our application as possible with as little performanc
e
impact as possible. We would prefer to implement something in the database,
but if necessary we could implement it in the data access layer of our
software.
The solution should allow flexible definition of ‘securable attributes’
of a
customer, so that if an additional attributes are defined in the future we
don’t need to modify the database structure or recompile any code.
Questions:
1) Is there a standard or best-practice architectural approach to applying
the sort of security/filtering described above?
2) Are there any third-party tools or technologies that we could purchase
that would provide some or all of this functionality?
3) What sorts of approaches have been used before to address this type of
security requirement?
Thanks in advance,
GuyAs I flew above your text, that thing came in mind for me:
http://vyaskn.tripod.com/ row_level...as
es.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Guy" <Guy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B4BD9CCC-1B6C-4B49-BEFA-49551E7F6167@.microsoft.com...
> My team is developing a new software system that is replacing several
> existing applications. The applications being replaced focus on billing
> and
> customer relationship management.
> Our company is an international corporation that serves a large number of
> customers (hundreds of thousands) spread throughout the US and Canada.
> For
> administrative and accounting reasons, we have divided the customers into
> groups based on geographic region (Eastern, Central, and Western US,
> Canada).
> Customers are also grouped within their Region into Service Areas.
> Our new system requires that authorization be performed based on both the
> roles of the user (basic RBAC) and on the set of data that the user is
> authorized to view and manipulate.
> For example, a customer service rep for the West region of the country
> should only be able to see and work with customers that are located in the
> West region - to this user, the rest of our customers are irrelevant. The
> system should behave as though the only customers that exist are those
> located in the region with which they work.
> In addition, we need to set up non-geographic data filters, such as the
> customer classification (similar to SIC code) and value fields such as
> Salesperson.
> The filtering of data must allow for both unions ("Or") and intersections
> ("And") between different security settings; for instance, we must be able
> to
> support the following overlapping sets of data filters:
> 1) All customers in the "East" region and all customers with the
> classification "Hospital"
> 2) All "East Hospital" customers (but no Hospitals outside of the East
> region or East customers that are not Hospitals)
> 3) All customers in either the "East" or "West" regions, regardless of
> classification
> We also need to support the concept of "Data Restrictions", where the
> default access to customers with a specific attribute is False unless a
> grant
> is assigned.
> We need a solution, whether it is a proven architectural approach or a
> third-party technology, which will allow us to apply the defined data
> filters
> at the lowest level of our application as possible with as little
> performance
> impact as possible. We would prefer to implement something in the
> database,
> but if necessary we could implement it in the data access layer of our
> software.
> The solution should allow flexible definition of 'securable attributes' of
> a
> customer, so that if an additional attributes are defined in the future we
> don't need to modify the database structure or recompile any code.
> Questions:
> 1) Is there a standard or best-practice architectural approach to applying
> the sort of security/filtering described above?
> 2) Are there any third-party tools or technologies that we could purchase
> that would provide some or all of this functionality?
> 3) What sorts of approaches have been used before to address this type of
> security requirement?
> Thanks in advance,
> Guysql

Data returned in QA is different than EM

Hi all,
Something strange is going on: When I do a select * from table some of the
data appears to be in the wrong field. However, when I look at the table
contents in the EM, they are all correct. When I do select statement in the
QA based on criteria, the correct rows are not returned.
Any one know what this is all about?
Thanks for your guidance,
SeanWhat version of SQL Server / Query Analyzer? Have you applied the latest
(SQL Server) service pack to the server and client?
How is Query Analyzer set to display data (grid or text)?
By "wrong field" I assume that you mean it appears that the data that is
displayed appears to be in the wrong <ahem> "field" because it appears in
the wrong area of the [text] display of the results. Do you store <carriage
return> and <line feed> (char(13) char(10)) within your data? If so perhaps
those characters along with results in text mode are causing the display to
look incorrect.
Keith
"sean sobey" <Sobey@.yahoo.com> wrote in message
news:%23iVjrebSFHA.508@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Something strange is going on: When I do a select * from table some of
> the
> data appears to be in the wrong field. However, when I look at the table
> contents in the EM, they are all correct. When I do select statement in
> the
> QA based on criteria, the correct rows are not returned.
> Any one know what this is all about?
> Thanks for your guidance,
> Sean
>|||How can we reproduce the problem?
AMB
"sean sobey" wrote:

> Hi all,
> Something strange is going on: When I do a select * from table some of th
e
> data appears to be in the wrong field. However, when I look at the table
> contents in the EM, they are all correct. When I do select statement in t
he
> QA based on criteria, the correct rows are not returned.
> Any one know what this is all about?
> Thanks for your guidance,
> Sean
>
>|||Hi, Sean
See:
http://groups-beta.google.com/group...ea781f3bc49bf23
Razvan

data replicate incorret field (Urgent)

I'm using SQL 2000 with SP3 and made an replication link to another server.
A few weeks ago, I drop the replication and add a new column in both
publisher and subscriber server and made a new replication. Recently, I
found that a new column did not replicate a data. I check with
sp_browsereplcmds found that it update a another field. Why? How to fix it?
please help
RC
Firstly, it's worth mentioning that sp_repladdcolumn will do what you
require 'in-place' ie there's no need to set up the publication from
scratch. You may have had other reasons for doing this anyway. So, to find
out a little more, are the schemas on the publisher and subscriber now
identical? If not, do you have any filters in place? Did you create the
replication stored procedures yourself?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||the schema is the same on publisher and subscriber and no columns filtering
RC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> glsD:%23M9TOCQFGHA.1312@.TK2MSFTNGP09.phx .gbl...
> Firstly, it's worth mentioning that sp_repladdcolumn will do what you
> require 'in-place' ie there's no need to set up the publication from
> scratch. You may have had other reasons for doing this anyway. So, to find
> out a little more, are the schemas on the publisher and subscriber now
> identical? If not, do you have any filters in place? Did you create the
> replication stored procedures yourself?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK. Perhaps you have changed the column order on the table at the
subscriber, or the subscriber and publisher schema are out of sync in some
way. If you run sp_scriptpublicationcustomprocs @.publication = 'yourpubname'
do you get the same stored proc syntax as the one currently existing on the
subscriber? Is this the same column order in the Insert Statement as that
existing on the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Data outside parameter field

Can any body help me that how to get data (opening balance) of an account in report wich showing data in between specific dates. The opening balance will be the values before the parameter fields.Use Sub report to show the Opening Balance. So create a sub report without using parameters, just show the opening balance and dont link the sub report and main report.

In the sub report created a formula, "sub_op"

WhilePrintingRecords;
Shared x As Number
x = Opening Balance

and in the main report create a formula, "main_op"

WhilePrintingRecords;
Shared x as number

Use this formula in the main report to show Opening Balance. Important thing is place the subreport in the header before using the main_op formula, so that subreports gets executed first and will show the Opening Balance value in the main report.|||But if I want to get opening balance for a specific date then I have to pass a parameter for that.

So, if the I could not insert the subreport at header then what could be the solution.

Thankyou|||First I agree with khs_shankar that a subreport for the OpeningBalance is the way to go.

If you want to select date(s) for OpeningBalance independent from the the main report parameter field, then create parameter field(s) in both main & subreport, say Op_date1 (& Op-date2?). In your subreport, use these dates as a filter. Link the main & subreport by these Op_ parameter fields so you can pass the values.

If the Opening Balance are for the dates prior to your main parameter field, like a cut off date to show previous balance & details afterwards, then you can create a parameter field in your subreport, say Op-date. Link the parameter field in the main report with the Op-date field in the subreport. Use this Op_date field as a filter in the subreport.

Hope this help.|||I want to alter my question here.

The Opening balance must be of the current month and les then by the date which is selected by user.

Because I have tried "thg's" logic but when I want to link parameter main report with the Op_date of sub report, it does not show the op_date field in link combo.

Help in this regard.

Data of different dates

I want to make a report which is using parameter field of a single date. But the same time I have some fields those have to show some data out of the parameter field. And the grouping is same.

For example, a coloum show data of the date which is slected but another field have to show data of the current month but Less and equal to the selected field.

ThankyouYour best bet is to create one as a subreport to the other. These 2 reports link by the parameter field, but use different filter.

That's all I can say with the info you provide.

Good luck.

Data not getting plotted in line graph

Hi,
I have a LINE chart with 2 data fields, and 1 category field.
I havent included any series field in the chart.
If the query returns more than 1 records then both the data field values are
getting plotted in the graph.
If the query returns only one record then only the first data point is
plotted. I dont know why the value for second data field is not plotted...
Is it a bug in reporting services?
Please any one reply me asap... I have to fix it for the client by today...
Thanks for help...
Regards,
RanjithTo add more information,
if i change the chart type to any other chart other then LINE graph, it is
getting plotted. Only for Line graph its not working...
Thanks,
Ranjith
"Ranjith Prakash" wrote:
> Hi,
> I have a LINE chart with 2 data fields, and 1 category field.
> I havent included any series field in the chart.
> If the query returns more than 1 records then both the data field values are
> getting plotted in the graph.
> If the query returns only one record then only the first data point is
> plotted. I dont know why the value for second data field is not plotted...
> Is it a bug in reporting services?
> Please any one reply me asap... I have to fix it for the client by today...
> Thanks for help...
> Regards,
> Ranjith
>
>|||Please read this recent thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
It sounds like you have a very similar situation. You can either request the
SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
wait till the official release of SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> To add more information,
> if i change the chart type to any other chart other then LINE graph, it is
> getting plotted. Only for Line graph its not working...
> Thanks,
> Ranjith
> "Ranjith Prakash" wrote:
> > Hi,
> >
> > I have a LINE chart with 2 data fields, and 1 category field.
> > I havent included any series field in the chart.
> >
> > If the query returns more than 1 records then both the data field values
are
> > getting plotted in the graph.
> >
> > If the query returns only one record then only the first data point is
> > plotted. I dont know why the value for second data field is not
plotted...
> >
> > Is it a bug in reporting services?
> > Please any one reply me asap... I have to fix it for the client by
today...
> >
> > Thanks for help...
> >
> > Regards,
> > Ranjith
> >
> >
> >|||Hi Robert,
Thanks a lot for helping me. That did fix my bug.
Thanks again,
Ranjith
"Robert Bruckner [MSFT]" wrote:
> Please read this recent thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
> It sounds like you have a very similar situation. You can either request the
> SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
> wait till the official release of SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
> message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> > To add more information,
> >
> > if i change the chart type to any other chart other then LINE graph, it is
> > getting plotted. Only for Line graph its not working...
> >
> > Thanks,
> > Ranjith
> >
> > "Ranjith Prakash" wrote:
> >
> > > Hi,
> > >
> > > I have a LINE chart with 2 data fields, and 1 category field.
> > > I havent included any series field in the chart.
> > >
> > > If the query returns more than 1 records then both the data field values
> are
> > > getting plotted in the graph.
> > >
> > > If the query returns only one record then only the first data point is
> > > plotted. I dont know why the value for second data field is not
> plotted...
> > >
> > > Is it a bug in reporting services?
> > > Please any one reply me asap... I have to fix it for the client by
> today...
> > >
> > > Thanks for help...
> > >
> > > Regards,
> > > Ranjith
> > >
> > >
> > >
>
>

Data missing

Hey I'm really really new to crystal reports and I'm working on setting up a new paramater field and I'm running into some issues. The table for some reason is not showing all the data it should meaning its missing some records. When I go into SQL manager the data all seems to be there and in order but when I pull the same data into crystal part is missing. I looked at the way I had the tables linked to see if that would have something to do with the data is coming up but from the best I can tell everything looks fine. Has anyone noticed similar issues with a report if so what steps did you take to hunt down the problem?Did you use any Selection Formulas?
If so remove them
Also Do Verify Database

Data Lost?

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated, TKS
BB3
Hi,
Please open the SQL profiler and check the statements fired. You can use the
profilers default template.
Thanks
Hari
MCDBA
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:2f9a001c46d00$b4b588c0$a301280a@.phx.gbl...
> I have an ASP form created in FrontPage on an XP client
> that connects to a "remote" SQL database hosted on a
> Win2003 server. Connection tests, field mapping etc. all
> indicate OK. When I fill out the form and submit, I get
> confirmation and no indication of problems, however data
> does not appear in the database. Some insight in to what
> may be going on would be appreciated, TKS
> BB3

Data Lost?

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated, TKS
BB3Hi,
Please open the SQL profiler and check the statements fired. You can use the
profilers default template.
Thanks
Hari
MCDBA
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:2f9a001c46d00$b4b588c0$a301280a@.phx
.gbl...
> I have an ASP form created in FrontPage on an XP client
> that connects to a "remote" SQL database hosted on a
> Win2003 server. Connection tests, field mapping etc. all
> indicate OK. When I fill out the form and submit, I get
> confirmation and no indication of problems, however data
> does not appear in the database. Some insight in to what
> may be going on would be appreciated, TKS
> BB3

Data lost during converting int to smallint

I am trying to keep it consistent with data from the source, I change
datatype for a field from int to smallint and it has a value between 200 and
1000.
When I do it in Enterprise Manager, I give me this warning:
- Warning: Data might be lost converting column 'auto_pre_appr' from 'int'.
Is this just a general warning?
CulamYes, it is warning and you may or may not lose some data, if you know what
the actual data values in the column and what value scope of smallint type
is (-32768 to 32767). Obviously, if the column of int contains data with
value smaller that -32768 or greater than 32767, the warning would become
reality.
"culam" <culam@.discussions.microsoft.com> wrote in message
news:2DB9A059-2D1A-44C9-8D44-32F8F7D7848C@.microsoft.com...
>I am trying to keep it consistent with data from the source, I change
> datatype for a field from int to smallint and it has a value between 200
> and
> 1000.
> When I do it in Enterprise Manager, I give me this warning:
> - Warning: Data might be lost converting column 'auto_pre_appr' from
> 'int'.
> Is this just a general warning?
> Culam

Data Lost

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3
You'l have to use Profiler to see what exact procedures are called, and what
queries are executed inside SQL Server.
It could be that you are suppressing errors in your ASP code. Check that as
well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:3bab01c47285$3d18d170$a301280a@.phx.gbl...
I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3

Data Lost

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3You'l have to use Profiler to see what exact procedures are called, and what
queries are executed inside SQL Server.
It could be that you are suppressing errors in your ASP code. Check that as
well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:3bab01c47285$3d18d170$a301280a@.phx.gbl...
I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3

Data is there BUT could NOT find it!

Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
could not get the same record if I give the BO_ID Value. Please hava a look
at the following query and other details.
Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
ON/OFF Setting. Let me know should you need more info. Thanks.
USE ZADMIN
--BO_ID is there.
SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
--BO_ID ROW_ID
--898509508329674401118020031483 576
--Column_name Type
--BO_ID varchar
--ROW_ID int
--But, could NOT find it if BO_ID is given.
SELECT * FROM ZADMIN..CB_INST_IMPORT
WHERE BO_ID = '898509508329674401118020031483'
SELECT * FROM ZADMIN..CB_INST_IMPORT
WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
--Length of BO_ID is 30 but query says as 32.
SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
WHERE ROW_ID = 576 --32
--SeequellI'm curious about those two phantom characters. Try running something
like:
SELECT *,
ASCII(SUBSTRING(BO_ID),01,1)),
ASCII(SUBSTRING(BO_ID),02,1)),
ASCII(SUBSTRING(BO_ID),03,1)),
ASCII(SUBSTRING(BO_ID),04,1)),
ASCII(SUBSTRING(BO_ID),05,1)),
...
ASCII(SUBSTRING(BO_ID),29,1)),
ASCII(SUBSTRING(BO_ID),30,1)),
ASCII(SUBSTRING(BO_ID),31,1)),
ASCII(SUBSTRING(BO_ID),32,1))
FROM ZADMIN..CB_INST_IMPORT
WHERE ROW_ID = 576
Roy Harvey
Beacon Falls, CT
On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
<Seequell@.discussions.microsoft.com> wrote:

>Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
>could not get the same record if I give the BO_ID Value. Please hava a look
>at the following query and other details.
>Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
>ON/OFF Setting. Let me know should you need more info. Thanks.
>USE ZADMIN
>--BO_ID is there.
>SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> --BO_ID ROW_ID
> --898509508329674401118020031483 576
> --Column_name Type
> --BO_ID varchar
> --ROW_ID int
>--But, could NOT find it if BO_ID is given.
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE BO_ID = '898509508329674401118020031483'
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
>--Length of BO_ID is 30 but query says as 32.
>SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
>WHERE ROW_ID = 576 --32
>--Seequell|||Hi Roy,
I will test it soon and post the result. Thanks for replying.
--
--Seequell
"Roy Harvey" wrote:

> I'm curious about those two phantom characters. Try running something
> like:
> SELECT *,
> ASCII(SUBSTRING(BO_ID),01,1)),
> ASCII(SUBSTRING(BO_ID),02,1)),
> ASCII(SUBSTRING(BO_ID),03,1)),
> ASCII(SUBSTRING(BO_ID),04,1)),
> ASCII(SUBSTRING(BO_ID),05,1)),
> ....
> ASCII(SUBSTRING(BO_ID),29,1)),
> ASCII(SUBSTRING(BO_ID),30,1)),
> ASCII(SUBSTRING(BO_ID),31,1)),
> ASCII(SUBSTRING(BO_ID),32,1))
> FROM ZADMIN..CB_INST_IMPORT
> WHERE ROW_ID = 576
> Roy Harvey
> Beacon Falls, CT
> On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
> <Seequell@.discussions.microsoft.com> wrote:
>
>|||Found it. Hope I am right.
Characters were Line feed (char(10)) and Carriage return (char(13)).
Have a look at the query and result. Now, how do I remove it?
Thanks a lot.
SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
BO_ID
----
898509508329674401118020031483
--***--
SELECT ASCII(SUBSTRING(BO_ID,01,1)),
ASCII(SUBSTRING(BO_ID,02,1)),
ASCII(SUBSTRING(BO_ID,03,1))
FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
-- -- --
56 57 56
--***--
SELECT ASCII(SUBSTRING(BO_ID,29,1)),
ASCII(SUBSTRING(BO_ID,30,1)),
ASCII(SUBSTRING(BO_ID,31,1)),
ASCII(SUBSTRING(BO_ID,32,1))
FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
-- -- -- --
56 51 13 10
--***--
--Seequell
"Seequell" wrote:
> Hi Roy,
> I will test it soon and post the result. Thanks for replying.
> --
> --Seequell
>
> "Roy Harvey" wrote:
>|||Seequell wrote:
> Found it. Hope I am right.
> Characters were Line feed (char(10)) and Carriage return (char(13)).
> Have a look at the query and result. Now, how do I remove it?
> Thanks a lot.
> SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> BO_ID
> ----
> 898509508329674401118020031483
> --***--
> SELECT ASCII(SUBSTRING(BO_ID,01,1)),
> ASCII(SUBSTRING(BO_ID,02,1)),
> ASCII(SUBSTRING(BO_ID,03,1))
> FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
>
> -- -- --
> 56 57 56
> --***--
> SELECT ASCII(SUBSTRING(BO_ID,29,1)),
> ASCII(SUBSTRING(BO_ID,30,1)),
> ASCII(SUBSTRING(BO_ID,31,1)),
> ASCII(SUBSTRING(BO_ID,32,1))
> FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> -- -- -- --
> 56 51 13 10
> --***--
>
UPDATE CB_INST_IMPORT
SET BO_ID = REPLACE(BO_ID, CHAR(13)+CHAR(10), '')
WHERE ROW_ID = 576

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to use.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
Paul G
Software engineer.
Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.
|||ok, thanks for the information.
"Tibor Karaszi" wrote:

> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
>
>

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to us
e.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
Paul G
Software engineer.Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to
use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.|||ok, thanks for the information.
"Tibor Karaszi" wrote:

> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
>
>

Data insertion using stored procedure.

Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
following field insertion into a table, just wondering what statements to use.
table name = tabletest
*****************************
* primary key * varchar * integer *
*****************************
* 1 * test * 34 *
*****************************
* 2 * test2 * 21 *
*****************************
The input to the procedure is a 2, and I want the procedure to replace
the 21 with another integer (say 5) while leaving the rest of the row
unchanged, still contains string test2 and primary key 2.
Thanks.
--
Paul G
Software engineer.Read about the UPDATE command. An example (no column names were given...):
CREARE PROC procname
@.col1 int
AS
UPDATE tabletest
SET col3 = 5
WHERE col1 = @.col1
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> following field insertion into a table, just wondering what statements to use.
> table name = tabletest
> *****************************
> * primary key * varchar * integer *
> *****************************
> * 1 * test * 34 *
> *****************************
> * 2 * test2 * 21 *
> *****************************
> The input to the procedure is a 2, and I want the procedure to replace
> the 21 with another integer (say 5) while leaving the rest of the row
> unchanged, still contains string test2 and primary key 2.
> Thanks.
>
> --
> Paul G
> Software engineer.|||ok, thanks for the information.
"Tibor Karaszi" wrote:
> Read about the UPDATE command. An example (no column names were given...):
> CREARE PROC procname
> @.col1 int
> AS
> UPDATE tabletest
> SET col3 = 5
> WHERE col1 = @.col1
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6A3AA6DC-BC23-446D-A2B4-26A1602BDDF1@.microsoft.com...
> > Hi kind of new to SQL. Anyhow I want to use a stored procedure to do the
> > following field insertion into a table, just wondering what statements to use.
> > table name = tabletest
> > *****************************
> > * primary key * varchar * integer *
> > *****************************
> > * 1 * test * 34 *
> > *****************************
> > * 2 * test2 * 21 *
> > *****************************
> > The input to the procedure is a 2, and I want the procedure to replace
> > the 21 with another integer (say 5) while leaving the rest of the row
> > unchanged, still contains string test2 and primary key 2.
> > Thanks.
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>

Data Grouping

hi,

I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".

I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.

Now i want something like this.

Group my expenditure in three categories like

1. My expenditure on monday

2. My expenditure on tuesday

3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)

what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.

how can i do this?

Thanks

rohit

Is my problem not clear?

Please anybody help!!

Thanks & regards

|||

Instead of using a matrix table top create your report, you could use a query to pivot your data.

(SUM(CASE WHEN DAY = 'MONDAY' THEN EXPENDITURE ELSE 0 END)) AS [MON EXP],

(SUM(CASE WHEN DAY = 'TUESDAY' THEN EXPENDITURE ELSE 0 END)) AS [TUES EXP],

(SUM(CASE WHEN (DAY <> 'MONDAY' AND DAY <>'TUESDAY') THEN EXPENDITURE ELSE 0 END)) AS [ROW EXP]

FROM EXPENDITURE_TABLE

Then insert the fields from the datasource into a std table component in a report.

data from one table into another

I have searched for this answer without success, probably not hard enough.

I want to take the data from one field in a table, and place it in another field in another table. c#

Database1.InsertCommand = "insert INTO

I just want the id in table one to be put in table 2

That much depends on the rows where you want to transfer the values to already exists on the other table (then you will have to do an update)

UPDATE SomeOtherTable
SET SomeColumn = SomeTable.Id
FROM SomeOtherTable
INNER JOIN SomeOtherTable Sot
ON SomeTable ST
Sot.SomeColumnToJoin = St.SomeColumnToJoin

or the rows do not exists yet

INSERT SomeOtherTable
(SomeColumn)
SELECT SomeColumn
FROM SomeTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thankyou for spending the time jens.

That does answer the question. All I have to do now is put

that into c#

data format

How can I display a value as 0.00 from a field of datatype numeric?How can I display a value as 0.00 from a field of datatype numeric?
select convert(numeric(9,2),round(columnname,2)) from tablename