Tuesday, July 31, 2012

There is insufficient system memory in resource pool 'internal' to run this query SQL Server 2008 R2

One of the production servers I maintain crashed  and I saw this error in the log viewer.This server was recently upgraded to SQL Server 2008 R2.

To fix this I had to cap the max memory usage for this database server at 29 GB. The max was set to less than the Windows Server 2008 memory which is 32 GB.Since then I have not had issues with the database server.


Also applied the service pack 2 which is available here

http://support.microsoft.com/kb/2527041









Friday, July 27, 2012

Type 'System.Web.UI.UpdatePanel' does not have a public property named , asp.net, ajaxtoolkit

You get this error when you have a control inside a Update panel but not inside the ContentTemplate of the update panel.The right way to use the updatepanel is listed below

<asp:UpdatePanel ID="UpdatePanel1" runat="server>
     <ContentTemplate>
           < Your Control goes here >
    </ContentTemplate>
</asp:UpdatePanel>



Windows Presentation Foundation #WPF Example: Base 64 encode/decode C#

This is a very basic example of a Windows Presentation Foundation application.In this application I encode a string to its base 64 equivalent string and then decode it back to the original string.

Open Visual Studio 2010 -> Create New Project -> Choose WPF Application




Once the project is created.Drag and drop two textboxes and two buttons to your MainWindow.xaml file


Double click the buttons and add the code below.

 private void button1_Click(object sender, RoutedEventArgs e)
 {
     textBox2.Text = Convert.ToBase64String(Encoding.Unicode.GetBytes(textBox1.Text));
 }

 private void button2_Click(object sender, RoutedEventArgs e)
 {
     textBox2.Text = Encoding.Unicode.GetString(Convert.FromBase64String(textBox1.Text));
  }

Compile the solution and run the program.

Output:




Source Code:

using System;
using System.Text;
using System.Windows;

namespace Base64EncodeDecode
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            textBox2.Text = Convert.ToBase64String(Encoding.Unicode.GetBytes(textBox1.Text));
        }

        private void button2_Click(object sender, RoutedEventArgs e)
        {
            textBox2.Text = Encoding.Unicode.GetString(Convert.FromBase64String(textBox1.Text));
        }
    }
}






Thursday, July 26, 2012

Token based server access validation failed with an infrastructure error.Check for previous errors [CLIENT:IP ADDRESS]

After migrating one of the production servers, I noticed this error in the  SQL Server log file viewer.
To resolve this, first I took the IP address [IP ADDRESS listed in the error] and did a nslookup on the ipaddress to get the servername.


I logged on the server and checked the job history and noticed that one of the jobs was failing.










The error indicated that the user did  not have permissions to access the new upgraded server.The solution was to add that user to the new server  and grant access to the database on the new upgraded server.Once added the sql job on the server executed without any issues.

Tuesday, July 24, 2012

Cannot bulk load because the file could not be opened.Operating system error code 5(Access is denied)

Recently I upgraded one of our production servers to SQL Server 2008  on a Windows Server 2008 R2.Once the migration was complete, all processes,jobs looked fine except for one.

I have a daily process that checks for a file on a ftp server,downloads it to the local file server and then bulk inserts the file into multiple databases located on multiple servers.The file is a csv file and I am updating 7 sql server databases located on 7 servers.Some of the servers are managed by other teams and the data is used for multiple processes.

The process started failing and in order to isolate the problem I took the file and manually started bulk inserting into the 7 databases on 7 servers.All the 7 servers are Windows Server 2008 R2 running SQL Server 2008.The account used to bulk insert was a SQL Server account with BulkAdmin,SysAdmin permissions.

I noticed this error only on one of the servers and the rest of the servers the bulk inserts were fine.



So after we isolated the problem we checked for permissions to the file which was located on a network share which had access to everyone and sql account on all the servers.The next step was to check the default protocol used and that led us to the problem.

On the server where the bulk insert was failing the default protocol used was  changes to Named Pipes and on the rest of the servers TCP was the protocol.

Bulk Insert failure: server protocol










Bulk Insert success: server protocol


Once the Named pipes was disabled bulk insert was successful.







Monday, July 23, 2012

Atom RSS date,ISO8601 to SQL date conversion C#

On a daily basis we deal with date of the following format 
yyyy-MM-ddThh:mm:ssZ known as ISO 8601, the International Standard for the representation of dates and times, and we have to convert it other formats,mainly SQL.

More about the format can be found here

http://www.w3.org/TR/NOTE-datetime

Below code does the conversion


try

   {
       string date = "2012-07-23T18:30:02Z";
       string dateString = date.Replace("Z", string.Empty);
       DateTime value = DateTime.ParseExact(dateString, "s", null);
       Console.WriteLine(value.ToString( "yyyy-MM-dd HH:mm:ss.fff"));
   }
   catch (Exception ex)
   {
       Console.WriteLine(ex.Message);
   }
   Console.Read();

Friday, July 20, 2012

c# - Copy top n lines from text file

Below code does the same job of head command in Unix which is widely used to get the top n lines from a text file.

namespace PartialFileCopier
{
    class Program
    {
        static void Main(string[] args)
        {
            String sLine = String.Empty;
            Int32 iLineCounter = 0;
            Int32 iLineCount = Int32.Parse(ConfigurationSettings.AppSettings["LINE_COUNT"]);
            
            try
            {
                using (StreamWriter writer = new StreamWriter(ConfigurationSettings.AppSettings["OUTPUT_FILE_NAME"]))
                {
                    using (StreamReader reader = new StreamReader(ConfigurationSettings.AppSettings["INPUT_FILE_NAME"]))
                    {
                        while (!reader.EndOfStream )
                        {
                            sLine = reader.ReadLine();
                            iLineCounter++;
                            Console.WriteLine(sLine);
                            writer.WriteLine(sLine);
                            if (iLineCounter == iLineCount)
                                break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.Read();
        }
     }
}

The configuration entries

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="INPUT_FILE_NAME" value="\\Servername\InputFileName.txt"/>
    <add key="OUTPUT_FILE_NAME" value="\\Servername\OutputFileName.txt"/>
    <add key="LINE_COUNT" value="1000"/>
  </appSettings>
</configuration>

Inserting data from csv files into SQL Server from C#.3 different ways.

There are 3 different ways you could insert data in bulk to the SQL Server database tables from your C# application.

BULK INSERT 
               http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx

BCP
               http://msdn.microsoft.com/en-us/library/ms162802.aspx

SQLBULKCOPY
               http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Examples to follow in future posts.

Wednesday, July 18, 2012

Windows Presentation Foundation #WPF Example: Send notes in an Email C#

At my workplace we are supposed to send notes about our work on a weekly basis to our manager.

So when it was time for my annual review I wanted retrieve all my projects notes for the entire year.I always copy the notes I send to my manager in a text file so that at the mid and annual review I would have all the notes in one single file.


New hires who are not aware of the processes did not have all their notes in place and had to dig through their emails to get all the notes.The mailbox could only save up to 6 months of mails so the notes older than 6 months were lost.

So I decided to write this tool for learning something new i.e Windows Presentation Foundation.

Very basic requirements:



  • Application would have a placeholder for entering notes (plain text).
  • A button to save the notes to a text file and email the notes to manager.
  • A window to display notes and buttons to navigate through all notes.
Create a new project and choose WPF Application


















When created the project consists of App.xaml,MainWindow.xaml files. Add a WPF window i.e LoadFileWindow


















Add an App.Config to hold all the keys used in the application.My configuration file looks like this

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="FROM_EMAIL" value="myname@mycompany.com"/>
    <add key="TO_EMAIL" value="myname@mycompany.com,mymanager@mycompany.com"/>
    <add key="FILE_PATH" value="C:\Status_2012.txt"/>
  </appSettings>
</configuration>

Add controls to the forms based on the requirements outlined at the beginning

MainWindow with textbox to hold the email address,richtextbox to hold the notes,notes button to open all notes windows and send button to save and send an email to the manager















LoadfileWindow with richtextbox to display notes,previous,next buttons to shift through notes and close button to close the window.















Next add code to the button events.

MainWindow.xaml.cs

using System;
using System.Windows;
using System.Windows.Documents;
using System.IO;
using System.Configuration;

namespace StatusNotes
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            DateLabel.Content = "Date:" +DateTime.Now.ToString();
            EmailTextBox.Text = ConfigurationManager.AppSettings["TO_EMAIL"];
        }

        private void SendMail_Click(object sender, RoutedEventArgs e)
        {
            TextRange textRange = new TextRange(StatusRichTextBox.Document.ContentStart,StatusRichTextBox.Document.ContentEnd);
            WriteToFile(textRange);
            SendMail(EmailTextBox.Text, "Status Notes for " + DateTime.Now.ToString(), textRange.Text, false);
            MessageBox.Show(Application.Current.MainWindow,"Your status notes has been emailed to your manager.","Confirmation",MessageBoxButton.OK);
            StatusRichTextBox.Document.Blocks.Clear();
        }

        private void ShowAllStatusButton_Click(object sender, RoutedEventArgs e)
        {
            var newWindow = new LoadFileWindows();
            newWindow.ShowDialog();
        }

        private void WriteToFile(TextRange textRange)
        {
            using (StreamWriter oWriter = new StreamWriter(ConfigurationManager.AppSettings["FILE_PATH"], true))
            {
                oWriter.WriteLine(DateTime.Now.ToString());
                oWriter.WriteLine("*****************************************************************************");
                oWriter.WriteLine(textRange.Text);
                oWriter.WriteLine("*****************************************************************************");
                oWriter.Write("$");
            }
        }

        private void SendMail(string p_sEmailTo, string subject, string messageBody, bool isHtml)
        {
            try
            {
                string sEmailTo = p_sEmailTo;
                string sEmailFrom = ConfigurationManager.AppSettings["FROM_EMAIL"];

                string DEFAULT_MAIL_SERVER = "DefaultMailServerName";
                System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage(sEmailFrom, sEmailTo);
                msg.From = new System.Net.Mail.MailAddress(sEmailFrom, "Status Notes");
                msg.IsBodyHtml = isHtml;
                msg.Subject = subject;
                msg.Body = messageBody;

                System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient(DEFAULT_MAIL_SERVER);
                client.Send(msg);
            }
            catch
            {

            }
        }
    }
}

LoadFileWindow.xaml.cs

using System.Collections.Generic;

using System.Linq;

using System.Windows;

using System.Windows.Documents;

using System.IO;

using System.Configuration;

namespace StatusNotes
{
    /// <summary>
    /// Interaction logic for LoadFileWindows.xaml
    /// </summary>
    public partial class LoadFileWindows : Window
    {
        public  int iCounter = 0;
        public  int iMaxcounter = 0;
        public  List<string> sStatusList = new List<string>();

        public LoadFileWindows()
        {
            
            InitializeComponent();
            using (StreamReader oReader = new StreamReader(ConfigurationManager.AppSettings["FILE_PATH"]))
            {
                string sFullText = oReader.ReadToEnd().Trim('\n').Trim('\r');
                sStatusList = sFullText.Split('$').ToList<string>();
                iMaxcounter = sStatusList.Count;
            }
            FlowDocument oFlowDocument = new FlowDocument();
            oFlowDocument.Blocks.Add(new Paragraph(new Run(sStatusList[iCounter])));
            StatusRichTextBox.Document = oFlowDocument;
        }

        private void CloseButton_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }

        private void PreviousButton_Click(object sender, RoutedEventArgs e)
        {
            if (iCounter == 0)
                MessageBox.Show(Application.Current.Windows[1], "No more status notes beyond this point.Use the > button to scroll forward !", "Alert", MessageBoxButton.OK);
            else
            {
                iCounter--;
                StatusRichTextBox.Document.Blocks.Clear();
                FlowDocument text = new FlowDocument();
                text.Blocks.Add(new Paragraph(new Run(sStatusList[iCounter])));
                StatusRichTextBox.Document = text;
            }
        }

        private void NextButton_Click(object sender, RoutedEventArgs e)
        {
            if (iCounter == iMaxcounter-2)
                MessageBox.Show(Application.Current.Windows[1],"No more status notes beyond this point.Use the < button to scroll back !","Alert",MessageBoxButton.OK);
            else
            {
                iCounter++;
                StatusRichTextBox.Document.Blocks.Clear();
                FlowDocument text = new FlowDocument();
                text.Blocks.Add(new Paragraph(new Run(sStatusList[iCounter])));
                StatusRichTextBox.Document = text;
            }
        }
    }
}

Output:



















Click on the Notes button.This is bring up the below window.Use the <,> buttons to shift through all your notes which are saved in the text file on your machine.





Tuesday, July 17, 2012

c# - Search functionality on a database table using Dynamic SQL

I have seen this request on forums and this is one of the most common type of searches that I come across in applications.

Search a table for a given string and retrieve matching records.
use case:Retrieve parts from a PartsInventoryTable which matches a certain name.

The page would have a textbox for search string,dropdownlist with columns from the database tables, a button to retrieve the search records that match the search string and a gridview to display the search records.

Below is the code for this simple database table search.


public void GetSearchResults() 
        { 
            string searchString = MyTextBox.Text.Trim(); 
            string searchParam = MyDropDownList.SelectedValue; 
  
            StringBuilder searchQuery = new StringBuilder("SELECT * FROM MYTABLENAME WHERE "); 
            searchQuery.Append(searchParam); 
            searchQuery.Append(" LIKE "); 
            searchQuery.Append(" %"); 
            searchQuery.Append(searchString); 
            searchQuery.Append("% "); 
 
            SqlConnection conn = new SqlConnection(connectionString);            
 
            SqlCommand cmd = new SqlCommand(searchQuery.ToString(),conn); 
            cmd.CommandType = CommandType.Text; 
 
 
            conn.Open(); 
            try 
            { 
                dataReader = cmd.ExecuteReader(); 
 
            } 
            catch (Exception ex) 
            { 
                //ExceptionHandler 
            } 
            finally 
            { 
                conn.Close(); 
            } 
            if (dataReader != null) 
            { 
                MyGridView.DataSource = dataReader; 
                MyGridView.DataBind(); 
            } 
        }

Monday, July 16, 2012

Could not connect to mail server. (No connection could be made because the target machine actively refused it Database Mail SQL Server 2008

After installing SMTP server on the new Windows Server 2008 and configuring the database mail, I tested out the mail feature but it was not working and I was getting the below error.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-07-16T17:24:59). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).




This error is generated because of an incorrect SMTP server name.Specifying the correct SMTP server name will fix the issue.
See the below images.







Note:The label says Server Name.My mistake was that I had specified the current server name instead of the SMTP Server Name.Once I specified the SMTP server name i.e smtp.office.myoffice.com the error disappeared.



Compare date to a given date

In one of the projects I was working on, I had to compare the current date to a date in future and disable a section(panel) of the aspx. page. This can be done with the code below.

DateTime oFixedDate = new DateTime(2012,7,15);//Constant Date.
Console.WriteLine("Fixed DateTime:" + oFixedDate.ToString());
Console.WriteLine("Today DateTime:" + DateTime.Now.ToString()); 
            
if (DateTime.Now.CompareTo(oFixedDate) > 0)
       Console.WriteLine("Today is greater than the fixed date");
else if (DateTime.Now.CompareTo(oFixedDate) == 0)
       Console.WriteLine("Today is equal to the fixed date");
else
       Console.WriteLine("Today is less than the fixed date");

Output:

















After changing the fixed date to 
DateTime oFixedDate = new DateTime(2012,7,17);





c#,asp.net - Disable html input Radio Button inside a ListView dynamically

ListView_ItemDataBound event can used to modify the controls inside the ListView in codebehind.The below code is to disable html input radio buttons inside a listview.

protected void MyListView_ItemDataBound(object sender, ListViewItemEventArgs e)
{
        ListViewDataItem dataItem = (ListViewDataItem)e.Item;
        if (e.Item.ItemType == ListViewItemType.DataItem)
        {
            HtmlInputRadioButton oRadioButton = (HtmlInputRadioButton)dataItem.FindControl("MyRadioButton");
            if (oRadioButton != null && MyConditionEqualsTrue) //Based on certain condition
                  oRadioButton.Attributes["disabled"] = "disabled";
         }
}

Friday, July 13, 2012

c# - Generate rolling half hour time slots for the last 24 hours

In my previous post I posted code to generate half hour timeslots for a given day.One of the developers in my team was working on a application that needed half hour timeslots for rolling 24 hours instead of a given day.The code to achieve this is essentially same except that the oCurrentDate is set to Date.Now.AddDays(-1).

try
            {
                DateTime oCurrentDate = DateTime.Now.AddDays(-1);
                DateTime oTimeSlot = new DateTime();
                if (oCurrentDate.Minute > 30)
                    oTimeSlot = new DateTime(oCurrentDate.Year, oCurrentDate.Month, oCurrentDate.Day, oCurrentDate.Hour, 30, 0);
                else
                    oTimeSlot = new DateTime(oCurrentDate.Year, oCurrentDate.Month, oCurrentDate.Day, oCurrentDate.Hour, 0, 0);


                for (Int32 iCounter = 1; iCounter <= 48; iCounter++)

                {

                    Console.WriteLine("Day: " + oTimeSlot.DayOfWeek.ToString() + ", " + oTimeSlot.ToString("m") + " Time: " + oTimeSlot.ToString("t") + " - " + oTimeSlot.AddMinutes(30).ToString("t"));

                    
                    oTimeSlot = oTimeSlot.AddMinutes(30);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

Output:



c# - Generate half hour timeslots for a given date

In one of my previous posts, I had posted sql to generate half hour timeslots.Here's the same in C#.

Note: I am using the date July 10,2012

try
{
      DateTime oCurrentDate = new DateTime(2012, 7, 10);                  
      DateTime oTimeSlot = new DateTime();

      if (oCurrentDate.Minute > 30)

           oTimeSlot = new DateTime(oCurrentDate.Year, oCurrentDate.Month, oCurrentDate.Day, oCurrentDate.Hour, 30, 0);
      else
           oTimeSlot = new DateTime(oCurrentDate.Year, oCurrentDate.Month, oCurrentDate.Day, oCurrentDate.Hour, 0, 0);

      for (Int32 iCounter = 1; iCounter <= 48; iCounter++)

      {
            Console.WriteLine("Day: " + oTimeSlot.DayOfWeek.ToString() + ", " + oTimeSlot.ToString("m") + " Time: " + oTimeSlot.ToString("t") + " - " + oTimeSlot.AddMinutes(30).ToString("t"));
                   
            oTimeSlot = oTimeSlot.AddMinutes(30);               
      }
}
catch (Exception ex)
{
      Console.WriteLine(ex.Message);
}

Output:



Unable to open BCP host data file xp_cmdshell SQL Server 2008

I have a process that created tab delimited text files on a file server which was then loaded to the SQL Server database using xp_cmdshell. The xp_cmdshell is calling the bcp command.


When the server was migrated to SQL Server 2008 and Windows Server 2008 the exec xp_cmdshell started throwing the error "unable to open bcp host data file".

The actual sql that was being executed was 

exec xp_cmdShell 'bcp DataBase.dbo.Table_Name in E:\Folder_Name\File_Name.txt  -c -T -S Server_Name '

To fix this error I had to replace the file path with the UNC path.

exec xp_cmdShell 'bcp DataBase.dbo.Table_Name in \\FileServer_Name\E$\Folder_Name\File_Name.txt  -c -T -S Server_Name '

Wednesday, July 11, 2012

SSPI handshake failed with error code 0x8009030 SQL Server 2008 Migration

I had to upgrade one of our production SQL Servers from 2005 to 2008.The new server was running Windows Server 2008.After finishing the migration I noticed the above error in the Event viewer of the new server.The SQL Server Agent archive had the below error.

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)"


After searching for a fix and messing around with the host file finally was able to fix this by modifying the registry.The steps is listed in this knowledgebase article.