Replication Question About Job Names And Categories

Jul 23, 2005

I have a TSQL script to add daily tables to replication and then run
the snapshot agent to distribute them to two subscribers. The script
executes without errors, but when I check the running jobs for each
server I see the following:

JUST AN EXAMPLE
Job1'Category' REPL-Snapshot
Job2'Category' REPL-Distribution

What is the difference between these two categories? Also 'Job1' works
properly and receives the 3 new replicated tables, while 'Job2' seems
to be stuck on Step 2 and isn't receiving the 3 new replicated tables.

Below is a copy of the stored procedure for reference.

GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS OFF

GO
CREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@@IOI_TABLE
varchar(64), @@TRADE_TABLE varchar(64), @@CHAT_TABLE varchar(64) ) AS

DECLARE @SUBSCRIBER_NYPROD2 varchar(64)
DECLARE @SUBSCRIBER_CTDEV2 varchar(64)

DECLARE @SP_INSERT_PREFIX varchar(24)
DECLARE @SP_DELETE_PREFIX varchar(24)
DECLARE @SP_UPDATE_PREFIX varchar(24)

DECLARE @INSERT_SP varchar(24)
DECLARE @DELETE_SP varchar(24)
DECLARE @UPDATE_SP varchar(24)

SET @SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'
SET @SUBSCRIBER_CTDEV2 = 'D02'

SET @SP_INSERT_PREFIX = 'CALL sp_MSins_'
SET @SP_DELETE_PREFIX = 'CALL sp_MSdel_'
SET @SP_UPDATE_PREFIX = 'CALL sp_MSupd_'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@IOI_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@IOI_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@IOI_TABLE

DECLARE @SCHEMA_OPTIONS int
SET @SCHEMA_OPTIONS = 0x000000000000CEA3
exec sp_addarticle @publication = N'Indii', @article = @@IOI_TABLE,
@source_owner = N'dbo', @source_object = @@IOI_TABLE,
@destination_table = @@IOI_TABLE, @type = N'logbased', @creation_script
= null, @description = null, @pre_creation_cmd = N'drop',
@schema_option = @SCHEMA_OPTIONS, @status = 16, @vertical_partition =
N'false', @ins_cmd = @INSERT_SP, @del_cmd = @DELETE_SP, @upd_cmd =
@UPDATE_SP, @filter = null, @sync_object = null, @auto_identity_range =
N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@IOI_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@IOI_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@TRADE_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@TRADE_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@TRADE_TABLE

exec sp_addarticle @publication = N'Indii', @article = @@TRADE_TABLE,
@source_owner = N'dbo', @source_object = @@TRADE_TABLE,
@destination_table = @@TRADE_TABLE, @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd =
N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,
@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =
@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,
@auto_identity_range = N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@TRADE_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@TRADE_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@CHAT_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@CHAT_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@CHAT_TABLE

exec sp_addarticle @publication = N'Indii', @article = @@CHAT_TABLE,
@source_owner = N'dbo', @source_object = @@CHAT_TABLE,
@destination_table = @@CHAT_TABLE, @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd =
N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,
@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =
@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,
@auto_identity_range = N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@CHAT_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@CHAT_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

DECLARE @SNAPSHOT_JOB_NAME varchar(64)

-- Run Snapshot for NY2 server
SET @SNAPSHOT_JOB_NAME = 'INNYWPP01PRODUCTION-Indii-Indii-1'
EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME
-- Run Snapshot for CT2 serve
SET @SNAPSHOT_JOB_NAME = 'innywpp01production-Indii-Indii-D02-3'
EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME

PRINT 'added ' + @@IOI_TABLE + ' from replication'
PRINT 'added ' + @@TRADE_TABLE + ' from replication'
PRINT 'added ' + @@CHAT_TABLE + ' from replication'
GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS ON
GO

View 1 Replies


ADVERTISEMENT

How To Get All Sub Categories For All Parent Categories

Jan 18, 2012

I have a table Category whose structure is as follows:

id
name
parentid

So i have number of nodes from the root of a specific category. How to get all the sub categories(ie nodes) for all parent categories?

View 14 Replies View Related

Categories And Sub Categories Best Practice

Aug 9, 2007

I have an application which has a database table called Category. Its very simple, with fields id and categoryname.

My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.

Is this the best way? Or something else.

Thanks

View 2 Replies View Related

Replication :: Reserved Column Names?

Mar 8, 2010

I am attempting to define a Transactional Publication with Updateable Subscriptions.

One of the articles in the definition has a column called "insertDate" The attempt fails with"A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSmakeconflicttable_ sqlclr" System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name insertdata in table 'conflict_ <Publication Name>_<TableName>' is specified more than onceDoes sp_MSmakeconflicttable_sqlclr attempt to create a derived table with this additional column.

Must I rename my column.Are there any other names I should watch out for (other than the standard reserved keywords).

View 8 Replies View Related

Replication Monitor Bug - Subscription Names Suddenly Not Displaying?

Apr 23, 2007

This is strange.



We use Merge (pull) replication to get data from the "mother ship" updated to the laptops of 15 sales reps before they head out into the field, where they work disconnected. At end of day, they all reconnect and all the data is once again shared.



We've been having problems with the replication, but that's another topic.



This morning, I logged onto the server to check the replication monitor, and found that all of the subscriber names for that particular subscription are blank! They show the open/close bracket ( [] ) followed by the database name. This is under the My Publishers >> [server name] >> [publication name] node in the explorer tree. If I try to double-click on one of the subscribers to get their detail, I get tne message, "Replication Monitor could not open the Detail Window." and "Specified cast is not valid".



If I look at the publication in SQL Server Management Studio, all of the subscriber names show up just fine. But they do NOT show up under Replication Monitor.



No one touched the database over the weekend, and this was working just fine on Friday afternoon.



Has anyone seen this before? What to do?

View 3 Replies View Related

Categories...

Feb 17, 2007

Hi,
i'm devoloping a web application that contains categories for example
Category1
   Category11
       Category111
          Category1111
           ...........
       Category112
   Category12
Category2
categories with categories with categories...  someone can help me about designing an elegant table for this situation.
 
Thanks
Fdo.

View 2 Replies View Related

How To Add Sub-categories???

Oct 27, 2004

Hi all,

Can any body tell me how to add sub-categories to a database (MS SQL Server 2000) please.

I have a db with the following tables set:
Products: ProductID, CategoryID, ProductName…
Categories: CategoryID, CategoryName …

If a sub-category needs to be added how would I add it and what fields would it contain and the relationship between the Products and Category Table be?
Also any useful tips on this would be appreciated.

TIA

View 3 Replies View Related

Categories And Sub Categories

Apr 23, 2008

Hello,

I am working on a web site that makes documents available to students.
I am using tags to classify each document content.

So I have the following tables:
Documents, Tags, DocumentsTags

I also need to categorize the documents:

Subject
|---- Level
|----- Year

For example:

Document_1 - Math > University Level > Second Year

How can I create a table to create such classification?

Thank You,
Miguel

View 2 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Integration Services :: Chinese Names Export To CSV - Garbled Names

Aug 11, 2015

As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).

I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.

Here is the connection manager setup

There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?

View 4 Replies View Related

SQL Agent Job Categories

May 6, 2005

Hi,

Is there a correct way to add to the list of Categories shown in the SQL Agent Job properties dialog? I have many jobs on my servers that are [Uncategorized(Local)] as there isnt a Category title that is applicable.

Do i just insert into sysCategories?

thanks

View 2 Replies View Related

Synchronize Categories

Sep 13, 2007

Hi,

I am trying to create a type of synchronization but I am having a lot of problems to make this work.

I have 3 tables:

CREATE TABLE Articles (ArticleId INT, ArticleText NVARCHAR(512))

CREATE TABLE Categories (CategoryId INT, CategoryText NVARCHAR(512))

CREATE TABLE CategoriesInCategories (ArticleId INT, CategoryId INT)

Consider the following tables content:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2


I need to create the following procedures:

CreateArticle

Create Procedure [CreateArticle]
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Create the article in table Articles

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

For each category check if there is already one with the same CategoryText in table Categories.

If there is just get the CategoryId and add a new record into CategoriesInArticles with ArticleId and found CategoryId.

If there isn't any then create the new Category into table Categories and then add the new record to CategoriesInArticles.


DeleteArticle

Create Procedure [DeleteArticle]
@ArticleId INT
As
Begin
...
End

This procedure should the the following:

1. Delete the article in tables Articles and CategoriesInArticles given its ArticleId

