SQL 2012 :: ETL Through Linked Server

Dec 28, 2014

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.

View 9 Replies


ADVERTISEMENT

SQL 2012 :: Linked Server To CSV File

Aug 5, 2014

I upgraded my 2008 R2 instance to 2012 and the Linked Server I use for aconnect to a csv file stopped working.

Create script:

EXEC sp_addlinkedserver @server ='Stats_CA',
@srvproduct='',
@provider ='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:RLAPPSStatsStats_CA',
@provstr='Text'

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.

View 6 Replies View Related

SQL 2012 :: Triggers With Linked Server

Nov 10, 2014

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.

View 9 Replies View Related

SQL 2012 :: Tablediff With Linked Server

Jan 26, 2015

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 .

View 2 Replies View Related

SQL 2012 :: Oracle Linked Server

Jun 8, 2015

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

[code]...

View 0 Replies View Related

SQL 2012 :: Create Linked Server For Firebird?

Jan 26, 2015

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)

View 4 Replies View Related

SQL 2012 :: Using Linked-server During (logon Trigger)

Oct 8, 2015

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?

View 0 Replies View Related

SQL 2012 :: Linked Server Naming Convention?

Nov 6, 2015

How do you name sql linked-servers in your company ?

we have many legacy systems (random naming), but mostly newer systems follow this pattern

<ENVIRONMENT><APPLICATION><FUNCTION><VIRTUAL><NUMBER>

e.g.

PRD-FAX-DB-V01, TST-PAY-WEB-P02 etc.

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.

View 0 Replies View Related

SQL Server 2012 :: How To Insert Data Into Table From Linked Server

Nov 19, 2013

I wonder if it possible to move data from tables on a linked server to a "normal database"?

Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName

Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country

This is just a test setup. I figure that if I get this working the rest will be easier.

My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')

View 8 Replies View Related

SQL 2012 :: Selecting From Linked DB2 Server / Specific Column

May 13, 2014

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.

View 0 Replies View Related

SQL 2012 :: Can't Access Linked Server When Connecting Via Alias

May 27, 2014

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.

View 9 Replies View Related

SQL 2012 :: Linked Server And Remote Login Password

Oct 7, 2014

Is there any way to find out the password for the remote login of the Linked server

View 1 Replies View Related

SQL 2012 :: Linked Server To Lotus Notes Database

Nov 24, 2014

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.

View 0 Replies View Related

SQL 2012 :: Adding Column To A Table Over Linked Server

Apr 22, 2015

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

<hr noshade size='1' width='250' color='#BBC8E5'>

View 1 Replies View Related

SQL 2012 :: How To Run Transaction Across Multiple Instances Without Linked Server

May 18, 2015

i want to run a transaction across mulitpule instences of sqlserver with out linked server.

distributed trnasaction can do it with link server , can it do it with out linked server.

View 9 Replies View Related

SQL 2012 :: Not Exists Linked Server Object Query

Aug 10, 2015

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,

[Code] ......

View 4 Replies View Related

SQL Server 2012 :: Push Records Using Linked Server

Apr 16, 2015

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.

View 9 Replies View Related

SQL 2012 :: Create Linked Server With Failover Partner Option?

Jul 22, 2014

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.

EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',

[code].....

View 2 Replies View Related

SQL 2012 :: Allow Authenticated User To Query Excel File Via Linked Server?

May 25, 2014

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.

Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0

View 9 Replies View Related

SQL Server 2012 :: Parameter Datatypes Linked To Underlying Table Column Datatype

Jun 5, 2014

In Oracle when i create any procedure i define parameter datatype linked to under lying table.

For ex
create procedure testprocedure
(param1 customer.name%type,
param2 customer.salary%type )

Here i have defined param1 and param2 with datatype of name and salary of customer table respectively.

This way i do not need to worry about modifying param1 and param2 datatypes when datatypes of name & salary of customer table changes in future.

How can i accomplish this in SQL server.

View 2 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

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?

View 1 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

Mar 25, 2002

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

Thanks

View 5 Replies View Related

DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server

Apr 24, 2015

I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.

I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.

How to point the linked server to a specific database? How to rename the Linked Server?

The following is the code that I am using right now:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'Machine123Instance456',
    @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'  

View 6 Replies View Related

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

Jul 18, 2006

Is there a way to bypass the syntax checking when adding a stored procedure via a script?

I have a script that has a LINKed server reference (see below) .

INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.

ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.

PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.

Thanks,

Terry

View 4 Replies View Related

SQL 2012 :: AlwaysON Linked Servers Failover

Jan 27, 2015

So I have 2 servers S1 & S2.

Database Group 1 = L1 with Primary S1 and Secondary S2
Database Group 2 = L2 with Primary S2 and Secondary S1

For 99% of the time the 2 groups of databases are not related. For the 1 procedure that does move data from L1 to L2 something like

Update L2.DB.Owner.Table
set flag = 1
Whare a = 0

On S1 I have a linkedServer with connection to L2.

If I have a failover I cannot have L2 on S2 as they are essentially the same server.

How to I use the 2 groups hand in hand.

View 0 Replies View Related

SQL 2012 :: Can SSIS And Database Engine Be Linked To SSAS

Feb 12, 2014

I have a server which has SQL Server 2012 SSAS.

My client wants SSIS and database engine to be installed to be mapped to this analysis services engine.

Is that possible?

View 3 Replies View Related

SQL 2012 :: Linked Servers Connecting To High Availability Group

Dec 16, 2014

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

View 1 Replies View Related

Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005

May 4, 2007

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.

View 3 Replies View Related

Error :Execute Trigger From Remote Server To Another Server By Linked Server

Jul 22, 2007

i did "Linked server" between To Servers , and it's Working.

---------------------------



For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2



and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS


BEGIN





SET NOCOUNT ON;


insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND

**************************************************



result is :

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.





how i can execute this trigger



View 5 Replies View Related

Connect From A SQL Server 2005 Db To A SQL Server 2000 Db, Without Linked Server Connection

Apr 18, 2007

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

View 1 Replies View Related

Instructions For Setting Up A Sybase ASE Server As A SQL Server 2005 Linked Server?

Dec 28, 2005

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:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'

</code>

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

</code>

Any ideas what is happening here?

View 10 Replies View Related

T-SQL (SS2K8) :: OPENQUERY Syntax To Insert Into Server Table From Oracle Linked Server

Aug 28, 2014

I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.

View 7 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

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

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved