The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:
SELECT * FROM DB_INTRANET...Employees
I get the following error:
OLE DB provider "SQLNCLI" for linked server "DB_Intranet" 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.
The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.
I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.
How do I specify the Catalogue while querying a linked server?
For exmaple, I have a remote SQL 2000 server as a linked server on my server. I will refer to it as "ServerRemote." I was given access to that server through my Active Directory Domain account and the catalogue 'master' was set as my default database.
Aside from having the admin change my default database, is there a way to query a linked server and specify the catalogue in the query?
Right now I use the following code to query the default catalogue for the linked server: SELECT * FROM ServerRemote...ViewName
I've tried various queryies for a different catalogue but always get a table not found error:
SELECT * FROM ServerRemote..DatabaseName.ViewName or SELECT * FROM ServerRemote.DatabaseName..ViewName or SELECT * FROM ServerRemote.DatabaseName.ViewName all return errors
I have SQL 2K with SP4. Oracle client is installed on the Server. I have created linked server to Oracle. I am abld to connect to Oracle Server through DTS but when I query an oracle linked server SQl Server crashes with "EXCEPTION_ACCESS_VIOLATION" error.
Info on the Server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have the SQL dump if you need that. Please help.
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.
There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.
Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.
Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.
Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005
do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?
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
I've been digging for a while to try to find something, even had a consultant looking for me, but came up empty haned.
What I want to do is this:
I activated the Security Audit level for all logins. I want to be able to report last login date for every user from the log, and all login events for SA or any other defined super users.
Is it possible to Query the SQL Server Logs to report this information?
This is for proposed Sarbox Database Security settings.
I know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA
I am remotely logging onto a MS SQL 6.0 server using sp_addlinkedsrvlogin. When I try to query against the table on this server I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]
I know that 6.0 doesn't support OLEDB but shouldn't 7.0 be able to access this using odbc. Do I need to do anything other than set up the dsn on odbc.
Any feedback on this would be great. Cheers, bhanly@apcc.com
When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:
declare @x xml select @x = P from openrowset (bulk 'E:VehicleOption0514.xml', single_blob) as Products(P) declare @hdoc int exec sp_xml_preparedocument @hdoc output, @x
I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John
Can I run queries against a SQL DB that is running on SQL Server 2005 Enterprise from the Express edition? I just need to get the info in a table. I have been told I have windows authentication to the DB, but the DB does not show up in my Express' list of networked severs.
Your help is appreciated. I have only worked with MySQL so this is all new to me.
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked' FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd)) AS VARCHAR) AS daysworked, ActivityId AS totalcalls FROM PhoneCall AS p WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb) AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
I was wondering if it is possible to continuously query a real-timedatastream using SQL Server. Does anyone have any experience of this? Ihave found LINUX based systems such as Borealis and STREAM. I wouldprefer to use a Windows based system as the program using the queryresults is Windows based.
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
HI, I'm trying to implement a site search. The only problem is that some of the pages have more than 8000 characters (like the press release pages) so I had to use the TEXT datatype (sql server 2000). I wrote a simple stored procedure to query the column, however it does not return any results. I'm assuming this is due to the fact that it is the TEXT datatype. ANY help would be GREATLY appreciated. Code below: CREATE PROCEDURE sp_SiteSearch @keyword text
AS
SELECT PageText FROM tbl_Pages WHERE PageText LIKE '%@keyword%' GO
Hello --I'm trying to get SQL Server 2000 on a Windows 2000 Server to be ableto query an Active Directory. We've got two domain servers one Win2000and one Win2003. However, I'm having problems:I've run the following query to setup the linked server:sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces','ADSDSOObject', 'adsdatasource'Using Enterprise Manager I've changed the login details for the ADSIlinked server to:Be made using the following security context:Username: MyDomainAdministratorPassword: ****I've then tried to run the following query in Query Analyzer:SELECT * FROMOpenQuery(ADSI,'<LDAP://CN=Users,DC=MyDomain,DC=com>;(&(objectCategory=Person)(objectClass=User));name,ad spath')But get the following error message:Could not execute query against OLE DB provider 'ADSDSOObject'.Does anyone have any ideas on what this message means or how to getthis working?Thanks--James Allanjames [at] allan-home.co.uk
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns from DateTable d Join Project p where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data MessageContentType | BodySegment xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT ID,MsgContentType BODYSEGMENT, BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan FROM s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML SET @XMLVAR = '<?xml version="1.0"?> <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
I created an Image for the Database in sqlserver 2000. When I am querying directly the database as "Select * from employee", it is returning the result set.
But, when I am querying Database image with same query. It is giving error
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'Employee'
It is expecting the Database Owner. If I do query like "Select * from [Owner].employee", it is working fine.
My requirement is not use owner in queries, it should be simple query ("Select * from employee").
Can any one, help me out. Thanks in advance. Bhupesh
Is there any way to deploy reports to a Report Manager in SQL Server Reporting Services 2005 if the reports query SQL Server 2000 databases? Or are only 2005 databases able to deploy to the web?
Also, if I have a report that queries both, can I deploy it to the web on the 2005 Report Manager with a linked server to the 2000 server?
I am trying to setup querying Active directory from sql for the first time.
We are running on windows server 2012 and using sql 11.0.2100.60. Have tried the following
sql is on sever dev AD is on sever DO
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' GO
[Code] ....
I get the following error when I try and query
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query "SELECT name FROM 'LDAP:// xxxx.internal' WHERE objectCategory='Person' AND objectClass = 'contact'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
