SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


openrowset from sql server 2000 to sql server 2016


openrowset from sql server 2000 to sql server 2016

Author
Message
Netanel
Netanel
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1792 Visits: 521
Hello all,
I have sql server 2000 Sad
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!
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96480 Visits: 23423
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Netanel
Netanel
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1792 Visits: 521
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
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96480 Visits: 23423
I really hope you aren't connecting as the sa, and that isn't your password.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Netanel
Netanel
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1792 Visits: 521
Oops 😜
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96480 Visits: 23423
89netanel - Tuesday, May 8, 2018 6:52 AM
Oops 😜

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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Steve Jones
Steve Jones
SSC Guru
SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)SSC Guru (688K reputation)

Group: Administrators
Points: 688788 Visits: 21594
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search