How To CREATE PROCEDURE In Another DATABASE?

Oct 2, 2007

Hi,

I need to write a script, which should run in a particular database (which name is not know, but I get it at runtime). It should add another database, which should be the same name with some suffix (i.e. "_Log"). And then it should create some tables and stored procedures there. The biggest problem is creating stored procedure, because CREATE PROCEDURE sentence doesn't allow to specify the database name, and it has to be the first statement in a batch (so USE doesn't help). I tried something like:




Code Block
DECLARE @logDBName nvarchar(100)
SET @logDBName = DB_NAME() + '_Log'
BEGIN TRY
EXEC ('CREATE DATABASE ' + @logDBName)
EXEC ('
CREATE TABLE [' + @logDBName + ']..Some_Log
(
id int IDENTITY(1, 1) NOT NULL,
timestamp datetime DEFAULT(GETDATE()) NOT NULL,
someText ntext NOT NULL
CONSTRAINT PK_Some_Log
PRIMARY KEY CLUSTERED (id)
)')
EXEC ('
CREATE PROCEDURE Add_To_Log
@someText ntext
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Some_Log(timestamp, someText) VALUES (GETDATE(), @someText)
END
')
END TRY
BEGIN CATCH
END CATCH




Of course this sample creates the stored procedure in the current database, and not in the newly created one. As I mentioned, if I add USE to the last EXEC, it doesn't work, because 'CREATE PROCEDURE must be first batch statement', and I can't write '[Some_Log]..Add_To_Log', because CREATE PROCEDURE syntax doesn't allow to specify the database name.
P.S.: I should also be able to alter the tables and procedures int he future from the CURRENT database, again by using

SET @logDBName = DB_NAME() + '_Log'

Thank you

View 5 Replies


ADVERTISEMENT

How Do You Create A Database Within A Stored Procedure

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

Stored Procedure To Create New Database

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

Create Store Procedure That Take Data From 2 Database

Sep 7, 2007

 hello all.., i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo 
 in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill            balance
111222            200$
222444            10$ 
this is a list of order table 
user_id            no_order            date            totalcost
    a                     1                  1/1/07             50$
when customer insert no_bill(111222) in page and click a button,  then bill table became
no_bill            balance
111222            150$
222444            10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" 
is procedure can take data from 2 database?mystore procedure like:ALTER PROCEDURE [dbo].[pay](    @no_bill AS INT,    @no_order AS int,    @totalcost AS money)ASBEGIN    BEGIN TRANSACTION            DECLARE @balanc AS money                SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill)        UPDATE [bill]        SET            [balance] = @balanc - @totalcost        WHERE            [no_bill] = @no_bill    COMMIT TRANSACTIONEND  it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.Transaction
count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 0, current count = 1.No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[pay]." plss.. help... 

View 13 Replies View Related

CREATE PROCEDURE Permission Denied In Database

Apr 7, 2008

i'm using SQLCacheDependency in my code. I have ran following lines to enable SQL notification.

ALTER DATABASE [DataBaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DataBaseName] SET ENABLE_BROKER
ALTER DATABASE [DataBaseName] SET ARITHABORT ON
ALTER DATABASE [DataBaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
In Global.asax file, i have protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(CONNECTION_STRING);
} but whenever my application starts i get error saying "CREATE PROCEDURE permission denied in database", if i give dbo owner permission to SQL user then it works fine, but ofcource i dont want to give dbo owner permission to sql user defined in connection string

View 3 Replies View Related

How To Create An Stored Procedure To An Existan Database??

Mar 4, 2005

i already hava an database in the MSDE database server, but now i want to add some stored procedures to that specifi database in the MSDE database server, how can i do that???

View 4 Replies View Related

Stored Procedure To Create A New Database With Tables

Feb 28, 2007

I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.

View 6 Replies View Related

How To Grant Create Procedure Permission To Database User?

Apr 7, 2008

Can somebody tell me without pointing to any other link how to grant Create Procedure permission to DB user.

View 3 Replies View Related

SQL Server 2012 :: Mirror Database - Create Stored Procedure

Nov 5, 2014

I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".

My Current Logic:

CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN

[Code] .....

The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...

View 4 Replies View Related

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

Oct 2, 2007

 
 I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
 
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
 Prontonet
 
 
 

View 4 Replies View Related

Grant CREATE VIEW, CREATE PROCEDURE ...

Apr 12, 2006

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

View 8 Replies View Related

Create Procedure To Create Many Triggers And Procedure

Apr 19, 2002

Hi guys.

I am trying to create a procedure which should drop all existing triggers and can create about 40 differnt triggers in a table.

I cant use "GO" statement in a procedure.

Is there any way to create a procedure like that?

I dont want to run this as a script.

please advice.


--Note: Many triggers use same kind of variable names inside.

-MAK

View 1 Replies View Related

The Old Inability To Toggle/change/switch Between ALTER PROCEDURE &<---&> CREATE PROCEDURE Bug (or Is It A Feature?)

Apr 1, 2007

Keep in mind this is my first compiled SQL program Stored Procedure(SP), copied from a book by Frasier Visual C++.NET in Visual Studio2005 (Chap12). So far, so theory, except for one bug (feature?)below. At some point I'm sure I'll be able to laugh about this, akinto forgeting a semi-colon in C/C++, but right now it's frustrating(time to sleep on it for a while).Problem--For some reason I get the error when trying to save files where twotables (called Author and Content), linked by a single key, form arelationship.By simple comparison of the source code in the textbook and my program(below) I found the difference: instead of, like in the textbook, theStored Procedure (SP) starting with "CREATE PROCEDURE", it*automatically* is (was somehow) given the name of 'ALTER PROCEDURE'and I cannot change this to "CREATE PROCEDURE" (you get an error in MSVisual Studio 2005 Pro edition of "There is already an object namedXXX in the database", see *|* below). No matter what I do, the SP isalways changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!(otherwise it simply will not save)Anybody else have this happen? (See below, others have had this happenover the years but it's not clear what the workaround is)Keep in mind this is my first attempt and I have ordered somespecialized books on SQL, but if this is a common problem (and Isuspect it's some sort of bug or quirk in VS2005), please let me know.Frankly I think SQL as done by VS2005 is messed up.Here are two Usenet threads on this problem:(1) http://tinyurl.com/2o956m or,http://groups.google.com/group/micr...1454182ae77d409(2) http://tinyurl.com/2ovybv or,http://groups.google.com/group/micr...9e5428bf0525889The second thread implies this is a bug--any fix?Also this bug might be relate to the fact I've switched (and notrebooted) from Administrator to PowerUser after successfully changingthe permissions in the SQL Server Management Studio Express (see thisthread: http://tinyurl.com/2o5yqa )Regarding this problem I might try again tommorrow to see if rebootinghelps.BTW, in the event I can't get this to work, what other SQL editor/compiler should I use besides MS Visual Studio 2005 for ADO.NET andSQL dB development?RL// source files// error message:'Authors' table saved successfully'Content' table- Unable to create relationship 'FK_Content_Authors'.The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"FK_Content_Authors". The conflict occurred in database "DCV_DB",table "dbo.Authors", column 'AuthorID'.// due to the below no doubt!--CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--*|* Error message given: when trying to save CREATE PROCEDURE StoredProcedure: "There is already an object named 'InsertAuthor' in the dB

View 11 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related

Create Script To Create/Refresh Identical Database

Mar 26, 2008



I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.

I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.

Any help would be appreciated. Thanks!

View 9 Replies View Related

CREATE PROCEDURE

Feb 5, 2008

CREATE PROC selectpro
@id INT=1
WITH RECOMPILE
AS
SELECT * FROM [contain-1]
WHERE ID=@id 
RECOMPILE indicates that SQL SERVER does not cache a plan for this procedure and the procedure is recompiled as run time.
What is purpose statement above?
why use RECOMPILE?

View 2 Replies View Related

Create Procedure

Nov 7, 2002

Hi,

I would like to have a default header everytime anyone create a stored proc on the server.

when someone clicks on create new procedure, it should come up like this:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

/***********************************
Created By:
Date Created:
Purpose:
History:
************************************/

I saved a templet in Model database with this header. Somehow still when we try to create new procedure, it doesn't show the header.

Does anything else need to be done besides saving the templet?

If changing the way in the MOdel database is not the correct way to do it, How else can I accomplish this so that developers are forced to fill in the information?

Thanks
:confused:

View 1 Replies View Related

Create Procedure

May 19, 2008

I am trying to create a procedure but I am not sure how to script this correctly.

CREATE PROCEDURE spPriceChange
@newprice DECIMAL(5,2),
@margin DECIMAL(5,4),
@mincalc DECIMAL(5,2),
@maxcalc DECIMAL(5,2)
AS
UPDATE item set item_prc_3 = @newprice
where item_lst_lnd_cost/@margin BETWEEN @mincalc AND @maxcalc
and item_id in (select item_id from item where item_prc_1 like '%9.99')
and item_cat in ('AB', 'AC', 'AD', 'AH', 'AM', 'AS', 'AT', 'DB', 'DC', 'DD', 'DH', 'MB', 'MC', 'MD', 'MH', 'MM', 'MU', 'SM', 'UA', 'UC', 'UM', 'UO', 'UU', 'VM', 'VU')
and item_id not like '*%'
and item_id not like 'A%'
and item_id not like 'C%'
and item_id not like 'L%'
and item_id not like 'Q%'
and item_id not like 'R%'
and item_id not like 'Z%'

The query throws an error that only shows the @ symbol as the stop point. What did I do wrong?

Brooks C. Davis
IT AdministratorLogistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

View 6 Replies View Related

Procedure To Create DB

May 4, 2007

Hi All.
I just start use SQL2005. I have code
CREATE DATABASE DMR_V3_0 ON
(FILENAME = 'X:ABCMYFILE_Data.mdf')
FOR ATTACH;
How to create procedure if value of FILENAME will specify before procedure will run.
Thanks.

View 3 Replies View Related

Create Procedure

Nov 6, 2006

Hi

I try to create a very simple stored procedure,

CREATE PROCEDURE Importera_fil

@fil varchar

AS

INSERT INTO XMLFaktura(Faktura)

SELECT * FROM OPENROWSET

(Bulk '@fil', SINGLE_CLOB) AS DocumentID

but I get an errormessage when I try to create it,

Msg 4860, Level 16, State 1, Procedure Importera_fil, Line 4

Cannot bulk load. The file "@fil" does not exist.

So my question is, how can i create the procedure?

Thanks for any help

View 1 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

Sep 10, 2012

We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?

View 1 Replies View Related

SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit

Jul 15, 2015

I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.

View 6 Replies View Related

How To Create A New Database From An Existing Database Saved To An External Hard Disk?

Feb 20, 2007

Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.

Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.

View 1 Replies View Related

Create Database Permision Denied In Database ' Master' (MS SQL SERVER, ERROR 262

Feb 17, 2007

Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,

I am running windows Vista Business, SQL SERVER 2005

so what going on here

View 23 Replies View Related

SQL Problem Cant Create A Procedure

Nov 8, 2007

create proc sp_fillrowintable(    @tablename nvarchar(50),    @colvalue    nvarchar(100),    @id int )asdeclare @colname nvarchar(30)declare @colid nvarchar(30)--declare @tablename nvarchar(50)--set @tablename = @tableselect @colid = column_name from information_schema.columns where table_name = @tablename and data_type ='int'select @colname = column_name from information_schema.columns where table_name = @tablename and data_type ='varchar' if(@id = 0)begin    select @id = max(@colid) from @tablename    Insert into @tablename values (@id,@colvalue)endelsebegin    update @tablename set @colname = @colvalue where @colid = @id end getting errors at my side while executing this script Msg 1087, Level 15, State 2, Procedure sp_fillrowintable, Line 21Must declare the table variable "@tablename".Msg 1087, Level 15, State 2, Procedure sp_fillrowintable, Line 22Must declare the table variable "@tablename".Msg 1087, Level 15, State 2, Procedure sp_fillrowintable, Line 26Must declare the table variable "@tablename".Msg 156, Level 15, State 1, Procedure sp_fillrowintable, Line 26Incorrect syntax near the keyword 'where'. help me out  I want to insert and update rows for various table having only 2 columns in each table.want to insert and update rows in these tables. please tell me is it possible to do this through stored procedure or i have to do inline query in .net 

View 5 Replies View Related

How Can I Create Stored Procedure?

Feb 13, 2008

 forumid    questid   answerid   answer     replyby   replyon    1            1            1            xxxx        aaa        01/01/08(mm/dd/yy)    1            1            2            yyyy        bbb        01/02/08    2            1            1            zzzz        ccc        01/02/08    1            1            3            hhhh         bbb        01/04/08    2            1            2            uuuu         vvv         01/04/08    1            2            1            tttt            ooo        01/05/08suppose i give forumid value=1 i want following answer recent reply and no.of replies  forumid    questid   answerid  no.of.reply  answer     replyby   replyon            1            1            3            3               hhhh        bbb       01/04/08      1            2            1            1               ttt            ooo        01/05/08 

View 14 Replies View Related

How To Create Store Procedure

Dec 6, 2005

Hi all,

I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???

View 8 Replies View Related

Create Procedure Wizard

Jul 23, 2004

has anyone ever used the Create Procedure wizard in SQL server 2000?? is that effective

View 3 Replies View Related

Drop And Create Procedure

Feb 4, 2005

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...


Thanks

View 3 Replies View Related

How To Create Store Procedure

Dec 6, 2005

Hi all,

I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???

View 1 Replies View Related

Create Store Procedure

Feb 14, 2006

I need your help again.
I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.

i have two input parameters (@fname, @lname).
Thanks.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved