Showing posts with label record. Show all posts
Showing posts with label record. Show all posts

Wednesday, March 28, 2012

How to use variables as a Counter?

While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).

I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.

If someone knows how to perform this task, please let me know. I would greatly appreciate it.

I think this: http://www.sqlis.com/default.aspx?37 should do what you need.


As will this: http://www.sqlis.com/default.aspx?93

They both do the same thing. One uses script, one is a custom component.

-Jamie

|||

I used the script component and it does exactly what I need it to do.

Thanks, Jamie.

Monday, March 12, 2012

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset fin
d
only works if I issue another select statement for the entire file. Of cours
e
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/d...-us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "examnotes" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset find
only works if I issue another select statement for the entire file. Of course
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.
Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/de...us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "=?Utf-8?B?Qm9iTVRlbm4=?=" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

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.

Wednesday, March 7, 2012

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).V

Hi

I am using SQL 2005, VB 2005

I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works fine

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

My query is how to detail with dates my previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Could someone point to me in the right direction?

Thanks in advance

yazzy:


string insertSQLstring date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Why don't you just directly pass DateTime.Now to the @.Date parameter (SqlDbType.DateTime)?

|||

Sorry,

I am having a hard time trying to figure out what it is that you are trying to do. Convert(datetime,@.Date,3) does NOTHING. @.Date is already a datetime, and the third parameter isn't valid for datetime to datetime ?conversions?.

I think you are trying to store a date into a datetime column? Or is the date field a varchar?

If it's a datetime, then change:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, @.Date,@.IDStaff)";

andchange:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

to:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = Now.Date

*OR* change your insert to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, DATEADD(d,0,DATEDIFF(d,0,getdate())),@.IDStaff)";

and remove the Date parameter completely (Which is the method I prefer, since I use the SQL Server's clock exclusively).

|||

Hi Motley

I am trying to store a date into a datetime column. The thrid parameter is to store the date in 'dd/MM/yy' formate (i.e UK date)

Because I have to store date in the above formate, it is causing when I try to insert/update a record.

Sorry I had not included this information.

Speak soon