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.
--Israr

View Replies


ADVERTISEMENT

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
returned
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
msgbox
saying.. the sp has finished running.

Thanks

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:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

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

 

 

CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT

---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
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO

Thanks in advance

Dave

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
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

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
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

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?

Greetings,
 
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:
 
RETURN @@ROWCOUNT
 
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.
 
Thanks,
BCB

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

Hello,

I have a stored procedur like this:

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

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
Werner

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:
TEST1
3
TEST2

... (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?

Greetings,

 

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?

 

Thanks,

BCB

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?
 Thanks,
Kevin

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)
AS
 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
asp.net 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...
 

BULK INSERT Customers

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)...
 

BEGIN TRY

BULK INSERT Customers

FROM 'c: estcustomers.txt'

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

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH

 
 
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

GO
 
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

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
 

AS
 

SET NOCOUNT ON
 

DECLARE @v_SQL NVARCHAR(2000)

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

BEGIN

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

END
 

IF @PositionType <> 0

BEGIN

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

END
 

IF LEN(@BeginAvailableDate) > 0

BEGIN

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

END
 

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

PRINT(@v_SQL)

EXEC(@v_SQL)

 
-----------------------------------------------------------------------------------------------------------------
 

View Replies View Related

SQL Server Stored Procedure Resultset Unable To View In Crystal Report 5.0

hi
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 ,

TIA
Pankaj

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

Hi
 
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
 
Jav

View Replies View Related

Exec Stored Procedure

Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
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

Hi,

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?

Thanks

Rich

View Replies View Related

Exec Stored Procedure

Hi,

 

  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

hi,

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

 

 

thks,

 

 

View Replies View Related

Exec Twp Stored Procedure In A Main Stored Procedure

Hi there

i have a stored procedure like this:

CREATE PROCEDURE SP_Main

AS
SET NOCOUNT ON

BEGIN TRANSACTION

exec SP_Sub_One output

exec SP_Sub_Two output


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

return
GO


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!

][arco

View Replies View Related

Exec Stored Procedure Permission

Hello,
 
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?
 
Thanks
Arun

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.

Thanks,
ODaniels

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved