Fetching XML data from Oracle CLOB data field

I have one Oracle Table space which hav one CLOB type table field -- this field have XML data. Now What I doing with:
I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for XML data. Data is comming properly into MS sql 2000 DB.
Now I would to seprate XML field into another table in MS SQL TABe
Example
XML name,city,age,price field will copy to SQL field name,city..
is this possiable iwith MS SQL.
Arvind
Yes. You can use the OpenXML functionality to shred it (see books Online for
information). One caveat: You need to pass the document to T-SQL via a
stored proc using an NTEXT typed parameter if the data is larger than 8kB.
In SQL Server 2000 you can only do so by calling the stored proc from the
client/midtier and not within the server.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>I have one Oracle Table space which hav one CLOB type table field -- this
>field have XML data. Now What I doing with:
> I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for
> XML data. Data is comming properly into MS sql 2000 DB.
> Now I would to seprate XML field into another table in MS SQL TABe
> Example
> XML name,city,age,price field will copy to SQL field name,city..
> is this possiable iwith MS SQL.
> --
> Arvind
|||HI Michael,
Coould you please give one example so I can user OPEN XML. I'd already setup SQL package to get data from Oracle to SQL table. After that I would like to split XML data from SQL TEMP table to another SQL table.
Arvind
"Michael Rys [MSFT]" wrote:

> Yes. You can use the OpenXML functionality to shred it (see books Online for
> information). One caveat: You need to pass the document to T-SQL via a
> stored proc using an NTEXT typed parameter if the data is larger than 8kB.
> In SQL Server 2000 you can only do so by calling the stored proc from the
> client/midtier and not within the server.
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>
>
|||If you have the data inside a SQL table, then you unfortunately cannot
easily pass it through to sp_xml_preparedocument due to no support for
variables of type TEXT/NTEXT. So you either have to get the data out to
OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
the data from the Oracle database passed into the stored proc directly.
There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...[vbcol=seagreen]
> HI Michael,
> Coould you please give one example so I can user OPEN XML. I'd already
> setup SQL package to get data from Oracle to SQL table. After that I
> would like to split XML data from SQL TEMP table to another SQL table.
>
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote:
|||How DO I pased XML data from oracle to MS SQL thru store proc.
My Oracle Table space have 5 field and 1 clob filed for XML.
--
Arvind
"Michael Rys [MSFT]" wrote:

> If you have the data inside a SQL table, then you unfortunately cannot
> easily pass it through to sp_xml_preparedocument due to no support for
> variables of type TEXT/NTEXT. So you either have to get the data out to
> OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
> the data from the Oracle database passed into the stored proc directly.
> There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...
>
>
|||You would write a midtier program (for example using the Oracle OLEDB
provider) and retrieve the information and pass it through ADO to a stored
proc where you have a parameter per field and an NTEXT parameter for the
XML.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:2F32C6BE-BD3E-4F29-AE93-66DA5CEC914A@.microsoft.com...[vbcol=seagreen]
> How DO I pased XML data from oracle to MS SQL thru store proc.
> My Oracle Table space have 5 field and 1 clob filed for XML.
>
> --
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote:

fetching unique pins...

Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
...
100 1864678198
101 7862517189
102 6356178381
...

10 users request a pin at the same time. What is the easiest/best way
to ensure that the 10 users will get 10 different unacquired pins?

Thanks for any help...Bobus wrote:
> Hi,
> I have a table which contains a bunch of prepaid PINs. What is the
> best way to fetch a unique pin from the table in a high-traffic
> environment with lots of concurrent requests?
> For example, my PINs table might look like this and contain thousands
> of records:
> ID PIN ACQUIRED_BY
> DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 different unacquired pins?

Place a Primary Key or Unique constraint on the PIN column. When a
duplicate error occurs generate a new PIN & try to save the new user row
again. Repeate until success.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Unless you're only using the PIN for a one-time operation - somewhere
you are going to save that PIN (in a table). That table is where you'd
put the Primary Key/Unique constraint.

I don't know what the LIMIT function does. If you want to just update
one row you'd indicate which row in the WHERE clause:

UPDATE table_name SET foo = bar WHERE foo_id = 25

foo_id would be a unique value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/GTdYechKqOuFEgEQLJ/wCgxLHQiPaeDWXwsi5BxBpg6tlKmFoAn0tv
KM3PLa2qdl2KzW3Lp/XFHbiv
=gfzL
--END PGP SIGNATURE--

