Log The Query Executed In Stored Procedure
Jun 19, 2008hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
ADVERTISEMENT
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Nov 8, 2007Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Can A Stored Procedure Be Executed From Within A Select Statement?
Dec 2, 2005Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
How To Check When A Stored Procedure Was Last Called/executed
Jul 23, 2005HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies View RelatedWeird Situation - Stored Procedure Executed Twice
Aug 16, 2006In SQL 2005 I have a stored procedure as below:@sub_no smallint OUTPUTBEGINBEGIN TRANSACTIONINSERT...INTOSET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)INSERT...INTOEXE another_stored_procedure (it includes also BEGIN...COMMIT)EXE another_stored_procedure (it includes also BEGIN...COMMIT)SET @sub_no = .......COMMIT TRANSACTIONWhen Visual Studio (ASP.NET 2005) is open and I run the program,procedure is executed once without any problem. If I publish theproject and put files on another server (or even use the publishedfiles from my machine) I have an error because stored procedure isexecuted twice. @sub_no is used as input/output parameter.I followed/trace the steps in procedure and it seems that procedure isexecuted once with correct value of @sub_no. The second time procedureis executed, the value that it was assigned before COMMIT is used,which gives an error because the INSERT values have NULL values.In ASP.NET I call the store procedure once.What could be the reason ?Thanks a lot for any help.
View 2 Replies View RelatedTransact SQL :: Stored Procedure Executed At The Server?
Nov 5, 2015I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
View 2 Replies View RelatedHow To View The Real SQL Statement That Was Executed In Stored Procedure?
Nov 29, 2006Hi everyone,
I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
Just need that for debugging purposes...
thanks!
DB Design :: How To Find Stored Procedure Which Executed Event
Aug 21, 2015I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
[code]...
Is there a way that I can find which stored procedure or event dropped this stored procedure?
How To Create Stored Procedure In SQL Express That Will Be Executed On Timer?
Dec 3, 2006I need to check the databes on the server side every 3 days and delete old data.
I am using SQL Express.
SQL Rep Srvcs 2005 SP2 - Stored Procedure Getting Executed Thrice (unnecessarily)
Feb 14, 2008Hi all,
I have a Matrix report (SQL 2005 SP2) which uses a stored procedure to retrieve the result set. When I preview or view the report on IE, the SP gets executed thrice (instead of just once)? Anybody know about this? Is this is a bug in Rep Srvcs?
It is slowing down the report considerably.
Any help would be appreciated!
Thanks
SS
- I used SQL Profiler SP-Starting event to track this
SqlDataSource Set To Stored Procedure, Tests OK In Wizard, Does Not Return Data When Executed
Apr 27, 2007With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database.
Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem.
I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected.
So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp.
Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?
Jul 22, 2015isn'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 RelatedSQL Server 2014 :: Find Out Last Executed Date For Any Stored Procedure In Database
Oct 6, 2015Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies View RelatedSSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
Aug 9, 2006I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window
Apr 23, 2008Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Percent Of Executed Procedure
Jul 20, 2005Hi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
View 1 Replies View RelatedIs There A Way To Find Our When A Specific Procedure Was Last Executed.
May 29, 2002Hi,
Is there any system tables or something , which one can query to get when a specific procedure was last run.
Thanks for you help.
The Procedure 'sp_addrolemember' Cannot Be Executed Within A Transaction
Jul 9, 2007I use sp_addrolemember inside a DML trigger and every thing goes well in my computer; I can insert or update into my table and sp_addrolemember is executed successfully, but my customer get this error every time he tries to insert or update into the table.
"the procedure 'sp_addrolemember' cannot be executed within a transaction"
Can anybody help me solve this funny error.
Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access
Jan 12, 2007For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
ddave
How Stored Procedures Are Executed
Apr 20, 2007I was wondering the way in which stored procedures are executed.
For example if I had a stored procedure (A) that executed a sub stored procedure (B).
And two different users where to execute the Stored procedure at the same time what would happen.
Would the second user have to wait while the first user had finished (A and B), dose each user get a copy of the stored procedure or can one user be running stored procedure (B) whilst the other is running stored procedure (A).
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedHow To Get The Last Query Executed?
Feb 9, 2004Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks
View 3 Replies View RelatedSame DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer
Mar 13, 2002I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
DTS File Created After Stored Proc Executed Now What?
Apr 11, 2005I'm trying to fire a DTS package through a stored procedure.
After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.
How would I use the DTS file to update my data.
Thanks,
RB
Select Permission Obligatory Need To Execute Procedure (that Has Executed Permssion)
Jan 30, 2006I've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables.
But some procedures of above cited, in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables.
The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok.
You troube the same, some help please :)
Integration Services :: SSIS Package Fails When It Is Executed By Procedure
Mar 31, 2015I have a very anoying problem with SSIS. I've done new packages into same project, almost identical compared to old ones. They work well in visual studio, but I can't execute them using procedure. Old packages works just fine, but none of the new. Error message is in the end of my story.Visual Studio version is 9.0.30729.1 and SQL Server version is 10.0.4000.0. Is it possible, that these not not updated versions could cause this problem?Package ProtectionLevel is DonSaveSensitive.
Error messages, when package is executed by procedure:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started: 10:36:48 AM
Error: 2015-03-31 10:36:48.48
Code: 0xC0016016
[code]...
SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements
Oct 30, 2015When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Mar 25, 2008Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
Stored Procedure Query Optimization - Query TimeOut Error
Nov 23, 2004How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
Jun 7, 2006Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?
Thanks you!