Does your password has special characters in it?
If so, wrap the password within double or single quotes and try creating the linked server.
Tried that but still same issue, also changed the password to something simple with no special characters in.
Tried KB KA328821 everything looks ok.
Not sure if you were replying to Naveen or I :-)
Is this a SQL Server named instance? (as opposed to a default instance?)
Default sql shows this
TS-BL-DB-VSQL (SQL Server 10.50.2500 - userid)
Lets step out of BDSSA for a moment.
In MS SQL Server Management Studio, what do you receive when you navigate to the linked server, right click and select “Test Connection”?
If that fails, please send the error and also include what you provided for this section of create_linked_server_for_om.sql when creating the link:
set @om_sqlsvr_instance = N''
The Error i receive is
TITLE: Microsoft SQL Server Management Studio
The test connection to the linked server failed.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Login failed for user 'bladelogic'.
OLE DB provider "SQLNCLI10" for linked server "DB_SITE1_LINK" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 18456)
The SQL Script
set @om_sqlsvr_instance = N'TS-BL-DW-VSQL'
set @om_db_name = N'bladelogic1'
set @om_sqlsvr_login = N'bladelogic'
set @om_sqlsvr_pwd = N'password'
What version of SQL Server is involved here?
Are the BSARA_DW_DB and bladelogic DB on the same server or different servers?
Is there a SQL Server DBA onsite who you can show this to as the error is obviously happening even from within SQL Server Management Studio.
Using SQL 2008 R2
BSARA_DW_DB and BladeLogic DB on different Servers.
No DBA on site at present.
If the OM database is on default instance it is good to use the machine IP Address as the Instance name to avoid error free transaction. In case if its on named instance, then get the Instance name from SQL Configuration Manager and provide it as 'IP Adress\Named Instance name'
Adam, as discussed in ticket by using IP Address the Link has created sucessfully
Have another Issue now, get this when copening Cognos configuration
and then this when trying to start Cognos
Successfully launched a test JVM with the memory setting of '768'. Note that this does not guarantee that IBM Cognos 8 will start and run successfully.
To see which JVM options are based on this setting, view c8_location/bin/bootstrap_<OS>.xml and see your JVM documentation for an explanation of those options.
[ ERROR ] CFG-ERR-0101 Unable to register IBM Cognos 8 service.
Execution of the external process returns an error code value of '5'.
there's a lock file in the portal\config directory - look for *.lck or *.lock. if no other cogconfig is running you should be ok removing that file. you may want to stop the cognos service as well.
and ETL job failed for this to
Session Step: Order Number:
No. of Executions:
Oracle Data Integrator Command
Oracle Data Integrator Command
Name: Start Date:
Status: Return Code:
com.sunopsis.dwg.function.SnpsFunctionBaseException: OS command returned 6.