Stored Procedure Querying Another Database
Feb 6, 2007
Hello,
I need to come up with a stored procedure that will allow me to read
data from another database. The database I need to read the data from
is a UniData residing on a Unix server. The stored procedure needs to
reside on my sql 2005 server. The task is very simple in Access as we
have ODBC connections set up to the UniData via Informix (or IBM)
UniData ODBC drivers. I can easily combine my UniData and Sql Server
tables from within access. However, I can't seem to find a way to
replicate the same behavior in MS SQL Stored Procedure without the use
of Access. Is that even possible?
Thanks,
Marek
View 7 Replies
ADVERTISEMENT
Jun 27, 2001
Is it possible to query the result set of a stored procedure? and if so what is the procedure to do so?
View 1 Replies
View Related
Jun 7, 2005
What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
View 5 Replies
View Related
Jun 11, 2007
Hi, all.
I have a question about how you would design this. I have to return a list of projects in a stored procedure with a statement like this:
SELECT ProjectID, ProjectName FROM PROJECTS;
This populates a DropDownList with all possible projects. When the user selects one, I need to find out more information about that project, like this:
SELECT a.ProjectID, a.ProjectName, b.OtherStuff FROM PROJECTS a INNER JOIN OTHERSTUFF b on a.ProjectID = b.ProjectID;
In the old ASP world, we just filled the ProjectID parameter with NULL if we wanted to return all results, and the stored procedure was set up to dynamically return results based on whether that parameter was NULL or not.
I'm looking for a more elegant way to do this. I can use two stored procedures, but I'm not sure what to name them. Obviously, I could call one 'SelectProjects' and the other 'SelectProject', but that just doesn't seem elegant enough. Anyone have any other ideas?
View 4 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
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
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
Sep 3, 2007
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
View 2 Replies
View Related
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
Jun 12, 2008
how to write a query to find the no of tables present in that database.
Any suggestions.
View 4 Replies
View Related
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
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
Mar 2, 2005
I need to query a database with about 3 million records using a 'like' statement on various varchar columns. I've have a non-clustered index on the columns included in my query, but the performance is still pretty slow. Can anyone suggest ways to increase the speed of my queries? Does full-text indexing improve performance or does it just facilitate more advanced searches (which I don't need)? Would char instead of varchar datatypes be faster?
thanks!
View 3 Replies
View Related
Sep 20, 2007
Hi, I have been using Microsoft Access for a couple of years and we have just switched to SQL Server. However, I'm having problems querying the Mysql databases our company has in place. I have managed to establish connections in the Visual Studio but can't seem to link tables from two different databases like I could in Access. Is this possible? Also, is it possible to connect to the mysql database via management studio? Apologies for my complete lack of knowledge!
View 4 Replies
View Related
Mar 24, 2008
Hi everybody,
Our SRS reporting security model had to be designed so that the access is by individual report basis and by user. I cannot give access to individual reports based on windows AD group. Because everybody in that group does not need to have access to a particular report. Two people from one AD group needs access to say Report1. Only one person need to have access to Report2 etc.... Creating lot of AD groups for the security is also not advisable.
Right now I can tell right away users having access to a particular report as the number of reports and the users is small. But I want to design a report that can give me a list of users who have access to a specific report.
How do I query the reportserver database to get this information. I mean is this information saved in any of the tables?
Can you please give any info that you know of such information?
Thank you in advance for all your help,
View 5 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
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
