Transact SQL :: How To Select Result From OPENQUERY Into A Variable

Sep 23, 2015

I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?

DECLARE    @HISTORY_ID        INT,
        @NSTATUS    VARCHAR(20),
        @IMPORT_DATE    DATETIME,
        @TSQL            VARCHAR(8000);
SET        @HISTORY_ID = 350721;
   SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
   SELECT @TSQL, @HISTORY_ID;
   EXEC (@TSQL) ;

View 7 Replies


ADVERTISEMENT

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Assign SELECT Result To Variable

Jun 7, 2007

Hi this is probably a very stupid question, but I still need to know.

How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?

View 3 Replies View Related

Storing The Result Of A Select TOP 1 Into A Variable

Sep 7, 2007

hello

Does anybody know how to store the result of a select top 1 into a variable??

I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc


And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc

But none of them work


Any ideas??
Thanks

View 1 Replies View Related

How To Use Variable To Receive The Result Of A Select Statment. ?

Jun 21, 2002

Good morning;

My Problem is :im my transaction i use insert code like the following :

" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"

In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.

" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "

I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.

Thinks.

View 1 Replies View Related

How To Write A SELECT Statement And Store The Result In A Session Variable

Nov 6, 2007

I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns


ContactID

View 1 Replies View Related

Transact SQL :: Passing Variable In Select Statement

Apr 21, 2015

Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.

My error is Invalid column name 'A113', etc.  However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary

-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID

[Code] .....

View 4 Replies View Related

I Want To Show OpenQuery() Result With Local Table Values

May 12, 2008

Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks

View 2 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

OpenQuery Using A Variable

Jan 29, 2004

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.

View 5 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

OPENQUERY And Variable Question

Aug 6, 2007

I am trying to create a stored proc that uses OPENQUERY to get data from a db2 database. Can anyone look at the code below and let me know what I am doing wrong.
This is my error msg:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'SELECT'.

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near ')'.




This is my code:

USE [FVDashBoard]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spDaily_TBT_Load_OpenQuery]

as

-- define variables

declare @FM_DT char(10)

set @FM_DT = (SELECT CALENDAR_DT FROM dbo.PROD_SCHD WHERE(ISSUER_NO = 90)AND (CALENDAR_DT =CONVERT(VARCHAR(10), GETDATE() - 2, 120)))

declare @remotesql nvarchar(4000)

set @remotesql =

'select * from OPENQUERY (BACARDI, '+

'SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@FM_DT+ ')'

PRINT @remotesql

EXEC (@remotesql)



Thanks!

View 6 Replies View Related

SSIS Variable In Openquery Stmnt

Oct 3, 2007



Does anyone have an example of how to use a SSIS variable in an openquery stmnt. I am having a hard time finding a way to pass the variable to the openquery.

Thanks,

View 1 Replies View Related

SQL Tools :: How To Use A Table Variable As Parameter For OPENQUERY

Jul 9, 2015

Something Like:

DECLARE
@TSQL VARCHAR(8000)
DECLARE
@VAR TABLE (VAR1
VARCHAR (2))
INSERT
INTO @VAR
values ('CA'),('OR')

[Code] ....

View 4 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Transact SQL :: Using Literal In Openquery Where Clause

Aug 14, 2015

I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error   incorrect syntax near '32810'.

declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122'
declare @sql varchar(500) =
'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')'
exec(@sql)
selecting @sql it looks like
select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')

View 6 Replies View Related

Transact SQL :: Openquery Update With A Parameter?

Sep 17, 2015

I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.

This is the gist of what I need to do in a stored procedure

DECLARE    @ID1        INT,
        @SQL1        VARCHAR(8000),
        @STATUS     VARCHAR(10),
        @DATE        DATETIME;
SET        @ID1 = 350719;
SET        @STATUS = 'COMPLETED';
SET        @DATE = GETDATE();
SELECT    @ID1;
SELECT  @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID =  ' + CAST(@ID1 as nvarchar(30)) + ''')'
SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE;
EXEC (@SQL1);

View 5 Replies View Related

FoxPro Linked Server And Passing Variable Within OPENQUERY

Mar 8, 2005

I'm posting this because I found this solution after much digging.

The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.

First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:

http://support.microsoft.com/default.aspx?scid=kb;en-us;314520

Here's code for a solution:

DECLARE @OPENQUERY nvarchar(4000),
@TSQL nvarchar(4000),
@FAMILY CHAR(10)

SET @FAMILY='Touring'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''

SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'

EXEC (@OPENQUERY+@TSQL)

All shown are single quotes.

In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters

In addition, if wanting to build a dynamic where clause, you could do something like:

SET @TSQL = 'select cov,family,model from vinmast '
IF <some condition met to include FAMILY filter>
Begin
SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']'''
SET @TSQL=@TSQL+ ')'
End
-----------------
Here's the entire Stored Procedure:


CREATE PROCEDURE dbo.ewo_sp_DUTLookup
(
@DUTPROJECT char(25)=NULL,--Project
@DUTFAMILY char(10)=NULL,--Family
@DUTMODEL char(20)=NULL,--Model
@DUTYEAR char(4)=NULL,--Model Year
@DUTBEGIN char(25)=NULL,--Beginning of COV/DUT number
@DEBUG int=0
)


AS

DECLARE @OPENQUERY varchar(4000),
@TSQL varchar(4000),
@TWHERE varchar(4000),
@intErrorCode int

select @intErrorCode = @@ERROR,
@TSQL='',
@TWHERE=''


IF @intErrorCode=0
Begin
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast '
End

set @intErrorCode = @@ERROR

IF @intErrorCode = 0 and
@DUTFAMILY is not NULL or
@DUTMODEL is not NULL or
@DUTPROJECT is not NULL or
@DUTYEAR is not NULL or
@DUTBEGIN is not NULL
set @TWHERE=' where '



-- Check for Family criteria
If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND '
set @intErrorCode = @@ERROR

-- Check for Model criteria
If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND '
set @intErrorCode = @@ERROR

--Check for Project criteria
If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND '
set @intErrorCode = @@ERROR

--Check for Model Year
If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND '
set @intErrorCode = @@ERROR

--Check for beginning of DUT
If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0
Begin
SET @DUTBEGIN=RTRIM(@DUTBEGIN)
SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND '
End
set @intErrorCode = @@ERROR


IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND '
Begin
set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3)
select @intErrorCode=@@ERROR
End



SET @TWHERE=@TWHERE+''')'

IF @debug<>0 and @intErrorCode=0
Begin
print @intErrorCode
print @OPENQUERY
print @TSQL
print @TWHERE
print @OPENQUERY+@TSQL+@TWHERE
End

IF @intErrorCode=0
EXEC (@OPENQUERY+@TSQL+@TWHERE)
GO

Peter

View 2 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

Aug 10, 2015

One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?

View 2 Replies View Related

Update Openquery (linkedserver, 'select...) Set Field = #

Sep 23, 2004

After running the following OpenQuery...

UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0

----The START_ORDER_NO field contains a 48

SET START_ORDER_NO = 1~9

----The START_ORDER_NO field contains 49~57 respectively.

SET START_ORDER_NO = 10
---The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
---The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
---The START_ORDER_NO field contains 12849

incrementing by 256 as I increase the value passed...

ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).

The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'

The START_ORDER_NO field is a Numeric(8,0)

I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.

I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...

View 1 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Getting SQL Result In A Variable

Nov 14, 2006

Hi there

I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.

how do we achieve that?



thanks and regards

Rahul Kuamr

View 8 Replies View Related

Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into

Jul 9, 2002

When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks

View 1 Replies View Related

How To Put Result From EXEC Into A Variable

Jul 20, 2005

Hi!Can anybody give me a hint how to put sa resut from EXEC into avariable.EXEC is called:EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)....)Thanks!Mario.

View 8 Replies View Related

Query Result In A Variable

Sep 5, 2006

Hello Again, did you miss me?

ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:

DECLARE @A VARCHAR(50)

@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'

There is a form to do it?

Thanks Friends

View 1 Replies View Related

Result From Sql Task In Variable

Feb 21, 2008

i need to know how many rows are in a certain table and store this value in a variable in order to process this variable later in the package. of course i can build a data flow task within a row count component but as far as i understand it's necessary to read all data from a data source in order to use the row count component. now the question is if it's possible to use a sql task in the control flow and put a select count(*) statement within the task and then write the result of this select statement into a variable. shouldn't that be much more faster than using a whole dataflow for this problem?

View 1 Replies View Related

Setting Result To A Variable

Jan 16, 2008



Hi,
below is the sql statements for my web service using C#.




Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"

SqlCommand comm = new SqlCommand(sql, conn);




Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?

View 5 Replies View Related

Simplest Way To Get A Query Result Into A Variable

May 25, 2004

Hello,

I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?

What do you think? A simple example would be nice.

Cheers
J

View 11 Replies View Related

Storing The Result Into A Variable W/o Cursor

Feb 1, 2007

Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View 1 Replies View Related

Can We Set Result Of Dynamic Query To Variable?

Dec 13, 2007

Is this possible as given below

declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end


But its not working....

can any one help me out in this.....

Thnx

Parag

View 1 Replies View Related

SQL Task, Result To Variable Errors Out

Jan 31, 2008

I have looked thru several similar threads with errors like this, but have not found a resolution. I have a SQL Task that runs this query:




Code Snippet
select NetRevenue = cast(sum(Base_Price + AL + MI + PO) as dec(10,2))
from Lancelot.DataWhse.dbo.GrossMarginDetail
where tran_date_key <= ? + ' 23:59:59' and
label_group <> 'X' and
not (ar_ship_key in ('S999991','S998101''S998102')) and
Document <> 'Cust Bal Debit Memo'






There is a parameter mapping of "User::LastSaturday" of type date. I also have a result set with a result set name of "0" with a variable name of "User::GrossMargin," which is defined as a double in the package. The task has a resultset type of Single row, and at the moment the answer that is returned is 66228637.10.

If I change the package variable to a type of string it of course works, but then I cannot do comparisons against it. I have step by step manually copied this from an existing DTSX, so I am baffled why this isn't working, and I'm ready to throw myself under a truck!

I also tried to bring it in as a string, then convert it in a script task, but I cannot figure out how to reference the input and output variables. I can't seem to find any relevant docs on how to do that...

If anyone has any ideas, I and my sanity would greatly appreciate it.

View 5 Replies View Related

Store SQL Task Result As Variable

Mar 18, 2008



Help - I am having a moment!

I am building a simple package that looks at the results of a sql query, prior to running the next step.

Basically Outline and settings


SQL task


Result Set: Full result set

SQL Source: Direct Input

SQL Statement: Select Count (*) FROM X

Parameter Mapping


Variable Name: User::C

Direction: Return Value

Data Type: Numeric

Parameter Name: NewParameterName

Result Set = NewResultName: User::C

Precedence Constraint


Evaluation Operation: Expression and Constraint

Value: Success

Expression: @C > 0 ( I originally tried User::C and received an error message)

Execute SQL Server Agent Job


If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "

TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)


Any thought or suggestions would be greatly appreciated.

r/ Anthony

View 7 Replies View Related







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