I still want to let you know that a cursor is the wrong way to do this. So, unless this is a homework assignment you may want to ask about...

if @sql...

You are saying IF @sql = '' and after the first run through, it does not = ''. Then you are setting @sql = something. It should be set...

Really? I have linked servers set up with users that do not exist on the...

Well... I am out of ideas. Sorry!

Jared

No it is passing these credentials to the remote server.

Jared

Sapen (11/30/2011)

---

I havent setup anything in the security part of the linked server properties. But in the provider string, I am using the username and password to connect to the...

Sapen (11/30/2011)

---

Forgot the Mention that I am using SQL Server 2005 Standard Edition. I have enabled the RPC but still ended up getting the same error. I dont see the...

mamidimahesh (11/29/2011)

---

I get below error when I restored productin db to my environment. Can someone help me how to resolve this issue

Could not connect to server '<<ServerName>>' because 'sa'...

So this is before you try to execute it? If so, does it execute properly?

Jared

Sapen (11/30/2011)

---

Thanks for you inputs Jared.

I have just enabled the RPC out and executed my query and I end up getting the below error:

OLE DB provider "MSDASQL" for linked server...

Hmm... Try enabling RPC out. Also, is the login from your SSMS on your machine the same that you use to login to SSMS on the server?

Jared

Ha! I realized who...

sturner (11/30/2011)

---

That's just how it works. You could use dynamic SQL to avoid the issue, but make sure you use try - catch to handle any run time permission/connectivity...

Also, I believe doing SELECT TOP 1000 will not necessarily give you the first identity value. You have to have an ORDER BY to guarantee your result set.

Jared