INSERT OPENQUERY With XML When Remote Server Involved

Mar 13, 2008



Hi Guys,

We already have work around when it comes to read xml data type from remote servers
remote server (eurodata3) table

create table temp_asaf

(


xmlCol XML

)


SELECT


Cast(a.XML_Data as XML) as XML_Data

FROM

OPENQUERY(eurodata3,'

SELECT TOP 10

Cast(xmlCol as Varchar(MAX)) as XML_Data

FROM

em_port.dbo.temp_asaf'

) a


Here is a question for you. How do I insert data into remote server when data type for a column is xml?


INSERT OPENQUERY (eurodata3, '


SELECT Cast(xmlCol as Varchar(MAX)) AS xmlCol

FROM em_port.dbo.temp_asaf')

VALUES ('<html><body>MS Sql Server</body></html>');



Running above code would give me the following error:

OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.

Location: memilb.cpp:1493

Expression: (*ppilb)->m_cRef == 0

SPID: 59

Process ID: 1660




Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.

View 3 Replies


ADVERTISEMENT

Do I Need To Use Openquery To Run A Backup On A Remote SQl Server

Dec 19, 2007



I would like to backup a database on a remote SQL 2005 server using T-SQL. The local server I want to issue the command from is also a SQL 2005 Server.

Do I need to use the openquery function?
I am doing this as a job step so I will be executing the query from a local server.
I do not want to use a SSIS package.

Thanks

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

Update Remote Table With Openquery

Apr 16, 2008

Hello,

I have an application that uses a MS SQL 2005 database. When data is changed in certain tables, that data needs to be pushed to a MySQL box. I've added the MySQL server as a linked server in SQL 2k5 and I can delete and insert data with no problem, however when I try to update I get the following error (query included):





Code Snippet

with RemoteTable(r_AccountID, r_Name)
as (select AccountID, Name from openquery(RACS_TEST, 'select AccountID, Name from accounts'))
update RemoteTable
set r_Name = ex_Name
from Export_RACS_Accounts join remotetable
on r_AccountID = ex_AccountID















OLE DB provider "MSDASQL" for linked server "RACS_TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 33
The OLE DB provider "MSDASQL" for linked server "RACS_TEST" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

What am I doing wrong ?

View 2 Replies View Related

Openquery Insert With Additional Columns For Manual Insert.

Apr 24, 2008



I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.

The query below works well because the table's columns are even in both databases:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')


I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.

My insert statement above no longer works because the column numbers don't match.

In a nutshell I would like a way to combine the following queries:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')

Insert into Webcases (insurancetype) Values ('SF')


--insurancetype is the new column.

View 9 Replies View Related

Need Help With OPENQUERY And Insert Statement

Nov 7, 2006

I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.

Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')

The error that I get is the LA1 column cant be found? any help thanks

View 4 Replies View Related

Perform Insert Statement With Openquery?

Aug 29, 2013

Import Hadoop Data into Analysis Services Tabular | Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.

I was wondering how can i also perform an insert statement with openquery?

I understand that for example to insert into Hive is to use a Load command, like:

Code:
load data inpath '/tmp/data.csv' overwrite into table tableA;

How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors

Code:
insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')

View 1 Replies View Related

Need Help Creating Statement Using OPENQUERY AND INSERT

Nov 7, 2006

I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.



Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')

View 6 Replies View Related

BULK INSERT FROM REMOTE SERVER

Nov 7, 2001

I am running the following:
BULK INSERT DB.dbo.[stblCLIENT]
FROM 'SERVER1downloadClient.txt'
WITH
(
FIELDTERMINATOR = 'Ø',
ROWTERMINATOR = ''
)
DB.dbo.[stblCLIENT] is on SERVER2. I receive the following error:
"Could not bulk insert because file 'SERVER1downloadClient.txt' could not be opened. Operating system error code 53(The network path was not found.)."

I am able to run a DTS package that imports the same text file from SERVER1 with no error.

Is BULK INSERT limited to importing text files from the server on which SQL Server is running or should I be able to BULK INSERT from another server on my LAN?

View 1 Replies View Related

Transact SQL :: Bulk Insert From Remote Server?

Nov 20, 2015

SQL Server 2012

I want to be able to run the following command from SSMS (as an ad-hoc query).

BULK INSERT Database_Name.dbo.Table_Name FROM 'serverfile.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', MAXERRORS = 0);

