how to use UPC database in sql server

I am working in ASP.NET 2.0 and Sql Server 2005, I need to use UPCdatabase for functioning of my website as i need to fetch results fromthis databse by comparing it with the Code entered by the user.I am notsure how to use this UPC database.Can i directly fetch this databaseinformation into my local databse or i need to make calls to the centilizedlocation where the databse is stored and how to do in either way. Do ineed to pay for this or its available freee.

Please provide me the information and solution for this.

Thanks in advance

If the information in the UPC database is not to be updated then just copy the tables into your application database. This will mean one less connection string in use and thus improve performance. If the UPC database will be updated often then either you must set up a DTS job (SQL2000) or SSIS (SQL2005) to import the data, or just connect to it separatly.

As to licencing costs, please ask the vendor.

|||

How can i copy the tables.And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.

Can we import the data into sql server from a text file ? If yes , then how we can do that.

Appreciate your reply.

|||

>And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.
Without a copy of the UPC database I cannot do this.

I did ask you which version of SQL Server you are using?

>Can we import the data into sql server from a text file ? If yes , then how we can do that.
There are facilities within SQL Server to import such a file. How big is it? Can you send a sample via private message to me?

|||

Appreciate your reply.

I am working in SQL server 2005. The file size is approx. 10MB i suppose, didnt have file rite now with me.

|||

If the the records are all very much the same, just post a few (say 10!) and I will have a look at it tomorrow morning.

|||

This is the sample data i have for now.It can be available in Microsoft Word also.

Check it up and let me know the solution for this & let me know if any other information you need from me.I appreciate you response.

0000000000017,,"wrsi river cd"

0000000000093,,"treo 700w"

0000000000123,,"Wrsi Riversound cafe cd"

0000000000161,0,"Dillons/Kroger Employee Coupon($1.25 credit)"

0000000001090,VAR,"Rountech Asset 1"

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

0000000001243,,"CVS Photo 1-Hour 4x6 Finishing"

0000000001601,,"Sainsbury's Red Pepper"

0000000002288,"18 oz","Winn Dixie HandLotion"

0000000002745,,sunglasses

0000000004145,"7.6 cm x 1.8m","ConformingBandages"

0000000005210,,maggi

0000000006217,"1 gal","Trader Joe's 1%Lowfat Milk"

0000000007511,"44oz","Maverik Plastic Soda Cup"

0000000008570,,jewerly

0000000009997,"32 FL OZ","Down to Earth -Goji Berry Juice"

0000000010344,CD,"WNCI Morning Zoo - Oops... We DidIt Again!"

0000000014946,"6.5mm thick - 3mroll","Florist Ribbon - Lemon Yellow"

0000000043816,"1.76 oz","Altoids/Peppermint"

0000000046268,,JICAMA

0000000049887,"1 x 8 oz","Coca Cola:DietCoke"

|||

Looking at

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

I derived the following table structure
/****** Object: Table [dbo].[UPC] Script Date: 06/02/2007 06:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UPC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[A] [char] (13) COLLATE Latin1_General_CI_AS NOT NULL,
[B] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[C] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_UPC] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Identity primary key' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC',
@.level2type=N'COLUMN', @.level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Possible key' ,@.level0type=N'SCHEMA', @.level0name=N'dbo',
@.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'A'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Optional measure size' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'B'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Item description?' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'C'

I will save the supplied data as UPC.TXT

|||

Do i need to run this structure as it in Query analyzer.Is the location of the UPC.txt matters.

Please advise for the procedure to use the above script.

Thanks

|||

>Do i need to run this structure as it in Query analyzer.
Yes you you will need to run the script to create the table.

>Is the location of the UPC.txt matters.
No, the the location does not matter, just so long you can remember where it is.

The ASP.NET site was down when I put in my first reply this morning, so the reasoning concerning the column naming was lost.The column names are arbitrary, other than the Id column. Column A is possibly a unique value.

|||

Do the following:

Right click on the database, select tasks..., import Data|||

I created the table using your script.But i am not able to find the Import/Export functionlality when i right click on my database.I am using Microsoft Sql Server Management Studio Express.

|||

I have just had a hunt on the MSDN site, the screen of of the express range, do not have the Import..., Export... sub options. You will therefore need to write procedural code to:

Read the file record by record,|||

I am able to import tha data.What i done is first imprted the file in Sql server 2000 and then from 2000 to sql sever 2005. And import successed

I appreciate your reply.

sql