Showing posts with label fetch. Show all posts
Showing posts with label fetch. Show all posts

Monday, March 26, 2012

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

Monday, March 12, 2012

How to use Like with DataTime ??...

i want to fetch visitors record by data but visiting date field has time also and i want to get by data only because i am unknown to exact time.

i am using this query but record does not comes.

select * from visitors where in_date_time like '03-05-2007%'

where as i am using visitor name instead of in_data_time it works success fully.

plz solve it.

Jasim...

New Delhi...

to account for the time portion of a datetime, you could do it like this:

select * from visitors where in_date_time >= '03-05-2007' AND in_date_time < '03-06-2007'

this would select all records from 03-05-2007 regardless of their time.

Friday, March 9, 2012

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.