SQL Server 2012 :: Include Source Feature In More Than One Stored Proc
Sep 2, 2015
I need to use the same SQL source code in more than one stored proc.
Is there a way to -include mysql.sql or something like that ?
Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
View 9 Replies
ADVERTISEMENT
Mar 18, 2014
I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
View 5 Replies
View Related
Jul 10, 2014
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
View 9 Replies
View Related
Jul 3, 2015
I have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.
I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.
Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.
View 8 Replies
View Related
Sep 29, 2015
I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;
declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1
[Code] ...
If the condition meets with either of the above 2 conditions, than have to run the actual store proc.
My actual Storeproc in which I need to incorp the above conditions is:
CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,
[Code] .....
View 12 Replies
View Related
Jan 24, 2014
Reformatting data in a PIVOT Table or find a better way to display.
--ORDERDETAIL TABLE
SKUO QTYO ORDERIDO
KUM 1 12345
KUS 2 12345
SUK 1 12345
KHN 4 12345
DRE 1 12345
[Code] ....
Number of SKU's in order could be over 1000.
Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.
Data I am looking to get. MAX of 15 SKUS Per line.
ORDERID SKU1 QTY1 SKU2 QTY2 SKU3 QTY3 SKU4 QTY4 SKU5 QTY5 SKU6 QTY6 SKU7 QTY7 SKU8
QTY8 SKU9 QTY9 SKU10 QTY10 SKU11 QTY11 SKU12 QTY12 SKU13 QTY13 SKU14 QTY14 SKU15 QTY15
12345 KUM 1 KUS 2 SUK 1 KHN 4 DRE 1 HGF 2 FDE 1 CDS 1 GYT 1 POI 3 LKH 2 TTT 4 JHG 8 YUI 2 WQE 1
12345 PMN 1 BVC 1 ABD 1
[Code] ....
CURRENT PIVOT ONLY GOES TO 150 - BELOW
SELECT PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
[11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
[Code] ....
View 6 Replies
View Related
Aug 22, 2014
If you use the LEFT() function for example it provides intellisense support for some of the required parameters.
Is there a way to get that same descriptive text in user defined functions?
Some comment block you define when creating the function?
View 4 Replies
View Related
Apr 7, 2015
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
View 2 Replies
View Related
Feb 11, 2015
I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.
E.g. : a,b,c,d
Converted to result table
result
a
b
c
d
I want to insert each row of the result table into another table. How to do that.
E.g., the table after the function is :
CREATE TABLE #result
(
Subject varchar(100)
)
insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')
So the pseudo code is something like
for each row in #result
insert row into another table
View 9 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Apr 9, 2014
Does sql server 2012 mount points support FileStream feature?
View 1 Replies
View Related
Sep 18, 2014
Is there a command at the CLI or in PowerShell that will list the components installed for SQL? I'm looking for something like the Feature List you can get from running the discovery report from Tools in the SQL Server Installation Center program option.
I'm running SQL Server 2012 on the CORE version of Windows Server 2008 R2 Enterprise.
View 4 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Aug 22, 2014
I need to search on varchar column including zero length string and null value.What is the best way to do it?
View 5 Replies
View Related
Jul 22, 2015
I have a procedure where Login and Password of the database are to be used and right now i have given a valid hardcoded value for them.
How can I get these values from the actual login (I mean sql server authentication value or windows authentication).
View 8 Replies
View Related
Aug 24, 2006
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Jun 2, 2014
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies
View Related
Jul 26, 2004
Can anyone help me? I'm trying to assign @totalvisits the sum of visits.totalvisits in the example below. I keep getting errors concerning the line
"@totalvisits= Sum(visits.totalvisits) AS SumOfTotalVisits"
I can't figure out how to write it out correctly so the sum fills @totalvisits.
--------------------------------------------------
DECLARE @email varchar (50)
DECLARE @totalvisits int
SELECT
@email = users.email,
@totalvisits= Sum(visits.totalvisits) AS SumOfTotalVisits
LEFT JOIN visits ON users.username = visits.username
GROUP BY users.username HAVING ((users.username) = @username)
--------------------------------------------------
Any help would be appreciated!
View 1 Replies
View Related
Sep 21, 2004
I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?
thanks
D.
View 1 Replies
View Related
Feb 2, 2006
I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?
View 12 Replies
View Related
Mar 10, 2005
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
View 2 Replies
View Related
Jul 13, 2004
As part of a nightly process, I must call a stored procedure that uses cursors to update a Sybase linked server from SQL Server. The stored proc was written by someone else and is intended to interface to a finance system to upload new transactions.
The stored proc works fine most of the time but occasionally dies part-way through with the following confusing error message:
[OLE/DB provider returned message: Internally generated command failed to affect a row.]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange::SetData returned 0x80004005: ].
Msg 7399, Level 16, State 1, Server ********, Procedure ********, Line 162
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
I initially ran this stored proc from a perl script via an ODBC link but in the futile hope of fixing this problem I tried manually executing it via isql.exe instead. Both return exactly the same error. I have also increased the OLE/DB query timeout to 15 minutes with no success in stopping the problem.
There seems to be no connection I can spot between when it succeeds and when it fails. When scheduled to run just after midnight it fails about 30% of the time (at a time when there should be a light load on the servers). When run manually it almost always suceeds (I have only managed to get it to fail through running manually once in dozens of attempts).
I can't seem to find anything relevant about this error via Google or any of my usual reference sources so hoped someone else might have a clue as to what the hell's going on. :confused:
The stored procedure uses a cursor to traverse a SELECT statement then performs several INSERTs, UPDATEs and DELETEs depending upon each row processed and attempts (but usually fails) to perform a ROLLBACK upon an error.
I'd appreciate any pointers in the right direction here. Is it because I'm performing cursor-based operations on a linked server? Is it due to the linked server being a Sybase server? Could it be due to locking problems or timeouts? Is my server in a bad mood and simply trying to taunt me? I'm tearing my hair out trying to find more information on this annoyingly intermittent problem and really appreciate any help or tales of similar errors.
Thanks in advance
View 11 Replies
View Related
Apr 20, 2008
hi
pls can any one help me out in how to migrate sql server stored procedures into oracle plsql procedures .
View 1 Replies
View Related
Jul 20, 2005
We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev
View 1 Replies
View Related
Sep 20, 2007
hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????
View 5 Replies
View Related
Oct 24, 2007
ok...i give up.....
can this be done? (sql server 2000)
all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.
here's my current working file output code:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P'
i'd like it to be something like this:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P'
but nothing seems to work.
View 16 Replies
View Related
Jan 28, 2008
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
View 1 Replies
View Related
Jul 18, 2000
I am attempting to compile a stored proc that contains SQL statements that access databases across different SQL servers. I am getting the following error:
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."
Does this mean that I need to put the necessary statements (Set ANSI_NULLS ON etc.) in my stored proc or that I need to configure my SQL Server differently somehow? I tried the former without success.
Thanks.
View 2 Replies
View Related
Jul 12, 2007
Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
View 10 Replies
View Related
Oct 19, 2015
We're trying to troubleshoot a timeout issue, so it was requested that I add a WAITFOR statement (1 hour) in a certain stored proc our application uses. I added it and confirmed that it was working by executing the stored proc in SSMS.
However, when our application (using Entity Framework) calls the stored proc, the WAITFOR statement is ignored.
View 4 Replies
View Related
Jan 18, 2008
I'm in sql server mgmt 2005 and i highlighted a stored proc and clicked on "execute". I need to pass a null value in as the first param value to test this stored proc. what is the syntax, is below correct?
EXECUTE @RC = [SomeDataBaseName].[dbo].[sp_SomeStoredProcedure]
null
,'1234'
,1
View 3 Replies
View Related
