SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work
Apr 16, 2015
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
View 0 Replies
ADVERTISEMENT
Jul 21, 2015
I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. why it won't run when I have it like this but will run when it is like this?
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp)
FROM [DATABASE]
WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time
DECLARE @timeint int SET @timeint = '5' --time interval in minutes
DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as
DECLARE @comma varchar(4) SET @comma = ', '
[Code] ....
View 9 Replies
View Related
Mar 26, 2008
If this post belongs somewhere else I appologize. I have spent several days trying to solve this problem with no luck. My site is online. Hosted at NeikoHosting. I can connect to the database remotely when adding a datacontrol. It tests fine. But when running the page it won't connect. Even if I go in and change the Web.Config connection string to a local Data Source provided to me by Neiko, it still won't work. It just won't connect. Here are the two connection strings in the Web.Config, minus my login info: Only the remote string will pass testing. Neither works on the site. <add name="yourchurchmychurchDBConnectionString" connectionString="Data Source=MSSQL2K-A;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
<add name="yourchurchmychurchDBConnectionString2" connectionString="Data Source=66.103.238.206;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
Here is the stack trace, if that helps.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.OleDb.OleDbConnection.Open() +37
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.FormView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.FormView.EnsureDataBound() +163
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
So....... HELP!!!!
Thank you!
View 3 Replies
View Related
Jun 21, 2006
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
View 7 Replies
View Related
Aug 4, 2015
I am trying to run a query from a Linked server "ABCD"
Set @SQLCMD = 'Select * from TableName"
Insert into @TempTableName
Execute (@SQLCMD) AT ABCD
I am getting below error while running the above statements. When I Remove the Insert into @TempTableName it is working fine.
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ABCD" was unable to begin a distributed transaction.
View 2 Replies
View Related
Mar 31, 2004
hi all
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin
declare @current_date datetime
set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc
end
View 1 Replies
View Related
Feb 23, 2015
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
View 2 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Mar 28, 2008
I have created a lot of reports using this technique, but this is the first one that doesn't work. As there is absolutely nothing special about it, I can't figure out what the issue is.
I have one dataset that uses parameters chosen from two other dataset results. One dataset result runs just fine and returns values in reporting services, but the other returns blank in reporting services. In Visual Studio, both datasets return data.
I cannot for the life of me figure out what I've done that gives this result as I've never encountered it before and use this method of parametization quite frequently.
The dataset in question doesn't even join any tables, it's a direct select distinct field"1" from table"a".
I'm running SQL2005, SP2.
Thanks for any advice.
Margaret
View 6 Replies
View Related
Dec 17, 2010
I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.
In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.
When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.
When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'
This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.
View 8 Replies
View Related
Mar 9, 2015
I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?
View 9 Replies
View Related
Feb 20, 2005
I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
Here's some particulars:
---
EXEC sp_addlinkedserver
@server='VFP',
@provider='VFPOLEDB',
@datasrc='\hdmcpdctis1 isrnddata',
@srvproduct='Visual FoxPro'
--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \hdmcpdctis1 isrnddata ',
'select * from [tislists.DBF]')
With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================
Any Help is greatly appreciated! Thanks,
peter :confused:
View 5 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
Jul 23, 2005
Hi all,I set up our Oracle Financials as a linked server to one of my SQLServer boxes. On running a test query, I got the following errormessage:OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.Metadata information was changed at execution time.OLE DB error trace [Non-interface error: Column 'TEST_NUM'(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reportedto have aDBTYPE of 130 at compile time and 5 at run time].The Oracle datatype of the column with the supposedly inconsistentmetadata was NUMBER, which according to the Oracle OLE DB documentationactually maps to 139. 130 is a null-terminated unicode characterstring, 5 is a float, and 139 is a variable-length, exact numeric valuewith a signed scale value. Oracle NUMBER is an all-purpose numerictype, apparently they use that instead of int, float, etc. The Oracleguy used it for this column in the test table because in OF it is usedin pretty much every table. (For starters it is the datatype of theiridentity columns.)There is something in the OLE DB spec about all datatypes having to beable to be expressed as DBTYPE_WSTR (130), but what I don't get is thatI can connect to the same Oracle instance using VB6 code and theMSDAORA provider and there is no problem at all interpreting the NUMBERcolumns. So why does it work from VB and not as a linked server? Andmuch more importantly, HOW DO I MAKE THE LINKED SERVER WORK?TIA
View 11 Replies
View Related
May 24, 2006
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
View 1 Replies
View Related
Apr 23, 2015
I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.the SQL Server allows me to make this relationship in only one field.A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
View 5 Replies
View Related
Jan 15, 2008
I was using linked servers to import Excel spreadsheets into SQL Server Express 2005. This worked fine with Windows XP and Office 2003.
I have just migrated all my stuff to Vista and Office 2007. Linked servers just can't be created:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
The parameter values I used are:
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Excel
Data source: D:...somelocalfile.xls
Provider string: Excel 8.0
I gave full access rights to the Data source file and folder to NETWORK SERVICE and SQLServer2005MSSQLUser$MOZART$SQLEXPRESS where mozart is my PC name.
If I change Data source or Provider string to some garbage string, the exact same error message appears. So it appears the error might be in the Provider?
Help help please.
View 4 Replies
View Related
Jun 12, 2015
I lost my database on my pc as I format my hard drive. Now, how I can get a copy of my ms sql server database from my hosting to work with my local pc? I need to have a local copy so that I can develop my site without internet connection. For your information, I used Hostforlife.eu hosting service. I have searched and tried many methods to do it, but no luck yet. For example, I created a backup on Hostforlife.eu and restore backup file on my pc, but nothing happens. Then I created a new database, and re-created its structure same as the one on Hostforlife, then I used the import wizard to import data from them to my pc. Again, nothing happens.
View 5 Replies
View Related
Sep 15, 2015
Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
How to setup the linked server........... Below are the drivers installed on the SQL server
PostgreSQL35W
PostgreSQL30
View 2 Replies
View Related
Jun 26, 2007
hi,
I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.
Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.
Any ideas?
View 4 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Oct 23, 2006
works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
View 2 Replies
View Related
Oct 25, 2007
SSRS 2000
Data driven subscription uses a SQL query to create report parameter values.
The subscription errors are inconsistent meaning sometimes no reports in a batch fail and sometimes 3 out of 5 will fail. I don't think it is data related because I'm using the same data each time.
When a report fails to email there is a consistent error related to rendering a chart. That message is in red below.
The data source is Analysis Services 2000. Has anyone seen this before? I've seen a lot of postings that are similar but no solutions. Is it possible for RS to lose data or get corrupt data from an MDX query?
Is there a timeout between RS and Analysis Services (PivotTable Service) to check?
SSRS trace log:
ReportingServicesService!runningjobs!237c!10/25/2007-14:15:01:: w WARN: Thread pool pressure: turning off threads
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
ReportingServicesService!reportrendering!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
--- End of inner exception stack trace ---
ReportingServicesService!library!237c!10/25/2007-14:15:01:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!emailextension!237c!10/25/2007-14:15:01:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Data Driven Notification for activation id 1ab1f67c-82c0-4dba-9745-a4057ab1cb4f was saved.
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
ReportingServicesService!notification!237c!10/25/2007-14:15:01:: Notification 15307c1b-0b60-493d-b2c1-fde89780ff06 completed. Success: False, Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., DeliveryExtension: Report Server Email, Report: SalesPerfScorecard_Email, Attempt 0
ReportingServicesService!dbpolling!237c!10/25/2007-14:15:01:: NotificationPolling finished processing item 15307c1b-0b60-493d-b2c1-fde89780ff06
View 4 Replies
View Related
Oct 27, 2006
Hey. I've a windows vista machine which can't connect to SQL 2005 box. Other machines which are lower than vista work fine. Also, vista box can connect to other SQL 2005 instances but can't connect to only one instance. But, I can connect to the instance fine from my XP box. Also, it's not a permissions issue. This user is an SA and can connect form some other machine. SQL 2005 box is at 2047 version right now. And, the user is able to connect to some other boxes which are also at 2047. Please help. Thank you
View 1 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
May 27, 2008
Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
View 1 Replies
View Related
Jul 2, 2007
Hi Anyone,
I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.
Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.
Anyone has experience on that?
Thanks a lot
View 3 Replies
View Related
Apr 4, 2008
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Thanks
Sean
View 2 Replies
View Related
Feb 12, 2008
Hi,
I have two servers S1,S2.
I am currently connect to S1 but I want to Query a table from S2 without creating a linked server.
Is it possible.Can someone please post some information on how this can be accomplished.
ETL, Lookup ,Stored procedure anything is ok. I am using SQL Server 2005.This is a very urgent requirement.
Please help!
Thanks,
View 6 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
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
View 6 Replies
View Related
May 8, 2001
Greetings all,
I have successfully been able to use a SELECT query to see tables on a remote (Linked) server. Now, I want to be able to join those records with a WHERE clause to the same tables I have on my local database. The idea here is to be able to see current data on my local database(which is not current data), which resides in the remote database(which is current data). Here is what I have so far:
SELECT top 10 * from LinkServer.MC_Card.webuser.POS_TX
But, I'm not sure how to SELECT from two tables. Would I do a SELECT from the local database WHERE all records = records on the remote database?
Not sure how to do this. Is this what would be considered a Distributed Query? And how would I make this work with joins like the existing joins I have to the tables in my local database?
This is new territory for me. Sorry if this is such a newbie question.
Thanks,
Bruce
View 1 Replies
View Related
Nov 7, 2006
I'm using MS SQL Server Express and I've added a linked server. (I created the linked server by right mouse clicking on Server Objects, selecting SQL and entering the SQL2 as the name.) Now I'm trying to query a linked table. The following query works.
SELECT * FROM SQL2.PA.dbo.Counties
This one doesn't:
Select * from OPENQUERY(SQL2,"SELECT * FROM SQL2.PA.dbo.Counties")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT * FROM SQL2.PA.dbo.Counties'.
Can you tell me why?
View 2 Replies
View Related
