I am currently trying to optimize an ETL process that pulls around 150 tables from a OLAP-esque system through a linked server. It's the AllScripts Touchworks EMR database, if that makes a difference. I am having a difficult time fitting an incremental load into a reasonable window of time. For instance, one table, which has around 40 columns, and a total of about 50 million rows, takes around an hour just to pull one day's worth of records when filtering on a single datetime field. The query is:
SELECT *
FROM OPENQUERY([linkedserver],
'
SELECT *
FROM Works.dbo.Visit
WHERE StartDttm BETWEEN ''2014-12-23'' AND ''2014-12-24''
'
)
Which, in the end, returns only about 30k records. However, in trying different approaches, I decided to test out bcp using the following code:
bcp Works.dbo.Visit out f:etlvisit.dat -n -S linkedserver.this extracted the entire table to flat file, but it only took about 15 minutes....add on another 3-4 to load that into the database on my end. Now, my ability to see what's happening on the remote server's end is very limited, but I know that the server has much higher specs than mine. They have 96GB memory vs. my 32GB, twice the cores I've got, etc. So how is this running faster by pulling the entire table over?
I figured that even in the worst case scenario of a full table scan, picking 30k rows out of the table that match a single criteria would certainly run faster than bringing the whole thing over. This isn't isolated to a single table, either. I finished running a test of the incremental extract/load vs. full extract/load via bcp (200GB of data). The incremental takes about 7 hours, bcp takes about 2 hours.
Error message: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA".
I uninstalled and reinstalled the AccessDatabaseEngine - no change.
The provider is displayed, so it's not that.
I verified temp and csv location folder permissions
I tried the -g512 startup option
The csv has a schema.ini as required
It was an 'in place' upgrade, so nothing else has change.
I have a system where people can apply for registration and also a registration fee is involved. The screen where the application is captured has a couple of inserts into different tables. Each of these tables have triggers on them to transfer data to a linked server and they work fine. Today I just added another trigger on a different table but also part of this same save process. Now I am getting the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
The statement has been terminated.
/orisys.int.za/applicants/ApplicantInsert.asp, line 111
I do not know this error and think it might be time-out.
I have created a linked server that point to a firebird database, everything is fine, I can query the database with something like
Select * from MYLINKEDSERVER...MYTABLE
Now , can i use tablediff utility with this linked server. If yes, any example ..
I would say also that performance is not very important to me. this would be used at the end of the day to make some sort of replication by generating a sql script with datadiff, and run it on a local sql server database and a web based SQL database. The replication is not an option since i am using a shared web hosting and replication is disabled on it .
I have a SQL Server 2012 instance set up with a linked server to an Oracle Database. I need to build a stored procedure (single NVARCHAR(6) parameter in: @accountID) which executes a select statement against the Oracle DB.
Everything's working fine when testing with 'EXEC (''<oraclecodeblock>'') at LINKEDSERVERNAME' if I hard code the value of @accountID in <oraclecodeblock> but when I encapsulate it all within sp_executesql and attempt to parameterize @accountID it's throwing ORA-01722 'invalid number' errors on the Oracle DB. The parameterized value is NVARCHAR as are the account numbers.
Working: DECLARE @accountID VARCHAR(6); SET @accountID = '10842'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'EXEC (''SELECT * from A.Accounts WHERE ID = ''''10842'''';'') AT LINKEDSERVER';
i didn't figure out how to add a firebird linked server via ODBC
my system :
win 7 pro 64 SQL server 2012 express 64 firebird 2.5.3 64 Official ODBC drivers 64
i have created the odbc source without problem, and checked that in visual studio and ssms import data wizard , both show me the data from firebird database but when i try to create a linked server , i have something like this message (the image is from the web)
Used linked-server during logon trigger? is it possible? i know we can access tables, SPs, etc with in that server, if i want to check something in other server during logon trigger, can i do it?
We have link-servers all over the place. The ideal naming convention should satisfy following:
- developers should not have to modify code when deploying between environments DEV->TEST->PROD. This rules out using actual server name as linked-name.
- the name should easily identify actual server without much mental translation. This rules out relevant but generic names like FAXSERVER or PAYSERVER
- also, if the name could state that it is a linked-server e.g starting with LINK_, it works when reading the code.
So, I was thinking the link-name should simply remove the environment :
e.g LINK_FAX_DB_V01
that way, on DEV box, the underlying sql data source could point to the dev server, while on test, it could point to test server etc. without having to change code, and also knowing which server it's pointing to by just adding a TST or PRD in front-of it.
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
I'm having a few issues trying to get a linked server setup from a new SQL2012 installation to a Lotus Notes database. This linked server has previously been working fine on our old SQL2005 environment. All setup using the same details (ODBC driver name matches, account details etc). If we test the connection from the Lotus ODBC driver the test connection is successful, If I then create the Linked server to access this ODBC driver I receive an error :
Cannot initialize the data source object OLE DB Provider "MSDASQL" for linked server "LotusTest"
OLE DB provider "MSDASQL" for linked server "LotusTest" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetAttr failed" OLE DB provider "MSDASQL" for linked server "LotusTest" returned message "[Lotus][ODBC Lotus Notes]
Unable to find path to server. check your network connection is working.(Microsoft SQL Server, Error: 7303)We have just migrated to a SQL Server 2012 64 bit Enterprise from SQL Server 2005 32 bit Standard. We have a 64 bit Lotus client installed to provide the correct ODBC driver which has enabled the test connection direct from the ODBC driver to connect successfully. I'm just at a loss as to how to resolve this issue and get the linked server connecting.
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE ' + @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244 Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio from [TranscendDB].[dbo].[CMContactEvents] as CM inner join ( select N.SSN, A.ACCOUNTNUMBER from [DBServer1].[DB1].[dbo].[Account] AS A,
what is the best way to push records using linked server. below is my query on Source server
insert into LSDestserver.DB.dbo.tablename select * from #temp order by abc desc
I initially thought to pull records from Source server to LSDestserver but its a temp table. I don't know whether it is possible or not. Is there any other way we can achieve this in optimal way.
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select * from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.
Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well
Hi I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'abc'. ------ Error Message Ends
Consider login name is abc. Now this login abc has sysadmin rights on sql server 2005. The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options; 1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 . 2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help. Regards, Salman Shehbaz.
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "". Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".