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
ADVERTISEMENT
Jul 23, 2005
Anyone,Is this possible?I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuerystatement. I need to pass a list of ever-changing deal numbers Mylist of numbers are stored as a table on MS SQL.So what I want is thisSelect * from OpenQuery(TeraSrvr, "Select Col1, Col2, Col3[color=blue]>From Teradata_Table_1[/color]Where Deal_no in (Select Deal_no from SQLTable)")Now I know that wont work, but How can I pass 184 Deal Numbers from mySQL server to this query before it is sent to the Teradata server to bedone? Do I have to keep re-doing an in statement each month?Anyone can help?Doug
View 3 Replies
View Related
May 23, 2007
I have an openquery statement with a parameter embeded as a variable:
declare @product varchar(3)
set @product= 'ABC'
select * from openquery(SomeServer,'
SELECT Description, Size
FROM Products
WHERE
Group = ''XY'' AND
Code = ''' + @product + '''')
When I run it I get the following message:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.
When I hard code the "Code" value, like so:
Code = ''ABC''')
...it works fine.
I am at a loss and would appreciate any help on this.
Thanks in advance
SQL Servant
View 7 Replies
View Related
Nov 7, 2006
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
The error that I get is the LA1 column cant be found? any help thanks
View 4 Replies
View Related
Aug 29, 2013
Import Hadoop Data into Analysis Services Tabular | Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.
I was wondering how can i also perform an insert statement with openquery?
I understand that for example to insert into Hive is to use a Load command, like:
Code:
load data inpath '/tmp/data.csv' overwrite into table tableA;
How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors
Code:
insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')
View 1 Replies
View Related
Nov 7, 2006
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
View 6 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
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
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
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
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 14, 2004
Hi All,
Does anyone know the syntax for an insert statement using Openquery in a stored procedure? All the examples I've seen are Select statements, but I want to send data to a linked server.
Would I be better off using DTS??
Thanks,
Greg
View 1 Replies
View Related
Dec 13, 2007
How to create linked server to Dbf,
How to openquery util step by step
View 1 Replies
View Related
Sep 19, 2007
HI,
Can we use OPENQUERY with a parameter? Something like this:
SELECT * FROM OPENQUERY(@SOURCE_SERVER_NAME, 'Select * from dbo.FEED')
Please let me know at the earliest. Thanks a lot,.
Mannu.
View 3 Replies
View Related
Apr 18, 2008
Hi everybody,
I am having a problem using a servername with '' in the openquery statement. I'd really appriciate if someone could suggest how I should be using it. Here is the query:
select * from openquery(sqldev est,'SELECT COUNT(*) FROM t_login WHERE username=''Tom''') into count
thanks in advance
devmetz
View 2 Replies
View Related
May 20, 2008
Hi All,I want to use the following code to use 'NOT LIKE' clause for my File system search here is the code:SELECT Docs.FileNameFROM OPENQUERY(OPINIONSERVER, 'SELECT Filename FROM SCOPE() WHERE FREETEXT(''Any text not to search'')') AS Docs I want to use the above code for my html file system search similar to:ColumnName NOT LIKE N'%1971%'The confusing part for me is that in normal queries we use the column name to search in, but while searching in the file system using the FREETEXT() function how we exclude the words user dont want to search.I am using Dotnetnuke.
View 2 Replies
View Related
Jul 20, 2001
I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :
select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')
The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.
Any suggestions??
View 1 Replies
View Related
Nov 20, 2002
Hello,
if I am running this query and getting the error the below error
SELECT * FROM OPENQUERY(AS400ODBC,'
SELECT CBH_CREDIT_MEMO_NMBR,CBH_CREDIT_MEMO_DATE,CBH_CUST _TAX_CODE,
CBH_TAXABLE_TOTAL,CBH_CUST_NMBR FROM CREDITBLDH CR1 WHERE CR1.
CBH_CREDIT_MEMO_DATE = (SELECT MAX(CR2.CBH_CREDIT_MEMO_DATE) FROM CREDITBLDH CR2 WHERE CR1.CBH_CUST_NMBR = CR2.CBH_CUST_NMBR)')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 7211 exceeds limit 600.]
What can I do for this?.
I can able to run fine without subquery
You suggestion appreciated.
T's,
Ravi
View 1 Replies
View Related
Jan 22, 2003
Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'
My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi
View 3 Replies
View Related
Feb 5, 2003
Hi,
I need your help to solve this error.
I am running the open query against Oracle server and this shows blow.
UPDATE Lp.dbo.CB
SET oldest_invoice_date = x.oldest_invoice_date
FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date,
I.ACCOUNT_NUM
FROM CDWD..CDW.INVOICE I
WHERE I.INVOICE_NUM >= 0
AND INVOICE_DATE IS NOT NULL
GROUP BY (I.ACCOUNT_NUM)
)x
WHERE account_num = x.ACCOUNT_NUM
I am getting the below error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oldest_invoice_date'.
Anybody give solution what I am doing wrong?.
Thanks,
Ravi
View 3 Replies
View Related
Aug 25, 2004
Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )
View 4 Replies
View Related