Bobus wrote:
> Thanks, however, we do not generate the PINs ourselves. We simply
> maintain the inventory of PINs which are given to us from a 3rd party.
> Is there a way in SQL to update a single row ala the LIMIT function in
> MYSQL? Something like:
> update tablename set foo = bar limit 1|||Maybe this

select top 1 ID, PIN from pin_table where acquired_by = <not acquired
value> (NOTE: this could be expensive if you use null to signify Not
Acquired, perhaps a non-null value with an index would help).

update pin_table set acquired_by = <acquired value> where ID = <ID from
select
commit

--or --

set up one table containing the unused pins and one containing the used
pins

then
select top 1 ID, PIN from unused_pin
insert into used_pin values (ID, PIN)
delete from unused_pin where ID = ID

commit|||I successfully used a transactional message queue for a similar
scenario.

Besides, try this:

create table #pins(id int identity, PIN decimal(10));
insert into #pins(PIN)values(1000000000);
insert into #pins(PIN)values(1000000001);
insert into #pins(PIN)values(1000000002);
go
create table #point_to_pins(id int identity)
go
--to get a PIN
insert into #point_to_pins default values
select @.@.identity

use @.@.identity to get the PIN, you will not get any collisions ever|||On 13 Feb 2006 20:00:07 -0800, Bobus wrote:

>Hi,
>I have a table which contains a bunch of prepaid PINs. What is the
>best way to fetch a unique pin from the table in a high-traffic
>environment with lots of concurrent requests?
>For example, my PINs table might look like this and contain thousands
>of records:
> ID PIN ACQUIRED_BY
>DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
>10 users request a pin at the same time. What is the easiest/best way
>to ensure that the 10 users will get 10 different unacquired pins?
>Thanks for any help...

Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK, READPAST)
> WHERE Acquired_By IS NULL
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION
> And to get "first row in line", use:
> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK)
> WHERE Acquired_By IS NULL
> ORDER BY Fill in the blanks
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION

Yet a variation is:

SET ROWCOUNT 1
UPDATE PinsTabel
SET @.ID = ID,
@.Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0

It is essential to have a (clustered) index on Acquired_By.

Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.

Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the responses everyone!

MGFoster: I was asking about the "limit" clause so that I could
implement a solution similar to what Erland recommended. This
guarantees no collisions.

Randy: in your solution, I believe there is a chance that two
concurrent requests will end up grabbing the same pin.

Alexander: clever! It's like an Oracle sequence. But, in our
particular case, we could have a problem of unused pins for
transactions which rollback.

Hugo: that should definintely do the trick.

Erland: yours too! I will try them both out.

Thanks for the help!|||Bobus,

When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> When I was solving a similar problem, I did try out the approaches
> suggested by Hugo and Erland. I hate to say that, but I was always
> getting a bottleneck because of lock contention on PinsTable. Maybe I
> was missing something at that time. I had a requirement to produce
> hundreds of PINs per minute at peak times, so I decided to allocate a
> batch of PINs at a time, instead of distrributing them one at a time -
> that took care of lock contention

Bobus said "But, in our particular case, we could have a problem of unused
pins for transactions which rollback."

This would call for a design where you get a start a transaction, get a
pin, use it for whatever purpose, and then commit. But as you say, you
will get contentions on the PINs here, although it's possible that READPAST
hint could help. I did some quick tests, and it seem to work.

The other option as you say is just to grab a PIN or even a bunch of
them. A later job would then examine which PINs that were taken, and
which were never used, and then mark the latter as unused.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Alexander/Erland: thanks for the tips. I will let you know what issues
we run into, if any.

Best wishes.|||Erland,

I think you are right. I looked up that project, and in fact I did not
try READPAST at all. Having observed poor performance, I implemented
batches, which reduced amount of database calls and as a side effect
took care of lock contention. I immediately got good performance and
did not drill down any furhter.|||Erland's solution seems to work great from our initial tests!

Unrelated question, and probably should be another thread, but have any
of you SQL Server geniuses tried PostgreSQL? Any comments, positive or
negative?|||In 25 words or less: It is nice but has the feel of a college project
where grad students kept addinf things to it based on the last academic
fad or thesis topic. I would go with Ingres, which has a "commercial
feel" and a great optimizer.

