"Amos Soma" <amos_j_s***@yahoo.com> wrote in message
news:uewePxFyGHA.540@TK2MSFTNGP03.phx.gbl...
> Hello,
>
> In SQL 2005, from a stored procedure in a local database I am attempting
to
> execute a remote stored procedure in another database on another server. I
> am getting the error referred to in the Subject when the local stored
> procedure tries to execute the remote stored procedure. A couple of
> comments:
>
>   a.. The remote database is set up as a linked server in the local
> database. As part of the linked server definition I selected the 'be made
> using this security context', and provided a local user name and password.
>   b.. The remote database is set to Trustworthy.
>   c.. I have tried every combination of WITH Execute As on the remote
stored
> procedure but nothing works.
>   d.. I can query against the remote database successfully within
Management
> Studio. I can even execute the remote stored procedure successfully from
> within M.S., but not from within my local stored procedure when it is run.
> Thank you for your help on this - Amos.
>
>

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O%23QO$dHyGHA.3456@TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using?
>
> Are you using a SQL Server login for your linked server, or a network
> login?
> The code below assumes you are using a SQL Server login.
>
> Are you using QA to try to run this code?
>
> Try creating the linked server in QA with a script like this one, and see
> what happens...
>
> exec sp_addlinkedserver @server = 'MyLinkedServer', --Server Alias
>    @srvproduct = '', -- Leave this blank
>    @provider = 'SQLOLEDB', -- provider for SQL Server
>    @datasrc = 'SERVERNAME', -- DB Server Name
>    @catalog = 'CATALOG' -- Default catalog, seems to do nothing as tables
> need to be fully qualified
> go
> EXEC sp_addlinkedsrvlogin 'MyLinkedServer' -- Server Alias
>    , 'false' -- do not use local credentials
>    , NULL -- do not use one local ID, but rather set the logins for all
> local IDs as follows
>    , 'LOGIN' -- set login
>    , 'PASSWORD' -- set password
> go
>
> "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
> news:uewePxFyGHA.540@TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> In SQL 2005, from a stored procedure in a local database I am attempting
> to
>> execute a remote stored procedure in another database on another server.
>> I
>> am getting the error referred to in the Subject when the local stored
>> procedure tries to execute the remote stored procedure. A couple of
>> comments:
>>
>>   a.. The remote database is set up as a linked server in the local
>> database. As part of the linked server definition I selected the 'be made
>> using this security context', and provided a local user name and
>> password.
>>   b.. The remote database is set to Trustworthy.
>>   c.. I have tried every combination of WITH Execute As on the remote
> stored
>> procedure but nothing works.
>>   d.. I can query against the remote database successfully within
> Management
>> Studio. I can even execute the remote stored procedure successfully from
>> within M.S., but not from within my local stored procedure when it is
>> run.
>> Thank you for your help on this - Amos.
>>
>>
>
>

"Amos Soma" <amos_j_s***@yahoo.com> wrote in message
news:O0cuACIyGHA.4336@TK2MSFTNGP06.phx.gbl...
> Hi Jim,
>
> I am using SQL 2005. I am using a SQL Server login. I tried the linked
> server login and that didn't help. This is what is confusing to me because
> everything seems to be set up correctly.
>
> Amos.
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:O%23QO$dHyGHA.3456@TK2MSFTNGP03.phx.gbl...
> > What version of SQL Server are you using?
> >
> > Are you using a SQL Server login for your linked server, or a network
> > login?
> > The code below assumes you are using a SQL Server login.
> >
> > Are you using QA to try to run this code?
> >
> > Try creating the linked server in QA with a script like this one, and
see
> > what happens...
> >
> > exec sp_addlinkedserver @server = 'MyLinkedServer', --Server Alias
> >    @srvproduct = '', -- Leave this blank
> >    @provider = 'SQLOLEDB', -- provider for SQL Server
> >    @datasrc = 'SERVERNAME', -- DB Server Name
> >    @catalog = 'CATALOG' -- Default catalog, seems to do nothing as
tables
> > need to be fully qualified
> > go
> > EXEC sp_addlinkedsrvlogin 'MyLinkedServer' -- Server Alias
> >    , 'false' -- do not use local credentials
> >    , NULL -- do not use one local ID, but rather set the logins for all
> > local IDs as follows
> >    , 'LOGIN' -- set login
> >    , 'PASSWORD' -- set password
> > go
> >
> > "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
> > news:uewePxFyGHA.540@TK2MSFTNGP03.phx.gbl...
> >> Hello,
> >>
> >> In SQL 2005, from a stored procedure in a local database I am
attempting
> > to
> >> execute a remote stored procedure in another database on another
server.
> >> I
> >> am getting the error referred to in the Subject when the local stored
> >> procedure tries to execute the remote stored procedure. A couple of
> >> comments:
> >>
> >>   a.. The remote database is set up as a linked server in the local
> >> database. As part of the linked server definition I selected the 'be
made
> >> using this security context', and provided a local user name and
> >> password.
> >>   b.. The remote database is set to Trustworthy.
> >>   c.. I have tried every combination of WITH Execute As on the remote
> > stored
> >> procedure but nothing works.
> >>   d.. I can query against the remote database successfully within
> > Management
> >> Studio. I can even execute the remote stored procedure successfully
from
> >> within M.S., but not from within my local stored procedure when it is
> >> run.
> >> Thank you for your help on this - Amos.
> >>
> >>
> >
> >
>
>

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uLr3gIIyGHA.2168@TK2MSFTNGP06.phx.gbl...
> It sounded right to me as well, but we know something is not right because
> you are getting the error.  Unfortunately, I only know a little about
> linked
> servers, and the code below works for me.
>
> Hopefully someone more knowledgeable will be able to chime in with some
> insight.
>
>
> "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
> news:O0cuACIyGHA.4336@TK2MSFTNGP06.phx.gbl...
>> Hi Jim,
>>
>> I am using SQL 2005. I am using a SQL Server login. I tried the linked
>> server login and that didn't help. This is what is confusing to me
>> because
>> everything seems to be set up correctly.
>>
>> Amos.
>>
>> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
>> news:O%23QO$dHyGHA.3456@TK2MSFTNGP03.phx.gbl...
>> > What version of SQL Server are you using?
>> >
>> > Are you using a SQL Server login for your linked server, or a network
>> > login?
>> > The code below assumes you are using a SQL Server login.
>> >
>> > Are you using QA to try to run this code?
>> >
>> > Try creating the linked server in QA with a script like this one, and
> see
>> > what happens...
>> >
>> > exec sp_addlinkedserver @server = 'MyLinkedServer', --Server Alias
>> >    @srvproduct = '', -- Leave this blank
>> >    @provider = 'SQLOLEDB', -- provider for SQL Server
>> >    @datasrc = 'SERVERNAME', -- DB Server Name
>> >    @catalog = 'CATALOG' -- Default catalog, seems to do nothing as
> tables
>> > need to be fully qualified
>> > go
>> > EXEC sp_addlinkedsrvlogin 'MyLinkedServer' -- Server Alias
>> >    , 'false' -- do not use local credentials
>> >    , NULL -- do not use one local ID, but rather set the logins for all
>> > local IDs as follows
>> >    , 'LOGIN' -- set login
>> >    , 'PASSWORD' -- set password
>> > go
>> >
>> > "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
>> > news:uewePxFyGHA.540@TK2MSFTNGP03.phx.gbl...
>> >> Hello,
>> >>
>> >> In SQL 2005, from a stored procedure in a local database I am
> attempting
>> > to
>> >> execute a remote stored procedure in another database on another
> server.
>> >> I
>> >> am getting the error referred to in the Subject when the local stored
>> >> procedure tries to execute the remote stored procedure. A couple of
>> >> comments:
>> >>
>> >>   a.. The remote database is set up as a linked server in the local
>> >> database. As part of the linked server definition I selected the 'be
> made
>> >> using this security context', and provided a local user name and
>> >> password.
>> >>   b.. The remote database is set to Trustworthy.
>> >>   c.. I have tried every combination of WITH Execute As on the remote
>> > stored
>> >> procedure but nothing works.
>> >>   d.. I can query against the remote database successfully within
>> > Management
>> >> Studio. I can even execute the remote stored procedure successfully
> from
>> >> within M.S., but not from within my local stored procedure when it is
>> >> run.
>> >> Thank you for your help on this - Amos.
>> >>
>> >>
>> >
>> >
>>
>>
>
>

"Amos Soma" <amos_j_s***@yahoo.com> wrote in message
news:O5rqZLIyGHA.2400@TK2MSFTNGP03.phx.gbl...
> Are you attempting to do what I am? I am attempting to have a local stored
> procedure execute a remote stored procedure. I can execute the remote
stored
> procedure just in within Management Studio. However, it fails when the
local
> stored procedure attempts to execute the remote.
>
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uLr3gIIyGHA.2168@TK2MSFTNGP06.phx.gbl...
> > It sounded right to me as well, but we know something is not right
because
> > you are getting the error.  Unfortunately, I only know a little about
> > linked
> > servers, and the code below works for me.
> >
> > Hopefully someone more knowledgeable will be able to chime in with some
> > insight.
> >
> >
> > "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
> > news:O0cuACIyGHA.4336@TK2MSFTNGP06.phx.gbl...
> >> Hi Jim,
> >>
> >> I am using SQL 2005. I am using a SQL Server login. I tried the linked
> >> server login and that didn't help. This is what is confusing to me
> >> because
> >> everything seems to be set up correctly.
> >>
> >> Amos.
> >>
> >> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> >> news:O%23QO$dHyGHA.3456@TK2MSFTNGP03.phx.gbl...
> >> > What version of SQL Server are you using?
> >> >
> >> > Are you using a SQL Server login for your linked server, or a network
> >> > login?
> >> > The code below assumes you are using a SQL Server login.
> >> >
> >> > Are you using QA to try to run this code?
> >> >
> >> > Try creating the linked server in QA with a script like this one, and
> > see
> >> > what happens...
> >> >
> >> > exec sp_addlinkedserver @server = 'MyLinkedServer', --Server Alias
> >> >    @srvproduct = '', -- Leave this blank
> >> >    @provider = 'SQLOLEDB', -- provider for SQL Server
> >> >    @datasrc = 'SERVERNAME', -- DB Server Name
> >> >    @catalog = 'CATALOG' -- Default catalog, seems to do nothing as
> > tables
> >> > need to be fully qualified
> >> > go
> >> > EXEC sp_addlinkedsrvlogin 'MyLinkedServer' -- Server Alias
> >> >    , 'false' -- do not use local credentials
> >> >    , NULL -- do not use one local ID, but rather set the logins for
all
> >> > local IDs as follows
> >> >    , 'LOGIN' -- set login
> >> >    , 'PASSWORD' -- set password
> >> > go
> >> >
> >> > "Amos Soma" <amos_j_s***@yahoo.com> wrote in message
> >> > news:uewePxFyGHA.540@TK2MSFTNGP03.phx.gbl...
> >> >> Hello,
> >> >>
> >> >> In SQL 2005, from a stored procedure in a local database I am
> > attempting
> >> > to
> >> >> execute a remote stored procedure in another database on another
> > server.
> >> >> I
> >> >> am getting the error referred to in the Subject when the local
stored
> >> >> procedure tries to execute the remote stored procedure. A couple of
> >> >> comments:
> >> >>
> >> >>   a.. The remote database is set up as a linked server in the local
> >> >> database. As part of the linked server definition I selected the 'be
> > made
> >> >> using this security context', and provided a local user name and
> >> >> password.
> >> >>   b.. The remote database is set to Trustworthy.
> >> >>   c.. I have tried every combination of WITH Execute As on the
remote
> >> > stored
> >> >> procedure but nothing works.
> >> >>   d.. I can query against the remote database successfully within
> >> > Management
> >> >> Studio. I can even execute the remote stored procedure successfully
> > from
> >> >> within M.S., but not from within my local stored procedure when it
is
> >> >> run.
> >> >> Thank you for your help on this - Amos.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>