145
Hi I am trying to run a report from RMS which includes another database. I can successfully run the sql from Server management studio but when I turn this into a report it cannot access the other database with an sa login fail. Brilliant idea change the sa log on for the other database to the same logon as RMS the report runs fine - fantastic – but the other database is broken :( ….. any ideas how to overcome this
*This post is locked for comments
Hi I use loads of linked servers particularly with Excel to import items, update prices and all sorts of other useful things. This is the first time I have ever tried directly linking RMS with a database written by someone else. It works joining it with a combination of barcodes and supplier.reordernumber(s) to run a report highilighting items that our supllier has on special offer giveing a really easy way of ordering same.
I was suprised how easy it was and how powerful especially being able to drill down from the itemlookupcode. Defininately a sucess - wished I hadden't waited a year to get around to it.
/****** Object: LinkedServer [Server\database] Script Date: 10/26/2013 15:31:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Server\database', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server\database',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server\database',@useself=N'True',@locallogin=N'login',@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'collation name', @optvalue=N'SQL_Latin1_General_CP1_CI_AS'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Server\database', @optname=N'use remote collation', @optvalue=N'true'
To clarify, did you link the external database using sp_addlinkedserver? This is something I've wanted to try setting up for quite a while, but I was advised against even trying it for stability reasons. If you pulled it off, would you mind posting the syntax that you used?
Got it - well it works - I changed the the security on the linked server to log into the external database with sa and the sa password for the external password if it couldn't log on with windows security. I now have the other databases sa password because I had to admit to breaking some elses program!
Yes it is on a different server. I would prefer not to alter sa on the RMS's database as it would effect some other programs which use sa to access the RMS database.
The ammount of data is not great. I don't want to have to run a script before running the report if I can help it. I suppose I could create a trigger on the external database to update the tables on the RMS server but that might break the other program.
Hi David,
I assume that this database is on a different server right?
If the other database won't tolerate the 'sa' password change, you could change RMS's 'sa' password to match the now broken database.
Alternatively, you could set up a scheduled SQL agent job to pull the data over from the external database into custom or even temp tables in RMS (of course, I am unsure of how much data you're dealing with).
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (