sql-server-2005

Find non-default collation on columns for all tables in SQL Server

by

Try this script here: DECLARE @DatabaseCollation VARCHAR(100) SELECT @DatabaseCollation = collation_name FROM sys.databases WHERE database_id = DB_ID() SELECT @DatabaseCollation ‘Default database collation’ SELECT t.Name ‘Table Name’, c.name ‘Col Name’, ty.name ‘Type Name’, c.max_length, c.collation_name, c.is_nullable FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE … Read more

Splitting delimited values in a SQL column into multiple rows

by

If you are on SQL Server 2016+ You can use the new STRING_SPLIT function, which I’ve blogged about here, and Brent Ozar has blogged about here. SELECT s.[message-id], f.value FROM dbo.SourceData AS s CROSS APPLY STRING_SPLIT(s.[recipient-address], ‘;’) as f; If you are still on a version prior to SQL Server 2016 Create a split function. … Read more

How do I convert a number to a numeric, comma-separated formatted string?

by

The reason you aren’t finding easy examples for how to do this in T-SQL is that it is generally considered bad practice to implement formatting logic in SQL code. RDBMS’s simply are not designed for presentation. While it is possible to do some limited formatting, it is almost always better to let the application or … Read more

SQL Server pivot vs. multiple join

by

The answer will of course be “it depends” but based on testing this end… Assuming 1 million products product has a clustered index on product_id Most (if not all) products have corresponding information in the product_code table Ideal indexes present on product_code for both queries. The PIVOT version ideally needs an index product_code(product_id, type) INCLUDE … Read more

SSIS Connection Manager Not Storing SQL Password

by

You can store the password in the configuration string by going to properties and adding password=yourpassword, but it’s very important to put a space after the ; on the line before password and after the ; on the password line, as shown below: Data Source=50.21.65.225;User ID=vc_ssis; password=D@mc317Feo; Initial Catalog=Sales; Provider=SQLNCLI10.1; Persist Security Info=True;Auto Translate=False; Application … Read more

SELECT with a Replace()

by

Don’t use the alias (P) in your WHERE clause directly. You can either use the same REPLACE logic again in the WHERE clause: SELECT Replace(Postcode, ‘ ‘, ”) AS P FROM Contacts WHERE Replace(Postcode, ‘ ‘, ”) LIKE ‘NW101%’ Or use an aliased sub query as described in Nick’s answers.

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)