Hot answers tagged linked-server
12
The name of your linked server doesn't have to be the server's name. You can use a generic name.
EXEC master.dbo.sp_addlinkedserver
@server = N'COMMONNAME',
@srvproduct=N'MSDASQL',
@provider=N'SQLNCLI',
@provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;',
@catalog=N'database1'
Set up the linked server on ...
8
CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView
Then just use dbo.FooView in your SPs and they can be the same between production and dev.
Note that this is only for SQL 2005 and up.
A solution for SQL 2000 might be to use views that ONLY SELECT * FROM [LinkedServer].Database.dbo.FooView with no other code in them. You could have a ...
7
There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.
Aggregate /Case version:
select personid,
max(case when optionid = 'A' then 1 else 0 end) OptionA,
max(case when optionid = 'B' then 1 else 0 end) OptionB,
...
7
No, you cannot load a 32-bit ODBC driver into the 64-bit SQL Server address space (reference). From your flurry of recent questions, it seems you are having continuing problems accessing a Pervasive database using their 64-bit driver. Have you considered contacting Pervasive support for assistance or an updated driver? You could also ask a question on their ...
6
Linked Servers allow you to connect from SQL Server on an adhoc basis to another datasource, be it SQL Server, Oracle, or something else. Adhoc is the key word, so occasional use is fine. You'll see a lot of negative comments online about performance, hopefully Microsoft will fix in the next SQL Server after Denali.
SSIS is a more robust way of moving and ...
5
I'll just summarize all my comments here as an answer.
You should read this:
http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts
The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since ...
5
I would imagine its either:
EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;
Use this option to protect the actions of a server-to-server procedure
through a Microsoft Distributed Transaction Coordinator (MS DTC)
transaction. When this option is TRUE (or ON) calling a remote stored
...
5
When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check
http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076
4
I wrote something a while back, perhaps this link will help you.
http://www.mssqltips.com/sqlservertip/2017/script-to-check-all-your-linked-server-connections-for-sql-server/
I had a need to verify that the linked servers were working, especially after a server rebuild. And I had the same issues you are seeing with regards to using straight t-sql. So I ...
4
The plan you have at the moment looks like the most optimal plan to me.
I don't agree with the assertion in the other answers that it is sending the 2.6M rows to the remote server.
The plan looks to me as though for each of the 54 rows returned from the remote query it is performing an index seek into your local table to determine whether it is matched or ...
4
No, this is happening because you have a deadlocked resource. What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.
This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim ...
4
Have you had a look at setting up a Server wide DDL trigger? I have used this successfully on several production servers to block unauthorized changes made by developers. I joined a company where ALL developers had full SA access.
When we tried to change their rights we had a full blown revolution as the developers all of a sudden 'could not do their work'. ...
4
I don't think this is empirically deterministic. My understanding is that an entire table could be brought over and processed on the calling server instead of being processed remotely on the linked server. If both servers are SQL Server 2008+ you should see scenarios like this:
SELECT x.foo FROM linked.db.dbo.x JOIN linked.db.dbo.y ON ...
And in these ...
4
I prefer to pivot query manually, but you may use PIVOT as well.
SELECT PersonID,
MAX(CASE WHEN OptionId ='A' THEN 1 END) AS OptionA,
MAX(CASE WHEN OptionId ='B' THEN 1 END) AS OptionB,
MAX(CASE WHEN OptionId ='C' THEN 1 END) AS OptionC
FROM PersonOptions
GROUP BY PersonID
4
This would be the equivalent in SQL Server syntax. Based on my reading of the Oracle docs, NULLIF and PIVOT appear to have the same format as their SQL Server kin. The challenge will be the pivot list which needs to be static unless you make the query dynamic as Itzik demonstrates but I have no idea if that can be translated to P/SQL
WITH ...
3
Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...
Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked ...
3
SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For ...
3
I think you are looking at this from the wrong point of view. You don't want SQL to upate your spreadsheet, you want your spreadsheet to connect to your database and get the latest data.
Create a local DSN -- using "setup data sources (ODBC) -- then connect to that data source from excel.
3
The system catalogs (SYS objects) are stored in the resource DB. The resource DB is usually located at: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, i.e. the same location of the master DB. Yes, this is like the fifth system database.
Check this link on: Moving the master and Resource Databases to move your master and resource DB.
...
3
The error you have is unrelated to logins as such.
This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.
You need "Security Account Delegation" to be configured for the ...
3
I've had this problem before, and while I didn't find a fix (as it appears from the error message, SQL Server seems to 'lose' the brackets when it's processing the query), I did find a couple of workarounds:
Using OPENQUERY will allow you to use the linked server name as-is.
(My preferred solution): create a synonym for the linked server and use that in ...
3
Connecting to a remote resource is expensive. Period.
One of the most expensive operations in any programming environment is network IO (though disk IO tends to dwarf it).
This extends to remote linked servers. The server calling the remote linked server needs to first establish a connection, then a query needs to be executed on the remote server, results ...
3
Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. ...
3
I just reviewed the replication on one of my clients systems and I see similar linked servers both on the publisher and the distributor. In my case the linked servers are all setup using the "Be made using the login's current security context".
If you've got the linked servers setup using a specific security context that's probably because the machines ...
3
Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.
We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):
IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server
Removing the automatic affinity option and setting the affinity ...
3
The way we solved this was to use a template db to auto create views against the access linked tables. Each view was created as
CREATE VIEW AS
SELECT *
FROM LinkedServer...Table
Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.
SELECT v.name, c.name
FROM sys.columns c
JOIN ...
3
Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.
Here is there full response:
...
2
If you are really executing the exact same query, then there shouldn't be much of a difference in executing and returning results. I'd expect SQL server with the linked server to have an edge in the actual lookups into the results.
But this won't mean a thing if the queries you are executing against the sources are different. The differences could go either ...
2
The linked server "lives" in the clustered SQL Server instance, so whichever node the SQL Server happens to be running on, the linked server should run fine....as long as you've installed the required ODBC/OLEDB driver(s) on each node.
Looking at that error, I'm assuming the linked server doesn't work at all on any node. I suspect you'd have the same ...
2
you can make your sql server service run under the username that can access the MsDB file, and take a look at other similar questions on SO1 & SO2
Only top voted, non community-wiki answers of a minimum length are eligible
