Need To Hide Irrelevant Tables/views From ODBC Connection

May 18, 2007

hi,

I maked ODBC for SQL erver data base and linked with Access database which is working good but when I check ODBC link in access it shows alot of other stuff and all tables related to that database .



Is there any way to remove irrelevant tables/views etc from that ODBC - thanks for help

AA

View 2 Replies


ADVERTISEMENT

ODBC Connection - Lost Tables

Jan 8, 2004

I have a SQL Server database that I connect a front end to using an ODBC connection. Our LAN folks upgraded the server recently and now I can no longer see any of the tables through the ODBC connection that the user used for login has permission in SQL Server Enterprise Manager to see - throught the ODBC connection the user can only see things like:

dbo.spt_datatype_info
dbo.spt_datatype_info_ext
dbo.spt_fallback_db
dbo.spt_fallback_dev
dbo.spt_fallback_usg
.
.
.
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
etc.

I've tried deleting the user for the connection and re-establishing it with owner permissions. I've tried deleting the dsn and re-establishing that as well but nothing so far.

Please help!!!

View 9 Replies View Related

Import Tables Through ODBC Connection Into SQL Express

May 13, 2008

I'm trying to import data from an Oracle database into SQL Express. Basically I have a database that's local to my machine (C# front end with a SQL express back end) but I need to tie in some data from an Oracle database. The reason I'm trying to import it instead of just using a connection in C# to hit it is that I need to be able to access the tables while I'm offline so I need a local copy. I couldn't find any references on doing it through stored procedures or anything like that. Any assistance would be greatly appreciated. thanks in advance.

View 1 Replies View Related

Query To Select Multiple Tables In Dataset Through ODBC Connection

Apr 10, 2007

Hi there,



I have a MS Access database (mdb) containing the following tables:



Crime

Criminal

CrimeCommitted

Hideout

CriminalType



The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:




Code Snippet



SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;







This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).



The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?

View 2 Replies View Related

Integration Services :: Unable To See 64bit ODBC Driver From SSIS Odbc Connection?

Jun 1, 2015

I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.

When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.

Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650

SQL Server Integration Services   
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0

And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.

I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.

Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938

SQL Server Integration Services   
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0

why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?

View 9 Replies View Related

Can't Find SQL Native Client In ODBC Connection Manager In SQL Server Open Database Connectivity (ODBC)

Feb 13, 2007

I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.

I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?

View 4 Replies View Related

Schemas, Views, And ODBC

Mar 28, 2008

I have some tables sitting in schema "A", then I created a view in schema "B" based on the tables in schema "A". Then I gave the user just access to schema "B" to only run the view but not see the tables in schema "A".

Could it be that if the user tried to ODBC to that db, they would only see the view in schema "B" and not the tables in schema "A"?

Thanks!

View 2 Replies View Related

Accessing Views Via ODBC From MSAccess

Jul 20, 2006

Hello,

This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.

The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.

I then find out there is no data in the views and so the user would not have been able view anything anyway.

So what I want to do is remove "public" from the views permissions, is this possible?

Thanks for any help,

Tyrone

View 4 Replies View Related

How To Hide Sme Tables In DB

Dec 24, 2001

Configuration:
Windows2000 , SQL7
Three people have administrative priveleges (with priveleges Enterprise admins)
There is special DB in SQL7
Some tables should be hidden from 2 admins (they should be unable to read information from these tables)
Is it possible?
And if it is possible, How to do it?
Thank you very much
Merry Christmas
Happy New Year
Seasons greetings

View 1 Replies View Related

Restricting Access To Sys And INFORMATION_SCHEMA Views In ODBC

Oct 23, 2007

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!

View 4 Replies View Related

Transact SQL :: How To Fetch Data Updated Today Irrelevant Of Timing

Apr 21, 2015

I need the rows updated today in catalogue table irrelevant of timing. I tried all the below queries.

select * from CATALOGUE where CAT_DATE=CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
select * from CATALOGUE where CAT_DATE= CONVERT(date, getdate())
select * from CATALOGUE where CAT_DATE= cast(GETDATE() as date)

I am getting output only if the information updated on 04/21/2015 00.00.00.000 but not for other timings, For example
04/21/2015 03.30.00.000, 04/21/2015 07.17.00.000 and all.

How can I retrieve all the records updated today.

View 4 Replies View Related

System Tables And More - How To Hide Them?

Mar 9, 1999

Hi everyone,

