aiDeeva.com

Tag: SQL Scripts

  • OpenDataSouce function – to query OLEDB Data Source

    OPENDATASOURCE
    OpenDataSouce function helps you to get ad hoc connection information as part of a four-part object name as an one time alternative of linked server. You don’t have to specify or create the linked server to query other data sources (i.e. MS Excel, MS Access, MSSQL Older version to newer version etc.) if you are querying it infrequently.

    You can use OPENDATASOURCE for the OLEDB data sources those are accessed infrequently, for several time use linked server as it provides more functionality.

    You can get more information about the arguments of OpenDataSource function on MSDN site.

    To use the OPENDATASOURCE you have to enable the ad hoc distributed queries. You required to have execute permission to use OPENDATASOURCE fucntion.

    Execute below query to enable it

    EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

    Make sure Provider AllowInProcess and DynamiceParameters value is checked. For example lets enable it for SQLNCLI10 provider.

    USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'DynamicParameters', 1
GO

    OpenDataSource Examples

    -- SQL Server 2000/2005/2008
-- You can use SQLNCLI10 provider for SQL Server 2008 as well
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  
   

-- SQL Server 2012
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI11', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI11',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  

--Access DB
SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 
                              'Data Source=D:\MyDB\MyAccessDB.accdb')...TableName

    Common Errors
    Error 1#
    Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider “SQLNCLI11” has not been registered.
    Solution: You will get above error if you have mentioned SQLNCLI11 while running OPENDATASOURCE query on SQL Server 2008 or lower version, it will work fine on SQL Server 2011. You can check list of registered provider by browsing Server Objects -> Linked Servers -> Provider in SSMS
    Error 2#
    Msg 15281, Level 16, State 1, Line 2
    SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

    Solution: Enable the ad hoc distributed queries by executing above SP_CONFIGURE query.

  • Script to find out the database file size, log file size on SQL Server 2000/2005/2008

    Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.

    You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.

    --SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

  • Script to Create Foreign Key on the Compound Primary Key

    Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.

    Check the below example.

    create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

  • Primary Key, Unique Key Constraints – Clustered Index and Non Clustered Index

    You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.

    Primary key will not allow NULL values.

    -- Adding the NON NULL constraint
ALTER TABLE [TableName]	 
ALTER COLUMN PK_ColumnName int NOT NULL

--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

    If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.

    Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

    --script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

  • SQL Server Agent Job Owner

    It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
    You can find out the jobs owner by executing the below query. 

    SELECT 
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

    You can change the job owner by executing the below query.

    EXEC MSDB.dbo.sp_update_job 
@job_name = 'Job Name', 
@owner_login_name = 'sa'