How To Fix The Issue When SQL Jobs Are Running But Not Showing History Records



TODO:

Have you experienced the issue that jobs are running, but you do not see any job history?  This can occur if you have a job that runs at say 3AM, and you have a series of other jobs that run every minute or so during the day.  Those jobs will fill up the Job History, and the entry for the job in question is purged from the history, due to the Maximum History setting.

 

SOLUTION:

1.  Right click on SQL Agent, choose Properties.

2.  Click to History

3.  Increase the value for "Maximum Job History Log Size (in rows)

 

NOTES:

There are no notes on this topic.

How To Configure Log4Net In A C# Application



TODO:

Have you ever wanted to use log4net in your C# application?

 

SOLUTION:

To do so, you need to do 2 things:

 

Step 1:  Add the following entry to the bottom of your AssemblyInfo.cs file

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

 

Step 2:  Add this entry to your web or app.config file

<configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
  </configSections>
 
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="c:\YOUR DIRECTORY HERE\your log file name.log" />
      <appendToFile value="true" />
      <rollingStyle value="Composite" />
      <datePattern value="yyyyMMdd" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="30MB" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d %-5level %logger{1} : %message%newline" />
      </layout>
    </appender>
    <root>
      <level value="ALL" />
      <appender-ref ref="RollingFileAppender" />
    </root>
  </log4net>

 

NOTES:

If you already have a "configSections" entry, just add the new key to that section

How To Fix Web Service Negotiate Login Errors Using C#



TODO:

Have you ever had an issue calling a web service due to the fact that you need to provide credentials to the service?  The below example calls a web service that has an API level user / pass, and also a network user / pass.

 

SOLUTION:

ServiceApi.API api = new ServiceApi.API();

//API Credentials
ServiceCredentials credentials = new ServiceCredentials();
credentials.Username = "user";
credentials.Password = "password";

//Creds, for 401. challenge
System.Net.CredentialCache cc = new System.Net.CredentialCache();
System.Net.NetworkCredential creds = new System.Net.NetworkCredential("user", "pass", "domain");
cc.Add(new Uri("https://webservice.url"), "Negotiate", creds);
api.Credentials = creds;
            
string result = api.GetResultscredentials);

 

NOTES:

Replace the first user / pass with your web service password.  Replace the second set of user / password with your network user / password.

How To Find The Tables And Columns Referenced By A Foreign Key Using T-SQL



TODO:

Have you ever wanted to find the table and column names referenced in a Foreign Key Constraint using T-SQL?

 

SOLUTION:

SELECT 
   OBJECT_NAME(fk.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS fk
INNER JOIN 
   sys.foreign_key_columns AS fkc 
      ON fk.OBJECT_ID = fkc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fkc.referenced_object_id
WHERE 
   OBJECT_NAME (fk.referenced_object_id) = 'YourTableGoesHere'

 

NOTES:

There are no notes on this topic.

How To Create a Comma Separated List Of Table Columns Using T-SQL



TODO:

Have you ever wanted to get a comma separated list of columns that belong to a table using T-Sql?

 

SOLUTION:

DECLARE @columnList varchar(8000)
DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR  
	SELECT   COLUMN_NAME
		FROM    INFORMATION_SCHEMA.COLUMNS 
		WHERE    TABLE_NAME = 'MyTable'
		ORDER BY  COLUMN_NAME ASC;
SET @columnList=''
	
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN  
		IF @columnList='' 
			SET @columnList = '[' + @name + ']'
		ELSE
			SET @columnList = @columnList + ',[' + @name + ']'
		
       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

SELECT @columnList

 

NOTES:

Just paste to a query window, hit ctrl-a and F5 to run it.

How To Round A Decimal Down To The Nearest Whole Number Using C#



TODO:

Have you ever wanted to round a number down to the nearest whole number using C#.  Example:  14.789 rounded down to 14

 

SOLUTION:

Math.Truncate(14.789)  //produces 14

 

NOTES:

There are no notes on this topic.

How To Find Text In SQL Server Job Agent Step Using T-SQL



TODO:

Have you ever wanted to find text in a SQL Agent Job or Job Step?

 

SOLUTION:

SELECT	j.job_id,
     s.srvname,
     j.name,
     js.step_id,
     js.command,
     j.enabled 
FROM	dbo.sysjobs j
JOIN	dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
JOIN	master.dbo.sysservers s
	ON	s.srvid = j.originating_server_id
WHERE	js.command LIKE '%Enter Text Here%'

 

NOTES:

There are no notes on this topic.

How To Download A File From A Document Library Using The SharePoint Client Object Model



TODO:

Have you ever wanted to download a document from a document library using the SharePoint client object model?

 

SOLUTION:

using SP = Microsoft.SharePoint.Client;

//class and code go here

string saveLocation = "C:\\Temp\\";

//SET YOUR ITEM HERE!!!!!!
SP.ListItem item = null;

using (SP.FileInformation fileInformation = SP.File.OpenBinaryDirect(put your context here, (string)item["FileRef"]))
{
     string fileName = (string)item["FileRef"];
     fileName = string.Concat(saveLocation, fileName.Substring(fileName.LastIndexOf("/")+1)); //get the name of the file based on last '/'

     //now save it
     using (System.IO.FileStream outPutFile = System.IO.File.OpenWrite(fileName))
     {
          fileInformation.Stream.CopyTo(outPutFile);
          fileInformation.Stream.Close();
     }
}

 

NOTES:

There are no notes on this topic

How To Zip A Directory Using C#



TODO:

Have you ever wanted to zip an entire directory using C#?

 

SOLUTION:

using System.IO.Compression;

//class and other code here...

//zip the entire directory
//parameter 1 is the directory to zip
//parameter 2 is the resulting zip file
ZipFile.CreateFromDirectory(directoryToZip, zipFilePath);

 

NOTES:

There are no notes on this topic

How To Open Explorer To A Specific Directory Using C#



TODO:

Have you ever wanted to launch explorer from your C# application to a specified directory?

 

SOLUTION:

using System.Diagnostics;

//class and other code goes here...

//set your location here
string explorerLocation = "C:\\Temp\\MyLocation";

//open the location
System.Diagnostics.Process.Start(explorerLocation);

 

NOTES:

There are no notes on this topic