Fetching sungle record from join

Hello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul JhaHello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul Jha

Are they 3 identical records, or is there something different about them ? If they are identical you can cheese it with a distinct or a group by.|||which one do you want?|||I've never heard of a sungle record|||Rudy asks the correct question here - which of the 3 corresponding records do you want to return? And the answer "it doesn't matter/any of them" doesn't cut it ;)|||And the answer "it doesn't matter/any of them" doesn't cut it ;)Why? He could simply using MAX() or MIN() to get only one record|||MAX or MIN will of course return only one value out of the joined row

what about "the row with the max value"|||Just checking in, pulling up a chair, putting my feet up on the ottoman, leaning back, opening a beer, putting my 3-D glasses on, and waiting for the show...|||BTW, Brett, a "sungle row" is simply a Single row from amongst a Jungle of rows.|||Or he's from New Zealand|||I just might take a stab at this one.

It sounds like rows from t2 are different in some way. If you had data in t2 having to do with say a person and all of the phone numbers they could possibly have, you would get a different row for every phone number.

This is of course, if I am understanding the question correctly.

Fetching Record From Table

Hi,
I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.

Below is the error that I get when tryong to open the record from SSMS

SQL Execution Error.

Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
--------
OK Help
--------

Thanks,
Rahul Jhawhere's the TOP in that query?|||there is no top in the query......... the system wil generate the query when you try to open the table from ssms...........

Fetching of data

hi,

I would like to know that, I have three instances of the same database at three different servers and I am trying to fetch the data from the select query. "select * from table_name"

I would like to know, whether the order of rows fetched by this query will be different on different servers of sql server or the same order of rows will be fetched.

For me the output is coming different on each server database with he same query . Pls let me know, is there any default order by or it takes it randomly.

Thanks

Gaurav Gupta

By spec, the order of rows are undetermined unless you specify an order by clause. Even multiple identical queries to the same server can return rows in different orders. It may or may not occur, but the order is not guaranteed unless you ask for the data that way.

Fetching duplicate rows uisng IN clause

I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith

Fetching Duplicate Rows

Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:

Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)

Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on

HTH

Marp

[
QUOTE]Originally posted by d_kishan
Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,

do this

[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)

fetching data in a datatable

hello, i'm using asp.net 2 with VB, i have a table in my db named 'exams' and i have 6 columns in this table one for question number and one for the question and 3 columns for 3 answers and a last column for the right answer, now in my page i want to show 7 questions and each question has 3 answers that i can choose between them i want the question appear in datatable and the answers in radiolist i have a little code for that but it shows an error it says "no row at position 1" so hope u guys can help :

Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnetdb.mdf;Integrated Security=True;User Instance=True")
Dim a As New ArrayList(8)
Dim i As New Integer
i = 0
Dim d As New DataSet()
Dim cmd As New SqlDataAdapter("select top(7) question from exams", con)
cmd.Fill(d, "exams")
a.Add(d.Tables.Item(0, 1))
i = i + 1
Dim ta As New DataTable
ta.Rows(i).Item(0).text = d.Tables(0).Rows(0)(i)

and by the way this code is for the questions i still dont have the code for the answers that will appear in a radiolist hope u can help .thanksnobody can help?!!!!|||Are you sure the sql query did return result?
This error always occurs if you specify an index that is out of range.sql

Fetching Data from Table result

