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
ADVERTISEMENT
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
May 13, 2008
I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.
DECLARE @BuildClrVersionx nvarchar(128)
SET @BuildClrVersionx =
(SELECT *
FROM OPENQUERY(LKMSSQLXYZ01, 'CONVERT(nvarchar(128),SERVERPROPERTY("BuildClrVersion")'))
I am getting the following errors:
OLE DB provider "SQLNCLI" for linked server "LKMSSQLADM01" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
If you have any ideas how I can run this query across a linked server I would appreciate it.
Thanks,
Scott
View 8 Replies
View Related
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
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
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
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
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
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
Aug 21, 2007
We have a MS SQL database with an Oracle linked server 'ALTTEST'
We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
View 5 Replies
View Related
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
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
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
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
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
Aug 17, 2015
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13
Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
View 2 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 1 Replies
View Related
Feb 1, 2005
Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.
I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.
I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:
Provider Name: Pervasive.SQL V8 OLE DB Provider
Product Name: Pervasive.SQL V8
Data source: DemoData
Provider string: <blank>
Location: workstation03
Catalog: <blank>
However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.
Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].
How would I fix this?
Thank you.
View 1 Replies
View Related
Feb 27, 2007
Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.
I'm generally following theTips and Tricks article.
"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.
e.g.
SELECT * FROM OPENQUERY(DMServer,
'select €¦ FROM Modell PREDICTION JOIN OPENQUERY€¦')
The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query
SELECT * FROM OPENQUERY(DMServer,
'SELECT
t.[CardTransactionID],
t.[PostingDate],
[Misuse Abuse Profile].[Even Dollar Purchase],
PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,
PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,
t.[BillingAmount]
FROM
[Misuse Abuse Profile]
PREDICTION JOIN
OPENQUERY([Athena Dev],
''SELECT
[CardTransactionID],
[PostingDate],
[BillingAmount],
[AccountNumber],
[SupplierStateProvinceCode],
[MerchantCategoryCode],
[PurchaseIDFormat],
[TransactionTime],
[TaxAmountIncludedCode],
[Tax2AmountIncludedCode],
[OrderTypeCode],
[MemoPostFlag],
[EvenDollarPurchase]
FROM
[dbo].[vMisuseAbuseProfile]
'') AS t
ON
[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND
[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND
[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND
[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND
[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND
[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND
[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND
[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND
[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND
[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]
')
In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".
View 4 Replies
View Related
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
Aug 9, 2001
Running SQL2000 on Win2000 server. Installed Oracle8 client from Oracle for Winnt CD. This appeared to install OK could use SQL*PLUS to link to Oracle servers. Setting up a Linked server and then doing an openquery
gave an error:
' msadora reported an error
ole/db returned a message Oracle client & networking component were not found ..'
Any ideas? Should we have a Oracle for Win 2000 CD. It worked for SQL2000 on Winnt server.
Jim
View 1 Replies
View Related
Jul 17, 2015
I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.
SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''Â in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)
View 9 Replies
View Related
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
Jul 20, 2005
All,Can someone help me with the following SQL and help me write it in anOPENQUERY format. I am running the following code from a SQL Server 7box, trying to update a table in an Oracle Linked Server. The coderuns fine, except it takes almost an hour to complete. I know if I runvia OPENQUERY,I can get the same done in much less time.Some of the relevant information is as follows:ORACLE_HBCPRD04 is a linked Oracle Server.SITEADDRESS is a table in Oracle#SiteAddress_New is a table in SQL Server.UPDATE ORACLE_HBCPRD04...SITEADDRESSSETCUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR1 END,CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR2 END ,CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR3 END,CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR4 END ,CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSESiteAddress_New.CTY_NM END,ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSESiteAddress_New.ST_ABBR END,POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSESiteAddress_New.POST_CD END,CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSESiteAddress_New.CNTY_NM END,CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSESiteAddress_New.CNTRY_NM END,ADDR_STAT = NULL ,LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATEFROMORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN#SiteAddress_New SiteAddress_New ONSiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_IDWHEREUPPER(SiteAddress_New.PROCESS_CODE) = 'U'Best Regards,addi
View 2 Replies
View Related
Mar 9, 2004
Hello,
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
Thanks
Josh
View 1 Replies
View Related
Jul 20, 2005
HiI received the below error when trying to run an update from one SQLServer to another.I can insert and select. I cannot delete or update. The permissionshave been changed to allow the linked server user to carry outeverything, the linked servers are working but we cannot change thedata.We are stumpped and your help would be appreciated.Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"charmfin"."charm"."TMP_BATCHPOSTING"' from OLEDB provider '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. Nowork was 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...Thanks in advance.Simon
View 1 Replies
View Related
Aug 11, 2007
I just finished up setting up linked server from one of my database machines to another. After futzing to get permissions just right, it works great for many things:
select top 5 * from altai.prep.dbo.simulation_status
0 Prepping Simulation is being prepped by the owner.
1 Prepped Simulation has been prepped by the owner.
2 Checking Simulation is being checked by the owner.
3 Checked Simulation has been checked.
4 Running Simulation is running.
I can even see tables using sp_tables_ex:
sp_tables_ex 'ALTAI', 'S%', 'dbo', 'prep', NULL, 1
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
prep dbo Simulation TABLE NULL
prep dbo Simulation_References TABLE NULL
prep dbo Simulation_SimulationGroup TABLE NULL
prep dbo Simulation_Status TABLE NULL
prep dbo SimulationGroup TABLE NULL
(5 row(s) affected)
The trouble is, I have another table in this database called that begins with "S": Staging_Transition_States. If I try to access this table via the linked server, I get:
select * from altai.prep.dbo.Staging_Transition_States
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "altai" does not contain the table ""prep"."dbo"."Staging_Transition_States"". The table either does not exist or the current user does not have permissions on that table.
This table exists, sp_help for this table is below. Why doesn't this table show up in the output of sp_tables_ex? As far as I can tell, permissions for the tables that work and this table are identical.
Thanks!
--Andrew
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
Staging_Transition_States dbo user table 2007-07-27 11:29:47.790
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
pdb4 char no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
temp smallint no 2 5 0 yes (n/a) (n/a) NULL
run smallint no 2 5 0 yes (n/a) (n/a) NULL
sim_id int no 4 10 0 yes (n/a) (n/a) NULL
time_step int no 4 10 0 yes (n/a) (n/a) NULL
time decimal no 5 9 2 yes (n/a) (n/a) NULL
status tinyint no 1 3 0 no (n/a) (n/a) NULL
order int no 4 10 0 yes (n/a) (n/a) NULL
comment varchar no 250 yes no yes SQL_Latin1_General_CP1_CI_AS
created smalldatetime no 4 yes (n/a) (n/a) NULL
created_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
updated smalldatetime no 4 yes (n/a) (n/a) NULL
updated_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object 'Staging_Transition_States' does not have any indexes, or you do not have permissions.
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT on column created DF__Staging_T__creat__047AA831 (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column created_by DF__Staging_T__creat__056ECC6A (n/a) (n/a) (n/a) (n/a) (suser_sname())
DEFAULT on column status DF__Staging_T__statu__038683F8 (n/a) (n/a) (n/a) (n/a) ((0))
DEFAULT on column updated DF__Staging_T__updat__0662F0A3 (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column updated_by DF__Staging_T__updat__075714DC (n/a) (n/a) (n/a) (n/a) (suser_sname())
No foreign keys reference table 'Staging_Transition_States', or you do not have permissions on referencing tables.
No views with schema binding reference table 'Staging_Transition_States'.
View 10 Replies
View Related
Jun 29, 2000
Hi,
I am running the follwing query to select data from a local and linked
server using a right outer join as follows and get the error message
shown below the query. How can this be corrected please? sg_q5 is local
and sg_qt1 is defined as a linked server on sg_q5. Non join direct
select commnads from sg_Q5 on the linked server works fine.
>>
Query :
Select name
from sysobjects RIGHT OUTER JOIN sg_qt1.globaldb.dbo.sysobjects
ON sysobjects.dbo.Name = sg_qt1.globaldb.dbo.sysobjects.name
where type = 'u' and name <> 'dtproperties' and name <> 'Rowcounts'
Error Message:
Server: Msg 117, Level 15, State 2, Line 4
The number name 'sg_qt1.globaldb.dbo.sysobjects' contains more than
the maximum number of prefixes. The maximum is 3.
>>
Thanks.
Ranjit
View 2 Replies
View Related
Apr 28, 2004
I am trying to create a linked server to some dbase 5 tables. I have read several other posts about using the OPENROWSET option and that does work however, I would like to be able to create a linked server for easier access.
End Goal: I am developing an ASP based app that needs to join a DB5 table to a SQL table.
Any help on the linked server properties in Enterprise Manager for dbase5 or on the proper sp_addlinkedserver function for dbase5 would be highly appreciated.
Joe
View 2 Replies
View Related
Dec 3, 2007
Reference:
http://support.microsoft.com/kb/306397
http://www.databasejournal.com/features/mssql/article.php/10894_3290801_2
In enterprise manager, I can browse the objects by clicking on "Tables" under the selected linked server, and that allows me to browse my objects in that server and eventually design my query graphically.
In SQL Server 2005 Express, I know that I cannot browse the objects after referring to microsoft article above.
"Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains. "
However, is it possible to design my query graphically?
View 1 Replies
View Related
Aug 16, 2001
I have a Table TEST (TCD int,NUMCD int ,TNM varchar(20))
where TCD is an identity column
1- When inserting I want the value of TCD to be inserted also in NUMCD.
2- I want to prevent two users to insert the same value of NUMCD
if executing the same insert at the same time .
For this i use the following insert :
INSERT Into TEST WITH (TABLOCK)
(NUMCD,TNM) select IsNull(Max(TCD),0)+1,'Abcd' from TEST
- Is the sql statment above resolve my probleme. If not is there someone who can help me resolving this probleme.
Think you for helping me
View 1 Replies
View Related
Mar 8, 2005
Hi DB Gurus..
I need to lock one table ( table-level locking) as i dont want anyone to modify till i finish my stored procedure.
I need simple example on how to Lock the table and unlock..
Something like this
Lock Table Name...
...
statements go here
...
Unlock table...
Is this possible in Sql server 2000
waiting for ur replies..
Thanks in Advance
View 3 Replies
View Related
May 2, 2008
I've written a C# program which (1) Calls a SQL stored procedure which, among other things, updates a table with email information and then (2) sends the email via Outlook. This is ultimately going to be on a production server where the C# email program can be called by a number of processes.
If more than one instance of the email program is invoked at the same time, problems could occur. For instance, after the first instance updated the table it would then begin the process of creating and sending the email, but if a second instance is called at that time,it could be updating the SQL table which I think would screw up the email the first instance was creating. As a solution, I've been looking into locking tables. What I'd like to do is lock the table after it's been updated and unlock it after the email is sent. I know I can use the SLEEP function in C# so if it tries to access the table and it's locked, to wait 20 seconds or so and try again.
So how can you lock (and unlock?) a table in SQL? I'm reading about using WITH (TABLOCK) in the UPDATE query, but I'm not sure that will solve my problem. From what I can tell, TABLOCK automatically "unlocks" when the update is done. That still wouldn't resolve the issue of instance 2 updating the SQL table after instance 1 had updated it, but BEFORE instance 1 has completing creating the email (based off the data in the table).
Thanks
View 3 Replies
View Related
