I am passing System::ExecutionInstanceGuid to a stored procudure using the Execute SQL task (OLEDB connection). When it is being passed as a GUID (through the parameter mapping screen), the wrong value gets passed to the procedure. If i change the Parameter Data Type to VARCHAR, it passes the correct value.
This is also true for the PackageId.
Has anyone seen this?
Thanks,
Abe
Hi all,
I'm stuck here trying to use the system variable ExecutionInstanceGUID.
During a first DataFlowTask I store the ExecutionInstanceGUID in a Table.
Further in my Control Flow I want to get all rows with the current InstanceGuid, my query is pretty simple:
SELECT ColumnA
FROM D_Data
WHERE InstanceGUID = ?
In the parameter mapping collection I have added a parameter which maps to System::ExecutionInstanceGUID
When I execute my package I have the following error:
"Message: component "OLE_SRC_SData" (1282) failed the pre-execute phase and returned error code 0xC0010001."
I tried to map the parameter to another variable and it works properly, I don't see what's wrong using ExecutionInstanceGUID like this.
Any help will be appreciated.
Sébastien.
Any help in understanding and working around this odd behavior is greatly appreciated!
I have the default SQL database logging enabled (to sysdtslog90).
I start my package with an ExecuteSQL task that writes most of the system variables to an execution log.
Included is the value of the System::ExecutionInstanceGUID variable (which I thought should match the standard SSIS logging executionid value).
I have an error handling ExecuteSQL task that updates my execution log with an end time and the task that failed.
This is right out of the ProjectREAL audit approach.
Problem with the Error ExecuteSQL update is that it is trying to match the SSIS log but cannot.
This is the SQL being used:
select top 1 @failureTask = source
from dbo.sysdtslog90
where executionid = @executionGuid
and (upper(event) = 'ONERROR')
and upper(source) <> @packageName
order by endtime desc
When I enable OnInformation event logging something interesting appears.
The ExecutionGUID changes during validation of my one Data Flow Task in the package YET the system variable does not get updated with the new value!!!!
Log Excerpt:
1 OnInformation DFT Load Customer Test B94FDA4C-46D9-4193-BD87-12CFCB31EA1A Validation phase is beginning.
2 OnInformation DFT Load Customer Test 282F7D2E-329A-49DB-90B6-838053114940 Validation phase is beginning.
3 PackageStart AuditSample 282F7D2E-329A-49DB-90B6-838053114940 Beginning of package execution.
4 OnPreExecute AuditSample 282F7D2E-329A-49DB-90B6-838053114940
5 OnPreExecute SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940
6 OnVariableValueChanged AuditSample 282F7D2E-329A-49DB-90B6-838053114940 ExecutionGUID
7 OnInformation SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940 System Variable - Package Name AuditSample Start Time 11/14/2007 8:56:26 PM ExecutionGuid b94fda4c-46d9-4193-bd87-12cfcb31ea1a
Please also reference http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=420279&SiteID=1
Why are two log files generated when using a Text Log Provider with a Connection String property set to the following expression?
"c:\" + @[System::ExecutionInstanceGUID] + ".log"
I've set up package logging with a view to getting a unique, and linkable log. However, two files are always generated, no matter what the DelayValidation settings may be on the connection manager, file, and package tasks.
Help!
Attached proc is working fine. But how should I pass the value of @LexNexID without manul key in the value. How can I call this proc. to update comment in table tblSEMS_FTP_FINAL automatically
Thk
CREATE PROCEDURE DBO.SP_SEMS_ADD_COMMENT (
@lexnexid int,
@commentstring varchar(4000)
)
AS
DECLARE @Comment varchar(4000)
--DECLARE @CommentString varchar(4000)
--DECLARE @LexNexID INT
--SET @LexNexID = 1
SET @CommentString = ''
DECLARE Test CURSOR
FOR
SELECT
convert(varchar(4000),Comment) COMMENT
FROM dbo.TBLSEMS_FTP
WHERE LEXNEXID = @LexNexID
OPEN Test
FETCH NEXT FROM Test
INTO @Comment
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommentString = @CommentString + @Comment + '{CR}{LF}' + ' '
FETCH NEXT FROM Test
INTO @Comment
END
CLOSE Test
DEALLOCATE Test
--PRINT @CommentString
update tblSEMS_FTP_FINAL
set comment = @CommentString
where lexnexid=@LexNexID
GO
Hi Guys,
Im trying to pass a value from a C# page to the corresponding ASP page.
I want to use this value in my SQL Query on the asp page within a Details View.
Ive made the variable public in c# page.
And im trying to concatenate it to my sql query within the <asp: SqlDataSource.
Thanks.
A beginner's question I'm sure. When visitors have logged into my site, how do I then use their UserName as a parameter in an SQL WHERE clause so that I can pull up their detailed information. I'm using VB if additional code is required.
Thanks in advance.
Hi,
how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn); //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");
I have a DDL with two values: 0 and 1. When I use
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Boolean.Parse(DropDownList1.SelectedValue)
or
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Convert.ToBoolean(DropDownList1.SelectedValue)
to insert the selected value in a sql2k database BIT field I am given this error:
System.FormatException: String was not recognized as a valid Boolean.
How can I solve this problem?
Hi,
i m tring to pass my xml string to my sql server but i receive empty value even tho my xml have it's value.
here's my code,
insert user(userName)
select xml_user
from OPENXML(@xmlHandle,'/NewDataSet',1)
with (xml_user nvarchar(20) 'table/user_name')
my XML code
<NewDataSet>
<table>
<user_name>dorris</user_name>
</table>
</NewDataSet>
Please advice on this matter.Thanks
Hi,
i manage to passs an xml string to sql server based on the reference site(http://www.eggheadcafe.com/articles/20030627c.asp by Robbe Morris). From his way, i can only pass XML in this format
<cart>
<order id="10">
<prod id="1" qty="1"/>
<prod id="2" qty="3"/>
</order>
</cart>
Note : the value is stores as attribute (<prog id="1") . I am just wonder can i actaully pass my xml string to sql Server with this format
<cart>
<order id="10">
<prod>
<id>1</id>
<qty>3</qty>
</prod>
</order>
</cart>
and how can i read the value from this format in my stored procedure?
Regards,
Dorris
Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH
View 1 Replies View RelatedHi,
I have a stored proc as follows:
Create stored procedure sp_test
@Cno nvarchar (1000) AS
SELECT * FROM Contracts WHERE Cno IN (@Cno)
How do I actually pass the parameters to the stored proc?
sp_test "'cn01','cn02','cn03','cn04'" doesn't work.
In VB.NET this doesn't work either:
dim s as string = "'cn01','cn02','cn03','cn04'"
cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000))
cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
TIA
Jeremy Holt
I would like to pass the name of a table in my update query. It goes something like :
set @Table = 'TableA'
UPDATE
@Table
SET
Company_id = @Company_id
However I get a syntax error when I try to pass the table name like this.
What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.
SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHEREcontact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)
I have a dts package file. I am able to execute the file thro C#. I want to pass userid, password, datasource through program. How to do this?
DTS.PackClass package = new DTS.PackClass();
string filename = @"c:abc.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTSPack";
object pVarPersistStfOfHost = null;
package.LoadFromStorageFile(filename, password, packageID,
versionID, name, ref pVarPersistStfOfHost);
package.Execute();
package.UnInitialize();
package = null;
Thanks
Jtamil
Hi ,
I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done:
1) Use SQL task to include "Select Count(*) from TableA"
2) In parameter mapping tab of SQL Task, define the following
Parameter name : Table::Count
Direction: Input
Data Type: Numeric
Parameter Name : @Count
3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out]
4) The constraints has been set as
Evaluation Operation : Expression and Constraints
Value: Success
Expression : @Count >"50000"
In SSIS tab > Variable
I have set the following
Variable Name : Count
DataType : Int32
Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]
Thank You in Advance
I am pretty new to this and hope someone can help and I hope I can explain my problem.
I have a php form
<form action="cpesearch.php">
State: <select name="stname">
<option value ="ALL">all states</option>
<option value ="vic">VIC</option>
<option value ="sa">SA</option>etc etc etc
I am then connecting to my datatbase, assign the variables and doing the SQL query.
if ($dbc = mysql_connect ('localhost','root',''))
{
if (@mysql_select_db('database'))
{
$sname = $_GET['stname'];
$pname = $_GET['prname'];
$sql = 'SELECT * FROM `cpe` WHERE eventdate >= CURDATE() AND state = ''.$sname.'' AND presenter = ''.$pname.'' ORDER BY eventdate ASC';
$r = mysql_query($sql) or die("Query failed".mysql_error($dbc));
while ($row = mysql_fetch_array($r))
{
print "<div id="mainContent"> <p>".$row['state']."</p>".
"<p>".$row['eventdate']."</p>".
"<p>".$row['eventtitle']."</p>".
etc etc etc
So my problem is... I have an events table with presenters and state (and other info). I want to be able to select each presenter and state or all presenters and/or states. Not sure then how to pass the variable into my sql statement if they want to select ALL.
can i pass recordset object(which have 10 records) from vb6 to stored procedures, if yes what is the datatype i have to declare in stored procedure
View 8 Replies View RelatedNeed help.
I’m trying to pass inputs from one stored proc to another but I’m having problems in passing them.
First proc (input_passing) should pass the following inputs – FirstName, LastNmae and RecordID to the second proc (input_receive) – then the second proc will diplay the outcome – YES/NO if there is a match.
------------------------------------------------------------------------------------------
First proc (input_passing)
-------------------------------------------------------------------------------------------------------
DECLARE
@RecordIDVARCHAR(11),
@FirstNameVARCHAR(60),
@LastNameVARCHAR(60)
--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName
FROM
tbInputs
OPEN tbInputs
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
--GET RECORD
SET @RecordID = RecordID from dbo.tbInputs)
END
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
END
CLOSE tbInputs
DEALLOCATE tbInputs
exec input_receive
----------------------------------------------------------------------------------------------------------------- Second proc. (input_receive)
CREATE PROCEDURE input_receive
@RecordIDVARCHAR(11) = NULL,
@FirstNameVARCHAR(50) = NULL,
@LastNameVARCHAR(50) = NULL,
@RecordMatchBIT OUTPUT,
@MatchedOnVARCHAR(400) OUTPUT
AS
-- Declarations
DECLARE @CountINT
DECLARE@FoundBIT
DECLARE @AlertIDDECIMAL(18,0)
DECLARE@AlertCreateDateDATETIME
-- END Declarations
-- ******* Initializations **********
SET @Count = 0
SET @Found = 0
-- DEFAULT to No File Match
SET RecordMatch = 0
SET @MatchedOn = 'NO FOUND'
-- ******* END Initializations **********
-- First check to see if the RecordID Matches
IF( @RecordID IS NOT NULL AND @RecordID <> '' )
BEGIN
IF( CAST( REPLACE(@RecordID, '-', '') AS DECIMAL(18,0)) > 0 )
BEGIN
SELECT @Count = COUNT(*)
FROM tbAlert
WHERE REPLACE(RecordID, '-', '') = REPLACE(@RecordID, '-', '')
IF( @Count > 0 )
BEGIN
SET @RecordMatch = 1
SET @MatchedOn = 'FOUND : RecordID'
RETURN
END
END
END
Josephine
how can i pass report parameter via URL?
my jump to URL link is:
http://itreportsme096/Reports/Pages/Report.aspx?ItemPath=%2fDevelopment%2fhoneylette%2fdetailed+phone+calls
and i want to pass two parameters: billingmonth and phoneno
how can i do that?
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
View 4 Replies View Related
Hi,
I have a slight problem. I would like to pass some form of out of band data to every SP when called using ADO.NET.
A little more info is needed I think. I need to pass a unique identifier to ever SP my application calls and have that identifier available to use by the SP.
As my application has many many SP's in use I am trying to avoid adding an extra parameter to each and every one. I was really hoping for the ADO command object to have a property I could use to pass 'user defined' data to the database engine.
For example:
My SP's write to an audit trail table. The audit trail needs to record the user that executed the SP, this user might not be the same user that logged on to the database (depends on the connection string really). So, instead of adding an extra param to each SP, I want to access some data from the ADO command that was used to call the SP.
Can anyone help, or have I just confused everyone?
Graham
Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].
If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.
Is there a way to pass the "select all" option to a multi-select parameter from a URL string?
Thanks, Eva
Greetings,
I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.
What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.
I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).
What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?
Thanks for your ideas
Hi:
When i try to execute the following stmt using sqlcmd, it is giving me error.
c:>sqlcmd -i "c:sqlconfigDDLLogging.sql" -v LogDir='c:sctclogs'
the error is
Sqlcmd: ':sctclogs'': Invalid argument. Enter '-?' for help.
but when i pass just -v LogDir='csctclogs' it work fine.
The problem is with ":" when i put colon it throws error.
Any help to execute the above command would great.
Thanking you,
Best regards,
Kumar
Hi,
This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10.
Please help.
Thanks
I don't understand how to pass a parameter to a dts package from another. In SQL 2000 you can map a variable with a variable of the package you will call.
View 10 Replies View RelatedI was passing dynamic SQL to a parameter called 'SQL' and executing like =PARAMETERS!SQL.VALUE in the Generic Query Designer. Unfortunately my Dynamic SQL has grown to the point where it will no longer fit in a URL as it can exceed the 2048 limit. I've been tinkering with ways to get this into Reporting services using other methods but have thus been unsuccessful. Anyone have any ideas on this?
Hi all
I need to pass parameters when I call a .SQL file.
Is there a way to do this in SQL Server?
thanks!!!!
Hi guys...
As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report...
Thanks a bunch in advance!
Hi,
I have created an Endpoint based on a stored procedure that has a parameter of datatype XML. I have added a reference to the endpoint in a web project, I have no problems accessing but I do have a question on how to pass XML or an entire XML file to the XML datatype? How is this done in VB.net? I have loaded the XML file into a vb string but this is not compatible with the sql server xml datatype? Any examples anywhere?
Thanks!