SQL

CDO Email

Posted on Updated on

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’ + ‘
Advertisements

Maximum Connection Limit

Posted on Updated on

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

Posted on Updated on

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

Posted on Updated on

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

Posted on Updated on

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

Find a String in a Stored Procedure

Posted on Updated on

If you every decided to do something ridiculous and whatever you did requires that  all SP needs to be updated, here is how you can thumb through the SP in search of the string.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%AS400%’
AND ROUTINE_TYPE=‘PROCEDURE’

Creating a Linked Server

Posted on Updated on

Sometimes life is a lot easier if you don’t have to work with flat files. Here are some methods to link to other servers (i.e. DB2) or to SQL.

Linking to Microsoft Access 2007

— THE INITIAL SETUP

EXEC sp_addlinkedserver
@server = N’MSACCESS’,
@provider = N’Microsoft.ACE.OLEDB.12.0′,
@srvproduct = N’Access2007′,
@datasrc = N’C:\Users\Chris\Documents\MyDatabase.accdb’
GO

— SET UP LOGIN MAPPING
EXEC sp_addlinkedsrvlogin
@rmtsrvname = @@servername,
@useself = N’TRUE’,
@locallogin = NULL,
@rmtuser = N’MSACCESS’,
@rmtpassword =
GO

— TO SEE ALL OF THE TABLES
EXEC sp_tables_ex N’MSACCESS’
GO

— SELECTING THE TABLES

SELECT ID, FirstName, LastName
FROM [MSACCESS]…[Customer]

IF YOU GET THIS ERROR MESSAGE

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider “SQL Server” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot fetch a row from OLE DB provider “SQL Server” for linked server “(null)”.

1. Go to SQL Server Management Studio

2. Server Objects

3. Linked Servers

Providers

4. Left click on Microsoft.ACE.OLEDB.12.0

5. Click on Properties

6. Check Allow inprocess

7. Click Ok

Now re-run the query. It should work.

This is how the properties screen looks like.

SQL Linked Server
SQL Linked Server

If you get this message, The Maximum Connection Limit, click on the link to see correction.

Here’s a great article I found from StarQuest regarding DB2 with SQL.

StarQuest Technical Documents

How to Use a Microsoft SQL Server Linked Server to Access DB2 with StarSQL

Last Update: 12 May 2009
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ004

Abstract

SQL Server linked servers can be used to access DB2 through a StarSQL ODBC data source. This document demonstrates how to add and use a linked server. These instructions apply to SQL Server 7, SQL Server 2000, SQL Server 2005, and SQL Server 2008.

Solution

Perform the following steps to configure, test, and use a linked server.

StarSQL (64-bit) users should review the StarQuest Technical Document Considerations for using StarSQL (64-bit) with SQL Server 2005/2008.

Configure a Linked Server

  1. Launch the SQL Server 7/2000 Enterprise Manager or SQL Server 2005/2008 Management Studio.
  2. Under the Security folder (SQL Server 7/2000) or the Server Objects folder (SQL Server 2005/2008), right-click on Linked Servers and select New Linked Server.
  3. Enter in a name for the Linked Server and choose “Other data source”. Do not use any spaces or special characters in the Linked Server name.
  4. Select “Microsoft OLE DB Provider for ODBC Drivers” as the Provider Name. On SQL Server 2005/2008, enter MSDASQL for the Product Name.
  5. In the “Data source” field, enter in the name of the ODBC system data source you wish to use. Note that you must enter in the Data Source name exactly as it is shown in the ODBC Data Source Administrator.
  6. For SQL Server 7/2000, click the “Security” tab and select “Be made with this security context”. For SQL Server 2005/2008, select the Security page and select “Be made using this security context”. Enter a valid remote user/login (user ID) and password for the remote host.
  7. Leave the remaining fields blank and click OK to save the linked server configuration.

Test the Linked Server

If an error occurs while testing the linked server connection, review the error and make changes to the linked server configuration as necessary.

SQL Server 7/2000:

In the Enterprise Manager, expand the new linked server and click on Tables to display a table list.

SQL Server 2005:

In the Management Studio, click on the New Query button and execute a query using the samples below as a guideline.

SQL Server 2008:

In the Management Studio, expand the linked server node and then expand the Catalogs node. Click on the node for the linked server and expand the Tables to view a table list.

Sample Linked Server SQL Syntax

The following examples demonstrate how to use a linked server to execute SELECT, INSERT, UPDATE, and DELETE statements.  Execute these statements in either the SQL Server 7/2000 Query Analyzer or the SQL Server 2005/2008 New Query field. These examples assume the following:

  • “STARSQLDSN” is a linked server that accesses DB2 through a StarSQL data source.
  • TITLES is a valid table located within a library (or with a schema name) called BOOKS, TITLE is a valid column in the TITLES table.

To perform a SELECT on the table:

SELECT * FROM STARSQLDSN..BOOKS.TITLES

OR

SELECT * FROM OPENQUERY (STARSQLDSN, ‘SELECT * FROM BOOKS.TITLES’)

To INSERT a row into the table:

INSERT INTO STARSQLDSN..BOOKS.TITLES (TITLE) VALUES (‘The Grapes of Wrath’)

OR

INSERT INTO OPENQUERY (STARSQLDSN, ‘SELECT * FROM BOOKS.TITLES’) (TITLE) VALUES (‘The Grapes of Wrath’)

To UPDATE a row in the table:

UPDATE STARSQLDSN..BOOKS.TITLES SET TITLE = ‘Of Mice and Men’ WHERE TITLE = ‘The Grapes of Wrath’

OR

UPDATE OPENQUERY (STARSQLDSN, ‘SELECT * FROM BOOKS.TITLES’) SET TITLE = ‘Of Mice and Men’ WHERE TITLE = ‘The Grapes of Wrath’

To DELETE a row in the table:

DELETE STARSQLDSN..BOOKS.TITLES WHERE TITLE = ‘The Grapes of Wrath’

OR

DELETE OPENQUERY (STARSQLDSN, ‘SELECT * FROM BOOKS.TITLES’) WHERE TITLE = ‘The Grapes of Wrath’

NOTES:

  • To perform DELETE or UPDATE operations, the target table must have a unique index. For more information, please refer to the section titled UPDATE and DELETE Requirements for OLE DB Providers in the SQL Server Books Online.
  • If the table being accessed has an index defined on more than 16 columns, you may receive this error message:OLE DB provider “MSDASQL” for linked server “” returned an invalid index definition for table “””.””.”””.

As a workaround, either use the OPENQUERY syntax to query this table or remove the offending index. Refer to the “Maximum Capacity Specifications for SQL Server” section of the SQL Server Books Online for more information.