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


ADVERTISEMENT

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

Linked Server..... Can I Use A File DSN?

Apr 1, 2008

Linked Server File DSN?

Can anyone help? I am trying to connect a Domino Server with the NotesSQL ODBC.
I was unable to set the ODBC up as a System DSN ('The setup routines for the
Lotus NotesSQL Driver (*.nsf) ODBC driver could not be loaded due to system error
code 126') and had to set it up as a file DSN.


Can I still set up a Linked Server to it? I keep getting errors:

'7399 Invalid Connection String attribute]
(not sure what I was supposed to enter in the 'Product String' dialog box)
Datasource name not found and no default driver specified]....'


The file DSN was setup OK. I know this because I can use it as a connection in
DTS and successfully import various tables (although it does not work with the
import wizard?)


Does anyone know if I can use the file DSN to setup a linked server?

If so, what do I put in the 'provider string' and 'catelog' dialogs.

Any help much appreciated. wyatt.anderson@nwmhp.nhs.uk

View 2 Replies View Related

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

Excel File As A Linked Server

Apr 25, 2000

I am trying to link an excel file as a linked server. I tried two methods unsuccessfully.

first: using

EXEC sp_addlinkedserver 'Ex',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:list5.xls',
NULL,
'Excel 5.0'
GO

When I run
EXEC sp_tables_ex Ex, I get the error below.

Server: Msg 7303, Level 16, State 2, Procedure sp_tables_ex, Line 20
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]


Second: I link the excel file using ODBC datasource:

EXEC sp_tables_ex mylink, works fine with mylink being the linked server name. I get the table names eg. table1$, table2$.... However when I try to query the tables, I get this error:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


thanks
FS

View 1 Replies View Related

Create A Linked Server To MPP File

Apr 13, 2007

I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.

But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server

select * from linktompp.testmpp.dbo.tasks

such that linktompp is the linked server name
testmpp is the catalog name
tasks is the table name i need to select data from

it shows an error that says

"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."

"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"
could anyone tell me the reason of this error

thanks




Mostafa Salama
M.Sc. in Computer Science

View 1 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 Server 2014 :: Linked Server To File Folder Security

Dec 8, 2013

Here is my Problem:

1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]

All this works fine. I can even put it in to an After Insert Trigger on another table and it works.

My problem is that I need this to work in a scheduled job.

F:Data is just a folder with files in it.

This info is from a Restaurant POS system and I need to update it every night.

I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.

View 4 Replies View Related

Linked Server For Mdb File On A Shared Network Drive

Jan 6, 2000

Hi

I am having an Access database on a shared network drive which has read/write access rights on the that shared network drive.
When I try to Access data through the linked server it gives me gives me a message box saying you do not have permissions to view the data.
Also if i try to use xp_cmdshell to copy over the mdb file to my local drive it say 'Access denied'

But when I copy (through command prompt) the same file to another network drive or my local drive where I have full control the linked server can connect sucessfully.

The problem is the i cannot have 'full control' permissions on shared drive where my database resides.

Has anybody encountered this problem....

Any help will be greatly appreciated.

Urgent
Puru

View 1 Replies View Related

How To Create Linked Server To Access 2007 Accdb File?

Feb 20, 2008

How does one create a linked server to an Access 2007 (accdb) file? I am aware that to create one to a mdb file, the code would be -EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:MSOfficeAccessSamplesNorthwind.mdb'
GO
What is the corresponding code for Access 2007 accdb files?

View 18 Replies View Related

Cannot Initialize The Data Source.. Text File Linked Server

Sep 7, 2007

Hi All,

I have this very big text file (2.5 GB - which of course I am not able to open). This resides on a server which has SQL 2005.

I have created a linked server for this text file on my SQL database, and when I try to query the table, I get an error:

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "txtsrv".

Why am I getting this error? When I make a dummy linked server on my local machine and test the steps it all works OK. but when I go to this server where the file and it's SQL instance is lying, I get this error. Please can someone provide me with some help on this?

Many thanks,
~S

P.S. Here is how I created the linked server (I also have the Schema.ini lying under the same folder):


EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:SSAnalysis',

NULL,

'Text'

GO

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

SQL 2012 :: Retrieve Binary File From Server

Apr 14, 2014

I have been trying to store binary file in a folder from the SQL Server.

Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.

I have already configured server using

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Database Log File Is Full?

Jul 10, 2014

I am receiving the below message however when going into my database properties and going into 'File' it's set as either unrestricted growth for the log files or 2097152MB limit and the log files are only taking up about 3gigs.

Could not allocate new page for database. There are no more pages available in the file group.

Database log file is full. Back up the transaction log for the database to free up some log space.

Could not allocate space for ojbect in database because the filegroup is full.

how I can resolve this and give it more space?

View 1 Replies View Related







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