Exporting Stored Procedures In Sql 2005 From Database To Database?
Apr 14, 2008
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...
View 5 Replies
ADVERTISEMENT
Feb 13, 2008
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>
View 1 Replies
View Related
Mar 19, 2007
1). 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?
View 3 Replies
View Related
Apr 14, 2008
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
View 5 Replies
View Related
Jan 14, 2008
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
View 10 Replies
View Related
Jan 31, 2008
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 Related
Apr 24, 2007
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
View 2 Replies
View Related
Feb 13, 2005
I have to retrieve all the stored procedures in a certain database via a aspx page. My problem is that I have basically no idea how to do it. I did manage to get all the data as XML and reconstruct the database (pain). All I need now (I hope) is the sprocs. I do not have any other access to the database. And I'm a novice with SQL Server. Any guidance is appreciated.
Steve
View 5 Replies
View Related
Nov 8, 2004
Hi,
Does anyone know how to export all stored procedures attached to a database (MS SQL Server) into a sql script. Please help.
Thanks
View 2 Replies
View Related
Mar 7, 2008
Hi anyone know of a quick way of copying Stored procedures on SSMS ?
I just need to back a bunch of SPs up.
I don't need to export them just to save them as files, I can do it but its one at a time
and is really slow.
View 3 Replies
View Related
May 31, 2006
I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?
Thanks!
View 3 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
May 13, 2008
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!
View 5 Replies
View Related
Mar 1, 2008
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
View 2 Replies
View Related
Jun 16, 2008
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
View 1 Replies
View Related
Apr 24, 2007
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.
View 1 Replies
View Related
Jul 20, 2005
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 Related
Apr 19, 2007
How do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies
View Related
Aug 17, 2000
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
View 1 Replies
View Related
Aug 31, 2006
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
View 2 Replies
View Related
Dec 16, 2005
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
View 1 Replies
View Related
Feb 12, 2015
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?
View 4 Replies
View Related
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.
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.
View 1 Replies
View Related
Jul 20, 2005
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 Related
Oct 9, 2006
Hello,
Is there a way to copy some selected or all stored procedures and functions from one database to another?
Thanks in advance.
Best regards,
View 3 Replies
View Related
Mar 21, 2008
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!!!
View 4 Replies
View Related
Apr 24, 2015
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...
View 4 Replies
View Related
Sep 19, 2006
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
View 2 Replies
View Related
Oct 31, 2014
is there a way to backup all stored procedures in a database?
View 4 Replies
View Related
May 16, 2007
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
View 1 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Feb 6, 2008
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
View 24 Replies
View Related
Jun 15, 2015
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
