Hot answers tagged

22

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


19

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


18

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


17

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.


17

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


17

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


16

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


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


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


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

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

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

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


7

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


7

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


7

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


6

The SQL Server Analysis Services drivers aren't actually installed as a part of the normal SQL Server installation unless you install SSAS on the server. You can download the MSOLAP driver from the SQL Server feature pack and install it. Then you can create the linked server. Don't forget to install the 64bit install if you have a 64bit SQL Server or the ...


6

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


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


6

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


6

This is a bug, though one that only arises in very specific circumstances. SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote ...


6

I am not sure exactly how you have determined that all of the data is going across the wire (or why that should be the case) but you could always have a computed column: ALTER TABLE dbo.SavedImage ADD PictureSize AS CAST(DATALENGTH([Image]) AS BIGINT); Or just add a column and calculate that yourself whenever you insert/update a row, either by forcing ...


5

This approach works fine for me: SELECT * FROM [linked_server_name].master.sys.master_files; What you need to be sure of is that the linked server is running in the context of a user who has enough privileges to see the contents of the DMV. You can check who you're executing as using: EXEC [linked server name].master.sys.sp_executesql N'SELECT ...


5

Why is SQL Server required to do this task? This isn't the objective of an RDBMS. You would have to go through hoops to get SQL Server to find this data. The quickest/easiest/most natural way to do this? The Active Directory PowerShell module. It's a one-liner: Get-ADGroupMember "administrators" | Select-Object Name, DistinguishedName, ObjectClass In ...


5

I can reproduce that same behavior on SQL Server 2012. However, I am able to get it to work by putting the Linked Server name back into the query. Is there a reason you moved it out into the AT clause? Try the following: SET FMTONLY ON EXEC('SELECT TOP(10) [MCMCU] ,[MCSTYL] ,[MCDC] FROM ...


5

You use the collate operator in the join. Such as: select <column list> from dbo.firsttable f join linkedserver.database.dbo.secondtable d on f.name collate database_default = d.name collate database_default Of course, you can also specify a specific collation, such as: SQL_Latin1_General_CP1_CI_AI


5

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


5

Be careful when using SQLNCLI10 as described in connect item A workaround can be found here You have to first test the application that is using such SP's from 2012 to 2000. It also depends on where both server's are located and how much data you are pulling out using Linked Servers. Is there a reason that the other referenced databases cannot be moved ...


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



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