How to pass variable to sql script from batch file?

How to pass variable to sql script from batch file?

1.       Create a Table called Employees

id int

fname varchar(50)

lname varchar(50)

dept varchar(10)

2.       Insert few records into it as

Id            fname                   lname                   dept

01           Mohd                    Irfan                      IT

02           Gaurav                  Thakur                  IT

03           Animesh              Mishra                  Security
04           Ashok                   Roy                        Admin

3.       Create the sql script as getEmployees.sql

use $(dbname)

select * from $(tablename)

4.       Create the runsqlscript.bat file for running the sql script as below.

sqlcmd -S %1 -i getEmployees.sql -v dbname = %2 tablename = %3 -o %4

5.       Run this command from cmd.

>runsqlscript.bat SQLServerName DBName Employees D:\EmployeesList.txt

Ref - http://blogs.msdn.com/b/ssehgal/archive/2009/04/03/passing-parameters-to-sql-script-using-batch-files.aspx

Can SQL Mirroring be implemented on .NET 1.1

The straight answer is NO, let me find some tweeks and update this topic.

And yes its true, for mirroring , failover implementation it need to provide the failover partner into the connection string which is not supported for .NET Framework 1.1 and 1.0.


http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx


Key: Failover Partner
Default Value : N/A
Description: The name of the failover partner server where database mirroring is configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0 or 1.1.

Storing and Retirving Data in XML format from sql server

Retrieving and storing data in XML format in Sql Server 2000.

Suppose you have 20 X 20 controls on your page. And want to send all the data to sql server then create xml for it.

Example :

Let there are A,B,C,D,E,F ……….. controls .

Then create xml for it as

Through String builder as

StringBuilder xmlData = new StringBuilder();

xmlData.Append("<Column>");

xmlData.Append("<ColumnVal ");

xmlData.Append(" A=");

xmlData.Append("'0'");

xmlData.Append(" B=");

xmlData.Append("’1’");

xmlData.Append(" C=");

xmlData.Append(" 2=");

xmlData.Append(" D=");

xmlData.Append(" 3=");

xmlData.Append(" E=");

……………………………………………………………………………………

……………………………………………………………………………………..

……………………………………………………………………………………

xmlData.Append("</Column>");

xmlData.Append("</ColumnVal ");

And at last it will created as :

<Column ColumnVal=1 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=2 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=3 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=4 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

………………………….

………………………….

………………………….

<Column ColumnVal=20 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

send this string into DataBase in as xml datatype.

And write query for inserting into datatable.

insert into YourTableName(A,B,C,D,E,F,G,H,…………………………… ColumnVal)

SELECT A,B,C,D,E,F,G,H, ColumnVal FROM OPENXML (@idoc, '/XML/ Column',1)

Retriving data from database.

It will return data in xml format.

CONVERT(xml,(select * from YourTableName where ‘yourcondition’ for xml raw,Elements,ROOT(' Column '))) as ColumnValue

Clustered Index vs Non Clustered Index

Clustered Index | Non Clustered Index

Indexing is used to get fast retrival of data. The indexing which is done on physical storage is known as Clustered Indexing. And an indexing which is performed logically is know as Non Clustered Indexing.

Clustered Indexing can be only one per table as we phyisically can arrange in one way only, while a table can have 249 Non Clustered Index.

Indexing should be used on table which not very frequently having update operations on it. There are many ways which can be used to get more performance while retrieving data.

Covering Index is --> when you create an index on fields which you are also using in select query, is know as covering index.

For ex suppose
Employee Table
---------------
EmpID int
EmpName
EmpDept
EmpDesing

----------------
If you have a query which only select EmpID and EmpName, then we can create a non clustered index with these two fields only, and it gains our performance while retrieving data.

Get all the triggers for a Database

To get all the triggers list and their scripts run the script below. View the result in file view mode.

SELECT [text]
FROM sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'TR'

Getting all stored procedures scripts from a Database

By using Cursor
declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

From sysobjects
This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

Passing Data from one sp to other sp using Temp Table.

Passing Data from one sp to other sp using Temp Table.

 

Hi,

Here I tried a trick, there may be others trick also to do the same.

 

 

CREATE PROCEDURE Proc_Record_Insertion

AS

-- This #Temp1 table is defined in the other sp which I am using here.

INSERT INTO #Temp1(id,info) VALUES(1,'Mohammad')

INSERT INTO #Temp1(id,info) VALUES(2,'Irfan');

INSERT INTO #Temp1(id,info) VALUES(3,'matespoint@gmail.com');

INSERT INTO #Temp1(id,info) VALUES(4,'matespoint.blogspot.com');

GO

-- This will create a sp Proc_TempTableCreate which create a Temp table and insert four recors into it.

 

CREATE PROCEDURE Proc_UseTempData

AS

BEGIN

CREATE TABLE #Temp1

            (id int,

             info varchar(40) )

 

-- Executing the sp

EXEC Proc_Record_Insertion

 

SELECT * FROM #Temp1

END

