Tag Info

Hot answers tagged

13

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 ...


13

Clearly there isn't a nested CASE expression here. Not in the query text, no. But the parser always expands CASE expressions to the nested form: SELECT CASE SUBSTRING(p.Name, 1, 1) WHEN 'a' THEN '1' WHEN 'b' THEN '2' WHEN 'c' THEN '3' WHEN 'd' THEN '4' WHEN 'e' THEN '5' WHEN 'f' THEN '6' ...


9

Suppose I have to export data from one server to another. Best is to use IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance. Executing ...


9

Use a synonym, and just don't sync the synonyms. So when it is on a local server: CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName; When it is remote: CREATE SYNONYM dbo.FooBar FOR ServerName.DBName.dbo.TableName; Now all your code just has to reference dbo.FooBar no matter where it is.


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 ...


8

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, ...


8

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

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 ...


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

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 ...


5

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


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

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. ...


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


5

My hunch is that the query is getting re-written somewhere along the way to have a slightly different CASE structure, e.g. CASE WHEN column = 'a' THEN '1' ELSE CASE WHEN column = 'b' THEN '2' ELSE ... I believe this is a bug in whatever linked server provider you're using (in fact perhaps all of them - I've seen it reported against several). I also ...


5

Some objects are allowed to be created to facilitate Deferred Name Resolution. The assumption is that if you create a procedure that references dbo.MissingProcedure, you will create dbo.MissingProcedure some time between the moment you create the procedure, and the first time you execute it. Another situation that works is a table that doesn't exist yet. I ...


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

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 ...


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

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

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

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 ...


4

How are you measuring efficiency? Which one will be faster? Which one will consume fewer resources on the target? on the source? How many rows and what kind of data types are the columns in these rows? Are you sure you can execute a TVF through a linked server (is the target SQL 2008 or later?)? How are you ensuring a 1:1 migration of this data if you're ...


4

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right? SET NOCOUNT ON; DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE()); INSERT dbo.ANNOUNCEMENTS(column list) SELECT column list FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS] AS r WHERE CREATION_DATE >= @Yesterday ...


4

You are probably seeing an error like: Msg 7357, Level 16, State 1, Line xx Cannot process the object "<query text>". The OLE DB provider "<provider>" for linked server "<server>" indicates that either the object has no columns or the current user does not have permissions on that object. This occurs when SQL Server tries to discover ...


4

Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another. http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx


4

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk ...


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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible