select from xml using nodes() function

Check below script:


DECLARE @myXml AS XML
SET @myXml = 
'<ArrayOfCustomer>
        <Customer id="1">
                <Description>Mike</Description>
        </Customer>
        <Customer id="2">
                <Description>James</Description>
        </Customer>      
        <Customer id="3">
                <Description>Brian</Description>
        </Customer>      
        <Customer id="4">
                <Description>Taylor</Description>
        </Customer>      
</ArrayOfCustomer>'


SELECT
        T.c.value('@id[1]', 'int') AS [id]
        , T.c.value('Description[1]', 'nvarchar(max)') AS [description]
FROM @myXml.nodes('/ArrayOfCustomer/Customer') AS T(c)

List all tables with size and row count

Use below script to find the List of all Tables with Size and Row Count:



DECLARE @tbl table(Id int IDENTITY(1,1), Name varchar(256))


INSERT INTO @tbl
SELECT b.name + '.'+ a.name
FROM sys.tables a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id


INSERT INTO @tbl
SELECT '-1'


DECLARE @result table(TableName varchar(256)
 , TotalRows int
 , Reserved varchar(50)
 , DataSize varchar(50)
 , IndexSize varchar(50)
 , UnusedSize varchar(50))


DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1
WHILE 1=1
BEGIN
SELECT @temp = NAME FROM @tbl WHERE Id = @index


IF @temp = '-1'
BREAK
INSERT @result( TableName, TotalRows, Reserved, DataSize, IndexSize, UnusedSize)
EXEC sp_spaceused @temp


SET @index = @index + 1
END


SELECT c.name+'.'+b.name as [table]
, a.*
 FROM @result a
INNER JOIN sys.tables b
ON a.TableName = b.name
INNER JOIN sys.schemas c
ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC

Backup MSSQL Database on Windows Dedicated Server

It is necessary to take Database regularly. You never know when Server face some problem, So it is necessary to take backup on your dedicated Windows Server. You can schedule the MSSQL Database to generate backup files from the below link :-

1. Start Management Studio. Expand Databases. 
2. Right Click on the data to be backup and Select “All Tasks” 
3. Then Select “Backup Database”.
4. SQL is configured to Select “Database - Complete” by Default.
5. If you do not want to do this you can select “Database - Differential”.
6. Generally, the Destination chosen by many users to save the SQL Backup file is “C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP”.
7. Now, Double Click on the given default path and add “sqlbackup.bak” to the Existing Path.
8. Then Click OK.
9. Now, to Schedule backups select the “Schedule” option.
10. Once done, Click “…” option.
11. Now you will find a Recurring option i.e. Radio option. Click the option and Select “Change” option right after you do that.
12. Select the Time on which you would like to have the backup with the Schedule of the Backup. You will have to enter the Time on which you would like to have the Backup Directory Created and also specificy if you wish to have the Backup Directory Created Daily, Weekly or Monthly.
13. Now click “OK” on Each Dialogue box and proceed.
14. You can also check if the Backup time and the Backups to be taken are Scheduled Successfully through “C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP” Directory.
15. Now, In the Remote Data Backups only Select the SQL Backup Directory and you are done.

Error: 18456

Error 18456 is very common error in SQL Server. It generally occurs when there is some problem with the installation of MS Share Point Services. This error occurs in below mentioned scenario:

You have installed MS Windows SharePoint Services and you configured your SharePoint Services settings in SharePoint Central administration. After this, you specify the configuration database settings on Set Configuration Database Server page and then clicked 'OK'. After performing all these tasks when you clicked 'OK' you got the following error messages -

"Login failed for user 'DOMAINNAMEACCOUNTNAME'. (Error code: 18456)

Login failed for user 'NT AUTHORITYNETWORK SERVICE'. (Error code: 18456) "

Cause

If the account using for this purpose doesn't have the database creation rights in the MS SQL Server these types of errors occur. This issue generally occurs when the account is not the member of the Security Administrator role.

Solution

To solve this issue you need to add an account to the Database Creators role and to the Security Administrators role in SQL server which will certainly resolve the issue. To perform this task, just follow the below steps:

• Add an account to the Security Administrator role that the application pool is running.
• On Configuration Administrative Virtual Server page of SharePoint Central Administration create a new application pool and click 'OK'.
• Restart IIS i.e. Internet Information Services of Microsoft, click 'Run' and type ' iisreset ' in the open box and click 'OK'
• Now, on the Application Pool changes page click 'OK' and set the configuration database server page of SharePoint Central Administration after specifying the configuration database settings.

After doing all the above setting if you still get the same error then you will not be able to access the database file of MS SQL Server. In this case you have to repair the file with some third party SQL Database Repair software.

Some communication error with SQL Server

Sometime when you connect to SQL Server you may get communication that may be Client side or Server Side. Here is a list of some common errors with reasons: 

Server-side errors109 The pipe has been ended - This generally happen if a named pipes connection is lost during a write.


232 The pipe is being closed - This generally occur if a named pipes connection is lost during a read.


10053 Software caused connection abort - This generally happen if a sockets client (which includes SPX) aborts the connection.


10054 Connection reset by peer - This generally happen if a sockets client (which includes SPX) is actively sending or receiving data to or from SQL Server, and the network connection is lost.


10058 Can't send after socket shutdown - This generally happen if a sockets client (which includes SPX) is actively sending or receiving data to or from SQL Server, and the client application hangs or traps, or is abruptly shut down.


17824 Unable to write to server-side connection - This generally happen when SQL Server attempts to write to a client connection and the write fails. This could be caused by the client computer being hung or shut down, or due to network problems. 


17825 Unable to close server-side connection – This generally happen when SQL Server gets an error while attempting to close the network connection to the client.


17832 Unable to read login packet(s) - This generally happen when a client starts to connect, but never successfully completes the attempt because of a client operating system or application failure.

Client-Side Errors

10008 Bad token from SQL Server: data stream processing out of sync - Often caused by an abruptly terminated network connection, which causes a damaged Tabular Data Stream token to be read by the client.

10010 Read from SQL Server failed - The client was reading from the SQL Server network data stream, and the read returned a failure code.

10018 Error closing network connection - The client attempted to close a network connection and the close returned a failure code.

10025 Write to SQL Server failed - The client was writing to the SQL Server network data stream, and the write returned a failure code.

Besides communication errors, You may get some very serious error related to Data. For this you can use some third party software to recover data.