IF EXIST Vs COUNT(*) - SQL Server

IF EXIST Vs COUNT(*)

 

Many times we see that people use count(*) to get the condition that if we have any record like

IF (SELECT COUNT(*) FROM Employee where Salaried =0 ) >0

BEGIN

PRINT 'Yes Got It'

-- Do your calculation here.

END

 

We are doing some calculation if we get Salaried=0 for at least once. And I know many times we use the same trick.

"The cost for getting the count(*) is that it counts all the data we get by where clause."

Use if exist to reduce this overhead.

IF EXISTS(SELECT * FROM Employee where Salaried =0)

BEGIN

PRINT 'Yes Get it'

-- Do your calculation here.

END

 

In this as soon as we get the first record further execution stops and it comes out of the if condition with true. If this not get any result then will cost same as above query.

 

Uses of GO in SQL

Very interesting question, can anybody what will be the answer if we execute this query by selecting all at once.

 

DECLARE @Test1 int, @Test2 VARCHAR(20)

SET @Test1 = 1;

SET @Test2 = 'Character';

SELECT @Test1,@Test2

GO

SELECT @Test1,@Test2

 

-       Either it through an error

-       Or Give 1 Character twice.

 

Yes – It will through error as @Test1 is undefined, because after GO it ends the scope of declaration of the variable.

Here we seen the scope of Variable declaring into SQL.

 

 

Getting all columns for a Table

Using INFORMATION_SCHEMA View

 

Getting all columns for a Table

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '<TABLE NAME>'

 

Getting all constraints for a Table

SELECT * FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = '<TABLE NAME>'

 

Getting all check constraints for a Table

 

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS where CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = '<TABLE NAME>')

 

 

For more about INFORMATION_SCHEMA please visit (However this link is a kb for a bug still has enough pointers to go ahead)

http://support.microsoft.com/kb/294350

 

Happy learning

Getting all the constraints for a Table

How to get all the constraints for a table?

 

Sysconstraints table –

This is the table which contains the constraints which are defined on various tables.

If you query by – SELECT * FROM sysconstraints

You will get OUTPUT –

constid     id          colid  spare1 status      actions     error

----------- ----------- ------ ------ ----------- ----------- ------

2099048     2133582639  6      0      2069        4096        0

11147085    1269579561  2      0      2067        4096        0

14623095    2130106629  6      0      2069        4096        0

27147142    1301579675  2      0      2067        4096        0

For details go to http://msdn2.microsoft.com/en-us/library/aa260399(sql.80).aspx

 

Now come to the actual point – getting all constraints for a table

 

SELECT OBJECT_NAME(constid) 'Constraint Name',

  constid 'Constraint ID',

  CASE (status & 0xF)

    WHEN 0x1 THEN 'Primary Key'

    WHEN 0x2 THEN 'Unique'

    WHEN 0x3 THEN 'Foreign Key'

    WHEN 0x4 THEN 'Check'

    WHEN 0x5 THEN 'Default'

    ELSE 'Undefined'

  END 'Constraint Type',

  CASE (status & 0x30)

    WHEN 0x10 THEN 'Column Level'

    WHEN 0x20 THEN 'Table Level'

    ELSE 'Unknown'

  END 'Constraint Level'

  FROM sysconstraints

  WHERE id=OBJECT_ID(‘TABLE_NAME’)

 

This will result to all the constraints applied to this table.

How to get the first not null column from a table?

Hey , really a tricky question , select all the values into a single column from a table which is first not null in that table.
For Exapmle
There is table test

id	col1	col2	col3	col4
1	NULL	NULL	W	M
2	Q	NULL	R	NULL
3	NULL	W	P	T
4	NULL	NULL	NULL	S

Now I want to get the result like that in a single column
First Not NULL Values
W
Q
W
S

So what you think how can we get that,
First thing is that you can use cursor and loop each row and get them.
The second one is the use of COALESCE to get that

this query will get the result

SELECT COALESCE(col1,col2,colo3,col4) FROM TEST

If you have any query you can ask me at matespoint@gmail.com

Concatenate strings from a column into a single row?

CREATE DATABASE Test1
Go
Use Test1
set nocount on
CREATE TABLE dbo.Test2
(
Col1 int identity,
Col2 varchar (5) default('aaaaa')
)
declare @loop
int set @loop = 1
while @loop <= 100
begin
insert into Test2 default values
set @loop = @loop + 1
end


Now you had create a Database Test1 and a Table Test2 with 100 records

Now concatenate this to a single string

DECLARE @ConcatString varchar(8000)
SELECT @ConcatString = COALESCE(@ConcatString + ',','' ) + CAST(Col1 as varchar(12))
FROM Test2
SELECT @ConcatString

You can find the same result by using ISNULL

DECLARE @ConcatString varchar(8000)
SELECT @ConcatString = ISNULL(@ConcatString + ',','' ) + CAST(Col1 as varchar(12))FROM Test2
SELECT @ConcatString


Enjoy .. .. better performance using this instead of using CURSOR.