I know how to change the schema of a table in SQL server 2005: ALTER SCHEMA NewSchama TRANSFER dbo.Table1 But how can i check and/or alter stored procedures that use the old schema name? Sorry: I mean: There are stored procedures that have the old schema name of the table in the sql of the stored procedure... How can i edit all the s...
I am trying to execute some stored procedures in groovy way. I am able to do it quite easily by using straight JDBC but this does not seem in the spirit of Grails. I am trying to call the stored procedure as- sql.query( "{call web_GetCityStateByZip(?,?,?,?,?)}",[params.postalcode, sql.out(java.sql.Types.VARCHAR), sql.out(java.sql.Type...
What are good methodologies for creating sprocs that reduce the pain of debugging? And what tools are out there for debugging stored procedures? Perhaps most importantly, what are indications to look out for that errors are happening in a sproc and not in the code? I hope I'm not all over the board too terribly bad here. Votes for answe...
We have a classic ASP application that simply works and we have been loathe to modify the code lest we invoke the wrath of some long-dead Greek gods. We recently had the requirement to add a feature to an application. The feature implementation is really just a database operation requires minimal change to the UI. I changed the UI and...
What tools do you use to develop Oracle stored procedures, in a team : To automatically "lock" the current procedure you are working with, so nobody else in the team can make changes to it until you are finished. To automatically send the changes you make in the stored procedure, in an Oracle database, to a Subversion, CVS, ... reposit...
I have writtent some Oracle storedprocedures in these there are more then 20 input parameters and from them morethen 10 parameters are required , I want all with some value and do not want to accept null values for that , Is there anything that I can declare in the Procedure defination itself which can restrict null input parameter or Wi...
The question is a fairly open one. I've been using Stored Procs with MS SQLServer for some time with classic ASP and ASP.net and love them, lots. I do have a small amount of experience with PHP4 and mySQL, and previously when working with these was limited to in-line SQL. I have a small hobby project I'm working on and for various rea...
I have a huge database with some 100 tables and some 250 stored procedures. I want to know the list of tables affected by a subset of stored procedures. For example, i have a list of 50 stored procedures, out of 250, and i want to know the list of tables that will be affected by these 50 stored procedures. Is there any easy way for doing...
Hello! I have an MSSQL2005 stored procedure here, which is supposed to take an XML message as input, and store it's content into a table. The table fields are varchars, because our delphi backend application could not handle unicode. Now, the messages that come in, are encoded ISO-8859-1. All is fine until characters over the > 128 stan...
I have a huge database with 100's of tables and stored procedures. Using SQL serve 2005 How can i get the list of Stored procedures that are doing a insert or update operation on a table. ...
My stored procedure is called as below from an SQL instegartion package within SQL Server 2005 EXEC ? = Validation.PopulateFaultsFileDetails ? , 0 Though i'm not sure what the ? means ...
I have an SP that takes 10 seconds to run about 10 times (about a second every time it is ran). The platform is asp .net, and the server is SQL Server 2005. I have indexed the table (not on the PK also), and that is not the issue. Some caveats: usp_SaveKeyword is not the issue. I commented out that entire SP and it made not difference...
Everything I have read says that when making a managed stored procedure, to right click in Visual Studio and choose deploy. That works fine, but what if I want to deploy it outside of Visual Studio to a number of different locations? I tried creating the assembly with the dll the project built in SQL, and while it did add the assembly, i...
If I need to copy a stored procedure (SP) from one SQL Server to another I right click on the SP in SSMS and select Script Stored Procedure as > CREATE to > New Query Editor Window. I then change the connection by right clicking on that window and selecting Connection > Change Connection... and then selecting the new server and F5 to run...
I am actually working on SP in SQL 2005. Using SP i am creating a job and am scheduling it for a particular time. These jobs take atleast 5 to 10 min to complete as the database is very huge. But I am not aware of how to check the status of the Job. I want to know if it has got completed successfully or was there any error in execution. ...
I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the beginning of SP and deleting it in the end. I am now debugging the SP in VS 2005. In between the SP i would want to know the contents into the temporary table. Can anybody help in in viewing the contents of the temporary table at run ...
I have several long running report type transactions that take 5-10 minutes. Would I see any performance increase by using stored procs? Would it be significant? each query runs once a night. ...
On Sql Server 2000, is there a way to find out the date and time when a stored procedure was last executed? ...
Is it possible to reference a .NET Assembly from a SQL Server Stored procedure or function, or otherwise access the clr code from SQL Server? EDIT Whilst this solution will require to be somewhat generic, I am fairly confident expecting SQL 2005+ ...
Ok, this is a curly one. I'm working on some Delphi code that I didn't write, and I'm encountering a very strange problem. One of my stored procedures' parameters is coming through as null, even though it's definitely being sent 1. The Delphi code uses a TADOQuery to execute the stored procedure (anonymized): ADOQuery1.SQL.Text := "ex...