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
ADVERTISEMENT
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
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
Jul 20, 2005
Hi,Plz, I need some info (SQL2000) :)A stored procedure is like this:"Select table1.id, table1.txt, (select table2.nr from table2 wheretable2.fk_table1=table1.id) as nr where table1.id<>10"The essence here is that "select table2.nr from table2 wheretable2.fk_table1=table1.id" returns either the integer in table2.nr, or NULLif there isnt a match. The whole sentence runs EXTREMELY slow...3-4 sec.What is wrong?"select table2.nr from table2 where table2.fk_table1=table1.id" runs quicklyoutside the stored procedure. The original sentence without the "nr" (Selecttable1.id, table1.txt where table1.id<>10) runs quickly too...But together it slows down dramatically..why? I should mention that thesub-query could return NULL if theres no match in table2...But i cant seewhy that should slow things down (remember - it runs fine outside the SP)?Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.518 / Virus Database: 316 - Release Date: 11-09-2003
View 3 Replies
View Related
Aug 17, 2007
Hi,
I have a website and i am uploading the gif image to the database. i have used varchar(500) as the datatype and i am saving the file in the webserver so the path to it c:intepub....a.gif
my upload table has the folliwing feilds
UploadId Int Identity, Description, FileName, DiskPath varchar(500), weblocation varchar(500). I have a main sproc for the report where i am doing a inner join with other table to get the path of the gif..
So my question is how can i get a picture to show up on the report. .
What kinda datatype the gif file should be stored in the database? If it is stored as a varchar how can i access it and what is best way to reference that particular.
any help will appreciated....
Regards
Karen
View 9 Replies
View Related
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
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
May 17, 2008
I am creating a website. where the data to be stored in my server. I had my webspace from some service provider.
How can i connect the website and thee database.
--> website is deployed at webspace given by the provider.
--> database is at my server( located in our office).
****************How can i connect these website and database.*******************
View 3 Replies
View Related
Oct 6, 2015
I would like to know if is possible copy data from a database located in my hosting to my database located in my pc,but using a store procedure,how can i do that ?.What tool i have to use?
Sql server 2008
View 2 Replies
View Related
Dec 5, 2006
Hello everyone,
I have been playing with a Standard Version of VS 2005 and SSE 2005 and I just cannot get these two to interact together well. I am sure it is a noob problem but I have seen this error addressed on this forum and I am just not getting it. Here is exactly what I am doing.
I want to create a database within SSE using the Management Studio. Then, I want to connect to it with VS2005. Both SSE and VS2005 are local. I just cant seem to get this to work.
I always seem to get this error "An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSecond.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
One thing... I create the website with Location: HTTP. I don't even know anymore why I do this.. I think because I like to be able to pull up a browser and get to my app. Other than that I am doing nothing special. However, I get the above error when I hit F5 to run the app inside VS2005.
Is there someplace where a complete noob can find a thorough and simple covering of this? Again, I have read through some of the previous posts on this and I don't understand too many of them and it seems as if there are at least 13 causes of which I don't know which one I have. Help!
Thanks.
View 30 Replies
View Related
Jan 12, 2015
I have the Image in FTP Server Folder and i need to insert that image into my local database.
How can i do this I tried with the below Query but i shows the errors as below.
--INSERT INTO AcademyStudents (ImageURL)
--SELECT BULKCOLUMN FROM OPENROWSET(BULK'https://iconic-solutions.net/OTA/test/images(1).jpeg',Single_Blob) AS BLOB
--Where StudentIdentificationNum = 2
--GO
GOt Error
;
Cannot bulk load because the file [URL] could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Oct 22, 2007
how can we use 2 databases in 1 stored procedure.
like for example i want to get records from 1 table and insert it to other tables in another database.
thnx for the help.
View 1 Replies
View Related
Dec 28, 2007
I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:
CREATE PROCEDURE sp_UpdateProductsTable
@prodName varchar(50),
@prodManufacturer varchar(50),
@prodShortDescrip text,
@prodLongDescrip text,
@prodCatId int,
@prodPrice varchar(6),
@prodSortOrder int,
@prodActive int,
@prodId int
AS
if( @prodId <> 0 )
begin
update Products set
Name = @prodName,
Manufacturer = @prodManufacturer,
ShortDescription = @prodShortDescrip,
LongDescription = @prodLongDescrip,
CategoryID = @prodCatId,
Price = @prodPrice,
SortOrder = @prodSortOrder,
Active = @prodActive
where ID = @prodId;
select ID as prodId from Products where ID = @prodId
end
if( @prodId = 0 )
begin
insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID, SortOrder, Active )
values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip, @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
SELECT SCOPE_IDENTITY() AS prodId
end
GO
I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.
Thanks in advance for your help.
View 8 Replies
View Related
Feb 27, 2008
From within a stored procedure in an existing database I'm creating a new database from the scratch.
In the query window if I write the statement:
CREATE DATABASE [tomasdb]
USE [tomasdb]
GO
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = '123'
END
everything works ok and the database is created and the stored procedure Test1 is created within it.
However, if I want to do the same from another stored procedure (in another database), i.e. I write
EXEC('
CREATE DATABASE [tomasdb]
USE [tomasdb]
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = ''123''
END
')
the problem I get is that I'm not allowed to put USE to change the execution context into [tomasdb] before the CREATE PROCEDURE statement.
My question is: how can I change the context in this situation / or / can I create the stored procedure in the [tomasdb] database somehow from outside (another context).
Thanks,
Tomas
View 4 Replies
View Related
Feb 6, 2007
Hello,I need to come up with a stored procedure that will allow me to readdata from another database. The database I need to read the data fromis a UniData residing on a Unix server. The stored procedure needs toreside on my sql 2005 server. The task is very simple in Access as wehave ODBC connections set up to the UniData via Informix (or IBM)UniData ODBC drivers. I can easily combine my UniData and Sql Servertables from within access. However, I can't seem to find a way toreplicate the same behavior in MS SQL Stored Procedure without the useof Access. Is that even possible?Thanks,Marek
View 7 Replies
View Related
Jul 20, 2005
As I had real problems working my head around sp_spaceused, I've written anSP to do it (I also noted a lot of questions about this when "searching").Pass in a database name and it will return the size of the database as afloat (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB shouldupdate its size information before giving you the result.Anyway, comments appreciate.ALTER PROCEDURE dbo.proc_BL_Get_Space_Used@DatabaseName NVARCHAR(54),@updateusage BIT,@Size REAL OUTPUTASBEGINDECLARE @dbsize DECIMAL(15,0)DECLARE @bytesperpage DECIMAL(15,0)DECLARE @pagesperMB DECIMAL(15,0)DECLARE @Error INTEGERDECLARE @ExecString NVARCHAR(256)DECLARE @ParmString NVARCHAR(128)SET @Error = 0/*Update usage for this database.*/IF @updateusage = 1BEGINDBCC UPDATEUSAGE (@DatabaseName) WITH NO_INFOMSGSSET @Error = @@ERRORENDSET NOCOUNT ON/*Work out the database size.*/IF @Error = 0BEGINSET @ExecString = 'SELECT @dbsize = SUM ( CONVERT ( DECIMAL (15 ), SIZE ) ) FROM ' + @DatabaseName + '.dbo.sysfiles WHERE (status & 64 =0)'SET @ParmString = '@dbsize DECIMAL(15,0) OUTPUT'EXECUTE sp_executesql @ExecString, @ParmString, @dbsize OUTPUTSET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )END/*and bytes per page.*/IF @Error = 0BEGINSELECT @bytesperpage = LOWFROM master.dbo.spt_valuesWHERE number = 1AND type = 'E'SET @Error = @@ERROREND/*pages per mb*/IF @Error = 0BEGINSELECT @pagesperMB = 1048576 / @bytesperpageSET @Error = @@ERROREND/*and finally, the result.*/IF @Error = 0BEGINSET @Size = CONVERT(REAL, @dbsize) / CONVERT(REAL, @pagesperMB )SET @Error = @@ERRORENDRETURN @ErrorEND
View 1 Replies
View Related
Jul 20, 2005
Is there a stored procedure installed by sql server 2000 that I cancall and just pass in the name of a new database and have it createthe database for me? If not, how do I do it in sql? Thanks.
View 3 Replies
View Related
Jul 20, 2005
I need to create a stored procedure in the master database that canaccess info to dynamically create a view in another database. Itdoesn't seem like it should be very hard, but I can't get it to work.Here's an example of what I want to do.CREATE PROCEDURE create_view @dbname sysnameASBEGINDECLARE @query varchar(1000)SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'EXEC(@query)ENDIn this case, I get an error with the word "go". Without it, I get a"CREATE VIEW must be the first statement in a batch" error. I tried asemicolon in place of "GO" but that didn't help either.Thanks
View 4 Replies
View Related
