October 30, 2009 at 11:28 am
igngua (10/30/2009)
Elliott W (10/30/2009)
Until you have it all working you might just use the GUI and then trace a session as you recreate it to find out all the commands for scripting later.
Go to the GUI and send screen prints of the second and third tabs, they shouldn't contain any data that wouldn't be safe for the internet. You may want to blur our the server name using paint. I suspect they contain all default values..
wich GUI are we talking about ??
In SSMS if you have the server up in the object explorer and expanded you will see "Server Objects" expand that. You will see "Linked Servers", expand that. What you should see is "Providers" and any linked servers you have defined. Right click on the linked server with the instance name listed and select properties. That is the GUI..
CEWII
October 30, 2009 at 12:41 pm
Elliott W (10/30/2009)
igngua (10/30/2009)
Elliott W (10/30/2009)
Until you have it all working you might just use the GUI and then trace a session as you recreate it to find out all the commands for scripting later.
Go to the GUI and send screen prints of the second and third tabs, they shouldn't contain any data that wouldn't be safe for the internet. You may want to blur our the server name using paint. I suspect they contain all default values..
wich GUI are we talking about ??
In SSMS if you have the server up in the object explorer and expanded you will see "Server Objects" expand that. You will see "Linked Servers", expand that. What you should see is "Providers" and any linked servers you have defined. Right click on the linked server with the instance name listed and select properties. That is the GUI..
CEWII
Ok this is going to be funny. I´m from chile so the screens will be with SSMS in spanish.
are this se SS you meant ?
October 30, 2009 at 1:10 pm
While my spanish is EXTREMELY rusty, the good thing is that only the names have changed, not their meaning..
What kind of security are you using? SQL Loging or trusted connectivity? If it is SQL does your login name exist on both servers with the same password?
For now you might choose the bottom radio button and select a user that you know is on the other server and enter its password. Then you can be fairly sure that security is not involved..
That third tab doesn't look too bad.. Later on you might want to set connection and query timeouts.
I have to admit I would have expected more security related errors..
CEWII
October 30, 2009 at 1:39 pm
What kind of security are you using? SQL Loging or trusted connectivity?
SQL loging
If it is SQL does your login name exist on both servers with the same password?
No it doesnt.
But in the tab where i specify the user for the linked server i typed in the users that have permissions.
For now you might choose the bottom radio button and select a user that you know is on the other server and enter its password. Then you can be fairly sure that security is not involved..
i did that and it didnt work.
October 30, 2009 at 1:41 pm
i checked FW and AV and it still doesnt work.
October 30, 2009 at 2:07 pm
it finally worked!!!!!
I did everything again...and it seems some user config was missing.
sp_addlinkedserver '<Server Alias>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL
sp_addlinkedsrvlogin '<Server Alias>', 'false', NULL, '<username>', '<password>'
the trigger was created succefully.
The new issue is that the triggers doesnt allow to get data written succefully on the main table. i get this message.
the 2660 row data were not confirmed.
source of the error:. Net SqlClient Data Provider.
error message: MSDTC not available on server 'development'
October 30, 2009 at 2:24 pm
igngua (10/30/2009)
it finally worked!!!!!I did everything again...and it seems some user config was missing.
sp_addlinkedserver '<Server Alias>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL
sp_addlinkedsrvlogin '<Server Alias>', 'false', NULL, '<username>', '<password>'
the trigger was created succefully.
The new issue is that the triggers doesnt allow to get data written succefully on the main table. i get this message.
the 2660 row data were not confirmed.
source of the error:. Net SqlClient Data Provider.
error message: MSDTC not available on server 'development'
This is why it was suggested that you NOT use a trigger for this. There are too many things that can go wrong that will cause your original insert to fail. Check services to make sure that MSDTC is started on BOTH SQL Servers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 30, 2009 at 2:30 pm
Jack Corbett (10/30/2009)
igngua (10/30/2009)
it finally worked!!!!!I did everything again...and it seems some user config was missing.
sp_addlinkedserver '<Server Alias>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL
sp_addlinkedsrvlogin '<Server Alias>', 'false', NULL, '<username>', '<password>'
the trigger was created succefully.
The new issue is that the triggers doesnt allow to get data written succefully on the main table. i get this message.
the 2660 row data were not confirmed.
source of the error:. Net SqlClient Data Provider.
error message: MSDTC not available on server 'development'
This is why it was suggested that you NOT use a trigger for this. There are too many things that can go wrong that will cause your original insert to fail. Check services to make sure that MSDTC is started on BOTH SQL Servers.
I´ve your advice present.
This topic is for learning purposes.
So far i´ve learned a lot.
I´m working on development DB nothing on production.
i will check MSDTC.
October 30, 2009 at 3:16 pm
the service is initiated in both servers.
it doesnt work.
im looking in
ControlPanel->AdministrativeTools->Component Services->Computers->My Computer->Properties
November 2, 2009 at 7:18 am
hi!.
I rebooted both services and servers.
But now i have a new error.
"Transaction context in use by another session"
I´ve read about it and it seems that setting MARS true would solve my new problem.
Any idea where do i have to set ,
"MultipleActiveResultSets=True"
November 7, 2009 at 7:19 am
igngua (10/30/2009)
This topic is for learning purposes.
So far i´ve learned a lot.
I'm hoping that you've learned that this is not the best way to do this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply
