May 8, 2018 at 12:49 am
Hello all,
I have sql server 2000 🙁
and i have a view that query sql server 2016 via linked server and the performance are very bad.
on sql server 2000:
select * from sbo_invoicesl_vw
sp_helptext:
CREATE view [dbo].[SBO_InvoicesL_vw] as
select * from idc.idc.dbo.SBO_InvoicesL
(the server IDC is sql server 2016)
how can i rewrite it not to use linked server but to use with openrowset from sql server 2000?
Thanks in advance!
May 8, 2018 at 2:55 am
For things like this, you should be having a read of the documentation first; you'll find you learn more if you do the research yourself. This one, however, should be as simple as:
SELECT ORS.*
FROM OPENROWSET('SQLNCLI','SERVER=YourServerName;Trusted_Connection=yes;',
'SELECT* FROM idc.dbo.SBO_InvoicesL') AS ORS;
Obviously replace YourServerName with your server's name.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 6:03 am
Hello Thom,
thank you very much for your quick reply.
For some reason the SQLNCLI didn't work for me.
But i found this script and its work well for me:
Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=idc;UID=sa;PWD=1234',
'SELECT * FROM idc.dbo.SBO_InvoicesL') AS A
May 8, 2018 at 6:52 am
Oops ??
May 8, 2018 at 7:36 am
89netanel - Tuesday, May 8, 2018 6:52 AMOops 😜
I wasn't joking there. Having such a simple password for an sa account is probably one the worst things you can do in SQL Server. You should be using a highly secure password for any account with sysadmin privileges, and you should only be connecting as that login when you need to do sysadmin things. The sa account should NEVER be used for something like returning data in an OPENROWSET query, especially as the DDL will be stored in plain text in the View's DDL. YOu need to change the password, and the login that the OPENROWSET query uses, now (not later, this should be a huge priority for you).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 8:26 am
I'll second Thom's advice. This is asking for trouble and a poor habit.
Please create a new account that has limited access to what is needed. Use that. The password in code is a bad idea as well, but I understand restrictions and problems in the short term. It's best to at least limit the account. Longer term, you ought to use a linked server here.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply