August 6, 2010 at 4:16 am
SQL 2005 standard edition
Hi
I have 2 instances on my SQL Server - Instance1 and Instance2 and I have created linked servers to each instance i.e. on Instance1 I have create a linked server to Instance2 and vice versa.
On each linked server I have ticked the option 'For a login not defined in the list above, connections will...Be made using the login's current security context'.
I have a stored procedure in a database on Instance1 which inserts data from tables on Instance1 into a table in a database on Instance2 and this works fine when I run the stored procedure from a new query window on Instance2 using 'Exec [Instance1].[DBname].dbo.[SPname]'. The code in my SP has the correct prefixes to tables.
When I put this exec SP command into a step within a scheduled job in SQL Server agent (on Instance1) the job reports successful execution however no data is transferred and my destination table remains empty.
I am running the Agent job as myself using my Windows login and I am system admin on the Instance1 and Instance2.
I prefer to push the data from Instance1 and use the SQL Agent on Instance1 as all our scheduled jobs run from there and I don't want to activate Agent on Instance2 unless absolutely necessary.
I feel this is something to do with SQL Agent permissions between my two instances - can anyone advise please?
Thanks L
August 6, 2010 at 5:46 am
1) On each linked server I have ticked the option 'For a login not defined in the list above, connections will...Be made using the login's current security context'
The login defined here as right permissions on the destination database?
2) Are you able to execute the SP manually?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 6, 2010 at 6:26 am
Why don't you run the sproc locally ?
(have it push the data to your linked server using insert into linkedserver.linkeddb.linkedschema.linkedtable)
In general I'm not fond of using linked servers for regular/normal operations.
If you need to push data from here to eternity, try to do it very performant, using the right tool.
(SSIS / Service broker)
If you can do it asynchronously.
Service broker may be your help.
This can be done using SQLAgent with SSIS linked on a SQLAgent alert condition (because I don't like having sp_start_job in regular code).
Maybe even have a look at the replication alternatives SQLServer offers.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 6, 2010 at 8:44 am
Yes I can run the SP locally and I am sysadmin on both servers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply