I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword] ( @loginid nvarchar(180), @oldpassword nvarchar(40), @newpassword nvarchar(40) ) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid) BEGIN BEGIN TRANSACTION UPDATE contacts SET password = @newpassword WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword END ELSE BEGIN RAISERROR(N'The password you entered does not match your current password.', 16, 1) RETURN END
IF @@ERROR <> 0 BEGIN RAISERROR(N'There was an error creating your new password.', 16, 1) RETURN END
COMMIT TRANSACTION
END
************ This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15 Incorrect syntax near '@loginid'. Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. ************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views. Does anyone know why someone would do this? I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?
Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.
I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?
Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...
with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?
Hi all, I want to know if there is a way to use a stored procedure in a view OR a table value function OR use the store procedure in table value function.
If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.
Hi all, I want to know if there is a way to use a stored procedure in a view OR a table value function OR use the store procedure in table value function.
If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
Hi guys I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order . The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that. Any Idea?
Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time? CREATE PROCEDURE sp_SurveyMainDetails_Update @Constructor_ID int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert' SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN SET @Tstamp = @CHANGED_Tstamp RAISERROR('This survey has already been updated since you opened this record',16,1) RETURN 14 ENDELSE BEGIN SELECT * FROM tblSurvey WHERE Constructor_ID = @Constructor_ID AND --Contractor_ID = @Contractor_ID AND Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND IsAbortiveCall = @AbortiveCall IF @@ROWCOUNT>0 SET @ActionDone = 'Update' UPDATE tblSurvey SET Constructor_ID = @Constructor_ID , SurveyorName_ID = @SurveyorName_ID , Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) , IsAbortiveCall = @AbortiveCall , Note = @Notes WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin exec dhoc_ChangeLog_Insert 'tblSurvey', @Survey_ID, @User_ID, @ActionDone, 'Main Details', @Survey_ID end else BEGIN RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1) RETURN 10 END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID END --Make sure this has saved, if not return 10 as this is unexpected error --SELECT * FROM tblSurvey DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO This is the view; CREATE VIEW dbo.vw_Property_FetchASSELECT dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1, dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code, dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID, dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2, dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name, dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date, dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID, LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address, dbo.tblProperty.TenureFROM dbo.tblPropertyType RIGHT OUTER JOIN dbo.tblProperty LEFT OUTER JOIN dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
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.
hi, Can someone tell me when to use SQL Server View as oppose to Stored Porcedure? Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure. In what situations and senarios views are better and one should consider them over Stored Procedure?
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 .
SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?
Also, in my sp, I have the following:
SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS, ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted, Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#] WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1; GO
ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?
Hiya folks, I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.
The syntax I'm using is as follows :
SELECT TOP 1 @MyJobN = IJobN FROM MyView
I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.
SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView
Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.
CREATE PROC upProcName AS
DECLARE @Variable varchar(50)
CREATE VIEW vwName AS
SELECT DISTINCT Table1.*, Table2.* FROM dbo.Table1 INNER JOIN dbo.Table2 AS BUG ON Table1.Col1 = Table2.Col1 WHERE LI.accname = @Variable
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique Details: String
Table2: MasterRefNum : String, not Unique SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back 1- the details where Ref_No = the required refernece number 2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex create proc myProc as use [myDb1] go create view myV1 as select * from mytable go use [myDb2] go create view myV2 as select * from mytable go
go --- of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
HelloNewbie here.Is there a way of creating a VIEW...using a stored procedure. I ambasically trying to create a view to return some data that I amgetting using a stored procedure.I have created the procedure and when I execute this its working ok.The stored procedure uses a datefrom and dateTo which I have set up bytweaking the getdate() and getdate()-2.In other words can you create a view like thisCREATE VIEW view_testASexec proc_testGOAny help will be greatly appreciated.Remmy
Hi,Ik created an application with visuals basic.NET. This has aconnection string to one database, let's say 'A'. In this database astored procedure is called which should execute a string (which ispassed by the) VB tool. This string is a CREATE VIEW statement en thisshould be executed in another database let's say 'B'.I tried this in Transact - SQLEXEC('USE B;' + Query)An error occurs : CREATE VIEW should be the first in a batchedstatement.Could anyone help me with this one?Greetz,Hennie
We are running SQL Server 2000 Developer Edition. I don't want tomake the developers the sysadmin or even the dbo in the userdatabases. Is there a way to give them access to only view thepermissions for the stored procedures in the user database withoutmaking them dbo?When I take them out of the db_owner role, when they open a storedprocedure they no longer see the permissions tab. I would like forthem to see the permissions tab and be able to view the permissionsbut not change the permissions.Is that doable?