[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1741
  • Last Modified:

Maximum commandText text length

I'm trying to modify an SP using Vb code. The SP is rather extensive because it uses a lot of  Else If  statements. I keep getting errors when I run the Execute statement to create the SP and I think its because I'm sending too large of a string.  My ADO looks something like this:  

cmd.CommandType = adCmdText
cmd.ActiveConnection = cn
cmd.CommandText = str
cmd.Execute

The length of the variable  str is >33000
The length doesn't seem to be a problem when the sp is created manually using either SQL Query analyzer or Access ADP.
How can I create a large SP in VB?
0
Mach1pro
Asked:
Mach1pro
  • 9
  • 6
  • 2
  • +1
1 Solution
 
sudheeshthegreatCommented:
first of all, i believe this is more of a VB question than a SQL Server one. :-)

to execute a stored proc, use
cmd.CommandType = adCmdStoredProc
and in
cmd.CommandText = str
str should be the name of the stored proc.
0
 
rafranciscoCommented:
Any reason why you can't modify the SP in Query Analyzer?
0
 
Anthony PerkinsCommented:
It would help if you psoted the contents of str that created the stored procedure, as well as the error message when you "run the Execute statement to create the SP"
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
sudheeshthegreatCommented:
guess i didn't read the question propery earlier. i thought you wanted to execute the stored proc.
in VB, a fixed length string can have upto 64k (2^16) characters, and a variable length string can have upto 2 billion (2^31) characters.
it would be helpful if you could post the error message.
0
 
Mach1proAuthor Commented:
The error message is:  -2147217900
Closing delimiter not found for the string beginning at position 33032 in the command. The string begins with: ' + [EmployeelastName].

I wrote str to a text file just before the cmd.Execute command. The file length was 33322.
 I was able to copy the text from the file and paste it into a new SP and it worked fine.
0
 
Anthony PerkinsCommented:
>>I was able to copy the text from the file and paste it into a new SP and it worked fine.<<
Evidently something got lost,  Unfortunately without seeing the query it is difficult to even speculate.
0
 
Anthony PerkinsCommented:
If you cannot post the whole text at least post the lines in the vicinity of  + [EmployeelastName]
0
 
Mach1proAuthor Commented:
Here's the last 1000 characters:

oyeefirstname] + ' ' + Coalesce([employeemidinitial],'') + ' ' + [employeelastname] AS FirstLast, [_Employee].EmployeeSSN,  [_Employee].EmployeeLastName, [_Employee].EmployeeFirstName, [_Employee].EmployeeMidInitial,  [_Employee].EmploymentStatus,[_Employee].Department, @SessionID AS SessionID
    FROM _Employee
    WHERE ((([_Employee].Autonumber)=2086));

Else
    INSERT INTO XEmployee ( AutoNumber, LastFirst, FirstLast, SSN, LastName, FirstName, MiddleInitial, Status, Dept, SessionID )
    SELECT [_Employee].AutoNumber, [employeelastname] + ', ' + Coalesce([employeefirstname],'') + ' ' + Coalesce([employeemidinitial],'') AS LastFirst, [employeefirstname] + ' ' + Coalesce([employeemidinitial],'') + ' ' + [employeelastname] AS FirstLast, [_Employee].EmployeeSSN,  [_Employee].EmployeeLastName, [_Employee].EmployeeFirstName, [_Employee].EmployeeMidInitial,  [_Employee].EmploymentStatus,[_Employee].Department, @SessionID AS SessionID
    FROM _Employee
    WHERE ((([_Employee].Autonumber)=@EmpID))
0
 
Anthony PerkinsCommented:
There is either a typo in your error message or it does not match with the code you posted.
0
 
Mach1proAuthor Commented:
I don't think there is any typo myself.  I think the data being sent got truncated.

0
 
Anthony PerkinsCommented:
>>I don't think there is any typo myself. <<
Really? I suggest you take a second look.  [EmployeelastName] does not match exactly with any string in the query you posted.  Since SQL will always report the exact (including the case) text, I can only assume:
1. There is a typo.
2. It is not match with the query you posted.

>>I think the data being sent got truncated.<<
Would it make any difference if we said that is not the case?
0
 
Mach1proAuthor Commented:
The reason it doesn't match EXACTLY is because I hand typed in the error message.
Here is a link to the text file created in VB. http://www.melandreba.com/sp.txt
I can copy the contents of the file into a new SP in Enterprise Manager and it works, but if I try to put the contents into a string variable in VB as described above, it fails.
0
 
Anthony PerkinsCommented:
>>The reason it doesn't match EXACTLY is because I hand typed in the error message.<<
That is what we call here a typo.

>>if I try to put the contents into a string variable in VB as described above, it fails.<<
I just created your stored procedure without any problem using both the Execute method for the Connection, as well as the Command object. So I suggest you post your VB code so that we can see what you are doing.
0
 
Mach1proAuthor Commented:
Here's the VB code:    http://www.melandreba.com/SPCode.txt
0
 
Anthony PerkinsCommented:
If I get some time I will check it when I get home.
0
 
Anthony PerkinsCommented:
Since I do not have your data I cannot duplicate the exact code.  So other than to tell you that strictly speaking this code is MS Access code, not VB I do not see anything obviously wrong with it.  Have you tried debugging the code, by putting breakpoints at regular intervals and watching the values of the variables?

If you are still stuck tomorrow, let me know and I will post the code I used to test.  But basically all it did was to read the entire stored procedure from disk and Execute it using first the Connection object and next (after first dropping the stored procedure) the Command object.
0
 
Mach1proAuthor Commented:
Evidently the problem is limitations of the adodb command object.
I was able to create a stored procedure that takes my string and creates the procedure.
Here's my SP:

Alter Procedure CreateNewProcedure
    @str nText
AS
Exec(@str)
return

All I do is send  my variable str to this stored procedure as Parameter(1) and it works.
0
 
Anthony PerkinsCommented:
>>Evidently the problem is limitations of the adodb command object.<<
Nope.  That is simply not true.  Here is the code I tested successfully with:

Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream
Dim SQL As String
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set fso = New Scripting.FileSystemObject
Set f = fso.OpenTextFile("c:\temp.txt", ForReading, False)        ' c:\temp.txt contains the stored procedure code from http://www.melandreba.com/sp.txt
SQL = f.ReadAll()
f.Close

Set cn = New ADODB.Connection
cn.ConnectionString = "Connection string goes here"
cn.Open
'cn.Execute SQL

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = SQL
cmd.CommandType = adCmdText
cmd.Execute

cn.Close

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now