.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS_SQL2005_Sharepoint ListSource Adapter_DT_NTEXT values returning NULL on extract/import

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

Hello.  Please HELP!!!!  I've been trying to figure this out for a few days.

I am using the SSIS SharepointAdapters for 2005.  When I pull data from my sharepoint site, for fileds that are choice fields in sharepoint are a datatype of DT_NTEXT w/4000 characters.  The only thing in these fields is "PASS" " "FAIL" and one filed has "QA Review Completed".  When the data is imported into SQL these fields eiher show NULL or <Long Text>.  I am using the combination of the Derived Column and Data Conversion to change the Datatype of these fields from DT_NTEXT since no output will accept this datatype.  I've tried exporting to Excel, Access and Flat files only to get same results.  What I don't understand is if I import the data from Sharepoint to Access or Sharepont to Excel using Sharepoint and not SSIS, I get the data that is in the field(s).

I've searched all over the internet and have not found where anyone has had the same issue which I find to be odd - I can't be the only one.  I hope I'mnot.




View Complete Post

More Related Resource Links

Gridview RowCommand Event returning null values

HiI'm trying to retreive values entered by users in the textboxes in the footer of the gridview and insert these values into another sql table.My code is as follows :-protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)    {       if(e.CommandName.Equals("addnew"))       {                       TextBox t1 = (TextBox)GridView2.FooterRow.FindControl("tfrm");            string frm = t1.Text;            TextBox t2 = (TextBox)GridView2.FooterRow.FindControl("tsub");            string sub = t2.Text;            string cmd = "insert into suggestions values('" + frm + "','" + sub + "','c','c')";            con.Open();            SqlCommand com = new SqlCommand(cmd,con);            int a = com.ExecuteNonQuery();&n

Excel to sql import null values issue

How to handle null data or empty rows in the excel, as i got no record in mydestination table.

Navin.D http://navind.spaces.live.com/

How to deal with NULL values in a SQL table


 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default

With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

Control Values When Returning to Page


Why do controls value not persist when returning to a page (e.g. via browser back button)?  If I set control values without utilizing a post back the controls will maintain their last value when I return to the page.  If I set a control value and then call a post back, the control value is reset to the default value when returning.

I have many pages with a gridview, and a textbox which is used to filter the gridview contents.  I set the filter textbox, filter the gridview (postback), go to another page, and when I return to the gridview page (via back button), my filter textbox is reset empty, and the gridview is the original (unfiltered) data.

What concept am I missing here?

Thanks in advance...

Help: Too many lines of code to achieve a simple goal: keep my NULL SQL values...


Hello all

What is the best way (best practice) to preserve my NULL SQL values in the database
during an insert/update operation (without receiving cast invalid errors)?
Also, how to display a string 'n/a' when a sql value is NULL?

My project is using FormView with Edit/Insert templates and classes to represent my tables

Goal1....: display 'n/a' when there are NULL SQL values in the database
Solution1: I am using ISNULL(field,'') in my store procedure for SELECT statements

Goal2....: if the fields are 'n/a', then save them back in the database as NULL
Solution2: on my insert/edit methods I am having to check the values being passed, i.e:

cmd.Parameters.Add(new SqlParameter("@scope", SqlDbType.NVarChar, 50));

if (item.Scope == "n/a")
 cmd.Parameters["@scope"].Value = DBNull.Value;
 cmd.Parameters["@scope"].Value = item.Scope;

Not to mention that for SQL DateType fields when you use ISNULL(field,'') the return string is '1/1/1900 12:00:00 AM'
and I have to change the field value in every field that represents a date, i.e:

protected void FormView1_DataBound(object sender, Sy

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Comparing Null Values(from variable) in SSIS

Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

Problems querying XML data with null values

If the tags have "xsi:nil" in them, then my query failes with a "XML parsing: line [x], character [x], undeclared prefix" error.  So my question is how to get the query to handle or ignore those tags with "xsi:nil" embedded in them.  So specifically, the first SQL query below is successful, and the second one fails with the aforementioned error.  Any comments appreciated, thanks. -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </data>'; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('data/customer') AS T(customer); Returns: 1 Allied Industries 2 Trades International ================== -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> <fax xsi:nil="true" /> </customer> <customer> <id>2</id> <name>Trades International</name> <fax xsi:nil=&quo

Create unique constraint on a column which has null values

Hi All, I have a table suppose 'Temp' having one of the column as 'ColA' which has some null values as well as non null values. Now i have a requirement to create a unique constraint on it. We have tried but couldnt do it.Apart from having a trigger on insert and update statements is there any other alternate. Can any one please help me on this. Thanks & Regards, Srikanth  

export not compatible with import for null smallints

We have one system that exports a table to a pipe-delimited "|" csv file, and another that reads the csv file into a very similar table. One field in the table(s) is a nullable smallint. When the values in the table are null, SSIS exports to the flatfile as an empty field, just two adjacent separators. When I try to read that file back in, it generates an error, can't convert string to smallint. So, what's up with that? I suppose I can fix this one field, but is this going to be a general problem?  Is there a quick fix other than put in my own transformation? Thanks. Josh  --- oh hey, I think I fixed it!  Well, worked around it anyway.  Went to the source in the dataflow, in the advanced editor changed the output column type to DT_I2.  Now it imports as 0 instead of null.  Hmm, is that good enough?

WCF service returning old values

Hi. I am developing an application with WCF services. The basic functionaloty of a screen is to provide an entry screen to add particular entity. Once save even fired It will save the data to the database and call the bind method to bind into the grid by refreshing the latest data. When I am running the site from my source (Visual Studio) , it works perfectly. When the client calls method to fetch the data the WCF service, it returns latest (added) data. But the issue comes in the server when it works with IIS. I deployed the code in the server, once the data added I am getting the old data only. But after two refresh I am getting the latest data. Could you please let me know what required to be done to get the latest record. Many Thank, Kelvin Kumar

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

Null values in formula fields

Hi,I tried using this and am concatenating the fields together.  It does not appear to be working.  Here's what I have.  Any suggestions?   IIF(IsNull({nomination.recparty1}), '0.0', {nomination.recparty1} ) &  chr(13)IIF(IsNull({nomination.recparty2}), '0.0', {nomination.recparty2} ) & chr(13)IIF(IsNull({nomination.recparty3}), '0.0', {nomination.recparty3} )andif IsNull({nomination.recparty1}) then '0.0' else {nomination.recparty1} & chr(13)if IsNull({nomination.recparty2}) then '0.0' else {nomination.recparty2} & chr(13)if IsNull({nomination.recparty3}) then '0.0' else {nomination.recparty3}Both this gets failed beacuse my fields are returned as null.Please get me to solve this.Thanks in advance.Regards,Mugil

Process.Modules Not Returning Correct Values in x64

Hey All!   Here is my problem, when i use the following code:   Process hProcess = (.....); foreach (ProcessModule m in hh.Modules) { Console.WriteLine(m.ModuleName + " - " + m.BaseAddress); }   On a 32bits machine I get like 20 different values, those are the ones I use further in my program. However in a 64bits machine I only get: myexe.exe, ntdll.dll, wow64.dll, wow64win.dll and wow64cpu.dll.   I also tried EnumProcessModules, but got the same result.   How may I get the correct values I need?   My .NET exe is compiled at 32bits and the target process is also 32bits.   Thanks, Best Regards, Mywk

Custom membership provider not returning values from my web.config


I created a custom membership provider in my ASP.NET 4.0 web site, stored  in App_Code, and referenced in my web.config.

However, it doesn't appear to be pulling values out of web.config during initialization.

The code was taken from http://www.asp.net/general/videos/how-do-i-create-a-custom-membership-provider, and the only modifications were changing "connectionStringName" here to the name of my connection string:

    Dim ConnectionStringSettings As ConnectionStringSettings = _

The connection string always comes back as nothing in this line:

    If ConnectionStringSettings Is Nothing OrElse ConnectionStringSettings.ConnectionString.Trim() = String.Empty Then
      Throw New ProviderException("Connection string cannot be blank.")
    End If

No matter what I change the password format to in web.config, the default value here is always used:

    Dim temp_format As String = config("passwordFormat")
    If temp_format Is Nothing Then
      temp_format = "Has
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend