DB Design :: Implementing Hierarchies In Server
Jun 12, 2015
I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager, Delivery Manager).
One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child has more than 1 parent).
Are there other more efficient ways? What is the best way to implement this?
View 9 Replies
ADVERTISEMENT
Feb 15, 2000
Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.
Many Thanks in advance!
View 1 Replies
View Related
Mar 25, 2008
Dear Colleagues,
How do I install a Microsoft SQL Server 2005 database application in an existing server in an existing database server and still have the control over it and also restrict the new server Admin user from editing or even opening my DB. Any modification of my database or code should be implemented only by me. Is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.
Thanks and best regards,
Peter
View 3 Replies
View Related
Jul 29, 2007
Hi!
I'm a student of computer science, and for one of my projects I would need Weka plug-in for SQL Server 2005. I don't know much about plug-ins, so if I'm asking stupid questions, I hope you will forgive me. It was easy to get a library from weka that I can use in Visual Studio, but I just can't figure out, how to get Weka data mining algorithms into plug-ins for MSSQL 2005.
I would appreciate any help given...
AzDHeX
View 8 Replies
View Related
Jun 2, 2006
I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:
[MessageId] [uniqueidentifier] NOT NULL,
[MessageType] [uniqueidentifier] NOT NULL,
[Status] [tinyint] NOT NULL,
[SubmittedTime] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[DispatchedTime] [datetime] NULL,
[CompletedTime] [datetime] NULL,
[MessageData] [image] NULL
This is how I retrieve the next message for processing:
SELECT TOP 1 *
FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus
ORDER BY [StartTime]
and mark it as being processed:
UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId
After message has been processed I delete it from the queue:
DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId
All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.
I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:
SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)
But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this?
Thank you,
Alex
View 18 Replies
View Related
Feb 22, 2006
How can I implementing Membership and Role Management on SQL Server 2005 (not Express)? Anyone have any documentation? Are there any scripts to run that will basically set up the same Database schema on SQL Server 2005 that Express uses?
Thanks.
View 3 Replies
View Related
Feb 27, 2006
Hello Folks
I am a total new boy to Visual Web Developer and am struggling to implement a lookup field in the SQL Server Express that ships with it.
I am trying to find the equivelent of using the lookup wizard in MS Access. So in Access I can select the lookup wizard from the drop down list of datatypes and refer to another table of standing data. Once configured the tables contents appear as a drop down list in the field of the master table.
So how do I create a table in SQL Server 2005 that uses another table to give a drop down list in one of its fields?
I have found some excellent tutorials and books but none of them seem to drill down to this level of table design, can anyone recommend any books that do?
Thanks
Bradley
View 8 Replies
View Related
Nov 5, 2006
I am looking for some assistance from the grand knowledgebase out there concerning the implementation of Sql Server Express 2005 on a client's strict domain environment.
I am designing and implementing a pos software that resides on registers and a server within a number of stores. The registers are running WePos and the server is running Server 2003. I run an instance of sql server express on all devices. The registers read the server when it can see it but when it cannot it reads the local instance. I am seeing a number of performance issues and I am trying to tweek the installation and coding of SSE on all devices.
Any words of wisdom for me out there???
Thank you,
Sir Robert
View 3 Replies
View Related
Nov 10, 2005
Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
View 5 Replies
View Related
Feb 6, 2005
As promised, question #2... ;)
As I mentioned in my previous post, my "real" case is a cube with 79 dimensions, most of which virtual, have been added for convenience.
Think for instance about a time dimension... Wouldn't it be nice to get a matrix with years horizontally, months vertically and displaying say the number of order you had for each cell in the resulting grid.
Ok, maybe you can do this with MDX but not in Excel, unless you create virtual dimensions for the Year and Month levels.
That's all good, but as it is, in my real case, I end up with four date dimensions for which I have to provide:
YQMD (Year, Quarter, Month & Day) hirarchized dimension
YWD (Year, Week, Day)
YQMD (Fiscal calendar)
Year
Quarter
Month
Week
Day (as 1, .. 31)
Week Day (for periodicity analysis over a week's time)
Date (as individual day - this is the backbone for the virtual dimensions)
It turns out this makes 40 dimensions by itself. For the sake of it, I grouped them by 4 hierarchies, although I've seen no specific functionality off of this in the data browser or Excel, so it really seems to be only for "show".
Now in my previous post I explained how I "spread" my session count to calculate a conversion rate. Given the number of dimensions I have (very high segmentation at the order level, very limited segmentation at the session/visit level), this means my calculated cell formula looks like this (hold your breath, it's ugly):
SubCube: {[Measures].[Session Count]}, DESCENDANTS([Business Unit].[All]), DESCENDANTS([Channel Source].[All]), DESCENDANTS([Currency].[All]), DESCENDANTS([FO Product Segment].[All]), DESCENDANTS([FO Quarter].[All]), DESCENDANTS([FO Value Segment].[All]), DESCENDANTS([Is Cancelled].[All]), DESCENDANTS([Is Customer Care].[All]), DESCENDANTS([Is Design Services].[All]), DESCENDANTS([Is Direct].[All]), DESCENDANTS([Is Estimated].[All]), DESCENDANTS([Is Holiday Order].[All]), DESCENDANTS([Is New].[All]), DESCENDANTS([Is Sales Services].[All]), DESCENDANTS([Language].[All]), DESCENDANTS([Payment].[All]), DESCENDANTS([Product Segment].[All]), DESCENDANTS([Value Segment].[All]), DESCENDANTS([FO Channel].[All]), DESCENDANTS([Reg Channel].[All]), DESCENDANTS([FCD].[Date].[All]), DESCENDANTS([FCD].[Day].[All]), DESCENDANTS([FCD].[Mth].[All]), DESCENDANTS([FCD].[Qtr].[All]), DESCENDANTS([FCD].[Wk].[All]), DESCENDANTS([FCD].[WkDay].[All]), DESCENDANTS([FCD].[Year].[All]), DESCENDANTS([FCD].[Fiscal].[All]), DESCENDANTS([FCD].[YMQD].[All]), DESCENDANTS([FCD].[YWD].[All]), DESCENDANTS([FRD].[Date].[All]), DESCENDANTS([FRD].[Day].[All]), DESCENDANTS([FRD].[Mth].[All]), DESCENDANTS([FRD].[Qtr].[All]), DESCENDANTS([FRD].[Wk].[All]), DESCENDANTS([FRD].[WkDay].[All]), DESCENDANTS([FRD].[Year].[All]), DESCENDANTS([FRD].[Fiscal].[All]), DESCENDANTS([FRD].[YQMD].[All]), DESCENDANTS([FRD].[YWD].[All]), DESCENDANTS([FSD].[Date].[All]), DESCENDANTS([FSD].[Day].[All]), DESCENDANTS([FSD].[Mth].[All]), DESCENDANTS([FSD].[Qtr].[All]), DESCENDANTS([FSD].[Wk].[All]), DESCENDANTS([FSD].[WkDay].[All]), DESCENDANTS([FSD].[Year].[All]), DESCENDANTS([FSD].[Fiscal].[All]), DESCENDANTS([FSD].[YQMD].[All]), DESCENDANTS([FSD].[YWD].[All])
Caculation Value: CalculationPassValue(([Business Unit].[All],[Channel Source].[All],[Currency].[All],[FO Product Segment].[All],[FO Quarter].[All],[FO Value Segment].[All],[Is Cancelled].[All],[Is Customer Care].[All],[Is Design Services].[All],[Is Direct].[All],[Is Estimated].[All],[Is Holiday Order].[All],[Is New].[All],[Is Sales Services].[All],[Language].[All],[Payment].[All],[Product Segment].[All],[Value Segment].[All],[FO Channel].[All],[Reg Channel].[All],[FCD].[Date].[All],[FCD].[Day].[All],[FCD].[Mth].[All],[FCD].[Qtr].[All],[FCD].[Wk].[All],[FCD].[WkDay].[All],[FCD].[Year].[All],[FCD].[Fiscal].[All],[FCD].[YMQD].[All],[FCD].[YWD].[All],[FRD].[Date].[All],[FRD].[Day].[All],[FRD].[Mth].[All],[FRD].[Qtr].[All],[FRD].[Wk].[All],[FRD].[WkDay].[All],[FRD].[Year].[All],[FRD].[Fiscal].[All],[FRD].[YQMD].[All],[FRD].[YWD].[All],[FSD].[Date].[All],[FSD].[Day].[All],[FSD].[Mth].[All],[FSD].[Qtr].[All],[FSD].[Wk].[All],[FSD].[WkDay].[All],[FSD].[Year].[All],[FSD].[Fiscal].[All],[FSD].[YQMD].[All],[FSD].[YWD].[All],[Measures].[Session Count]), 0)
If you read all this, you can see already the cryptic dimension names like "FRD", "FSD" and so forth... that's because with the real names ("First Refund Date", "First Ship Date", the query processor errored out... visibly there is a limit in the size of the formulas you can post!
Is there no other way to achieve this result? Basically I mean to say: if the session count is not defined at your level along this dimension, go to the root of the dimension to get the value there, this along a slew of dimensions, many of which are inherently dependent because of the usage of virtual dimensions (therefore if I wish to go to the root of my "First Refund Date" for instance, I wish to do so along all sub-dimensions... Heck, as far as I'm concerned this is conceptually only ONE dimension, just with various views upon it...
Using hierachies I was sort of hoping for the ability to have something like:
[FRD].[All Hierachies].[All]
Am I just asking for too much or do I just not know (quite probable) the magic keyword that can do this?
Where this is becoming quite critical is that I actually have a calculated cell that goes as follows (abridged):
SubCube: similar as before
Calculation: [Measures].[Order Count]*CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Distributed Marketing Cost])/CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Order Count])
Now the purpose of this is to distribute external costs at the order level. In short, say that you know you spent $10,000 globally promoting a specific group of websites in commission money (you pay $1 for each order). This formula allows me to get that a specific website, with 20 orders incurred an additional marketing cost of $20. That's actually the object of my next thread's question (spreading a multiplication through the aggregations)
For the purpose of this thread I am just concerned about the size of my formulas. Renaming the dimensions seemed to "buy" me some margin and I was actually quite surprised to find that the formula still fitted in, and works, but it is only a matter of time until I have to add more dimensions and the whole thing blows up in my face. Adittionally this is obviously not pleasant to look at and maintain.
Any better solution?
Thanks,
Seb
View 1 Replies
View Related
May 27, 2008
Hi,
Nwebie question:
is it possible to have a fact that will behave diffeerntly as the user
is viewing it from different hierarchies? say i got creatin measures
of a product inventory. i got a location hierarchy, as as user is
'moving up' in it (say,up by city, region, state, country...)i want to
do a sum. but i also got time hierarchy, and as user begins to group
by weeks, months, years... a sum makes no sense..i want LastNonEmpty
or something like that. Is it possible?
thanks!
View 9 Replies
View Related
Jan 24, 2004
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
parent | child
1 | 2
2 | 3
2 | 4
3 | 6
3 | 7
4 | 8
5 | 9
8 | 10
8 | 11
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Thanks.
Jon
View 20 Replies
View Related
Jan 25, 2004
Hi SQL Server Experts,
Oracle has a 'Connect by prior' extension to SQL that is neat for display hierarchies.
Does SQL Server have anything simialr or would I use T-SQL or ???
I will appreciate any suggestions and advice.
B.Dimple
Junior DBA
View 5 Replies
View Related
Oct 31, 2007
i guess really i'm looking for opinions and / or experiences of modelling organisational hierarchies.... anyone?
i'm in the process of creating a logical model (very early stages) of a new datawarehouse. The current OLTP schema stores it as a self referencing key (i.e. parent_group_id). The performance problems involved in aggregating to different levels of the hierarchy causes no end of complaints from customers, as i'm sure you can imagine. So, now we are modelling a datawarehouse for their reporting requirements, i obviously want it to be better
so far i've considered...
1) keeping it as it is
2) allocating a 'node' id and storing the left / right nodes in the hierarchy tree. we've used before when reporting over the OLTP system with report developers building it 'on the fly' as part of their report.
(better explanation than mine... http://www.dbpd.com/vault/9811/kamfn.shtml )
3) an intermediate table storing its 'position' in the hierarchy? not really played with this yet but mr kimball seems to like it
see... http://www.dbmsmag.com/9809d05.html
4) denormalising it completely. i.e. level_1, level_2, level_3, Level4.... for every fact? the advantage of this is that it makes aggregation at any level really easy. the problem is that each of our customers has a different organisation hierarchy, with a different number of levels, and given the OLTP schema there is no limit to the number of levels in a hierarchy
so... thoughts / experiences anyone?
Em
View 2 Replies
View Related
Jul 20, 2005
Is there a good approach to modelling many heterogeneous entity typeswith that have some attributes in common?Say I have entities "employees" which share some attibutes (e.g.firstname, lastname, dateofbirth) but some subsets of employees (e.g.physicians, janitors, nurses, ambulance drivers) may have additionalattributes that do not apply to all employees. Physicians may haveattributes specialty and date of board certification, ambulancedrivers may have a drivers license id, janitors may havepreferredbroomtype and so on.There are many employee subtypes and more can be dynamically addedafter the application is deployed so it's obviously no good to keepadding attributes to the employees table because most attributes willbe NULL (since janitors are never doctors at the same time).The only solution I found for this is a generalization hiearchy whereyou have the employee table with all generic attributes and then youadd tables for each new employee subtype as necessary. The subtypetables share the primary key of the employee table. The employee tablehas a "discriminator" field that allows you to figure out whichsubtype table to load for a particular entity.This solution does not seem to scale since for each value of"discriminator" I need to perform a join with a different table. Whatif I need to retrieve 1,000 employees at once?Is that possible to obtain a single ResultSet with one SQL statementSQL?Or do you I need to iterate look at the discriminator and thenperform the appropriate join? If this kind of iteration is necessarythen obviously this generalization hierarchy approach does not work inpracticesince it would be painfully slow.Is there a better approach to modelling these kind of heterogeneousentities with shared attributes that does not involve creating a tablefor each new employee type or having sparce tables (mostly filled withNULLS)I guess another approach would be to use name/value pairs but thatwould make reporting really ugly.Seems like a very common problem. Any ideas? Is this a fundamentallimitation of SQL?Thanks!- robert
View 13 Replies
View Related
May 19, 2008
I have a date dimension and Fiscal Year is a Hierarchy in it, like April, August and October for different fiscal years. I need to populate this in a report (SSRS) in a combo box so that the user can pick the fiscal year he/she wants. When done, I want to know what is the Fiscal year start month is, like April for April Fiscal year etc.
How do I achieve this? Is it even possible?
View 3 Replies
View Related
Sep 23, 2015
I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.
For example I have data like
H1 H2 Amount
1 1 100
2 1 50
3 1 45
I am getting a value of 100+50+45=195.
I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...
H1 H2 Amount
1 1 100
2 1 50
View 2 Replies
View Related
Apr 20, 2006
I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.
It appears that MS have assumed that SSIS users will automatically use SSAS.
We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.
I would like to be able build multi-level dimension hierarchies directly from within SSIS.
Has MS considered this for future versions?
View 2 Replies
View Related
Mar 12, 2015
I've built a robust looking Calendar dimension but that's easy because its all so well structured. Every date belongs to a particular week and every week belongs to a particular month and every month belongs to a particular quarter etc. January 1st is always going to be a member of week 1, month 1, quarter 1.But here's the rub, real life isn't as predictable and stable as a calendar.
Lets say you have an table containing personal information. You might have gender and marital status in there and even though they both relate to one person, they're unrelated to each other so how do you put those in a hierarchy? Which one goes first and which second because you can get combinations of either.
View 1 Replies
View Related
Dec 17, 2006
Hi,
I am currently trying to develop an application which would help in retrieving data from cubes (Microsoft Analysis Services)! The user would not be accessing the Business Intelligence Studio, etc. but would be viewing the data from a custom made application developed in VB.Net2005.
While implementing this, I want to populate the drop-down-list in the VB(.Net) Form, by retrieving the various hierarchies in the dimensions of the cubes (along with dimensions if possible). This should be done dynamically and in real-time!
Can you please help in implementing this? Any code/method, etc would be highly appreciated!
Thanks in advance.
Best wishes!
(Software : SQL Server 2005 Enterprise Edition (with Analysis Services and BI Studio), VS.Net 2005 Enterprise Edition, ADOMD.Net)
View 1 Replies
View Related
May 5, 2005
Good day all
I am unable to crosstab different alternate hierarchies of an MSAS cube from a single dimension, I'm using Cognos Powerplay (7.3 web and client) to browse the data. v 7.1 displays the same behaviour.
When I try to create the crosstab the display replaces both rows and columns to the alternate path, it overwrites the original path. Can anyone tell me if this is a Cognos issue or an Analysis services one and if there is any way around it. The only solution I have at the moment is to bodge it by creating seperate dimensions for each alternate hierarchy however this is ugly and difficult to use.
Regards
Mike
View 7 Replies
View Related
Jul 2, 2015
I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
View 2 Replies
View Related
Sep 20, 2011
I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as
For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with
member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
View 11 Replies
View Related
May 5, 2008
Hi y'all I think i have some problem in here perhaps you guys can help.I have this code: 1 public void AddQstrWhere(ref string TmpStr,string Parameter)
2 {
3 if(TmpStr=="")
4 {
5 TmpStr +="WHERE"+Parameter;
6 }
7 else
8 {
9 TmpStr +="AND"+Parameter;
10 }
11 }
12
13
14 public string querySlsPerson;
15 //public string queryLastSls;
16 public ArrayList CLSGetSalesman(string szSalesmanID)
17 {
18
19
20 // TODO: Add CLSReportManager.CLSGetSalesman implementation
21 BOSTx Tex = GetTx(CLSReportConstants.TrnMasterID.FQN2,"QueryRptSales");
22 try
23 {
24 string querySalesPerson = "SELECT dbo.BOS_GL_Workplace.WpszState, BOS_PI_Employee_1.szName, dbo.BOS_PI_Employee.szName AS Expr1, dbo.BOS_PI_Employee.szEmployeeId, "+
25 " dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription "+
26 " FROM dbo.BOS_PI_Employee LEFT OUTER JOIN "+
27 " dbo.BOS_GL_Workplace ON dbo.BOS_PI_Employee.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN "+
28 " dbo.BOS_PI_Division ON dbo.BOS_PI_Employee.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN "+
29 " dbo.BOS_PI_Team ON dbo.BOS_PI_Employee.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN "+
30 " dbo.BOS_PI_Employee BOS_PI_Employee_1 ON dbo.BOS_PI_Employee.szSupervisorId = BOS_PI_Employee_1.szEmployeeId ";
31 //"WHERE dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
32 //" WHERE BOS_GL_Workplace.WpszState = '" + szProvince + "' AND BOS_PI_Employee.szName = '" + szsalesPrsn + "' AND BOS_PI_Employee_1.szName = '" + szSupervisor + "'";
33
34 string wheretext = " dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
35 string queryLastSls;
36 //AddQstrWhere(szSalesmanID,wheretext);
37 if(szSalesmanID==string.Empty)
38 {
39 queryLastSls = querySalesPerson;
40 }
41 else
42 {
43 //string querySlsPerson;
44 AddQstrWhere(ref queryLastSls,wheretext);
45
46 queryLastSls = querySalesPerson + querySlsPerson;
47 }
48
49 OleDbCommand comd = Tex.GetTextCommand();
50 comd.CommandText = queryLastSls;
51
52 DataTable slTable = new DataTable();
53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd);
54 adapt.Fill(slTable);
55
56
57 ArrayList DataSalesArr = new ArrayList();
58 foreach(DataRow row in slTable.Rows)
59 {
60 CSDatasales sls = new CSDatasales();
61 sls.sz_province = row["WpszState"].ToString();
62 sls.sz_supervisorname = row["szName"].ToString();
63 sls.sz_salemanname = row["Expr1"].ToString();
64 sls.sz_salesmancode = row["szEmployeeId"].ToString();
65 sls.sz_Divisi = row["Divisi"].ToString();
66 sls.sz_typeOp = row["szDescription"].ToString();
67
68 DataSalesArr.Add(sls);
69 }
70 Tex.CloseConnection();
71 //AddQstrWhere("",wheretext);
72
73 return (DataSalesArr);
74
75 }
76 catch(Exception ex)
77 {
78 throw ex;
79 }
80
81 }
82 You see I want to make the query more dynamic. So instead of making the SQL parameter permanent I wanna make it dynamic, so the usercan choose whether they want to use the parameter or not. I already declare the parameter which is szSalesmanID. There's a function called AddQStrWhere which can make the query more dynamic. But the thing is when I wanna use the parameter it seems the code doesn't take it, it just use the regular query I dont know why.I already use the function but it just wont work, can you guys tell me what's wrong, Please I really do need some help from you guys, feelin; a lil' bit desperate in here.I appreciate any kinds of help, thanks. FYI: I use Visual Studio.NET 2003, SQL Server 2000.Best Regards.
View 2 Replies
View Related
Jul 22, 2003
Hi!
How to Implement scd type 1 and 2 functionality using DTS.
Implemented the functionality using T-SQLS, but want to make use of DTS. Can we do this using DTS without using T-SQLs.
Thanks
View 5 Replies
View Related
Mar 28, 2008
Here's my table:
----------------------------
News
----------------------------
ID | Headline | Article
----------------------------
I want the user to be able to search for keywords in articles. Here's my stored procedure:
CREATE PROCEDURE sproc_GetNewsSearch
(
@keyword varchar(50)
)
AS
SELECT Headline, Article
FROM News
WHERE Article LIKE '%' + @keyword + '%'
This will work fine if I search with one word. If I enter "zebra", it will find the articles containing "zebra".
The problem is when I search for more than one word. If I enter "monkey zebra", it won't search for articles containing "monkey" and "zebra", it will look for "monkey zebra".
How can I fix that?
View 6 Replies
View Related
Oct 23, 2006
I have about a half-dozen insertion tasks that depend on a condition: SQLServerProcessDate == OLEDBprocessDate. If the task executes early, old data gets inserted and needs to be backed out later on. (This happens about two-three times a month.)
The jobs start at 8:00 PM, but on occasion processing runs late. In order to make the task intelligent enough to not insert old data, I've thought of a couple options:
1.) Separate scheduled job runs continuously, comparing process dates. When the dates match, it sends a message to the other jobs. Pro: less repetitive. Con: another scheduled job?!? I'll have to learn the message queue task; a little bit complex; flood of database activity on message reception may slow system down
2.) Compare the dates in each individual task; if they're not the same, sleep for a while (15 min.) Pro: simpler. Con: I haven't found any way to pause/sleep/wait a task based on a condition; repetitive functionality in multiple tasks
Any opinions on which is the better choice?
View 7 Replies
View Related
Oct 12, 2006
I am writing my own SSIS package host and I want to create custom logging and event trapping in the host. My goal is to reroute logging calls to the patterns and practices logging block and control the logging destinations using the config file.
Here is an excerpt of code from my main method of the host:
// recurse through all DtsContainers in package
//and enable logging, set filter kind to Exclusive, and clear filter list
ConfigureLogging(package);
// LoggingEventHandler inherits from Microsoft.SqlServer.Dts.Runtime.DefaultEvents
LoggingEventHandler eventHandler = new LoggingEventHandler();
// LoggingBlockLogger implements IDTSLogging
LoggingBlockLogger logger = new LoggingBlockLogger();
DTSResult result = package.Execute(null, null, eventHandler, logger, null);
The behavior I am looking for is that regardless of what logging providers are configured in the package, all the providers will be bypassed and all logging will be passed through my custom LoggingBlockLogger class. However, I find that if there were any providers configured in the package, they are being passed logging events. I tried returning 'false' from LoggingBlockLogger.Enabled, and I'm returning an empty array from LoggingBlockLogger.GetFilterStatus().
I followed the stack trace from the point of entry into the package log providers and found that they were being called directly from ManagedWrapper.Log. Why are the package providers still being called? What is the purpose of the IDTSLogging interface if every container simply passes log entries to the providers in its LoggingOptions.SelectedProviders collection?
Thanks!!!
View 2 Replies
View Related
May 21, 2015
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
View 13 Replies
View Related
Jun 19, 2006
We're trying to implement an audit mechanism to track all changes made to our database and would appreciate any suggestions or alternate ways to do this, we're using the regular trigger based approach.
We also need to capture the user name of the current logged in user - this is NOT the sql server database user, it is at the application level, the user who logs into our site. We were planning to pass this username using context_info in SQL SERVER through triggers as described here - http://www.sqlservercentral.com/columnists/spustovit/easyauditingasharedaccount.asp
Alternatively I could add a userId field to every BC and store the logged in user's name there but I'd rather avoid that.
View 2 Replies
View Related
Mar 26, 2008
I'm not trying to do anything too fancy; given a string, I just wanna see if anything in the column of my database matches the string. I'm using an SQL query that takes a string, then selects the data using LIKE %searchword%.This works fine when the user enters only one word. But I guess you can see that a problem arises when they enter more than one word.So how can I implement a very simple search that will take more than one word?
View 5 Replies
View Related
Mar 4, 2006
Hi there, I am a little confused with some data ideas in .NET 2.0.
First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions?
Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself.
If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say?
Thank you very much!
Tristan
View 3 Replies
View Related
Feb 26, 2008
I am working on a project where I want to implement service broker. A lot of the examples that I have seen are all based on T-SQL. Here are my questions.
1. I have a web page that allows a user to type in an order. How do I put a message on the queue using a .NET assembly? Is there some sort of API set? Do I just pass the message to a stored procedure that puts the message on a queue?
2. Are there any samples that illustrate how to read messages off a queue using a console application or windows service? Is it possible to read the messages off of the queue with a console application or do you have to subscribe to the queue activation event and write a service program that runs under the control of service broker? All of the data that I need to fill the order has to come from a DB2 database on the mainframe. Therefore, I am stuck with creating an external application that processes the messages.
3. In order to use Service Broker, do I have to install Notification Services as well?
View 3 Replies
View Related
