Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Return Value Of EXECUTE Sp_executesql(SQLStr)


Hi,
How can I check return value from
EXECUTE sp_executesql(SQLStr)
Thanks,


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Execute Sp_executesql And Return A Value
This is what I am trying:
set @cntsql = 'select count(' + @dimky + ') as dimcnt from ' + @dimtb + ' where ' + @dimky +' is not null'

set @dimcnt = execute sp_executesql @cntsql

What I want to do is return the count from the dynamically selected database. If I type it in like:

set @dimcnt = (select count('ptky') as dimcnt from pttable where ptky is not null)

it works.... can anyone help... this code is in the middle of a cursor.

View Replies !   View Related
Return Value Of EXECUTE Sp_executesql(SQLString)
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,

View Replies !   View Related
Unable To Get Return Value Of Execute Sp_executeSql @sqlString
Hi All,

Create proc sproc_Insert
@TableName varchar(50),
@InsertColumns varchar(1000),
@InsertValues varchar(2000),
@WhereCondition varchar(200)
as
Begin 
 Declare @CheckStr nVarchar(2000)
 Declare @RetVal int
 Set @checkStr = 'Select * from '+ @TableName + ' '+ @WhereCondition
 execute sp_executesql @checkStr,@RetVal output
 print @RetVal
End

I am not able to retrieve the return value in the above procedure. For example if data exists then 1 else o

 

Thanks & Regards

Bijay

View Replies !   View Related
Help With Sp_executesql And Return Parameter
I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:

Public Function totalrecordsbysql(list as arraylist) as integer
dim RetVal as new integer
dim querystring as string

Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
cmd.commandtype = commandtype.storedprocedure

dim mydr as SqlDataReader

cmd.Parameters.add("@TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
cmd.Parameters.add("@age",sqldbtype.int).value = 18

cn.Open()

try
mydr=cmd.executereader()
catch e as sqlexception
dim err as sqlerror
dim strErrorString as string

for each err in e.Errors
strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
trace.write("sqlexception",strErrorString)
Next

finally

RetVal = cmd.parameters("@TotalRecords").value

end try


Return RetVal
cn.close()
End Function

Now here is the stored procedure:

CREATE PROCEDURE SimpleDynProfileCount

@age int,
@TotalRecords int output

AS

Declare @sql nvarchar(4000),
@paramlist nvarchar(4000)

select @sql = 'select @xTotalRecords = count(*) from profile where 1 = 1 '

// RAISERROR(@sql, 16, 1)

IF @age > 0
Select @sql = @sql + ' AND age > @xage '

Select @paramlist = '@xage int, @xTotalRecords int output'

Execute sp_executesql @sql,@paramlist,@age,@xTotalRecords = @TotalRecords output

select @TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11

View Replies !   View Related
Using Delete With Sp_executesql And Getting Return Value
 
The basic syntax for the sp_executesql with a return value is:
 



Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint, @cnt int OUTPUT'
execute sp_executesql
          N'select @cnt=count(*) from pubs.dbo.employee where job_lvl = @lvl',
          @ParmStr, @lvl = 35, @cnt=@count OUTPUT
print 'count: ' + cast(@count as nvarchar(4))
 
 


This returns a value of 3.
 
I need to change this to return the numbers of rows that were deleted, such as:
 



Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint'
execute sp_executesql
          N'delete from pubs.dbo.employee where job_lvl = @lvl',
          @ParmStr, @lvl = 35
-- Need count
print 'count: ' + cast(@count as nvarchar(4))
 
 


Any ideas on how to modify this so that I can report on number of rows deleted?
 

View Replies !   View Related
Sp_executesql Vs. EXECUTE
please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?

View Replies !   View Related
Problem With Using EXECUTE/sp_executesql
Hi... Everybody,

I am new to using SQL Server and I present to you the following problem that I am facing:

Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?

To be more specific:
I have the following set of SQL Statements that do not seem to work:

------------------------------------------------------------------------------
DECLARE @CustomerID char(6)
DECLARE @OfficeID char(3)
DECLARE @DestinationAccountNo char(7)
DECLARE @TableName char(30)

SET @TableName = 'Users'
SET @CustomerID = '001001'
SET @OfficeID = '001'
SET @DestinationAccountNo = '0001011'

DECLARE @ExecuteString nvarchar(500)
DECLARE @CurDestPatIDChar char(2)

SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

EXECUTE SP_EXECUTESQL @ExecuteString
PRINT @CurDestPatIDChar
------------------------------------------------------------------------------
When I run this in the Query Ananlyzer I get the following error:
Server: Msg 137, Level 15, State 1, Line 0
Must declare the variable '@CurDestPatIDChar'.

The above set of statements do not seems to work with EXECUTE either.

Where as if I run the following query with the same variable declarations as above:

-----------------------------------------------------------------------------
SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '

SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
PRINT @CurDestPatIDChar
-----------------------------------------------------------------------------

I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.

The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.

I shall be grateful if you can provide me with a solution for this,

BR,

Sudhakar

View Replies !   View Related
Cannot Execute DDL Sql Batach Via EXEC Or Sp_executesql ???
Hi There
 
Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.
 
GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?
 
In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.
 
I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.
 
The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.
 
The sql is as follows:
 
--===============================================================================

--DDL script

CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';

CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;

CREATE TABLE TEST_TABLE (Column1 int NULL);

GRANT INSERT ON TEST_TABLE TO TEST_USER;

CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);

