I want to dynamically build a page for a given stored proc.
Given the name of the sproc how can i return the names, datatypes & sizes of the parameters ?
I then needs to find the fields in the result set to generate the page.
Any ideas ?
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
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies View RelatedHi everyone! Good day!
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Thanks so much!
Hi...
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
Thank you.
Bal.
Hello:
Interestingly enough, I haven't come across this before. I have a SQL stored procedure which takes four parameters; periodstartdate (datetime), periodenddate (end time), hsgradyearstart (int), hsgradyearend (int)
[dbo].[CalculateActivityTotal]
-- Add the parameters for the stored procedure here
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007
AS...
If I run the stored procedure and pass the parameters using EXEC or
sp_executesql "CalculateActivityTotal '2008-01-04 12:00:00', '2008-01-11 12:00:00', 1900, 2008"
the stored proc takes well over ten minutes to run (it does a bunch of aggregation). If I modify the stored procedure to take no parameters, however, and I hardcode the dates in the stored proc using declare and set then it runs in 13 seconds. What could be causing my problem and how I can I go about resolving this? I need to pass the parameters via reporting server. Thanks!
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.
Hi,
I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO
I have 2 stored proc.Stored proc1(sp1) will call stored proc2(sp2).sp2 will return one output parameter of VARCHAR(5000) to sp1.Sp1 will gets the o/p parameter and stores it to a table.
My problem is while returning sp2 output parameter will truncate the size of the o/p I'm getting a part of it's actaul output.I am using SQL server 2000.How we can solve this truncation?
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies View RelatedRecently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc
CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO
NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.
Is it possible to use a parameter as a table name? OR is there another way to do this?
Thanks in advance for your help!
trying to create SP with parameter and i want to use current date getdate() as parameter.. doesn't seem to work. do i have to use getdate in where clause?
here my SP
CREATE PROC report
(@date datetime)
SET @date = (getdate())-1
as
SELECT..here goes my select statement
where (@date = mydatecolumindatebase)
but im getting error on line 3 and 4
........
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 3
Incorrect syntax near the keyword 'SET'.
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 4
Incorrect syntax near the keyword 'as'.
Hi!
I'm trying to execute a SP on a SQL Server 2000, using Delphi 2007 (win32) and DBExpress components.
Work on my computer. Don't work on computers without the delphi instaled.
its not a problem with DLLs. All the Necessary DLL are there (and I think that if one was missing, the windows will call for it hauauh)
Midas.dll is inside the apllication and he driver for the SQL Server is there too.
I don't know if this is the corect place to put my problem... But don't can think of other place...
The SP has this:
Code Snippet
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[ms_TESTE]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[ms_TESTE]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ms_TESTE
(
@Id varchar(12),
@Nome varchar(500)
)
AS
SELECT *
FROM wtDocAM
WHERE
convert(nvarchar(12), idDOcAM) LIKE @Id
and
nom LIKE @Nome
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I assure that I'm passing both the parameters.
One test was with 8% for the @Id and %a% for Nome.
Anyone have experienced this error?
I´d like to hear a solution if anyone can help me
Thanks in advance!
Hi,
I am calling an Oracle stored proc which contains an IN and an OUT parameter also.
To the stored proc, I pass two reports parameteres. I get following error when I execute the report:
PLS-00306: wrong number or types of arguments in call to <Procedure name>
Where am I going wrong?
TIA,
Tanmaya
hi,
im getting an error when i run the stored proc with a string parameter in execute sql task object.
this is the only code i have:
exec sp_udt_keymaint 'table1'
I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.
How do i do this right?
cherrie
I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@UserExists", SqlDbType.Int);
cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output;
cn.Open();
int x = (int)cmd.Parameters["@UserExists"].Value;
cn.Close();
return (x>1);
} And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName
(
@UserName varchar(16),
@UserExists int OUTPUT
)
AS
SET NOCOUNT ON
SELECT @UserExists = count(*)
FROM mys_Profiles
WHERE UserName = @UserName
RETURN
Hi Guys
I am wondering if you could spare some time and help me out with this puzzle.
I am new to this stuff so please take it easy on me.
I’m trying to create procedure which will take 2 input parameters and give me 1 back.
Originally there will be more outputs but for this training exercise 1 should do.
There are 2 tables as per diagram below and what I’m trying to do is
Verify username & password and pull out user group_name.
|---------------| |-----------------------|
| TBL_USERS | |TBL_USER_GROUPS|
|---------------| |-----------------------|
| USERNAME | /|GROUP_ID |
| PASSWORD | / |GROUP_NAME |
| GROUP_ID |< | |
|---------------| |-----------------------|
For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.
'====================================================
ALTER PROCEDURE dbo.try01
(
@UserName varchar(50),
@Password varchar(50),
@Group varchar Output
)
AS
SET NOCOUNT ON;
SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME,
TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID
FROM TBL_USERS INNER JOIN TBL_USER_GROUPS
ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID
WHERE (TBL_USERS.USERNAME = @UserName)
AND (TBL_USERS.PASSWORD = @Password)
'====================================================
and this is what i'm getting in VS.Net while trying to save.
'====================================================
ADO error: A select statement that assigns a value to variable must
not be combined with data-retrieval operation.
'====================================================
I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?
Please help.
CC
I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.
One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.
Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.
Any help is appreciated.
I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;
Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.
Hi all,
I am trying to create a stored proc that will take
in a long string, but the stored proc does not allow me to take in more
than 50 characters at a time. Is there a way to take away the limit?
Please help me out, thanks in advance.
Daren
How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
Hi, I have created an insert stored procedure which inserts a userid into an sql server 2005 field of datatype uniqueidentifier.
What datatype would the parameter be in my c# code?
do i pass it in as an object datatype?
the code is below....
1 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
2
3 cmd = new SqlCommand("spInsKeyswap_history", conn);
4
5 cmd.CommandType = CommandType.StoredProcedure;
6
7 MembershipUser mu = Membership.GetUser(User.Identity.Name);
8
9 object gdUserID = mu.ProviderUserKey;
10
11
12
13
14
15 cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
16
17 cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
18
19 cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
20
21 cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
22
23 cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
24
25 cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
26
27 cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
28
29 cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
30
31 cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
32
33 cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
34
35 cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
36
37 cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
38
39 cmd.Parameters.Add("@status_code", SqlDbType.Int);
40
41 cmd.Parameters.Add("@support_id", SqlDbType.Int);
42
43 cmd.Parameters.Add("@return_value", SqlDbType.Int);
44
45 cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
46
47 cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
48
49 cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
50
51 cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
52
53 cmd.Parameters["@birthday"].Value = txtBirthday.Text;
54
55 cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
56
57 cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
58
59 cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
60
61 cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
62
63 cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
64
65 cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
66
67 //get logged in users user_id from membership
68
69 cmd.Parameters["@dealer_id"].Value = gdUserID;
70
71 cmd.Parameters["@status_code"].Value = 1;
72
73 cmd.Parameters["@support_id"].Value = 0;
74
75
76
77 cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
78
79 cmd.ExecuteNonQuery();
80
81 int returnValue = (int)cmd.Parameters["@return_value"].Value;
82
83 conn.Open();
84
85
86
What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!
Hi,
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
http://search1.workopolis.com/jobshome/db/work.search_cri
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:
Code Snippet
select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName
as DbComboText,
e.EmployeeID as DbComboValue
FROM DepartmentDirectory.dbo.Employees ee
INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN
WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + '''
ORDER BY e.DisplayName'
Any help is greatly appreciated.
Anyone can help with this question: thanks
in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.
if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:
Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin
while exists (*******)
begin
//position A
if certain condition
begin
select msg_out = "error message"
return 1
end
end
end
end
It seems to me that anything inside if - the second begin...end - it wont get executed.
Anyone has got a clue
Any help much appreciated!
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
View 2 Replies View Relatedi want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
I have a job that executes an ssis package. The SSIS package executes successfully in business intelligence Studio however when I make it into a job I get the error message in the topic title. The log file isnt any more helpful which simply says "The package execution failed. The step failed."
I have no idea how to diagnose this problem!
When you run a deployed package manually, there is a nice load of information output showing you what is going on, this would be very good log file info. Is there anyway to write the same output to a log file when the package is run as a Job from SQL Agent?
View 4 Replies View RelatedI 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
I need to find top level node details from xml Execution Plan .
Code :
select distinct mgrCorpID
from
(
select DISTINCT cd.EmpCorpID, cd.EmpName, cd.mgrCorpID, cd.mgrName ,userrole.ROLE
FROM T_Staging_BWDRE_USERROLE AS userrole
INNER JOIN T_Staging_BWDRE_USER AS user2 ON userrole.ID = user2.USERROLEID
[code]....