Backup Linked Server script in SQL Server by SSIS

Backup Linked Server script in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database only, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for all Linkedserver in SQL Server instance by SSIS.

As long as possible, I, personally, try to avoid linked server but even in some scenario DBA used to use linked server because of any reason (don’t want to start debate of whether to use linked server or not). while making a plan for failover or for creating another server/instance of one of the production server, DBA has to keep Linked Server in mind otherwise some package, view, SP etc. can break in new server/instance if it is uses linked server.

Let us now create one SSIS package to generate the script of all Linked Server from SQL Server. I used to keep monthly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of linked server every month.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]
SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
‘Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ”A” and name = ”ALL SETTABLE OPTIONS”

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 open d

fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname

SET NOCOUNT ON

while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values(”——————–” + @Server + ”——————–”)
If @status in (64,65)
Begin
Insert Into @LinkedServer values( ”sp_addserver”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(” GO”)
If @status = 64
Begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values( ” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values( ” @optname = ””rpc””,”)
Insert Into @LinkedServer values( ” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
End
exec (”declare r cursor for select l.name, r.remoteusername from sysremotelogins r join sysservers s on r.remoteserverid = s.srvid join syslogins l
on r.sid = l.sid where s.srvname = ”””+ @server + ””””) open r fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_addremotelogin”)
Insert Into @LinkedServer values(” @remoteserver = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @loginame = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @remotename = ”””+ @rmtlogin + ””””)
Insert Into @LinkedServer values(” GO”)

fetch next from r into @loclogin, @rmtlogin end close r deallocate r
End
Else
Begin
If exists (select * from tempdb..sysobjects where name like ”#tmpsrvoption%”)
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption ( srvoption varchar(30) )

insert #tmpsrvoption
select v.name from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid and (v.number & s.srvstatus)=v.number and (v.number & isnull(@allsetopt,4063)) <> 0 and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = ”A” Insert Into @LinkedServer values( ”sp_addlinkedserver”)

Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(”, @srvproduct = ””” + @srvproduct + ””””)
If @srvproduct <> ”SQL Server”
Begin
Insert Into @LinkedServer values( ”, @provider = ””” + isnull(@provider,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @datasrc = ””” + isnull(@datasrc,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @location = ””” + isnull(@location,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @provstr = ””” + isnull(@provstr,”NULL”) + ”””” )
Insert Into @LinkedServer values(”, @catalog = ””” + isnull(@catalog,”NULL”) + ””””)
End

Insert Into @LinkedServer values( ” GO”)
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc out””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””data access””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)

declare s cursor for SELECT srvoption from #tmpsrvoption open s
fetch next from s into @srvoption while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ”””+ @srvoption + ”””,”)
Insert Into @LinkedServer values(” @optvalue = ””true”””)
Insert Into @LinkedServer values(” GO”)

fetch next from s into @srvoption
End

close s
deallocate s

If exists (select * from tempdb..sysobjects where name like ”#tmplink%”)
Begin
drop table #tmplink
End

create table #tmplink ( rmtserver sysname, loclogin sysname null, selfstatus smallint, rmtlogin sysname null )

insert #tmplink exec (”sp_helplinkedsrvlogin ”””+ @server + ””””)

declare ll cursor for select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin open ll fetch next from ll

into @loclogin, @selfstatus, @rmtlogin while (@@FETCH_STATUS<>-1)

begin If (@selfstatus = 1 and @loclogin is null)

Begin

Insert Into @LinkedServer values( ”sp_addlinkedsrvlogin”)

Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)

Insert Into @LinkedServer values(” @useself = ””true”””)

Insert Into @LinkedServer values(” GO”)

End

Else If (@selfstatus = 1 and @loclogin is not null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””true””,”)
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0 and @rmtlogin is null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)
Insert Into @LinkedServer values(” @locallogin = NULL,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0)
Begin
exec (”declare password cursor for select l.password from master..sysservers s join master..syslogins l on s.srvid = l.sid
where s.srvname = ”””+ @server + ””” and l.name = ”””+ @rmtlogin + ””””)

open password fetch next from password into @rmtpass while @@fetch_status = 0
begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)

If (@loclogin is null)
Begin
Insert Into @LinkedServer values(” @locallogin = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
End

If (@rmtlogin is null)
Begin
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtuser = ”””+ @rmtlogin + ”””,”)
End

If (@rmtpass is null)
Begin
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtpassword = ”””+ @rmtpass + ””””)
End

Insert Into @LinkedServer values(” GO”)

fetch next from password into @rmtpass
end
close password

deallocate password
End

fetch next from ll into @loclogin, @selfstatus, @rmtlogin End close ll deallocate ll
End

If @netname <> @server
Begin
Insert Into @LinkedServer values( ”sp_setnetname”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @network_name = ”””+ @netname + ””””)
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname
End close d deallocate d Select [LinkedServer] From @LinkedServer’

Exec (@SQL)
[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2PropertyofDataflow

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “LinkedServer.sql” which I already had. You can keep blank SQL file in your destination.

33FileDestination

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “LinkedServer” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

FinalExecutionBackupLinkedServerScriptSSISPackage

We have many different server/instance and need script for all Linked Server from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

Note: If you have set password for your linked server, this script won’t decrypt password and give it to you. You have to manually change it in the script this SSIS package will create.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

I have written few more Linked Server related articles in past, have a look if you are interested.

  • Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)
  • Linked Server in SQL Server 2005 from ACCESS 2007 (click Here)
  • Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)
  • Linked Server is not configured for data access Error: 7411 in SQL Server 2005(Click Here)
  • Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkServerName” was unable to begin a distributed transaction. (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

This error comes when you are trying execute distributed transaction via linked server. Personally I don’t prefer linked server and would always go for other alternatives like SSIS package or something else but one of my friend had a situation where he must had to setup linked server to make distributed transaction. Security wasn’t concern for him as his network was just an intranet and under hardware as well as software firewall so I have helped him to setup distributed transaction for linked server so that he can fix the error:

Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “MARSLINK” was unable to begin a distributed transaction

For example we have two server named “SATURN” and “MARS” accordingly. We have set link server of MARS in SATURN so that I can access database of MARS from SATURN. I wanted to execute one SP from one of the database of MARS from SATURN and it was working fine after setting up linked server but when I tried to take output of this SP into one temp table, it started popping up the error given above.

Let us try to resolve this error.

First I will connect to first server which is SATURN in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab

134

Select “Network DTC Access” checkbox and also select “Allow Outbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Once SATURN is configured, let us do the same in MARS with minor change:

Now, I will connect to second server which is MARS in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab .

136

Select “Network DTC Access” checkbox and also select “Allow Inbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Now, you will be able to execute distributed transaction via linked server.

I have some more article on the subject of Linked Server, have a look at it, if you are interested on this subject.

Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)

