Using Distributed queries, You can select,insert,update delete data from other server(oracle, Mysql, sqlserver)

In previous post, we saw how to create linked server and how to select data with these linked server.To day I will try to show some more interesting things we can do this distributed queries.
When I select data with distributed queries, I thought why don't we insert,delete, update data in linked server. SQL server providing Pass-through queries to achieve this functionality.

---TESTING WITH MY ORACLE INSTANCE

---SO I CREATED ONE TESTING TABLE IN MY ORACLE INSTANCE NAME CALLED "TEST".

--FIRST CREATE LINKED SERVER WITH ALL THE CREDENTIALS

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'ORACLE_REMOTE', @SRVPRODUCT=N'ORACLE', @PROVIDER=N'ORAOLEDB.ORACLE', @DATASRC= 'INSTANCE NAME'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'ORACLE_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XX'

---EXECUTING SELECT QUERY

SELECT * FROM OPENQUERY(ORACLE_REMOTE,'SELECT ID,NAME FROM SHEMA_NAME.TEST')

-----EXECUTING INSERT PASS-THROUGH QUERY

INSERT OPENQUERY(ORACLE_REMOTE,'SELECT ID,NAME FROM SHEMA_NAME.TEST')

VALUES(1,'BHASKER')

-----EXECUTING UPDATE PASS-THROUGH QUERY

UPDATE OPENQUERY(ORACLE_REMOTE,'SELECT NAME FROM SHEMA_NAME.TEST WHERE ID=1')

SET NAME='BHASKER_NEW'

-----EXECUTING DELTE PASS-THROUGH QUERY

DELETE OPENQUERY(ORACLE_REMOTE,'SELECT NAME FROM SHEMA_NAME.TEST WHERE ID=1')

---TESTING WITH MY SQL SERVER INSTANCE

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'SQL_REMOTE', @SRVPRODUCT=N'SQL', @PROVIDER=N'SQLOLEDB', @DATASRC=N'SQLINSTANCE'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'SQL_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XXX'

---EXECUTING SELECT QUERY

SELECT * FROM OPENQUERY(SQL_REMOTE,'SELECT ID,NAME FROM DB_NAME.DBO.TEST')

-----EXECUTING INSERT PASS-THROUGH QUERY

INSERT OPENQUERY(SQL_REMOTE,'SELECT ID,NAME FROM DB_NAME.DBO.TEST')

VALUES(1,'BHASKER')

-----EXECUTING UPDATE PASS-THROUGH QUERY

UPDATE OPENQUERY(SQL_REMOTE,'SELECT NAME FROM DB_NAME.DBO.TEST WHERE ID=1')

SET NAME='BHASKER_NEW'

-----EXECUTING DELTE PASS-THROUGH QUERY

DELETE OPENQUERY(SQL_REMOTE,'SELECT NAME FROM TGI_DBSNP.DBO.TEST WHERE ID=1')

---SET NAME='BHASKER_NEW'

Distributed Queries (access and query data from multiple sources Ex;Sql server, Oracle,Excle, textfiles)

The heading of the topic looks small, but its a huge topic.I cannot cover whole topic in one blog.
But I will try to make this topic is very simple.We will start from basic, when you want to retrieve data
from any table you simply execute, "select * from database_name.schema_name.table name".When you execute this query it automatically
pull data from server, to where you connected from SSMS(sql server management studio).
Same way, If you want to query data from other data source, ( it may be sql sqlserver, oracle, excel, mysql, text file) from your local sql engine, we call it as distributed queries.
Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers.
Microsoft SQL Server supports distributed queries by using OLE DB.

OLE DB providers expose data in tabular objects called rowsets. SQL Server allows rowsets from OLE DB providers to be referenced in
Transact-SQL statements as if they were SQL Server tables.

If you see above statement, its saying you can query on any source( even textfile or excel file) using T-sql satements.
The whole intenstion of this post is also to show, how can we achieve that.

With these ditributed queries, you can even insert,update,delete data in othere sources using T-sql statements.

Sql server supports referencing heterogeneous OLE DB data sources in Transact-SQL statements by using: the linked server name or the ad hoc computer name.

If you are using frequent distributed qieries then,use linked server, otherwise use Ad-hoc queries.

