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
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
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 RelatedCREATE 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?
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:
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.
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 8 Replies View Related
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 Relatedforumid 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 RelatedHi 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???
has anyone ever used the Create Procedure wizard in SQL server 2000?? is that effective
View 3 Replies View RelatedI 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
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???
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.
hi. i'm trying to create a stored procedure but it keeps messing up and i have absolutely no clue why. here is what i have:
CREATE PROCEDURE sp_OfficeReportStats AS
------------------------------------------------------------
--NEW CASE
------------------------------------------------------------
--NC2
CREATE TABLE TempWorkDB
(
ProsAtty SMALLINT, Stat INT
)
GO
INSERT INTO TempWorkDB (ProsAtty, Stat)
SELECT DefendantCase.ProsAtty, COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey = DefendantEventPros.VBKey WHERE DefendantEventPros.EventID=2 AND DefendantEventPros.EventDate BETWEEN DATEADD(MONTH,-2,GETDATE()) AND GETDATE() GROUP BY DefendantCase.ProsAtty
GO
UPDATE OfficeReport SET NC2=TempWorkDB.Stat FROM TempWorkDB WHERE TempWorkDB.Prosatty=OfficeReport.ProsAtty
GO
UPDATE OfficeReport SET NC2=0 WHERE NC2 IS NULL
GO
DROP TABLE TempWorkDB
GO
this code works in query analyzer just fine but it says i have an error at TempWorkDB. I do not have a TempWorkDB in my database currently. Waht am I doing wrong? thanks for you help!
Hi!
I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.
Please help me!
Mike.
Hi
I am sql server 2005.
how to create rdl using stored procedure which uses parameter
Thank you.
Is there a way to create a stored procedure that accepts a column name and returns the name of the tables that have that column name?
View 2 Replies View RelatedI have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:
create proc spTheNameOfTheProcedure
as
select table_name from information_schema.columns
where column_name = XXXXXX
no sure what to put where I have XXXXX
Hello All,
I'm new to this forum and looking for helps tip on SQL Server.
How to create a store procedure? Here's the proper information that below:
1)In house data, have 2 tables called: Advo and
Advosum;included Excel sheet (ChampaignAddress.xls)
have raw data rows
(Latitude1,Latitude2,Longitude1,Store).
2)I have a query statement to retrieve data from
"In House Data" which in Where Clause using
(latitude between # and #)& (longitude). Get each rows data in
column from excel(ChampaignAddress.xls)to populate into Where
Clause that when it execute the query.
3)
SELECT CASE WHEN deliveryTypeCode >= 'A' AND deliveryTypeCode <= 'H' THEN CONVERT(char(20), 'R') ELSE CONVERT(char(20), 'B')
END AS RBDI, case(left(advo.crrt,1))when 'B' then convert(char(20),'BOXHOLDER')
when 'C' then case when (deliveryTypeCode>='A') and (deliveryTypeCode <='H') then convert(char(20),'RESIDENT')
else convert(char(20),'BUSINESS OWNER') end else 'RESIDENT' end as Title, case (left(advo.crrt,1))
when 'B' then convert(char(64),( rtrim(StreetName)+ ' ' + rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' + rtrim(alternateTopLine) + ' ' +
rtrim(AptNum)))else convert(char(64),(rtrim(streetNum) + ' ' + rtrim(StreetPreDir) + ' ' +
rtrim(StreetName) + ' ' + rtrim(StreetPostDir) + ' ' + rtrim(StreetSuffix) + ' ' +
rtrim(alternateTopLine) + ' ' + rtrim(AptNum))) end as Address, cityName as City, State,advo.ZIP, advo.Plus4,
convert(numeric,ltrim(Walkseq)) as Walkseq, advo.Crrt,('******************ECRWSS**' + advo.Crrt)as Endorse,
cityRuralFlag as City_rural,convert(char(2),replace(dpb,' ','0'))as dpb,dpbc,null as primaryPreName,
null as PrimaryFirstName,null as PrimaryMiddleInitial,null as PrimaryLastName,null as PrimaryPostName,updateDate
FROM advosum squareRadius join Advo On advo.crrt=squareRadius.crrt and advo.zip=squareRadius.zip
WHERE latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'C%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND advo.crrt LIKE 'B%' OR latitude between 40.44294591 and 40.48836091
AND longitude between (-88.35746062-(5/(69.1*cos(latitude /57.3)))) and (-88.35746062+(5/(69.1*cos(latitude/57.3))))
AND (advo.crrt LIKE 'R%' OR advo.crrt LIKE 'H%' OR advo.crrt LIKE 'G%')
ORDER BY advo.zip,advo.crrt,walkseq
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thank you to all for your helps
ryan,
RV
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007
If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)
results would be 10_12_2007,10_13_2007.
So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3
I am writing some code generation stuff and I am trying to get a scriptlike this to work:IF (something)BEGINCREATE PROCEDURE WhateverASSELECT 1 as oneENDBut it complains about this, so I am guessing that I can't put thecreate prodcedure in an IF block.Does anyone know of a work around for this?
View 6 Replies View RelatedI have a requirement in SQL 2005 in Development database1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .2. Only DBA's ( who are database owners ) can create, alter tables .Developer's should not create or alter tables .3. Developers can create/alter Stored Procedure/User Defined functionsin dbo schema and can execute SP/UDF.4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (tables in dbo schemaHow to achieve this using GRANT SCHEMA statementThanksM A Srinivas
View 2 Replies View Related I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):
First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141
Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please
Thanks in advance for your help
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
Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.
create procedure new_forum
@user_id int,
@title varchar(50),
@description varchar(200)
as
begin
declare @totalForuns int
select @totalForuns = count(*) from foruns
set @totalForuns = @totalForuns + 1
Insert into foruns (title,creation,country,[views],[description])
values (@title,getdate(),'England',0,@description)
CREATE TABLE [dbo].[@totalForuns](
[thread_id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[last_user_id] [int] NOT NULL,
[title] [nvarchar](50) NOT NULL,
[creation] [datetime] NOT NULL,
[last_answer] [datetime] NOT NULL,
[answers] [int] NOT NULL CONSTRAINT [DF_threads_answers] DEFAULT ((0)),
[vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views] DEFAULT ((0)),
[fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed] DEFAULT ((0)),
[votos] [int] NOT NULL CONSTRAINT [DF_threads_votos] DEFAULT (''),
CONSTRAINT [PK_threads] PRIMARY KEY CLUSTERED
(
[@totalForuns] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[@totalForuns] WITH CHECK ADD CONSTRAINT [FK_threads_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[@totalForuns] CHECK CONSTRAINT [FK_threads_users]
end
Go
Thank you very much.
Hi All!
I have Store Procedure:
If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum') Drop Procedure Forum_Topic_SelectFromForumgoCREATE PROCEDURE Forum_Topic_SelectFromForum( @ForumID varchar(10))
AS BEGIN TRANSACTIONSELECT * from Forum_Topic where ForumID=@ForumID Order by Tmp DESCIF @@ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION
Now, I want to Add 2 Variables: @Offset int, @Count int . With @Offset: the point of data, @Count: sum of row will get.
when get data I want it get from @Offset to Added @Count.
Help me to rewrite this store procedure. Thanks
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.
Dear all,
i want to create a storeprocedure that may acept value such as 0,1, 1a, 2, 2a etc.. from a dropDownList. But i always get an error. below is sample of my SQL1 DECLARE @val varchar(5)
2 DECLARE @sql varchar(4000)
3
4 SET @val = '1a'
5 SET @sql = 'select * FROM [dbo].[vwFormNAllAnswers]'
6
7 IF @val NOT LIKE '0'
8 SET @sql = @sql + 'WHERE QuestionCode LIKE '+ @val
9
10 EXEC(@sql)
I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.
alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)