Hey all,
Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.
Thanks for any advice.
Hello everyone.
I'm facing to a serious problem.
I wanted to migrate SQL 2000 -> SQL 2005.
First of all, I tried to attach my *.mdf file to SQL 2000. but It was not only failed but also SQL 2005 failed to recover my *mdf file.
Now, I think my *.MDF file has system tables for SQL 2005 because when trying to attach *.mdf to SQL 2000 engine send the following message :
--------------------------------------------------------------------------
Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'rudol.net'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.
--------------------------------------------------------------------------
Also, I tried again to attach the *.mdf to SQL 2005 but SQL 2005 doesn't really works.
Stupidly, I didn't back up the *.mdf file.
How do I attach it to SQL 2005 or recovery "sysobjects" table for SQL 2000?
Please answer.
P.S.
Forgive my terrible English. English is not my mother tongue.
Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.
I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:
quote:select * from MyTable
Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects.
dbcc checkdb ('MyDatabase')
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc checktable ('MyTable') with ALL_ERRORMSGS
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
dbcc checktable ('sysobjects') with ALL_ERRORMSGS
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I've fully verified the correct existence of the table and how it links to the object ID by running statements like:
quote:select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)
Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.
Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.
All tables are owned by dbo.
Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.
Any thoughts?
Hi,I'm trying to determine with my program whether or not a given databasesupports a given feature set. To do this I'm querying for certain storedprocedures in the sysobjects table and if they are present, making theassumption the database will support the given feature. The problem is Ican't find a certain stored procedure in the sysobjects table, even though Iknow it exists and can see other similar procedures using:select * from dbo.sysobjects order by nameIts as if all of my other stored procedures are in the sysobjects tableexcept this one, the one I'm specifically querying for. Are there certainreasons why a proc won't appear in the sysobjects table? Is this somethingI need to fix?Thanks,Robin
View 4 Replies View RelatedAll my online research has told me how to get info about a table field's data using the system tables, but I can't find anything that tells how to get that table field's data specifically. Could be that it's difficult to explain so difficult to search on but I know there has to be a way to do this and it can't be that difficult.
How do I use the system tables sysobjects and syscolumns to give me the data from a specific field in a third table?
Basically I don't want to know the field type for a tables field, I want to know that field's value.
Let's say I have a table called tblCompanies and that table has 4 fields. idCompany, companyName, companyState, and companyCountry.
How can I return the value as a command parameter for any one of the 4 fields using sysobjects and syscolumns?
If I were writing dynamic SQL I would do something like this:
set @valueToReturn = exec ('select ' + @fieldNameToReturn + ' from ' + @tableToSearch + ' where ' + @fieldToMatchOn + ' = ' + @valueToMatchOn)
But I don't want to use dynamic SQL, I want to use the existing system tables to write a straightforward query. My nonfunctioning/English version of this would be:
give me the value for the field name I send in as a string (@fieldNameToReturn)
from the table I send in as a string (@tblToSearch)
where (sysobjects.name = @tblToSearch) and (syscolumns.name = @fieldNameToReturn) and (@tblToSearch.@fieldnameToMatchOn = @valueToMatchAgainst)
I'm using sysobjects and syscolumns because that's where I can use my variables for table name and column name to link. I just can't figure out how to get hold of my actual data table and the values in it.
Does that make any sense to anyone? I'm sure someone has had to want something like this.
Thank you, thank you, thank you!
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedRESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
I have a sp where I have 2 tables. I have already populated ecah table with data.
Now I need to update Table1 with data from Table2.
Key fields between the 2 tables are job_date, job_number and job_phase.
If the record exists in Table1 (reading Table2) need to update record in Table1 with qty_received from Table2.
If record does not exists in Table1, need to insert record into Table1 with job_date, job_number, job_phase and qty_received from Table2
I am very new to sql and wonder if someone would be so kind to help me out? Thank you.
Table1 ie Target
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2)
qty_received decimal (8,2)
Table2 ie Source
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2)
I have a SQL table where most of the fields are numeric. I have tried to run a query in Access to either make a new Access table or insert the records into an empty Access table. However, it changes some of the numeric fields to text fields. Also if I view the design of the SQL table in Access some of the numeric fields appear as text. If I view it in SQL they are numeric. It is a linked table in the Access database.
View 1 Replies View Relatedhi,
how can I use stored proc to delete all rows from a table (table1) and insert new ones.
for instance.
my query is:
-------------------------------------
select field1, two, three
from table1 innerjoin table2 on etc...
where condition1 and condition2
-------------------------------------
I want to place the result in my table (table1) and everything I run the stored pro I want to delete the content and place the new dataset in the table1?
I hope I'm clear what I'm trying to do.
thank you very much in advance.
P.S please be detailed when explaining.
I want to step through each record in one table and extract a School number and concatenate it to a date and insert it in another table. I get an error abour duplicate keys when I run my sp though. each school number is unique. I want to end up with the key of my new table like this
01/03/07*2232
01/03/07*2253 etc
CREATE PROCEDURE spSM_AddWeeks
@dteWeekEnding nvarchar
as
INSERT INTO tblSM_Meals
(ML_Id)
select @dteWeekEnding +"'*'"SchoolNumber from tblSM_Schools
GO
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View Relatedwhich one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
View 7 Replies View RelatedHow can you modify the crdate in sysobjects?
View 1 Replies View RelatedI am not aboe to view the userdefined tables in sql 2005. not sure whether my user dont have permission to view. how to view my user permission or not ..
another one , is there any table to find system table instead sysobjects,information_schema in 2000.
Hello !
I try to drop a table, but I get this message :
Could not drop object 'dbo.cs_Users' because it is referenced by a FOREIGN KEY constraint
This message does not tell me which is the Foreign key constraint so that I may disable it.
Any solution to force the dropping?
Thank you very much!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
hi, is it possible to recover the dropped table?
if means please tell me how.
hi everyone,
I have a couple questions. I'm very new to SQL and I have this problem:
I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Any help is greatly appreciated.
Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.
View 4 Replies View RelatedHi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina
View 1 Replies View RelatedI am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?
sSQL = ""
sSQLExists = ""
sSQLExists = "IF OBJECT_ID (N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"
sSQL = "CREATE TABLE [" & TableName & "] (" & sFieldInfo & ");"
ConnectSQLExpress "ImEx.mdf"
DbConn.Execute sSQLExists
DbConn.Execute sSQL
DbConn.Close
Any idea what are different types in sysobjects table? How can I seperate views only?
select distinct type from sysobjects
type
----
P
U
S
V
K
D
R
I would very much like to create a trigger on the sysobjects table in my database. When I attempt to do so, I get a permission error. Is there some way I can change the permission setting to allow me to do this?
Thanks.
HI there , i have recently upgraded my publishers to 2005.
Transactional replciation seems to be running fine.
However in SS2000 i always used to query sysobjects on replinfo to see if a table was replicaed and how it was replicated, 1 = snapshot , 3 = transactional etc.
However after upgrading replinfo on sysobjects is 0 for all my articles, whats up with this ?
Must i use sp_helparticle only now? Also how does the system know if an object is replicated now , i used to think it used the replinfo column on sysobjects, how is this different in 2005?
Thanx
What kind of problems may I see if a process drop and re-create a set of tables every night?
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
View 8 Replies View RelatedI'm trying to drop a temporary table. I keep getting this error:
Cannot drop the table '#temp_table', because it does not exist in the system catalog.
I tried the following but it did not work, so help would be appreciated.
if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.
What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error
'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.
So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).
Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
Hi all,
how can I drop a column in a table.
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Thanks in Advance,
Phillip M. Triocli
Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran