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 then
itself executes a stored procedure from another database? We have decide to
split our data into a tree structure (DB1) and data blobs (DB2) (we are
using MSDE and we have a 2gb limit with each DB so we've done it this way
for 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 in
DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of
course, as will DB2 when it adds the blob. Is this possible?
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
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?
I have a SSIS package that contains a DTS 2000 package in it. The DTS 2000 package imports data into several tables from an ODBC data source. When I execute the package through BIDS, no problems. Everything works great. I am now trying to execute the SSIS package in my stored procedure & it gives me the following error: Error: 2007-01-30 11:54:24.06 Code: 0x00000000 Source: Populate IncrTables Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error
I did a search for this & found KB 904796. It had the exact error message but I don't believe my packages uses 2000 metadata services. Just to be safe, I reinstalled the backward compatibility features & the DTS 2000 tools on the server. That still did not fix anything. I found another forum that suggested loading the DTS 2000 package internally, which I did & it did not fix anything. I am using a password for the protection level so that is not causing my issue. Does anyone else have any suggestions as to what I might be able to try?
SQL 2005 Dev Ed SP1 & post SP1 hotfixes installed Win 2k3 server Thanks! John
All- Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter? Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.) Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13. Your comments would be most appreciated!!! -Kurt1 <InsertCommand> 2 <DbCommand CommandType="Text" ModifiedByUser="false"> 3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText> 4 <Parameters> 5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" /> 9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" /> 10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" /> 11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" /> 12 </Parameters> 13 </DbCommand> 14 </InsertCommand> 15 <SelectCommand> 16 <DbCommand CommandType="Text" ModifiedByUser="true"> 17
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance ripern
-- Retrieves the value of column @columnName for credential id @credID ALTER PROCEDURE [dbo].[PMC_User_GetUserValue] @credID int, @columnName nvarchar(50) AS
DECLARE @userDataTable nvarchar(50) DECLARE @userDataID int DECLARE @statement nvarchar(500) SET @statement = ' '
SET @userDataID = (SELECT PMC_UserMapping.fk_userDataID FROM PMC_UserMapping INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable = (SELECT PMC_UserType.userDataTable FROM PMC_UserType INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName ) BEGIN EXEC (@statement) END
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRYINSERT INTO [syl_Categories] VALUES( @CategoryName, @LanguageID) SELECT SCOPE_IDENTITY() AS [CategoryID] RETURNEND TRY BEGIN CATCH --Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1 END CATCH END
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
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?
Hi,I have stored procedure (MS SQL Server 2000) which operateson around 600 000 rows (SELECT, UPDATE, INSERT)and executes in 5 minutes,when I put it in SQL transaction it slows down to more than 5 hours (!!)I have to admit that it is not problem with data locks (beside thatprocedurenothing else is executed on db),It is not also problem with that exact procedure, other proceduresalso slow down heavily when wrapped by SQL transactionvery very seldom stored procedure within transaction executescomparably long that its copy without transactionI guess it could be MS SQL Server 2000 configuration/tuning problem.Any ideas ?Chris
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.
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
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 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?
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.
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
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?
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
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!
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'
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?
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
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.
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.
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).
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
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
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.
