Return Value Of EXECUTE Sp_executesql(SQLStr)

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



ADVERTISEMENT

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







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