Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

How To Capture The ResultSet Of EXEC Command In Stored Procedure


Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me.  within stored procedure.
Note: If run the statement using EXEC()   command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
I Exec A Stored Proc From VB... No Resultset
using VB5, SQL 6.5 and ADO to connect how can I get SQL to send me a
signal, flag, whatever when a stored procedure is finished running.

Let me explain... I exec a stored Proc from VB... no resultset
since SQL does all the processing (moving data between tables, blah,
blah, blah) and when it`s done, that`s it... however I would like
SQL to
send me something back to the front end app so that I can put a
saying.. the sp has finished running.


View Replies !   View Related
Exec SQL Task: Capture Return Code Of Stored Proc Not Working
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".


Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure



@OutSuccess INT

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
SET @OutSuccess = 1

Thanks in advance


View Replies !   View Related
Execute Stored Procedure (with Parameters) With An &"exec&" Command
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output

sqlPar = new SqlParameter("@parent_id", DBNull)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.

View Replies !   View Related
Stored Procedure With Resultset _
Hi,I would like to write a stored procedure that returns a resultset.e.g. select student_id, student_subject, subject_marksfrom student_resultswhere student_class_no = ?The input parameter is student_class_no (see ? as per above SQL).The output is a resultset, with each result consisting of student_id,student_subject, subject_marks.Can anyone advise how this is done in sql server stored procedure?Thanks,June Moore.

View Replies !   View Related
Capture Stored Procedure Along With Parameters
Is there a way to capture Stored Procedure Name along with the Parameter passed in SQL 2005 or SQL2000 when a stored procedure is executed?

Thanks !

View Replies !   View Related
How Do I Capture @@ROWCOUNT From A Stored Procedure?
I have a stored procedure that does updates.  The sproc is called from an €œExec SQL Task€?.  The sproc returns the number of rows that were updated using this technique:
How do I capture the sproc€™s return value (number of rows) in a package-level variable?  I eventually want to log this value.  The sproc is not returning a result set.  I€™m new to SSIS so any general guidance would be appreciated.

View Replies !   View Related
Suppess Resultset In Stored Procedure ?
I am try to execute a stored procedure via HTML using an XML template. The last statement in the procedure is a select stmt with FOR XML specified which will provide the output back to the calling program.
The problem is, the stored procedure I am calling in turn calls other procedures, which may also provide result sets.
So, I get this error message back
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="Streaming not supported over multiple column result" ?>

I would really like a way to suppress all the resultsets produced during the execution of the procedure except the last one. Is there a way to do that within a procedure?

View Replies !   View Related
Dynamic Use Of Stored Procedure Resultset

I have a stored procedur like this:

ALTER PROCEDURE dbo.pdpd_DynamicCall
@SQLString varchar(4096) = null

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @SQLString )

select * from #T1

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you

View Replies !   View Related
Execute Stored Procedure In A Resultset[:D]
I have a sql server resultset that returns productid numbers. I would like to loop thru this resultset and run a stored procedure using the productid as the input parameter. Any samples would be helpful. thanks

View Replies !   View Related
Weird Resultset From Stored Procedure
I have 2 stored procedures:

the 1st uses the second sp. to fill a parameter.
the 2nd has one output parameter defined.

When I run the 2nd sp. on its own in MS SQL Server Manangement studio, there is no resultset shown.

BUT when the 2nd sp. is executed inside the 1st sp., I get a '3' in the resultset everytime the 2nd sp. executes. What is causing this?????

This is the way I execute the 2nd sp. inside the 1st sp.:
select 'TEST1'
execute dbo.uspMonthlyHeadCount @soe, @eoe, @Months, @count output
select 'TEST2'

Results are like this:

