Few Useful SQL Commands

1.) Executing a sql file with SQLCMD command and passing a variable value to file variable.

Let's say restoredb.sql is a file contains following sql.

RESTORE

DATABASE MyDB FROMDISK = '$(root)\mydb.bak'WITH REPLACE,MOVE 'mydb_data' to '$(root)\mydb.mdf',GO

Now I can execute the content of restoredb.sql file with any value of root variable.

sqlcmd -E -i restoredb.sql -v root="%CD%"

Site Reference: 

http://www.pluralsight-training.net/community/blogs/craig/archive/2005/12/16/17525.aspx

Loading data into SQL database from file/file data

1.     Load data into SQL database from Excel:

sp_configure 'show advanced options', 1

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

SELECT * INTO Link FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\data1.xls','SELECT * FROM [categoryLink$]')

2.     Load Docx file into SQL db table:

Declare a variable to store the binary data

      DECLARE @Doc AS VARBINARY(MAX)

-- Load the binary data

      SELECT @doc = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
     BULK 'C:\Documents\MyDoc.docx', SINGLE_BLOB) AS x


     -- Update the Fragment table
     UPDATE dbo.Fragments
     SET Document= @doc
     WHEREID = @ID

My daily used SQL Syntaxes

1. To find the SQL

SELECT DISTINCT a.name FROM SYSOBJECTS a inner join SYSCOMMENTS b ON a.id=b.id WHERE b.text like '%Product%'

2. Change the User in SQL 2000

SETUSER 'Domain\User' Example : SETUSER 'MyDomain\Hitesh'

3. Change the User in SQL 2005/2008

EXECUTE AS LOGIN = 'Domain\User' Example : EXECUTE AS LOGIN ='MyDomain\Hitesh'

Most frequently used SQL Syntaxes

Most often I were googling to find the simple enough syntexes while writing the sql query.

So I thought to List down all of them in single page and refer to that page while writing a sql query, Stored procedure or functions.

This may helpful to you.......

I will used the following table through out this article.

Create table Product (ProductId int, Category varchar(20) Default(0), ProductDesc varchar(200))

Here is a smart SQL Syntaxes.

1. If table exist, drop it.

Declare @table as varchar(100) SET @table='Product' if Object_ID(@table) is not null Exec('Drop table ' + @table)

2. Create Index on an existing table.

CREATE UNIQUE CLUSTERED INDEX CUnqIdxProductId ON Product (ProductId)

3. Drop Index.

DROP INDEX Product.CUnqIdxProductId

4. Alter Column

ALTER TABLE Product ALTER COLUMN ProductId longint Not null

5. Create the table with Primary Key (Without constraint Name)

CREATE TABLE [dbo].[Product](       [ProductId] [int] NOT NULL,       [Category] [varchar](20) NOT NULL,       [ProductDesc] [varchar](200) NULL,      PRIMARY KEY (       [ProductId] ASC,       [Category] ASC ))

6. Create the table with Primary Key (With constraint Name)

CREATE TABLE [dbo].[Product](   [ProductId] [int] NOT NULL,   [Category] [varchar](20) NOT NULL,   [ProductDesc] [varchar](200) NULL,  CONSTRAINT [pk_Product] PRIMARY KEY CLUSTERED (   [ProductId] ASC,   [Category] ASC ))

7. Alter statement to add the Primary Key Constraint in an existing table (without Constraint Name).

ALTER TABLE Product ADD PRIMARY KEY (ProductId, Category)

8. Alter statement to add the Primary Key Constraint in an existing table (with Constraint Name).

ALTER TABLE Product ADD CONSTRAINT pk_Product PRIMARY KEY (ProductId, Category)

9. Drop the Primary Key Constraint when Constraint Name is not Known.

DECLARE @PKConstraintName VARCHAR(500) SELECT @PKConstraintName=QUOTENAME(constraint_name) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Product' IF ISNULL(@PKConstraintName,'')<>'' EXEC('ALTER TABLE Product DROP CONSTRAINT ' + @PKConstraintName) GO

10. Drop the Default Constraint when Constraint Name is not Known.

DECLARE @table_name NVARCHAR(256) DECLARE @col_name NVARCHAR(256) DECLARE @Command  NVARCHAR(1000) SET @table_name = N'Products' SET @col_name = N'Category' SELECT @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name  FROM sys.tables t      JOIN    sys.default_constraints d           ON d.parent_object_id = t.object_id     JOIN    sys.columns c          ON c.object_id = t.object_id           AND c.column_id = d.parent_column_id  WHERE t.name = @table_name    AND c.name = @col_name EXECUTE(@Command)

11. Alter statement to add the Foreign Key Constraint in an existing table (without Constraint Name).

ALTER TABLE Product ADD FOREIGN KEY(CategoryID) REFERENCES CATEGORY (CategoryId)

12. Alter statement to add the Foreign Key Constraint in an existing table (with Constraint Name).

  

ALTER TABLE PRODUCT ADD CONSTRAINT FK_CATEGORY_CATEGORYID FOREIGN KEY(CategoryID) REFERENCES CATEGORY (CategoryId)

13.Create the table with Foreign Key (Without constraint Name)

CREATE TABLE [dbo].[Product](    [ProductId] [int] NULL,    [CategoryID] [int] NULL references Category(CategoryId),    [ProductDesc] [varchar](200) NULL ) ON [PRIMARY]

14.Create the table with Foreign Key (With constraint Name) 

CREATE TABLE [dbo].[Product](       [ProductId] [int] NOT NULL,       [Category] [int] NOT NULL,       [ProductDesc] [varchar](200) NULL,  CONSTRAINT [fk_Category] Foreign KEY (       [Category] )REFERENCES CATEGORY (CategoryId))

15.Drop the Foreign Key constraint when constraint name is known

Alter table [Product] Drop constraint FK_CATEGORY_CATEGORYID

16.Drop the Foreign Key constraint when constraint name is known

Alter table [Product] Drop constraint FK_CATEGORY_CATEGORYID

Common error with SSIS/DTSX Package + SSIS Package Execution from T-SQL using xp_cmdshell and dtexec

I had a scenario where I was invoking SSIS/DTSX package from my stored proc using sp_cmdcell.

Here is a command which I was executing.

 

EXEC xp_cmdshell 'dtexec /SQ Package1'

 

First Error:	Could not load package "Package1" because of error 0xC0014062
Description:	The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.). The SQL statement that was issued has failed.
Issue/Resolution:	My SQL server and SQL server Agent services was running with this account (NT AUTHORITY\NETWORK SERVICE'). And this account was not added into the administrator group of my machine. So I added this account into the local administrator group. Another way, we can resolve this issue by running My SQL server and SQL server Agent services with other Admin account which also have access on MSDB, Master DB and other local DBs.

 

When you faced the same issue (Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’) in ASP.NET site,
you can refer the following blog.

http://programmers.wordpress.com/2007/10/16/login-failed-for-user-nt-authoritynetwork-service/

 

Second Error:	Could not load package "Package1" because of error 0xC001000A.
Description:	The specified package could not be loaded from the SQL Server database.
Issue/Resolution:	I had not specified the destination server name. I corrected the command and add the /SER attribute with correct server name.

 

EXEC xp_cmdshell 'dtexec /SQ Package1 /SER EDVXGCPSQLCD02\sql2008'

 

Third Error:	Could not load package "Package1" because of error 0xC0014062
Description:	The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Could not find stored procedure 'msdb.dbo.sp_dts_getpackage'.). The SQL statement that was issued has failed.
Issue/Resolution:	Specify the full path for dtexec exe. Final command I could successfully run was :

 

EXEC xp_cmdshell 'C:\"Program Files (x86)\Microsoft SQL Server"\100\DTS\Binn\DTEXEC.exe /SQ Package1 /SER EDVXGCPSQLCD02\sql2008'
Or
EXEC xp_cmdshell 'C:\"Program Files\Microsoft SQL Server"\100\DTS\Binn\DTEXEC.exe /SQ Package1 /SER EDVXGCPSQLCD02\sql2008'

 

One intresting fact about SQL

Here is an one very intresting thing about SQL.
Even we don't specify the column name in where condition of SQL query, till we can filter the record from the table.
How is that possible ?

Let's go through this scenario.
Here is an Products table having two colum CategoryId and ProductId.

create table Products(CategoryId varchar(20),ProductId varchar(20))
insert into Products values ('C01',1)
insert into Products values ('C02',2)
insert into Products values ('C03',3)
insert into Products values ('C04',4)
insert into Products values ('C05',5)

insert into Products values ('C01',6)
insert into Products values ('C02',7)
insert into Products values ('C03',8)
insert into Products values ('C04',9)
insert into Products values ('C05',2)

insert into Products values ('C01',11)
insert into Products values ('C02',12)
insert into Products values ('C03',13)
insert into Products values ('C04',14)
insert into Products values ('C05',15)

Now I will not specify the CategoryId column in where condition of below query, still I would get the desired result.

declare @CategoryId as varchar(20)
set @CategoryId = 'C04'

select * from Products where @CategoryId=case when ProductId in('15','2','5')
then 'C05'
When ProductId in('14','9','4')
then 'C04'
end


Here is a result of above query.

isn't it intresting ?
I have recently come across such scenario. so I thought to share it with you.

Send me your feedback on this.

Thank you