By default, SQL Server does not allow ad hoc distributed queries using OPENQUERY, OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access.
When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENQUERY,OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.
Enable AD Hoc Distributed Queries on Instance:::

SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1;

RECONFIGURE;

SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1;

RECONFIGURE;

GO

WAYS TO ACCESS DATA THROUGH DISTRIBUTED QUERIES::

1) USING LINKED SERVER:::

TO USE LINKEDSERVER IN A QUERY, WE NEED TO CONFIGURE LINKED SERVER FIRST.

NOTE::PLEASE CHECK PROVIDERS AND INSTALL REQUIRED PROVIDES(IF NEEDED), BEFORE YOU CREATE LINKED SERVER

EX:(ORACLE VERSION8 OR LATER)

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'ORACLE_REMOTE', @SRVPRODUCT=N'ORACLE', @PROVIDER=N'ORAOLEDB.ORACLE', @DATASRC=N'INSTANCE_NAME'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'ORACLE_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XXXX'

EX:(ORACLE)

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'ORACLE_REMOTE', @SRVPRODUCT=N'ORACLE', @PROVIDER=N'MSDAORA', @DATASRC=N'INSTANCE_NAME'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'ORACLE_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XXXX'

EX:(SQLSERVER)

USE MATEREXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER=N'SQL_REMOTE', @SRVPRODUCT=N'', @PROVIDER=N'SQLOLEDB', @DATASRC=N'SQL_INSTANCE_NAME';

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'TEST',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XXXX',@RMTPASSWORD='XXXXX'

EX:(EXCEL FILES)

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER=N'EXCEL_TEST', @SRVPRODUCT=N'JET 4.0', @PROVIDER=N'MICROSOFT.JET.OLEDB.4.0', @DATASRC=N'D:\TEST.XLS',@PROVSTR='EXCEL 5.0'

SELECT * FROM EXCEL_TEST...[ONE$]

EX:(TEXT_FILES)

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER=N'TEXT_TEST', @SRVPRODUCT=N'JET 4.0', @PROVIDER=N'MICROSOFT.JET.OLEDB.4.0', @DATASRC=N'D:\',@PROVSTR='TEXT'

SELECT * FROM TEXT_TEST...[FILENAME#TXT]

NOTE: WHEN THE TEXT DRIVER IS USED, THE FORMAT OF THE TEXT FILE IS DETERMINED BY USING A SCHEMA INFORMATION FILE. THE SCHEMA INFORMATION FILE IS ALWAYS NAMED SCHEMA.INI AND ALWAYS KEPT IN THE SAME DIRECTORY AS THE TEXT DATA SOURCE. THE SCHEMA INFORMATION FILE PROVIDES THE IISAM WITH INFORMATION ABOUT THE GENERAL FORMAT OF THE FILE, THE COLUMN NAME AND DATA TYPE INFORMATION, AND SEVERAL OTHER DATA CHARACTERISTICS. A SCHEMA.INI FILE IS ALWAYS REQUIRED FOR ACCESSING FIXED-LENGTH DATA.(EX: TAB DELIMITED OR ^ OR ! OR )

IF THE FILE IS COMMA(,) SEPARATED FILE, THEN NO NEED TO USE SCHEMA.INI FILE.

HERE ARE THE SAMPLE SCHAM.INI FILES

EX:--(^) DELIMITED

[FILENAME.TXT]

COLNAMEHEADER=TRUE

FORMAT=DELIMITED(^)

MAXSCANROWS=0

CHARACTERSET=ANSI

COL1=ID CHAR WIDTH 50

COL2=NAME LONGCHAR

--TAB DELIMITED

[FILENAME.TXT]

COLNAMEHEADER=TRUE

FORMAT=TABDELIMITED

MAXSCANROWS=0

CHARACTERSET=ANSI

COL1=ID CHAR WIDTH 50

COL2=NAME LONGCHAR

ONCE YOU CREATE LINKED SERVER, YOU CAN ACCESS THESE DATA FROM

MULTIPLE WAYS

.

A)OPENQUERY::( IF YOU WANT TO ACCESS DATA FROM TABLE FROM OTHER SQL SERVER)

SELECT * FROM OPENQUERY(LINKED_SERVERNAME,'SELECT * FROM REMOTE_DBNAME.DBO.TABLE_NAME')

