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?
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
i have the following problem. After creating a linked Server on a SQL-Server 2005 connected to a DB2 instance i have the problem that my catalog is named in the object explorer: default.
Why is it so? And how can i fix it.
While the catalog is named "default" i can't use the four parted names to use statements.
I keep recieving a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema."
I'm attempting to set up a linked server from SQL 2005 to Oracle 10.2. I run the following sp_addlinkedserver and sp_addlinkedsrvlogin procedures:
sp_addlinkedserver
@server = 'CUSTOMERLINK',
@provider = 'MSDAORA',
@srvproduct = 'ORACLE',
@datasrc = 'CUSTOMER'
GO
sp_addlinkedsrvlogin
@rmtsrvname = 'CUSTOMERLINK',
@useself = 'False',
@rmtuser = 'CUSTOMER1',
@rmtpassword = 'PASSWORD!'
These procedures complete successfully. I then run a sp_tables_ex procedure:
This procedure completes successfully and gives me all of the table names in my oracle database that relate to the Customer1 schema.
Then when I go to run a query
select id from [CUSTOMERLINK].[CUSTOMERDB].[CUSTOMER1].[CLIENT] Where name = 'codm'
I recieve a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema." error.
I'm trying to create a linked server to generalize the query for pointing to different versions/instances of the same schema. For instance, we are testing against a test db named Name_Config, but we also need to execute the same tests against Name_ConfigQA. By building the linked server (named "LS_Test"), we are attempting to leave the sql as is in another catalog on the same server by pointing through LS_Test so the following sql won't need modifying, just the linked server properties:
select * from LS_Test...table1
This produces the following error:
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='LS_Test', TableName='table1'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'LS_Test' does not contain table 'table1'. The table either does not exist or the current user does not have permissions on that table.
I've been able to do this through synonyms in SQL Server 2005, but I'm having no luck setting the propertes in 2000 such that the linked server maintains the catalog name. Any help would be greatly appreciated.
Can anyone tell the advantage of using the new catalog views over the traditional system tables which were given as compatiblity views in sql server 2005
I'm trying to access a System Catalog View (sys.database_permissions, and others) from a table valued function. I need to be able to pass the database name into the function so that I can operate on any database. Typically I'd use dynamic SQL to do something like
INSERT INTO #tempTable
EXEC ('SELECT * FROM ' + @DBName + '.sys.database_permissions')
But of course I can't use dynamic SQL inside of a UDF. I know I could do this using a stored procedure, but I'd need the output to be a recordset that I can query.
Has anyone done anything like this? I think I'm stuck.
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.
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).
Select * from LINKED_SERVER.MYDATABASE.DBO.MYTABLE
and it returns my results fine , however the reason I included a catalog was so the linked server would default to MYDATABASE , so technically (according to the documentation) I should be able to run the query
Select * from LINKED_SERVER..DBO.MYTABLE
and exclude my database name, however I keep getting the error
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "LINKED_SERVER'".
Could someone please help explain to me why this does not work?
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
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!!!!!!!!
Zero 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)
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 am building an intranet application for my company .. similar to code generator
I am developing ASP.NET Application for this
1) How do I display list of SQL Server on the Network
2) Once I have list of SQL Server, I asked user to select once of the Server, provide credential for that server and obtain list of Database for that Server
3) Once I have list of Database, I need list of table under particular database
If SQL Server Setup fails, Setup will roll back the installation but may not remove all .manifest files. The workaround is to rename the files and then rerun Setup. For more information, see How to: Work Around COM+ Check Failure in SQL Server Setup.
I was using SQL SERVER 2000 ... In one table I've created FULL TEXT SEARCHING ( Full text catalog along with full text indexing)
Now we had to install our db in SQL SERVER 2005 standard edition. But while taking script it gave me two lines like:
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 exec sp_fulltext_database N'enable' GO if not exists (select * from dbo.sysfulltextcatalogs where name = N'DEV_CAS_DiagnosisCatalog') exec sp_fulltext_catalog N'DEV_CAS_DiagnosisCatalog', N'create' GO
so I used this in the new db creation script...
But I couldn't get where it actually is in SQL SERVER 2005 standard edition.
and also plz help how should we create if it doesn't exist...
Fellow 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.
Can any one help - my Full text catalog on a remote shared sql server hasdied and i need to recreate it completely - I have done this before but i'velost the code to do it.If I remember right what i did was use start --> run --> to run an exe in themssql folder that connected to the remote server - but i've lost all theparameters I needed for it.ANy help much apprectiated
Books On Line makes breaking out the report server databases on to a separate machine from the report server itself sound very easy. I have tried it and it is but I can't get the report server to connect to the databases once they are on the other machine.
Everything was done by the book(s). We use SSRS only for intranet use so all inside firewall. The SQL Server 2005 services on both machines (including the RS Windows service) all run as the same domain administrator account, this account was used to log in to RS Config Tool and it was told to use this account under Windows Authentication to connect to the database server. The two machines are in the same domain, even on the same switch. The only difference is the one with report server on is Windows 2000 Server SP4 and the one to which the rs databases are moving is Windows Server 2003 SP1. The account mentioned above has sysadmin rights on both SQL Servers, as well as having all the RSExecRole rights as specified in BOL. And it has rights to logon as a service in group policy on both machines. Its password hasn't been changed.
The RS Config Tool completed fine and reported no errors.
But when you try to connect to the report server you get the error:
"The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed)" The log shows that a privilege required by the client is missing but doesn't specify what! I thought this was all taken care of when the RS Config Tool told it which account to connect with.
This is driving me daft. It should be so easy!! Does anyone have any idea what might be wrong? Nothing says I need to do anything with the encryption key when I am keeping the report server itself exactly as it was and just moving the catalog - or have I missed something here?
Since 2 weeks now I'm trying to figure out how to get SQL CE 3.5 into the CE 6.0 R2 image (I'm building my own image with platform builder 6.0, latest patches - until march 2008 - installed).
There is no Catalog item in my "Catalog item view" not even an older version of SQL! In Platform builder 5.0 there were Items in the Catalog and - as expected - by selecting them I got the server into the image.
I also tried to add "SYSGEN_SQLMOBILE = 1" to the project environment variables, which resulted in having build errors instead of getting a working image.
There are also some postings around in the internet telling how to add the required dll's manually. (Hey, Microsoft, do we live in stone age or in the 21st century?)
Btw. installing the cab files is possible for me, but of course not for our customers.
Are there any sugesstions how to get a catalog item?
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID] , MAX([TimeStart]) [LastRunDate] FROM [dbo].[Catalog] [c] LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID] WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source! group by [c].[Name],[c].[ItemID],[xl].[ReportID] order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT * FROM [dbo].[Catalog] WHERE [Type] NOT IN (1,5) -- Not a folder or a data source! AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1' select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
