How Can I Transfer Stored Procedure From One Database To The Other In Sql2000?

May 21, 2008

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.

View 2 Replies


ADVERTISEMENT

Stored Procedure Transfer - Best Way

Jul 26, 2006

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

View 4 Replies View Related

SQL Server7.0 Stored Procedure Transfer

Jul 9, 2001

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

View 1 Replies View Related

How To Deal With Image Data Type Within Stored Procedure When Using SQL2000?

Dec 5, 2007

Hi,
Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP,  change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.

View 1 Replies View Related

Transfer Logins From Sql2000 To Sql2005

Sep 20, 2005

Hi,

View 4 Replies View Related

Transfer SQL Server Objects Task - Warning: Stored Procedure Already Exists At Destination.

Apr 13, 2006

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

View 4 Replies View Related

I Must Transfer My Stored Procedures To Another Database. How?

Jun 16, 2008

Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
 

View 1 Replies View Related

Table Structure And Data Transfer From SQL2000 To Access (.mdb)

Aug 8, 2006

Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...

View 3 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Transfer Manager Does Not Transfer Stored Procedures

Oct 22, 1998

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??

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

How Can I Transfer Stored Procedure From SERVER A To SERVER B

May 17, 2008

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,

View 3 Replies View Related

Transfer Data(Cubes&#043;virtual Cubes) From Sql2000 To Sql 2005

Jun 6, 2006

Süleyman writes "I want to transfer databases with cubes and virtual cubes from SQL 2000 Analysis Server to SQL 2005 SQL Express.

1.how i can transfer the cubes?

2.how i can transfer the virtual cubes?

3.Why i see the tables(saw)and the cubes in SQL Managment only under Tables together and nothing under cubes?

Please help me
many thanks

Süleyman from Germany/Frankfurt am Main"

View 1 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

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

SQL2005 Cannot Backup A Restored SQL2000 Database With Unknow Database Full-text Catalog Database

Nov 15, 2007



We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message


System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)


Please note we left the DataBaseA in the old SQL2000 server.


Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup


Many Thanks

View 1 Replies View Related

Inserting Record Into Second Database From A Stored Procedure In First Database

Aug 10, 2005

is it possible to insert record into second database from a stored procedure which is in first database?

View 1 Replies View Related

How Can I Identify The Caller's Database From A Stored Procedure In Another Database?

Apr 2, 2008

I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.

This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.

Thanks,
Mike

View 4 Replies View Related

How To Run A Stored Procedure Located In One Database Against Another Database

Feb 14, 2008



I have a stored procedure that is located in one database and I would like to have it execute against a different database. My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?

This is what I have now, which isn't working:




Code Snippet

use [Database2]
exec [Database1].[dbo].usp_SetupDatabaseUser





The end result still executes against Database1.

Thanks for any advice,

Flea#

View 9 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

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?
 

View 1 Replies View Related

Stored Procedure Use Different Database

May 10, 2006

Hi,
I have a set of databases. Each user had his own database. Every database had the same stored procedures. The is one "admin" database where content about these users is stored.
When I want to update or change a stored procedure I have to update it in every database. Therefore I want to put the stored procedures in the "admin" database. From there I have to access the databases of the user. I want to send the database name as a parameter.
How can I change de database which te procedure should access?
If I use the "use" statement I complains about that I can't use that command in a stored procedure. I've tried this method too:
SELECT * FROM @databasename.dbo.tblname
but that's not accepted too.
Does anybody know how to select another database in the stored procedure?

View 3 Replies View Related

Migrating From Sql2000 MSDE Database To Sql2005 Express - Attach Database Errors

Apr 23, 2006

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:


Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.


Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them.  But I should still be able to switch over to sql 2005?!?!?!?


Or... is there a way to attach the databases to SQLExpress manually?

Help pls?

View 1 Replies View Related

Trying To Add Info To Database Using A Stored Procedure

Jul 16, 2004

Hi there :)

I was hoping someone could help me figure out why this doesn't work....

I have a class called ProductsDB with a method called AddProduct that looks like this:


public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);

SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);

SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);

SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);

SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);

SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);

SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);

SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);

SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);

SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);

try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;

return productId.ToString();
}
catch
{
return String.Empty;
}
}



And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:


// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);


And finally, here is my stored procedure:


CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS

INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)

SELECT
@ProductID = @@Identity
GO


Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.

Any ideas?

Many thanks indeed :)

View 1 Replies View Related

Multiple Database Stored Procedure

Apr 18, 2001

I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.

Something like...

Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID

yada yada yada.......

Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?

View 2 Replies View Related

Stored Procedure Database Qualification

Nov 3, 2000

We are trying to develop some standards for SQL stored procedures and I am wandering what is the preferrable method to reference a table within a stored procedure for use within a Client Server / Web OLTP.
One:
SELECT Customer_Id, Customer_Name FROM CUSTOMER

Or TWO:
SELECT Customer_Id, Customer_Name FROM [DATABASENAME].[dbo].[CUSTOMER]

Most of my background is in working with data warehousing which is constantly loading between a staging and main DB, so I am in the habit of using TWO, but is this the best method for an OLTP and why.

Thanks
Loren

View 1 Replies View Related

Passing Database Name To A Stored Procedure

Jul 28, 2004

Hi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve

View 3 Replies View Related

Run Stored Procedure In Specific Database

Jul 20, 2007

I have created a stored procedure in the master database that can be run against any database on the server.
I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?

View 4 Replies View Related

Can A Stored Procedure Access Another Database?

Jun 30, 2004

Hi all,

I have an urgent problem. Can a stored procedure create a connection or access data from another database?

Thanks in advace. :)

View 11 Replies View Related

Restoring The Database Using Stored Procedure

Mar 5, 2005

hey guyz...
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"

i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?


CREATE Procedure spRestoreDatabase
@Path VARCHAR(100)
AS
Restore Database Test From Disk = @Path
GO

View 4 Replies View Related

Stored Procedure To Access Another Database...

Feb 8, 2008

Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!

View 3 Replies View Related

Script Database Using Stored Procedure

Jan 21, 2004

Is it possible to use a stored procedure to script an entire database in MS SQL 2000?

View 8 Replies View Related

How Do You Create A Database Within A Stored Procedure

Apr 20, 2004

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

View 3 Replies View Related

Stored Procedure Not Save Within Database

Feb 8, 2007

I am running Sql Server 2005, When I create a new stored procedure and try to save it, I'm prompted with the 'save file as dialog'. Is'nt the stored procedure suppose to be saved within the database?

View 2 Replies View Related

Select From Different Database In Stored Procedure

Feb 27, 2007

How do I correctly query from a different database table in a stored procedure?

Here is the procedure:

CREATE PROCEDURE spInsertRegistration

@firstname varchar(25),
@lastname varchar(25),
@job_title varchar(50),
@manager varchar(50),
@cost_center char(3),
@address varchar(50),
@city varchar(50),
@state_id INT,
@zip char(5),
@email varchar(50),
@phone char(10),
@roommate varchar(50),
@arrival datetime,
@departure datetime,
@special_needs varchar(500),
@result INT OUTPUT

AS

DECLARE @stateabr VARCHAR(2)

SET @result=0


SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id


INSERT INTO registration_form
VALUES(@firstname,@lastname,@job_title,@manager,@cost_center,@address,@city,@stateabr,@zip,@email,@phone,@roommate,@arrival,@departure,@special_needs)

IF @@rowcount>0
SET @result=1
GO

This is the specific line where I'm trying to access a different database:

SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id

Thank you.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved