Return Value Of EXECUTE Sp_executesql(SQLStr)

Jun 3, 2006

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

View 1 Replies


ADVERTISEMENT

Execute Sp_executesql And Return A Value

May 13, 2002

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 2 Replies View Related

Return Value Of EXECUTE Sp_executesql(SQLString)

Jun 3, 2006

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

View 4 Replies View Related

Unable To Get Return Value Of Execute Sp_executeSql @sqlString

Aug 21, 2006

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 3 Replies View Related

Sp_executesql Vs. EXECUTE

Dec 20, 2006

please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?

View 3 Replies View Related

Help With Sp_executesql And Return Parameter

Nov 10, 2003

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 5 Replies View Related

Using Delete With Sp_executesql And Getting Return Value

May 19, 2008


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 4 Replies View Related

Problem With Using EXECUTE/sp_executesql

Jan 11, 2000

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 2 Replies View Related

Cannot Execute DDL Sql Batach Via EXEC Or Sp_executesql ???

Mar 26, 2008

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 5 Replies View Related

Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery

Aug 21, 2007

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 4 Replies View Related

Conditional Execute By Execute SQL Task Return Value?

Jun 25, 2007

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 1 Replies View Related

Execute And Return Value Of SP Within Another SP

May 7, 2008

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 3 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

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 3 Replies View Related

Execute String With Return Parameter

Jun 26, 2001

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 5 Replies View Related

Execute Stored Proc And Then Return A Value

Jul 13, 2004

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 3 Replies View Related

How To Capture The Return Code Of An Execute Process Task

Jan 18, 2008

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 4 Replies View Related

Execute SQl Task Return Decimal Type Result

Dec 3, 2007



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 1 Replies View Related

Execute Process Task - Error/Return Code Listing With Descriptive Messages?

May 28, 2008



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 1 Replies View Related

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?

Apr 7, 2008

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 6 Replies View Related

SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task

Aug 9, 2006

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 10 Replies View Related

Sp_executesql

Jul 31, 2006

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 2 Replies View Related

Sp_executeSql

Aug 8, 2005

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 2 Replies View Related

Sp_executesql ¿qué?

Feb 27, 2008

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 3 Replies View Related

Sp_executesql

Jan 18, 2002

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 1 Replies View Related

Sp_executesql

Sep 3, 2004

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 2 Replies View Related

Sp_executeSQL

Jan 7, 2004

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 1 Replies View Related

Sp_executesql

Apr 10, 2008

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 9 Replies View Related

Sp_executesql

Jul 23, 2005

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 3 Replies View Related

Help With Sp_executesql

Jul 20, 2005

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 2 Replies View Related

Sp_executesql

Oct 2, 2006

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 7 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


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 2 Replies View Related

Sp_executesql And Speed

Jul 12, 2006

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 1 Replies View Related

Avoid Sp_executesql With ADO.NET

Jan 30, 2008

 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 1 Replies View Related







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