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.
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?
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.
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
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
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;
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?
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
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
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.
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...
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'')
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);
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:
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
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
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)
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?
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...
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.
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
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.
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?
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.
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.
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.
