November 30, 2011 at 2:11 pm
Yes it does return 1.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 2:12 pm
Randy Doub (11/30/2011)
In your linked server script you have @provstr=N'DSN=SOTAMAS90'
Is this a DSN that lives on the DB server?
And when you use your laptop, that DSN can't be found? Do you need the same DSN on your laptop?
No, the DSN is used by the server not by the local client accessing it.
Jared
I dont think I need to have the mas90 drivers installed on my laptop so I havent configured it.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 2:14 pm
Sapen (11/30/2011)
Randy Doub (11/30/2011)
In your linked server script you have @provstr=N'DSN=SOTAMAS90'
Is this a DSN that lives on the DB server?
And when you use your laptop, that DSN can't be found? Do you need the same DSN on your laptop?
No, the DSN is used by the server not by the local client accessing it.
Jared
I dont think I need to have the mas90 drivers installed on my laptop so I havent configured it.
OH! Thought... Did you set up the DSN as a system DSN or a user dsn? I bet it has something to do with the DSN settings...
Jared
Jared
CE - Microsoft
November 30, 2011 at 2:22 pm
OH! Thought... Did you set up the DSN as a system DSN or a user dsn? I bet it has something to do with the DSN settings...
Jared
I had to set up DSN in both User as well as System DSN coz it didnt work from the database server as well when I just set up with User DSN
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 2:26 pm
Sapen (11/30/2011)
OH! Thought... Did you set up the DSN as a system DSN or a user dsn? I bet it has something to do with the DSN settings...
Jared
I had to set up DSN in both User as well as System DSN coz it didnt work from the database server as well when I just set up with User DSN
I would delete any user dsn for this and then double check configuration settings for the System DSN.
Thanks,
Jared
Jared
CE - Microsoft
November 30, 2011 at 2:28 pm
I can do that real quick but I am able to select the data from the DB server directly...does removing the user DSN be helpful?
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 2:34 pm
Sapen (11/30/2011)
I can do that real quick but I am able to select the data from the DB server directly...does removing the user DSN be helpful?
Not sure... but it may be. Also, User DSN is useless if set up as a System DSN, so there may be conflicts. Also, do you have SQL Server 2005 installed anywhere that you could use SSMS 2005 to connect and see if there is a difference? The fact that you can query tells me that the connection is good, but the fact that you cannot see schema could be a version issue.
Jared
Jared
CE - Microsoft
November 30, 2011 at 2:46 pm
I dont have 2005 on any other server. But I tested the linked server connectivity with 2008 R2 as well. I come up with same error when I try to connect from my laptop that has 32bit sqlserver 2008 r2 sp1 SE.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 2:49 pm
I removed the USER DSN now and tested it but with sql server 2008 R2 linked server connectivity and I get the same error.
I have the linked server setup to the same mas90 from sql 2005 and sql 2008 r2.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 8:19 am
MyDoggieJessie/Jared...Thanks for your inputs...but this is still an issue...I am doing my research to figure out how to make it work...please let me know incase if you come up with something...
thanks again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 8:28 am
Sapen (12/1/2011)
MyDoggieJessie/Jared...Thanks for your inputs...but this is still an issue...I am doing my research to figure out how to make it work...please let me know incase if you come up with something...thanks again
Can you access any other linked servers from SSMS on your laptop in the expected way? I just want to make sure that it is specific to this linked server. I just have to believe that it is a permissions issue somewhere or a setting.
Jared
Jared
CE - Microsoft
December 1, 2011 at 8:47 am
No..I am not able to access any linked server unless I create the linked server connection directly on sql server installed on my laptop.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 8:59 am
Sapen (12/1/2011)
No..I am not able to access any linked server unless I create the linked server connection directly on sql server installed on my laptop.
I think it is this setting:
SELECT * FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries'
Is this set to 1 for Value?
Jared
Jared
CE - Microsoft
December 1, 2011 at 9:01 am
p-nut (12/1/2011)
Sapen (12/1/2011)
No..I am not able to access any linked server unless I create the linked server connection directly on sql server installed on my laptop.I think it is this setting:
SELECT * FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries'Is this set to 1 for Value?
Jared
Or not... I remember something in the past about a setting for this. I will keep researching.
Jared
Jared
CE - Microsoft
December 1, 2011 at 9:12 am
I ask again, only because I cannot figure this out... Are you using the same SQL Server (windows auth) user to log into the database instance between the server and your laptop? It has to be a permissions issue with the user as far as I can tell...
http://blogs.msdn.com/b/john_daskalakis/archive/2010/02/01/9956271.aspx i.e. When you log into the server are you using the EXACT same credentials that you use to log into your laptop? If you use windows authentication to log into SQL and the machine logins are different, you will have different permissions. For example, you may log into your laptop as domain\username, but log into the server using only username. Those are 2 different SQL logins and may have different permissions.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply