Category: SQL

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 contained carriage returns as delimiters (which looked like spaces in Management Studio) so my SQL statement turned out to be something like this:

--; can be change to whatever is useful
SELECT
REPLACE(column_with_carriage_return_delimiters, CHAR(13), ';')
FROM MyDBTable

Now I could copy/paste the result in a text editor and replace ; with whatever I needed. There maybe a better solution but this is what I came up with so far.

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 it expecting the results to be ordered as specified in the view, it’s not going to happen.

-- Select From View
SELECT lProductID, vsProduct
FROM vwProduct

This would return the results ordered ascending not descending:
lProductId  vsProduct
———– ———
1           Widget A
2           Widget B
3           Widget C
4           Widget D
5           Widget E

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 documentation on Querying the SQL Server System Catalog, it contains some information on backward compatibility and future releases of 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'

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 problem by changing the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)” to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

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 is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string, or not specified.

Report

Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string, or not specified.

Update_One

Links the specified user in the current database to login. login must already exist. user and login must be specified.

[@UserNamePattern =] 'user'

Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can only be used with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows users.

[@LoginName =] 'login'

Is the name of a SQL Server login. login is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Permissions

Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.

Examples

A. Show a report of the current user to login mappings

This example produces a report of the users in the current database and their security identifiers.

EXEC sp_change_users_login 'Report'

B. Change the login for a user

This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).

–Add the new login.

USE master
GO

EXEC sp_addlogin 'NewMary'
GO

–Change the user account to link with the ‘NewMary’ login.

USE pubs
GO

EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

Related Article: http://msdn2.microsoft.com/en-us/library/ms174378.aspx