Quick Question About Checking For Existing Procedure Before Creation.
Feb 27, 2008
Hey everyone,
I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.
Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END
GO
It's generating a syntax error at PROCEDURE.
Anyone know why this might happen?
The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.
Hi,I have a table which I want to alter to add the data from anothertable.Now both these tables have a few columns which are same. Now,I want the system to be such that only the unique columns can beadded to the first table.How can I accomplish this in SQL?
I wirte down a small query and i wanna write down a store procudure for that..can somebody help me out for this ASAP...its urgent..
Select Id,address1,address2,city,state,zip from xyz where city is null
HOW TO WRITE STORE PROCEDURE..Do I need to delcare parameter and value...??? can't I just save this query directly by saying CREATE PROCEDURE AS... and than save...beacuae my boss just wanna keep that procudure as reference to run that.
I need to create a login which has the same access permissions on databases and its objects as an existing login.
I understand in SQL Server that a login is for the instance and the user is for the database. And for each login to access a database there must be an associated user within the database.
Example: login u1 has access to databases d1 and d2. And u1 has the dbowner privilege on d1 And u1 has the db_datareader privilege on d2
I want to create another login u2 which has identical privileges and access as u1.
I need this because in the case that I am working on, this u1 login is associated with almost 30 database within the instance.
5- Set SQL Project Permission Level to External, change from the project properties Database Tab
"Permission Level Select the permission level from the drop-down list to specify a set of code
access permissions granted to the assembly when accessed by Microsoft SQL Server. The value can be
Safe, External, or Unsafe; these values correspond to the SQL Server permission sets SAFE,
EXTERNAL_ACCESS, and UNSAFE, respectively. Safe is the default.
This setting corresponds to the PERMISSION_SET argument for the SQL Server CREATE ASSEMBLY command. " MSDN
6- "The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts
the database and the contents within it. By default, this setting is OFF, but can be set to ON by
using the ALTER DATABASE statement.
Note: To set this option, you must be a member of the sysadmin fixed server role." MSDN
ALTER DATABASE [Database Name] SET TRUSTWORTHY ON .
7- Build and Deploy assembly.
8- The web service reference generates XmlSerializers assembly, there are many ways to resolve this issue, a quick way would be to add the XmlSerializers assembly to the database where the main CLR assembly is deployed.
From "SQL Server Management Studio" select the target database, goto "Programmability" then -> "Assemblies", there right click "New Assembly" and specify reference.
Hope this is helpful to others. Regards Rabeeh Abla
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
procedure creation I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
any clues? i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.
create table CUSTOMER ( CUSTOMER_ID int identity(1000,1), NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256) )
But while writing insert statement didnt specify this column.
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values ------------------------------------------- PoId Podate Approved
22008-07-07 No 42007-05-05 No 52005-08-06 Yes 62006-07-07 Yes
Table B contains the following columns and values ------------------------------------------------- TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No 3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
I have created a database and I a table. Now I want to create a stored procedure. I go to Programability=>Stored Procedures in my database and press right click=>New Stored Procedure. After I write the procedure I don't understand how to save it inside the datebase and execute it to preview the results in the Management Studio Express.
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
I need to create a view using a stored procedure .
The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration. After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.
I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another . I am thinking to create a view though a stored procedure . You can suggest me alternate ways to do same .
Is there any system stored procedure or any table that contains the creation date of stored procedures in one database . I really want to know wich is the last stored procedure created in a database. Thanks.
I have two tables called A and B and C. Where A and C has the same schema A contains the following columns and values-------------------------------------------PoId Podate Approved 2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 6 2006-07-07 Yes Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status 1 A 7/7/2007 No3 B 2/4/2006 Yes Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. Pls provide me with full stored procedure code. It is very urgent.
Hi i have a stored procedure which deletes a row from a table. Sometimes the proc will break the constraints for the table. How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0 i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only *** similarly when it checks the 100th record it compares it for 99 times *** similarly when it checks the 10000th record it compares it for 9999 times so here lies the problem....
when it checks the 100000th record it gets dammm slow... what i have get till now is that i have checked.....>>>>> 5000 records in 4 mins.... 25000 records in 22 mins.... and 100000 records in 20 hours....(means initially its faster but it gradually slows down) ************************************************** ************************ here i m giving the code for the procedure...... ************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON Declare @IvgId as numeric(15) Declare @Address as nvarchar(250) Declare @AddressClean as nvarchar(250) Declare @MaxLen as INT Declare @Add as nvarchar(250) Declare @Ic as int Declare @FoundIvgId as numeric(15) Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0 Begin /*here i m doing string cleaning by removing special characcters */ Select @MaxLen = len(LTRIM(RTRIM(@Address))) Select @Address = LOWER(@Address) Select @Ic = 1 Select @AddressClean = ' ' While @Ic <= @MaxLen /* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */ Begin Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End
If ascii(@Add) > 90 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End
Select @Ic = @Ic + 1 End
/* now we have removed special characters , for failure checking i m using this 'Y' */ Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y' Where IvgId = @IvgId
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N' OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0 Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)) Begin Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20) End
ELSE Begin Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0) Select @NewIvgId = @NewIvgId + 1 End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean End
Close CurData Deallocate CurData SET NOCOUNT OFF
Print 'Done................................'
************************************************** ************************** now the procedure is over....now i m writing the SQL script of DATA & RESULT table ************************************************** ************************
so now i have given the whole description of my problem....i m eagerly waiting for reply...... if anybody can help....i will be very thankful..... bye for now Bhupinder singh
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/ if NOT EXISTS (SELECT object_id('procedureName','p')) CREATE PROCEDURE [dbo].[procedureName] @location char(2) AS ...Procedure...
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p')) SELECT 'FALSE' ELSE SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'PROCEDURE'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@location'.
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure: Code: ( text )
Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.
I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.
I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.
I am trying to create a new stored procedure in Sql server managment studio express in a database. I am getting an error message saying
Invalid object name 'Consumer_delete'.
Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution. Thanks for your help
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Consumer_delete
@ConsumerID int,
@BusinessId int
AS
BEGIN
DECLARE @intError int
DECLARE @ConsBusinessID int
SET NOCOUNT ON
SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]
FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
DELETE FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex() method reads the script file from starting to end and returns script text. My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime, @prmEndDate datetime,@prmEvent char(1)ASBEGIN SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) as EventDate from dbo.Parishioner P left outer join dbo.Sex S on P.SexId = S.SexId where (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) between @prmStartDate and @prmEndDate END But when i executing script file having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm
I would ask if there some precaution on creating stored procedure on a database member of an always on group. I copied the stored procedure on all nodes but I'm not sure if this is enough to make it works in case of failover ?
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
Hi all, I have an Existing Stored Procedure on the database, and I want to modify it. However, when I changed it and saved it with the same name, the value,say 20 here I changed was not upgraded to the new value (I wanna it be 30). so anyone know how to solve that? Thanks in advance.
ALTER PROCEDURE [dbo].[spr_getCompanyId]( @companyname as varchar(20), @companykeyword as varchar(20) //i want to change 20 to 30 )
I have a stored procedure I created in SQL Server 2000 enterprise manager which I would like to modify in SQL Server 2005 Express Management Studio. When I right click on the stored proc and select "Modify", the code opens - however any attempt to save creates a local .SQL file. How can I save these changes to the stored procedure on the server?
Hi, I already got a database and I need to add another stored procedure on it in order to call it from the SSIS package. But when I execute the sql file to create the procedure, there is a error message popping out like:
"Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'logit'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."
And I have tried it on another computer, there is the same error. Could you please tell me how to solve the problem. Thanks very very much.
I have an existing (working) SP that when I try to drop into the reoirt wizard gives me errors. The procedure runs in SQL management Studio, works in an existing rdl, even returns data in the repor wizard but also returns errors and therefore I can't continue. My work around is to drop it into an existing rdl; then I lose the Report Wizard features of generating a slick, quick repport.
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
