.

Password issues when installing PostgreSQL

Getting the following error message:

The password specified was incorrect. Please enter the correct password for the postgres windows user account.

When the installer asks you to enter the password for the database?
SOLUTION: Try the following command (must be run as administrator, can be done from CMD prompt):

net user postgres /delete 

 

 

SQLCMD Syntax

SQLCMD Syntax (sql command)


Sqlcmd
  [-U login id] [-P password] [-S server] [-H hostname] 
  [-E trusted connection] [-d use database name] [-l login timeout]
  [-N encrypt connection] [-C trust the server certificate]
  [-t query timeout] [-h headers] [-s colseparator] [-w screen width]
  [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
  [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"]
  [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel]
  [-W remove trailing spaces] [-u unicode output]
  [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile]
  [-f <codepage> | i:<codepage>[,o:<codepage>]]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting] [-b On error batch abort]
  [-v var = "value"...]
  [-X[1] disable commands[and exit with warning]]
  [-? show syntax summary]

more here: <a title="http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx" href="http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx">http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx</a>

SQL delete/drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

Allowing Sql traffic through your firewall

To allow Sql traffic through your firewall, add the following firewall rule:

windows firewall steps:

start – control panel – windows firewall – advance settings
click on inbound rules
click “new rule”
rule type = port
tcp, specific local ports: 135, 1433, 1434,2383,4022
Allow the connection, domain & private (& possibly public too – at your own risk)

give it a name, and that’s it

Allow SQL remote access

Using SQL Server Management Studio

In Object Explorer, right-click a server and select Properties.

Click the Connections node.

Under Remote server connections, select or clear the Allow remote connections to this server check box.

 

or

USE MyDatabaseNameHere;
GO
EXEC sp_configure 'remote access', 1 ;
GO
RECONFIGURE ;
GO

SQL Server CONVERT

SQL Server CONVERT() Function

Definition and Usage

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

 

Value Description
data_type(length) Specifies the target data type (with an optional length)
expression Specifies the value to be converted
style Specifies the output format for the date/time

The table below represent the style values for datetime or smalldatetime conversion to character data:

Value
(century yy)
Value
(century yyyy)
Input/Output Standard
- 0 or 100 mon dd yyyy hh:miAM (or PM) Default
1 101 mm/dd/yy USA
2 102 yy.mm.dd ANSI
3 103 dd/mm/yy British/French
4 104 dd.mm.yy German
5 105 dd-mm-yy Italian
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mm:ss
- 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec
10 110 mm-dd-yy USA
11 111 yy/mm/dd Japan
12 112 yymmdd ISO
- 13 or 113 dd mon yyyy hh:mi:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
- 20 or 120 yyyy-mm-dd hh:mi:ss (24h)
- 21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
- 126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
- 130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
- 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

 


Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243
similar SQL topics here

Displaying a datatable

To display a Data Table:

@model System.Data.DataTable
@using System.Data;

<h2>View Table</h2>

<table>
    <thead>
    <tr>
    @foreach (DataColumn col in Model.Columns)    
    {         
        <th>@col.ColumnName</th>
    }    
    </tr>
    </thead>        
    <tbody>
    @foreach (DataRow row in Model.Rows)    
    {        
        <tr>
        @foreach (DataColumn col in Model.Columns)        
        {             
            <td>@row[col.ColumnName]</td>
        }        
        </tr>
    }    
    </tbody>
</table>

You can get data as follows:

public DataTable GetDataForSql(string sql, string connectionString)
{
    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        using(SqlCommand command = new SqlCommand())
        {
            command.CommandType = CommandType.Text;
            command.Connection = connection;
            command.CommandText = sql;
            connection.Open();          
            using(SqlDataReader reader = command.ExecuteReader())
            {
                DataTable data = new DataTable();
                data.Load(reader);
                return data;
            }

        }

    }

}

If you just wanted a DataTable then the following method is short and reduces complexity:

<code>public DataTable GetDataForSql(string sql, string connectionString)
{
    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        using(SqlCommand command = new SqlCommand())
        {
            command.CommandType = CommandType.Text;
            command.Connection = connection;
            command.CommandText = sql;
            connection.Open();          
            using(SqlDataReader reader = command.ExecuteReader())
            {
                DataTable data = new DataTable();
                data.Load(reader);
                return data;
            }

        }

    }

}</code>

usage:

<code>try{
    DataTable results = GetDataForSql("SELECT * FROM Table;", ApplicationSettings["ConnectionString"]);
}
catch(Exception e)
{
    //Logging
    //Alert to user that command failed.
}</code>

Retrieving a sample of table data

To only select a random portion of a table:

select * from patients
tablesample (.1 percent);
--or tablesample (50 rows);
--and even append: repeatable(15); 

There will be times (when you work with statistics) that this won’t give accurate results. Try the following then instead (due to the combination of paging and clustered indexes):

select avg(total) from price
where 0.001 >= cast(checksum(newid(), id) & 0x7fffffff as float) 
/ cast (0x7fffffff as int) 

SQL pivot explained

SQL pivot command:

select * from (select total, cast([date] as date)  as [date],
payment from [sales receipts]) as t
pivot
(
sum(total)
for [payment] in ([VISA], [AmEx], [MasterCard], Cash)
) as p
order by [date]

and what it pretty much does behind the scenes:

select cast([date] as date) as [date],
count(case when payment = 'VISA' THEN 1 END) as VISA,
count(case when payment = 'AmEx' THEN 1 END) as AmEx,
count(case when payment = 'MasterCard' THEN 1 END) as MasterCard,
count(case when payment = 'Cash' THEN 1 END) as Cash
from [sales receipts]
group by cast([date] as date)
order by date

Simplest way to execute a SQL query

using (var conn = new SqlConnection("server=.;database=mydatabase;integrated security=yes"))
using (var cmd = new SqlCommand("select count(*) from dbo.Patients", conn))
{
//cmd.Parameters.AddWithValue("@gender", "male");  // just example if needed
conn.Open();
var results = cmd.ExecuteReader();
}