Recordset Field Limit??

Hey Everyone,

I have what seems to be a unique problem, which I am not sure if it is an ASP problem or an SQL Server issue. I'm making a website that pulls its content from ntext fields within SQL Server 2000.

Heres my problem, when I pull the data into the page it cuts off the text from the field at a certain spot. I pulled the data into a variable and did some tests on it, if I run length on it I get 1023 and also if I run length on just the recordset field I also get 1023. But if I check the SIZE of the recordset I get a return of 2046. I have had this problem before with an Access database as well but it seems as if the amount of characters that it cut off at with the access database was in the 200's. I've self taught myself ASP so this could very easily be something I have missed. Same for both Access and SQL Server.One mystery is easy enough. ntext is a double-byte system, so 1023 characters takes up 2046 bytes.

The other mystery is a bit harder to figure. What sort of variable are you retrieving the data into? If it is not declared, it is probably a variant datatype, which could top out at 1k. I am not too good with VB6 datatypes, but have you tried casting the variable as VB's equivalent of text?|||Well I'm not using ASP.Net so no type casting, and actually I wasn't originally pulling the data into a variable type, I only done so to do those tests. I am pulling the data onto the page with the following

<%=(recordset.Fields.Item("ntextfield").Value)%>

But I did set a variable equal to that to check the length and size.|||I got nothing on that. Most I have ever worked with in ASP is varchar(1000). If you retreive the value into a variable, I take it you get the same result?

And I suppose there is no possiblity of you not using ntext? Terribly finicky stuff. At least, if you can avoid using chinese/japanese/greek/hebrew letters in your text, you can demote it down to just text. Likely, you can get a way with a varchar(4000) field or something.|||It is like that cuz the data being pulled in can sometimes be up to 10000 characters long... Ugh I bet thats going to cause me major headaches before this is said and done :D

Recordset Destination

Every time I try to us a recordset destination for error output, I have problems with setting the "VariableName" field. Is this a known issue? Or am I just doing something wrong?

Thanks
Jim

Jim,
Can you elaborate on what the problem is? An error message would be helpful.

My only tip based on what you have said is make sure that the type of the variable to which you are assigning the recordset is "Object".

Regards
Jamie|||Here is the text I get

Error 3 Validation error. Data Flow Task: Recordset Destination [332]: The VariableName property is not set to the name of a valid variable. Need a runtime variable name to write to. Package.dtsx 0 0|||Have a look here and see if it helps

This is the June CTP

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)|||In addition to having to point to a variable of type "Object", this property is also case sensitive. You may want to specify the variable's namespace as well.

Other than that I can't think what's wrong because I have had this working!

-Jamie|||Thanks Allan.

Pretty simple and I figured it was something like this. I didn't realize I needed a "variable" to hold onto my recordset.

Jim|||You need to create a variable by providing a name, of type object and also the scope of the variable. Check your object type.

I have written a book for beginners and even if you are not you may benefit from reading it. I recommend as it gives you a more deeper understanding of packages as implemented in the Visual studio 2005

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:
> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
--
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...
No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/
|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>
|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegro ups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

Records not updating in Script Component

I am seeing a strange behavior in a script component that I can't figure out.

Basically, there is a field in my script that checks to see if a string field is length of 1. If it is, it updates the field with some value. The strange behavior is that it picks up some of the rows but not others.

If Row.FieldValue.Length = 1 Then

Row.FieldValue= "Some Value"

End If

I've tried variations such as:
If left(Row.FieldValue.,1) = "-" Then

Row.FieldValue= "Some Value"

End If

But I get the same results. Any Ideas?Does [Field] need to be trimmed? Might some data contain trailing white spaces?|||

No, I've tried every combination of Trim, left, equals etc...that I can think of. The funny thing is that it picks up some of the fields and misses others.

Very puzzling. I was hoping to hear of some rare condition on this board that sounds similar to this, but I have yet to hear of any similar situations.

The code that I am talking about is incredibly simple, as shown in my previous post.

|||What happens when [Field] is null?|||

You could fire some debug iformation to help you. Something like this maybe:

Me.ComponentMetadata.FireInformation(,,Row.FieldValue.ToString() + " - " + Row.FieldValue.Length.ToString(), ....

-Jamie

|||

Thanks, that did it. I am relatively new to SSIS, never really delved into the debug information before.

that is very helpful.

|||

KML67 wrote:

Thanks, that did it. I am relatively new to SSIS, never really delved into the debug information before.

that is very helpful.

Great! But what solved your issue?|||

Basically, matching the string by length, equal, explicit match was working fine all along.

There was some logic that followed this string test that was providing the error. The error was very subtle, so it did not always produce the same value for the same input (depending on multiple variables), but at quick glance (watching the data flow into and out of the component with dataviewers) it looked as like there was an inconsistency in the component itself.

Jamie's post was very helpful, I am new to SSIS and wasn't familiar with the fireInformation method. That allowed me to solve it in a second.

Thanks for the tips, this board is a great asset.

Records NOT in OTHER table - SELECT statement

I am trying to compare 2 tables with similar data. One table has a field named [X Sent] and the other table has a field named [X Setup]. I was trying to extract all of the fields in Table 1 with field [X Sent] that are not yet in Table 2, i.e. field [X Setup]. I was trying some LEFT JOINS and other stuff but I can't seem to get the syntax correct. How can I get a query of only the fields that are not in Table 2, but are in Table 1?

Here are two of my attempts:

SELECT [X Sent], [X Date]
FROM [Table 1]
WHERE ([X Sent] NOT IN
(SELECT [Table 1].[X Sent]
FROM [Table 2] INNER JOIN
[Table 1] ON [Table 2].[X Setup]= [Table 1].[X Sent]))

I don't believe 'NOT IN' is proper syntax!

And my other attempt:

SELECT [Table 1].[X Sent]
FROM [Table 1] LEFT OUTER JOIN
[Table 2] ON [Table 2].[X Setup]= [Table 1].[X Sent]

Any help would greatly be appreciated.An outer join, checking for NULL in [Table 2] will do it.

SELECT [X Sent],[X Date] FROM
[Table 1] t1 LEFT OUTER JOIN [Table 2] t2 ON t1.[X Sent]=t2.[X Setup]
WHERE t2.[X Setup] IS NULL

|||Thanks once again Douglas Reilly!!!!

Greatly appreciative...|||After analyzing the data further, I have noticed that I can't only compare one field, but rather two from each table. I have been trying to work with the following statement, but I can't seem to get it to work. To start off with, I would like to capture all the fields where Field1 and Field2 from Table1 equals Field1 and Field2 of Table2.

SELECT [Field1], [Field2]
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2

Once I figure this part out, I will go ahead and search for all of these tuples not in the other table. Your assistance will greatly be appreciated.|||You should pick one of your candidate keys to be the primary key of Table1. Then you can do this:

select * from Table1
where Table1PrimaryKey not in
(select Table1PrimaryKey from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2)

If for some reason you really need the fields, you can use this:

select Table1.Field1, Table1.Field2
from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2

RecordNumber field Q...

Greetz. . My problem is: I'm kinda unfamiliar with both Crystal Reports and VBasic as well .. and yet, my job position requires me to do the following:

I have a report which draws it's data from an SQL Database .. Depending on the data in the database ( table ), the report may have as little as 1 or as many as 30 or more records generated on it. Now, what I need you guys 2 help me with is .. When the special field RecordNumber reaches a certain value ( 20 for example ), I should somehow ( that's where you jump in ) tell the report to pass the remaining data onto the next page.

Now, I've tried 2 do something like If fldrecno.value > 20 then report.newpage ( where fldrecno is a name of the recordnumber field ) but I keep getting the same message Runtime Error 424, Object Required. As I said, I'm a newbie in both CReports and VBasic (6.0) area .. and any help would be more than appritiated.
If the problem cannot be solved using the recordnumber field, maybe a formula might help. Again .. I don't know much about formulas either.In the Crystal report's Design window,right click the left panel of "Details" ,in
Format section --> New page after -->Formula Editor(X+2 button) , write this code and try

RecordNumber>=(PageNumber*20)
I think you can pass a variable instead of 20...:)|||In the Crystal report's Design window,right click the left panel of "Details" ,in
Format section --> New page after -->Formula Editor(X+2 button) , write this code and try

RecordNumber>=(PageNumber*20)
I think you can pass a variable instead of 20...:)

Otherwise write this code

RecordNumber mod 20=0|||Thnx a bunch! It works .. and it works fine ..

RecordNumber by Group?

Hello,

Is it possible to show a record number or count by group? I know the RecordNumber field will show me the number for the whole report but what I would like is the following:

Group A
1
2
3
Group B
1
2
3

Instead of group B being 4,5,6...

I'm trying to figure out how to number the lines per group heading. I hope that makes sense.

Many thanks,

StephenInsert a count() into the footer and count how you want. It gives the choices|||Thank you for the suggestions but I'm looking to get a count (like a row number) at the detail level however and I don't think I can do it that way (that I'm aware of).|||You have to do it manually for each Group. Increment and show the counter in the detail and reset in the Group Header/footer.|||You can create a running total:

select a field to summarize;
type of summaries: count
evaluate: for each record
reset: on change of group (select one you need)
:wave:

Recordet Problem

I'm creating a Visual Basic program linked to an SQL server DB. In one of my tables I have a field for phone number which is an nvarchar datatype that is stored in the following format (example: 617-782-6415).

I have a form where I want to view the contents of the following recordset:

SELECT d .ACCOUNT_CODE, d .DURATION, d .[DATE], d .[HOUR], d .NUMBER_DIALED, p.ACCOUNT_CODE AS Expr1, p.LAST_NAME, p.FIRST_NAME,
p.SUMMARY_GROUP, p.DIRECT_DIAL, p.TITLE, p.LOCATION, p.DIVISION, p.DEPARTMENT, p.DIRECTOR, p.OLD_CARD_NUM, p.REISSUE, p.CLASS,
p.PHONE
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d .ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE number_dialed = " & astring & "

The value for astring is pulled from a combobox that stores all the values for number_dialed. However when i run this program i get the error message

"syntax error converting the nvarchar value '401-781-7078' to a column of datatype int"

I dont need to convert it to a column of datatype int so I dont understand this error... can anyone help me?The only thing that can be forcing a convertion is

WHERE number_dialed = " & astring & "

What is this tryin to do?
Take one value frm teh combo box?
I assume as you have " as the string delimitter then you are trying to form the select in VB.
You would be better to create an SP and pass the parameter.
For your string here try

WHERE number_dialed = '" & astring & "'"

i.e. making astring into a string in the query.|||Yeah, I'm trying to form the select in VB because I'm more familiar with that way... I'd prefer to do it in VB if at all possible... Why is it converting it?|||WHERE number_dialed = " & astring & "

will give something like

WHERE number_dialed = 617-782-6415

it will evaluate 617-782-6415 giving a negative number then try to convert number_dialed to an integer for the compare.

you need
WHERE number_dialed = '617-782-6415'
to do a string compare
hence the quotes in my previous post.

Record value depending of the next record

Hi,
I am trying to do something with Crystal Reports 9, but I am not sure if it is possible to do it...

how_long is the field I want to add
...depending of how many consecutive True value there is in the active field, I want to know for how many days it is True

when it is False...I put no value in the how_long record

SEE example.txt

I thought I could use the function Next(), but I need to go further than the next record

Any way to do this?
Would it be with a subreport? I don't know
Thanks for your help

Michaelput a formula which gives 1 when active is true then use running total and reset it when value changes , hope it workss..

Record sorting based on input param

I'd like to have my records sorted EITHER on outstanding debt field OR age of account field, based on a user-entered parameter. Is it possible to do this?
Thanks.For this you need to use two different reports. One for Outstanding and other for Age. Depending on the parameter call different report|||I thought this might have been the case. Thanks for replying :)