... (it's displayed more times because the sp. is inside a while)
Why is this happening and what can I do about it? (I'll provide more info if needed!)

View Replies !   View Related
Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?
When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure?  For example, if I have a simple procedure as below to insert a row into the database.  Ideally I would like it to return back the parameter named NewId to my ASP.NET server.  I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName;  1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

View Replies !   View Related
How Do I Capture The Error Message From A Stored Procedure?


I am creating a package that has many SQL tasks.  Each task executes a stored procedure.  I need to capture any error messages returned by the stored procedures.  Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.


I'm not sure where or how I can access a stored procedure message.  What is the best way?




View Replies !   View Related
Use Resultset Returned From A Stored Procedure In Another Stored Procedure
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?

View Replies !   View Related
Feed Stored Procedure With SELECT Resultset
I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 hasabout 50 input parameters. PROC2 is the main procedure that does somedata modifications and afterwards calls PROC1 using an EXECUTEstatement.The input parameter values for PROC1 are stored in a table in mydatabase. What I like to do is passing those values to PROC1 using aSELECT statement. Currently, all 50 parameters are read and stored ina variable, and afterwards they are passed to PROC1 using:EXEC spPROC1 @var1, @var2, @var3, ... , @var50Since it is a lot of code declaring and assigning 50 variables, I waswondering if there is a possibility to run a statement like:EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)Any help on this is greatly appreciated!

View Replies !   View Related
Capture Return Value From Stored Procedure, Use Same In Code Behind Page
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted.  In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber         nvarchar(25), @ID  nvarchar(50), @LName  varchar(50), @FName  varchar(50)
 DECLARE @myERROR int    -- local @@ERROR      , @myRowCount int  --local @@rowcountBEGIN  -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info   WHERE ID = @ID)   BEGIN    RETURN 1END ELSEBEGIN TRAN    INSERT INTO Info(IDNumber, ID, LName,             FName) VALUES (@IDNumber, @ID, @LName,             @FName)             SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
                               COMMIT TRAN RETURN 0 HANDLE_ERROR:  ROLLBACK TRAN  RETURN @myERROR      ENDGO page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"            SelectCommand="SELECT * FROM TABLE_One"                        UpdateCommand="UPDATE TABLE_One WHERE ID = @ID"                         InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">                        <SelectParameters>                <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />            </SelectParameters>             </asp:SqlDataSource>

View Replies !   View Related
Is There A Way To Capture All Bulk Insert Errors From Within A Stored Procedure?
Hi all!!
I have a stored procedure that dynamically bulk loads several tables from several text files.  If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load.  We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes.  Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...


FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')

Produces this (notice column name at the end of the first error)...

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Running this (similar to code in my stored procedure)...



FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')




ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;


Produces something similar to this (which is useless)...
 ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

View Replies !   View Related
Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
This is the error...

Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24

Error converting data type varchar to numeric.
This is the exec point...

EXEC [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType = 4,

@PositionType = 4,

@BeginAvailableDate = '10/10/2006',

@EndAvailableDate = '10/31/2007',

@EmployerLatitude = 29.346675,

@EmployerLongitude = -89.42251,

@Radius = 50






ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType int = 0,

@PositionType int = 0,

@BeginAvailableDate DATETIME = NULL,

@EndAvailableDate DATETIME = NULL,

@EmployerLatitude DECIMAL(10, 6),

@EmployerLongitude DECIMAL(10, 6),

@Radius INT




DECLARE @v_RadiusMath NVARCHAR(1000)

DECLARE @earthRadius DECIMAL(10, 6)

SET @earthRadius = 3963.191

-- SET @EmployerLatitude = 29.346675

-- SET @EmployerLongitude = -89.42251

-- SET @radius = 50

SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius

SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +

'Round(' + @v_RadiusMath + ', 0) AS Distance ' +

'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +

'WHERE ' + @v_RadiusMath + ' <= ' + @Radius

IF @LicenseType <> 0


SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType


IF @PositionType <> 0


SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType


IF LEN(@BeginAvailableDate) > 0


SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate


--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'




View Replies !   View Related
SQL Server Stored Procedure Resultset Unable To View In Crystal Report 5.0
I am trying to get the resultset of a table in StoredProcedure
in Cyrstal Report 5.0 , MS Sql Server 6.5 is the Backend
THere is a main Stored Procedure SP1 which is populatingthe
Main Table say T1 . This SP1 is calling another SP say SP2
which is populating table T2 . A cursor is then set on table T2and
based on certain conditions the main table T1 is populated .
Our problem is that this entire thing goes fine at the ISQL
Level . But when the same SP SP1 is getting called by CrystalReport
then the data inserted in SP SP2 is not visible at all . Itseems that
somehow the data is geting lost thro ODBC etc.
We tried all combinations of T1 and T2 making thempermanent and
temporary . But each way there are some or the other problem .Many
times we are getting `Cursor already exists ` with temp. tables. or
table T2 is already existing .

PLease reply soon ,


View Replies !   View Related
Capture Returned Value From Exec(@Build) Into Another Variable
I am building a SQL statement that returns a number.when I execute the Built SQL statment EXEC(@Build). What I need to donow is take that number that comes back and store it in anothervariable so I can do some conditional logic. Any ideas? See SQL below.Something like @Count=Exec(@Build) which I know doesnt work.Thanks,PhilDECLARE @PullDate varchar(12)SET @PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''PRINT(@PullDate)DECLARE @COUNTER BIGINTDECLARE @SELECT VARCHAR(500)DECLARE @SELECT2 VARCHAR(1000)DECLARE @BUILD VARCHAR(5000)SET @SELECT='SELECT COUNTER FROMOPENQUERY(PROD,'SET @SELECT2='''SELECTCOUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTERFROMCOLLECTOR_RESULTS,WHEREWMB.COLLECTOR_RESULTS.ACTIVITY_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCOUNT.END_DATE ) =to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy''''))GROUP BYWMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'SET @BUILD=@SELECT+@SELECT2PRINT(@BUILD)EXEC(@BUILD)--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD STOREDINTO @COUNTER so I can do a conditional statement.)if @COUNTER>=1beginprint('yes')end

View Replies !   View Related
Stored Procedure Returning Multiple Resultsset, Yet Reporting Services Only Uses The First Resultset Returned
I have written a stored procedure that returns 8 tables.
When I try to design a server report based on this stored procedure, reporting services only recognises the first table and not the other 7 tables.
I am using SQL Server 2005 and Visual Studio 2005.
Thank you in advance

View Replies !   View Related
Exec Stored Procedure
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
if @id=1  insert into ....else if @id=2 update...-----------------------orif @id=1   exec InsertProcedureelse if @id=2   exec UpdateProcedurePlease help

View Replies !   View Related
Exec Stored Procedure

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?



View Replies !   View Related
Exec Stored Procedure


  I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!


 I have an OLE DB Command in my data flow task that I want to update a table with.  I have looked round this forum and on Google and just can not find a solution or what I am doing wrong.  So any help would be great!


The ole db command calls a stored procedure with two input variables:

exec stp_updedgrsholds  status, temp_cr_num


from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.

but nothing gets updated in the database.


If I call the stored procedure the following way

exec stp_updedgrsholds  'C', 87


It updates fine!  I have tried a number of different way with @ symbols and assignment p_status = @status

but nothing seems to work.


Any ideas are much appreciated.


Ninder Bassi 


View Replies !   View Related
How To Exec A Stored Procedure

how do I exec stored procedure that accept parameter and return a single value?


here is example of report


stu_id = ******

stu_name = ****


subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject






View Replies !   View Related
Exec Twp Stored Procedure In A Main Stored Procedure
Hi there

i have a stored procedure like this:




exec SP_Sub_One output

exec SP_Sub_Two output

IF @@ERROR = 0
-- Success. Commit the transaction.
Commit Tran
Rollback Tran


now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.

now i can put the "begin transation" in the two sub stored procedure's. The problem is

what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"

now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data

how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.

Thanks in advance

Mahmoud Manasrah

View Replies !   View Related
Stored Procedure Exec An SQL Statement
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?

View Replies !   View Related
Timeout While Exec Stored Procedure
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running

If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.

View Replies !   View Related
OPENROWSET, EXEC And Stored Procedure
How can I dynamically run code that execute remote sp? I execure remote queries, but now I've to remotely run the undocumented 'dbcc showfilestats' !!

declare @SQL varchar(2000)
SELECT @SQL = "SELECT a.* FROM OPENROWSET('SQLOLEDB','Server=[Server Name];Trusted_Connection=yes', 'exec sp_who') AS a"
exec (@sql)

When you run it, sql answer

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'SELECT a.* FROM OPENROWSET('SQLOLEDB','Server=DSIALBA;Trusted_Conn ection=yes', 'exec sp_who') AS a'.

but the code is correct...

Where it the mistake? :-((

Thans a lot!


View Replies !   View Related
Exec Stored Procedure Permission
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?

View Replies !   View Related
Exec A Stored Procedure And Not Wait For Response
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?

View Replies !   View Related
Capture A Sql Command
Hi All.

I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.

If anyone knows how to do this, please help.


View Replies !   View Related
Error Executing Stored Procedure - EXEC(&#34;any String&#34;)
I'am getting an error when running a stored procedure that issues an EXEC command. The message says that a variable has not been declared when it has.
The error message states "Must declare the variable '@gid_amt'."

Below is code from the procedure:

DECLARE @gid_amt char(18)

SELECT @select_stmt = "SELECT @gid_amt=SUM(" + @elm_nm + ")" +
" WHERE a.ACCTG_TIME_ID = 18 " +
" AND a.ASSET_CTGY_CD = 'FM' " +
" AND a.ST_CD = 'N' " +
" AND a.SCHD_D_CD = " + @schd_d_cd +
" AND a.SCHD_D_KIND_CD = " + @schd_d_kind_cd +
" WHERE c.MLNM_CORP_CD = " + @mlnm_corp_cd +
" AND c.MLNM_PROD_LN_CD = " + @mlnm_prod_ln_cd +
" AND c.CO_CD <> '^^^^' " +
" AND c.SUBSEG_CD <> '^^')"

EXEC (@select_stmt)


If have tried everthing to resolve the problem. If anyone knows of a way to resolve the problem, please let me know.


View Replies !   View Related
Report Error Using Stored Procedure With Exec Sp_executesql
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement.  We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'.  Unfortunately, attempts to call the latter cause an error in the report.  From everything that I've read, there should be no difference between datasets created using either method.  Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1.  The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.

View Replies !   View Related
Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................

FROM @Temp T

WHERE ' + @Criteria )

Unfortunately this is not working.
Giving the errror

Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance

View Replies !   View Related
EXEC Stored Procedure For Every Line Of SELECT Result Table - How?
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert

View Replies !   View Related
In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?

View Replies !   View Related
Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)

I have a stored procedure where I run an insert statement.  I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string.  See examples of what I am talking about in both cases below:

I want to be able to do this (with or without the EXEC) :

DECLARE @NewTableNameOut  as varchar(100)

Set @NewTableNameOut  = 'TableToInsertInto'

                Insert Into @NewTableNameOut
                Select * From tableToSelectFrom


I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.

I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.


DECLARE @NewTableNameOut  as varchar(100)

Set @NewTableNameOut  = 'TableToInsertInto'

                'Insert Into ' + @NewTableNameOut + ' ' +
                'Select * From tableToSelectFrom'


It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. 

Is this possible at all?

If you need more info please let me know.

View Replies !   View Related
Stored Procedure Or Command In Sql
Hi fellows
I am working in a formula about drilling machine. This machine sends the inofrmation to the server basically the name of a blasthole, revolution,pulldown and so on. I have another database with information of the same blasthole, but with different items such as drill diameter,site,area and so on, so i have two tables with information, but both of them have the same blasthole that means that i can relate with a join and identify each blasthole. but teh main point is that i have a specific column con a distance that the machine is drilling for example in a certain moment this machine is drilling 1 feet 3 feet 4 feet until to reach the bottom of a limit. his
 My problem is that i want to create a from to for those distances.
table 1
pk    diameter       bench
dh1  12.5             4050
table 2
blasthole     depth
dh1             1.2
dh1             3.5
dh1             4.7
dh1             5.4
dh1             8.3
dh1            15

with a join i can obtain something like this
view 1
blasthole   diameter    bench       depth
dh1            12.5        4050            1.2
dh1            12.5        4050            3.5
dh1            12.5        4050            4.7
dh1            12.5        4050            5.4
dh1            12.5        4050            8.3
dh1            12.5        4050           15
the most difficult part is to include a new colum taht can start with 0 and to obtain teh previus value from depth something like this
view 1-A
blasthole   diameter    bench      from    depth
dh1            12.5        4050          0          1.2
dh1            12.5        4050          1.2       3.5
dh1            12.5        4050          3.5       4.7
dh1            12.5        4050          4.7       5.4
dh1            12.5        4050          5.4       8.3
dh1            12.5        4050          8.3       15
I was thinking to create a pk2 in order to know how many values have each blasthole and create a loop and maybe i can organize the column from something like this
pk2       blasthole   diameter    bench      from    depth
1           dh1            12.5        4050          0          1.2
2           dh1            12.5        4050          1.2       3.5
3           dh1            12.5        4050          3.5       4.7
4           dh1            12.5        4050          4.7       5.4
5           dh1            12.5        4050          5.4       8.3
6           dh1            12.5        4050          8.3       15
I hope that somebody can understand my question, but i think is a hard one for the forum

View Replies !   View Related
ORDER BY Command In SQL Stored Procedure
How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
CREATE Procedure SS_POList
@CompanyID nvarchar(10),
@PO varchar (20) = '%'

COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost


SS_Sendback.EndusercustomerNumber = @CompanyID
SS_Sendback.EnduserPO Like @PO


I changed it to
CREATE Procedure SS_POList
@CompanyID nvarchar(10),
@PO varchar (20) = '%',
@Order varchar(20)

COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost


SS_Sendback.EndusercustomerNumber = @CompanyID
SS_Sendback.EnduserPO Like @PO



and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.

View Replies !   View Related
ADODB Command (Stored Procedure)
Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.

View Replies !   View Related
Can Stored Procedure Execute A Dos Command?
Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?


View Replies !   View Related
How To Call A Stored Procedure In An Insert Command
hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish

View Replies !   View Related
OLE DB Command And Stored Procedure That Returns Value And/or Error

could someone please tell me :  am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of  the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?



View Replies !   View Related
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?
Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:



CREATE PROC sp_insertNewRecord @procPersonID int,

                                                       @procFirstName nvarchar(20),

                                                       @procLastName nvarchar(20),

                                                       @procAddress nvarchar(50),

                                                       @procCity nvarchar(20),

                                                       @procState nvarchar(20),

                                                       @procZipCode nvarchar(20),

                                                       @procEmail nvarchar(50)


VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)


EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',

'Michigon', '67899', ''


Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"

Dim connection As SqlConnection = New SqlConnection(connectionString)



Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', ''

Console.WriteLine("Row inserted: " + _


Catch ex As Exception





End Try

End Sub

End Class

I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared  (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', ''  "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.

Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang

View Replies !   View Related
Job To Exec Stored Proc To Exec DTS Package
We want to limit the access of our developers to production, yet allow them to run DTS pakages without having access to the package itself. One idea was to create a job that executes a stored procedure that executes the DTS package.
Here's the developer's tale of woe:

When I originall wrote the application I instantiated a DTS Package object in Visual Basic using the SQL-DMO library and executed it directly from the code. This was not desirable because it required SA access on the server.
I've tried the following steps as a member of an NT Global Group. This global group should have data reader/writer access to the database. This login is not SA on the server

Executing master..xp_cmdshell 'dtsrun /Sausroadsgate /E /Nroads_cd_import'
For this option, I was a public member of master and granted execute access to xp_cmdshell.
The error I received when I tried this option with normal permissions is below:
"Msg 50001, Level 1, State 50001
xpsql.cpp Error 997 from GetProxyAccount on line 472"
When I added account to the SA role, it worked correctly.

Next I created a Job. The job had a single step, which was to run a CmdExec task. The command line for the CmdExec task was: "dtsrun /Sausroadsgate /E /Nroads_cd_import". In a stored procedure to be called by the client application I added the following statement:
msdb.dbo.sp_start_job @job_name = 'roads_cd_import'
The error I received when I tried this option with normal permissions is below:
"Server: Msg 14262, Level 16, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('roads_cd_import') does not exist."
When I added the account to the SA role, it worked correctly.

Any help is appreciated

View Replies !   View Related
Listbox Contents To Sql Stored Procedure Command Parameter?

Stuck in a spot and hoping someone will nudge me in the right direction....

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it....

Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' .......other (working) parameter statements.........
Dim parameterDates As New SqlParameter("@Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")


the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?


View Replies !   View Related
How To Call A Stored Procedure Just After A TableAdapter's INSERT Command Executes
Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter?
Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.)
Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13.
Your comments would be most appreciated!!!
-Kurt1 <InsertCommand>
2 <DbCommand CommandType="Text" ModifiedByUser="false">
3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText>
4 <Parameters>
5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" />
6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" />
7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" />
8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" />
9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" />
10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" />
11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" />
12 </Parameters>
13 </DbCommand>
14 </InsertCommand>
15 <SelectCommand>
16 <DbCommand CommandType="Text" ModifiedByUser="true">

View Replies !   View Related
Passing Error Messages From Stored Procedure To Osql Command
I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @@error <> 0

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.


How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.

View Replies !   View Related
How To Execute Stored Procedure By Command Line In Client's Computer ?
I've a S.P. that need to execute in local PC, how can I execute ?

View Replies !   View Related

Copyright © 2005-08, All rights reserved