Stored Procedure Does Not Appear In The Sysobjects Table.
Jul 23, 2005
Hi,
I'm trying to determine with my program whether or not a given database
supports a given feature set. To do this I'm querying for certain stored
procedures in the sysobjects table and if they are present, making the
assumption the database will support the given feature. The problem is I
can't find a certain stored procedure in the sysobjects table, even though I
know it exists and can see other similar procedures using:
select * from dbo.sysobjects order by name
Its as if all of my other stored procedures are in the sysobjects table
except this one, the one I'm specifically querying for. Are there certain
reasons why a proc won't appear in the sysobjects table? Is this something
I need to fix?
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.
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.
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.
All 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.
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Simple example: declare @tTable(col1 int) insert into @tTable(col1) values (1) select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
Hi, I am having a SP which returns two Rowsets: create proc GetSalesDetails as select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006' select ProductName from Products where SalesDate > '10/10/2006'
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible? Marius G.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi guys I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order . The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that. Any Idea?
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
I've got a stored procedure which should update a table (add new customer record) When I run it locally everythings fine,
Since uploading it all to the web it no longer seems to add a new record, I've debugged it and it seems that the output parameters is set to nothing.
I believe it's a permissions issue but the user i'm using has full access to both the table and permission to execute the stored procedure is there any error handling I can do to capture the exact error? the code I use to execute the sProc is below
I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure. This is what I have so far, but it does not allow me to run it.
alter procedure dbo.createNewBUtable ( @BU as varchar(50) ) as set nocount on; create table @BU ( BUid varchar(50) primary key, BUinfo varchar(50) )
Hi, I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
I can't script the table because this process must be automatic no manual.
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
Hy , How is the stored procedure, to copy from a table to others tables? I have a stored procedure which is doing that, but for 1 registration : " insert into....". So i want for each registration in the source table to execute this procedure and put the data in my format table.
I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.
I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.
The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.
The table looks like this: Logid PK LogTypeID -- what category LogValue -- LogTime -- when it occurred
My imaginary stored procedure looks something like this:
CREATE PROCEDURE ShrinkDB AS -- 1) "lock table log" -- do I have to do something like this?
-- 2) select * into log_keep FROM Log where ( logtypeid <> 2020 AND -- activity played logtypeid <> 5020 AND -- database connected -- ....etc et..... about 10 different things I don't need to keep or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours ) -- 3) drop table log
-- 4) EXEC sp_rename 'log_keep', 'log' GO
I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??
Hopefully it works that way, but if not I assume I'll run into these two problems:
- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?
- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?
Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject.... :-)
