DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins
Apr 26, 2015
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" 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.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
View 3 Replies
ADVERTISEMENT
Apr 14, 2007
I have a single table named PROCESS which contain following three fields
ProcessID
ParentID,
info
* Every process have a unique ProcessID and ave single parent process which is identified by ParentID.
* If a process does not have a Parent then its ParentID value is -1.
*Only single level of Parent-child hierarchy is maintained.
Can anyone please tell me the Query that uses Recursive JOINS to retrieve the ProcessID s based in following Conditions.
1. if the Parent's 'info' field contains given value then retrieve all the process under it.
2. Retrive all the process whose 'info' contains given value and excluding the Processes resulted from 1st conditiion
Thanks in advance
Bharath Booshan L
View 2 Replies
View Related
Mar 2, 2008
I just installed a SQL Server 2005 Express SP2 instance on a server with an existing SQL Server 2000 SP3 installation. (I need SQL Server 2005's INSERT from an EXEC capability). It's working great now except for one thing: I can't link any other SQL servers! I've already successfully added and queried a linked Oracle server, but attempting to add a linked SQL server gives me the following error, no matter which SQL provider I try to use:
Code Snippet
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
Additional information:
--> An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
--> Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "SERVERNAME". (Microsoft SQL Server, Error: 7302)
The technical details reveal the error source as "sp_testlinkedserver".
I've scoured the net and premier support for advice on this problem, but the little I found wasn't helpful. I've tried reinstalling the SQL Native Client, but it didn't help. I've tried uninstalling SQL Server 2005 Express completely (including management studio and native client), rebooting, and reinstalling everything, with no luck. The server (obviously) does not have a firewall enabled. I've tried stopping the SQL 2000 Server installed on the system, no help. If I create a test .UDL file on the system, pointing to any of the SQL servers I'm trying to link, clicking the "Test Connection" button returns successful. I'm also able to connect to and query the servers directly from the SQL 2005 Express Management Studio. I just can't add them as linked servers.
The server is running Windows 2003 SP1, SQL Server 2000 SP3, and SQL Server 2005 Express SP2 (the default "SQLExpress" named instance). I've tried setting up SQL 2005 Express to run under the network service account and under a domain account used by the other SQL 2000 servers.
From other SQL 2000 servers, I can connect and link to the SQL Server 2005 Express instance successfully. I can also successfully add linked SQL servers in SQL Server 2000 instance installed on the same server. Only adding linked SQL servers in 2005 Express seems to be broken.
Anyone have any other suggestions? I'm totally baffled. Thank you so much for any helpful advice.
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
Sep 29, 2015
Will the order of inner joins and cross apply effect the results of a query?
Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c
[Code] ....
I want to change the position of doing "CROSS APPLY". Will it effects query results?
View 2 Replies
View Related
Aug 25, 2005
I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel
View 4 Replies
View Related
Jun 17, 2007
full outer joins and cross joins not working!?!?
am using vc++2005, ADO, and MSAccess 2003. MS "documentation" straight out of the VC++2005 help facility at
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vdt01/html/419ef633-5a89-41a2-aefe-03540afc9112.htm
provided the following code samples for different types of joins
inner join
Code:
SELECT title, pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id left join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idright join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idfull join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idjoin
Code:
SELECT *
FROM authors CROSS JOIN publishers i created two MSAccess Tables:
Merge1:
K1 x
---- ----
a 1
b 2
c 3
Merge2:
K1 x
---- ----
b 20
c 30
d 40
e 50
and executed the following code to test the different joins. the first three joins worked but the last two did not. would appreciate any insight. DBM is an instance of an AccessDBManager class i have written to encapsulate interactions with Access DBs.
Code:
DBM.SQLExtractString = "select * from Merge1 INNER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 2 records returned (K1 = b,c)
Code:DBM.SQLExtractString = "select * from Merge1 LEFT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 3 records returned (K1 = a,b,c)
Code:DBM.SQLExtractString = "select * from Merge1 RIGHT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 4 records returned (K1 = b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 FULL OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 5 (K1 should = a,b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 CROSS JOIN Merge2";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 20 (5 * 4)
appreciate any ideas/comments to get the last two joins to work.
thanks - j
View 3 Replies
View Related
Jun 24, 2015
As bcp does not allow for the column names to be included; I have developed a method for providing the columns. The end result is that two Tables are required for each output; a "ColumnNames" table and the Table that contains the actual data; however the bcp command is sorting the data; why this is happening?Â
According to Microsoft, by default bcp will not apply any sorting unless specified.
Here is the command I am using to perform the bcp output: -
SET
@bcpCommand =(select
'bcp "SELECT * FROM GPReports.dbo.MIS001_BCPColumnNames UNIONÂ SELECT * FROM GPReports.dbo.voltemp" queryout '
+ @FilePath+'
-c -t -T')
EXEC
master..xp_cmdshell
@bcpCommand
This is the bcp topic I referred to [URL] ....
View 3 Replies
View Related
Feb 10, 2003
SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id
Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.
The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id
Please help I can't figure this one out.
Thanks.
View 1 Replies
View Related
Jun 18, 2015
I have installed a default installation of a named instance of a 2008 R2 server and I want to restore a server from the main system and application files. I have a bak file for the latest master and model database and the four application databases and transaction log files. If I restore the master database in single - user mode I will get an error when I start-up the SQL service as the other databases are not attached to the SQL instance.
Is it restore databases and then master/model? best way to restore a SQL server environment?
View 10 Replies
View Related
Aug 6, 2015
Is there any query/script to find out if SQL Server 2012 instance is set to hidden or not instead of going to SQL Server configuring manager?
View 2 Replies
View Related
Oct 11, 2007
I recently installed SQL Server and Visual Studio. When I went into SQL Server, I could only connect to a SQL EXPRESS Database engine. I need to access the full Database. I tried disconnecting it, uninstalling/reinstalling. Now I cant connect to the SQL EXPRESS DB Object. There are no SQL Server services running.
Please help.
Thanks
View 8 Replies
View Related
Nov 24, 2015
I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
View 3 Replies
View Related
Sep 20, 2011
I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as
For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with
member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
View 11 Replies
View Related
Aug 21, 2015
I have been trying to configure a linked server to AD and have found plenty of write ups on how to do it, but have had zero luck with getting it to work. Â I also read that the ability was removed after SQL 2008; is that correct? Â I am running 2014 and am seeing the following error trying to expand the tree:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)...An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADSDSOObject" for linked server "ADSI". (Microsoft SQL Server, Error: 7301). URL....Then the following error when trying to query:
SELECT * FROM OpenQuery(ADSI, 'SELECT displayName FROM ''LDAP://<DOMAIN>/DC=DOMAIN,DC=<DOMAIN>'' WHERE objectCategory=''User'' ')
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT displayName FROM 'LDAP://----/DC=----,DC=----' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".Â
The linked server is set for 'Be made by the login's current security context' and local to remote is configured SA to a domain account we use for lookups. Â It does not seem to matter if I am logged in to the server with SQL or Windows credentials. Â
View 3 Replies
View Related
May 8, 2013
We have 3x instances of SQL Server 2012 installed on a single remote server - there's the default MSSQLSERVER instance, then INSTANCE01 and INSTANCE02. I can remotely connect to the default MSSQLSERVER instance through SSMS, but I cannot connect to either of the additional named instances (INSTANCE01 or INSTANCE02).Â
For example, if I try to connect to "sql.domain.com", I can successfully access the default instance on the remote server. If I try to connect to "sql.domain.comINSTANCE01", I get an error stating
"A network-related or instance-specific error occurred while establishing a connection to SQL Server".
However - if I try to connect to "sql.domain.comINSTANCE01, 49301" (where 49301 is the TCP Port for the TCP/IP Protocol for this SQL Server instance), I am able to successfully connect.
This leads me to think that there's a communication issue with the SQL Server Browser service running on the remote SQL Server and my workstation.Â
The following items have been verified:
SQL Server Browser is running on the remote SQL ServerWindows Firewall has been disabled on the SQL ServerTCP Ports 1433, 1434, 1954, and 49301 have been opened up on the remote destination's firewallUDP Port 1434 has been opened up on the remote destination's firewall.
View 10 Replies
View Related
Jun 21, 2015
I have TWO named SQL Server instances (on the same machine) and I need to know the port of each of them, how can I do that? Is it write to check the following:
Which one to take: "TCP Dynamic Ports" or "TCP Port"? and what is the difference between them anyways?Â
Can the two instances (or more)Â on the same machine use the same port?!
View 12 Replies
View Related
Aug 6, 2015
I am new to SQL Server 2012 clustering.I added a new instance to one of the two nodes.when I try to move it to the other node it fails.Do I need to install it on both?If so, what options do you install on the second node?
View 7 Replies
View Related
Apr 23, 2002
Hello,
I am researching alternatives to a stored procedure that uses a linked server query that my ASP page uses to retrieve records to the page.
Should I:
Use replication (Data is constantly updated, so a snapshot won't do. Merge replication?)
Break up the stored procedure into subqueries?
Use a view on the remote server instead of the actual tables?
I am trying to reduce the amount of table scans, what is the best way to do this?
Here is the stored procedure I am trying to tune:
(@startDate and @endDate are parameters passed from the web page):
CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
SELECT Store.[Str#], Store.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (Store INNER JOIN Event E ON Store.[DemoID#] = E.event_number)
LEFT JOIN (Location L RIGHT JOIN POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((Store.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY Store.[Str#]
GO
Any suggestions on my sp or anything else that can implement would be greatly appreciated.
TIA,
Bruce Johnston
Programmer/Analyst
View 1 Replies
View Related
Oct 27, 2015
connecting to a SQL Server Instance. I have a SQL server DB on a server having an Instance not running on the default port. Let's say ServerAInstanceA has the services exposed on Port 12345. When I'm trying to connect to ServerAInstanceA without specifying the port number, while I can connect from one client machine (ClientA), I am unable to connect from a different client machine (ClientB).On a side note, when I provide the port number, I can connect both from clients A and B. My question is, when I do not provide the port number, why is one client machine able to connect while the other isn't?
View 4 Replies
View Related
Jul 3, 2003
Hi, all
In domain A I have Server1(sql 7),
Server2(sql 2000).
In domain B I have Server3(sql 2000) with two instances(Red and Blue). Both instances' ports are not 1433.
My goal is adding Server3Blue as a linked server in both Server1 and Server2.
In Server2 I added Server3Blue as a linked server successfully by specified alias and port number.
But I failed in Server1.
I got 'unable to connect to specified server' error.
Any idea?
Thanks in advance,
View 8 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
Apr 24, 2015
Need to set up a linked server in two node SQL Cluster pointing to a standalone SQL thru security setting "be made using current login's security context". It's double hop Kerberos setup. Cluster uses a domain account, so we manually set SPNs for that account with both instance name and protocol as well (set up these SPNs with SQL virtual name only).
Also, constrained delegation has been  set to that stand alone SQL services (MSSQLSvc).
Both SQL cluster and standalone servers are in same domain, cluster service account is domain account and standalone SQL uses default SQL local service account.
Coming to SQL, when I create linked server, sometimes it lets me create without issues and sometime it throws this below warning and even if I create it won't work.
 Login failed for user 'NT AUTHORITY/ANANOMOUS LOGIN'.Â
View 6 Replies
View Related
Jul 23, 2015
I am attempting to reach some Clipper tables through a 32-bit ODBC driver from a 64 bit SQL Server. As there is no 64 bit driver offered for Clipper, I am pursuing a solution similar to the one described here:
Creating a Linked Server with 64 bit SQL Server 2008 to MS Access
It involves using a SQL Express 32 bit instance as a bridge.
I have created a Linked Server on the 32 bit instance MTESTXPRESS as follows:
EXEC sp_addlinkedserver @server = N'ABDATA', @srvproduct=N'DataDirect 4.1', @provider=N'MSDASQL', @datasrc=N'ABServerCA'
On the 64 bit instance ALISTESTER I have another Linked Server as follow:
EXEC sp_addlinkedserver @server = N'ABACUS', @provider=N'SQLNCLI', @datasrc=N'ALISTESTERMTESTEXPRESS'
The suggestion is to then use a select statement such as:
SELECT * FROM OPENQUERY(ABACUS, 'SELECT COUNT(*) FROM ABDATA...ABBATCH')
Unfortunately, the DataDirect driver for MTESTEXPRESS will not recognize the 'ABDATA...ABBATCH' 3-part naming convention. The error message is:
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "ABDATA"
Is there some other way to select from the MTESTEXPRESS linked server?
View 21 Replies
View Related
Feb 12, 2009
We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
Â
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.
Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
Â
 Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
 ROLLBACK
Â
Source server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
      Nov 12 2008 12:10:04
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
Â
Target server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
      Jul 9 2008 14:43:34
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
View 30 Replies
View Related
Jun 14, 2006
I'd like to set up a Service Broker queue in one database (dbRespond) on a server so that events in another database (dbEvent) on the same server instance can post messages to the queue. The problem I'm having is that:
The BEGIN DIALOG CONVERSATION needs to reference a Contract that is in the current database, and I want to call BEGIN DIALOG CONVERSATION from dbEvent
The target service is in dbRespond. Based on the "Hello World" Service Broker example that comes with SQL 2005, in dbRespond I need to specify the contract in the CREATE SERVICE call that creates the target service. Here, too, the contract must be defined in the current database.
How do I deal with needing to have the same one contract in two different databases?
View 5 Replies
View Related
Oct 24, 2007
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.
The linked server is a Visual FoxPro database.
After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.
View 3 Replies
View Related
Dec 21, 2005
I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.
SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called,
Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds
FROM CallRecords
WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')
When I preview in the Transform Data Task, I get:
Package Error
Error Source: Microsoft JET Database Engine
Error Description: No value given for one or more required parameters.
When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)
When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.
Thanks for any help you can provide.
View 2 Replies
View Related
Feb 23, 2008
I am trying to perform a distributed query however have a situation I haven't dealt with before the linked server I add to do the query is a named instance (DVD_NASDOMINO). How would I specify this in a query as in the FROM part in a sql statement. I tried the obvious DVD_NASDOMINO.qlsdat.dbo.stmenqry and DVD_NAS.DOMINO.qlsdat.dbo.stmenqry.
Both returned errors in the query.
Thanks for the help.
View 1 Replies
View Related
Jun 21, 2006
Hi,
I run the code sample below in a SQL Mgt Studio query window. I get no error messages. However, the two SELECT statements at the end produce undesireable results (see comments). My messages to not make it to the target queue; they get stuck in sys.transmission_queue with the error message in the final comment of the code. The server principal "ddddXXXXX" is a Windows Active Directory account that: 1) is a domain admin in the domain that my SQL Server box is a member server of, 2) is the login used by the "SQL Server (MSSQLSERVER)" service account, and 3) is a member of the SQL Server instance's sysadmin fixed server role.
What is it I'm missing that is denying a sysadmin login access to a database?
/***
Service Broker try 1
***/
USE master;
GO
CREATE DATABASE sbRespond;
GO
CREATE DATABASE sbEvent;
GO
USE sbRespond;
GO
CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[ActivatedStudyTargetQueue];
GO
CREATE SERVICE ActivatedStudyTargetService
ON QUEUE [dbo].[ActivatedStudyTargetQueue]
(ActivatedStudyContract);
GO
USE sbEvent;
GO
CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO
CREATE SERVICE ActivatedStudyInitiatorService
ON QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO
-- Send a message.
USE sbEvent;
GO
BEGIN TRANSACTION;
GO
DECLARE @message XML;
SET @message = N'
<message>
<PROT_ID>123456</PROT_ID>
<StudyID>AAAA1234</StudyID>
</message>
';
DECLARE @conversationHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE ActivatedStudyInitiatorService
TO SERVICE 'ActivatedStudyTargetService'
ON CONTRACT ActivatedStudyContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE ActivatedStudyMessage
(@message);
END CONVERSATION @conversationHandle;
GO
COMMIT TRANSACTION;
GO
USE sbRespond;
GO
SELECT * FROM [dbo].[ActivatedStudyTargetQueue];
GO
--The query above returns zero records
SELECT * FROM sbEvent.sys.transmission_queue;
GO
--The query above returns two records, each with this "transmission_status" value:
--An exception occurred while enqueueing a message in the target queue.
-- Error: 916, State: 3.
-- The server principal "ddddXXXXX" is not able to access the
-- database "sbRespond" under the current security context.
View 3 Replies
View Related
Aug 3, 2007
System: Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.
Error message from Management Studio query window:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".
Event messages associated with this error:
App Event ID: 19036
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.
Sys Event ID: 10016
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user domainuser SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.
I€™m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename€™))
The linked server has a remote login and password that it uses to connect to DB2.
I found this from another post on how to fix this error:
Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security
Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account
Under Security - Access permissions: Allow System: Local Access and Remote Access.
After completing these steps I still get the error message but the events are no longer generated. I€™ve also tried different variations of these steps. The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties €“ Identity Tab €“ Run this App as this User.
Can someone please tell me the steps that I€™m missing?
View 11 Replies
View Related
May 16, 2015
i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.
View 4 Replies
View Related
Jan 31, 2008
Hi all,
My support team inform me that my servers have been updated and rebooted on monday. Now I need to run some cross db queries today and I dont seem to be able to connect anymore. I am getting the (dreaded apparant but not surprisingly) Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Now first thing I did was try to drop both linked servers and add again. I am thinking that the particular server has a dynamic ip which I have determined. Ive noticed a work around on MSFT but it say about using a connection string with IP.
Can anyone help me work this out? id appreciate it.
"Impossible is Nothing"
View 7 Replies
View Related