When I do I get:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "serverfile.txt" could not be opened. Operating system error code 5(Access is denied.).

I have full access to the file.I can do the same command successfully if the file is stored on a local drive on the server.

According to my DBA I can not run it with a remote file location because I don't have the SA permission. His solution is for me to create a job that runs the command. I have done so and the job works correctly.

Is he correct that there is no way for me to be able to run it from SSMS without SA permissions?

View 5 Replies View Related

How To Read A Remote CSV File Into SQL SERVER Using Bulk Insert Command..

Mar 24, 2008

Hi,
I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system.
Please help me.....

View 1 Replies View Related

Bulk Insert Into Views That Select From Table On Remote Server

Jul 20, 2005

Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal

View 7 Replies View Related

SQL Server 2008 :: Trigger Fire On Each Inserted Row To Insert Same Record Into Remote Table

Sep 9, 2015

I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET

i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.

The problem is when i call the stored procedure from trigger, i get an error message.

Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

When i try to insert a new description value in the table i got the following error message:

No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".

correct the errors entry or press ESC to cancel the change(s).

View 9 Replies View Related

Using Linked Server And OPENQUERY

Oct 19, 2007

I need to run a report using a linked server on SQL 2005. The report was running really slow so I tried doing an OPENQUERY which makes it a lot faster. However, I need to pass some parameters and not sure how to do it using OPENQUERY. Here is the query:

SELECT RTRIM(client.ClientID) AS ClientID, client.name2 AS Client_Name, Project.Project, Project.StatutoryDueDate AS DueDate, Project.Extension1, Project.Extension2, Project.StartDate AS AsofDate, Project.PromiseDate AS CommitmentDate, Project.ReceivedDate AS InfoIn, Task.TaskID, Task.Empid, Task.ActualStartDate, Task.ActualFinishDate, Client.Partner,
STAFF.PersonalTitle AS PIC,
Client.Manager,
MGR.PersonalTitle AS TIC,
Client.Accountant,
ACCT.PersonalTitle AS AIC,
CASE WHEN Task.TaskID = 'PREP' THEN EMP.personaltitle END AS Prep_BY,
CASE WHEN Task.TaskID = 'REV' THEN EMP.personaltitle END AS Rev_By,
CASE WHEN Task.TaskID = 'PREP' THEN Task.ActualStartDate END AS Prep_Date,
CASE WHEN Task.TaskID = 'REV' THEN Task.ActualStartDate END AS Rev_Date,
CASE WHEN Task.TaskID = 'MAIL' THEN Task.ActualFinishDate END AS Mailed_Date,
CASE WHEN Task.TaskID = 'TESNT' THEN Task.ActualFinishDate END AS TE_OUT,
CASE WHEN Task.TaskID = 'TERCD' THEN Task.ActualFinishDate END AS TE_IN
FROM PROJECT, PROJCUS, TASK LEFT OUTER JOIN STAFF EMP ON TASK.EMPID = EMP.EMPID, CLIENT LEFT OUTER JOIN STAFF ON CLIENT.Partner = STAFF.EmpID LEFT OUTER JOIN STAFF MGR ON CLIENT.Manager = MGR.EmpID LEFT OUTER JOIN STAFF ACCT ON CLIENT.Accountant = ACCT.EMPID
WHERE CLIENT.ClientID = PROJECT.ClientID AND CLIENT.Engagement = PROJECT.Engagement AND PROJECT.Project = PROJCUS.Project AND
PROJECT.Engagement = PROJCUS.Engagement AND PROJECT.ClientID = PROJCUS.ClientID AND CLIENT.ClientID = TASK.ClientID AND CLIENT.Engagement = TASK.Engagement AND PROJECT.Project = TASK.Project AND CLIENT.DroppedDate IS NULL AND ((PROJCUS.[~Custom35])='BT') AND (CLIENT.OfficeID in (@OfficeID)) AND (PROJECT.Project in (@Project))

View 1 Replies View Related

Run Openquery(mdx) Through A Linked Server

Nov 15, 2006

Hi,

I run openquery() from a client application(sql 2005) to query SSAS data(sql 2005) through a linked server(sql 2005), but I get the following error:

OLE DB provider "MSOLAP" for linked server "LINKEDMINING" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "LINKEDMINING" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Procedure gettpdt, Line 3
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "LINKEDMINING".

