SELECT Linked Server

If you already have a Linked Server setup, here are a way to access the Linked Server tables.

SELECT * FROM LinkedServer..Schema.Table

In the example below

Linked Server name: CACHE

Schema: SQLUser

Table: P

SELECT * FROM CACHE..SQLUser.P

Advertisements

OpenRowSet

Using OpenRowSet to read all data from an Excel worksheet into a global temp table.

INSERT INTO ##ABC
SELECT *
FROM
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
Excel 8.0;Database=D:\EDI\SAMPLE_051311_1.xls‘, [Sheet1$])

CDO Email

For those who are interested in using SQL cdosysmail to email embedded HTML follow this code as a model. Keep in mind that you are required to have the Stored Procedure sp_send_cdosysmail.

SET NOCOUNT ON
— SETUP VARIABLES
 DECLARE @body1 VARCHAR(MAX)
 DECLARE @subjecttitle VARCHAR(100)
 DECLARE @email VARCHAR(200)
DECLARE @today DATETIME

SET @today = (SELECT CONVERT(VARCHAR,GETDATE()-1,101))
SET @subjecttitle = ‘Daily Sales Summary Report for ‘ + CONVERT(VARCHAR,@today)

— PROCESS EMAIL DATA
 SET @body1=’


SELECTTOP 20 @body1 = @body1 +’

FROM dbo.TransactionHeader
WHERE DIMENSION = 1001 AND RECEIPTDATE = @today
GROUP BY DIMENSION— SEND THE EMAIL
exec master.dbo.sp_send_cdosysmail
@From=’cbuisr@gmail.com’,
@To =’cbuisr@gmail.com’,
@Subject = @subjecttitle,
@Body =  @body1

Act Plan Var $ Var % LY Comp

‘ + CONVERT(VARCHAR,DIMENSION) + ‘ ‘+ CONVERT(VARCHAR,SUM(CONVERT(DECIMAL(12,2),TOTALBEFORETAX))) + ‘ ‘ + ‘VAR $’ + ‘ ‘+ ‘Var %’ + ‘ ‘ + ‘LY’ + ‘ ‘ + ‘Comp’ + ‘

Maximum Connection Limit

The Maximum Connection Limit Has Been Reached

This article refers to any weird cases and in this case it’s making a Linked Server Connection to DB2.

To make nice with SQL run these steps.

  1. sp_configure 'user connections', 0 GO
  2. reconfigure with override
  3. Go to services and restart SQL Server

Now when you need to make or test the connection, it should work.

Find Columns in Table

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EncTotChgAmt%’
ORDER BY schema_name, table_name;

Find a String in all Tables

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET NOCOUNT ON;

DECLARE    @SearchStr NVARCHAR(100)

DROP TABLE #Results

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110)

SET  @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + ‘Muneeb’ + ‘%’,””)

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ”

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

FROM     INFORMATION_SCHEMA.TABLES

WHERE         TABLE_TYPE = ‘BASE TABLE’

AND    QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

AND    OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

), ‘IsMSShipped’

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM     INFORMATION_SCHEMA.COLUMNS

WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

AND    TABLE_NAME    = PARSENAME(@TableName, 1)

AND    DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘NVARCHAR’, ‘int’, ‘decimal’)

AND    QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

                EXEC

(

SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

)

END

        END   

    END

     SELECT ColumnName, ColumnValue FROM #Results

Get Table Unused Space

To find out how much space is being used in a table you’ll need to use the system’s sp_spaceused stored procedure.

EXEC sp_spaceused N’dbo.orders’

The following are the options:

name: Table name for which space usage information was requested

rows: Number of rows existing in the table

reserved: Total amount of reserved space for table data and indexes

data: Amount of space used by table data

index_size: Amount of space used by table indexes

unused: Total amount of space reserved for table but no yet used