Sysdepends And Compiling Stored Procedures In The Correct Order
Nov 9, 2000
Hello:
We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.
As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:
"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"
And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.
I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.
Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not
My question is:
1) Is there a way I can read and understand what the data means in sysdepends?
2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?
3) Any other suggestions?
Any information which can be proveded will be greatly appreciated. Thanks.
I have 3 tables: authors, companies, and countries.
I have a stored procedure defined as:
PROC addAuthor(AuthorName, CompanyID, CountryID) INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID) END PROC
....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.
Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example
PROC addAuthor(AuthorName, CompanyName, CountryName) SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName IF v_numRows = 0 -- error END IF
SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName IF v_numRows = 0 -- error END IF END PROC
yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.
What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?
We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables.
- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables.
- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2) create PROCEDURE [dbo].[SP_tmpSil] AS
declare @tablo1 as varchar(50), DECLARE @sil as nvarchar(max) select @tablo1='[tmptabloA]'
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name : (3) create PROCEDURE [dbo].[SP_yordam] AS BEGIN
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
I am attempting to compile a stored proc that contains SQL statements that access databases across different SQL servers. I am getting the following error:
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."
Does this mean that I need to put the necessary statements (Set ANSI_NULLS ON etc.) in my stored proc or that I need to configure my SQL Server differently somehow? I tried the former without success.
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
insert into Sales_Table select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date fromorg_model_data omd join org o on o.org_id = omd.org_id where o.client_id = @ClientID and model_Criteria_id = 27 and org_criteria_value <> 0 order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR SCROLL dynamic FOR select org_id, sales, sales_rank from Sales_Table for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0) Begin
FETCH NEXT FROM SalesRankCursor
set @StoreNext = @StoreCurrent set @SalesRank = (@SalesRank + 1)
update Sales_Table set@StoreCurrent = org_id where current of SalesRankCursor
if @StoreCurrent <> @StoreNext begin set @SalesRank = 1 end
update Sales_Table setsales_rank = @SalesRank where current of SalesRankCursor
I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).
Value for variable psRunNo = 0154
In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.
Expression for S_SOURCE_FILE_NAME = @[User:: psRunNo] + "_{TABLENAME}.txt"
So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.
Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:
So here for example, the final value for ConnectionString would be 0154_STADDRES.txt
Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.
It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).
Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ??
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITHΒ MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTIONΒ MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINALΒ SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue],Β
I have 13 parameters, ordered correctly within the Report Parameter screen.
When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.
I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.
Please can someone suggest how I can get the parameters to appear in the correct order within the application?
My Problem ========= I have a problem with a parameter drop down that is on my report. On one report server (i.e. a SQL Server Reporting Server instance), the drop down data is ordered correctly. On another, it appears to be random.
The report is created using Report Builder v9.0.2047.0.
I have used SQL Server Profiler and I have found that an "order by" clause is appended on one of the report servers, while it is excluded on another.
About my Set-up ============ I have configured multiple report servers (i.e. SQL Server Reporting Server instances) on a single SQL Server 2005 instance. The SQL Server 2005 details are: productversion = 9.00.3054.00 productlevel = SP2 edition = Standard Edition (64-bit)
Another post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1379591&SiteID=1) puts this problem down to SP2. However, both my report servers are on the same SQL server instance.
I also see that Bob has a blog on a possible solution for this: http://blogs.msdn.com/bobmeyers/archive/2007/10/11/sorting-the-values-in-parameter-dropdowns-in-report-builder.aspx. However, I would like to understand why the report is behaving differently.
I have some C# code that is pulling data from a database where a majority of the values being retrieved are NULL , yet their initial column data types are both string and int, which means that I have to temporarily store these NULL's in int and string data types in C#. Later on in my code I have to test against these values, and was wondering if I am doing it correctly with the following code.
The following statement the variable or_team_home_id is of a string data type, but may have had a NULL value assigned to it from the database if (!or_team_home_id.Equals(DBNull.Value)) {}
The following statement the variable or_manager_id is of a int data type, but also may have a NULL value assigned to it from the database. if (!Convert.IsDBNull(or_manager_id)){}
Are these the correct way to test against NULL values retrieved from teh database and that are stored in their respective data types.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
I know I shouldn't rely on sysdepends because it's got all sorts of problems with broken dependency chains, etc. But it's better than nothing for finding dependencies, short of rolling your own t-sql parser. So I use sysdepends anyway, with all its faults. I just don't rely on it. anyway, to the point, just an fyi really:
Here's something about sysdepends discovered today that annoys me. If you introduce a dependency of a proc on a table by selecting from the table in the proc, the sql parser discovers this and dutifully adds a row to sysdepends. Very well. But if you do the same select into a temp table, sysdepends doesn't pick up the dependency! Here's what I mean:
use master go
drop database test_sysdepends go
create database test_sysdepends go
use test_sysdepends go
create table MyTable01 (id int) go
create proc MyProc01 as select id from MyTable01 go
create proc MyProc02 as create table #t (id int)
-- comment this next line out and the dependency is picked up. -- but as long as we insert into the temp table, we don't pick it up... :( insert into #t select id from MyTable01
select id from #t go
select so2.name as parent ,so1.name as dependent from sysdepends d join sysobjects so1 on d.id=so1.id join sysobjects so2 on d.depid=so2.id go
I am currently experiencing an issue where the syscomments and sysdepends tables are increasing exponentially.
For example, i have a database with data segment of 666 MB (maybe its the number!) and the syscomments is 254MB and sysdepends is 325MB. all the other tables are remaining static, and over my sample period have not increased. Also there have been no new procedures or views created on this database, so I am not sure why these two tables are increasing so much.
Does anyone have any ideas as to what is causing this? or have any pointers for things i should be checking?
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
I've been building and running script tasks for years without issue. Then all of sudden last week Visual Studio starts showing the "Task is configured to pre-compile the script, but binary code is not found" error anytime I open a script -- even I make no changes. If I copy the dtsx package to another machine, the script compiles fine and I can see the binary data in the raw .dtx file -- so I know the code is correct.
I can also reproduce the error simply by adding a new script task, going into "Design Script", make no changes to the default code (which is basically one line: Dts.TaskResult = Dts.Results.Success) and simply press "OK" on the Script Task Editor.
I've been trying to find some VS setting somewhere that might stop the script IDE from producing the binary code, but I can't. It doesn't seem to be project setting, because all my SSIS projects are now suffering this problem on my main dev box.
I have now created a few simple SSIS packages. In BIDS I right-clicked on a solution and selected "Build". I went to the "bin" directory hoping to find a standalone ".exe.dll" file but found only a ".dtsx' file.
Can BIDS can actually build a standalone exedll or is the .dtsx file all that's available? (If so, I guess that another program is expected to invoke the .dtsx file.)
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out whatthe problem is? Can someone please help me out? I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it withsuccess. If I trace into the C# code though only a negative one (-1) is returned. On line 5 I have tried the alternate lines of code but this has not worked for me. mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output; mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue; Can someone please help me out. I have spent to much time trying to figure this one out. // C# code to call stored proc.1 try2 {3 SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);4 mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;5 mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;6 mySqlCommand.CommandType = CommandType.StoredProcedure;7 RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();8 } // Stored procedure below.USE [REALTOR]GO/****** Object: StoredProcedure [dbo].[GetRecordAssociatedWithRealtor] Script Date: 10/18/2007 13:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]( @RealtorId int, @TotalRecords int output)AS BEGIN DECLARE @HouseDetailRecords int DECLARE @RealtorRecords int SELECT @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId SELECT @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId SET @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords) RETURN @TotalRecordsEND
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first: I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like. To add a simple image to a given album, I am trying to do the following: * Retrieve name, description from the UI * Insert a new row into images with this data * Get the ID from the newly created row * Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
