Pages

Saturday, June 1, 2013

How to create a stored procedure for DTSRUN?

Execute the following Microsoft SQL Server T-SQL training script in SSMS Query Editor to create a sproc for DTSRUN execution :
CREATE PROC ImportFinanceData
AS
BEGIN
DECLARE @SQLCommand varchar(1000)
-- Replace it with your code
SET @SQLCommand = 'DTSRUN /S 140.242.136.106 /E /U "user" /P "password" /N
"DailyFinanceImport" '
PRINT @SQLCommand -- test & debug
EXEC master..xp_cmdshell @SQLCommand, NO_OUTPUT
END
GO
-- Execute stored procedure
EXEC ImportFinanceData
GO
Related articles:
Posted by SIVANANDA REDDY G at 0 comments

How to find the login name of the dbo?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to find the login name of the dbo:
USE master;

SELECT SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = 'Northwind'
GO
/* Result

sa
*/
Related articles:
Posted by SIVANANDA REDDY G at 0 comments

How to "fingerprint" a database quickly?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to create and execute a stored procedure for listing all tables with basic row count and size information and summary by schema.
-- Simplest all tables list method
SELECT * FROM AdventureWorks2008.sys.tables
------------

-- SQL Server list all tables in a database - SQL Server database row count
-- SQL Server database table list - SQL Server table size
USE AdventureWorks2008;
GO

-- SQL Server stored procedure create - dynamic SQL stored procedure
CREATE PROC DatabaseFingerprint @DBName SYSNAME
AS
BEGIN
DECLARE @DynamicSQL VARCHAR(1024), @bytes INT
SET NOCOUNT ON
SELECT @bytes = [low] / 1024
FROM MASTER..spt_values
WHERE NUMBER = 1
AND TYPE = 'E'

CREATE TABLE #Fingerprint (
id INT,
SchemaName SYSNAME,
TableName SYSNAME,
NoOfRows MONEY,
DataInMB MONEY,
IndexInMB MONEY)



SET @DynamicSQL = 'INSERT INTO #Fingerprint (id, SchemaName,TableName)
SELECT object_id, schema_name(schema_id), name FROM ' + @DBName + '.sys.objects
WHERE type = ''U'''
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET NoOfRows = A.rows
FROM ' + @DBName + '..sysindexes A WHERE #Fingerprint.id = A.id
AND A.indid IN (0, 1)'
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET DataInMB = C.DataInMB
FROM (SELECT A.id, A.SumDpages + ISNULL(B.SumUsed, 0) AS DataInMB
FROM (SELECT id, SUM(dpages) AS SumDpages FROM ' + @DBName + '..sysindexes
WHERE indid IN (0, 1) GROUP BY id) AS A
LEFT JOIN (SELECT id, ISNULL(SUM(used), 0) AS SumUsed
FROM ' + @DBName + '..sysindexes WHERE indid = 255
GROUP BY id) AS B ON A.id = B.id) AS C JOIN #Fingerprint
ON C.id = #Fingerprint.id'
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET IndexInMB =
A.SumUsed - #Fingerprint.DataInMB FROM (SELECT id, SUM(used)
AS SumUsed FROM ' + @DBName + '..sysindexes WHERE indid IN (0, 1, 255)
GROUP BY id) AS A JOIN #Fingerprint ON A.id = #Fingerprint.id'
EXEC( @DynamicSQL)
UPDATE #Fingerprint
SET DataInMB = DataInMB * @bytes,
IndexInMB = IndexInMB * @bytes

-- Summary of table sizes by schema
SELECT      SchemaName, 
            Rows = SUM(NoOfRows),
            DataInMB = SUM(DataInMB / 1024),
            IndexInMB = SUM(IndexInMB / 1024)
FROM #Fingerprint
GROUP BY SchemaName
ORDER BY SchemaName
SELECT SchemaName, TableName,
Rows = left(convert(VARCHAR,NoOfRows,1),len(convert(VARCHAR,NoOfRows,1)) - 3),
DataInMB = convert(VARCHAR,DataInMB / 1024,1),
IndexInMB = convert(VARCHAR,IndexInMB / 1024,1)
FROM #Fingerprint
ORDER BY NoOfRows DESC
END
GO

EXEC DatabaseFingerprint AdventureWorks2008
GO
/*
SchemaName        Rows        DataInMB    IndexInMB
dbo               1105637.00  47.398      41.3903
HumanResources    934.00      0.1873      0.2888
Person            141250.00   39.3355     8.3746
Production        349814.00   21.6398     9.2802
Purchasing        13426.00    0.8905      0.4216
Sales             253735.00   20.1714     15.726   */
/*
SchemaName  TableName                     Rows        DataInMB    IndexInMB
Sales       SalesOrderDetail              121,317     11.25       6.05
Production  TransactionHistory            113,443     7.22        3.61
Production  TransactionHistoryArchive     89,253      5.69        2.90
Production  WorkOrder                     72,591      4.78        2.09
Production  WorkOrderRouting              67,131      6.34        1.16
dbo         numbers                       65,536      0.83        0.02
..... */
------------
Related articles:
How do I get a list of SQL Server tables and their row counts?
http://www.sqlusa.com/bestpractices2005/alltablesrowcount/
http://www.sqlusa.com/bestpractices/dynamicsql/
Posted by SIVANANDA REDDY G at 0 comments

How to grant execute permission on all sprocs?

Execute the following script generator Microsoft SQL Server T-SQL query in SSMS Query Editor after setting the results to TEXT mode. Paste the resulting script back to the query window for deployment execution:

USE pubs;

DECLARE @DBrole sysname
SET @DBrole = 'RoleDELTA'

-- SQL execute permission check
PRINT 'Not permissioned'
SELECT name FROM sysobjects
WHERE xtype='P' AND base_schema_ver < 16
AND id NOT IN
            ( SELECT o.id FROM syspermissions p
                  LEFT JOIN sysusers u ON p.grantee = u.uid
                  LEFT JOIN sysobjects o ON p.id = o.id
                  WHERE lower(u.name) = lower(@DBrole)
            )

-- SQL grant execute - generate statements
PRINT 'GRANT EXECUTE generation '
SELECT 'GRANT EXECUTE ON [' + name + '] TO [' + @DBrole + ']'
FROM sysobjects
WHERE xtype='P' AND base_schema_ver <> 16
      AND id NOT IN
            ( SELECT o.id FROM syspermissions p
                  LEFT JOIN sysusers u ON p.grantee = u.uid
                  LEFT JOIN sysobjects o ON p.id = o.id
                  WHERE lower(u.name) = lower(@DBrole)
            )
GO

/* Partial results

GRANT EXECUTE ON [byroyalty] TO [RoleDELTA]
GRANT EXECUTE ON [CustomerListByState] TO [RoleDELTA]
*/


Related article:

http://www.sqlusa.com/bestpractices/grantexectorole/
Posted by SIVANANDA REDDY G at 0 comments

How to change ownership for a set of stored procedures?

The following Microsoft SQL Server T-SQL sample script will change ownership from CurrentOwner to NewOwner for all stored procedures in Northwind database.
In SQL Server 2005 and SQL Server 2008 use ALTER SCHEMA or ALTER AUTHORIZATION instead.
-- SQL Server 2005 and on - Change stored procedure owner - QUICK SYNTAX
USE AdventureWorks;
ALTER AUTHORIZATION ON OBJECT::dbo.uspGetWhereUsedProductID TO MaryS
EXEC sp_help 'dbo.uspGetWhereUsedProductID'
/* Name                       Owner Type              Created_datetime
uspGetWhereUsedProductID      MaryS stored procedure  2010-05-09 06:14:15.700  */
------------
-- SQL Server change stored procedure owner
USE Northwind
GO
-- T-SQL cursor
DECLARE  @ProcName SYSNAME
DECLARE  @DynamicSQL VARCHAR(2000)
DECLARE curSprocNames CURSOR FAST_FORWARD FOR
SELECT name
FROM   dbo.sysobjects
WHERE  xtype = 'P'

OPEN curSprocNames

FETCH NEXT FROM curSprocNames
INTO @ProcName

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRINT 'Changing ownership for ' + @ProcName
    
    SET @DynamicSQL = 'EXEC sp_changeobjectowner ''CurrentOwner.' + @ProcName + ''', ''NewOwner'''
    
    PRINT @DynamicSQL
-- T-SQL dynamic sql    
    -- EXECUTE (@DynamicSQL) -- uncomment for production
    FETCH NEXT FROM curSprocNames
    INTO @ProcName
  END

CLOSE curSprocNames

DEALLOCATE curSprocNames

GO
/* Partial messages

Changing ownership for Ten Most Expensive Products
EXEC sp_changeobjectowner 'CurrentOwner.Ten Most Expensive Products', 'NewOwner'
Changing ownership for Employee Sales by Country
EXEC sp_changeobjectowner 'CurrentOwner.Employee Sales by Country', 'NewOwner'
*/
Related links:
INF: SQL Server Procedure to Change Object Owner
Change the Owner for Stored Procedures
Posted by SIVANANDA REDDY G at 0 comments

How to create login for SQL Server (startup account)?

Follow the steps below to create service account(s) for SQL Server 2000:
1. Create Logins for the SQL Server and SQL Agent services. Could either be be one account for both, or separate accounts for each. Make you checkmark password never expire and logon as service.
2. Add this account(s) as SQL Server Login and grant it SysAdmin rights.
3. Configure the services to use these accounts. Use Enterprise Manager to do this; it will automatically set all the necessary server and registry settings.
4. Stop and restart the services, test to make sure everything is working.
Articles:
Posted by SIVANANDA REDDY G at 0 comments

How to find all orders with 10% discount?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor or Query Analyzer to get a report on all orders with 10% (0.1) discount. The queries demonstrate INNER JOIN GROUP BY with derived table and CTE forms.
-- SQL INNER JOIN with SELECT GROUP BY query - derived table
-- SQL sequence numbering groups
USE Northwind

SELECT   o.OrderID,
         SeqNo,
         ProductName,
         o.UnitPrice,
         o.Quantity,
         Amount = o.UnitPrice * o.Quantity,
         Discount = convert(DECIMAL(3,2),Discount)
FROM     Products p
         INNER JOIN [Order Details] o
           ON p.ProductID = o.ProductID
         INNER JOIN (SELECT   count(* ) AS SeqNo,
                        a.OrderID,
                        a.ProductID
               FROM     [Order Details] a
                        JOIN [Order Details] b
                          ON a.ProductID >= b.ProductID
                             AND a.OrderID = b.OrderID
               GROUP BY a.OrderID,
                        a.ProductID) seq
           ON o.OrderID = seq.OrderID
              AND o.ProductID = seq.ProductID
WHERE    o.Discount = 0.1
ORDER BY o.OrderID
GO
/* Partial results

OrderID     SeqNo ProductName                         UnitPrice
10248       1     Queso Cabrales                      14.00
10248       2     Singaporean Hokkien Fried Mee       9.80
10248       3     Mozzarella di Giovanni              34.80
10249       1     Tofu                                18.60
10249       2     Manjimup Dried Apples               42.40
10250       1     Jack's New England Clam Chowder     7.70
10250       2     Manjimup Dried Apples               42.40

*/
-- SQL INNER JOIN with SELECT GROUP BY query - CTE

;WITH CTE AS
(SELECT   count(* ) AS SeqNo,
                        a.OrderID,
                        a.ProductID
               FROM     [Order Details] a
                        JOIN [Order Details] b
                          ON a.ProductID >= b.ProductID
                             AND a.OrderID = b.OrderID
               GROUP BY a.OrderID,
                        a.ProductID) 
SELECT   o.OrderID,
         SeqNo,
         ProductName,
         o.UnitPrice,
         o.Quantity,
         Amount = o.UnitPrice * o.Quantity,
         Discount = convert(DECIMAL(3,2),Discount)
FROM     Products p
         INNER JOIN [Order Details] o
           ON p.ProductID = o.ProductID
         INNER JOIN CTE
           ON o.OrderID = CTE.OrderID
              AND o.ProductID = CTE.ProductID
WHERE    o.Discount = 0.1
ORDER BY o.OrderID
GO
Posted by SIVANANDA REDDY G at 0 comments
Subscribe to: Posts (Atom)