SQL Server 2008 :: Stored Procedure Text String Replace
Apr 16, 2015
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
View 2 Replies
ADVERTISEMENT
Jan 27, 2006
Hi
I'm trying to create a stored procedure using the northwind db which will do the following:
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products
However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.
Can anyone help please?
Cheers
Woolly
View 7 Replies
View Related
Apr 29, 2009
I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.
View 9 Replies
View Related
Mar 3, 2015
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'
[code].....
View 0 Replies
View Related
Nov 14, 2007
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
View 1 Replies
View Related
May 26, 2015
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
View 5 Replies
View Related
Jul 23, 2005
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
View 1 Replies
View Related
Aug 7, 2015
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
View 5 Replies
View Related
Apr 20, 2015
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">
I would like to replace this part of the string with a different piece of text.
I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.
View 4 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Jun 25, 2015
I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?
View 1 Replies
View Related
Sep 19, 2005
I have this small snippet from a stored procedure...--Let @course = XY-101SET @newUserRole =@course SELECT REPLACE(@newUserRole,'-','') SET @newUserRole =@newUserRole + 'STUD' Problem is, it doesn't seem to be replacing the dash (-) with a blank/nothing/delete.The STUD does come out attached to the string, but the dash remains...It comes back as XY-101STUDAnyone see a problem with it?Thanks,Zath
View 2 Replies
View Related
Jun 5, 2012
I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
View 5 Replies
View Related
Oct 7, 2013
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
View 3 Replies
View Related
Aug 10, 2015
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.
View 1 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
View 4 Replies
View Related
Mar 18, 2015
I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?
View 5 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 8 Replies
View Related
Jul 29, 2015
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
View 6 Replies
View Related
Oct 23, 2015
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies
View Related
Nov 2, 2015
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
View 9 Replies
View Related
May 13, 2004
Hi;
I have a stored procedure :
<code>
Create Procedure ControlDept
(
@DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @DeptID
)
Return 1
Else
Return 0
Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @DeptID".
for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces
if I say DeptID LIKE @DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database
but how?
can you help me please??
View 1 Replies
View Related
Jul 20, 2005
I'm using replace in a stored procedure to eliminate carriage returnsa user might have entered in an Access field.The problem is that even though it seems to strip out the carriagereturn I can't get rid of the "□" character.This is my code:DECLARE @myReturn nvarchar(1)SELECT @myReturn = NCHAR(10)REPLACE(UserAddress,@myReturn,' ') AS myTEXTFor example, the user has entered:123 Oak StreetSuite 101What I get after running the replace is: 123 Oak Street □ Suite101lq
View 2 Replies
View Related
Nov 29, 2014
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
View 1 Replies
View Related
Feb 9, 2015
I need to import multiple csv files and load into table and everytime new database has to be created .
I was able to create new databases using stored proc
How do i do a bulk insert for all the files at once to insert into tables .
i want to load all the files at once .
View 6 Replies
View Related
May 11, 2015
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
View 9 Replies
View Related
Jul 1, 2015
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies
View Related
Jul 9, 2015
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
View 9 Replies
View Related
Jul 22, 2015
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies
View Related
Mar 6, 2013
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
View 9 Replies
View Related
Sep 15, 2015
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
View 5 Replies
View Related
Jul 21, 2015
I have made this defination for a stored procedure:
PROCEDURE EP_Conterbalances
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty VARCHAR(500),
@CounterBalanceType_id INT,
@dateFrom DATETIME,
[Code] .....
The value of @EmpFilterAddDuty could be:
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int
EXEC@return_value = [dbo].[EP_Conterbalances]
@Start_Date_For_Totals_Date = N'20120831',
@EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
View 4 Replies
View Related
Jun 25, 2014
We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:
mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.
View 6 Replies
View Related
