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?
Thanks,
Robin
View 4 Replies
ADVERTISEMENT
Nov 13, 2007
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.
View 2 Replies
View Related
Dec 26, 2007
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.
View 6 Replies
View Related
Jan 18, 2008
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?
View 7 Replies
View Related
May 22, 2007
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.
Thank you, thank you, thank you!
View 1 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Mar 27, 2006
Hello to all!
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...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
Jul 19, 2012
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
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.
View 11 Replies
View Related
Apr 18, 2007
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
RETURN
END
View 2 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
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.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
Jan 31, 2006
I am working with the following two tables:
Category(NewID,OldID)
Link(CategoryID,BusinessID)
All fields are of Integer Type.
I need to write a stored procedure in sql 2000 which works as follows:
Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)
Then for each rows fetched from last query, execute a update query in the Link table.
For Example,
Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..
UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID
Please help me with the code.
Thanks,
anisysnet
View 1 Replies
View Related
Sep 8, 2006
Hello
Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?
insert #MyTempTable
exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3
JOIN dbo.OtherTable...
I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.
I just can't figure it out
Many Thanks!
Worf
View 2 Replies
View Related
Jul 23, 2005
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!
View 1 Replies
View Related
Apr 25, 2006
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.
View 1 Replies
View Related
Jan 29, 2013
I've never used a stored procedure before - let alone created one. how to append records from table A to table B.
View 1 Replies
View Related
Oct 11, 2007
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?
Cheers,
Soumen
View 3 Replies
View Related
Mar 9, 2004
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.
View 2 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
Aug 28, 2007
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?
View 8 Replies
View Related
Nov 12, 2007
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
View 2 Replies
View Related
May 17, 2008
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?
View 4 Replies
View Related
Jan 20, 2004
Hi,
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
thanks for any help
Dave
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
' Calculate the New CustNo using Output Param from SPROC
Dim custNo As Integer = CInt(parameterCustNo.Value)
Return custNo.ToString()
Catch
<---This is where it's dropping in can I put any
Error handling in to show me the error?
Return String.Empty
End Try
View 3 Replies
View Related
Apr 23, 2004
I have a flat table from 1 of the clients with a lot of fields (more then 100) like this
Item F1 F2 F3 .... F(N)
---------------------------------------------
100 X X
101 X
There are more 10000 records , X is the data inside the field.
I need to quickly convert it to this table
Item FieldNumber Value
----------------------------
100 1 X
100 2 X
101 1 X
Any ideas ?
Thanks
Mikhail
View 3 Replies
View Related
Feb 10, 2005
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)
)
View 4 Replies
View Related
Apr 25, 2006
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.
View 2 Replies
View Related
Feb 16, 2001
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.
Thank you
View 1 Replies
View Related
Nov 20, 2001
Hi All,
Can any one give me code sample which uses the recordset returned by the execution of a stored procedure as a derived table.
Thanks,
venkat.
View 1 Replies
View Related
Feb 16, 2001
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.
Thank you
View 1 Replies
View Related
Feb 28, 2006
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.
View 2 Replies
View Related
Jun 2, 2012
I am Creating a Procedure in SQL2000 but founding an error.
Procedure:
CREATE PROCEDURE Itmopbalance
@myfitemcode int,
@ttype char(1),
@myfyear char(9),
@mydatefrom2 datetime,
[code]....
View 3 Replies
View Related
Jan 29, 2004
Hi,
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.... :-)
View 5 Replies
View Related
