SQL Insert Statement Error / String Limitation

[tqeonline]

Hey Guys!

I have 160k rows I have to do formatting on and then dump into a SQL DB. I have all the variables declared correctly but it seems that my String is being limited.

    strSQL = "INSERT INTO [dbo].[MATStracingVer2v0] (TimeStamp,PreciseTimeStamp,ComputerName,Level,ProviderGuid,ProviderName,EventId," & _
      "Pid,Tid,OpcodeName,KeywordName,TaskName,ChannelName,EventMessage,ActivityId,HostName,RoleName," & _
      "Version,ServiceName,Operation,TraceLevel,TraceMessage,CallStack,Exception,DateTime,PartitionKey,RowKey,N,RowIndex)" & _
         "Values('" & strTimeStamp & "','" & strPreciseTimeStamp & "','" & strComputerName & "','" & strLevel & "','" & strProviderGuid & "','" & strProviderName & "','" & strEventId & "','" & strPid & "','" & strTid & "','" & strOpcodeName & "'," & _
         "'" & strKeywordName & "','" & strTaskName & "','" & strChannelName & "','" & strEventMessage & "','" & strActivityId & "','" & strHostName & "','" & strRoleName & "','" & strVersion & "','" & strServiceName & "','" & strOperation & "'," & _
         "'" & strTraceLevel & "','" & strTraceMessage & "','" & strCallStack & "','" & strException & "','" & strDateTime & "','" & strPartitionKey & "','" & strRowKey & "','" & strN & "','" & strRowIndex & "')"

The creates the error code:

Run-time error '91':

Object variable or With block variable not set

The length of the string is 915 at this point and it consists of:

"INSERT INTO [dbo].[MATStracingVer2v0] (TimeStamp,PreciseTimeStamp,ComputerName,Level,ProviderGuid,ProviderName,EventId,Pid,Tid,OpcodeName,KeywordName,TaskName,ChannelName,EventMessage,ActivityId,HostName,RoleName,Version,ServiceName,Operation,TraceLe

Is there a way to make it not cutoff at TraceLe (TraceLevel)? Also - is there a limit to string length? If so, how would I mitigate that?

- Matt

"If I must boast, I will boast of the things that show my weakness

 

[Andrzejek]

Since I don't have your variables, I did something like this:

Dim strSQL As String

strSQL = "INSERT INTO [dbo].[MATStracingVer2v0] (TimeStamp,PreciseTimeStamp,ComputerName,Level,ProviderGuid,ProviderName,EventId," & _
      "Pid,Tid,OpcodeName,KeywordName,TaskName,ChannelName,EventMessage,ActivityId,HostName,RoleName," & _
      "Version,ServiceName,Operation,TraceLevel,TraceMessage,CallStack,Exception,DateTime,PartitionKey,RowKey,N,RowIndex)" & _
         "Values('" & strTimeStamp & "','" & strPreciseTimeStamp & "','" & strComputerName & "','" & strLevel & "','" & strProviderGuid & "','" & strProviderName & "','" & strEventId & "','" & strPid & "','" & strTid & "','" & strOpcodeName & "'," & _
         "'" & strKeywordName & "','" & strTaskName & "','" & strChannelName & "','" & strEventMessage & "','" & strActivityId & "','" & strHostName & "','" & strRoleName & "','" & strVersion & "','" & strServiceName & "','" & strOperation & "'," & _
         "'" & strTraceLevel & "','" & strTraceMessage & "','" & strCallStack & "','" & strException & "','" & strDateTime & "','" & strPartitionKey & "','" & strRowKey & "','" & strN & "','" & strRowIndex & "')"

Debug.Print strSQL

and I've got this:

INSERT INTO [dbo].[MATStracingVer2v0] (TimeStamp,PreciseTimeStamp,ComputerName,Level,
ProviderGuid,ProviderName,EventId,Pid,Tid,OpcodeName,KeywordName,TaskName,ChannelName,
EventMessage,ActivityId,HostName,RoleName,Version,ServiceName,Operation,TraceLevel,
TraceMessage,CallStack,Exception,DateTime,PartitionKey,RowKey,N,RowIndex)
Values('','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','')

You may want to do:
[tt]
strSQL = "INSERT INTO [dbo] ...

strSQL = strSQL & " VALUES(...)
[/tt]

Have fun.

---- Andy