Categories
SQL SQL Server

How To Convert Confusing Ascii Code To A Character

CHAR can be used to insert control characters into character strings. The frequently used control characters are: CHAR(9) = Tab CHAR(10) = Line Feed CHAR(13) = Carriage Return My problem was trying to export data in Management Studio from a 3rd party application database into a spreadsheet. I found knowing this useful when a column […]

Categories
SQL SQL Server

TOP 100 PERCENT and ORDER BY

In SQL Server 2008, the TOP 100 PERCENT and ORDER BY is ignored. In SQL Server 2005, the output order isn’t guaranteed. For example if you created a view like so: CREATE VIEW vwProduct AS SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl ORDER BY lProductID DESC GO And tried to select from […]

Categories
SQL SQL Server

SQL 2005 Stored Procedures Last Modify Date

USE Database –Change to the database you want to use SELECT name, create_date, modify_date FROM sys.objects –User defined objects system view WHERE type = ‘P’ –Only return stored procedures AND modify_date > ‘2009-09-01’ –If you aren’t looking for a specific data range, take this line out ORDER BY modify_date DESC Read sys.objects documentation Also read […]

Categories
SQL

Oracle – Start With…Connect By

http://halisway.blogspot.com/2006/04/sql-connect-by.html http://www.java2s.com/Code/Oracle/Hierarchical-Query/HierarchicalQueriesSTARTWITHandCONNECTBYPRIORclauses.htm

Categories
SQL SQL Server

sp_rename

Changes the name of a user-created object (i.e. table, column, or user-defined data type) in the current database. Rename a table This example renames the customers table to custs. EXEC sp_rename ‘customers’, ‘custs’ Rename a column This example renames the contact title column in the customers table to title. EXEC sp_rename ‘customers.[contact title]’, ‘title’, ‘COLUMN’

Categories
ASP.NET SQL SQL Server

Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection.

The cause of this problem is the SQL server has been configured to operate in “Windows Authentication Mode (Windows Authentication)” and doesn’t allow the use of SQL accounts. This was the default setting in my case, I got the error after installing a new instance of SQL Server 2005. I was able to solve this […]

Categories
SQL SQL Server

Getting Database Stats

You could use sp_helpdb [ [ @dbname= ] ‘name’ ]

Categories
SQL

sp_change_users_login (Transact-SQL)

Syntax sp_change_users_login [@Action =] ‘action’ [,[@UserNamePattern =] ‘user’] [,[@LoginName =] ‘login’] Arguments [@Action =] ‘action’ Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values. Auto_Fix Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It […]

Categories
SQL

Inner Join On Two SQL Databases

SELECT t1.columnName, t2.columnName FROM databaseName.user.table AS t1 INNER JOIN databaseName.user.table AS t2 ON t1.id = t2.id

Categories
SQL

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

INSERT INTO MyTable (FirstCol, SecondCol) SELECT ‘First’, 1 UNION ALL SELECT ‘Second’, 2 UNION ALL SELECT ‘Third’, 3