ADO recordset field limitations

I am having trouble inserting a record into SQL using an ADO recordset.
Here's the line in question:
rs!ServerMessage = Data
It appears that when my data string is longer that 255 characters, it only
inserts the first 255 characters into the table and throws the rest away
without any errors. The SQL column is defined as varchar(5000).
Any ideas as to how I can get the full string into the database?
MS SQL 2000, VB6
Thanks,
StephenThree words: show more code.
Send us your DDL, your code, and then maybe we can see the problem.
ML|||The code below is collected from several different locations in the app.
Hopefully this is what you need.
Dim cn As ADODB.Connection
Dim provStr As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sqloledb"
provStr = " Provider=SQLOLEDB;Server=xxxxx;Database=
xxxxx;User
Id=xxxxx;Password = xxxxx"
cn.Open provStr
Set rs.ActiveConnection = cn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set rs.ActiveConnection = cn
rs.Open "Events", cn, , , adCmdTable
rs.AddNew
rs!ServerName = sName
rs!ServerIP = RemoteIP
rs!ServerMessage = Data
rs!DateTime = Date & " " & Time
rs.Update
If rs.State Then
rs.Close
End If|||Any special reason for not using stored procedures?
I don't see the Data variable being declared. Can it be that its length is
255 bytes?
Incidentally - 'Date & " " & Time' ? Please, use appropriate datatypes! The
Universe will be grateful...
ML|||I have verified that the Data variable does hold the complete string. But it
doesn't end up in the database.

>The Universe will be grateful...
Isn't that a little overboard?|||How are you determining that you only see 255 characters?
If you are doing it though query analyzer, be default each column will only
show 255 characters.
To change this, in QA Tools>Options, Results Tab, Maximum Characters per
Column
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>I am having trouble inserting a record into SQL using an ADO recordset.
> Here's the line in question:
> rs!ServerMessage = Data
> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters into the table and throws the rest away
> without any errors. The SQL column is defined as varchar(5000).
> Any ideas as to how I can get the full string into the database?
> MS SQL 2000, VB6
> Thanks,
> Stephen|||Luckily ML answered and not CELKO, then it would have been a right royal
chewing-out.
:)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C9556212-5C24-490A-81F4-C6EB84289192@.microsoft.com...
>I have verified that the Data variable does hold the complete string. But
>it
> doesn't end up in the database.
>
> Isn't that a little overboard?
>|||> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters
What does "appears" mean? What tool are you using to verify the length of
the data? Have you checked SELECT LEN(column) FROM table, and not just the
string result?|||That was exactly the problem. I increased the maximum characters, and saw al
l
of the information.
Thank you for taking the time to help me out.
"Mike Epprecht (SQL MVP)" wrote:

> How are you determining that you only see 255 characters?
> If you are doing it though query analyzer, be default each column will onl
y
> show 255 characters.
> To change this, in QA Tools>Options, Results Tab, Maximum Characters per
> Column
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>
>|||I ran the Len(column) as you suggested and many of the values returned were
well over 255 characters. Mike helped me out by suggesting increasing the
maximum characters in query analyzer. Then I was able to see all of the data
.
Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "appears" mean? What tool are you using to verify the length of
> the data? Have you checked SELECT LEN(column) FROM table, and not just th
e
> string result?
>
>

ADO does not add to Errors collection after the second FETCH NEXT in a SP

I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.

Consider the following table created in a SQL Server
database:

CREATE TABLE TestTable
(
TestInt int
)
go

INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)

This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.

Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON

declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable

open TestCursor
Fetch next from TestCursor into @.TestInt

While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end

Close TestCursor
DeAllocate TestCursor
return
END

This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.

Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:

Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error

On Error Resume Next

Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"

Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute

For Each oErr In cn.Errors
MsgBox oErr.Description
Next

End
End Sub

When this code is executed, only two message boxes appear
with the message "Error 1".

Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan

You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.

I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.