This may not be possible but if I don't ask...
I have a SQL query which returns something like :
Category Value1 Value2 Value3 Value 4
A 10 12 14 15
B 8 22 44 55
C 5 33 55 88
I have a table which displays the above data like:
Category Values
C 5
33
55
88
B 8
22
44
55
A 10
12
14
15
Note the ordering of the resultant table. In the Table Properties | Sorting
I have a function which returns the highest value given a set of values.
Hence the sorting is set to Code.HighestValue (Value1, Value2, Value3,
Value4). Sure there's a better way to do this and am open to suggestions.
Finally we come to my problem. I want to display 88 (highest value from
category C) in another part of the report. Is there a way to do this other
than changing the sorting in the SQL query so I can grab the first row?
MartinYeah as I see you have written code to find the highest value.
Just write another function with the same parameters as
HighestValue that returns the highest value.
In your code just compare value 1 ,value 2 ,value 3 ,value 4.
Then use the return value of the function whereever you want.
Hope this helps
>--Original Message--
>This may not be possible but if I don't ask...
>I have a SQL query which returns something like :
>Category Value1 Value2 Value3 Value 4
>A 10 12 14 15
>B 8 22 44 55
>C 5 33 55 88
>I have a table which displays the above data like:
>Category Values
>C 5
> 33
> 55
> 88
>B 8
> 22
> 44
> 55
>A 10
> 12
> 14
> 15
>Note the ordering of the resultant table. In the Table
Properties | Sorting
>I have a function which returns the highest value given a
set of values.
>Hence the sorting is set to Code.HighestValue (Value1,
Value2, Value3,
>Value4). Sure there's a better way to do this and am open
to suggestions.
>Finally we come to my problem. I want to display 88
(highest value from
>category C) in another part of the report. Is there a way
to do this other
>than changing the sorting in the SQL query so I can grab
the first row?
>
>Martin
>.
>

Fetching data from IBM DB2 to SQL Server 2005

Hi,

I am trying to fetch data from IBM DB2 to SQL Server 2005.

The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data.

The datatype of the original field is "Byte Stream".

I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.

I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text".

When I check this box, I am able to see the data correctly.

But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.

Please help.

Thanks and Regards,

B@.ns

I don't knot if this would resolve your problem; but you can still use the ODBC connection in SSIS by using a Data Reader source component.|||

Hi,

Thank you for the reply.

I will try and use the same ODBC connection and get back.

Thanks and Regards,

B@.ns

|||

Thank you Rafael!

This has indeed solved my problem.

Thanks and Regards,

B@.ns

|||

If you still having problems.

1. You can try to create linkserver

2. Create a view of the target table

3. Access it like a SQL server table

Fetching Data from a web service

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

MSDN Student wrote:

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

Its your decision. If its something that will need to be done in many packages then a custom component is the way to go. If its just for this package, calling from the script task will be adequate.

-Jamie

Fetching data from a Flat file in SQL Server2000

Posted - 11/03/2006 : 08:00:37 AM
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>

Fetching an id to use in another task

Hi there,

i have a task that has a ole-db destination. it inserts some data to a table. it's always 1 record that is gonna be inserted.after that the task is finished. however i want to use that same record in the followup-task. is there a way to retrieve the id of that specific record.

Please note that the id of the record is an int-identity from sql-server itself.

at the moment i'm fiddling with "remembering" the inserted data and hopefully it will be a 100%match in the next task, but i have a dark feeling it's not 100%.

is there a common way how to approach this?

The simplest way I can see is to use a Execute SQL task in the control flow to retrieve it after the Dataflow that inserted the row is done.

sql

fetching a string containging character " ' " from Datatable

Hello! I have a problem with selecting some string from a datatable containing character " ' " under the values of some attributes. For example I have a table called Table and attribute type of NVCHAR(255) called SomeString. This attribute contains strings with a charcter " ' " and I am unable to pull out the value using SELECT statement.

For example:

SELECT SomeAttribute FROM Table WHERE SomeString =' He's riding a bike ';

Because character " ' " is reserved for borders of the string and is also a part of my string. Is there any possibility to solve such a "problem"?

Thanx

Just escape the quote char.

SELECT SomeAttribute FROM Table WHERE SomeString =' He''s riding a bike ';

A better thing is using paramitrimized queries. Then you never have to worry about format's or SQL Injection.

It's olso better for the preformance, because you don't need to have to concatenate a string for example:

string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";

No escape characters needed, you doesn't have to think about using a " or not etc.

Parameters are like placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date variable.
DateTime date = DateTime.Now;
// Set query and parameters.
const string query = "SELECT * FROM Table1 WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate", SqlDbType.DateTime);
pMyDate.Value = date;
// Create connection and open it.
SqlConnection dbConn = new SqlConnection("ConnectingString");
dbConn.Open();
try
{
using(SqlCommand dbCommand = new SqlCommand(query, dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add( pMyDate );
// Execute the query and get results.
SqlDataReader reader = dbCommand.ExecuteReader();
try
{
// Walkthrough results.
while(reader.Read())
{
// TODO: Do something with the data.
}
}
finally
{
// Close reader.
reader.Close();
}
}
}
finally
{
// Close connection.
dbConn.Close();
}

|||Thank you!