Integration Services :: Connect Database Across 2 Domains Using Linked Server?

Oct 4, 2015

We have an Enterprise setup with multiple domains.

We a Domain A with SQL server 2008R2 say SQL-A and Domain B with SQL server 2008R2 ( SQL-B). It will be like SQL-A will always access SQL-B. Reverse is not required.

In Domain A we have a .Net Application which accesses SQL-A to perform CRUD operations which ultimately come to SQL- B since all SQL tables exist in SQL- B only.

Need to know the right way to implement Linked Server from SQL-A to SQL-B, so that Domain issues as well as DML execution work smooth.

I need not worry about DDL in SQL-A And SQL-B as it will taken care of by network and H/W team. I just need to focus on as developer.

View 7 Replies


ADVERTISEMENT

Integration Services :: Connect SSIS 2005 To Progress Database?

Sep 18, 2015

how can I connect SSIS 2005 to Progress database?

View 4 Replies View Related

Integration Services :: Connect To Oracle Database Using OLEDB Manager

Nov 23, 2010

Getting following error while trying to connect to ORACEL database using OLEDB manager.

Error at RADAR [Connection manager "RADAR_Updated"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

[code]....

View 6 Replies View Related

Integration Services :: Cannot Connect To Source Server

Aug 17, 2015

I have two servers (TESTSERVER, PRODSERVER). On the TESTSERVER i have a package that i'm using for data load from a source database on a SOURCESERVER. Everything works great until this point. Now from the PRODSERVER  in the SSIS package i'm trying to create a connection in the connection manager to the SOURCESERVER and i'm getting the following error

Test connection failed because of an error in initializing provider. Login timeout expired.A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

View 10 Replies View Related

Integration Services :: Cannot Fetch A Row From OLEDB Provider BULK For Linked Server

Nov 15, 2006

I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files.  Today I received this strange error ?  Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)