2 .Delete categories from table Categories that are no longer associated with any other article (in table CategoriesInArticles)


UpdateArticle
Create Procedure [UpdateArticle]

@ArticleId INT,
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Update the values into Articles Table

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

I think the easiest way is to delete all the categories from ArticleId as it is done in DeleteArticle.

Then It creates the new categories as it is done in CreateArticle.

Consider I create a new article:

EXEC CreateArticle @ArticleText = "Book launched at sports event" @ArticleCategories = "Entertainment, Business"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

3 Business

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 2

3 3

NOTE: Because there is no category named Business in Categories this must be created.

Then I would updated it:

EXEC UpdateArticle @ArticleId = "3" @ArticleText = "Book launched at sports event" @ArticleCategories = "Sports, Tech"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

4 Tech

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 1

3 4


Note: Since Business category is no longer used by any other article then it is also deleted from Categories.

The new Tech category is then created and associated with the updated article as well as the existing category Sports.

Finally I would delete it:

EXEC DeleteArticle @ArticleId = "3"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

Note: Since Tech category is no longer used by any other article then it is also deleted from Categories.

The Sports category is not deleted from Categories since it is used by Article with Id=1

Well, I hope I didn't miss anything and I explained it well.

I have been trying my code, either by creating a separate procedure named SynchronizeCategories or inside the Create, Delete and Update procedure.

Until now I was not able to make this work.

Here is the code I use in my SynchronizeCategories procedure:

INSERT INTO CategoriesInArticles(CategoryId, ArticleId)
SELECT c.CategoryId, @ArticleId
FROM Categories c INNER JOIN CSVTable(@ArticleCategories) ac
ON c.CategoryText = ac.String
LEFT JOIN CategoriesInArticles cia
ON c.CategoryId= cia.CategoryId
WHERE cia.CategoryId IS NULL

INSERT INTO Categories(CategoryId, CategoryText)
SELECT cia.CategoryId, NULL
FROM CategoriesInArticles cia
JOIN [Categories] c ON c.CategoryId = cia.CategoryId
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
WHERE c.CategoryId IS NULL

DELETE c
FROM Categories c
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
LEFT JOIN CategoriesInArticles cia ON c.CategoryId = cia.CategoryId
WHERE cia.CategoryId IS NULL

Could someone, please, help me out with this?

Thank You Very Much,

Miguel

View 4 Replies View Related

Search Categories

Oct 24, 2007

I have two tables can I print the Description from Both of them as a single Column from a select statement

Something Like
Select Trade_Category.iCategory_Name as Names,
FreeCategory.sName as Names From Trade_Category,FreeCategory Where
FreeCategory.iParent = 0 OR Trade_Category.iParent_Category = 0

But instead of two columns

Names Names
A B
A B
A B
A


Just the One
Names
A
A
A
A
B
B
B

View 2 Replies View Related

Multiple Categories In Same Row

Apr 20, 2007

Hello folks,

I've been pounding on this for a while, after countless searching I have to break down and ask for help.

I have a table set up like so:

+-----+------------+---------------+------------+
| id | title | path | category |
+-----+------------+---------------+------------+
| 1 | Firemen | firemen.pdf | CGL |
| 2 | Garages | garages.pdf | E&O |
| 3 | Auto | autos.pdf | EPLI |
| 4 | Garages | garages.pdf | CGL |
| 5 | Houses | houses.pdf | E&O |
+-----+------------+---------------+------------+



If there's a title that has more than one category (Garages for example), I would like to be able to show both categories in the same row.

Like so:

Title: Firemen
Path: firemen.pdf
Cat: CGL

Title: Garages
Path: garages.pdf
Cat: E&O, CGL

Title: Auto
Path: autos.pdf
Cat: EPLI


I assume that it might be a better set up if the category was a separate table, but I was hoping there is a way around it. I've tried grouping and subqueries but can't seem to get anything to work.

If someone could steer me in the right direction, it would greatly be appreciated. Thanks much and happy Friday!

Erik

View 2 Replies View Related

Categories Table

Jun 10, 2007

hi to all;



i want to make in my site the possibility for the user to make categories in the site



ex: he can add the category name"computer science" and then can add the category childs " data mining, software engineering,... "



so firstly i want to make a category table in my databasde to be able to save the category data

haw can i make this table? i mean what are the columns that should be in the table?what type of relationship should i make? and how to mahe it



