sql-server-2008-r2 – Page 8

How to query for Xml values and attributes from table in SQL Server?

January 5, 2023 by Tarik

Actually you’re close to your goal, you just need to use nodes() method to split your rows and then get values: select s.SqmId, m.c.value(‘@id’, ‘varchar(max)’) as id, m.c.value(‘@type’, ‘varchar(max)’) as type, m.c.value(‘@unit’, ‘varchar(max)’) as unit, m.c.value(‘@sum’, ‘varchar(max)’) as [sum], m.c.value(‘@count’, ‘varchar(max)’) as [count], m.c.value(‘@minValue’, ‘varchar(max)’) as minValue, m.c.value(‘@maxValue’, ‘varchar(max)’) as maxValue, m.c.value(‘.’, ‘nvarchar(max)’) as Value, … Read more

Calculating distance between two points (Latitude, Longitude)

January 1, 2023 by Tarik

Since you’re using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data: DECLARE @source geography = ‘POINT(0 51.5)’ DECLARE @target geography = ‘POINT(-3 56)’ SELECT @source.STDistance(@target) Gives ———————- 538404.100197555 (1 row(s) affected) Telling us it is about 538 km from (near) London to (near) Edinburgh. … Read more

A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)

December 26, 2022 by Tarik

For SQL2008, open Management Studio Rt click on instance go to properties select Security Under Server Authentication, check SQL Server and Windows Authentication Mode hit OK Restart the server using configuration manager.

Partition Function COUNT() OVER possible using DISTINCT

December 25, 2022 by Tarik

There is a very simple solution using dense_rank() dense_rank() over (partition by [Mth] order by [UserAccountKey]) + dense_rank() over (partition by [Mth] order by [UserAccountKey] desc) – 1 This will give you exactly what you were asking for: The number of distinct UserAccountKeys within each month.

Search of table names

December 22, 2022 by Tarik

I’m using this and works fine SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%%’

Could not load file or assembly Microsoft.SqlServer.management.sdk.sfc version 11.0.0.0

December 20, 2022 by Tarik

Problem: (Sql server 2014) This issue happens when assembly Microsoft.SqlServer.management.sdk.sfc version 12.0.0.0 not found by visual studio. Solution: just go to http://www.microsoft.com/en-us/download/details.aspx?id=42295 and download: ENU\x64\SharedManagementObjects.msi for X64 OS or ENU\x86\SharedManagementObjects.msi for X86 OS, then install it, and restart visual studio. PS: You may need install DB2OLEDBV5_x64.msi or DB2OLEDBV5_x86.msi too. Problem: (Sql server 2012) This issue … Read more

SQL Server 2008: How to query all databases sizes?

December 15, 2022 by Tarik

with fs as ( select database_id, type, size * 8.0 / 1024 size from sys.master_files ) select name, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB from sys.databases db

Check if table exists and if it doesn’t exist, create it in SQL Server 2008

November 21, 2022 by Tarik

Something like this IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]’) AND type in (N’U’)) BEGIN CREATE TABLE [dbo].[YourTable]( …. …. …. ) END

SQL Server – Create a copy of a database table and place it in the same database?

November 20, 2022 by Tarik

Use SELECT … INTO: SELECT * INTO ABC_1 FROM ABC; This will create a new table ABC_1 that has the same column structure as ABC and contains the same data. Constraints (e.g. keys, default values), however, are -not- copied. You can run this query multiple times with a different table name each time. If you … Read more

SQL Update with row_number()

November 20, 2022 by Tarik

One more option UPDATE x SET x.CODE_DEST = x.New_CODE_DEST FROM ( SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST FROM DESTINATAIRE_TEMP ) x