Linked Server is not configured for data access Error: 7411 in SQL Server 2005 (Click Here)

Linked Server in SQL Server 2005 from ACCESS 2007 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Create Linked server with Excel 2007 worksheet in SQL Server 2005

I have written one article to create linked server with Access database but I seen in many forums that people mostly would like to use excel file and wants to import its data. However, there are many ways to import Excel data to SQL Server but Linked server gives you more freedom over data. Let us see how we can do this. Before you start doing this, I would like you to create one excel 2007 file named “Dept” and put it in C: drive.

See my previous article for linked server with Access at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

There will be two column in your excel 2007 file with following data in Sheet1

Department Description
MIS IT department
Acct Account Department
Chem Chemist Department

Now let us move to SQL server and make linked server.

EXECUTE sp_addlinkedserver

@server = ‘DeptExcel’,

@srvproduct = ‘ACE 12.0’,

@provider = ‘Microsoft.ACE.OLEDB.12.0’,

@datasrc = ‘C:\Dept.xlsx’,

@provstr = ‘Excel 12.0’

GO

–excel’s credential if any

EXEC sp_addlinkedsrvlogin ‘DeptExcel’, ‘false’

go

–list all tables from excel file

exec sp_tables_ex ‘DeptExcel’

go

–see table in sheet1

SELECT * FROM [DeptExcel]sheet1$

go

–create table in SQL and imports data

select * into tempTable FROM [DeptExcel]sheet1$

go

–check your SQL table

select * from tempTable

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “TestLinkServer”.

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “ TestLinkServer” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.

After reading my linked server’s article in SQL Server 2005 at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

http://www.sqlhub.com/2009/03/linked-server-is-not-configured-for.html

One of my subordinate wanted to use it in one of our small application. He started using the same code I gave in one of my above article about Linked Server but he was facing above described error. He tried everything and every possible solution from internet but didn’t find his luck. Finally he told me to look at the error and try to solve it. At the very first glance I found problem with his OLE DB adapter. He was using the MDB file which has been developed in ACCESS 97 but there was ACCESS 2007 in our SQL’s development server. As MDB was old, he was using “Microsoft.Jet.OLEDB.4.0” but he should use ” Microsoft.ACE.OLEDB.12.0” as our development server has ACCESS 2007 and it doesn’t understand old provider.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server is not configured for data access Error: 7411 in SQL Server 2005

I wrote one article to set Linked server at:

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Sometime you properly set the link server and while trying to execute SELECT statement over it, it may show you following error.

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution of this error is really pretty much simple. Before we move to solution of error I would like to raise this error. Run the linked server example given in above article and see whether it is working properly. One you done with that let us change one property of Linked Server which will raise above said error.

–set data access property to ‘false’ which will generate error

–when you try to execute any SELECT statement against your linked server

exec sp_serveroption [LinkToAceess],‘Data Access’,‘false’

Now try to execute SELECT statement and greet the error:

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution is so simple; just enable your data access to linked server with following command.

exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server in SQL Server 2005 from ACCESS 2007

Linked Server is one of the cool features of Microsoft SQL Server especially for those who want to access remote database, remote database could be of any type SQL Server, Sybase, Access etc. you can access those databases with OLEDB provider. Let us see how can we do it?

Before you move further I would like to request my readers that please create one Access MDB file named “Testing.MDB” and create at least one table named “empDetails” in that MDB.

Now, we will link “Testing.MDB” database to SQL Server 2005.

Note: You can do it by wizard and by query but I prefer query so will show you that path only.

Below query will cover file steps to link the server.

1.) Add linked server

2.) Add linked server credential, if any

3.) Check sys.servers whether database is added as linked server

4.) List all available tables in MDB file

5.) Perform SELECT on “empDetails” table.

–add link server

exec sp_addlinkedserver

@server=‘LinkToAceess’,

@srvproduct=‘AccessDatabase’,

–if you have older version of access then kindly use old jet provider

–Microsoft.Jet.OLEDB.4.0

@provider=‘Microsoft.ACE.OLEDB.12.0’,

@datasrc=‘C:\Testing.mdb’

GO

–add MDB’s credental, if any

EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’

–check whether ‘LinkToAccess’ has been added

select * from sys.servers

–list all tables available in Testing.MDB

exec sp_tables_ex ‘LinkToAceess’

–perform SELECT on empDetail table.

SELECT * FROM [LinkToAceess]empDetails

So, finally you have used Linked Server from within your SQL Server

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah