RE: SQL Server not inserting all rows from single long statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 13:45:06 -0800

>From Books Online explaination of EXECUTE:

@string_variable

Is the name of a local variable. @string_variable can be of char, varchar,
nchar, or nvarchar data type with a maximum value of the server's available
memory. You can use up to 4,000 characters for nchar or nvarchar and 8,000
characters for char or varchar data type strings. If the string is greater
than 4,000 characters, concatenate multiple local variables to use for the
EXECUTE string. For optimum performance, do not use more than 4,000
characters. For more information about system-supplied SQL Server data types,
see Data Types.

[N]'tsql_string'

Is a constant string. tsql_string can be of nvarchar or varchar data type.
If the N is included, the string is interpreted as nvarchar data type with a
maximum value of the server's available memory. If the string is greater than
4,000 characters, concatenate multiple local variables to use for the EXECUTE
string.

Hope this helps. It looks like you are running into a possible memory issue
and/or try the recommendation of concatenating less than 4000 chunks together.

Sincerely,

Anthony Thomas

"Abbas Mukadam" wrote:

>
> Hello folks
> I am using ADO to execute a SQL statement. The variabble StrSQL that
> holds multiple INSERT statements all concatentated.. To be precise there
> are 812 INSERT statements; all concatenated to form one long string
> When i call cnn.Execute (StrSQL) I should get 812 rows in the table.
> However, I get sometimes 467 sometimes 468 rows.
> I get no error when executing. When i breakdown the string to hold 200
> INSERT statements at a time it works fine.Is there a limit on the size
> of string SQL statment?
> Any help would be greatly appreciate..
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Relevant Pages

  • Re: Are you guys sure about + in this sp
    ... You're still not evaluating that SQL; all you're doing is preparing the ... string. ... You need to EXECUTE it. ... > Parameter '@strSQL' has an invalid data type. ...
    (microsoft.public.sqlserver.programming)
  • verifying data type
    ... I want my code to look at the contents of a cell, and if the contents are not ... a number (any string) execute one set of code. ... but I don't know how to test the data type of the ...
    (microsoft.public.excel.programming)
  • Re: Array Or what?
    ... > I'd like to select some rows from SQL Server table. ... > What data type for a parameter in VB should I use. ... statement around it into a larger string and then use the EXECUTE ...
    (microsoft.public.vb.general.discussion)
  • Re: New to Classes, please help.
    ... David Lozzi ... > A class is a data type, just as an integer or a string. ... >>> example, username dlozzi, password fun. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)