Want To Run Stored Procedures To Build Maintenance Database
May 13, 2015
create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.
I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.
Basically I would like to ask whether parameters can be used to pass the value of the 'symmetric key id', 'certificate' and optionally 'password' to a stored procedure that uses encryption functions.
The reason this is appealing is that when encryption keys etc change over time (we have a requirement to decrypt data, destroy and create new keys, then encrypt data every time we lose a staff - don't ask), as we would be passing the value of keys, passwords and certificates as parameters to a standard stored procedure.
I have a new SQL 2005 Standard Instance. Server Collation is Latin_General_CI_AI - which should be the default or Norm. build 9.00.3042
I cannot Create Maintenance Plans. All error messages below. Some of the links point to help which address possible wrong collation - but my Server Collation is Latin_General_CI_AI - which should be the default or Norm.
Has anyone ever seen this before or can anyone offer any suggestions? is there a post sp2 patch to fix this????
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------ ===================================
Saving maintenance plan failed.
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.GetSubPlanJob(ServerConnection userConnection) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.UpdateAgentJob(ServerConnection localConnObj, MaintenancePlan maintPlan) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.OnMaintenancePlanSaved(MaintenancePlan maintPlan) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.OnSave() at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.Save() at Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query) at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent) at Microsoft.SqlServer.Management.Smo.Environment.GetData() at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci) at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
===================================
Invalid column name 'msx_job_id'. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------ Server Name: KOCSQLDEV01 Error Number: 207 Severity: 16 State: 1 Line Number: 5
------------------------------ Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
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'
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?
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
Hello everyone,I face currently a problem where I could need some input for searchingthe source of the ProblemSystem: SQL Server 9.0I fill from Database A with triggers Database B, everything worksfine.On Database B there is a Stored Procedures that checks the records andadd additional information accordingly, this Stored Procedures isnormally called by the application on "update and insert" in theaccording table.When I try to call this Stored Procedures from the Database A, thetrigger does not work anymore, even if I do a try catch over the wholetrigger, he never reach the Catch and the insert I try to do there toget the error message.On both Databases the user, that is taken to execute the trigger isexistent and DB-Owner of both Databases.If I go and execute the Stored Procedures manually after an insert orupdate to Database B everything works fine.I also already tried to check on Database B if there is an insert orupdate from Database A and if, to execute the Stored Procedures, withthe same result, nothing and all happens anymore, neither update onDatabase A and also not on Database B.And also I cant catch the error as the Try/Catch is not working.Hope I could explain it understandable and maybe someone remembersalready having the same problem.Thanks & Best regardsPascal
I've never dealt with stored procedures much.. but i have a new database created.. imported the tables, but the stored procedures didnt get copied over..
Is there an easy way to export them.. perhaps to a .sql file ... then import them or run a script on the other database..
I have never done much with the query window before, so i'm not sure how to handle this.. as there are around 20 stored procedures that need imported..
is there a way to copy whole stored procedures to another database? I have aproximately 80 SPs in a database, I want to copy whole SPs into another DB. Is there a practical way to copy them? Thanks
I am attempting to compile a list of questions that will enable meassess 'at risk' stored procedures that need to be remediated in orderto minimize unplanned downtime and enhance database performance. Isthere a subset of criteria (parameters) from the MS SQL Server BestPractices Analyzer that can be used for this purpose? Any pointers tothe appropriate documentation is most welcome.Thanks in advance.
I have 2 databases, one that we use called MyShop and one that I develop oncalled TestShop.After I have a stored procedure working the way I want in TestShop , isthere a way to just copy the SP to the other database without the copy andpaste method?. Same if I have a new table. Any way to add it in withoutrecreating it in the MyShop database?I am using Sql Server 2000ThanksAndy
I have read that it is possible to configure sql server express so that the database can only be accessed through stored procedures. Can anyone tell me how to do this. Many thanks.martin
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail"). I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using: Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End Sub When I try to run my application I'm getting an error which reads: Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated. The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong! The code I'm using for my stored procedure is as follows: ALTER PROCEDURE AddCustomer ( @CustomerID int, @Email nvarchar(50), @Password nvarchar(MAX), @Name nvarchar(50), @Address1 nvarchar(50), @Address2 nvarchar(50), @Address3 nvarchar(50), @City nvarchar(50), @County nvarchar(50), @PostCode nvarchar(50) ) AS INSERT INTO Customers (Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) VALUES (@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode) I'd be really grateful if anyone could help me out with this. Thanks in advance, Luke p.s. just incase it helps, here's my createaccount.aspx page: <%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %> <%-- Add content controls here --%> <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1"> <span style="text-decoration: underline"><strong>Create Account<br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)"> <SelectParameters> <asp:Parameter Name="CustomerID" Type="Int32" /> <asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Password" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address1" Type="String" /> <asp:Parameter Name="Address2" Type="String" /> <asp:Parameter Name="Address3" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="County" Type="String" /> <asp:Parameter Name="PostCode" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /> </strong></span> <table style="font-weight: bold; width: 394px; text-decoration: underline"> <tr> <td style="width: 111px; height: 21px; text-align: left"> Email:</td> <td style="height: 21px"> <asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Password:</td> <td style="height: 21px"> <asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Name:</td> <td style="height: 21px"> <asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 1:</td> <td> <asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Address 2:</td> <td style="height: 21px"> <asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 3:</td> <td> <asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> City:</td> <td> <asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> County:</td> <td style="height: 21px"> <asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Post Code:</td> <td> <asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td> </tr> </table> <asp:Button ID="btnSubmit" runat="server" Text="Submit" /> </asp:Content> Thanks again
Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ
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 have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I have this requirement where some store procedures from a "seed" database need to be replicated to another database (on demand, so replication is not suppose to be use in this scenario).
I know it can be achieved by exporting the store procedures and then execute that at the B database but I want something a bit more automatic since it can be a large number of sprocs. I am trying something like this (still in dev):
SET NOCOUNT ON; -- SELECT ROW_NUMBER() OVER(ORDER BY definition) seq, definition base into #sprocs FROM databaseA.[sys].[procedures] p INNER JOIN databaseA.sys.sql_modules m ON p.object_id = m.object_id
[Code] ....
But I am sure there are way better ways to accomplish that...
While doing maintenance on a development server we detached a database and later reattached it to the same server.
For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.
It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.
Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.
Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp
In order to minimize the number of connection strings I have to use toaccess different databases on the same Sql Server, I was consideringstoring all stored procedures in just one database. I want to do thisbecause connection pooling in my application - ASP.NET is based onthis connection string. So if I need to access 6 different databaseson one sql server & set 6 different connection strings, I end upcreating 6 different connection pools.Other than it might create more management work for the DBA, are thereany performance implications with implementing this scheme? Do storedprocedures run any slower if they access tables that are stored indifferent databases within the same server?Any comments/suggestions are appreciated.TIA,Minh Tran
Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.
Hence, I wanted to write a script, Here the code below.
-- These two variables must contain a valid database name. DECLARE @vchSearch VarChar(15), @vchReplacement VarChar(15)
SET @vchSearch = 'Search' SET @vchReplacement = 'Replacement' /* -- Select the Kaplan Database Names in the Current Server */
DECLARE @tblDBNames TABLE (vchDBName VarChar(30)) INSERT INTO @tblDBNames SELECT Name FROM MASTER.DBO.SYSDATABASES WHERE Has_DBAccess(Name)=1 And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')
--SELECT * FROM @DBNames
IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames) BEGIN PRINT 'Not a Valid Search DB Name' GOTO Terminate END IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames) BEGIN PRINT 'Not a Valid Replacement DB Name' GOTO Terminate END
-- We have Valid DB Names, lets proceed... --USE @vchReplacement
SET @vchSearch = '%' + @vchSearch + '..%' SET @vchReplacement = '%' + @vchReplacement + '..%'
-- Get Names of Stored Procedures to be altered DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))
INSERT INTO @tblSProcNames SELECT DISTINCT so.Name FROM SYSOBJECTS so INNER JOIN SYSCOMMENTS sc ON sc.Id = so.Id WHERE so.XType='P' AND sc.Text LIKE @vchSearch ORDER BY so.name
-- Now, the table @tblSprocNames has the names of stored procedures to be updated. -- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to -- update the database reference -- Then, use cursors to loop through each stored proc and upate the reference
Now, I have got stuck how to extract the body of a stored procedure into a variable.
Please Help.... I dont want spend weeks of time in the future to do this work manually.
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time. I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it. When I execute stored procedure defined in dbo with query against dbo tables, it works as expected. However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain. I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp. The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.
I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.
