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?
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.
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?
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?
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 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?
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 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
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?
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?
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!
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,
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).
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
