-1

This may not actually be a code issue, but here goes... My C# program reads a CSV file and builds an SQL Insert statement to insert all rows of the file into a table, so the Insert statement becomes: 

Insert GHP_For_CMS (GroupId, EmployeeID, etc...) values (rec1_field1, rec1_field2, etc...), (rec2_field1, rec2_field2, etc...)

SQL runs flawlessly against a Sandbox/Dev db from a Dev Server. SQL crashes with a NullReference exception ("Object not set to an instance of an object") against the same Sandbox/Dev db from a Production server. The Dev server has Framework version 4.0.30319.18052, and the Production has Framework version 4.0.30319.18063.

I know I haven't given a lot of information here, but anyone have any ideas? Or at least ideas of something to check? I've been banging my head against this one for 3-4 days and nothing is rearing it's ugly head.

TIA!

The error happens on the 'ExecuteNonQuery' below...

string dbConnString = ConfigurationManager.ConnectionStrings[targetDatabase].ToString();
if (null != dbConnString)
{
    using (SqlConnection dbConn = new SqlConnection(dbConnString))
    {
        if (dbConn != null)
        {
            SqlCommand cmd = new SqlCommand(m_sqlInserts.ToString(), dbConn);
            dbConn.Open();
            int rowsAdded = cmd.ExecuteNonQuery();

...

Bryan Dickerson
  • 63
  • 10
  • 3
    So a [NullReferenceException](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it). You will need to show where the exception occurs within your code. – Alex K. Oct 20 '15 at 16:32
  • Can you attach to and debug the process running in the production environment? Then please show us the C# line of code that has the error and the parameter or object that is null in the C# call to execute the SQL command. – user8128167 Oct 20 '15 at 16:36
  • I know this may seem obvious, but do the table columns line up between your dev and production? Like does your development table have a new column that is being used in new functionality that isn't in production yet? – GreatNate Oct 20 '15 at 16:40
  • @GreatNate: I'm running the same pgm and query against the same db and table from two different servers--to wit, I compiled and copied the same program to both servers from the same file on my box. – Bryan Dickerson Oct 20 '15 at 17:37
  • What is the value of m_sqlInserts? – cdonner Oct 20 '15 at 17:51
  • @cdonner: see the "Insert GHP_For_CMS..." statement above. It can be very long depending on the length of the file. I have copied and pasted the statement from debugging in VS and print statements to the SQL studio and it parses correctly every time. As I noted earlier, it runs correctly in one instance. – Bryan Dickerson Oct 20 '15 at 18:17
  • The value that you pasted is from the instance where it does not run? Are you running into a size limit for the command text by any chance (see http://stackoverflow.com/questions/21390537/sqlcommand-executenonquery-maximum-commandtext-length). The error suggests that your SqlCommand object was not instantiated. – cdonner Oct 20 '15 at 18:25
  • All due respect, but again, it's the exact same program creating the exact same insert statement against the exact same connection/server/table runs successfully from one server and gets the error from the other server. – Bryan Dickerson Oct 20 '15 at 19:02
  • This is an exe project? Can you add a stack trace? – Alex K. Oct 20 '15 at 23:45
  • @AlexK.: Yes, this is an EXE project. I also presume you mean from the instance where it fails, but what would I add to the program to spit out a stack trace at the point of failure? – Bryan Dickerson Oct 21 '15 at 15:37
  • So this is the same instance of SQL Server in both cases, i.e. you are connecting remotely. Can you confirm that the connection object is properly populated? It may not be null and still not be fully populated. Check if it is open (http://stackoverflow.com/questions/6943933/check-if-sql-connection-is-open-or-closed). – cdonner Oct 21 '15 at 18:59
  • Bryan when you run the exe in the Visual Studio debugger it will break when there is an exception and you can see the stack trace in the exception helper. – cdonner Oct 21 '15 at 19:01
  • @cdonner: Aha! Thank you! Instead of printing out the "InnerException", I echoed the StackTrace back to the console and finally, I get the real error which is a SQL simple permissions-issue! Dang, I feel so stupid... but thanks again! – Bryan Dickerson Oct 21 '15 at 20:34

1 Answers1

1

So the answer to the question (because I couldn't post more details and I really wanted to know of things to try): is that it's a simple Permissions issue with the account that is running the process not being able to insert records to the table. The comment that helped me get to that conclusion came from @cdonner (Thank you, again!) in wanting to see a stack trace. I realized that I could return a StackTrace in the console log instead of just the InnerException message and it (the SQL Error) was finally revealed.

Bryan Dickerson
  • 63
  • 10