Opening Firewall for SQL Server allowing TCP access

From Microsoft TechNext

When you can't access a remote sql server, always change the firewall 1st.
the confirm what the sa / user password.

To open a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Create a new Guid in T-SQL

Use NewID() function.

1	SELECT newid()

Result:

1	1CA040D5-53D6-4E7C-8D36-1000C8B03A91

How to take 1 record from an INNER JOIN

Hit a problem moving data between systems where there is legitimate duplicate mappings between the two systems but I need just one.  and It really does not matter which one.  basically I want to take SELECT TOP 1 on records coming off a join:
I should use this approach more but it can be slower under some conditions

SELECT
        A.RetailerID,
        X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID 

ORDER BY B.RetailerIDTheirs ASC) AS X

Interesting SQL Arithmetic overflow error problem

Hit an interesting SQL problem:
There were a number of int and numeric fields being converted to varchar() fields migrating data from System2 to System1.  I eliminated the obvious fields and hadn't solved it.  There were a number of columns with NULL values - one in particular: StandardTurnAroundTime

Cause:

 INSERT INTO SYSTEM1.dbo.W_EXAM (Exam_Duration_Tm)

SELECT StandardTurnAroundTime

FROM SYSTEM2.[dbo]. tbProcedures

Exam_Duration_Tm – varchar(4)

StandardTurnAroundTime –numeric(5.2) – max value 999.99 would cause Arithmetic overflow error

Turns out NULL value as tbProcedures. StandardTurnAroundTime was causing the issue.

Fix:

       CONVERT( varchar(4),CONVERT(int,ISNULL(StandardTurnAroundTime,0)))

INSERT INTO  SYSTEM1 .dbo.W_EXAM (Exam_Duration_Tm)

SELECT CONVERT( varchar(4),CONVERT(int,ISNULL(StandardTurnAroundTime,0)))

FROM  SYSTEM2 .[dbo]. tbProcedures

Returns values that will fit varchar(4) field even for values at the max of numeric(5,2) 999.99.

I was careful to test maximum values for numeric(5.2) to be converted to varchar(4).

How many times this would have been caught by manual code review?  

How to re-seed identity columns

DBCC CHECKIDENT (yourtable, reseed, 34)

How to drop linked Server from SQL Server 2008

Another task I can't seem to remember.

Two useful system stored procedures showing linked servers in SQL 2005/2008


exec sp_helpserver
exec sp_linkedservers

I had trouble removing a linked server because I forgot to remove all of the logins.
(seems like removing a linked server should cascade remove the associated logins)

exec sp_droplinkedsrvlogin 'FUSIONRIS','sa'
exec sp_droplinkedsrvlogin 'FUSIONRIS',NULL
exec sp_dropserver 'FUSIONRIS'

How to append to a text field in t-sql SQL Server 2005/2008

update
tablename
set
fieldname = convert(nvarchar(max),fieldname) + 'appended string'

Multiple Cursor Example

DECLARE @IPaddress nvarchar(40)
DECLARE @PartnerName nvarchar(40)
DECLARE @AllMessageCodes nvarchar(200)
DECLARE @MessageCode nvarchar(10)

DECLARE @Partners TABLE
(
IPAddress nvarchar(40),
PartnerName nvarchar(40),
AllMessageCodes nvarchar(200)
)


DECLARE cur CURSOR FOR
select distinct p.IPaddress, p.PartnerName
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'

OPEN cur
FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes=''

DECLARE curMessages CURSOR FOR
select M.MessageCode
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'
and p.IPaddress =@IPaddress and p.PartnerName=@PartnerName
GROUP BY p.IPaddress, p.PartnerName, M.MessageCode

OPEN curMessages
FETCH NEXT FROM curMessages INTO @MessageCode
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes = @AllMessageCodes + ' ' + @MessageCode
--SELECT @MessageCode

FETCH NEXT FROM curMessages INTO @MessageCode
END
CLOSE curMessages
DEALLOCATE curMessages

INSERT INTO @Partners(IPAddress, PartnerName, AllMessageCodes)
SELECT @IPaddress, @PartnerName, @AllMessageCodes

FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
END
CLOSE cur
DEALLOCATE cur

INSERT IDENTITY syntax

Piece of SQL knowledge that I can't seem to keep in my brain...

