I need to create a stored procedure in the master database that can
access info to dynamically create a view in another database. It
doesn'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 sysname
AS
BEGIN
DECLARE @query varchar(1000)
SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
EXEC(@query)
END
In 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 a
semicolon in place of "GO" but that didn't help either.
Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck, because how would i output a value e.g "@Access_Right_ID", this is the stored procedure below CREATE PROCEDURE dhoc_AccessRight_Insert @AccessLevel char(50), @Access_Right_ID int OUT, @Tstamp timestamp out ASSELECT * FROM tblAccess_Right WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0 BEGIN RAISERROR('This record is already in the database',16,1) RETURN 14 END ELSE BEGIN SET NOCOUNT OFF; INSERT INTO tblAccess_Right ( AccessLevel ) VALUES (@AccessLevel); SET @Access_Right_ID = @@Identity SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID) --Make sure this has saved, if not return 10 as this is unexpected error IF @@rowcount = 0 BEGIN RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1) RETURN 10 END ELSE BEGIN IF @@error <>0 BEGIN RETURN @@error END ENDEND GO
After I changed the activation stored procedure of a queue, the old activation stored procedure keeps being launched by the service associated with the queue. I turned the queue off and on, but to no avail. I also checked the dynamic view sys.service_queues to make sure the proper stored procedure exists in the field activation_procedure. Do you have any idea about what might have gone wrong?
I have a stored proc which will be fed a value that is compared to a char value for a selection. One of the choices might be 'all' for which I want to return all records. Something like this
create procedure name @location char(40) as select ----------------
where locname like @location
I tried using LIKE COALESCE but no luck. Any ideas Thanks
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A [ = Plan B + = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
The SQL Server is running full recovery model but they would like it to be changed to simple while the data backup occurs then set back to full. Is there a way to do this in a stored procedure or is it all done via options ? Thanks
After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
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?
Hi After install the mdf and log files are stored in the Program Files directory tree. How do I change the place were they are stored to a path of my own choice, so that new DBs get created there too? I've looked though the docs, but I can't find this. Thanks John
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 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.
