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
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'
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.
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...
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
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???
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.
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
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.
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
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.
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?
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?
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
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.
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?
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.
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.
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
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???
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...
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???
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.