Need Category Table Implementation Help

Apr 20, 2008

Hi

I new to sql procedures and I need your advice on below subject. Thanks in advance.

I have products table that has catid (string 50) and subcatid (string 50) I like to transfer the string to another table (categories) and keep only int values in products catid and subcatid.

therefore I created categories table and added 3 fields;

catid int (primary and autonumber)
catname string 50 will be equal to category name in products table
parentid int (if it is a parent category, it will be equal to 0 otherwise if it is a subcategory it will be equal to parentcatid)

so instead writing a vb program I like to ask your advice on better solutions.

please help

Also I am using sql express 2005 and I wanted to practice (learn) sql DTS. is there any way I can download this package and work it with my system?

thanks again
Cemal

View 5 Replies


ADVERTISEMENT

Category/Parent Category Design And Querying

Jun 29, 2005

Hi,

I have a simple table:

Categories
----------
CategoryID
ParentID
Name

I want to associate my Products to a category so that I can search by category....ok.  If I just have a simlpe table:

CategoryProducts
-------------------
CategoryId
ProductId

I can link a product to a category.  Now, if I just link a product to a single category, such as the bottom leaf category:

Self Help / Personal Development / Spiritual / Meditation

I would link a product to the Meditation category.  However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation.  So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation.  My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories.  I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?

Thanks,

jr.

View 5 Replies View Related

Mirroring :: Table Partitioning Implementation

Oct 30, 2015

In our environment we have some "Huge" tables with around 2 million records. Planning to implement table partition.We have 4 LUN's. 

View 3 Replies View Related

How Can I Store More Than One Category In A Products Table?

Apr 16, 2007

How can I store more than one category in a products table?
For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it?
 Thanks
David

View 3 Replies View Related

Join Category Table So That Categories Are In The Result On Only

Feb 22, 2007

SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name

FROM Image, JobTag, Tag, Job, Tagcat

WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID

ORDER BY Image.ImageID, Tag.SortOrder


I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?

View 1 Replies View Related

SQL Server Admin 2014 :: Filegroup By Table Category

Oct 14, 2015

I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...

First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.

So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.

1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?

View 0 Replies View Related

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category

Sep 21, 2006

I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those.  Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID   Category1      Saints2      Falcons3      Bucaneers4      Chargers5      FalconsPlayer Table:ID    CategoryID   Player                 News                                Player Last Updated1            1           Reggie Bush       Poetry in motion                                9/21/20062            1           Drew Brees         What shoulder injury?                        9/18/20063            5           Michael Vick       Break a leg, seriously.                       9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this.  I just don't know how to loop through and display it on a page.  Right now I have two datareaders in the code behind but ideally something like this, I would think the code  would go on the page itself, around the html.

View 1 Replies View Related

Security Implementation With SQL 7.0

Aug 10, 1999

I am trying to understand the way roles/users/permissions fit together in SQl7.0.
I created a user/login FSHUTE than I created a role called Developers and
granted permissions to all the objects in the database.
Then I made FSHUTE part of the Developers role.
FSHUTE is also part of the DBO role.
There were some problems accessing the objects.
If a users belongs to multiple roles are the permissions cumulative?
Or do they replace each other?
Do I need to give to the individual user access to every object?
Since I was completly comfused I ran the SP_helprotect procedure and only the permissions that were associated with FSHUTE displayed.
HELP!!!

View 2 Replies View Related

Where To Put System DB's With SAN Implementation

May 21, 2008

This is my first experience with a SAN and we're about to migrate my SQL server to the SAN. The SAN is a nice product made by HP and worth about $200k, so it's not a cobbled together system...but I can't find any documentation to indicate if I should have the system level db's (master, msdb, and tempdb) running of the local system or the SAN.

The next step is a cluster, which brings forward the same question...do I store the system level db's on the local or the shared storage device?

Anybody have experience with this?

Thanks,

alex8675

View 1 Replies View Related

Implementation Of SCD Without Using A Wizard

May 24, 2007

Hi,



Please someone tell me the procedure of implementing the Slowly changing Dimension without using a wizard.



Thanks,

Chandana.

View 5 Replies View Related

New SQL Replication Implementation

Jul 24, 2007

Hello,

I'm just beginning my R&D on SQL Server Replication for a project and had some questions about what paths to pursue

We have multiple locations which we want to all work off the same set of data essentially, but the kicker is that many of the sites become disconnected for minutes or either days at a time due to their remote locations, so we cannot simply deploy a web application with one SQL backend because remote sites will be unable to work during service disruptions.

Ideally, what I'd like to be able to do is have multiple instances of the SQL server/web applications(one at each site..they all have their own internal network), but which replicates with the main site/all other sites whenever it can(i.e. the internet is available). So one site goes without internet for a few days, they have up to date data from the last replication and they can work off their own sql server/web application, and any updates they've made/or any updates from other sites they've missed during the disconnected period would be replicated when the opportunity arises(ie. the web connectivity comes back)

Is this scenario possible? and I am struggling to find the right strategy path for implementing something like this. Any guidance would be greatly appreciated.

Thanks,

Chris

View 1 Replies View Related

Does SSAS Allow Implementation Of SCD?

May 25, 2007

Can I implement a slowly changing dimension type 2 in SSAS. I am looking at creating an SSAS cube which can pull data directly from an operational OLTP database. The source database does not maitain history of changes for the dimensions, and I wanted to know if SSAS will help me keep that history by defining certain dimensions as a SCD. If so how do I define that rule. All tutorials I have read only skim on that topic and don't describe the steps/ways to define it in SSAS. Any help would be appreciated.

View 1 Replies View Related

Implementation Question

Sep 6, 2007

Hi All!

Not being an ace on T-SQL or working with Sql Server in general beyond the basics, I thought I'd ask for some input/ideas/feedback on an implementation question...

Here's the simple scenario:
I new user is added to a Users table in a Master database. Because of size limitations in Sql Server Express, every user has her own little database. This database gets created at the same time as the new user is inserted into the Users table. The name of this database is stored as a field in the Users table.

How would you go about implementing a scenario like this? Would you implement it at all or solve it in some other way?

I was hoping I coudl write a stored proc. that takes the user data and new database name as parameters, adds the user to the Users table and then creates the new database... Is this feasable? (Some prel. research would suggest that this might not be as simple as it seems...)

Thanks.

View 6 Replies View Related

Regarding.....Full Text Implementation..........

Sep 21, 2007

I have implemented Full text search in my web application.
I am using sql server 2005 database.
I used “contains “Keyword for full text search.
These are syntax as given below: CONTAINS    ( { column | * } , '< contains_search_condition >'     )  < contains_search_condition > ::=         { < simple_term >         | < prefix_term >         | < generation_term >         | < proximity_term >         | < weighted_term >         }         | { ( < contains_search_condition > )         { AND | AND NOT | OR } < contains_search_condition > [ ...n ]         }  
My search gives correct results according to AND NOT and OR.
But, it is not working if I used AND. 
Please give me solution.
Its very urgent for me…… 
 

View 6 Replies View Related

XNOR Condition Implementation

Mar 31, 2008

Hi there,I want to implement XNOR condition in my SQL statement.XNOR =====x  y  = ? ---------0  0  = 10  1  = 01  0  = 01  1  = 1Can any one help me out what operator I should use?BTW what operator do we use for C# or VB.Net?Thank you for any tip.-Ahsan

View 3 Replies View Related

Resources On Security Implementation

Jun 9, 2000

Hi,

a) Thanks for all contributions on this site, they have been very useful but,
i am trying to implement a security procedure including auditing on sql server 6.5,7, sybase and oracle can anyone help?

b) does any one know any site like this that covers issues on oracle ( besides Oracle Technet)?

View 2 Replies View Related

Hiding Implementation/Code

Jul 14, 2004

We are planning to sell a software.. and don't want our clients to access the code or database design.

Is there any way to hide the stored proc/trig code and the database table?

Help is highly appreciated.

Thanks

View 4 Replies View Related

Base Database Implementation

Jan 31, 2006

I am currently looking to buy or build software that enables my firm to:

Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.

The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.

What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?

We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.

View 2 Replies View Related

Learning Microsoft's Implementation Of SQL

Jul 7, 2007

Hi All,

I have Oracle 9i personal edition on my laptop which I use to learn Oracle/SQL including creating and quering tables. With this I have been able to go into jobs confident with being able to query Enterprise implementations of Oracle in a commercial environment.

Now I would like to learn Microsoft/SQL and I was wondering whether Microsoft's SQL Server 2005 Express will be enough for me to learn MS/SQL like Oracle personal edition has.

Regards,

Seaweed

View 12 Replies View Related

Active Directory Implementation

Jul 20, 2005

We are implementing Active Directory. I need to know if this will presentany issues/changes for our SQL Server 2000 servers.TIADave Edwards

View 1 Replies View Related

Variable Array Like Implementation Using T-SQL

Aug 13, 2007

Hi,

I want to perform Oracle's VARRAY like implementation in SQL SERVER. How should I go about it?
I've done an implementation in Oracle where I have a VARRAY variable which stores a number of insert statements where each INSERT statement is a single array element. I then traverse through the VARRAY and executed each INSERT statements inside a loop.

View 3 Replies View Related

Is This Outer Join Implementation Dependant?

Jan 9, 2007

Hi all,
Sorry if it looks a little cluttered!I have these two tables: CAMPAIGN(MEDIACODE varchar(10) UncheckedSPECIALOFFERCODE varchar(15) UncheckedLAYOUT varchar(10) UncheckedHEADERTEXT varchar(100) CheckedSORTORDER varchar(10) UncheckedSORTORDERCOLUMN varchar(50) UncheckedWIDTH varchar(50) Checked)andPROMORATEVIEW(MEDIACODE varchar(10) CheckedSPECIALOFFERCODE varchar(15) CheckedCAMPAIGNCODE varchar(15) CheckedNUMBEROFISSUES smallint CheckedRATE decimal(9, 0) CheckedDESPATCHMETHODCODE varchar(50) Checked)CAMPAIGN HAS ONLY ONE ROW:(CE DG8398 GRID ASC NUMBEROFISSUES NULL)ANDPROMORATEVIEWTOO MANY AND HERE A VERY SMALL RANGE OF RECORDSMEDIACODE SPECIALOFFERCODE CAMPAIGNCODE NUMBEROFISSUES RATE DESPATCHMETHODCODE(...CE CER1R02 CER1 12 429 WCE CER1R03 CER1 24 829 WCE CER1R03 CER1 12 429 WCE DG8398 DG8398 12 411 FCE DG8398 DG8398 12 405 1CE DG8399 DG8399 12 399 WCE DG8399 DG8399 12 735 1CE DG8399 DG8399 12 756 ACE DG8400 DG8400 12 756 ACE DG8400 DG8400 12 396 W...)Now the question:Why these two OUTER JOINS RETURN the same number of rows in My Sql2000 & 2005 express???1.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE2.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE AND CD.SORTORDER IS NULLIf you still with me here is what I am trying to do:to right outer join the campaign with promorateview and the if it's not overriden in campaign( there is no record for MEDIACODE, SPECIALOFFERCODE in campaign ) I will use that view to override the default values.I suspect that the last statement makes no "influence" on right outer join.Another work around for me now is that if I create a view that has these values and then filter it with VIEW.SORTORDER IS NULL it does the job but I am trying to get rid of this extra view.Thanks for your time, avarair

View 8 Replies View Related

Stop Word List Implementation

Sep 26, 2007

Hi,
I would like to introduce stop word list in my search engine tool (web page).  Could anyone please provide me the steps required for implementing this? 
This is basically to filter the stop words used for searching in my search page.  The technologies used are ASP .NET 2.0 using VB .NET, VS .NET 2005 and SQL Server 2000 as the backend.(Note: I have already got the stop word list with me).
Thanks.

View 1 Replies View Related

Regarding SSIS Implementation Difficulties In Migration

May 2, 2008



We would like to Share the BatchJobs functionality before going to the difficulties what we are facing in SSIS package Design and it's implementation.

BatchFile Process Steps:

1.Flat File is coming from the upstream server(DB2 main Frame Server) to the linux box in which the jobs are scheduled using crons.

2.The Flat file is validated whether having header and trailer information.The flat file name itself (eg. APPLWIC00077.dat ) will have it's sequence number ie 00077.This number will be checked against Flat file Header information Which also gives the sequence number information .If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.

3.The Job will process the coming flat file and Loading it into SQL Server Staging Table.

4.If loading is successful,then Some Sql server Procedure are called to validate the Staging Table Data,Filters,then move the Data from Staging table to Target table.

5.All Process or Steps are tracked,If anything goes wrong,Mail will be sent to Concerned Person.

Utilities Involved:

1. SQL Loader to Load the FlatFile Data into the SQL Server 2005 Database.

2. SQLPLUS to execute the PL/SQL procedures.

3. SENDMAIL or MAILX to send mails to externals.

Additional Information:

The SQL Loader Control File Splits the flatfile data by Position wise in order to load it into the particular column of the Staging Table.No column delimiters.Data are taken position wise (like column1 -> Sequence No , 1-3 -> column2 , 4-8 -> column2 )and checked against a condition By which the particular Staging Table is selected.





Difficulties We face in migration in regard to SSIS Package Design and it's Implementation:

1. We could Receive the flat file using FTP Task and we don't know how to open and validate the flat file data Like What we mentioned in STEP 2.

2. We could load the sample flat file which is properly delimited like {CR}{LF} ,{CR},{LF},semi -colon,comma,colon,tab,vertical bar .

If flatfile is messed up and data need to be processed by position wise (Scenario mentioned in Additional Information) ,We don't know how to implement this in SSIS package with the Limited delimiter option given.

FYI : Flat File columns are not fixed and ragged type.

3. We need logging information like howmany rows inserted, discarded,something in detail..How can we log these information using SSIS.This will help to send detailed mail to Application handler.It should be automated using SSIS.

4. How can we execute Procedures Using available SSIS tasks ( Executing many Procedures which is residing in Sql server 2005 using Single sql file as input to SSIS package)



Thanks in Advance!
Your input is priceless.

Regards,
Prabhu.M

View 4 Replies View Related

Disadvantages Of The Implementation Of Explicit Subtypes On MS SQL 7 And Oracle8i/9i

Jul 20, 2005

Could some database expert post some information or link about that?thanksJanusz

View 1 Replies View Related

Real World: Backup Strategy And Implementation, How?

Jan 24, 2007

Real World: Backup Strategy and implementation, how?
A quote:

€œReal World:

Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:


//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups.
//servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups.
//servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups.
//servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.

Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?

I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.

Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.

I would like full details on this please, as I need to get my head around it.

Thanks

Neil

View 6 Replies View Related

Implementation Of A Backend/frontend Architecture Using SYNONYMS

Mar 28, 2008

Hi!
I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.

It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.

Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem:
Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.

I have a sp (frontend) with the following code:
INSERT INTO dbo.TABLE1...
SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()

Well in that case, @SCOPE_IDENTITY is NULL!

Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...

Thanks for your input!
Frantz


View 13 Replies View Related

MCTS 70-431: SQL Server 2005 Implementation And Maintenance

Dec 24, 2006



Hi, I am about to take a MCTS 70-431 exam next week.

I tried to find few information regarding this exam, but I cannot find what I need at Microsoft site.

I wonder if anyone has taken this exam or knows about any information about this exam.

My Questions:

Q1: Number of questions on the exam

Q2: Passing Score or percentange or numbers of right questions

Q3: Format of exam:

Definition, concepts, case study, or simulation?

Q4: What if I fail? Retake policy

Do I pay same amount to take it again?

Any information is really helpful and appreciated.

Thank you so much!



View 3 Replies View Related

Reporting Service 2005 CustomSecurity Implementation

Apr 1, 2008



Hi,
I am facing a problem in Reporting Service 2005 custom security(formsauthentication).
I have implemented Custom Security(using sample code in msdn site) Reporting Service 2005.
When i try to view report from my webapplication(within iframe) it redirects me to Logon.aspx page instead of showing result.If both Reporting Service 2005 and Webapplication is present in the same machine then there is no issues it is working fine, the session is establised. If both are (Reporting Services 2005 and Webapplication) in different in machine then the above mention error is occured ie., it redirects to Logon.aspx page. Please help.



Thanks and Regards,
S.Vallinayaagm

View 4 Replies View Related

TS: Microsoft SQL Server 2005 - Implementation And Maintenance

Jun 18, 2007

I want to prepare for the TS: Microsoft SQL Server 2005 - Implementation and Maintenance exam. I want to know that how can i tackle it in the proper way. Can somebody please give me tips & tricks or study guide?

View 1 Replies View Related

Is There A Forum For Those Of Us Who Study From SS 2005 Implementation And Maintenance (Exam 70-431)??

Sep 21, 2007



Is there a Forum for those of us who study from SS 2005 Implementation and Maintenance (Exam 70-431), a la Wrox P2P user forum??

Thanks.

View 1 Replies View Related

TOP N Value Per Category

Oct 26, 2006

HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit

View 1 Replies View Related

Data Warehousing :: Listing Out Checklist From DBA Perspective For BI Implementation Project

Apr 16, 2015

We are in Designing phase Data Modeling of PDW/APS Implementation Project.

I need making a checklist from a DBA perspective.

Like what are things ill be needing at a time of implementation/Deployment.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved