Tag Info

Hot answers tagged

16

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


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


12

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


10

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


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

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


7

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


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


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

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


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


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

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


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

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

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

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

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


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

Every machine in the chain from your desktop 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 both ServerA and ServerB. You ...


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

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

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


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

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

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

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



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