GO

CREATE PROCEDURE TEST_PROCEDURES

AS

SELECT GETDATE();

GO

CREATE VIEW TEST_VIEW

AS

SELECT * FROM TEST_TABLE;

GO

--ALTER DDL

ALTER VIEW TEST_VIEW

AS

SELECT GETDATE() AS 'DATE';

GO

ALTER PROCEDURE TEST_PROCEDURES

AS

SELECT * FROM TEST_TABLE;

GO

--DROP DDL

DROP INDEX TEST_TABLE.TEST_INDEX;

DROP TABLE TEST_TABLE;

DROP VIEW TEST_VIEW;

DROP USER TEST_USER;

DROP LOGIN TEST_LOGIN;

DROP PROCEDURE TEST_PROCEDURES;

--===============================================================================

View Replies !   View Related
Conditional Execute By Execute SQL Task Return Value?
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?

Or is it possible to conditional run other control flow items according the the status of SQL task execution?

View Replies !   View Related
Sqlstr.dll Initialization Error
I M Installing Sql Server 2000 On Microsoft Server2003.
At The End Of The Installation I Get The Following Message

Fatal Sqlstr.dll Initialization Error File : ..scr.... Line 422
Can Not Found User32.dll

Pls Help

View Replies !   View Related
Not Able To Create Hash Table Inside Stored Proc Through &&"execute Sp_executesql @strQuery&&"
Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
 
now instead of select query , i want to creat hash table.
so i wrote :

set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through

execute sp_executesql @strQuery  , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
 
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery  through execute sp_executesql @strQuery only as query is dynamic .
 
 
please guide me how to do this?
its very urgent for me.
thanks in advance.

View Replies !   View Related
Execute And Return Value Of SP Within Another SP
I need to get a value from another Stored Procedure to use within another Stored Procedure. This is what I currently have, but it is not even close, I'm sure:

CREATE PROCEDURE dbo.sp_JT_BS01c_Calendar_Build_BufferSheet_DateRange AS

DECLARE @MinOfDMD_WK datetime, @MaxOfDMD_WK datetime

@MinOfDMD_WK = Exec sp_JT_BS01a_CalendarBuild_Min_PlanningWeek
@MaxOfDMD_WK = Exec sp_JT_BS01b_CalendarBuild_Max_PlanningWeek

SELECT @MinOfDMD_WK, [master - weekly_range].week,

@MaxOfDMD_WK, dbo.fn_Bucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK) AS [Inclusion Range]

FROM [master - weekly_range], @MaxOfDMD_WK, @MinOfDMD_WK

WHERE dbo.fnBucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK)=1;



Thanks,

Bob Larson

View Replies !   View Related
Execute Stored Proc And Then Return A Value
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....

CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS

declare @IDFound bigint
declare @LastChangedDate datetime

select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode

if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO

It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable

I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?

View Replies !   View Related
Execute String With Return Parameter
I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.

What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')

Where I would later use @val something like:
Select Value * @val as Total Value

Is there an easy way to do this..i've read and read to no avail.

TIA,
Charles

View Replies !   View Related
Exec Sp_executesql Vs. Sp_executesql And Performance
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View Replies !   View Related
How To Capture The Return Code Of An Execute Process Task
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.

View Replies !   View Related
Execute SQl Task Return Decimal Type Result
 

I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
 
 
The query in the Execute SQL Task is:
select 735.234, 2454.123
 
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
 
I don't want to use a for loop enumerator since there is a single row returned.
 
I simply want to assign these two values to SSIS Double variables (double is the closest match)
 
 
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
 
Thanks for the help

View Replies !   View Related
Execute Process Task - Error/Return Code Listing With Descriptive Messages?
 

I'm having a hard time locating a listing of potential error codes and the meanings for the Execute Process Task component...can someone assist?
 
Thanks

View Replies !   View Related
How Can The Return Code Of An Executable Called By An Execute Process Task Be Stored To A Variable?
How can the return code of an executable called by an Execute Process Task be stored to a variable?

View Replies !   View Related
How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All
 
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
 
Here is my query sample for yours to understand.
 
 

SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union 
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor

 
Why i use @qstring? It is because the query will be changed by different critiera.
 
Regards
Edmund

View Replies !   View Related
SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.

I'm passing in an XML data as a parameter and getting  back resulting XML data as a parameter.  The Execute SQL task is using ADO connection to do this job.  The two parameters(in/out) are type of "string" and mapped as string.

When I execute the task, I get the following error message.

[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I also tried mapping the parameter as XML type, but that didn't work either. 

If anyone knows what's going on or how to fix this problem please let me know.  All I want to do is save returning XML data in the parameter to a local package variable.

Thanks

View Replies !   View Related
Get The Return Code Of &&"Execute Process Task&&"
I am running one .Net console application through "Execute Process Task". In case of error I want to trap the return code of this "Execute Process Task", so that I can put some meaningful error message. Is it anyway possible to trap this return code ?

 

View Replies !   View Related
Return Error Code (return Value) From A Stored Procedure Using A Sql Task
 
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
 
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE.  It is a OLEB connection.
 
I have two parameters mapped:
 
tablename  input   varchar    0    (this variable is set earlier in a foreach loop) ADO.
returnvalue  output  long       1
 
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure.  The failure is ignored and the package completes.  No quite what I wanted.   
 
The first part of the sp is below and I set the value @i and return.
 

CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as
 
Why is it not capturing and setting the error and execute my OnFailure code?  I have tried setting one of my parameter mappings to returnvalue with no success.

View Replies !   View Related
How To Return SqlDataReader And Return Value (page Count) From SPROC
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View Replies !   View Related
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
 
I'm a Reporting Services New-Be.
 
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
 
I added the Report Designer, a Report dataset ( based on a shared datasource).
 
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
 
I'll only reproduce one instance of the error for the sake of brevity.
 
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
 
I've searched on this error and it looks like it's a Visual Basic error :
 
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
 
My guess is that BIDS is creating some VB code behind the scenes for the report.
 
I can't find any other information that seems to fit this error.
 
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
 
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
 
Can anyone help ?
 
Thanks,

View Replies !   View Related
Sp_executesql
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL,  N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT',  @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.

View Replies !   View Related
Sp_executesql
Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson

View Replies !   View Related
Help With Sp_executesql
I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.

View Replies !   View Related
Sp_executesql
hi everybody
How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server.
ie a string can contain insert into a table statement,delete a row from a table statement, update etc.
Thanks all of You

View Replies !   View Related
Sp_executeSQL
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.

I'm using sp_ExecuteSQL and have hit the 4000 character wall

View Replies !   View Related
Sp_executesql ¿qué?
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.

Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.

I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.

Snippet that is not working:

declare @db_name varchar(80)

declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.

fetch

next

from

settings_cursor

into

@db_name



while

@@fetch_status = 0

begin

print 'CHECKING DBOPTIONS FOR ' + @db_name + ' - ( CHECKSUM, CREATE & UPDATE STATS, FULLRECOVERY)'



set @sql_command ='select'

set @sql_command = @sql_command + 'count(*)'

set @sql_command = @sql_command + 'from'

set @sql_command = @sql_command + 'sys.databases'

set @sql_command = @sql_command + 'where'

set @sql_command = @sql_command + 'name = ''@db_name'''

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'page_verify_option_desc = ''checksum'''

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_auto_create_stats_on = 1'

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_auto_update_stats_on =1'

set @sql_command = @sql_command + 'and'



-- select recovery model based upon database name.

if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

set @sql_command = @sql_command + 'recovery_model_desc = ''simple'''

end

else

begin

set @sql_command = @sql_command + 'recovery_model_desc = ''full'''

end



-- include db chaining for Master database

if @db_name = 'Master'

begin

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_db_chaining_on = 1'

end



-- execute sql command.

--print @sql_command



declare @count int

execute @count = sp_executesql @sql_command, N'@db_name nvarchar(20)',@db_name=@db_name



if @count = 0-- no records were returned as the settings were wrong.

begin

select 'Issue with settings. altering now'



if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

alter database [@db_name] set recovery simple

alter database [@db_name] set page_verify checksum

end

else

begin

alter database [@db_name] set recovery full

alter database [@db_name] set page_verify checksum



end



if @db_name = 'msdb'

begin

alter database [@db_name] set db_chaining on

end



-- all databases get these switched on

alter database [@db_name] set auto_create_statistics on

alter database [@db_name] set auto_update_statistics on

end

else

begin

select 'all settings for ' + @db_name + ' are good'

end



fetch next from settings_cursor into @db_name

end



-- clean up

close settings_cursor

deallocate settings_cursor

View Replies !   View Related
Sp_executesql
Hi
I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using
EXEC sp_execute sqlstmt.
Here is piece of code.

DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN c1
FETCH NEXT FROM C1 INTO @v_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @v_SQL= 'DROP TABLE ' + @v_TableName
--EXEC @v_SQL
PRINT @v_SQL
IF @v_Error<>0
BEGIN
SELECT @ErrorCount=@ErrorCount+1
PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName
--GOTO ErrorHandler
END
FETCH NEXT FROM C1 INTO @v_TableName
END
CLOSE c1
DEALLOCATE C1

Please let me know where I am doing wrong.

Thanks,
Rau

View Replies !   View Related
Sp_executeSql
What is wrong in this query..how can I make it to work


DECLARE @strSQL nVarchar(4000)
DECLARE @Name VArchar(100)

--SET @Name = '''sysdatabase'',''sysindexes'''

SET @Name = ''sysdatabase''
SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK) WHERE Name IN (@prmName)'

