SQL COUNT

SQL Server > Built-in Functions > COUNT

COUNT returns the number of rows that matches a specified criteria.
The NULL values will not be count.

Example

SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) FROM table_name
SELECT COUNT(DISTINCT column_name) FROM table_name

How to pass Database logon info to a Crystal Report at runtime in C#

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

private CrystalReport1 crReportDocument = new
CrystalReport1 ();
private Database crDatabase;
private Tables crTables;
private Table crTable;
private TableLogOnInfo crTableLogOnInfo;
private ConnectionInfo crConnectionInfo = new
ConnectionInfo ();

//Setup the connection information structure
//to log on to the data source for the report.
// If using ODBC, this should be the DSN. If using
// OLEDB, etc, this should be the physical server name


crConnectionInfo.ServerName = "DSN or
Server Name";

// If you are connecting to Oracle there is no
// DatabaseName. Use an empty
// string i.e. crConnectionInfo.DatabaseName = "";

crConnectionInfo.DatabaseName = "DatabaseName";
crConnectionInfo.UserID = "Your UserID";
crConnectionInfo.Password = "Your Password";

// This code works for both user tables and stored
procedures

//Get the table information from the report
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;

//Loop through all tables in the report and apply the
//connection information for each table.
for (int i = 0; i < crTables.Count; i++)
{
crTable = crTables ;
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo =
crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);

//If your DatabaseName is changing at runtime, specify
//the table location. For example, when you are
reporting
//off of a Northwind database on SQL server
//you should have the following line of code:

crTable.Location = crConnectionInfo.DatabaseName + ".dbo." +
crTable.Location.Substring(crTable.Location.LastIndexOf
(".") + 1)
}

//Set the viewer to the report object to
//be previewed.

crystalReportViewer1.ReportSource =
crReportDocument;

Default web browser Internet Explorer

Tools -> Internet Options ->Programs


Tools -> Internet

Added Microsoft Access as linked server SQL Server

SQL Server > Linked Server > Add

sp_addlinkedserver

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources

sp_addlinkedsrvlogin

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

Example: Add Microsoft Access as linked server SQL Server
 

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'ACCES',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\data\DB1.mdb'
GO

-- set password

EXEC sp_addlinkedsrvlogin 'ACCES', false, 'sa', 'Admin', NULL

First, last day of the month Oracle

Oracle > Date & Time > First day and last day of the month Oracle

SELECT (trunc(sysdate, 'mm')) first_day FROM dual;

SELECT last_day(trunc(sysdate)) last_date FROM dual;

--result

FIRST_DAY
-----------
12/1/2010

LAST_DATE
-----------
12/31/2010

Duplicate rows SQL Server

SQL Server > Scripts > Duplicate rows SQL Server

Here is a script to find number of duplicate rows in SQL Server

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL

DROP TABLE tbl1;
GO
 

CREATE TABLE tbl1

(
       id     int ,
       name   varchar(50)
);
GO
 

INSERT INTO tbl1 values  (1,'john'), (2,'dan') , (1,'john')

GO
 

SELECT

  id,
  count(name) 'Number of duplicate rows'
FROM
  tbl1
GROUP BY
  id
HAVING
  count(name) > 1
GO

Result:
id Number of duplicate rows
1 2

 

send fax C#

Send fax C# using using FAXCOMLib;

public void SendFax(string DocumentName, string FileName, string RecipientName, string FaxNumber)
 {
         FAXCOMLib.FaxServer faxServer = new FAXCOMLib.FaxServerClass();
         faxServer.Connect(Environment.MachineName);
         FAXCOMLib.FaxDoc faxDoc = (FAXCOMLib.FaxDoc)faxServer.CreateDocument(FileName);
         faxDoc.RecipientName = RecipientName;
         faxDoc.FaxNumber = FaxNumber;
         faxDoc.Tsid = "Fax";
         faxDoc.DisplayName = DocumentName;
         int Response = faxDoc.Send();
          faxServer.Disconnect();
}