B)FOUR PART NAME

USING OPENQUERY, YOU CANNOT USE PARAMETERS,TO EXECUTE STORED PROCS USE FOUR PART NAME.

WHEN YO SELECT DATA WITH FOUR PART NAME,YOU SHOUL INCLUDE DATABASENAME,SCHEMANAME,TABLENAME

SELECT * FROM SQL_REMOTE.REMOTE_DBNAME.DBO.TABLE_NAME (SELECT * FROM SQL_REMOTE_DB.REMOTE_DBNAME..TABLE_NAME, THIS IS NOT VALID)

EXECUTE STORED PROC USING PARAMETER

EXEC SQL_REMOTE.REMOTE_DBNAME.DBO.STOREDPROC1 @PARAMETER=1

2) AD-HOC METHOD:::

THIS METHOD IS AN ALTERNATIVE TO ACCESSING TABLES IN AL LINKED SERVER AND IS A ONE-TIME, AD HOC METHOD OF CONNECTING AND ACCESSING REMOTE DATA

NY USING OLE DB.

A) OPENROWSET:::

THE OPENROWSET FUNCTION CAN BE REFERENCED IN THE FROM CLAUSE OF A QUERY AS IF IT WERE A TABLE NAME.

EX::

SELECT A.* FROM OPENROWSET('SQLNCLI10','SERVER=SERVER_NAME;TRUSTED_CONNECTION=YES;', 'SELECT * FROM DB_NAME.DBO.TABLE_NAME') AS A

SELECT A.* FROM OPENROWSET('SQLNCLI10','SERVER=USCTAP3823;UID=SEDEV;PWD=SEDEV','SELECT * FROM DB_NAME.DBO.TABLE_NAME') AS A

SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','EXCEL 5.0;DATABASE=D:\TEST.XLS',[ONE$]) AS A

B) OPENDATASOURCE:::

PROVIDES AD-HOC CONNECTION INFORMATION AS PART OF A FOUR PART OBJECT NAME WITHOUT USING A LINKED SERVER NAME.

EX::

SELECT * FROM OPENDATASOURCE('SQLNCLI','SERVER=SERVER_NAME;UID=XXX;PWD=XXXX').DATABASE_NAME.SCHEMA_NAME.TABLE_NAME

SELECT * FROM OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','DATA SOURCE=C:\TEST.XLS;EXTENDED PROPERTIES=EXCEL 5.0')...[ONE$]

Dynamically Unpivot Given Table.

UNPIVOT performs by rotating columns into rows.Using below script you can unpivot any given table
by mentioning key columns.

IF OBJECT_ID('[dbo].[unpivot_dynamically]','P') IS NOT NULL

DROP PROCEDURE [dbo].[unpivot_dynamically]

GO

CREATE procedure [dbo].[unpivot_dynamically]

@schemaname varchar(512)

,@srcTblName varchar(512)

,@keycol varchar(512)

,@pivotCol varchar(512)=null

,@valueCol varchar(512)=null

,@debug bit=0

as

begin

set nocount on

if ( @schemaname is null or @srcTblName is null )

---- it will check the any input parameter is NULL

begin

print'input parameters are null, please provide proper values'

return(1)

end

DECLARE @CASTCOLUMNS varchar(1024),@COLUMNLIST VARCHAR(1024),@keycoumns varchar(1024),@SQLSTRING NVARCHAR(1024),@PARAMDEFINATION NVARCHAR(1024)

SELECT @keycoumns=''''+REPLACE(@keycol,',',''',''')+''''

SELECT @SQLSTRING='SELECT @COLUMNLIST=coalesce(@COLUMNLIST,'''')+''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@COLUMNLIST NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@COLUMNLIST =@COLUMNLIST OUTPUT;

SELECT @COLUMNLIST=SUBSTRING(@COLUMNLIST,1,LEN(@COLUMNLIST)-1)

SELECT @SQLSTRING='select @CASTCOLUMNS=coalesce(@CASTCOLUMNS,'''')+''coalesce (cast (['' + COLUMN_NAME + ''] as varchar (max)), ''''9876543212345678'''') as ''+ ''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@CASTCOLUMNS NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@CASTCOLUMNS =@CASTCOLUMNS OUTPUT;

SELECT @CASTCOLUMNS=SUBSTRING(@CASTCOLUMNS,1,LEN(@CASTCOLUMNS)-1)

select @SQLString = 'SELECT ' + @keycol + ',' + @pivotCol + ', nullif(' + @valueCol + ',''9876543212345678'' ) as '+@valueCol+'

FROM (SELECT ' + @keycol + ', ' +@CASTCOLUMNS +'

FROM '+@schemaname+'.'+@srcTblName+ ' ) p

UNPIVOT

(' + @valueCol+ ' FOR ' + @pivotCol + ' IN

( '+ @COLUMNLIST + ' )

)AS unpvt'

IF @debug=1

print @SQLString

EXEC(@SQLString)

end

GO
EX:::

Create table Unpivot_test

(

id int,

name varchar(100),

age int,

city varchar(100),

dob datetime,

salary money,

comment text

)

go

insert into Unpivot_test

select 1,'ABC',12,'waltham','12/2/2000',4000,'testing1'

union all

select 2,'DEF',13,'natic','12/5/2000',3000,'testing2'

union all

select 3,'GHY',12,'boston',null,null,null

SELECT * FROM Unpivot_test

Using Above Sp, unpivoting above table

EXEC [dbo].[unpivot_dynamically]

@schemaName = 'dbo',@srcTblName = 'Unpivot_test',

@keyCol = 'id,name',

@pivotCol = 'property_name',

@valueCol = 'Property_value',

@debug=0

Identify your different instances query windows with different color codes.

If you work/connect to multiple instances at same time, Its very difficult identify which query window connected which server.Some times it will confuse.(I know in the below it will display server name)
Color coding the status bar in the Query window in SQL Server Management Studio is only available if you are using SQL Server 2008 Management Studio. Although I must note that I've found that using versions prior to 2008 R2 SP1

Ex:
If you are connected to DEV,UAT,PRD instances, and opened 3 query windows.(one query window from each instance), So without moving your mouse cursor to top you cannot tell which window connected to which server.
Note: This color codes only possible with registered servers.I assume you already registered required instances.
I registered 3 instances to my management studio.

In The above image you can see all the three windows has same color.

To color code a connection, you first need to open up the Registered Server view. Under the "Local Server Groups" item of the "Registered Server" view you will be defining your servers and how they will be color coded. Figure 2 shows three of my local instances that I want to color code. Right click and select properties of the registered server.

Now select connection properties window, and select Use custom color and select required color for your instance.

Here I selected Red for DEV, Green for my UAT and Blue for PRD.

Now open one new query window for each instance, see each window has one color.
Here I selected Horizontal tab group view to see all the colors.

How to share Temp table data between stored procs

a local temporary table created in stored proc sp1 is visible only in that same stored proc as well
as in any stored proc called by sp1 directly or indirectly, that is it is a child in the calling
hierarchy of sp1.
The temporary table will not be visible in stored procs that are not called by sp1 directly or indirectly,
that is they are outside of the calling hierarchy of sp1.
Examples

create PROCEDURE [dbo].[test01]

as begin

insert #temp_test01(id) values(1)

insert #temp_test01(id) values(2)

insert #temp_test01(id) values(3)

select * from #temp_test01

end

create PROCEDURE [dbo].[test02]

as begin

select * from #temp_test01

end

begin

create table #temp_test01(id int)

exec dbo.test01 -- if the table is created in here, then dbo.test02 should be called from inside

-- test01, otherwise it won't see the table

exec dbo.test02

end

Same goes for dynamic SQLs. The execution context of exec(SQL) is different from the execution context
of the host stored procedure, thus a temp table crate inside the dynamic SQL cannot be seen in the
stored proc itself that executes the dynamic sql.
In case the columns of the temp table are not known upfront, one can create the temp table with static sql
then add the columns to it with dynamic sql.

begin --

drop table #temp_test01

create table #temp_test01(id int)

end

declare @sql varchar(2000)

select @sql = 'alter table #temp_test01 add id2 int '

exec (@sql)

select @sql = 'insert into #temp_test01 values (100, 200) '

exec (@sql)

exec dbo.test01

exec dbo.test02

end