Please help.
I created a linked server with Oracle Provider for OLE DB to Oracle Database from SQL Server 2000 SP 4. Afterwards I created Views from this linked server and granted SQL Server users "select" access to the Views.
Unfortunately when they try to access the views the following error message was generated (see the attached image - error.gif).
Can someone please explain to me what is wrong?
Thanks
Victor
Hi,
I have two servers that i want to create a SQL RS report on.
On one server there is an HR database with our staff details, on the other server there is a database of assets.
In order to report on the assets assigned to each user i am thinking that i will have to :
1) link the servers
2) create a view in the HR database exposing the fields needed
3) create a view in the assets database exposing the assets information joined to the view from the other server
4) create my reports on the view on the assets server.
is this right or am i barking up the wrong tree?
SQL 2005 SP2 in Managemrnt Studio...
I have a linked server set-up pointing to an instance that has several databases. In one catalog I have no tables or views being listed for the catalog. So I select a view, add the permissions that should allow the view to show in the list but it does not. And I did refresh and I did even restart management studio.
BTW: I can exec the view using a 4 part name.
Any one have an idea why the view don't show in the list?
Thanks much
chuck
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTDss2005].master.[information_schema.colums]
Thanks
Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!
View 1 Replies View RelatedZero Length Strings Showing up as a Space in Views linked in Access
I have code being converted from a SQL 6.5 DB to SQL 7; SQL 7 does not concantenate NULLS like in 6.5, so i have replaced all occurances of concantenated NULLs with zero-length strings (''s); in SQL, the view displays the zero-length strings properly, but when i view the linked view in Access97, the zero-length strings are displayed as spaces; is this a SQL problem or an Access Problem? Is there a solution?
Example of display:
10132 Hampton, VA: A. Deepak Publishing
SELECT PublisherID, Info = CASE WHEN City IS NOT NULL
THEN City + CASE WHEN State IS NOT NULL
THEN ', ' + State ELSE '' END + ': ' ELSE ''
END + Publisher
FROM tblLibraryPublishers
When view is linked in Access97, the display will be:
10132 Hampton, VA : A. Deepak Publishing
(this spacing can produce a number of lookup/search problems if only the Publisher name is displayed because a space is added to the beginning of the Publisher name)
Thank you!
Llyal
Has anyone seen this error related to SSIS? I get this everytime I try to perform any action on (import, export, or run) a file system package. I am racking my brain and I would prefer not to perform a full reinstall, but I am not seeing any posts that have helped with this error.
Unable to cast object of type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction' to type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ISimpleAction'. (Microsoft.SqlServer.DtsObjectExplorerUI)
Something strange just happened to my database.Windows Vista UltimateSQL Server 2005 SP2IIS 7.0I just did an insert into a few tables from my ASP.NET application that I'm building and I suddenly cannot query the database tables. The query window just spins and finally times out.So I decided to try a SELECT TOP and tried 100, nothing, then 10, nothing, then 1 and it returned the first row. Then I tried 2 and got that, and I remember this table had 8 rows prior to my last Insert, and once I tried to select TOP 9, it times out again.Does anyone knows why this happened and how to fix it? It's like every row past 8 either doesn't exist or got corrupted somehow. I can't even drop the table and rebuild it. SQL Server won't let me. If someone doesn't have a solution, I'll have to rebuild the entire database and I'd rather not have to do that.Any help would be appreciated.Thanks,Kahanu
View 2 Replies View RelatedMsg 945, Level 14, State 2, Line 1
Database 'Northwind' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I keep getting that error for just trying to USE Northwind. I don't understand why this problem is occurring. It was working fine for the past few weeks. Yesterday something happened to the DB and it said something about recovery to which I accidentally clicked cancel. From then on I can't access some of the DB that I have on my computer.
I'm fairly new to the SQL Server Environment so I would appreciate any and all help that can be provided. I've google searched the details but I can't seem to get any of those to work. Thanks in advance for your reply.
Hi All,
Does anyone knows what is the reason behind following error ? And resolution for that ?
Server: Msg 945, Level 14, State 2, Line 1Database 'Blaster' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Thanks in advance,
--kneel
Hi,
I have a problem with a database on our SQL 7 system . I had set away a restore from tape, but cancelled it after users complained that a different database was performing slowly . However, this has resulted in the database being marked as inaccessible when trying to access it through SQL Enterprise Manager and trying to access it through Query Analyser produces message 927 informing me that the database is in the middle of a restore . How do I remove this error and make the database accessible again??
Many thanks in advance
Peter Burton
(IT Support)
Durham Aged Mineworkers Homes Association
Hi,
I have a trouble with script component. From time to time, I cannot see available data types of the column I´m just editing, in the Script component editor. The cell in the section Data Type properties, where normally combobox with data types is, is completelly black. This happens both with "old" script components I'm only viewing and new script component I'm creating.
This problem occured for the first time immediately after I migrated from Windows XP to Vista Bussiness 64bit and then several times without any obvious similarity among terms under which this appeared.
I have SP2 for SQL Server and SP1 for VisualStudio 2005 Professional edition installed.
Please, can you help me?
Thanks
Jana
I am not MS SQL Developer, and I have no experience with it. I have had to use it as a part VAMT database of keys and activation.
There is 8.1 box with MS SQL (express I think).
After change host name I cannot connect to database with error "The specified database is not a valid VAMT database."
Looking into log gives me: Could not obtain information about Windows NT group/user 'litkjonca', error code 0x534."
Some googling suggest to use:
sp_dropserver "..." and sp_addserver '...', local
but with no effect .
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
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'Â Â
Been practicing DR strategies with a test SQL instance by following the scenarios listed here: [URL] ....
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1.
[Code] .....
I made a merge replication and sucessfully connected with mobile device. Everything works fine. Because I wanna try asynchronous synchronization (which won't stop my application executing when subscribing) I read How to example from MSDN :
http://msdn2.microsoft.com/en-us/library/ms172391.aspx
But there is a problem with creating SyncStatus class object. I can't get into it and I can't create. maybe some reference will help? (I use reference to SQLServerCE). This error stoped my work for now, so I am waiting for some answers.
And if it's simple resolution for this problem - sorry. I just started programming applications for Mobile Devices.
Best regards. Maciek Wysocki
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
How to fix this SQL error event logg 17204 and 17207 ?
View 8 Replies View RelatedFellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedWhich is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedI have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
Thanks!
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
Hi,
I've been using stored procedures to do queries including inner joins etc. I have always known about views but don't understand why they are used, what is there purpose, they seem the same as sql queries, is that true?
Is it more effiicient to use views rather than stored procedures to do your queries?
Really I just want to know why I would ever want to use views instead of stored procedures to do my queries?
Thanks in advance.
Need a Clarification in SQL Server 2000.
I would like to know if its possible to create a view of a table residing in a SQL server which is not the one in which we r currently working in
Desc:
1) Say i have 2 SQL servers( different Boxes) A & B
I am working in ' B ', can i have a view of a table residing in SQL server 'A' in my system( Server 'B')
2) Leaving out the network uncertainities, will such a setup give any performance issue when the view is for a table that captures daily transactions running into several
thousands of records
3) Is ther any other Alternative to access tables in another SQL sever without going for Views?
Plz find out and reply ASAP.
I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks
View 2 Replies View RelatedHi there,First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?Third, would it be better to create our crystal report using Views to improve performance?Thanks for anyone who participates in this thread! :-)
View 1 Replies View RelatedHi There,
Is it possible to make views which takes Data from Multiple Servers?
NOTE: The view is very complex because it uses FOUR Diff. statements
joined together with UNION clause.
One more Q.
If my Query Analizer Displays following on execution of query.
" Internal Query Processor Error: The query processor could not produce a query plan."
What should be my approach to rectify the problem.
Any help would really be appreciated.
zak
I have two databases on my Server - database1 and database2.
Database1 has a view on tables on database2 :
create view myview as select * from database2.dbo.tablename
(because all tables in database2 are owned by dbo)
and myview on database1 is owned by dbo.
when a user on database1 - lets say user1 tries to select rows from myview,
we get an error saying that user1 is not a valid user on database2.
it only allows user1 to select rows from myview if i make user1 a user on database2
as well, which is not what i want.
Can someone please point me in the right direction.
i would like to be able to select rows from a view which is based on tables in another database.
thanks
simran
not sure if this is by default or restricted,
but I can't view the sql server logs using ctp
an error is generated saying the tsql could not be retrieved or something, very long message to post here
is this normal?
TIA
--------------------
keeping it simple...
Hi,I am working with several tables and views. My goal is to create a viewwith critical reporting data from these tables and views. I havemanaged to get the majority of data but am having difficultly with thefinal step.For a record in the master dataview, add additional record informationby appending data from another view based on the first 2 letters of thedocument number and the document number i.eDocument numberSC11111DN22222SI33333For SC11111 look up data in Sales Credit table/view for doument SC11111and append to that lineFor DN22222 look up data in Delivery Note table/view for documentDN22222and append to that lineFor SI33333 look up data in Sales Invoice table/view for documentSI33333 and append to that lineAny help appreciated.Thank you in advance,Raj
View 1 Replies View Related