[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Reading from DTS buffer timed out.".

View 19 Replies View Related

Integration Services :: Run Transaction Across Multiple Instance Of Server Without Linked Server

May 16, 2015

i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.

View 4 Replies View Related

Integration Services :: Need To Create SSIS CONNECTION Manager Specifically For Linked Server?

Oct 1, 2015

I have a linked server setup in dev environment which is pointing to ORACLE server of a third-party. I m on  sql server 2008 r2 and   using BIDS 2008 for building an SSIS package which would pull data from oracle and do a look up in SQL server table to get a final  extract. Now the query which pulls data from a single table from linked server is like the one below

select * from [oracle linked server ]..dbo.tablename a
left join  mySQLSERVERtable b
on a.id=b.id 

Note:I can execute this query on SSMS as  the linked server is setup -[oracle linked server ] and I have sql  oledb connection manager for dev environment already configured in my ssis package .

My questions are do I need to create a SSIS CONNECTION manager specifically for linked server in my ssis package ?

Are there any best practices which we follow when using linked servers in this context ? I am thinking will the join suffer performance issue and what kind of exceptions I need to write when the linked server connection is broken.

View 4 Replies View Related

Integration Services :: Connect To SSIS 2012 Remotely - The RPC Server Is Unavailable

Sep 21, 2015

I have an SQL Server 2012 with SSIS installed on Windows Server 2008 R2 Core. I'd like to connect remotley to SSIS but I receive the following error. 'The RPC server is unavailable.'

View 2 Replies View Related

Integration Services :: Cannot Remote Connect To 2012 / 2014 SSIS Server

Sep 21, 2015

The following error occurred when trying to connect to 2012/2014 SSIS Server using SSMS remotely. Local connection works fine.Using the info from below link does not resolve the problem.  Permissions are granted through DCOM. If this cannot be resolved, packages will have to stored on filesystem instead.
URL....Connecting to the Integration Services service on the computer "" failed with the following error: "Class not registered".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

View 6 Replies View Related

Connect To Object Explorer But Cannot Connect To Integration Services.

Mar 9, 2006



I try to import my SSIS packages which are created using the Business Intelligent studio into SQL server 2005 but I cannot do that.

I am running SQL server 2005 enterprise edition.

Also when i tried to connect to Integration services using Object explorer, it only prompt me for server name. I cannot change the username and password which is disabled.

Then how am i supposed to connect to Integration services on a server in this case?

View 4 Replies View Related

SQL Server Admin 2014 :: Configure Transnational Replication Between Two Different Domains And Non Trusted Domains?

Apr 28, 2014

Is it possible to configure transnational replication between two different domains also non trusted domains.

It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.

View 2 Replies View Related

Integration Services :: Configure SMTP Server To Connect To GMAIL In Local System To Use Send Email Task

Sep 15, 2015

I am doing some ssis package development in my local machine. I have a requirement to send email the output.

I am developing in my local machine. Is it possible to configure SMTP configuration in my local system to receive the email in Gmail?

if yes, what all are the steps to be followed.

View 3 Replies View Related

SQL Server 2005 Analysis Services Across Domains

Sep 13, 2007

Hi,


With SQL Server 2000, the Enterprise Edition was required to access
Analysis Services across domains.



Is this also the case in SQL Server 2005, that the Enterprise Edition
is needed?



Thanks, S

View 3 Replies View Related

Cannot Connect To Integration Services

Nov 16, 2006

When i try to connect to SQL 2005 integration services from object explorer i get connected (in the sense it shows in object explorer running packages stored packages ..)but when i try to exand any of these objects i get the following error

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

Failed to retrieve data for this request(Microsoft.sqlserver.smoEnum)

Additional information

The sql server specified in SSIS service configuration is not present or not available.This might occur when there is no default instance of Sql server on the computer. For more information see "config integeration services" in server 2005 books online.

Login time out expired

An error has occured while establishing a connection to the server.When connecting to the Sql server 2005, the failure may be caused by the fact that under default settings Sqlserver does not allow remote connections.

Names Pipes Provider: could not open a connection to Sql Server[2].(MsDtsSrvr)

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

i have chkd remote connection settings and other settings but cant find a way out.

kindly provide some help

Thanks in advance



View 4 Replies View Related

Permissions To Connect To The Integration Services

Jul 3, 2007



Hi All,

i was ask to grant a developer permissions to use integration services.

he can run the ssis packages and it works fine, but he cant open integration services with sql management studio.

if i am adding him to the local administrators group on the server it works, but he is not a system administrator.

how can i connect to the integratin services without been a local administrator on the server ???

View 3 Replies View Related

Integration Services :: Unable To Connect To OLEDB

Sep 29, 2015

I'm using SQL Srv '12. Have ADW'12 and ADWDW'12 DBs installed.I created a new SSIS project. Two project level OLEDB con mngers (One for each OLEDB DB).Dragged and dropped a new DFT.Inside the DFT, I'm using Source Assistant to connect to the ADW'12 OLEDB source.

View 8 Replies View Related

Windows Authentication To Connect Integration Services?

Jul 11, 2007

I could not change user while attmepting to connvert Integration Services using Windows Authentication via MSSQL MS

any idea? thanks

View 8 Replies View Related

Integration Services :: Connect To INformix DB Via SSIS

Sep 22, 2015

I want to connect to an Informix Database via SSIS to read and store data. I did some search online and have downloaded Informix ODBC Drivers. I installed the drivers and am trying to configure a System DSN but have few doubts on what each field indicate. URL... I am not sure on what to enter in fields like IBM Informix Server, Service Name? Connecting to Informix DB via SSIS?

View 4 Replies View Related

Integration Services :: Unable To Connect To A Webpage From SSIS

Nov 3, 2015

I am trying to connect to a Web page from SSIS and I've tried both http connection manager and a Script Task but none of them seems to be working.

I get the following errors:

Through Script Component: Exception has been thrown by the target of an invocation.

Through http connection manager: The remote server returned an error: (503) Server Unavailable.

Here is one of the VB code I've tried:

Public Sub Main()
Dim MyWebClient As New System.Net.WebClient()
Dim proxyObject As New System.Net.WebProxy("BANANA:8080", True)
MyWebClient.Proxy = proxyObject

[Code] ....

And, here is one of the C# codes I've tried:

public void Main()
{
Variables varCollection = null;
Dts.VariableDispenser.LockForRead("User::RemoteUri");
Dts.VariableDispenser.LockForRead("User::LocalFolder");
Dts.VariableDispenser.GetVariables(ref varCollection);
varCollection.Unlock();

[Code] ...

I could open the same web page mentioned in the code manually in all my browsers (IE, Firefox, Chrome).

I am using SQL Server 2014.

View 18 Replies View Related

Integration Services :: Can Use SSIS To Connect To Oracle Via SSL TCPS

Jul 15, 2015

I've made connections in SSIS to Oracle before by setting up TNS but this latest connection is different.

I've been give an SSL cert that I had to import into the Oracle Wallet Manager and to get connected in 'Oracle SQL Developer' I had to choose connectionType = advanced and drop the following line in the JDBC URL section

jdbc:oracle:oci:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCPS)(HOST = server.domain.com)(PORT = 2484))(CONNECT_DATA =(SERVICE_NAME = XXXXX)))

