Browsed by
Category: Databases

Retrieve Server Name and Instance from MS SQL Server (TSQL)

Retrieve Server Name and Instance from MS SQL Server (TSQL)

SELECT SERVERPROPERTY(‘MachineName’) AS [ServerName], SERVERPROPERTY(‘ServerName’) AS [ServerInstanceName], SERVERPROPERTY(‘InstanceName’) AS [Instance], SERVERPROPERTY(‘Edition’) AS [Edition], SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion], Left(@@Version, Charindex(‘-‘, @@version) – 2) As VersionName

SQL Server (TSQL) vs. Oracle (PLSQL) Data Types

SQL Server (TSQL) vs. Oracle (PLSQL) Data Types

SQL Server Oracle Exact Numerics TINYINT NUMBER(3) SMALLINT NUMBER(5) INTEGER NUMBER(10) BIGINT NUMBER(19) DECIMAL(p,s) NUMBER(p,s) NUMERIC(p,s) NUMBER(p,s) SMALLMONEY NUMBER(10,4) MONEY NUMBER(19,4) Approximate Numerics REAL BINARY_FLOAT FLOAT BINARY_DOUBLE Date Time SMALLDATETIME TIMESTAMP(3) DATETIME TIMESTAMP(3) DATETIME2(fs) TIMESTAMP(fs) DATETIMEOFFSET(fs) TIMESTAMP (fs) WITH TIME ZONE DATETIMEOFFSET(fs) TIMESTAMP (fs) WITH LOCAL TIME ZONE Character strings CHAR(x) CHAR(x) VARCHAR(x) ARCHAR2(x) VARCHAR(MAX) CLOB TEXT LONG Binary strings BINARY(n) RAW(n) VARBINARY(n) LONG RAW VARBINARY(MAX) LONG RAW or BLOB IMAGE LONG RAW Binary strings XML XMLTYPE BIT NUMBER(1) TIMESTAMP…

Read More Read More

How to Recover SA Password and Start SQL Server in Single User Mode

How to Recover SA Password and Start SQL Server in Single User Mode

Recover SA Password on Microsoft SQL Server Introduction This article applies to Microsoft SQL Server 2005, and Microsoft SQL Server 2008 at the time of this writing. If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server…

Read More Read More

MySQL JDBC Connector

MySQL JDBC Connector

Download JConnector from Oracle, unpack and copy to the jar file to …jre\lib\ext folder of your Java installation. It will be automatically included in the default library available to every project you create.   Testing the JDBC /* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package helloworld; /* JDBC-MySQL */ import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException;…

Read More Read More

Parsing HTML to SQL using SQLDOM

Parsing HTML to SQL using SQLDOM

All Articles Source:  http://www.sqlservercentral.com/articles/HTML/88605/ Parsing HTML to SQL using SQLDOM By David Rueter, 2014/12/26 (first published: 2012/04/16)           Microsoft SQL incorporates a good XML parser. Unfortunately, the XML parser is not very useful for parsing out HTML. Why would anyone want to parse HTML in SQL? There are three main reasons that I can think of: Import data contained in HTML directly to SQL tables Manipulate, analyze and/or normalize HTML data Render data stored in SQL…

Read More Read More

SQL Server Developer Interview Questions

SQL Server Developer Interview Questions

Interview Questions – SQL Developer SOURCE: http://www.sqlservercentral.com/articles/Career/96536/ 15 Quick Short Interview Questions Useful When Hiring SQL Developers By Thomas Kovarik, 2014/12/25 (first published: 2013/02/28) Here is a useful exam that you can administer in either 10 or 15 minutes, either oral or written, when you interview for hiring permanent or contract workers for jobs in SQL Server development or support. (This is not a test for SQL Data Base Administrators.) After the list of 15 questions (below) you will find…

Read More Read More

TSQL Table Row Counts for All Database Tables

TSQL Table Row Counts for All Database Tables

TSQL Table Row Counts for All Database Tables SELECT TableName = t.NAME, TableSchema = s.Name, RowCounts = p.rows FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 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 WHERE t.is_ms_shipped = 0 GROUP BY t.NAME, s.Name, p.Rows ORDER BY s.Name, t.Name; go  

TSQL Delete Large Number of Rows Without Filling the Transaction Log

TSQL Delete Large Number of Rows Without Filling the Transaction Log

TSQL Delete Large Number of Rows Without Filling the Transaction Log Script to delete in chunks with commit every number of rows:   DECLARE @Count INT Declare @for_delete INT Declare @chunk_size INT SELECT @chunk_size=10000 SELECT @Count = 0 select @for_delete=count(*)from [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’ While (@Count < @for_delete) BEGIN SELECT @Count = @Count + @chunk_size BEGINTRAN DELETE top(@chunk_size) FROM [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’ COMMIT TRAN   Ref Source: http://dbtricks.com/?p=44  5/22/2015

Database Mirroring and Replication (SQL Server 2014)

Database Mirroring and Replication (SQL Server 2014)

Database Mirroring and Replication (SQL Server) SQL Server 2014 Other Versions SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005 SQL Server 2016 Database mirroring can be used in conjunction with replication to improve availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients. This copy is known as the principal…

Read More Read More