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_today
I would get a result, but I get an error. What is the right way to do this?
Many thanks
Steve
View 7 Replies
ADVERTISEMENT
Feb 19, 2007
Hi
when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'
select <some fields> from star
where nr_jahr_star = :Year
my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server
I've tried various combinations with the parameter settings also with no luck.
Thanks
Steven
View 1 Replies
View Related
Apr 29, 2008
I am using a Execute SQL Task which takes package variables and passes them to an Oracle SP as parameters:
{call oraclespname(?,?,?)}
where of course the parameters are set up as input parameters on the correct tab in the SQL Task.
Well the data that is being put into the table in Oracle has a CHR(0) or a null appended to the end of it. Of course this makes no sense. Does anyone have an idea of why this is happening or a way to fix it. The data becomes fairly useless as is without doing a RTRIM(data,CHR(0)) on it first which is a work around but kind of a hack.
View 2 Replies
View Related
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
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
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
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
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
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
View Related
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
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
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
Oct 22, 2001
I am trying to do a proof of concept on a simple sql statement, but it doesn't work. Am I doing it wrong, or is this not possible....
Table: tblItems
field: account varchar(20)
In Query Analyzer:
DECLARE @acct varchar(20)
SET @acct = '457760,123456'
SELECT account from tblaccount where account in (@acct)
Result:
0 Rows
Any thoughts?
Thanks,
Brian
Expected
457760
View 2 Replies
View Related
Sep 29, 2005
Hi!
I need a stored procedure with this basic setup:
CREATE PROCEDURE test
@Type int
AS
SELECT *
FROM
Cards
WHERE
CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
How can this be done?
Thanks!
/Rickard
View 2 Replies
View Related
Oct 31, 2007
is it possible to assign the results of the qry to the 2 variables?
Declare @a Integer
Declare @b Integer
SELECT A, B from myTable
View 1 Replies
View Related
Jul 20, 2005
Hi gurus,I just started to look at a very slow-running SQL statementgenerated by an application (Siebel). I spooled the SQL from theapplication, replaced the bind variables by their values, and tunedfrom the Query Analyser. But after awhile, I realized that thestatement using bind variables and the same statement using the valuesinstead of the bind variables often have completely differentexecution plans! Is that normal? Can someone tell me how the SQLServer treats bind variables. Don't worry about being too technical,I'm an Oracle DBA/developer.ThanxDaniel
View 1 Replies
View Related
Jan 5, 2007
I'm getting the error "No recepient is specified".
I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.
I couldn't figure out how to included screen shots.
Any ideas?
View 4 Replies
View Related
Jan 31, 2003
Hi,
Anybody give example in ORACLE for below sql server statement.
Thanks,
Ravi
select a, b into temp_table
from old_table
View 1 Replies
View Related
Oct 9, 2004
Hi,
Below is an Oracle query used for cost optimization purpose :
analyze table test estimate statistics sample 2500 Rows;
Is there any equivalent for the above in SQL Server ?
Please advice,
Thanks,
Sam
View 5 Replies
View Related
Aug 31, 2005
Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)
View 1 Replies
View Related
Nov 15, 2012
I would like to know what can be the equivalent update command in SQL server?
Code:
update Table1 set EndFlag = 1
where (c1,c2) in (select c1, MAX(c2) from Table1 group by c1)
View 2 Replies
View Related
Aug 10, 2015
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 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
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
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Apr 2, 2007
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
View 10 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
May 8, 2015
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
View 2 Replies
View Related
Feb 22, 2006
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
View 17 Replies
View Related
Jan 12, 2006
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
View 2 Replies
View Related
May 11, 2007
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
View 1 Replies
View Related
Aug 23, 2007
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
View 3 Replies
View Related
Jan 11, 2007
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
View 3 Replies
View Related
