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 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
"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.
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
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?
Hello,I am relatively new to SQL Server, although I have used Oracleextensively.In Oracle, there are system tables that you can query in order to geta list of all schemas and all the tables in them. Is there a similarconcept in SQLServer? How would I find about the system dictionarytables?TIA :-)
Hi All, I have a requirement where in for a given View name, I need to programatically find out all the tables that are used in the view and I also need to find out how each table in the view is linked.
For example, if I create a simple view like CREATE VIEW XYZ AS
SELECT * FROM TableA INNER JOIN TableB ON TableA.ID = TableB.FKID INNER JOIN TableC ON TableA.ID = TableC.FKID
I need to find out all the Tables involved in the view i.e. TableA, TableB and TableC. I also need to find out how TableA and TableB, TableA and TableC are linked (i mean the join Condition).
Could some one please point me to resources that can help me find the answer to this.
I got the following error msg last night, in fact running anything in a query window. My solution was to stop and restart the SQLServer, but not ideal in a production environment. Anyone know what the cause/solution could be?
TIA, Neil
sg 910, Level 17, State 1 Could not allocate a new object descriptor for required system catalog in database '1'. Another database must be closed or objects in another database dropped in order to open this database. Msg 2812, Level 16, State 4 Stored procedure 'sp_who' not found.
We run std 2008 r2. When I deploy and run a pkg from the catalog, how can I get that flat file system log we always instructed ssis to write to when we ran from the command line? I believe it was the /L param . Not sure at this point if i'll use sql agent or somehow employ task scheduler to kick off the pkg.
I was looking in sys.syslogins and sys.sql_logins for a property to tell me if the login is locked but all i see are properties for if the account is enabled or if the account has access. Is there a system view I can query to tell if if the account is locked ? Thanks,
can someone tell me where I can look to find a sql command that is being executed by an application against my database? In Oracle, I look in the v$sql_text view - is there something similar in SQL Server 2000 ? Many thanks in advance
Hi all, Is there any easy way to not allow a user to see system views? I have set up 1 view for a login, and I have to use an ODBC connection to access it. However, the 3rd party application I'm using is apparently timing out because of the number of tables/views that are returned. TIA! - Mark
I've had a couple problems with SMO and I wonder if anyone else has. Specifically, when I run through stored procedures (just like your example) it is painfully slow. Maybe 5 a second. Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject. Both cases crash my app. Any ideas? Below are two ways that both crash or hang. SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection) {} OR
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection) { if (!view.IsSystemObject) {} }
I got a script, which ran on SQL Server 7. After compiling on SQL Server 2012, I came across that the previous script used a system ViewsTables columns named "suid", which no longer operate in SQL Server 2012. After modifying the script with 2012 and replaced suid by SID, it works but the suid column is not appearing in System Views. What if we want to get that column back in SQL Server 2012? is there any possible way?
Please, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables. I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.
Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.
Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:
SELECT [name], recovery_model_desc FROM sys.databases;
Which turned exactly what it says it will.
however, I am confused. I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case. My question is, why is that? How do I know when to use [] around something?
All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
here is the situation. I have a DB on one system. I back it up and then restore it to a second system. This second system I run reports off of and I want to create custom views that do not exist on the original system. Can I restore the backup DB from the remote system without wiping out the custom views on the local system?
I have to do this this way as they won't let us create the views we want on the remote system so the only way we have access to run the reports is by restoring the backup locally.
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error? It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access. Thanks in advance - I hope.
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
Hi I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects). I'm stuck - help!
I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:
I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?
If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
We have allowed one of our clients to create views in our .net /sql application so they can build reports using MS Access. They have been doing so successfully for years. However lately they are experiencing Timeout Expired messages when in the actual web application. I have confirmed that several of the views when opened in Access do cause blocking, the locks are promptly realeased when the the access odbc of the sql view is closed.
When the views are opened in SQL query analyzer, there is no sql blocking.
Would this be related to how the tables are linked?
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)
