Schema (owner) Names Not Visible In Shapes On Diagram
Apr 20, 2007
When reverse engineering a SQL Server 2005 database (using VS 2007 Professional), I can't figure out a way to display the schema name in front of the table name in the database diagram. For instance, If I have a table named Person.Type, it just comes over as Type. It knows what the schema is because it's in the properties box for that object. It just doesn't appear in the name on the shape itself. Is there a setting somewhere that I'm missing that will display the schema in the name?
I am merging two databases and created two different schemas to contain the objects in the new DB. I would like the objects in both schemas to be visible to one user but I can't figure out how to make this happen. I thought I could just set the Owner of the schemas to the same User but that didn't work. At the moment, the User default schema is set to dbo. I would like the default to be both schemas but this isn't an option.
Is there any way to have multiple schema objects visible to a single user? Thanks!
I have a schema/user called mstp who has tables in another schema/user. How do I prevent the mstp user from seeing those tables/data that do not belong to their schema?
example:
database xxxx has tables som.employees and mstp.employees The mstp user should only allowed access to mstp.employees and nothing else.
I have a database that has been upgraded from SQL 200 to 2005. The database was owned by 'Joe' and all objects were also owned by 'Joe'. In SQL 2000 when Joe logged in he could simply issue 'select * from table' and would get results.
The upgraded db now has a user Joe and a schema Joe. All objects now belong to the schema Joe. Joe the user has his default schema set to Joe and he is the owner of the schema. When Joe logs in and tries to 'select * from table' he gets an invalid object. He can 'select * from joe.table'.
How can I set it up so that the objects are still owned by the schema Joe and a simple 'select * from table' works when user Joe logs in? I was convinced that is he owned the schema and this was his default schema it should work.
User bob has create procedure permission as follows:
grant create procedure to bob
bob would like to create a procedure in schema Accounts.
When he issues create proc Accounts.sp_proc.... it fails with:
Msg 2760, Level 16, State 1, Procedure sp_proc, Line 3 The specified schema name "Accounts" either does not exist or you do not have permission to use it.
What permission do I need to grant bob in order to allow this?
Hi, I need to write a stored procedure,that look's up the owner of each database on an server and returns the names of the databases which are not owned by SA. Can we do this..if so how??
Greetings,I read msdn article and has the following:Transferring ownership of a view to the schema owner The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. The view is located inside schema Production. ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;GOso what i understand that i can create an object within a schema that has a different owner from the owner of the schema!!!is this true ? so if if it is true so what the benefits to create an object within a schema that has a different owner from the owner of the schema.?your help is highly appreciatedbest regards
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
One of our databases has at some point in its dark past had the owner of the guest schema changed to be a named user, rather than the default guest user. Correcting this feels like it would be easy enough by running the following...
ALTER AUTHORIZATION ON SCHEMA::guest TO guest but that results in.. Msg 15150, Level 16, State 2, Line 3 Cannot alter the schema 'guest'.
I realise the guest schema is a special one, and cannot be dropped, but I'm not trying to do that. End goal is to export the database to a SQL Azure DB, and this guest schema assignment is blocking that process from completing.
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...
What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....
either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -
I am working on a dtsx package wherein i am sending the data from OLE DB Source (SQL Server) to OLE DB Destination (Oracle). For development purpose i use DEVLOPMENT environment on oracle but for unit testing i have to use QA or Some other Schema. when i use DEVELOPMENT Schema in ole db destination, tables are accessed under Schema name eg. "DEVELOPMENT"."EMPLOYEE", but when i m chenging schema name to QA table names are not changing as "QA"."EMPLOYEE". Data Flow Task is pushing the data to DEVELOPMENT environment only.
Can Anyone suggest me any remedy for it ? Or this is one more BUG in SQL Server 2005.
I wihsh to discuss whether to use fully qualified names:[database].[schema].objectof objects to operate (create, query..) on is good or not?If someone change order of sql code blocks in my script - this may causelose of it's context (like: use master / use <mydb>..). I wish to have mysript independed on changes like this and always produce correct result.Does using full name make use of 'use <db>' statement unnecessary?
I have created a database with several tables. I want to create a database diagram to show the relationshipbetween the tables. Below are the steps from the SQL Server 2005 documentation on how to create a database diagram. The problem is that when I right click on the Database Diagrams folder I am only given two options. They are:"Working with SQL Server diagrams" and "Refresh"There is no menu choice to create a new database diagram. Can anyone tell me what the problem here is?
To create a new database diagramIn Object Explorer, right-click the Database Diagrams folder or any diagram in that folder. Choose New Database Diagram on the shortcut menu. The Add Table dialog box appears. Select the required tables in the Tables list and click Add. The tables are displayed graphically in the new database diagram.
Working within SQL Server 2014 the old standby of shape2sql doesn't seem to be working. I even tried the trick of copying the DLL and renaming it. Still no joy.i need the alternatives to be free or near free, simple and easy to recommend to others. I'm checking out ogr2ogr currently.
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.
The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.
Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.
Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?
I recently imported some Access tables into SQL Server. I can go into enterprise manager and return all rows and the data is there.
I build an ASP page to edit the data. After the connection the data for the record is displayed in a form. For some reason some fields are not coming through?
Is this a problem when converting to SQL Server. It just doesn't make sense that the table in Enterprise manager says the field is populated but when I do a pull via ASP the data doesn't come through.
Does anyone know anything about this?
Any help or pointing in the right direction would be greatly appreciated.
I am using an expression to hide values in a row based on a date field ( if date is bewtween this date and that date show value, otherwise hide it). My problem is now I need to sum all the values, even though some are not visible the sum is still adding them. Is there an expression to check for only "visible" rows??
