Create Linked Server From Query in Sql Server

EXEC sp_addlinkedserver
@server='MY_TEMP_linkServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='MYDATASOURCE\SQLEXPRESS2005'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_TEMP_linkServer'
, @useself = 'false'
, @locallogin = 'sa'
, @rmtuser = 'sa'
, @rmtpassword = 'admin123'


Create Linked Server From Query:-

For Link the Two or More Server You need to follow this two steps

A) First Need to create/assign/add server name(Which you can use in program) in linked server list
B) Then set your user id and password to same linked list.

Note :- i) Server Name could be anything which you want to associate in Query.
ii) Need to assign Provider
iii) User names are must be permission to view the table/database.

Syntax :-

EXEC SP_ADDLINKEDSERVER @server=N'SOMESERVER', @srvproduct=N'', @provider=N'SQLOLEDB', @datasrc=N'IP/HOST of server'
GO
EXEC SP_ADDLINKEDSRVLOGIN 'SOMESERVER', 'false', 'remoteuser', 'remoteuser', 'remotepassword'
GO

For Example for Sql Server :-

A)
EXEC sp_addlinkedserver
@server='my_temp_inventory',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='INDIGO49\SQLEXPRESS'

B)
EXEC sp_addlinkedsrvlogin 'my_temp_inventory',
'true', 'my_db_username', 'my_db_username', 'murli'

Another Example :-

EXEC sp_addlinkedserver @server='my_temp_inventory',@srvproduct='',@provider='SQLNCLI',@datasrc='mutli89\SQLEXPRESS2008'
EXEC sp_addlinkedsrvlogin 'my_temp_inventory','true', 'sa', 'sa', 'admin123'

sp_linkedservers --SHOW ALL linked server list

Select * from LINKEDSERVERNAME.DATABASENAME.OWNER.TABLENAME --Execute records

SELECT * FROM OPENQUERY(LINKED_SERVER, ' DROP TABLE DB.dbo.TABLE SELECT NULL') -- drop table

Best Example :-
http://technet.microsoft.com/en-us/library/ms190479.aspx

http://networking.ringofsaturn.com/SQL/linkedservers.php

The following example enables the SQL Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'SQL Mail XPs', 1;
GO
RECONFIGURE;

Create Parameterize Query in Sql

Create your query as usewal and pass parameters in a querys


DECLARE @Query NVARCHAR(max)
DECLARE @Parameters NVARCHAR(max)

SET @Query = N'Select * from dbSeminar.dbo.indigo_tech Where Gender = @Gender And EmpGroup = @EmpGroup And FirstName = @FirstName'

SET @Parameters = N'@Gender varchar(3),@EmpGroup int,@FirstName varchar(25)'

EXECUTE sp_executesql @Query, @Parameters, @Gender = 'M' ,@FirstName = 'Murli', @EmpGroup = 1


OR
Exec Sp_executesql N'Select * from Deepak_PC.mydb.dbo.Branch Where RegionID = @RegionID',N'@RegionID int',@RegionID = 1

Find Rowindex in Row_Command event

you don't need to set anything in e.CommandSource


VB

Dim selectedRow As GridViewRow = DirectCast(DirectCast(e.CommandSource, LinkButton).NamingContainer, GridViewRow)
Dim intRowIndex As Integer = Convert.ToInt32(selectedRow.RowIndex)
GridView.Rows(intRowIndex).BackColor = System.Drawing.Color.Blue

C#

GridViewRow selectedRow = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;
int intRowIndex = Convert.ToInt32(selectedRow.RowIndex);
GridView.Rows[intRowIndex].BackColor = System.Drawing.Color.Blue;

Insert table to another table in sql

Insert tbltech(FirstName,Gender,EmpGroup)
Select FirstName,Gender,EmpGroup from indigo_tech

--===========================
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))
--====================

Select FirstName,Gender,EmpGroup Into Newtbl from indigo_tech

Simple script to backup all SQL Server databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

For More Details : http://www.mssqltips.com/tip.asp?tip=1599

Example uses a filter expression to return an array of DataRow objects.

private void GetRowsByFilter()
{
DataTable table = DataSet1.Tables["Orders"];
// Presuming the DataTable has a column named Date.
string expression;
expression = "Date > #1/1/00#";
DataRow[] foundRows;

// Use the Select method to find all rows matching the filter.
foundRows = table.Select(expression);

// Print column 0 of each returned row.
for(int i = 0; i < foundRows.Length; i ++)
{
Console.WriteLine(foundRows[i][0]);
}
}