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


ADVERTISEMENT

SQL 2012 :: Tablediff And Nvarchar (max)

Oct 9, 2014

Using MSSQL Server 2012 Standard on a Windows 2008R2 Standard server.

For the tablediff utility MSDN says:

The Transact-SQL script generated to bring the destination table into convergence does not include the following data types: varchar(max), nvarchar(max), timestamp...etc

We have a small replication job running and one of the tables contains a timestamp field. After running tablediff the generated script contains a note:

-- Column(s) AccessTS are not included in this script because they are of type(s) text, ntext, varchar(max), nvarchar(max), varbinary(max), image, timestamp, or xml. Columns of these types cannot be updated by tablediff utility scripts; .....

The updated record is in the script file, but the timestamp field is omitted.

All good so far.

However, another table contains nvarchar(max) fields. The scripts that are generated for these tables do not contain the above message and instead contain these nvarchar(max) fields in both the update and insert into statements!

So why does it say it will ignore these types of fields, but then actually include the nvarchar(max) ones in the generated scripts?

View 2 Replies View Related

Tablediff Utility For SQL Server 2000?

Dec 18, 2006

Does anyone know if there is a utility available in SQL Server 2000 equivalent to the Tablediff utility in 2005? While reinitializing a Subscriber, I got the error "The row was not found at the Subscriber when applying the replicated command." I would like to identify the location of the data to fix at the Subscriber. In the interim, I changed the Distribution agent from the default to "Continue replication on data consistency errors," which successfully replicated changes, except for the errors. Is the stored procedure sp_publication_validation the only option for SQL Server 2000? This ran successfully after the replicated changes were processed.

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

Tablediff Bug?

Sep 12, 2006

Hey,

I recognized a strange behaviour when using tablediff. Because I don't want to replicate all columns, I create two views on selected attributes and compare them.

Have two tables:
create table t1 (timestamp timestamp, b int PRIMARY KEY, c varchar(30))create table t2 (b int PRIMARY KEY, c varchar(30))

and two views:
create view vt1 as select b, c from t1 create view vt2 as select b, c from t2

Compare the views with tablediff and get a failure:
... vt1 and vt2 ... have different schemas and cannot be compared.

Now I just find out that it works when table t2 also has a timestamp column.

drop table t2create table t2 (b int PRIMARY KEY, c varchar(30), ts timestamp)

Compare the views again with tablediff and get:
... vt1 and vt2 ... are identical.

What dou you think, do I always need the same datatypes in source- and target-table I want to compare, even if a create a subselection via views?

Cheers,

View 5 Replies View Related

Tablediff Utility Bug?!?

Aug 14, 2006

In my transactional push replication I had differences, which were reported correctly by the utility tbalediff.

I created with the tablediff utility all necessary statements to correct them.

Afterwards I ran tablediff again to check if everything is ok.

It wasn't, I got a lot of mismatches.



The reason for those mismatches are columns from type "float". It looks like that float values are not generated correctly in insert statements. We had differences in the values and from that point on we have mismatches.



Does anyone has an idea how to get rid of those reported mismatches?!?!

I will report this also as a bug.

Regards

Norbert

View 11 Replies View Related

TableDiff Utility

Apr 9, 2007

What SQL Server 2005 feature do I need to install to get the tablediff.exe utility installed?



Thanks.

View 1 Replies View Related

TableDiff Utility

Apr 9, 2007

What feature do I have to install in SQL Server 2005 to get the tablediff.exe installed?



Thans.

View 3 Replies View Related

TableDiff Utility

Apr 9, 2007

What SQL Server 2005 feature do I need to install to get the TableDiff utility? I have the developer edition.



Thanks.

View 1 Replies View Related

TableDiff Stopped Functioning

Jul 31, 2007



Hi,

TableDiff Stop Functioning on 2million records with 1.8milion changes on a particular row. Only happen if "-f" is specified. Anyway to work around this?

Regards,
Ben

View 1 Replies View Related

Tablediff Utility Download

Nov 12, 2007

I want to use Tablediff utility. It has not been installed into my system. Any idea, from where I can download it? I have both sql 2000 and sql 2005. May I use tablediff in both sql 2000 and 2005?

View 6 Replies View Related

Tablediff,sp_publication_validation,sp_article_validation

Jul 11, 2007

I'm trying to put together an automated system that uses sp_publication_validation & the tablediff utility to automatically sync up our subscribers to the publisher. What I'm noticing is that the sp_publication_validation sys proc gets run on the publisher. It does a rowcount and binary_checksum of each article in a specified publication. When I do a binary_checksum myself I've verified these results. For some reason a column could be a smalldatetime on the publisher and all the subscribers, however, the binary checksum will be different IF RUN from the publisher for that column/row/table. If I run the binary_checksum from the subscriber (instead of accross ODBC using a linked server command) I'm seeing matching checksums.



The result of this is I'm seeing a bunch of records that are actually identical coming through the tablediff command as different. I have a few questions. Has anyone ever run accross this issue before? I believe it has something to do w/ ODBC. I'm willing to work/share a pretty nice SSIS package that I am developing if somebody could assist me in figuring a way around this issue.





Thanks,

Phil

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

Tablediff Utility Without 'delete' Command.

Apr 23, 2008



I am presently using 'tablediff' utility of SQLServer 2005 to compare tables located on two different servers. My real intension is to backup data from Serv1 to Serv2, except that I wish to update the tables on server2 with only newly inserted rows and updated rows and NOT deleted rows.

In short, I want the final .sql file generated by the tablediff utility to contain only 'Insert' and 'Update' statements and NOT 'Delete' statements.

I hope someone can help me in this regard.

Thank you,
Little_Birdie.

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







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