And, I am sure that I have made the MSOLAP provider Allow inprocess.

What can i do for this. Please advise.

View 11 Replies View Related

RPC Via Linked Server - Openquery?

May 19, 2008

The following used to work before we had to chang the Linked Server Name (Out of my control)


select * from

openquery(LServ1,'SET FMTONLY OFF; exec [LServ1].database1.dbo.proc1 @issue_id=1,@extract_type=1')



Now the Linked Server Name changed from LServ1 ==> LServ1.DOMAINMSSQLSERVER,1200
(1200-Port #)

Now when i change the Linked Server Name, It gives me an error @ openquery.
I tried the following and so far no luck

select * from

openquery(LServ1.DOMAINMSSQLSERVER,1200,'SET FMTONLY OFF; exec [LServ1].database1.dbo.proc1 @issue_id=1,@extract_type=1')
and
select * from
openquery([LServ1.DOMAINMSSQLSERVER,1200],'SET FMTONLY OFF; exec [LServ1].database1.dbo.proc1 @issue_id=1,@extract_type=1')

Basically how do i specify the NEW Linked Server Name(as a paramter for "openquery"?)

Thanks!

View 3 Replies View Related

Linked Server, Openquery, Oracle

Jan 20, 2000

Can someone please tell me what oracle software needs to be installed on a sql server machine that will link to an Oracle database on an NT4 machine.

Also can you also confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I have read about people having prolems executing stored procedures with arguments using the OPENQUERY Function.

Thanks

View 1 Replies View Related

TCP/UDP Ports Used For Index Server OpenQuery

Nov 4, 2005

If I am doing an Index Server query from SQL, such as:SELECTQ.*FROMOPENQUERY(FTIndexPM, ''SELECT path, characterization, rank, hitcountFROM SCOPE('DEEP TRAVERSAL OF .....can anyone tell me which TCP/UDP ports will be used between the SQLServer and the Index server if the Index Server is on another machine?I'm doing the query from SQL so that I can join the results with atable in the database and am not interested in doing the Index queryfrom the app server. I haven't been able to find any info on the netfor which firewall ports are used for this.

View 5 Replies View Related

OpenQuery Not Working After Applyin SQL Server SP4

Nov 3, 2006

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

View 1 Replies View Related

OPENQUERY Vs EXECUTE On A Linked Server -- What Is Better?

May 22, 2007

Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server 2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't been able to find a comparison between the two.



Have there been any tests of the difference?



What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?



To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.



The new option with EXECUTE would be something like the example in MSDN (Example J.) at:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx


EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO

View 2 Replies View Related

Problem With OPENQUERY And Linked Server

Mar 4, 2008

I have a simple SQL statement that runs in a web-based admin system as follows:

INSERT INTO someTable SELECT * FROM OPENQUERY (someLinkedServer, 'SELECT someTable.* FROM someTable WHERE ID = 57')

This works fine on my development system, but on the production system I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "someDB" returned message "Unspecified error".

The linked server definitely exists on the production machine; and the following stored procedure has been run:
exec sp_addlinkedsrvlogin 'someLinkedServer', false, 'machinenameIUSR_macninename', 'Admin', NULLto allow the internet anonymous user account to log into the linked server.

Also:
-- the internet anonymous user has full control permissions over the Access database file
-- the internet anonymous user has full control permissions the administrator/local settings/temp directory (after I read somewhere that this could cause problems; though no such permission is needed to make it work on my development machine.)

As far as I can tell, the production machine (2003 Server) is configured the same way in all relevant respects as the development machine (2000 Server) where everything works.

With such a vague error message, I have no idea where to start, and would appreciate any advice.

View 2 Replies View Related

FYI - Oracle Linked Server - OpenQuery Deletes

Aug 7, 2000

I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:

DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id

select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring

EXEC sp_executesql @sqlstring

*Note...I had an additional data conversion from varchar to integer in my statement.

I hope this information helps any others who are having trouble performing this type of task.

View 2 Replies View Related

OPENQUERY Fails To Work With SP On Linked Server

Oct 4, 2007

Hello.
Suddenly OPENQUERY had started to raise an exception 'Msg 7355, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "sql2000" supplied inconsistent metadata for a column. The name was changed at execution time.'
The OPENQUERY stament looks like:


Code Block
SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')





I don't know what has been changed - seems that everyting is OK.
The problem is that sql2000 is MS SQL 2000 server and this statment is executed on the MS SQL 2005 server.
dbo.sc_List is a procedure that selects data and I need insert that data to the temporary table on the sql2005 server to make some calculations.
I've used OPENQUERY to prevent DTC coordinator from starting distributed transaction.
Whole statement originally looks like



Code Block
INSERT INTO #Templates SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')




where #Template is temporary table with columns that exactly match with stored procedure output.
Help me please - what is wrong with sql servers? I know that this query has been working fine for a months and now - such strange errors. I should say also that strored procedure returns always the same columns when called - there is one large select inside it.
Thank you.


s w

View 4 Replies View Related

Error Running Openquery(mdx) Through A Linked Server

Feb 26, 2007

I'm trying to create linked server to access DMX functions from SQL Server as per:

Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

I create the link this query

EXEC master.dbo.sp_addlinkedserver

@server = N'KLSSQL01AnalysisServerLink',

@srvproduct=N'Analysis Services 2005',

@provider=N'MSOLAP',

@datasrc=N'kls-sql01',

@catalog=N'AnalysisServicesPredictorPrototype'

GO

SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')

where [Misue Abuse Profile] is the Mining model

Provider options: Allow in process

I receive the follwing error:

OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink".
I found this post but there was no resolution.

run openquery(mdx) through a linked server



http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924869&SiteID=1

View 2 Replies View Related

Remote Insert

Jun 22, 2006

I have an issue i need to understand and have not been able to find an answer yet. It may be something to do with parametised query execution but i`m not sure yet. Below is the scenario

If i do a

insert into server.db.dbo.remotetable
select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the destination server where as if the insert into references a local table only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert?

To test this i've supplied some very simple code. Just create source tbl on local server and destination and log tbl on remote server. Setup a linked server and run the remote insert then do a local insert and look at the log.

All advise gratefully received!

Cheers


Andrew


CREATE TABLE [dbo].[source] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO


--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO


--Insert some sample data into the source table
insert into source values ('MYSERVER','1/1/2000')
insert into source values ('MYSERVER','1/2/2000')
insert into source values ('MYSERVER','1/3/2000')

--Run the insert from the source db

insert into destinationsrv.destdb.dbo.destination
select * from source

--Switch to the destination server and run select there is only meant to be 1 row!
select * from tbllog

View 3 Replies View Related

Remote Access Server Configuration Option And Remote Query Timeout?

Jun 2, 2015

- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?

- what is the impact of changing the remote query timeout (on the same page) from default value?

View 4 Replies View Related

FoxPro Linked Server And Passing Variable Within OPENQUERY

Mar 8, 2005

I'm posting this because I found this solution after much digging.

The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.

First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:

http://support.microsoft.com/default.aspx?scid=kb;en-us;314520

Here's code for a solution:

DECLARE @OPENQUERY nvarchar(4000),
@TSQL nvarchar(4000),
@FAMILY CHAR(10)

SET @FAMILY='Touring'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''

SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'

EXEC (@OPENQUERY+@TSQL)

All shown are single quotes.

In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters

In addition, if wanting to build a dynamic where clause, you could do something like:

SET @TSQL = 'select cov,family,model from vinmast '
IF <some condition met to include FAMILY filter>
Begin
SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']'''
SET @TSQL=@TSQL+ ')'
End
-----------------
Here's the entire Stored Procedure:


CREATE PROCEDURE dbo.ewo_sp_DUTLookup
(
@DUTPROJECT char(25)=NULL,--Project
@DUTFAMILY char(10)=NULL,--Family
@DUTMODEL char(20)=NULL,--Model
@DUTYEAR char(4)=NULL,--Model Year
@DUTBEGIN char(25)=NULL,--Beginning of COV/DUT number
@DEBUG int=0
)


AS

DECLARE @OPENQUERY varchar(4000),
@TSQL varchar(4000),
@TWHERE varchar(4000),
@intErrorCode int

select @intErrorCode = @@ERROR,
@TSQL='',
@TWHERE=''


IF @intErrorCode=0
Begin
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast '
End

set @intErrorCode = @@ERROR

IF @intErrorCode = 0 and
@DUTFAMILY is not NULL or
@DUTMODEL is not NULL or
@DUTPROJECT is not NULL or
@DUTYEAR is not NULL or
@DUTBEGIN is not NULL
set @TWHERE=' where '



-- Check for Family criteria
If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND '
set @intErrorCode = @@ERROR

-- Check for Model criteria
If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND '
set @intErrorCode = @@ERROR

--Check for Project criteria
If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND '
set @intErrorCode = @@ERROR

--Check for Model Year
If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND '
set @intErrorCode = @@ERROR

--Check for beginning of DUT
If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0
Begin
SET @DUTBEGIN=RTRIM(@DUTBEGIN)
SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND '
End
set @intErrorCode = @@ERROR


IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND '
Begin
set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3)
select @intErrorCode=@@ERROR
End



SET @TWHERE=@TWHERE+''')'

IF @debug<>0 and @intErrorCode=0
Begin
print @intErrorCode
print @OPENQUERY
print @TSQL
print @TWHERE
print @OPENQUERY+@TSQL+@TWHERE
End

IF @intErrorCode=0
EXEC (@OPENQUERY+@TSQL+@TWHERE)
GO

Peter

View 2 Replies View Related

Linked Server Error... Openquery() Locking Tables

Feb 1, 2008



Hi All -

My Set up:

Server A - Oracle 10g Database
Server B - SQL2005
Client PC - Sql Express

Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).

When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:

Command attempted:
{call sp_MSreleasemakegenerationapplock}
Error messages:
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)

I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!

View 1 Replies View Related

Get Tables Involved In Operation

Jun 17, 2014

Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.

View 2 Replies View Related

Restoring A Db That Was Involved In Replication

Mar 24, 2004

Hi all

I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.

My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error

"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."

Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?

Matt

View 10 Replies View Related

Views With Parameters Involved

Apr 2, 2008

Hi,

I have done a stored procedure but happend to know that people calling my database cannot access procedures only views. But my select query requires to parameters (two dates) in order to get the correct data.

How can I solve the problem?

View 6 Replies View Related

How To Get Tables Involved In Constraint

Jan 18, 2006

Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you

View 2 Replies View Related

Newbie: Why Do I Have To Use OPENQUERY To A Linked Server? (ODBC To Firebird Database)

Jan 17, 2008

Hello,

pls. let me know where I could post if this is the wrong place.

I have a Firebird 1.5 application. I created a linked server from my SQL
Server 2000 to the firebird database. In SQL Server Query Analyzer I get errors from various ODBC drivers with "normal" queryies like

SELECT LVNR FROM LINKEDSRV...LVVERW

Pls. note, this all works perfectly in MS Access databases with ODBC-Links to Firebird!


From a programmer of a commercial ODBC driver I heard that this problem may be caused internally by SQL Server, there may be no solution possible in the ODBC driver. One workaround would be to use the OPENQUERY-Syntax like

SELECT * FROM OPENQUERY(LINKEDSRV, 'select LVNR from LVVERW ')

Are there any other solutions? Are there any known issues with firebird odbc-drivers and sql server? Are
there any known good drivers for the use with sql-server? What is the purpose of OPENQUERY - workaround ODBC problems? Are there any settings in SQL Server 2000 (2005 Express) that could help? Are there any settings in ODBC DSN that would help?

regards

arno


PS: Here are my favorite error messages

Error -2147217900 [OLE/DB provider returned message: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 89
"Col1014"] (Source: Microsoft OLE DB Provider for SQL Server) (SQL State:
01000) (NativeError: 7312)Error -2147217900 OLE DB-Fehlertrace [OLE/DB
Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ].
(Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000)
(NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' meldete
einen Fehler. (Source: Microsoft OLE DB Provider for SQL Server) (SQL
State: 42000) (NativeError: 7399)

This "tricky" query does not work:
SELECT LVNR FROM LINKEDSRV...LVVERW;

Error -2147217900 OLE DB-Fehlertrace [Non-interface error: Column
'ERHALTENABSCHLAG' (compile-time ordinal 35) of object 'LVVERW' was reported
to have a DBTYPE of 5 at compile time and 131 at run time]. (Source:
Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError:
7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' hat inkonsistente
Metadaten für eine Spalte übergeben. Die Metadateninformationen wurden zur
Ausführungszeit geändert. (Source: Microsoft OLE DB Provider for SQL
Server) (SQL State: 42000) (NativeError: 7356)

View 2 Replies View Related







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