We’re rewarding the question askers & reputations are being recalculated! Read more.
29

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


24

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' WHEN 'g'...


23

Here's a quick & dirty approach that only needs a linked server, in each direction, with sufficient privileges, collation compatibility, and data access enabled. You run this on the source linked server to generate the dynamic SQL that will be executed on the destination linked server. DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'SELECT * INTO [...


22

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation. The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem. Your Windows Account must have access to ...


21

You could use SQL Server Management Studio's "Export Data" task. Right click on the database in SSMS, select Tasks and then "Export Data". It will launch an interactive wizard that will allow to copy tables from one server to another, although you will have to recreate the indices yourself. The wizard creates a temporary SSIS package (although you can also ...


20

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


19

Turns out that I was able to get it working with different parameters. EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'', @provider=N'SQLNCLI', @provstr=N'DRIVER={SQL Server};Server=(local)\InstanceName; Initial Catalog=DBNAME;uid=user;pwd=password;'


19

You can use BCP OUT and BULK INSERT INTO the destination database. You can use this script You can use SSIS (data Import/export) to do the data transfer as well.


19

If you want something that can be scripted to be easily re-runnable as you test this and make minor changes, check out my answer here: Import data from one Database to another script That answer describes using a SQLCLR stored procedure which makes use of the SqlBulkCopy class in .NET. Using that stored procedure can be done in a cursor that loops through ...


14

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


14

How transparent will it be to the application code? How much change should I expect? Not transparent at all. Expect major changes. You should be prepared for very substantial performance degradation. Distributed Query (the framework for linked servers) uses a general OLEDB model whatever the server on the other end happens to be. It is true that a SQL ...


13

Linked servers can work very well as long as you have thought out the implications: Security: a key consideration is that if you have linked servers, if one gets compromised they are all at significant risk. Even if you have different credentials for each user different servers (which would stop an attacker getting at other resources if the only attack ...


13

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good idea regardless). However, now that I can reproduce the error, it ...


12

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows: DECLARE @myStatement VARCHAR(MAX) SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine that's longer than 8000 characters EXECUTE (@myStatement) AT LinkedServerName In order to make sure this doesn't throw an error, you need to enable the RPC OUT ...


11

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.


11

Instead of dealing with linked server references inside your code, you might want to consider a one-time code investment involving the use of a synonym in any location where currently you have a linked server. So instead of: SELECT whatever FROM someserver.somedb.dbo.mytable; You have a synonym: CREATE SYNONYM dbo.mytablepointer FOR someserver.somedb.dbo....


11

Some slightly shorter (and more natural, IMHO) approaches: EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;'; Or: EXEC('SELECT @@VERSION;') AT LinkedServer; I prefer the sp_executesql route because: It will still allow for strongly-typed parameters, instead of dealing with all kinds of string concatenation, escaping of single quotes, etc....


10

One thing you can try is to use sp_testlinkedserver. You can also issue the OPENQUERY using dynamic SQL (as Max correctly pointed out), to defer the parser validating the server name until runtime. BEGIN TRY EXEC sp_testlinkedserver N'server1'; EXEC sp_executesql N'SELECT * FROM OPENQUERY([server1], ''SELECT 1 AS c;'');'; END TRY BEGIN CATCH ...


10

I have just had the same problem yesterday, and here is how I was able solve it: Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio), the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there. After I added the key to the registry, I was able ...


9

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


9

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


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


8

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


8

Dealing with Active Directory is always so much fun. The single most important thing here is to realize you are dealing with distributed data that can take time to propagate across your network. The SQL Servers in question had their name changed as part of an upgrade procedure; we replaced an existing machine (SQL01) running SQL Server 2005 with a new ...


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


8

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


8

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


8

I've managed to get it working: I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server: GO EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain'; After adding the ...


8

Determine if "Disallow adhoc access" is enabled for your provider. This can be found in SQL Management Studio via the following navigation path: Server Objects/Linked Servers/Providers/Microsoft.ACE.OLEDB.12.0 Right click the "Microsoft.ACE.OLEDB.12.0" provider and select "Properties" from the context menu. In the pop-up window, make sure that the "Disallow ...


8

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


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