| Author |
Topic |
|
Kuda
Starting Member
17 Posts |
 Posted - 2004-02-11 : 14:18:52
|
Oh wonderful SQL Gurus of the SQLTeam Forums, I beg of thee some assistance in getting a linked server set up in SQL2K  Here's the situation: We have an application that uses its own OLE DB provider to access the SQL database (for logging and application security purposes). I need to set up some triggers on the underlying SQL tables, but need to have all writes to the database go through the provider. So... I though that a linked server would be a great option to let me pass trigger actions through the provider. The problem is, I can't figure out how to set it up. I have an OLEDB connection string that I use when hitting it from ADO, but I'm not sure how to break it up into the parts needed for a linked server definition. Here is the connection string (split into 2 lines):Provider=SLXNetwork.1;Data Source={databasename};User ID={username};Password={password};Extended Properties="Address={servername};Port=1706;Type=ODBC;Log=ON"When defining the linked server (through Enterprise Manager), I chose the appropriate provider under Server Type. I then filled in the appropriate values for Data Source and Provider. Under the Security tab I chose "Connections will be made under this security context" and filled in the appropriate username and password. My problem is that the Extended Properties in the connection string MUST be passed, but I have no idea how to do that. Can anyone shed some light on this? Many thanks in advance!==========================================================But who can save the sane? Some beings just can't change... |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-11 : 15:50:24
|
I'm assuming you're linking to another sql server?EXEC sp_addlinkedserver @server='LinkName', @srvproduct='', @provider='SQLOLEDB', @datasrc='Server\Instance'EXEC sp_addlinkedsrvlogin 'LinkName', 'false', NULL, 'Login', 'pwd' Brett8-) |
 |
|
|
Kuda
Starting Member
17 Posts |
Posted - 2004-02-11 : 16:03:46
|
| Because of the way the application I'm dealing with works, all changes to the database have to go through the application's custom OLE DB provider, otherwise the application breaks, meaning I can't use "normal" triggers and/or stored procs. My theory was that a way to get around this would be to create a linked server (using the app's OLE DB provider) and have any triggers/stored procs use the linked server to do add/update/delete queries, thus allowing me to use the triggers I need to set up and also preventing the app from breaking :)So in short, yes, I'm linking to another SQL server (actually the same SQL server), however a straight SQL link won't work.==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-11 : 16:46:04
|
| Did you run the code I posted?Also are you talking about running the procedures remotley?Brett8-) |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2004-02-11 : 17:27:34
|
| Yes I tried the code you posted, however the problem is that it creates a link to a SQL server, not to an OLEDB provider which is what I need. I don't plan on running any procedures remotely. What I DO need to do, however, is have a way to execute SQL through the OLEDB provider from Query Analyzer/triggers/etc. so that they are logged properly in the application. For example, say I have a table called ACCOUNT, that has three fields: ACCOUNTID, ACCOUNANAME, AND FIRSTCHARACTER. I want to set up a trigger on the table so that whenever ACCOUNTNAME changes, FIRSTCHARACTER gets updated with the first letter from ACCOUNTNAME. The problem is that I can't just write a trigger that does an update of the FIRSTCHARACTER field, because the application won't see it and log it properly, causing problems with the application. Instead, I want to be able to write a trigger that, when ACCOUNTNAME changes, it updates the FIRSTCHARACTER field through the OLEDB provider. The only way I can think of doing this is by setting up a linked server that uses that provider.Give the connection string I provided earlier, how do I set that linked server up?==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2004-02-12 : 14:43:46
|
| Here's another question: Is it possible to set up a linked server based on a .UDL file? How would I go about doing that?==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2004-02-13 : 11:17:47
|
Well, I've tried just about every way I can think of to get the linked server to work, but regardless of what info I put in (this is mostly through EM by the way, but I did try using the sp_addlinkedserver through QA just to make sure), I get a 7399 error when I try to view the table list on the linked server through EM, and the table list won't display. I am also unable to run any queries against the linked server through QA, and OPENROWSET doesn't work either. This tells me that it's gotta be a problem with the configuration of the linked server itself, but I can't figure out what I'm doing wrong  ==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
safigi
Starting Member
15 Posts |
Posted - 2004-02-17 : 06:38:24
|
| First a recommend Microsoft OLE DB Provider for ODBC because failure tolerance is better then the Microsoft OLE DB Provider for SQL Server. But it's real that the ODBC add a new layer that is why slower then OLEDB. So you can use sp_linkedserver like this:EXEC master.dbo.sp_addlinkedserver @server = 'your linked server name', @srvproduct = '', @provider = 'MSDASQL', @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=sapwd;'safi |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2004-02-17 : 08:57:33
|
| Well after much trial and error, I finally found some settings that worked. I created a .UDL file with a connecion to the OLEDB provider I wanted (not the OLEDB Provider for SQL Server) and configured/tested it. In the linked server, I put the appropriate name in for the Data Source, then for Provider String I copied over the info from the Extended Properties of the .UDL file. It's all working great now.==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
smummert
Starting Member
2 Posts |
Posted - 2005-05-11 : 11:01:04
|
| Kuda:I am running into the SAME problem attempting to configure the SalesLogix provider as a linked server. I created a UDL file but do not know what to do with the settings in the linked server area.Do you configure using the SLX provider and paste the connection string in the Proviers String (tried that and connect fails)Tried referecning the File Name=myslxudl but did no work either as a provider string. What foes in the data source? The SLX Server, the SLX database name or Local Hhost?Thanks!Scott |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2005-05-11 : 11:17:19
|
| Scott,Unfortunately I gave up on this idea shortly after my last post (mid-February 2004). While I was **finally** able to get the linked server set up, it suffered from some severe stability problems - sometimes it worked, sometimes it didn't, sometimes it crashed the machine, sometimes it hung the SQL server.... This was all using SalesLogix 6.0 and 6.1. I've been on sabbatical for a while and haven't had a chance to work much with SLX 6.2, so I don't know if the new version is more stable in that area (they were supposed to totally rewrite the OLEDB provider so it's more of a "normal" provider). Sorry!==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
smummert
Starting Member
2 Posts |
Posted - 2005-05-16 : 12:29:12
|
| Kuda:Could you give me some further informaion on what ***finally*** worked. I want to see if this is a potential solution before investing time in COM components.Thanksscott.mummert@tigroup-usa.com |
|
|
|
Kuda
Starting Member
17 Posts |
Posted - 2005-05-16 : 14:01:29
|
Scott,Ok here's what I did... however I have to warn you that this is a VERY unstable solution, and I can't/won't be held responsible for any server hosing or data wonking that may will happen if you use this in anything even remotely resembling a production environment  1. Create a blank text file and change its extension from .txt to .udl, then double-click on it.2. On the Provider tab, select "SalesLogix OLE DB Provider"3. On the Connection tab, select the appropriate SLX server and database, fill in a SLX username and password, and make sure "Allow Saving Password" is checked. Go to the Advanced tab and make sure that Port Number is correct, that you have the correct RO/RW password (if these are set in your environment) and that Enable Logging is checked. Go back to the Connection tab, hit Test Connection and make sure it succeeds. Switch to the All tab and leave the window open...4. In Enterprise Manager, expand your server, go to Security, then Linked Servers. Right-click and choose New Server.5. Give the server a name, then under Server Type select Other Data Source and pick [SalesLogix - OLE DB Provider].6. Click on Provider Options and make sure that "Allow InProcess" is the only option checked.7. Back to the General tab: Leave Product Name blank (or use "SalesLogix" without the quotes). Enter the SQL database name for Data Source (the actual SQL database name, not the SalesLogix alias). Under Provider string, enter:"Address=SALESLOGIX_SERVER_NAME;" without the quotes.8. Copy the value of the Extended Properties field on the All tab of the UDL file you created earlier and paste it in after the Address entry in Provider String in the Linked Server Properties. Close the UDL file if you so desire.9. Go to the Security tab and select "Be made using this security context" and enter the SalesLogix username and password you used in the UDL file.At this point you should, theoretically, have a functional linked server that you can run queries against in Query Analyzer using OPENROWSET or somesuch. You shouldn't have too many problems (aside from HORRIBLY LONG query times) running simple SELECT queries against the linked server, but I can almost guarantee you that either your local machine or your SQL server will crash if you try to run an INSERT or UPDATE query. Again, this is all information based on SLX 6.1 - your mileage may vary if you are using a different version.Hope that helps... it's all the information I have :)John (aka Kuda)==========================================================But who can save the sane? Some beings just can't change... |
|
|
|
|
Linked Server Newbie Question