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


10

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


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


7

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


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


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

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


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

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

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


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

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

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

Several possible reasons for this. Lack of data distribution statistics. It looks like you have this one covered in your post, but i'll leave it for completeness. If the linked server login does not have sufficient privileges to obtain all useful statistics on CRM, a suboptimal plan will be used. *If the linked server is an instance of SQL Server, to ...



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