sorry but i need any one to explain to me what should i do. and if anyone one know the solution please reply to me



thanks....

View 1 Replies View Related

How To Get The Top 10 Categories In Chart

Sep 27, 2007



Hi,

I would like to know how to get the Top 10 Categories from Reporting Services chart without using the scripting in T-SQL to query from db.
Is there a function like Ranking and then we filter the Ranking to be less than or equal 10 ?
Please kindly direct me in step by step to the way on how to achieve this as I am quite new in Reporting Services

Thank you

best regards,
Tanipar

View 6 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

Database Design Of Categories

Nov 24, 2007

I am creating a website that has categories such as:Sports -> Soccer -> Soccer ShoesI can not think of an effectively way to implement this into a database. Please help. 

View 4 Replies View Related

Assigning To Multipule Categories

Aug 18, 2004

Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it

When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.

If you need more clarification, post here and let me know.

Thanks in Advance,
Aaron Hawn (aaron@ionzion.net)

View 2 Replies View Related

Counting Items In Categories

Oct 28, 2007

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

Like

Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)....

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName

Which Produces

Real Estate12401 12
Extreme Sports3 4

I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports3 4
Cycling 5 0

View 2 Replies View Related

Design Issue (Sub Categories And Others)

Jun 15, 2008

I want your expert opinion to find out the best design solution to this my current issue.
1 . I have a Member Table. These members are sub categorised and again and again. So I have three three category tables which are related and all three tables directly related to the Member table via Foreign keys.
IS THIS THE CORRECT DESIGN??

2. I Have a SKILLS table where Members can select their SKILLS from that. I record this in a separate table called "MEMBER SKILLS". There is a SKILL category called "OTHER", then the Member can write those skills which may be not listed in the SKILLS table. I have created a separate column in the "MEMBER SKILLS" table it self to record this free text.
IS THIS THE CORRECT DESIGN??

3. How do you resolve a design issue where there are many nested tables of categories and sub categories and the use can create these categories dynamically as well??

I hope all above make sense to you to give a logical answer. Thank you very much for you assistance.

View 2 Replies View Related

Need To Select No Product Categories

Jan 19, 2014

I want to get a list of any Categories where ALL the products in that Category are not published (Published = 0). (I want to get the Categories where no products are listed for it). Here are the tables, not sure where to begin :

SELECT [Id], Published
FROM Product
WHERE Published = 0

SELECT [Id] ,[Name]
FROM Category

SELECT [Id] ,[ProductId] ,[CategoryId]
FROM Product_Category_Mapping

View 3 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
News
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View 1 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
News
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View 4 Replies View Related

Adding Record To Multiple Categories

Jun 16, 2006

I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables:
FORMS and UNITS
I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units.
I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it.
any help???
below is the stored procedure I was using:
CREATE PROCEDURE dbo.USP_AddForm

@UNIT_IDint,
@F_TYPE varchar(20),
@Titlevarchar(100),
@Keywords ntext = NULL,
@Descriptionntext = NULL,
@FileNamevarchar(50)


AS
BEGIN

SET NOCOUNT ON
DECLARE @NewFOrmID INT
INSERT FORMS

(
UNIT_ID,
F_TYPE,
Title,
Keywords,
Description,
FileName,
RevDate
)

VALUES
(
@UNIT_ID,
@F_TYPE,
@Title,
@Keywords,
@Description,
@FileName, GetDate()
)

SELECT @newFormID = @@IDENTITY
SELECT newFormID = @newFormID
END


GO

View 1 Replies View Related

Finding Missing Items That Are Not In Categories

Aug 25, 2014

I am creating a report that will identify the website categories that our items are in. The purpose is to find items that are not in categories that they should be so they can be fixed. Attached is a csv of a subset of the data.

The data I attached has all of our Baskets by SKU (ItemNoSKU) and the associated web categories that those items are in. For example, we can see that ItemNoSKU AB107 is in web categories 4, 22, and 23.

What I have already done is get a Total Baskets vs Web Cat Baskets. I know the total baskets is 44:

Code:
SELECT MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY MerchCatDesc, MerchSubCatDesc

And I know the number of baskets in WebCat 23 is 43:

Code:
SELECT WebCatCd,MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
FROM myTable
GROUP BY WebCatCd, MerchCatDesc, MerchSubCatDesc

So in this instance, I know there is 1 Basket ItemNoSKU that is not in WebCatCd 23. I need to list this "missing" ItemNoSKU along with the WebCatCd it is missing from. I am struggling on how to write the code to accomplish this for my full list of many categories and web categories.

View 11 Replies View Related

Application / User Defined Categories?

Mar 9, 2004

I have about 10 required (never to change) categories in my tbAccountCategories table, but after that, the user can add as many
as he/she wishes. I use an identity field to identify each record. The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?

Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?

Mike B

View 4 Replies View Related

Ascending Categories And Ascended Subcategories

Apr 18, 2014

I've been trying some examples on line and have not been able to get this to work.

Here is my table followed by what I am trying to output.

Id | ParentId | CategoryName

33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges

[Code] ....

I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories

48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges

[Code] ...

This is what I have currently, still a long way off :/

SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name

View 2 Replies View Related

DB Mirroring: Different Server Names With Same Instance Names

Jan 31, 2008

I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?

For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.

Thanks!

View 4 Replies View Related

Column 'coloumname' Does Not Belong To Table Categories .

Dec 22, 2007

I have a simple Stored Procedure with multiple select statements..doing
select linkid,linkdesc from links;select cateid,cate from categories... for example.
I created a dataset and then fill the tables see the code below ,CS  DataSet ds = Obj.GetDataSet("query here);            dlc.DataSource = ds.Tables[0];            DataTable llist = ds.Tables[1];            if (llist.Rows.Count > 7)            {                dr = ds.Tables[1].Rows[7];                dr["cateid"] = 0;                dr["cate "] = "more...";                            }            else            {                System.Data.DataRow newDataRow = llist.NewRow();                                newDataRow["cateid"] =0;                newDataRow["cate "] = "more...";                llist.Rows.Add(newDataRow);                            }  lls.DataSource = ds.Tables[1];DataBind(); .ASPX<asp:DataList ID="lls" RepeatColumns="4" runat="server" RepeatDirection="Horizontal"                                CellPadding="4" CellSpacing="4" BorderWidth="0" Width="100%">                                <ItemTemplate>                                    <asp:HyperLink  Text='<%# Eval("cate") %>' CssClass='<%# "mcs" + Eval("cate").ToString()%>' NavigateUrl='<%# PickURL(Eval("cateid").ToString(),Eval("cate").ToString()) %>'                                        runat="server" />                                </ItemTemplate>                                <FooterStyle HorizontalAlign="right" />                            </asp:DataList> but  ITS WORKING FINE SOME TIMES ONLY , FEW TIMES ERROR OCCURRED  THE ERROR IS  Column 'cate' does not belong to table
Table1. I DON'T KNOW WHY THIS ERROR IS  COME ANY BODY HAVING SOLUTION TO RESOLVE THIS PROBLEM  DETAILED ERROR MESSAGE: Server Error in '/' Application.Column 'lname' does not belong to table
Table1.Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more information
about the error and where it originated in the code.Exception Details:
System.ArgumentException: Column 'lname' does not belong to table
Table1.Source Error:An unhandled exception was generated during
the execution of the current web request. Information regarding the origin and
location of the exception can be identified using the exception stack trace
below.Stack Trace:[ArgumentException: Column 'lname' does not
belong to table Table1.]System.Data.DataRow.GetDataColumn(String columnName)
+1887619System.Data.DataRow.set_Item(String columnName, Object value)
+14state_common_index.FillIndexPage()
+590state_common_index.Page_Load(Object sender, EventArgs e)
+1203System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o,
Object t, EventArgs e)
+15System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,
EventArgs e) +34System.Web.UI.Control.OnLoad(EventArgs e)
+99System.Web.UI.Control.LoadRecursive()
+47System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Control.LoadRecursive()
+131System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

View 1 Replies View Related

How To Retrieve Data Of A Product Which Falls Under Two Categories??

May 12, 2006

I'm using MS SQL 2000. I developing a shopping cart where on the admin side when we are inserting or updating the products, there is a chance that a single product can fall into two categories therefore i'm giving the users a option of CheckBoxList control for the categories. The checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this??
Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear.
Thanks for your help and time in advance.

View 1 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

UPDATE A Variable To Different Categories By A Single Step?

Jul 23, 2005

Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end

View 6 Replies View Related







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