Hello
in my database I have schema "x". I have user "x" who is database owner, The user has schema "x" as default schema and is owner of this schema.
Now there are tables "x.mytable"
If I connect to the db as "x" and try
select * from mytable
I get an error "Invalid object name", I have to write it this way
select * from x.mytable
Why?
Thank you
Eckard
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
hello,
I have list of tables in a database where schema1 is attached. when ever i login thru windows authentication or any other user and if i run select * from schema1.table i get proper values.
what i need is even if i login thru windows authentication i should be able to run the query without attaching the schema
i.e., select * from table should work. i am not sure what permissions has to be given.
pls advise.
Hi,
I have a table 'TEMP' which owns by user 'SCOTT'.
I logined using the user 'SCOTT' and try to fetch the data without specifying schema name.
select * from TEMP.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'TEMP'.
I getting the above error.
while i execting with schema name.
select * from scott.TEMP.
I getting the required output.
Is it possible to fetch the data without specifying schema name??
Plz help me.
The owner of the table TEMP is scott.
In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema
(
@t1Name NVARCHAR(257)
,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
Compares the schema of 2 tables
If the schema is different RETURNS 0
If the schema is identical between the two table, RETURNS 1
NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
==================================================================================================================
SAMPLE USAGE:
DECLARE @schemaOK BIT
SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1
PRINT 'TABLE SCHEMA IDENTICAL'
ELSE
PRINT 'TABLE SCHEMA DIFFERENT'
==================================================================================================================
*/
BEGIN
IF @t1Name = @t2Name
RETURN 1
-- check if schema is different
IF EXISTS
(
SELECT*
FROM
(
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
UNION ALL
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
) U
GROUP BY
COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
HAVING COUNT(*) <> 2
)
RETURN 0
-- schema identical
RETURN 1
END
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
Hi guys, please help.What I did is :Step1. create a Database named [Test], it's default owner is [sa]Step2. create a User in Database [Test] named [kimliuTest], which mapsto the Login [kkhadkimliu] on this Database Server, it's a windowsdomain user.Step3. create a Schema in Database [Test] named [schTest], whose SchemaOwner is User [kimliuTest]Step4. created two tables: [dbo].[t1] and [schTest].[t2]Step5. run the scriptALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTestto set the default schema.Now my problem is, when I try to runselect * from schTest.t2, everything is OK, but if I runselect * from t2the error message says:Msg 208, Level 16, State 1, Line 1Invalid object name 't2'.why? I'm loggin in with my [kkhadkimliu] windows domain id , ActiveMonitor also shows that. but the command does not work?Yeah, it's a good habit to write the full name of the Table with Schemaname as prefix, however, we did our development in SQL 2000 and tryingto upgrade to SQL2005, so trying to avoid scanning codes and insertingin thousand of places.How could we omit the Schema name when referrring to Tables? Thanks.ps. I posted in another topic days ago, but nobody is replying, when Itried to bring it forward by replying myself, it failed. please help me, thanks!
View 3 Replies View RelatedHi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?
How do I grant a specific access to a schema. I have a request to grant a ddl access to a schema called Business_Banking.
View 1 Replies View RelatedHi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks
View 1 Replies View RelatedI think this should be a dead easy one.
SQL Server 200.
Can I query to find all the tables in a single schema? I can't for the life of me find this one out!
I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!
Cheers,
G
Hi all,
does anyone know of a way in which you can backup and restore objects that belong to a specific schema?
So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.
--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2
Thanks in advance,
Andy.
EDIT: I forgot to add that I want to backup the tables and the data contained within.
Hi all,
I downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
Thanks in advance,
Scott Chang
SQL BPA says the following:"One or more objects are referencing tables/views withoutspecifying a schema! Performance and predictability of theapplication may be improved by specifying schema names.""When SQL Server looks up a table/view without a schemaqualification, it first searches the default schema and then the'dbo' schema. The default schema corresponds to the currentuser for ad-hoc batches, and corresponds to the schema of astored procedure when inside one. In either case, SQL Serverincurs an additional runtime cost to verify schema binding ofunqualified objects. Applications are more maintainable andmay observe a slight performance improvement if objectreferences are schema qualified."How important is to specify the schame (dbo. in my case) instored procedures? Will it really improve performance if I goand fix each object that is missing "dbo."?The problem is I have thousands and thousands of themwith no schemas. Before I invest a lot of time fixing themI am trying to determine if it's really worth it or not?Thank you
View 1 Replies View RelatedHi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
i am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
I am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
Role Loans Payments Transactions Accounts Customer Emplo
Database Admin SUID SUID SUID SUID SUID SUID
Branch Manager SUI SUI SUI SUI SUI SUI
Internal Auditor S S S S S S
Loan Officer SUID SUI SUI S S
Tellers S S S S SU
Customers U
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.
Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.
Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?
Thanks!
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
I am at a loss as to what to do. Please help.
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!
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
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?
View 2 Replies View RelatedHello,
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.
Please give me some advices here.
Thanks.
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
View 3 Replies View RelatedI am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
If I want to disable access to a single table on an SQL server on a periodic basis can anyone tell me how to do it.
Thanks
Still really new at all this, but learning lots thanks to this forum.
I was wondering - is there a way to import Access tables into my SQL Server 2005 ?
(The Data and the Table Design?)
Hi,
We have a SQL server database on remote server. We are using ODBC connection to read and write into the table. But we have another user wants to retrieve(readonly access) our data on his pages. How can we create a user on our database and grant readonly access to that user?
Thanks..
The problem is we need local tables in Access for SubReports (can't use store procedures as record source for this), therefore trying to create temp table and link to Access for each report instance.
When we create the ## type table in tempdb this cannot be linked from Access (cannot be seen via DSN).
The only other option we can find is doing
CREATE TABLE tempdb.dbo.[tablename]
but this requires the user to have admin permissions and therefore be 'dbo' on tempdb and not 'guest'.
Any clues?
Palmy
I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:
SELECT COUNT(id) FROM @dbname..sysobjects WHERE type='U'
How can I access the tables while the database name is in a variable.
Thanx.