SET IDENTITY_INSERT table ON

SET IDENTITY_INSERT table OFF


Now to Reseed the Identity column in a table


DBCC CHECKIDENT (table, reseed, newseedvalue)

Query tables containing same column name

I use this type of query all the time...

SELECT o.name, o.xtype, c.name, c.length, c.type
FROM sysobjects o
INNER JOIN syscolumns c on o.id = c.id
WHERE ((c.name ='CPTCode' AND o.xtype='U') OR o.name = 'tbcdCPTCodes' and c.name ='Code')
ORDER by o.name

Read SQL blob as byte array and save to File

This is an example that fetches a report binary stored as a blob in a SQL server table.

tbBillingPaperReport: SQL Server table where report binary is stored in column Report file.
Writes files root folder the current user document and settings folder
example:  C:\Documents and Settings\Gkindel\

using System;
using System.IO;
using System.Data.SqlClient;

private const string _SQLConnectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};";

SqlConnection db = new SqlConnection(string.Format(_SQLConnectionString,"ServerName", "DBName", "user", "password"));

db.Open();

SqlCommand cmd = new SqlCommand("SELECT ReportID, FileVersion, ReportName, ReportFile FROM tbBillingPaperReport", db);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);

string savePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.Personal)).ToString();                    

foreach (DataRow row in table.Rows)
{
     byte[] report = row["ReportFile"] as byte[];
     string filename = string.Format("0}\\{1}.rpt",savePath,row["ReportName"]);                           

     BinaryWriter writer = new BinaryWriter(File.Open(filename,FileMode.Create));
     writer.Write(report);
     writer.Close();
}

Table Variable Example

DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)

Why stored procedures recompile

Some articles how to minimize stored proc recompiles:

Identifying Stored Procedure Recompilation Problems in SQL Server 2000

Minimizing Stored Procedure Recompilation Problems in SQL Server 2000

Optimizing SQL Server Stored Procedures to Avoid Recompiles

Clear SQL caches

From Devx.com

When tuning SQL Server applications, a certain degree of hands-on experimenting must occur. Index options, table design, and locking options are items that can be modified to increase performance. When running a test, be sure to have SQL Server start from the same state each time. The cache (sometimes referred to as the buffer) needs to be cleared out. This prevents the data and/or execution plans from being cached, thus corrupting the next test. To clear SQL Server’s cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Proper Useage related Indexed Views

Hidden Gotcha with Indexed Views NoExpand vs Expand See microsoft article

The NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index - basically I thought this was the main purpose for an indexed View...

Using the NOEXPAND view hint

When SQL Server processes queries that refer to views by name, the definitions of the views normally are expanded until they refer only to base tables. This process is called view expansion. It's a form of macro expansion.

The NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index. It prevents view expansion. The NOEXPAND hint can only be applied if the indexed view is referenced directly in the FROM clause. For example,

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...

Use NOEXPAND if you want to be sure to have SQL Server process a query by reading the view itself instead of reading data from the base tables. If for some reason SQL Server chooses a query plan that processes the query against base tables when you'd prefer that it use the view, consider using NOEXPAND. You must use NOEXPAND in all versions of SQL Server other than Developer and Enterprise editions to have SQL Server process a query against an indexed view directly. You can see a graphical representation of the plan SQL Server chooses for a statement using the SQL Server Management Studio tool Display Estimated Execution Plan feature. Alternatively, you can see different non-graphical representations using SHOWPLAN_ALL, SHOWPLAN_TEXT, or SHOWPLAN_XML. See SQL Sever books online for a discussion of the different versions of SHOWPLAN.

Using the EXPAND VIEWS query hint

When processing a query that refers to a view by name, SQL Server always expands the views, unless you add the NOEXPAND hint to the view reference. It attempts to match indexed views to the expanded query, unless you specify the EXPAND VIEWS query hint in an OPTION clause at the end of the query. For example, suppose there is an indexed view View1 in the database. In the following query, View1 is expanded based on its logical definition (its CREATE VIEW statement), and then the EXPAND VIEWS option prevents the indexed view for View1 from being used in the plan to solve the query.

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...
OPTION (EXPAND VIEWS)

Use EXPAND VIEWS if you want to be sure to have SQL Server process a query by accessing data directly from the base tables referenced by the query, instead of possibly accessing indexed views. EXPAND views may in some cases help eliminate lock contention that could be experienced with an indexed view. Both NOEXPAND a

Script to Randomly create names and SSN

Script to Randomly create names and SSN from RIS database


DECLARE @Firstnames TABLE(ID int identity(1,1), Firstname varchar(12))
DECLARE @LastNames TABLE(ID int identity(1,1), LastName varchar(20))
DECLARE @FirstNameCount int
DECLARE @LastNameCount int
DECLARE @PatientCount int
DECLARE @PatientID int

DECLARE @FirstNameRandom INT
DECLARE @LastNameRandom INT

DECLARE @Upper INT
DECLARE @Lower INT

INSERT INTO @Firstnames (Firstname)
SELECT FirstName FROM tbPatients GROUP BY FirstName

SELECT @FirstNameCount= Count(*) FROM @Firstnames

INSERT INTO @LastNames (LastName)
SELECT LastName FROM tbPatients GROUP BY LastName

SELECT @LastNameCount= Count(*) FROM @LastNames

SELECT @PatientCount=COUNT(*) FROM tbPatients

SELECT @FirstNameCount, @LastNameCount,@PatientCount

UPDATE tbPatients SET SSN=null


DECLARE cursor_Patients CURSOR FOR
SELECT PatientID FROM tbPatients
OPEN cursor_Patients
FETCH NEXT FROM cursor_Patients INTO @PatientID
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @Lower=1
SET @Upper=@FirstNameCount
SELECT @FirstNameRandom = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SET @Upper=@LastNameCount
SELECT @LastNameRandom = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

UPDATE tbPatients
SET FirstName = (SELECT Firstname FROM @Firstnames WHERE ID=@FirstNameRandom),
LastName = (SELECT LastName FROM @LastNames WHERE ID=@LastNameRandom)
WHERE PatientID=@PatientID

UPDATE tbPatients
SET SSN = (SELECT CONVERT(varchar(9),CONVERT(int,ROUND(((999999999 - 110000000 -1) * RAND() + 110000000), 0))))
WHERE PatientID=@PatientID

FETCH NEXT FROM cursor_Patients INTO @PatientID
END
CLOSE cursor_Patients
DEALLOCATE cursor_Patients


SELECT FirstName, LastName, SSN FROM tbPatients

SQL Cursor example

Good cursor example here

DECLARE cursor_Patients CURSOR FOR
SELECT PatientID FROM tbPatients
OPEN cursor_Patients
FETCH NEXT FROM cursor_Patients INTO @PatientID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @PatientID
FETCH NEXT FROM cursor_Patients INTO @PatientID
END
CLOSE cursor_Patients
DEALLOCATE cursor_Patients

SQL Server 2000 sysindexes Table Columns

, sysSource

Column name	Data type	Description
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status	int	Internal system-status information.
first	binary(6)	Pointer to the first or root page.
indid	smallint	ID of index: 0 = Heap = Table Data (not Index) 1 = Clustered Index 2 ... 254 = Nonclustered Index 255 = Entry for tables that have text or image data
root	binary(6)	For indid >= 1 and < indid =" 0" indid =" 255,">
minlen	smallint	Minimum size of a row.
keycnt	smallint	Number of keys.
groupid	smallint	Filegroup ID on which the object was created.
dpages	int	For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved	int	For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used	int	For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255, used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt	bigint	Data-level rowcount based on indid = 0 and indid = 1. For indid = 255, rowcnt is set to 0.
rowmodctr	int	Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
xmaxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
OrigFillFactor	tinyint	Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
reserved1	tinyint	Reserved.
reserved2	int	Reserved.
FirstIAM	binary(6)	Reserved.
impid	smallint	Reserved. Index implementation flag.
lockflags	smallint	Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr	int	Reserved.
keys	varbinary(816)	List of the column IDs of the columns that make up the index key.
name	sysname	Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob	image	Statistics BLOB.
maxlen	int	Reserved.
rows	int	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.

Find Primary Key in SQL 2000

In SQL Server 2000 it is difficult to see as primary key and a foreign key.

Try running this script, which will give you all primary key on all tables in the database including column information.

SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME

Insert Identity ON for SQL Server

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF