T-SQL (SS2K8) :: Errors When Inserting Rows From A Linked Server
Aug 14, 2015
I have a script that inserts rows from a linked server. It basically looks like this:
use mydb;
go
insert into my.table (col1, col2, ...)
select col1, col2, ...
from LinkedServer.db.my.table;
The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).
Running my query raises an error:
Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.
Three pairs of eyes have confirmed that the table definitions are identical on both source and target.
I've got a linked server to an attomix database. When i send an erroneous statements to the server an error message is returned to the Message pane is SSMS as below:-
OLE DB provider "MSDASQL" for linked server "noble" returned message "[Noble Systems Corp.][ATOMIX ODBC Driver]Atomix error - (-217)Column (crap) not found in any table in the query.
".Msg 7215, Level 17, State 1, Line 5, Could not execute statement on remote server 'noble'.
I'm trying to store this error using the ERROR_MESSAGE() function but i only get the second statement ('Could not execute statement on remote server 'noble'.') rather than the real error in the first line. The first line statement is displayed in black as a message rather than red for an error.
I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.
My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.
JobRequirements (A) JobID int QualificationTypeID int
EmployeeQualifications (B) EmployeeID int QualificationTypeID int
Employee (C) EmployeeID int EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
I have a local server with a linked server configured which contains the live data. The local server will run the stored procedure on a scheduled job to harvest the data from the linked server.
BEGIN TRAN GO INSERT INTO local_server...local_table SELECT * FROM linked_server...linked_table AS lnk WHERE NOT EXISTS (SELECT * FROM local_server...local_table AS loc WHERE loc.key = lnk.key) GO COMMIT TRAN GO
I have read there are some pitfalls to moving data across linked servers.
Will I have any problems with this type of transfer and if so, are there any alternatives?
Hi,I'm using sql server 2000 sp4.I've 2 databases linked, an instance and my local.I'm getting two different errors when trying to update the remote table(local server) from the instance.There is only one row of data in the table with an identity field.1st sql:-UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3This gives me the error:-Server: Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '1'.If I was to remove the space from [f 1] and use [f1] it would workfine."select [f 1] from [dev001].[fashion Master].dbo.numbers"will return the correct valueAny Ideas ?2nd sql:-UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1This gives me the error:-Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"fashion Master"."dbo"."NUMBERS"' from OLE DBprovider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. No workwas done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...If I was to remove the isnull part, then it will work okAny ideas
I am getting a linked server connectivity errors randomly. We have a set of DTS packages to pull data from OLTP (SQL 2000) to reporting server (SQL 2005) through linked server and both instances are on same server. The process is running ok on Acceptance server, and failing on production server. It is throwing the following error randomly, and it is getting fixed for a while after restarting SQL 2000 service.
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available. ". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "OLE DB provider "SQLNCLI" for linked server "GIS_STG" returned message "Communication link failure".". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".
What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?
I have an ODBC connection (System DSN) configured for an Informix ODBC driver. The Test button (belonging to this driver-setup) reports a successfull connection test. Getting data from this database by linking tables in MS Access works fast and easy. But I have tried for many days now to setup a linked server from SQL Server (2005) Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery') I get the following error: ---
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".
---
Why is Access able to read what "MSDASQL" cannot? I am desperate - can anybody help?Thanks a lot!
We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes. I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.
Here's what is happening: When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:
[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.
I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.
We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.
There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:
Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!
Interesting issue. I have the following CTE that JOINs some tables from a Linked Server which is our SAP data. This CTE is in a stored procedure and then executed via a SQL Server Agent Job on a timer (every 10 minutes). This ran fine for almost 20 hours and then dies with a multipart identifier could not be bound error (exact error below CTE).
Server running the job: SQL Server 2008 R2 (no SP)
Linked Server: SQL Server 2005 SP3 housing SAP
CTE:
WITH TaktValues ([Counter], NODE, PLNNR) AS ( SELECT MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR FROM etl.PLPO plpo1 GROUP BY plpo1.PLNNR
[Code] ....
Error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1008.AUFNR" could not be bound.
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1008.AUFNR" could not be bound.
I work in Healthcare IS for Company A, but Company B is hosting one of our EMR programs for us. This was done on purpose, so that whether a patient is seen at one or the other, their medical history is more complete. However, this puts all of the data that I need to get to on a server across town that I can only access via Sql Server Management Studio as a linked server.
Now, in some ways, the performance has been better than I expected, but sometimes it behaves very erratically. I am using OPENQUERY to handle all of the pulls, and am not joining to any local tables, in order to maximize efficiency.
Here is some of the code I run, and what happens:
SELECT * FROM OPENQUERY([linkedservername], ' SELECT * FROM Encounter_ItemChild
[Code] ....
***The above query was programmatically generated by taking the IDs from the second query, and packing as many into the IN condition as possible. Each statement could hold only about 900 IDs, so around 70 queries get built...however, each one returns the records in question in 1-2 seconds.
My main question is...if the second query pulls all IDs from Encounter in a few seconds, and that query is used in the first query's WHERE clause, why does it spin and spin, while manually throwing the IDs in instead runs almost instantly?
I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).
The code for creating the table is here:- USE [CDSExtractInpatients6.2] GO /****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/ SET ANSI_NULLS ON GO
[code]...
I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.
I am having troubles trying to copy some rows from a table on my local computer to a table on a remote SQL Server 2005 that is being hosted by one of thos web hosting companies. The problem is that the table has an identity column. I first tried using the the following command:
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
but that results in the error:
Msg 8103, Level 16, State 1, Line 1 Table 'remoteservername.Library2005.dbo.tblLanguages' does not exist or cannot be opened for SET operation.
I read in another topic, that I should change this into the following:
That command executes without error, but the problem is that I cannot perform the actual insert, because it is not within the execute statement. In other words, the following doesn't work:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON' INSERT INTO [remoteservername].Library2005.dbo.tblLanguages (colLangID, colEnglish, colGerman, colSpanish) SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2 OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[remoteservername].[Library2005].[dbo].[tblLanguages]' because of column 'colLangID'. The user did not have permission to write to the column.
The remote server is linked correctly on my end via the sp_addlinkedserver and sp_addlinkedsrvlogin. Is there any way to force the remote server to turn IDENTITY_INSERT ON permanently and then let me execute as many INSERTS as I want and then turn it back OFF?
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from ''' set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote' set @params = ''' with (firstrow = 2 , datafiletype = ''widechar'' , fieldterminator = '';'' , rowterminator = '' '' , codepage = ''1252'' , keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2 select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB] GO /****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Why on earth is this not working, It WILL insert if there are NO apostheres (" ' "), but if I say like, "Cedric's Group" It will error out. I thought this approach was suppose to handle this character? Thanks for any insight, String sqlStr = "Insert into cardgroups (username,groupname,insertdt,groupid) values (@username,@groupname,@insertdt,@groupid)";
Hi, I have two mssql databases. I am able to create a linked server. I want to delete some of the rows using this linked server.
Here HAFEEZ is one mssql database and DPVSQSL is another.So, i want to delete rows from DPVSQL from HAFEEZ using HAFEEZDPVSQL linkedserver.
DELETE OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');
when i run the query i got the following error.
"Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" supplied inconsistent metadata for a column. The column "EVENT_SYS_ID" (compile-time ordinal 1) of object "SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time."
Please note that i am able to query something using this linked server,please see the below query.
SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');
When i try to insert into a datetime field i keep getting this error.
Msg 242, Level 16, State 3, Procedure Sproc_01220_Tour_PendingPayment, Line 27 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.
(1 row(s) affected)
i am passing to the date parameters the date in this format dd/mm/yyyy
Any ideas
ALTER PROCEDURE [dbo].[Sproc_01220_Tour_PendingPayment]
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
I had been frequently encountering these errors when I run my page: "Error converting data type nvarchar to int.Cannot insert the value NULL into column 'COMPANY_CODE', table 'ZSWAREHOUSE.dbo.ZS_COMPANY_TBL'; column does not allow nulls. INSERT fails.The statement has been terminated.". How do I solve this, so that I can insert data in my company table?
In migrating our back end database from VFP8 to SQL we use linked servers to do a variety of tasks as well as some customized reporting applications. We are having issues with setting up the linked servers in SQL 2000 to VFP8 tables.
When you set up a linked server, locally it seems to work ok, sometimes we have authentication problems and have to switch back and forth between Windows and SQL which is annoying but manageable.
However when you start using the same SQL instance from other boxes by adding it in Enterprise Manager errors occur.
Error 7303: Could not initialize data source object of OLE DB provider 'vfpoledb.1'.
OLD DB Trace Error [OLE/DB Provider 'vfpoledb.1' IDBInitialize::Initialize returned 0x80040e21].
I have downloaded and installed the VFP8 OLE DB .dll from the Microsoft website but still have this issue. Can anyone shed any light?
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table 2) Update the parent Id in the table 3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[JSets]( [SetsID] [uniqueidentifier] NOT NULL, [Sets_Id] bigint Identity (1,1) NOT NULL,
I need to insert FK information into a temp table using database name and table name as a parameter.
I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.
IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs CREATE TABLE ##FKs ( [ForeginKeyName] [nvarchar](128) NULL, [TableSchema] [sysname] NULL, [TableName] [nvarchar](128) NULL, [RelatedTableSchema] [sysname] NULL,
I execute a script that someone else wrote and I get Query Completed with Errors but there is not an error message.If I highlight and execute parts of the script, it completes successfully.
I'm using a "Execute SQL Task" to run a stored procedure to populate a Fact table in our dimensional datawarehouse. There is one row which violates a foreign key constraint, which causes the entire task to fail so zero rows are loaded. Is there any way to grab the offending row and send it off to some holding ground and go ahead and load the rest of the rows.
I'm using Execute SQL Task mostly because I am very comfortable with writing SQL whereas the rest of SSIS is a bit unfamiliar to me, but I'm guessing that to handle error rows I might have to change to a different kind of task ?
I am performing analysis of linked servers across 2000-2008R2 and need to find/build a list of linked servers that are truly active. For the sake of the post let's define 'active' have executed a distributed query in the last 5 days.
I have been scanning the DMVs without much success. Perhaps I must look more closely at MSDTC?
The end result would be to cleanup 300+ linked servers across 40+ SQL Servers.
