Assigning Expression Results To Variables
Sep 7, 2007
I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.
At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.
Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation
View 3 Replies
ADVERTISEMENT
Jul 13, 2004
Hi,
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
This is what I've got so far...
[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()
dbcon.Close()
[/code]
The main prob is just what to connect this record to in order to access the individual fields.
Thx :)
View 2 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Dec 24, 2003
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"
What I would like to do is assign specific values to variables based off of the above query. For example:
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this?? Any help is much appreciated! Thanks in advance.
Ron
View 6 Replies
View Related
Feb 14, 2006
Hi,
I have a parent SSIS package that executes various subpackages. Each of the subpackages contain variables that are required for their successful execution, e.g. one has a variable of datetime datatype and a variable of varchar datatype.
This date will essentially change with every running of the package as it specifies the date that additional data has been added to the back-end SQL Server 2005 database.
I can't find anything in the expressions of the Execute Package Task that would allow me to pass these variables into the package.
Can anyone advise?
Thanks,
Paul
View 2 Replies
View Related
May 2, 2007
I have read a few different threads on this and I am still not having any luck. I have 4 dtsx files in my project. One represents the parent package. I have set up 3 Parent Package Variables and have assigned each to the value property of 3 variables within the parent package.
Not sure if I did this correct because when I execute the parent package, it in turn executes the first child package, but the child fails because the variables needed did not set the correct value for a connection string in the child's data connection.
I just need to know how to use these parent variables from Package A within the child (Package B) when setting up my expression for a connection- example: "@[User::varParentFilePath] " - This is the local variable name in the child. This is the variable I am assuming that I am supposed to be able to set the value property of from the parent variable?
I feel that I am close, but may need a little more direction. Thanks
View 10 Replies
View Related
Apr 15, 2008
Somebody provided me with this link http://www.sqlis.com/58.aspx that helped me understand Execute SQL Task and how to assign values to variables from a resulting SQL query.
But I'm not sure if this tutorial addresses my particular example.
Here is what I'm trying to do.
I have a a simple table called VarTable with 2 columns. Col1 is called VarName, and Col2 is called VarVal. As you may have guessed from the column names, I want to hold variable and value pairs in this table.
Right now, I have 2 entries as follows.
Var1, Hello
Var2, Bye
I want to assign Hello and Bye into two variables in my package.
In using the above tutorial, I figured out how to assign one variable by doing the following SQL and using "Single row" Result Set property.
Select VarVal from VarTable
Where VarName = 'Var1'
Then in Result Set section of the Execute Task Editor I map VarVal with one of my user variables.
How do I take care of the 2nd variable in the same Execute SQL Task, or do I need to have another Execute SQL Task? Based on my understanding, in order for me assign multiple variables in one Execute SQL Task, I need somehow construct a SQL statement to return as many columns as I have variables to assign, but this seems hard to do given the variable table structure I have.
Please let me know if my example is unclear.
View 13 Replies
View Related
Nov 16, 2006
I have variables and values stored in a table in this format
process_id | t_variable | t_value
-----------------------------------------------------
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows
how to assign values to variables in SSIS?
basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)
SELECT
@varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
View 3 Replies
View Related
Jul 20, 2005
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
View 2 Replies
View Related
Jan 15, 2008
I have a package with variables which are set to default values, in this instance table names.
I then use a script task to change these variable values, these change fine and I have looked in the watch window whilst debugging to ensure that the values have changed correctly, and they have.
My Script Task then pipes to a Execute SQL Task, where I am using Expressions to set the SqlStatementSource. The Expressions statement uses the variables I change in my script task to form a SQL query.
However, at run-time the Expression appears to be fixed with the default variable values, it does not change even though the variable values have changed. Below is an extract from my expression for the SqlStatementSource.
i.e
Code Block
"USE [DBNAme]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].["+ @[User::StagingEventLossTable] +"](
[PolicyId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
<snip>
...
Is an Expression value fixed from the moment the package is run and is therefore not dynamic during run-time?
If this is the case, how can I get around this? As I want to be able to change this SQL Statement on the fly at run-time.
Thanks
View 8 Replies
View Related
Feb 28, 2007
Hello,
I am using VWD, SQL EE, VB
I have a select statement with a varaiable in it (as below). Rather than displaying the results directly in a DetailsView I want to add the results to a set of variables (Dim XXX as String etc) so I can check to see if each variable contains data or not, before then displaying only those lines which contain data. Can anyone suggest how I go about linking each data field to a variable?
SELECT PID, Postcode, Address1, Address2, Address3, Address4, Address5, Town_City, County, Postcode AS Expr1FROM postcodeWHERE (Postcode = @Postcode)
cheers
marco gwiliani
View 2 Replies
View Related
Apr 18, 2001
I need to manage the number of rows affected by a query.
If I run this script on my database:
SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
the query analyzer gives me back the number of lines affected:
-----------
4,00
(1 row(s) affected)
I need to manage this amount (4,00), so I applied this modification:
DECLARE @NUM_ROWS INT
SET @NUM_ROWS = (SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
)
but it doesn't work!! the SRV returns me this:
Server: Msg 107, Level 16, State 3, Line 3
The column prefix 'TABLE_NAME' does not match with a table name or alias name used in the query.
Do you know another way to store the number of rows affected?
View 2 Replies
View Related
May 28, 2008
Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END
View 6 Replies
View Related
Apr 12, 2008
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
View 2 Replies
View Related
Sep 1, 2015
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
View 9 Replies
View Related
Sep 21, 2015
If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Feb 19, 2004
Hi,
I have three tables -
Table A
Col1...Col2
1.....A
2....B
Table B
Col3...Col4
1.....X
2....Y
TableC
Col1...Col3....Col5...Coln
1........1...........H....Y.
A&B serve as the parent table for C. A&B are the dimension tables and C is the fact table. How do I assign the PK and FK in EM?
Thanks
View 5 Replies
View Related
Mar 8, 2005
Consider the following:
Sub regUser(s as Object, e as EventArgs)
If IsValid Then
Dim stuTable as String
Dim connStr as String
stuTable = "mytable"
connStr = "myConnectionInfo"
'check to see that student num is in the db
Dim connect as SqlConnection
Dim strSelect as String
Dim cmdSelect as SqlCommand
Dim strDbResult as SqlDataReader
connect = New SqlConnection(connStr)
strSelect = "SELECT stu_num, stu_fname, stu_lname FROM " + stuTable + " WHERE stu_num=@stuNum"
cmdSelect = New SqlCommand(strSelect,connect)
cmdSelect.Parameters.Add("@stuNum", txtStdNum.text)
connect.Open()
strDbResult = cmdSelect.ExecuteReader()
Dim stuFName as String
Dim stuLName as String
Dim stuEmail as String
Dim strRandom as String
Dim strPassword as String
Dim i as Integer
Dim charIndex as String
Dim stuMailMessage as MailMessage
Dim strHTMLBody as String
'declare stuFname, stuLname, stuEmail
stuFName = strDbResult("stu_fname")
stuLName = strDbResult("stu_lname")
stuEmail = txtStdEmail.text
'more code follows ....
In my DB I have a table with the columns stu_num, stu_fname and stu_lname. Each of these columns contains rows with data. However, the proceeding code gives me this error: Invalid attempt to read when no data is present (line 58 --> stuFName = strDbResult("stu_fname") ).
What am I doing wrong here? How do I assign the stu_fname in the DB to the page level variable stuFName?
As a little aside how do I say "If no record was found matching stuNum Then" ... ??
Sorry, I'm a .NET beginner ...
View 1 Replies
View Related
Feb 4, 2008
Hi,
Is it possible to assign the column value to a user defined variable?
View 3 Replies
View Related
Aug 24, 2007
Hi ,
I have a typical scenario here where I am trying to assign a variable a value using another variable, for that I am using two execute SQL task , the fisrt one assigns the first variable a value and then the second one uses the first variable to evaluate and then assigns a value to another variable. The first execute sql task works fine , however the second one fails with the following error
"failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "
Here is the query I am using to assign the value in my second execute sql task
select (CASE
WHEN Sum(col1) <> 0 AND ? = 'M' THEN 'M'
WHEN Sum(col2) <> 0 AND ? >= 'T' THEN 'T'
WHEN Sum(col3) <> 0 AND ? >= 'R' THEN 'R'
ELSE 'S'
END) as Current
FROM dbo.TABLE1
I am passing the variable by mapping the parameters and then assigning the value using a single row result set.
Can someone please tell me where am I going wrong?
Thanks
View 13 Replies
View Related
Mar 1, 2008
Hi,
I have a sqldatasource which returns the result I want, but I need to assign it to a label or text box. Is there an easy way of doing this? I attempted it using this code:
PropertyFriendIDLabel.Text = PropUserIdSqlDataSource
Thanks
View 4 Replies
View Related
May 22, 2008
Hello,
I am using SessionParameters within a sqldatasource control, which is the datasource for a formview control.
The Session["PoolID"] has a value which is '0000009485'.<InsertParameters><asp:SessionParameter SessionField="PoolID" Name="pool_id" Size="10" Type="String" ConvertEmptyStringToNull="true" />
</InsertParameters>But when I click 'Save' I get the message 'Cannot insert the value null into column 'pool_id'. Column does not allow nulls. Insert fails'.Do i need to specify something like DefaultValue = <%Session["PoolID"] %>?Thank you in advance for your help.RajanP.S. How can I avoid double line spacing while writing a post? Thanks.
View 2 Replies
View Related
Jun 9, 2008
hi all
can we assign two foreign key to one table .
View 3 Replies
View Related
Apr 5, 2001
Hi,
I want to give 'Execute' permission for all the sp to a particular user.
Any straight way of doing this then to go to each sp and give him permission for every sp.
TIA.
View 2 Replies
View Related
Dec 6, 2006
i am trying on this but hit error
Declare @var int
Set @var = select max(value) from xxx
anyone can help me?
View 2 Replies
View Related
Aug 18, 2004
Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it
When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.
If you need more clarification, post here and let me know.
Thanks in Advance,
Aaron Hawn (aaron@ionzion.net)
View 2 Replies
View Related
Mar 3, 2008
Hello everyone. I wanted to learn to assign and change a schema names used in my table. I dont want a dbo. in my tables. I want to give a personal name like the adventureworks database has. (Such as sales.salesdetails, humanresources.employee and all)
> So how i do create a table with a schema name sales
and
> How do i change my for example dbo.employee to sales.employee ??
I tried
Create table sales.employee
{
statements
}
but it doesnt work...
Please reply ASAP
Thanks
into the world of programming now :)
View 6 Replies
View Related
Jul 23, 2005
hello,i would like to loop through a record set and assign each value to thesame string, (example i would like to return all of the first name inthe authors table = Authors_total.)should i use a cursor or just a loop to do this? I have had sometrouble with the syntax in a cursor.nicholas.gadacz
View 4 Replies
View Related
Aug 11, 2007
Hi
I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.
i am new to this concept.
i need to create a login, which i am successful in creating, but my problem is
I need to allow only this user to access my database and no other user should login my database.
please can any one explain how to do this.
its very urgent.
regards
James Alvin
View 6 Replies
View Related
Aug 8, 2006
Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.
So, I declare a variable:
DECLARE @variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.
I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.
Thanks in advance for any help
View 3 Replies
View Related
