How Do You Pass Variables To A Statement Plz Help

Mar 2, 2005

iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong

here is the code


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20)
set @TABLEDESC = 'ClientComment'
set @DBO = '.dbo.'
set @BDFR = 'Commander' + @DBO + @TABLEDESC
set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC
set @EQID = '80_300_113'
insert into @BDTO
select from @BDFR where Eqid = @EQID

View 5 Replies


ADVERTISEMENT

Pass Variables

Aug 8, 2007

Hello,

I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks


I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.

The output of print is:

dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"


Error is:

Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error


if only the output is run in the query analyser then the error is:

The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.



/*********************************************************************************

uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/

ALTER procedure [dbo].[uspCEMTradeExecutePackage2]

@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)


as

declare @returncode int
declare @cmd varchar(1000)

declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)

set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName

set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'

print @cmd

set nocount on

begin try

exec @returncode = master..xp_cmdshell @cmd

end try

begin catch

exec @LastGoodVersionSP

DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')

EXECUTE uspErrorReporter @msg
end catch

set nocount off

View 3 Replies View Related

Pass Variables From Trigger To SP ?

Jan 26, 2004

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIA

View 14 Replies View Related

HELP! Cannot Pass GUID's Through Variables?

Aug 17, 2006

I have a project that uses GUID's througout and I'm completely stumped.

1) I create a "batch" GUID to batch the records I'm about to process.

2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string ---works fine

3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable

4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:

a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)

This is where things get tricky:

I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task --- error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).

I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.

I've tried using the type Object and Casting to anything and that doesn't work either.

I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.

All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?

Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.

Thanks!

View 4 Replies View Related

How Do I Pass VB Variables To The Asp Parameters Of An SqlDataSource?

Feb 29, 2008

I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.

 

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"

            OldValuesParameterFormatString="original_{0}"


            SelectCommand="sp_diplomaViewQualifications"


            UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"

            DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"

            SelectCommandType="StoredProcedure">

            <UpdateParameters>

                <asp:Parameter Name="original_qualId"
/>

                <asp:Parameter Name="quantity"
/>

            </UpdateParameters>

            <DeleteParameters>

                <asp:Parameter Name="original_qualId"
/>

                <asp:Parameter Name="createdBy"  DefaultValue="TEST"/>

            </DeleteParameters>

            <SelectParameters>

                <asp:Parameter Name="masterKey"
/>

            </SelectParameters>

        </asp:SqlDataSource>

 

My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.

 

This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)

How do I pass this into the update and/or delete part
of the command field on the ASP page? 

View 3 Replies View Related

How To Pass DB Connection To SSIS Through Variables?

Jan 14, 2008

Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net


Regards
Ravi

View 1 Replies View Related

Pass Variables Across Forms, Then Insert Into Database

Nov 30, 2004

Hey Guys:

--- Not sure if this should be moved to webforms forum, or if it belongs here ---

Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.

What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.

Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.

I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.

I'm using asp.net 1.1, VB.net and SQL server.

Thanks,
Brian Sierakowski

View 1 Replies View Related

Any Way To Pass Command Line Variables To Osql

Sep 13, 2004

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.

Is it possible to do this.

View 1 Replies View Related

How To Pass Values From ASP DOT NET To SSIS Package Variables

Jun 1, 2006

Hi

Does anybody know how to pass values from asp dot net to SSIS package variables ?

Currently I have an SSIS package for monitoring windows service... for that...

I have to pass the Server-IP Addrress, UserName, Password, Service Name as Parameter.

I would like to pass these parameters through an Interface from RUN TIME.

Please help this problem

Regards

Deepu M.I



 

 

 


 

 

View 1 Replies View Related

How To Pass DB Connection String Through Variables To SSIS?

Jan 14, 2008

Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net


Regards
Ravi

View 1 Replies View Related

How To Pass Large Queries Into Variables In SSIS?

Nov 26, 2007








Hi,
I want to pass below given query into a variable

"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ <POS_MONTH>]]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ <POS_DATE>]
GO

SELECT * INTO [dbo].[<POS_DATE>] ]
FROM SG_POS_Template
WHERE 1 = 0;
GO"
Where [<POS_DATE>] is a parameter by which value will be assigned dymanically.....anybody please help me out....!!

View 5 Replies View Related

Integration Services :: Pass Variables Parent To Child Packages In SSIS 2012

Aug 8, 2013

How to pass variable from Parent to child and child to Parent Packages  is this possible in SQL SSIS 2012. I need this only in SSIS 2012 ...

View 6 Replies View Related

String Value Not Being Pass To Sql Statement

Jul 24, 2007

How can I get the UserID to pass to the sql statement?  The date is being passed but nothing else
Thank you.
My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime
dtToday = DateTime.Today
'Create connectionDim con As New SqlConnection(_connectionString)
'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd
.Connection = con
.CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here
.CommandType = CommandType.Text
'Add Parameters.Parameters.AddWithValue("@UserID", UserID)
.Parameters.AddWithValue("@Today", Today)
End With
'Return DataReader
con.Open()Return cmd.ExecuteReader()
End Function

View 15 Replies View Related

How To Pass The Excel Sheet Names To The Excel Source Control Through Variables

Feb 22, 2006

I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.

For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".

And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.

So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.







Thanks & Regards,

Prakash Srinivasan

View 3 Replies View Related

How To Pass Paremeter To A Select Statement?

Jul 28, 2006

I have this.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; string strQuery = "SELECT * FROM enews WHERE ((name_nws LIKE %'" + strSearch + "'%)"; SqlDataAdapter newsResultAdapter = new SqlDataAdapter(strQuery, strConn); //create a new dataset DataSet dsNewsSearch = new DataSet(); //populate the dataset with the "board_mtg_date" table from the database newsResultAdapter.Fill(dsNewsSearch, "enews"); //go through each rows in the dataset foreach (DataRow dr in dsNewsSearch.Tables[0].Rows) { int newsID = Convert.ToInt16(dr["id_nws"]); } I'm trying to pass a value to the Select statement with the variable "strSearch". It kept giving me error on this section.
The error is:
[SqlException (0x80131904): Line 1: Incorrect syntax near 'Challenge'.]"Challenge" is the text I passed it in.

View 17 Replies View Related

Pass Column Name Using Parameter In SQL Statement...

Oct 19, 2007

Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...

View 5 Replies View Related

Like Statement Is Not Working When I Pass A String

Mar 23, 2008

Hi,
I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.


my query is:

"select username from registration where username like '" + TextBox1.Text + "%'";

It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.


View 2 Replies View Related

Transact SQL :: How To Pass A String Into IN Statement

Apr 20, 2015

I want to run an update command where the user types in  a CSV value and the query runs.  If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail).

declare @SITE_ID int
declare @txtSchedule varchar (500)
set @SITE_ID=1
set @txtSchedule='5,6'
select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule .

View 3 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.


CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 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 "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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 "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

How To Pass Result From Case Statement To An Equation?

Dec 28, 2007

How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak)  + (PMBreak)  AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT   EmployeeID  , LastName  , FirstName  , LocationCode  , UserID  , Today  , Login  , AMBreakOut  , AMBreakIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64    THEN '45'  ELSE '60' END AS AMBreak , LunchOut             , LunchIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 0 AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 66    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 67 AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 81    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 97  AND ISNULL(DATEDIFF(Minute,  LunchOut, LunchIn),0) <= 111    THEN '45'  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0)  >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126    THEN '60'  ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn            , CASE  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19    THEN '0'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34    THEN '15'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49    THEN '30'  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64    THEN '45'  ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon             ,(LogOut - LogIn) + (LunchBreak) -(AMBreak)  + (PMBreak)  AS TimeWorked 

View 7 Replies View Related

HELP: How Do I Pass Variables From Control Flow To Data Flow

Mar 9, 2007

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

View 17 Replies View Related

Using Variables In SQL Statement

Nov 21, 2006

I am trying to use two variables in my SQL statement to query an access database and then pass the results to a datagrid. Nothing shows up in my datagrid. I think that the syntax on my SQL statement is wrong. I am really not sure how to embed the variables, especially since there are two. I really need help with this, it is for work. The code is posted below. Thanks.
Sub Search_Click( s as Object, e as eventArgs)
Dim conLibrary As OleDbConnectionDim Category As StringDim Search_Field As StringDim dstResults As DataSetDim dadResults As OledbDataAdapterDim dtblBooks As DataTable
conLibrary = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:ewpsychiatrylibrarydb.mdb")conLibrary.Open
Category = ddlSearch.SelectedItem.textSearch_Field = txtSearch.textdadResults = New OledbDataAdapter("Select * From rec WHERE '"& Category"' "="  '"&Search_Field"'", conLibrary)dstResults = New Dataset()dadResults.Fill(dstResults, "Table1")
dgrdResult.DataSource = dstResults.Tables(0).DefaultView
dgrdResult.DataBind()conLibrary.closeServer.Transfer("results.aspx")
end Sub

View 1 Replies View Related

How To Set Up Variables In Statement

Jul 29, 2013

The below code connects to a Teradata table and downloads the record set into my excel spreadsheet. No problem. The issue is: using a WHERE clause in the SQL statementI have a form with three variables on the form, (one is a date field and requires a range; (1) DateFrom to DateTo), (2) StatusX and (3) ErrorTypeX. I need to use any combination of these variables to retrieve the desired record set. i.e. variables 1 and 2, or 2 and 3, or 1 and 3 etc. or none of the variables which would return all records.how to set up the variables in the SQL statement?

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim recset As ADODB.Recordset
Set recset = New ADODB.Recordset
Dim cmdSQLData As ADODB.Command
Set cmdSQLData = New ADODB.Command
Dim RowCnt, FieldCnt As Integer

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Pass A Column Name Using Parameter In Select Statement

Sep 5, 2014

I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.

Here's the table structure, let's call it #temp

[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]
Bike, 200
Accessory , 100

I have a table in the DB created as [ProductSales]

Here's what works for me:

insert into [ProductSales](Product, Sales)
Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp

But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.

Here's what I tried to do:

Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")
Declare @SQL varchar(MAX)
SET @SQL='insert into [ProductSales](Product, Sales)
Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp'
Exec @SQL

But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?

View 1 Replies View Related

Using Variables In SQL Statement (.NET 3.5, VB, Oracle)

Jun 5, 2008

Hi Guys, I am trying to manipulate an SQL statement based on the return from a menu with auto postback enabled.Simply I am working with dates, so I will declare variables for today, next week, last week and so on.How do I then use this in the SQL? I assumed that if I declared the following Dim my_today As String = Format(Date.Now(), "dd/MMM/yyyy")Then in the SQL I used SELECT *  FROM my_table item_date = @my_todayI would get a result, but I get an error. What is the right way to do this? Many thanksSteve 

View 7 Replies View Related

Using Variables In Openquery Statement

Mar 27, 2001

Does anybody know how to pass variables to openquery statement? I executed the following statement against DB2 mainframe
and got an error message.

Thanks in advance

Hung-Ban


declare @deptname varchar(20)
select @deptname = 'HEAD OFFICE'

select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG
where DEPTNAME=@deptname")

Server: Msg 7399, Level 16, State 1, Line 4
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703
]

View 1 Replies View Related

Using Variables In An Update Statement

Nov 19, 1999

Declare @vname varchar(4)

select @vname = 'MT1'

--This works fine
UPDATE tblTmpLA
SET MT1 = 2222 / 1000
FROM tblTmpLA
WHERE ID = 1


This is the way I want to do it but

UPDATE tblTmpLA
SET @vname = 2222 / 1000
FROM tblTmpLA
WHERE ID = 1


I get the error that
Disallowed implicit conversion from datatype 'int' to datatype 'varchar'
I guess that the variable does not compile as the text that it holds or so it seems.

I want to put this code inside a WHILE loop where the @vname will change on each iteration.

Any ideas?

View 2 Replies View Related

Update Statement With Variables

Jun 3, 2004

I'm trying to execute an update statement in a stored procedure that includes a variable for a column and a variable for a conditional constraint. For example I want to execute the following UPDATE, but using variables:

Update table set flagcode = 'A' where Field1 < 100


the field flagcode is a varchar and the field Field1 is an int
This basically how I have my code set up:

declare @flag as varchar(20)
declare @lowrange as varchar(20)

set @flag = 'A'
set @thefield = 'Field1'

exec('update table set Flagcode = ' + @flag + ' where '+@thefield+' < 100')

I get the error:
"Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'A'."


Please help T-SQL Guru's. You're my only hope.

View 2 Replies View Related

Cursor And USE Statement With Variables

Sep 18, 2013

I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).

Here is my code:

Declare @databaseName varchar(50)
Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases
OPEN c_getDatabaseName
FETCH NEXT from c_getDatabaseName into @databaseName
While @@FETCH_STATUS = 0

[Code] ....

How to get USE to take the variable value ?

View 3 Replies View Related

How Can I Use Variables In This TSQL Statement

Mar 4, 2008

Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg

View 2 Replies View Related

Excel: Client Safety Options Do Not Allow Pass Through Statement To Be Issued...

Jun 1, 2005

Hi.. My Finance user create a microsoft Excel OLAP file that connect to our SQL server. And I have created the same ODBC setting for every PCs.

But for a few PCs, I encountered the following error "Client Safety Options do not allow pass through statement to be issued to the data source" When trying to connect the OLAP file to SQL server after entering the same SQL account.

Any idea what is the isssue ??

View 3 Replies View Related

Unable To Pass Queue Name In A Parameter For The FROM Clause Of RECEIVE Statement

Mar 19, 2008

I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:

CREATE PROCEDURE fnd_Send (
@Message VARCHAR(5000),
@Contract SYSNAME,
@MessageType SYSNAME,
@FromService SYSNAME,
@ToService VARCHAR(100),
@Encrypted Bit
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE @FromService
TO SERVICE @ToService
ON CONTRACT @Contract
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle
MESSAGE TYPE @MessageType(@Message);

Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.

The following will not compile:

CREATE PROCEDURE fnd_Receive (
@Queue SYSNAME,
@Message VARCHAR(5000) out,
@MessageType SYSNAME out
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM @Queue;
END
GO

It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:

Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15
Incorrect syntax near '@Queue'.

It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.


Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.

Thank you in advance -

Jeff Odell
Catapult Systems

View 3 Replies View Related

Using Declared Variables In SQL INSERT Statement.

Feb 3, 2007

 
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express.  I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL.  If I hard code the values in the SQL statement it works fine.  Can someone explain what I'm doing wrong accomplish this?  My code is below.  Thanks in advance. 
file.aspx
<asp:SqlDataSource ID="SqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
SelectCommand="SELECT * FROM [Table]"
InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" >
</asp:SqlDataSource>
file.aspx.vb
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click
Dim variable1 As String = FileUpload.FileName
Dim variable2 As String = Date.Now
Dim path As String = Server.MapPath("~/directory/)
If FileUpload.HasFile = True Then
Try
SqlDataSource.Insert()
FileUpload.PostedFile.SaveAs(path & _
FileUpload.FileName)
End Try
 
End If
 
End Sub

View 8 Replies View Related







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