EXECUTE dbo.sp_executesql @strSQL,
N'@prmName varchar(100)',
@prmName= @Name


Note : I do not want to replace the query as

SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK)
WHERE Name IN ' + @Name + ')' , because my queryplan changes if I do this.

Any work around or anything you guys suggest ..

Thanks.

View Replies !   View Related
Sp_executesql
I have a string which I want to pass into an IF statement. Here's what I have:

declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''

IF cast(@sumclause as int)>0
BEGIN
...
END

I'm first putting it in a string because I'm passing another string into it which can't be seen above.

As you can see, I tried casting it since it had a problem with computing a string as an int, but it still doesn't work. Help?

View Replies !   View Related
Sp_executesql
Hi There

Is a table variable invalid for sp_executesql ?

I am trying the following:

SELECT @SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '

+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out

I get the following message:

Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92

Must declare the scalar variable "@msgTable".

I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.

Can this be done?

Thanx

View Replies !   View Related
Using Output Clause And Return Timestamp And Return Value
I am trying to bring my stored proc's into the 21st century with try catch and the output clause.  In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well.  I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I  have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions.  This is easy enough but how do I do what I had done before?

 I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp). 

 Plus how do I check for concerrency error.  Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View Replies !   View Related
Avoid Sp_executesql With ADO.NET
 In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.

View Replies !   View Related
Sp_executesql And Speed
Hello,
 
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?

View Replies !   View Related
Question About Sp_executesql
Hi all,     I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN   FETCH NEXT FROM keyword_cursor into @1
  SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%'''
  SELECT @paramlist = '@x1 nvarchar(200)'  print @sql  EXEC sp_executesql @sql, @paramlist, @1
  RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN   FETCH NEXT FROM keyword_cursor into @1
  SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%'''
  SELECT @paramlist = '@x1 nvarchar(200)'  print @sql  EXEC sp_executesql @sql, @paramlist, @1
  RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?

View Replies !   View Related
Sp_executesql Question
I have not used this sp. We have a dynamic SQL statement generated by a sp.For performance reasons I would like to use it to reduce the number ofexplain plans created. I would like to understand its usage and pitfalls(if any) to its use. Any comments from the user community?

View Replies !   View Related
Problem With Sp_executesql
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @au_lname
',
N'@au_lname nVarChar',
@au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

Thanks

View Replies !   View Related
Sp_executesql Question
I have this code:
declare @a nvarchar(300), @camp nvarchar(15)

select @camp = camp from ref_activtot_paracnet where tip = 16011

set @a = 'select top 1 '+@camp+' from paracnet where datacalc = ''1/1/2005'''

exec sp_executesql @a

How can I store the value returned by the exec sp_executesql statement into a variable?

View Replies !   View Related
Sp_executesql For Dummies
I have a stored procedure using dynamic SQL and I've been told I can no longer use 'exec (@strsql)' and have to use sp_executesql instead.

Fiiiiiiine...except I don't know how to use it and make it work with the parameter I'm passing in.

The original sproc that I have to change reads as:


CREATE PROCEDURE dbo.sp_LetsGetSomeData(@Filter nvarchar(200) = NULL)
AS

DECLARE @strSQL nvarchar(200)

IF @Filter IS NOT NULL
BEGIN
SET @strSQL = N'SELECT * FROM vwRandomViewName ' + @Filter
END

IF @Filter IS NULL
BEGIN
SET @strSQL = N'SELECT * FROM vwRandomViewName'
END

EXEC(@strSQL)


GO


And is called thusly:

EXEC sp_LetsGetSomeData @Filter = ' WHERE Team = ''RandomTeam'''

When I do this it never adds the filter. I tried to write a sproc like this:

CREATE PROC dbo.LetsGetSomeData2(@Filter nvarchar(200) = NULL)
AS

DECLARE @strSQL nvarchar(200)

SET @strSQL = N'SELECT * FROM vwRandomView'

EXECUTE sp_executesql @strSQL, @Filter
GO


and call it the same way as the original flavor sproc, it's as if I'm asking it to just select all from vwRandomView.

I know I'm doing something massively wrong but I'm just having a brain-dead day and can't make sense of the books online.

View Replies !   View Related
Getting Parameter Value From Sp_executesql
i create a stored procedure that has a tablename as parameter. based on that parameter i use sp_executesql. but i want to retrieve the result from sp-executesql command to perform further actions in the stored procedure. is there a way of doing it? or is this any othe way, wihtouf using dynamic sql?

i have tried the follwoing in the Query Analyzer, assuming a pre-defined table. but
declare @Name varchar(100)
declare @N varchar(100)
declare @Str nvarchar(200)
declare @C varchar(10)
declare @Pd nvarchar(100)

SET @Pd = '@Code varchar(10), @Name varchar(100)'
set @Name = 'asf'
set @C = 'YP'
SET @Str = 'SELECT @Name = Name FROM Vouchers WHERE Code = @Code'
exec sp_Executesql @str, @Pd, @Code = @C
set @N = @Name

print 'Name : ' + @Name

but this does not work. basically, i want to retrieve the value @Name

View Replies !   View Related
Problem With Sp_executesql
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @au_lname
',
N'@au_lname nVarChar',
@au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

Thanks

View Replies !   View Related
How Can I Use Sp_executesql Make This.
I have this code:

USE BDPrincipal
GO

IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO

CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO

exec sp_executesql aquery;



I created that code to prove if it works.
But the result doesn´t appear, and the message is:

Command(s) completed successfully.

I need it to work.

Because I need to create a very dinamic query.

Please help me!

View Replies !   View Related
I Can't Use Sp_executesql Within Functions
Hi all
i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right?
Apparently this is true because for example create the following Function..

Create Function Test(@Input int)
Returns int
AS
Begin
Exec sp_who -- only for Test purpose
Return @Input
End

Now Execute this --> Select dbo.test(12).....
Sql Server will return the following Error

Server: Msg 557, Level 16, State 2, Procedure Test, Line 6
Only functions and extended stored procedures can be executed from within a function.

Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!

Any help greatly would be appreciated.
Kind Regards.

View Replies !   View Related
Sp_executesql Help Needed
Hi,
I want to use the output of the sp_executesql to update a coulmn in the table.
example
-first i run the below to get output
execute sp_executesql @Query, @returnedCount output

-then I want to use that output to update another coulmn in the table
update tableName set coulmn=@returnedCount

I am new to this and cannot figure out how. Can someone please guide me?
thank you!!

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved