Parameter Returned By A Execute

Mar 3, 2004

Hello,

How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp

set @sql= 'declare @res int select @res=count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'

???????? set @result=exec (@sql) ????????????? Something like this


Thanks
Paulo

View 2 Replies


ADVERTISEMENT

INSERT INTO WITH VALUE RETURNED BY EXECUTE SP(@PARAM)

Oct 27, 2006

Dear friends,

How can insert into a temp table 2 parameters. If it's only on, I dont have problem, but if I have 2 there is a error... The problem is the sintax of the execute SP ... thanks

ALTER PROCEDURE [dbo].[GD_SP_FACTURAS_TOTAL2]

AS

CREATE TABLE #TotaisDir

(

DirTotal bigint,

DirNome nvarchar(10)

)

DECLARE @DIR nvarchar(10)

DECLARE @Return_Status bigint

DECLARE LINHAS_CURSOR CURSOR FOR SELECT DIR_NOME FROM Direccao

OPEN LINHAS_CURSOR

FETCH NEXT FROM LINHAS_CURSOR INTO @DIR

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO #TotaisDir (DirNome,DirTotal) VALUES

('ww', EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR)

FETCH NEXT FROM LINHAS_CURSOR INTO @DIR

END

CLOSE LINHAS_CURSOR

DEALLOCATE LINHAS_CURSOR

SELECT DirTotal FROM #TotaisDir

SELECT SUM(DirTotal) As SOMATOTAL FROM #TotaisDir



ERROR:

Msg 156, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21

Incorrect syntax near the keyword 'EXECUTE'.

Msg 102, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21

Incorrect syntax near ')'.

View 3 Replies View Related

SQL Server 2012 :: Execute SP For Rows Returned From A Query

Nov 27, 2013

I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.

View 5 Replies View Related

SQL Task - Passing In A Parameter - Now Rows Returned

Mar 29, 2007

Hi,



am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.



Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.



Variable:

Variable Name = Subsystem

Scope= Package

Value = pgc

Data Type = string



SQL:



SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?



Have also tried:



SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0



Result Set = Single Row



Parameter Mapping:



VariableName = User:ubsystem

Direction = Input

Data Type=Varchar

Parameter Name= 0

Parameter Size= -1 (have also tried 3 - length of variable)



Oracle Table:



SQL> desc sys_subsystem
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSYSTEM_ID NOT NULL CHAR(3)
SUBSYSTEM_DS NOT NULL VARCHAR2(40)

....

....

...





The Error:



[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".



I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?



SQL:



UPDATE sys_subsystem
SET as_process_fg = 'X'
WHERE subsystem_id = ?0



The parameter mappings are the same as above.



Any assistance here would be much appreciated.



Thanks

Mick



View 5 Replies View Related

Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?

Feb 5, 2008

I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID  @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read    dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!

View 2 Replies View Related

Execute A Query Inside Dataflow And Use The Fields Returned To Continue Dataflow... How?

Apr 17, 2007

Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?

Thanks!!

View 38 Replies View Related

More Than One Parameter In Execute SQL Task

Nov 7, 2006

I am trying to create an Execute SQL task that sets a variable.

This is my SQL

DECLARE @Period AS DATETIME

SET @Period =Parameter0 + '/01/' + Parameter1
SET @Period = DATEADD(m, -1, @Period)

SELECT DATEADD(s, -1, @Period)

This statement parses okay.

I mapped two variables called "User::PeriodMonth" and "User::PeriodYear" in the Parameter Mapping tab to the parameters.

In the Result Set tabl I have mapped a variable "User::PeriodStartDate" to Result Name "PeriodStartDate".

The error I get is the following:

[Execute SQL Task] Error: Executing the query "DECLARE @Period AS DATETIME SET @Period =Parameter0 + '/01/' + Parameter1 SET @Period = DATEADD(m, -1, @Period) SELECT DATEADD(s, -1, @Period) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Online Books are not helpful. They just say you have to bind the parameters to the application variables.

What am I doing wrong?

I want to set another variable called "PeriodEndDate" also. Can both variables be set in the same task?

View 12 Replies View Related

Execute Properly Even If No Parameter Value Is Supplies

Aug 16, 2007

 set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- =============================================ALTER PROCEDURE [dbo].[Product_FindByParameters](@Name Varchar(255),@ManufactureID bigint,@ShortDescription Varchar(255),@ManufactureProductID Varchar(255),@ItemsInStock bigint,@StorePartNumber  Varchar(255))        ASBEGIN    SELECT P.ProductId,P.StorePartNumber,P.ShortDescription,P.ManufactureProductID,P.Name,P.Price,P.ItemsInStock,M.ManufactureName FROM Product P left join Manufacture MON P.ManufactureID=M.ManufactureIDWHERE     ( P.Name like '%' + @Name + '%' OR @Name is null)AND (P.ShortDescription LIKE '%' + @ShortDescription + '%'  OR @ShortDescription is null)AND( P.ManufactureProductID LIKE  '%' + @ManufactureProductID + '%' OR @ManufactureProductID is null)AND (P.ItemsInStock=@ItemsInStock)AND (P.ManufactureID = @ManufactureID OR @ManufactureID is null)END--exec  [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''--exec  [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''--exec  [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,'' First 2 exec statement  gives many data row as result,But why the last donot give any row ;(  ;(how can i rewrite the stored procedure, such  that it gives out put even if i don't supply  ManufactureID as inputkindly help me 

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

Error During Execute Sp With IMAGE Parameter

Sep 17, 2006

Hello.
It is the first time that Im using IMAGE field type..

I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@OutImg IMAGE declared as an output parameter of the procedure.)
(Select @OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.

Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:o

View 1 Replies View Related

Parameter Mapping In Execute SQL Task

Apr 17, 2008

I have 2 questions on this

(1) I know how to use the ? ? ? and 0, 1, 2 notation in Parameter Mapping within Execute SQL Task. However, the interface allows me to give descriptive names to my parameters (other than the ordinals 0, 1, 2, ...). To be more clear, if you go into Parameter Mapping and click in Parameter Name column, you are not just restricted to typing in 0, 1, 2, ... You can type anything you want for the name. Does this suggest that I can use other things besides a "?" in my SQL command?

(2) What is Parameter Size? Is this like a data type? If so, why am I allowed to type in anything I want in there?

View 3 Replies View Related

Execute SQL Task Parameter Mapping

Dec 13, 2007

Hi All,

I am using a stored procedure defined as follows:



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

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

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

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

CREATE PROCEDURE [dbo].[GetPriority] @PriorityID TINYINT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT [Priority]

FROM [MTD Dashboard].[dbo].[Priority] WHERE [Priority ID]=@PriorityID

END


I want to use this stored procedure in a Execute SQL Task. What should be the SQL Statement, Parameter mappings and Result Set?

Can someone please help me in doing this.

Thanks

View 5 Replies View Related

Parameter Mapping In An Execute SQL Task

Mar 9, 2006

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists  (  select name  from sys.databases  where name = ? )  begin   drop database ?;  end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

 

Regards,

DO

 

View 13 Replies View Related

Parameter Passing In Execute SQL Task

Mar 20, 2008



Hi All,

I have a Doubt in Parameter Passing in Execute SQL Task.

In Execute SQL Task, I have an Insert query in which I want to pass the Database Name Dynamically i.e. passing it as a parameter.

Eg.
The query is --------- Insert into [?].[dbo].[DimCurrency] values( value1, value2, value3)

I want to pass this Database name using a user variable. But I am not able to do so.

What is the catch in this, Can anybody please help me out.

Thanks & Regards,
kapadia Shalin P.

View 5 Replies View Related

How To Execute Multiple Reports With The Same Parameter

Mar 29, 2007

We have timed-based subscriptions set up for our standard set of 10 client
reports which run monthly with the appropriate parameters set for each
of our clients. Our management has requested that we provide them with
the ability to run the set of 10 reports together at will. So we would
provide the client parameter and all 10 reports would be generated.
What is the recommended approach to accomplish this? We are using Sql
Server 2005, Visual Studio 2003 and .Net 2.0.

View 1 Replies View Related

Table Name As A Parameter On Execute SQL Task?

Jun 22, 2006

Is it not possible to have table name as a parameter? For example have the SQL something like:

Delete From ? Where ID = ?

.. I get error:

[Execute SQL Task] Error: Executing the query "Delete From ? Where ID = ?" failed with the following error: "Must declare the table variable "@P1".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 5 Replies View Related

Parameter Error On Execute SQL Task

Jun 18, 2007

I have a stored proc which starts like this:



CREATE PROCEDURE dbo.AddAttachmentListItem
@ListID uniqueidentifier,
@AttachmentPath varchar (260),
@DeleteAttachmentAfterSend bit = 0



I have a Script task which generates a GUID and stores it in the variable @[User::AttachmentListId], which is of type System.Object since Guid wasn't an option. Following this is an Execute SQL task (with an OLE DB connection to an SQL Server 2000 database) whose SQL statement is



EXEC AddAttachmentListItem ?, ?, 0



My parameter mapping looks like this (variable name, direction, data type, parameter name, parameter size):



@[User::AttachmentListId], Input, GUID, 0, -1

@[User::AFilePath], Input, VARCHAR, 1, 260



When I execute my package I get the following error:



[Execute SQL Task] Error: Executing the query "EXEC AddAttachmentListItem ?, ?, 0" failed with the following error: "The type is not supported.DBTYPE_GUID". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



This worked earlier when the first parameter was an int and not a uniqueidentifier, but I reworked my design because the GUID was a better choice for what I was doing. Well, at least 'til I got to this point....



Any ideas?

View 6 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

Wont Execute Store Procedure With Parameter

Oct 4, 2007

Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()

Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)



Dim dt As New DataTable

adapter.Fill(dt)

Dim row As DataRow
 Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]

@InputDate Datetime
AS


SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions

Where
dtmDate > @InputDate

Order By dtmDate  

View 3 Replies View Related

Input Parameter In SSIS Execute SQL Task.

Mar 3, 2007

hi

I would like to create a SSIS package that is going to be called by store procedures.

What i have done so far.

1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?

2) I save this package as a DTSX file and will called it from a proc.

My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks

View 1 Replies View Related

How To Execute A Stored Procedure With Output Parameter

Apr 17, 2008

Hi guys. I have a procedure with just one parameter. That's a output parameter. It's type is NVARCHAR
I think there's no problem with the procedure. Now I want to execute it. Can I declare that variable passed by parameter to the procedure ? Must I use the Execute command ?

thanks a lot.

View 3 Replies View Related

Output Assign To Parameter....from Execute Sql Task.

Feb 28, 2008

Hi all,

I'm trying to capture the OUTPUT from Execute Sql task...However when I run, the parameter didn't seem to capture the OUTPUT.

In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: OUTPUT,
Data Type: Varchar,
parameter name: 0,
parameter size: -1

connectiontype: OleDB
sourcetype: direct input
statement ELECT columnx FROM table1 WHERE (columnID=
(SELECT MAX(columnID) ASID
FROM table1 A)

I could be misunderstood on how Execute sql task work on Output.

thanks

View 5 Replies View Related

Transact SQL :: Passing Parameter To Execute Task

Aug 6, 2015

In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything

create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)

[code]...

View 6 Replies View Related

Execute SQL Task Fails When Passing A Parameter Using OLE DB

Apr 26, 2008

I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.

Dan

View 6 Replies View Related

Mixing Parameter Syntax In Execute SQL Task

Feb 5, 2006

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @maxDate datetime
SELECT @maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @maxDate datetime
SELECT @maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

View 1 Replies View Related

Having Issues Passing Parameter Into SQL Execute Task

Oct 12, 2007

Hi, this might be a simple one, but I have been stuck on it for days. I am just getting into SSIS and have been muddling through it for the rest of this package but I am stuck on this. I am using this SQL Execute Task to run some lookup queries and then call the sp_send_dbmail stored procedure. I have this placed in a For Each loop container. I am using a SELECT DISTINCT Branch FROM table1 into an Object parameter, and passing that into the loop container. I am then using a Input Parameter into this SQL Execute Task of type String. I have run a Script task right before this step to ensure that the variable is populated and correct. Any assistance would be greatly appreciated!

Here is the SQL Execute Task :

DECLARE @SQL varchar(2400), @emaillist varchar(200),
@branchMgrEmail varchar(100), @officeMgrEmail varchar(100),
@branchMgrEmpNo varchar(5), @officeMgrEmpNo varchar(5), @subjectline varchar (100),
@Today varchar(10), @BranchNumber varchar(2)
SET @BranchNumber = ?
SET @Today = convert(char(8),getdate(),1)
SET @SQL = 'SELECT rtrim(CONVERT(char(10), PostedDate, 101)) AS Posted_Date,
CAST(Branch AS CHAR(2)) AS Branch,
CAST(Department AS CHAR(2)) AS Department,
CAST(InvoiceNumber AS CHAR(7)) AS Invoice_Number
FROM onbase.dbo.MHC_IncompleteRepairOrders
WHERE Branch = ' + @BranchNumber +
'AND HardCardCount = 0
AND WorkAuthCount = 0
AND QualityControlCount = 0
AND MiscDocsCount = 0'
SET @subjectline = @Today + ' - Repair Order Validation Notification for Branch #' + @BranchNumber
SET @branchMgrEmpNo = (SELECT branchempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @officeMgrEmpNo = (SELECT officeempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @branchMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @branchMgrEmpNo)
SET @officeMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @officeMgrEmpNo)
SET @emaillist = @branchMgrEmail + '; ' + @officeMgrEmail
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@email.com',
--@recipients = @emaillist,
--@copy_recipients = 'email@email,
@attach_query_result_as_file = 0,
@subject = @subjectline,
@body = 'This email contains a list of Stuff

',
@query = @SQL,
@query_result_header = 1,
@query_result_separator = '|',
@query_result_width = 150,
@exclude_query_output = 1,
@profile_name = 'MAIL'
END



This is a copy of the ERROR message that I am receiving. Any suggestions?


failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Send email to Branch Mgr and Office Mgr

View 11 Replies View Related

How-to Execute SSIS Package With Input Parameter?

May 19, 2006

Hi all.

we have SSIS package which runs under SQL Job.

Now we need to modify this package in order to have input parameter

which we want to setup in the "Set Value" Tab of SQL Server 2005 SQL Job |Edit Step |Option window.

Could anybody give me in details how-to setup Global Variable (or something else) inside SSIS Package in order to create input parameter of the package?

I also appreciate an advice how exactly we should assign value to input parameter of SSIS Package inside SQL Job Set Value Tab (example?).

Thank you,

Vitaliy

View 3 Replies View Related

Problem When Passing Parameter To Execute SQL Task

Oct 18, 2006

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?



Thank you!

View 10 Replies View Related

How To Execute A Dynamic SQL With Integer Parameter For Stored Procedure?

Sep 17, 2007

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error:  Unterminated String Constant.
What is the problem?
Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);

View 3 Replies View Related

Execute SQL Task With An INPUT Parameter Of Type DBTIMESTAMP

Dec 18, 2007

Hi Everyone,

I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.)

The command should be something like this:





Code Block

EXEC dbo.UpdateSynchTimestamp ?
I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error:

[Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is strange for a number of reasons:

1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue.
2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything.

If I change the code to include a value hard-coded it works:





Code Block

EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD
It is only when a variable is involved that it breaks.

Finally, here's the Stored Procedure itself:





Code Block

CREATE PROCEDURE [dbo].[UpdateSynchTimestamp]
@NewValue TIMESTAMP
AS
BEGIN
SET NOCOUNT ON;

UPDATE ServerSettings
SET [Value] = @NewValue
WHERE [Key] = 'SynchTimestamp'
END
Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported.

Thanks for reading this!

View 12 Replies View Related

Execute SQL Task (SSIS 2005) SP2 And Resultset To A Parameter

Oct 8, 2007



Hello! I would like to write a value from a column to a parameter in SSIS with the Execute SQL task. The problem is that I will never get a value for the parameter.



You can recreate the problem with the AdventureWorksDW sample database.



1. Drop an execute SQL task in the control flow

2. Set the connection to the AdventureWorksDw database

3. Write this in the SQL Statement box Select Max(FullDateAlterNateKey) as LastDate

From DimTime
4. Set the resultset to single Row
5. Under result set assign LastDate as the Result Name and create a parameter with a default date.
6. Execute the task, that will finish succesfully but the value of the parameter in 5 have not changed.

I have tried to change the scope to both the package level and the task level without any success. The value of the variable is still the default value. I have also tried a string variable without sucess.

Any ideas?

Kind Regards
Thomas Ivarsson

View 5 Replies View Related

Data Type In Parameter Mapping For An Execute SQL Task

Jul 12, 2006

Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.

The input parameter is define as @Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:

SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging

update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DCLoading.dtsx" finished: Failure.

Any help?

View 6 Replies View Related

Execute Sql Task To Set Output Parameter Of Type Integer

Jun 1, 2006

I'm having a heckuva time with creating output parameters based on a query.

Here's what I'm doing. Every day, we copy rows from our mysql table to our sql server table. We only want to copy those rows that are new, so as to not have to recopy the entire table.

So I have a DataReader Source set to Ado.net/odbc provider that connects to the mysql db.
The destination is an OLE connection to the local sql server.

I create an Execute SQL Task.
The connection is set to the OLE connection
The type is direct input
The SQL Statement is "select max(id) from copy_table"

In Parameter Mapping, I create a user variable called maxId that is an int64. That variable is now used as the Variable Name. The Direction is Output. The Parameter Name is 0.

Whatever data type I use for the mapping does not work with the variable type. If the parameter was set to ULARGE_INTEGER, here's the error
[Execute SQL Task] Error: Executing the query "SELECT MAX(stats_id) AS max_id FROM copy_table" failed with the following error: "Type name is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If parameter is set to LONG:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "maxId": "The type of the value being assigned to variable "User::maxId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

I found that if variable and parameter were dates to use datetime for the variable and DBTIMESTAMP for the parameter.

There are an awful lot of combinations between all the possible variable types, and the possible parameter types. Does anyone know the secret combination for your typical integer?

Thanks,
Lori

View 5 Replies View Related







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