So my question is. How can I go about making a connection like this in SSIS?

Just incase it matters, I'm working with OracleClient 11g

View 2 Replies View Related

Integration Services :: SSIS - Can't Connect Using SSMS Remote (locally OK)

May 18, 2015

Error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
 
Here are the steps I followed:

To configure rights for remote users on Windows Server 2003 or Windows XP

View 10 Replies View Related

Integration Services :: Connect SharePoint List View To SSMS

Aug 31, 2015

I need to install SSIS/SSRS in SSMS. I have connected the SharePoint list view to excel for reporting purpose but due to data size the performances is very slow in excel and now I would like to connect the SharePoint List to SSIS/SSRS

I have windows 8, 64 bit system

I have installed SQL Server Management studio 2012 but I am unable to enable the Integration and Reporting Services

May be I have missed to choose the option during installation.

View 5 Replies View Related

Integration Services :: SSIS Package Not Able To Connect To XLSX File

Apr 23, 2015

I have a very simply package using an Excel connection to an XLSX file.  It's a straight read of the file and import onto a table. 

The package works fine in Visual Studio 2008 development and also runs fine when executing on the (server I copied it to) under Integration Services.    

However, under a SQL Agent, the package (32-bit is checked) can not acquire the connection to an excel file.   I use UNC pathing to the file.  I've read other posts about similar problems and tried various scheduling options (including Owner of job).   

I even tried a to trigger it with a command-line which did not work: 

"C:Program Files (x86)Microsoft SQL Server100DTSBinnDTEXEC.exe" /sq "our packagesMy_XLSX_File_Import" /SERVER myserver /X86  /CHECKPOINTING OFF /REPORTING E

All errors are:   "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302." 

View 4 Replies View Related

Integration Services :: Using Attunity Connectors To Connect To Oracle Through SSIS

Apr 20, 2015

I'm having an issue connecting to an Oracle database using Visual Studio 2013 - Business Intelligence tools.I am running Windows 7 (64-bit), Visual Studio 2013, and have SQL 2012 Enterprise (64-bit) on my local machine. I downloaded the MS Connectors v2.0 for Oracle found here.  But when I try to add a new connection either through the connection manager or inserting a Data Flow Task and looking for the Oracle connections in the "Other Sources" or "Other Destinations" - neither option is available.I have the 32-bit Oracle client installed, the tnsnames.ora file has been created and I can connect to the Oracle DB through PL/SQL.I see Microsoft Connector for Oracle by Attunity 2.0 in my Programs list - installed for this file: Attunity SSISOra Adapters SetupX64.msi. don't know what else to try to get the connections available.

View 11 Replies View Related

Integration Services :: Using 2008 Native Client 10.0 Provider To Connect 2012 DB?

Jul 9, 2015

In our packages, we are using Microsoft SQL Server Native Client 10.0 provider to connect our SQL Server 2008 DBs, which is working fine till we are migrating 2008 DB server to 2012. after the upgrade, some of our packages are working fine, some are not. I'm just curiuos to know will the
old provider(Microsoft SQL Server Native Client 10.0) will work fine for connecting 2012 DBs? or I need to update all our packages to re-point to new 2012 Provider.

View 5 Replies View Related

Integration Services :: Database Creation Dynamically In Server From SSIS

Jul 31, 2015

I need to create a DB dynamically in the Server from the SSIS only....

How to implement this....

View 4 Replies View Related

Integration Services :: Extract Data From Server And Load Into Oracle Database?

Jun 23, 2015

Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.

View 5 Replies View Related

Analysis Services 2005 Database Processing Fails When Run From Integration Services

Oct 11, 2007

Hello, I have a problem when trying to fully process an SSAS database using Integration Services "Analysis Services Processing Task" task. I have 2 of these tasks which are responsible for processing the Dimensions then the Cubes. When I run the package either via the BIDS environment or on the local server from the Integration Services engine, I will get an error after about 20 minutes stating:

"Error: Memory Error: Allocation failure. Not enough storage is available to process this command""Error: Errors in the metadata manager. An error occurred when loading the <cube name> cube from the file \?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataMyWarehouse<cube file>.xml"

The cube name is not specific, it will fail and any of my cubes could be in the error log

If I fully process the AS database using the AS engine (logon to local AS server, right-click AS database and click Process), I get no errors at all, it processes and completes fine. The processing options are identical when I run in AS or via the SSIS "Analysis Services Processing Task" task.

I've searched quite a lot online but no joy, the information I have gleaned from various sites does not directly link SSIS with SSAS processing problems.

When either the AS processing starts via SSAS or SSIS the memory usage of MSMDSRV.exe increases to around 1.4 / 1.5 GB but never goes to 2GB ever, even when the error appears.

I've done the following with no effect.

" Have run via AS and works fine
" No specific cube it fails on
" Have created a Dimension only package, same problem
" Changed the maxmemorylimit
" Changed the connections to localhost
" Memory DOES NOT max out on server

Server Specs:
Windows Server 2003 Standard + Service Pack 2
4GM ram, 2GB paging file

SQL Server 2005 + Service Pack 2


Can anyone help?

Andy

View 2 Replies View Related

Can't Connect To Linked AD Server

Apr 13, 2008

Dear Friends,

select name As Name, sAMAccountName As UserName, title As Title,
physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber,
mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email,employeeid as emp_no from openquery
(
ADSI,'SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM ''LDAP://fp1-srvr.sidf.gov.sa''
WHERE objectCategory = ''Person''AND objectClass = ''user''
')

on sql server 2000 it was working but when migration to sql server2005
I got the following error when trying to excute on query analyzer:


Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM 'LDAP://fp1-srvr.sidf.gov.sa'
WHERE objectCategory = 'Person'AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Could you please help me in that

View 4 Replies View Related

Cannot Connect To Linked Server

Oct 16, 2007



Hi,

I have a SQL 2005 server (SQLA) on which I have created a linked server to a microsoft Access DB (LinkedAccessDB)

When I login (or remote desktop) directly into SQLA and run the following query, it works just fine:

select * from LinkedAccessDB...Table1

I also have SQL server 2005 installed on my workstation (WKSTA) and when I bring up Management Studio and connect to SQLA and try to run the previous query, I get the following error message:

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedAccessDB".

I've researched articles for the better part of a day, and haven't found anything that would help. Any ideas would be much appreciated.



NOTE: My workstation, the SQL Server (SQLA) and the file server where the AccessDB is located are all in the same Domain.

View 3 Replies View Related

Integration Services :: Connect SSIS To Google Drive Or Google Sheets

Aug 12, 2015

I need to download a file from google Drive or open it, using SSIS but I have not found any reference about it. Is it possible? Is there a provider to connect to Google Drive.

View 3 Replies View Related

Integration Services :: Import Varying Number Of Tables Each Time From One Database To Different Database

Sep 9, 2015

I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.

I have performed the following tasks **

1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)

3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.

4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.

What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.

View 5 Replies View Related

Linked Server Not Wanting To Connect

Sep 11, 2007

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:


================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

============= ERROR TEXT END ==============


Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:


================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

============= ERROR TEXT END ==============


Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========


/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB19', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB19',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'use remote collation', @optvalue=N'true'

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========


/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB21', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB21',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'use remote collation', @optvalue=N'true'

=========== CODE END ==========

Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.


@provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved