I am attempting to compile a list of questions that will enable me
assess 'at risk' stored procedures that need to be remediated in order
to minimize unplanned downtime and enhance database performance. Is
there a subset of criteria (parameters) from the MS SQL Server Best
Practices Analyzer that can be used for this purpose? Any pointers to
the appropriate documentation is most welcome.
Thanks in advance.
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'
Greetings:
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?
Thanks!
I have a sql server 2005 with a number of databases running on it. Now I need to move several dbs from 2000 over to this 2005, what measures will I need to take to ensure that the 2005 box will be able to handle all the dbs and perform normally after the move?
Thanks all in advance.
Hi all,
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()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
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;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
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.
Thanks in advance,
Scott Chang
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
View 1 Replies View RelatedHello 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
View 2 Replies View Related 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..
Thanks for any tips...
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
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
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
View 1 Replies View RelatedHow do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies View RelatedI 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
View 2 Replies View RelatedHello,
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
I'm looking for an easy way to run all stored procedures in a database that match a specified criteria.
Under normal curcumstances, I'd create a "master" procedure that would call each one in turn using the "EXECUTE" syntax.
As there will be around 140 procedures (there are a few more but they are used for different purposes), I'd like to try and execute them dynamically.
Is there any way of finding out which procedures match the pattern of "usp_merge_*" for the name and executing it?
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.
sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt
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.
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
View 4 Replies View RelatedHello,
Is there a way to copy some selected or all stored procedures and functions from one database to another?
Thanks in advance.
Best regards,
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!!
View 11 Replies View RelatedI 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.
Could use a few suggestions at this point.
Larry
is there a way to backup all stored procedures in a database?
View 4 Replies View RelatedHi 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
View 1 Replies View RelatedIn 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
View 4 Replies View Related1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues.
I can't find this functionality in the SQL Server Management studio.
2). By the way I also can't find where went the output of my print statement.
3). Does server cursor functionality is still working in 2k5?
Hi There,
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.
Madhu
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.
View 8 Replies View Related
Hello,
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.
Thanks,
IT
I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.
My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.
I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true
I've tried two approaches... one utilizes my stored procedure....
bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}
Then I tried a direct insert...
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}
Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.
Thanks for whatever help you can offer...
Jim-
Hi,
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.
Thank you in advance for any help on this matter
Hi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;