Losing double quotes?

Hi,

I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.

I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").

The vast majority of the records are formatted properly, and have the double quotes in the expected locations.

The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...

i apologize for being the worst ever at posting questions here, please let me know if i can add anything

Could you please post your code, its hard to uess what is goind wron without seeing your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

it is over 500 lines, but i could certainly post it once i get to work...

in the meantime, however, perhaps this might be useful:

I set a breakpoint at my function's final return statement and have observed the return value as follows:

Author of "Some random book." Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

This is how I'd like, and expect, the value to appear in my database, but after SELECTING the particular row, I get:

Author of Some random book. Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

I doubt this is much more revealing, and will be happy to post my code later, if thought to be any help.

|||

Another thing to add (probably useless):

The problem only occurs during an UPDATE. If I do:

SELECT dbo.EventBuilder([Title], [Author], [Published]) FROM MyTable

I get the correct output.

...getting desperate here

|||

Check your database hold the same result.

I can't able to understand where & what is your problem occurs..

looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

Loooping through SELECT values in SQL

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.

Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.

Is there a way, I can construct this String without using CURSORs??

Please advice. Thanks

PankajYou should be able to do something like this to concatenate the columns together:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
 myTable

Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1
FROM
 myTable

Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::

And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.

Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03

There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql

Lookup with multiple hits

In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?

Try changing your lookup query to only return the rows you are interesting in.

Code Block

select

colA

,colB

,max or min (colC)

from

tableA

group by

colA

,colB

|||

The lookup component does this by default does it not?

Edit: that is return only the first result that it runs into. Just make sure that you return the correct row first and you should be fine...

Try the following to prove:

select 19000101 as datekey, '01/01/1900' as datename

UNION

select 19000102 as datekey, '01/02/1900' as datename

UNION

select 19000103 as datekey, '01/03/1900' as datename

UNION

select 19000104 as datekey, '01/04/1900' as datename

UNION

select 19000105 as datekey, '01/05/1900' as datename

in an oledb source

and then in the lookup

select 19000101 as datekey, 'myname11' as name

UNION

select 19000102 as datekey, 'myname21' as name

UNION

select 19000103 as datekey, 'myname31' as name

UNION

select 19000104 as datekey, 'myname41' as name

UNION

select 19000101 as datekey, 'myname12' as name

UNION

select 19000102 as datekey, 'myname22' as name

UNION

select 19000102 as datekey, 'myname23' as name

Notice, there is no match for 19000105, it will redirect.

You will return the values

19000101, '01/01/1900', 'myname11'

19000102, '01/02/1900', 'myname21'

19000103, '01/03/1900', 'myname31'

19000104, '01/04/1900', 'myname41'

|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

I believe so. The message about duplicate values is just a warning, not an error. I don't think there's a guaranteed order -- it just picks the first one it comes across.|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

Yes. Lookup is a synchronous component; hence the number of rows in the output is the same than the number of rows in the input. If your lookup query returns more than one row for an incoming row; then 'the first' one would be used; and you don't have control over which one would be used.|||SQL query returns result set of multiple contact persons for multiple companies. On output I need for each company to filter out just one of the contact persons. Result set is sorted (ORDER BY) so that if many contact persons are found per comapny one contact person that should be chosen as highest in order. On input of the Lookup component I've put OLE_SRC component which fetches all the companies.

I couldn't use just GROUP BY because I need columns in resultset which aren't used in aggregate function nor should be be group on.
|||

I would not feel comfortable relying in SSIS picking the 1st from the list. If the query in the lookup component is against SQL Server, Oracle or other RDBS where the rank function I would use a query like:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Notice that you could mimic the result set even without the rank() function; but the query could get little complex|||Great, thank you all, especially to Rafael.

In an OLE_SRC I manually entered SQL command. I had to do it manually because it seems that Query Builder doesn't (yet) support OVER construct - when I click Build Query on OLE_SRC component it says "The OVER SQL construct or statement is not supported.".
|||

Is your source sql server 2005?

If not, you'll have to use another technique to get the row number

Code Block

select

colA

,(

select count(*)

from tableA b

where b.colA <= a.colA

) as RowNum

from tableA a

|||Yes, the source is SQL Server 2005. OLE DB Source component has Query Builder GUI which doesn't support OVER construct. I entered query manually and it works.