How to get the row count of all tables in a SQL SERVER

1) Using  sp_MSForEachTable SP

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t';

Output:  

2) Using System Table.
 

SELECT 
    sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM 
    sys.tables ta
INNER JOIN sys.partitions pa
    ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
    ON ta.schema_id = sc.schema_id
WHERE 
 ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY 
 sc.name,ta.name
ORDER BY 
 SUM(pa.rows) DESC

 

Output:  

No set method for property in WCF service

Problem:

I have some class that I'm passing as a result of a service method, and that class has a get-only property:
 

public string PropertyName
{
    get
    {
        return "Some Value";
    }    
}

 I'm getting an exception on service side "No set method for property"

Solution:

Remember that WCF needs to create an instance of the object from its serialized representation (often XML) and if the property has no setter it cannot assign a value. Objects are not transferred between the client and the server but only serialized representations, so the object needs to be reconstructed at each end.

Make setter property private. So, client can never see the set method on your property acting much like a read only property. 

public string PropertyName
{
    get
    {
        return "Some Value";
    }
    private set
    { }
}

C# datetime formatting separators issue

Problem:

Console.WriteLine(DateTime.Now.ToString("MM/dd/yyyy"));  

Output:

08-28-2013 

Expected output: 

08/28/2013

Solution:

Console.WriteLine(DateTime.Now.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture));

Reason,

If a CultureInfo is not specified, the current culture will be used.

If this is a culture that doesn't use slashes as separators in dates and 

the format string specifies the date separator to be a /, that is replaced 

by whatever the actual culture date separator is –

WCF: The maximum array length quota (16384) has been exceeded while reading XML data

Error:

"The maximum array length quota (16384) has been exceeded while reading XML data. This quota may be increased by changing the MaxArrayLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader."

Solution:

Try to change config file:

Add <readerQuotas> and set maxArrayLength="2147483647"

<binding name="WSHttpBinding_IBackup"
         closeTimeout="1:00:00"
         maxBufferPoolSize="2147483647"
         maxBufferSize="2147483647"
         maxReceivedMessageSize="2147483647"
         openTimeout="1:00:00"
         receiveTimeout="1:00:00"
         sendTimeout="1:00:00"
         transferMode="Streamed">
 
  <readerQuotas
    maxArrayLength="2147483647"
    maxBytesPerRead="2147483647"
    maxDepth="2147483647"
    maxNameTableCharCount="2147483647"
    maxStringContentLength="2147483647" />
 
  <!--if any issue then try this one-->
  <!--<textMessageEncoding messageVersion="Soap11" 
                       maxReadPoolSize="2147483647" 
                       maxWritePoolSize="2147483647">
    <readerQuotas
      maxArrayLength="2147483647"
      maxBytesPerRead="2147483647"
      maxDepth="2147483647"
      maxNameTableCharCount="2147483647"
      maxStringContentLength="2147483647" />
  </textMessageEncoding>-->
 
</binding>

If you have same issue then try following solution.

Reason:-

• My service is simply ignoring my binding “basicHttp_ServiceName” and it’s always taking default binding
• Reason is
  
  • My configuration service name does not match with service name in Service Host
  • The name I given is "ServiceName" where it has to be "XYZ.ServiceName"
  • E.g.
    

    <%@ ServiceHost Language="C#" 
                    Debug="true" 
                    Service="XYZ.ServiceName" 
                    CodeBehind="ServiceName.svc.cs" %>
    
    

Fix :-

• Right click your “.svc” file and choose “View Markup”
• Copy the Service name and paste it in your service name
• Now my Service configuration looks below
• E.g
  

   <services>
      <service name="XYZ.ServiceName">
   
      </service>
    </services>

Cannot login to network server in sql server

GO to "Sql Server Configuration Manager"

Select Sql server Services

Check following service are running
- Sql Server(Instance Name)
- Sql Server Browser

Go to "Sql Server Network Configuration"
- Select Protocol for <Instance Name>

 Now check TCP/IP and Named Pipes is Enable.

and make sure Your system Firewall should not block SQL Server port. SQL SERVER port is by Default 1433

How to check date is valid or not in Javascript

Features:
 

• Date format: MM/DD/YYYY

• Text box control values can be validated as correct date

• Month can be entered compulsory with two digits either 0 or 1 as first digit. So possible invalid entries are from 13 to 19.

• Date can be entered compulsory with two digits either 0,1,2 or 3 as first digit. So possible invalid entries are from 32 to 39.

• Entry will be validated on blur event. In case of invalid date then blank TextBox.

• It will also validate for Leap years for Feb month. Also, it will validate 31 date for April, June, Sep & Nov months.

JavaScript Function:

function isDate(txtDate) {
    var currVal = txtDate.val();
    if (currVal == '') {
        txtDate.val('');
        return false;
    }
 
    //Declare Regex 
    var rxDatePattern = /^(\d{1,2})(\/|-)(\d{1,2})(\/|-)(\d{4})$/;
    var dtArray = currVal.match(rxDatePattern); // is format OK?
 
    if (dtArray == null) {
        txtDate.val('');
        return false;
    }
 
    //Checks for mm/dd/yyyy format.
    dtMonth = dtArray[1];
    dtDay = dtArray[3];
    dtYear = dtArray[5];
 
    if (dtMonth < 1 || dtMonth > 12) {
        txtDate.val('');
        return false;
    }
    else if (dtDay < 1 || dtDay > 31) {
        txtDate.val('');
        return false;
    }
    else if ((dtMonth == 4 || dtMonth == 6 || dtMonth == 9 || dtMonth == 11) && dtDay == 31) {
        txtDate.val('');
        return false;
    }
    else if (dtMonth == 2) {
        var isleap = (dtYear % 4 == 0 && (dtYear % 100 != 0 || dtYear % 400 == 0));
        if (dtDay > 29 || (dtDay == 29 && !isleap)) {
            txtDate.val('');
            return false;
        }
    }
    return true;
}

Check Foreign key exists in MySQL

If you want to add/drop foreign key then you need to check is foreign key is exist or not.

IF NOT EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE
                   CONSTRAINT_SCHEMA = DATABASE() AND
                   CONSTRAINT_NAME   = 'fk_rabbits_main_page' AND
                   CONSTRAINT_TYPE   = 'FOREIGN KEY') THEN
   ALTER TABLE `rabbits` ADD CONSTRAINT `fk_rabbits_main_page`
                             FOREIGN KEY (`main_page_id`)
                             REFERENCES `rabbit_pages` (`id`);
END IF

Using following query you will get more information about foreign key with column details.

 SELECT 
    TABLE_NAME,COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
    TABLE_NAME='TABLENAME' 
    AND REFERENCED_TABLE_NAME = 'REFERENCED_TABLE_NAME' 
    AND CONSTRAINT_NAME='FK_Key_Name'
    AND REFERENCED_COLUMN_NAME='Code';