We just upgraded our DBConnect from 2.2.0 to 3.0.3 and are connecting to a SQL database (was an existing connection) and have found that columns which are (nvarchar(max),null) now come back to Splunk as Non-Displayable Column Type ntext.
These worked perfectly fine in 2.2.0.
Has anyone else seen this before? And is there something we need to do to get around it? Or is the only solution going back to the previous version of DBConnect?
Try this:
convert(varchar(64),sql_handle,1) sql_handle,
it's workable for me after upgraded to DBX v3 🙂
We cannot go through our db and update all our nvarchar((max), null) definitions in order to fix this issue because we'd end up breaking a bunch of applications. When is this going to be resolved? There shouldn't be anything wrong with importing a nullable field with the max size. The only solution so far is to revert dbconnect.
Is there ever going to be a fix for this? We cannot feasibly go through all the columns in our db and change our datatypes from nvarchar((max), null) to a fixed value - we need the max field because truncation errors could occur and break our applications.
Is the only fix for now is to downgrade back to DB Connect 2.x ?
Has there been any update to this problem?
Sorry if when I posted the (nvarchar(max),null) listing that you thought it meant the data coming back in the column was NULL. That is just the column type in SQL.
The data that comes back from that can be different values (some are just test, some seem to be XML formatted).
Hi,
The same change applies to *LOB fields, only that one was implemented in v2. IIRC v1 would run them through strings to see if something came out... v2 and v3 just throw an error.
The fields (db columns) that are no longer showing the data when reading work fine in 2.3.1 (the version we have been running).
When reading this database with these 2 columns, the AlertName one is read fine but the AlertDescription throws the Non-Displayable Column Type ntext.
Is there anything I can do in DBConnect 3.0.3 to get the AlertDescription field to read correctly like it did in 2.3.1?
Have you been able to find a workaround to this issue?
Never found a workaround.
For now, we reverted back to the 2.3.1 version of DBConnect to allow us to use the connections we were already using.
Hopefully someone will figure out some way around the issue.
Worked with support on this earlier today and this seems to be a UI issue. I went ahead and created the inputs and the data comes in fine for the columns that show Non-Displayable Column Type
would the columns show the data fine from those columns if you just did a dbxquery command instead of an input pull?
I do both here and when I set it up before, the field just would not even be there to show data.
Did you make any changes to ensure that the data would come in (even when it shows the Non-Displayable Column Type ?
It seems if you just do a dbxquery it presents same behavior as the SQL Explorer.
I can confirm however that defining the inputs with the query does bring in the data properly. I did not make modifications to the tables or db itself.
Hope it helps.
What version of Splunk are you running?
we are still on 6.4.3 and I am wondering if we see some of this behavior due to that.
Hi,
I apologize, we did change the behavior in v3. The SQL standards have never been very clear about what to do with columns that don't have data in them. Review https://en.wikipedia.org/wiki/Null_(SQL) for more background. DB Connect and Splunk do not render the NULL character, but instead replace it.
In DBX 2.x, a database NULL was transformed to the ASCII string "NULL", which is misleadingly ambiguous. Is this empty, or the last name "Null"? Upon reflection and review of bugs, we settled on the principle that we should not alter customer data any more.
Because database schemas may change and behavior has changed over the evolution of DB Connect, customers should consider using eval or fillnull to ensure that SQL searches are robust.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Eval