Category Archives: SQL Server

Databases SQL Server Tips & tricks Transact-SQL

How to get different SQL Server date formats

Below is a list of formats and an example of the output.  The date used for all of these examples is “2006-12-30 00:38:54.840”.

DATE ONLY FORMATS
Format # Query Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
12 select convert(varchar, getdate(), 12) 061230
23 select convert(varchar, getdate(), 23) 2006-12-30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
112 select convert(varchar, getdate(), 112) 20061230
TIME ONLY FORMATS
8 select convert(varchar, getdate(), 8) 00:38:54
14 select convert(varchar, getdate(), 14) 00:38:54:840
24 select convert(varchar, getdate(), 24) 00:38:54
108 select convert(varchar, getdate(), 108) 00:38:54
114 select convert(varchar, getdate(), 114) 00:38:54:840
DATE & TIME FORMATS
0 select convert(varchar, getdate(), 0) Dec 12 2006 12:38AM
9 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) 30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) 2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) 2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) 12/30/06 12:38:54 AM
25 select convert(varchar, getdate(), 25) 2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840
FORMATS WITH ISSUES
130 select convert(varchar, getdate(), 130) 10 ?? ????? 1427 12:38:54:840A
131 select convert(varchar, getdate(), 131) 10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output
select replace(convert(varchar, getdate(),101),’/’,”) 12302006
select replace(convert(varchar, getdate(),101),’/’,”) + replace(convert(varchar, getdate(),108),’:’,”) 12302006004426

read more »

Code Snippets SQL Server Transact-SQL

DBCC SHRINKFILE (Transact-SQL)

A. Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.


USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.


USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

C. Truncating a data file

The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried to obtain the file_id of the data file.


 

USE AdventureWorks2012;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.


USE AdventureWorks2012;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2012
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql

SQL Server

SQL Server: The transaction log for database is full

Right-click your database in SQL Server Manager, and check the Options page. Switch Recovery Model from Full to Simple then right-click the database again. Select Tasks Shrink, Files. Shrink the log file to a proper size (I generally stick to 20-25% of the size of the data files).

Switch back to Full Recovery Model, and take a full database backup straight away.

You now need to figure out whether you need the ability to recover your database to a point-in-time. If so, schedule regular transaction log and perhaps differential backups according to the requirements of your business.

In case the business can live with a certain amount of data loss, schedule full backups and switch to Simple mode.

SQL Server

Query to list number of records in each table in a database

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 
SQL Server Transact-SQL

SQL SERVER – FIX : Error 15023: User already exists in current database.

Error 15023: User already exists in current database.

1) This is the best Solution.

First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified

USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

more details in http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

Microsoft SQL SQL Server

SQL Server, Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

read more »

SQL SQL Server Transact-SQL

Recursive Queries using Common Table Expressions (CTE) in SQL Server

Given the example , hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused.

Syntax

WITH cte_alias (column_aliases)
AS
(
cte_query_definition   --initialization
UNION ALL
cte_query_definition2 --recursive execution
)
SELECT * FROM cte_alias

Sample (from Root nodes to Leaf  noes)

WITH Managers AS
(
--initialization
SELECT EmployeeID, LastName, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use.

WITH xEmployees AS
(
--initialization
SELECT  EmployeeID, LastName, ReportsTo
FROM Employees
WHERE EmployeeID IN (55,98,65,12)  -- sample Leaf nodes to filter/use
UNION ALL
--recursive execution
SELECT a.EmployeeID,a.LastName,a.ReportsTo
FROM Employees a INNER JOIN xEmployees m
ON a.EmployeeID = m.reportsTo
)
SELECT Distinct EmployeeID, LastName, ReportsTo  FROM xEmployees

!Importante to use “Distinct” statement!

SQL Server Transact-SQL

get a substring in SQL SERVER using space as separator

get a substring in SQL SERVER using space as separator


SELECT CASE CHARINDEX(' ', news.description, 40)
  WHEN 0 THEN news.description
  ELSE SUBSTRING(news.description, 0, CHARINDEX(' ', news.description, 40) - 0)
  END
 AS FirstWord
FROM news

source:
http://www.dreamincode.net/forums/topic/239440-how-can-i-get-a-substring-in-sql-server-using-space-as-separator/
CHARINDEX (Transact-SQL) http://msdn.microsoft.com/pt-pt/library/ms186323.aspx

SUBSTRING (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms187748.aspx

Microsoft SQL Server

How to enable remote Connection sql server 2008

Error message:

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”

How to solve this issue?

There are a couple of things that might be going on here… (All of the following configurations are made on the computer running your SQL Server 2008 instance)

Allow remote connections to this server

The first thing you want to check is if Remote Connections are enabled on your SQL Server database. In SQL Server 2008 you do this by opening SQL Server 2008 Management Studio, connect to the server go to Properties -> Connections -> Check “Allow remote connections to this server”.

for further issues check  http://blogs.msdn.com/b/walzenbach

Microsoft SQL Server

Problem with Identity Specification in SQL…

Error message when changing the identity column to “Yes” and save the changes:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

It’s not a bug. It’s a safety measure added to SQL 2008’s management studio to indicate that the change that you’ve requested to the table requires that the table be dropped and recreated.

It’s there so that people don’t ‘accidentally’ make changes in production that will take hours.

use “SET IDENTITY_INSERT” command, for more info, see SQL BOL.

Or turn off the warning under tools–>options–>designers–>table and database designers

uncheck prevent saving changes that require table re-creation