I've recently installed MSQL Server 7.0 Standart Version and every time I create a new database, system tables are created and are visible also. That means, I instantely get 18 tables, 20 views, 18 stored procedures and so on. Can I hide them??? I mean, my tables are all mixed with the system tables. If I can hide all the system tables and views and so on please tell me! Thanks.

View 3 Replies View Related

Hide Empty Tables

Mar 1, 2007

Hi,

I've just creates a report with about 20 Tables. To my suprise, it almost runs as intended ;-)
But there is one flaw - Is there any way of hiding an empyt table?
I want to hide all tables that are not filled with data. But wich one is empty differs from the entered parameter.

So long
Sven
(Germany)

View 2 Replies View Related

SQL Views Accessed In MS Access Via ODBC (Blocking Issues)

Jan 3, 2007

Hello,

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?

Any suggestions would be greatly appreciated.

View 3 Replies View Related

Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?

Jan 24, 2008

I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.

I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!

So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?

Thanks,
Keith

View 4 Replies View Related

Hide System Tables From Users?

Mar 7, 2008

How can I hide the system tables (sys and INFORMATION_SCHEMA) from users? I am not talking about Management Studio. We have users who access the database with ODBC and I do not want them to see all these system tables.

I have created a Hide_System_Tables role and added users to it. I have tried denying all the permissions to this role, but they still see it. Oddly enough, in the INFORMATION_SCHEMA permissions, it shows access from grantors of INFORMATION_SCHEMA and dbo. I cannot deny permission for the entries with dbo as grantor.

Does anyone have any suggestions on where I have to go to remove all the places that give users access to these tables?

Thanks,
Mike

View 6 Replies View Related

Hide System Tables From Users

Jan 28, 2008



Hi,

Can someone help me to hide all system views/tables from users?
I do not want the users to see all system tables/views, so they can have a clear view for only user tables in their schema.

Thx,

Vicki

View 20 Replies View Related

Remote Connection Refused When Creating ODBC Connection To SQL Server 2005

Aug 30, 2006

When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.

How can I allow the server to allow this.



Any help appreciated



regards

View 1 Replies View Related

How Do You Hide The System Tables In Enterprise Mngr?

Apr 30, 1999

How do you hide the system tables in Enterprise Mngr?

Is there a way?

Thanks,
Keri

View 1 Replies View Related

Hide/encrypt Username And Password In Connection String In Script Component

Jul 24, 2007



Hi,



I have connectionstring inside my script component, but I want to encrypt/hide it... how do i do that in ssis?



Dim cn As SqlConnection

Dim SQLCmd As SqlCommand

Dim dr As SqlDataReader

cn = New SqlConnection("Server=localhost;user=sa;pwd=test;database=test")



cherriesh

View 10 Replies View Related

Setting Up Read-only Users. How To Hide Sys. Tables. SQL SERVER 2005

Oct 16, 2006

I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).

The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)

I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables.
None of the required db1 tables appear.

under Security/Logins I've created User1:
SQL Server auth. with password
default db = db1
server_roles = none
user mapping = map, db1, user1,dbo
securables = none
status = grant, enabled

on the access db, the odbc link was set up with default db = db1

Why can't I see any of the db1 tables?

How can I restrict access to the sys. tables?

Thank you

View 3 Replies View Related

Open An ODBC Connection To A DB And Must Use A Connection That Looks Like This: DSN=myDSNname.

Dec 11, 2006

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

View 5 Replies View Related

Views Vs Tables

Oct 16, 2007

I am an SQL beginner / intermediate. 
My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
 

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly

View 3 Replies View Related

Views Vs. Tables

Aug 24, 2000

Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.

I created the following view PAYACTIVE:

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:

Select * FROM PAYACTIVE

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>

Emily

View 1 Replies View Related

256 Tables In Views

Aug 30, 2005

I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."

My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)

All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.

Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)

SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)

View 13 Replies View Related

Permissions On Views And Tables

Aug 12, 2005

I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:

View 2 Replies View Related

Create Views Not Tables

Jun 9, 2004

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??

Thanks, John :eek:

View 1 Replies View Related

Tables/Views In DTS Package

Jan 5, 2005

Is there an easy way to find all instances of a table/view name in packages?

View 1 Replies View Related

Linking Tables With Views

May 12, 2008

I want to link in a table from another sql server in a view, how is this done?

View 1 Replies View Related

Updating Tables Using Views

May 18, 2007

Is there any gud topic on "updating tables using Views".Plz let me know

View 3 Replies View Related

Permissions With Views And Tables

Jul 23, 2005

We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved