Hi
we are having SQL server7.0 with DPT raid card which gives the problem of backup/restore through normal SQL backup in Enterprise manager.So we are not using the above procedure
but we have moved all the tables using SQL EXPORT to another new server.
Can you please give a solution to transfer Stored procedure from the server to new server
Regards
Rajakumar
QuestionWhat is the best way to transfer
Stored Procedures to another db without having to script
it? Or is scripting the best way?
WHYWe need to transfer stored procedures from
one development database to another on different machines not on the
same
network.
PlatformStudio
2005
SQL Server Management Studio Express
advTHANKSance
hi,
I need to transfer my new stored procedure from local database to the database of my site.
What should I do? Could I use DTS ? I have built my databases in SQL2000
Please help me.
Thanks alot.
I am trying to use a Transfer SQL Server Objects Task to copy a portions of tables from one db to a second db.
the connection is using Windows Authentication.
I am logged in as the Administrator
That user has sysadmin rights on the Sql Server in Question.
DropObjectsFirst = True
But when I execute the task I get the follwing warning
[Transfer SQL Server Objects Task] Warning: Stored procedure "{0}" already exists at destination.
What am I missing?
Thanks,Kim
I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
How can I transfer stored procedure from SERVER A to SERVER B, running SQL Server.
When i choose Export/Import Wizard, it only let me transfer Tables and views , but not SPs,
Many Thanks,
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
hi,
i'm new to this sql server thing. i hope somebody can help me out!
i would like to find a way to determine whether or not the automated
backup of the database has been done without using the sql mail
functionality. i'm not allowed to use that because it only runs for
the microsoft outlook and it cost too much space. the server is in one
of our clients and they do not have a db admin, and nobody wants to go
there can i do something????
Does anyone know the advantages of using a view over a store procedure? Are there any proformance differences?
thanks
steve
I just brought SQL Server 7.0. Previously i am using Access97 . I am building some Asp stuffs using Access
Database. NOw i move the database Access to Sql server. I am planning to install this sql in separate machine
in the network.
After the installation, i need to use the database for my Asp programs..
I would like to know what are the things that i should keep(follow) while installing SQL Server 7.0.
Also i need to know some Admin procedure..Where i can found..?
Help will be appreciated.
Thanks
rams
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I'm worhing with SQL Server7.0 and I have created a database.
I have objets and data in my database
My database have 300Mb and I only need about 50.
How can I reduce to 50 the dimension of my database.
Is it posible??
Any advice will be greatly apreciatted.
Nauj
Can SQL server7 restore a single table? If one table corrupted, do I have to restore the whole database for this table ?( It is awkward to restore the whole database only for a single table! especially the database is large!)
thanks,
I have a full backup done on a very important server 7 database. Now i have to test to see if it is saving okay and to do a mock restore to see how quickly we can restore same. The size of the file is about 700M. I would have to do my testing on a test server. How do I go about this.
Thanks.
Hello,
Can anyone please tell me the best way to upgrade server1 with SERVER7 and OS of NT to a new server2 with SERVER2000 AND WINDOWS 2000 ?
The data in the old server is very important.
Thanks
HelloDoes anyone one know the max size of a database that I can create onMS-SQL server 7. Have had any problems using databases near the maxsize (slow performance, etc)ThanksHelena
View 2 Replies View RelatedHi
I have an excel file that I would like to put into SQL so I can run queries on it. Is there any way I can create a procedure that will pull my info from my excel file to SQL? Any suggestions would be helpful.
Thanks!
Wendy
Hi,
The company finally bought a legitimate copy of SQL Server 7. There was nothing special on my trial version, so I chose the uninstall option from the start menu (it was on my Win95 PC). It removed the files, but didn't uninstall the registry bits though! When trying to install the proper version of SQL 7 it has detected the other version, but no files & won't let me install the legitimate version. Can anyone tell me how to get around this to install the new version?
Thanks
Steve
I posted this problem last week and still couldn't get this to work.
I have a backed up copy of a production db that i want to test. The production server is using server 7 white i have 2000 at my desktop. How can i get this to work ? I am beginning to 'lose' it.
Any help would be greatly appreciated. I would like to complete this test today.
Thanks
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Hi,
Whilst not new to online database and web design I'm relatively new to ASP.NET and SQL Server. Can you guide me in this final hair tearing problem.
If I have a development system on my local machine and a live system on the web how do I achieve the following:-
If I make a change to a local Stored Procedure (or add a new one) after testing how do I transfer the changes to the Live Site?
Similarly if I add/amend a field to the database how do incorporate that change on the live site?
I can't find any tutorials on this on the net (probably because I don't know which terms to use in my search)
Thanks in advance
I'm trying to get everything I need for membership transferred from my local ASPNetdb.mdf file to another server.
When I use the Management Studio and do an Import or Export of data - it only gives me the Tables or Views as a possibility -
How can I transfer the stored Procedures from a SQL Express database to a SQL 2000 database?
Hi,
I have around 25 stored procedures kept in a DB1
I want to transfer all of them to DB2. How can i do that.
I tried Database Publishing Wizard to generate all stored procedure script, but it generates the scripts for table as well.
thanks.
Hello,
When I try to create a primary key for a table(alter table tablename add primary key( columnname)), I get a message
" SQL 1918. ( Index (key=2) is being rebuilt)."
Does any one have a clue, as to why do I get this. The primary key does get created ,but my application stops working, after getting this message.
Thanks,
Mitesh
Hello,
I have to do an upgrade from server 7 and OS NT to server2000 and windows 2000. Has anyone had any problems while doing this ? Please list the steps taken for the upgrade if you can.
Thanks
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
How do you transfer stored procedures from SQL Express 2005 to MS SQL 200 ? I am using the Personal home page starter kit and also need to know if I have to attach and detach the database to my ISP's server?thanks,
View 2 Replies View RelatedI have two databases named A and B. They are exactly same table structure, but the records are different, and we have near 400 stored procedures and over100 views in the database A. Now my question is that how can I transfer all stored procedures and views from database A to database B?
Thank you very much!
Hi,
I uploaded a database to a remote server (ISP) who has SQL2005 as do I. They got the database okay, but not all the stored procedures went along for the ride
Can anyone tell me how to just simply transfer the stored procedures only to the remote server?
Failing that, can you tell me how to export the entire database and be sure that it all goes, not just the tables. I assume you would have to do this via a script because the export wizard only lets you copy the files and I have dome this but the stored procedures do go along.
Any help?
Tia