OpenQuery With Local Table Data
May 13, 2008
Hi,
I have two queries as under:
QUERY 1:
SELECT * FROM
OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS Docs
QUERY 2:
SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, Attachment
From Dept_LegalLaw
INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID
INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID
WHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1
both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).
Try many thing but no success yet.
Need urgent help.
Thanks
View 1 Replies
ADVERTISEMENT
May 12, 2008
Hi,
I have two queries as under:
QUERY 1:
SELECT * FROM
OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS Docs
QUERY 2:
SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, Attachment
From Dept_LegalLaw
INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID
INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID
WHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1
both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).
Try many thing but no success yet.
Need urgent help.
Thanks
View 1 Replies
View Related
Jul 20, 2005
I created a stored procedure like this:CREATE PROCEDURE SPASBEGINCREATE TABLE #T( C INT )INSERT INTO #T(C) VALUES (1)SELECT * FROM #TENDWhen I call it this way: EXEC SP, it works ok.But when I do it like this:SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')I receive an error: Invalid object name '#T'Why?...*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
May 12, 2008
Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks
View 2 Replies
View Related
Jul 23, 2005
I have an online SQL Server database provided by an ISP. I do not havepermission to create a backup device and I understand this is normalpractice.I am not using Enterprise Manager to administer the online database.I know I can back up the structure of the database using SQLscripts.My question is:How do I back up on my own machine the data contained in the onlinedatabase tables I have created? If I were using Enterprise Manager Icould do it by downloading tables using the DTS facility but how can Ido it without Enterprise Manager?Is there some work around which I have missed eg creating a csv fileof the data?Best wishes for 2005 to all those helpful people in this newsgroup!John Morgan
View 1 Replies
View Related
Mar 16, 2008
Hello All
Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great.
It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great.
My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml
Any help on this would be greatfully apreciated
Thanks In Advance
Neil
View 4 Replies
View Related
Aug 21, 2007
We have a MS SQL database with an Oracle linked server 'ALTTEST'
We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
View 5 Replies
View Related
Apr 16, 2008
Hello,
I have an application that uses a MS SQL 2005 database. When data is changed in certain tables, that data needs to be pushed to a MySQL box. I've added the MySQL server as a linked server in SQL 2k5 and I can delete and insert data with no problem, however when I try to update I get the following error (query included):
Code Snippet
with RemoteTable(r_AccountID, r_Name)
as (select AccountID, Name from openquery(RACS_TEST, 'select AccountID, Name from accounts'))
update RemoteTable
set r_Name = ex_Name
from Export_RACS_Accounts join remotetable
on r_AccountID = ex_AccountID
OLE DB provider "MSDASQL" for linked server "RACS_TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 33
The OLE DB provider "MSDASQL" for linked server "RACS_TEST" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
What am I doing wrong ?
View 2 Replies
View Related
Oct 5, 2007
Hi,
I have an application that uses a Cache data base. The data is exposed to sql server via a linked server.
In the Cache database they have data columns that can be up to 32395 characters long. When I do a simple select query like this:
select Intervention_text from cwsavpmtest_live..system.Intervention_data
… I get the error message:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[Intervention_data].intervention_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
I figured out that I could get around the problem with an OPENQUERY like this:
SELECT *
FROM OPENQUERY(cwsavpmtest_live, 'select left(Intervention_text,32395) from Intervention_data’)
The error goes away, but I only get the first 255 characters of the data. I don’t really need all 32395 chars (I figure 4000 chars would be acceptable).
This is not a display problem in Query Analyzer. I set the maximum characters per column to 8000 on the Results tab of the Option dialog. Also I have a stored procedure that is called by Reporting Services and it is only sending 255 characters to the report.
The stored procedure has code that looks like this:
CREATE TABLE #Intervention_Data
(ID varchar(254),
FACILITY Int,
INTID Int,
Intervention_Text varchar(4000),
Intervention_Name varchar(50))
SELECT @QueryString =
'INSERT INTO #Intervention_Data SELECT * FROM OPENQUERY(' + @LinkedServer +
',''select ID, FACILITY, INTID, left(intervention_text ,4000), ' +
'Wiley_Intervention_ID from Intervention_Data where facility = ' +
cast(@FACILITY as varchar(10)) + ''')'
EXEC(@QueryString)
I have tried making the Intervention_Text column in the temp table both "text" and "ntext" data type with the same result.
Does anybody have any clues about how I can make this work?
Thanks,
Laurie
Note: When I link to the Cache database using Access, Access interprets the long fields as being of type "Memo" and I can see all of the data.
edited because tap and tab are not the same word.
View 10 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3
From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 11 Replies
View Related
Feb 26, 2008
I'm running a query on Oracle using OPENQUERY.
I need to pass a parameter.
Which means I'm having to use dynamic sql.
In turn this requires a ##table due to the connection issue of using EXEC.
There is surely a better way of doing this, but what?
View 4 Replies
View Related
Jul 9, 2015
Something Like:
DECLARE
@TSQL VARCHAR(8000)
DECLARE
@VAR TABLE (VAR1
VARCHAR (2))
INSERT
INTO @VAR
values ('CA'),('OR')
[Code] ....
View 4 Replies
View Related
Mar 31, 2006
Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.
I think you got what the situation is!! Woh!
Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!
------------------------------------- The Code ----------------------------
create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx
create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')
-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine
Thanks in advance...
View 5 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 1 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
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
Apr 23, 2008
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
View 6 Replies
View Related
Jan 13, 2004
Hi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
View 1 Replies
View Related
Jan 23, 2008
Hi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
View 5 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Jul 23, 2005
Greetings -I'm using an Access front end to a SQL Server (2000) databas*e. Viaseveral steps, I create a temp table and manipulate the data* in it.Iwant to update the backend with this new data but my UPDATE *queryfailsas my temp table is local and the SQL database doesn't know *about it.There are no linked tables in the FE database.I have the following (DAO):Set Db = CurrentDbSet Qdf = Db.CreateQueryDef(TMP_QUERY_NAME)Qdf.connect = ConnectString()sqlString = "UPDATE tblRemote " & _"SET " & _"tblRemote.Some_Foo = tblLocal.Foo, " & _"FROM tblRemote INNER JOIN tblLocal " & _"ON tblRemote.Some_ID = tblLocal.Some_ID;"Qdf.sql = sqlStringQdf.ReturnsRecords = FalseQdf.Execute dbFailOnErrorIs there any way of doing this without adding a linked table*?Thanks, chris
View 2 Replies
View Related
Jul 23, 2005
I am implementing an MSDE database and I would like to keep some data(like user preferences) in the Access project so that I can filter datafor each user based on their selected preferences. In Access I justkept user preferences in the application database on each workstationand used queries to get only the data I wanted from the linked tables.How can I do this in MSDE/Access projects?thanks!Jerry
View 1 Replies
View Related
Jul 15, 2014
I wanna to import excel from local to DB table.
Excel will be in one server and DB will in another server.this import should be scheduled.
How to achieve this.My friend suggest to use SSIS.how to use SSIS for this.
View 1 Replies
View Related
Jul 23, 2005
I am new at sql so would appreciate some helpI have the name of a table in alocal variable is it possible to select thistableDECLARE @name sysnameSET @name = 'tblSniffedItems'PRINT @nameSELECT * FROM @nameI expected this wo work but I got the following error.@name is declare as far as I knowServer messageMust declare the variable '@name'.Thanks in advanceAndre
View 4 Replies
View Related
May 7, 2008
I have a function that returns a table of matching names. For the most part it returns under 5,000 results which doesn't cause too much lag joining to the search report data (which is indexed). But sometimes there might be 10,000 + search name results. I find this and higher can cause excessive lag joining. Is it possible to index this result table from the function? Or any other suggestions?
THANKS
View 8 Replies
View Related
Oct 2, 2007
We have a 64-bit VM server running SQL Server 2005. The SQL Server on this particular VM server has 6 local instances installed. On the Management Studio logon screen I can type the full name of the local instance and connect to it, however if I press the drop down in the Server name field, choose Browse and select the Local Servers tab there is nothing listed under Database Engines.
Any idea why the 6 local instances don't show up under Database Engines? This is preventing me from installing a vendor application because their installer looks for local SQL Server instances on this server, but if SQL Server won't even show the local instances then the installer doesn't see them either.
Any help is greatly appreciated.
Thanks,
Craig
View 3 Replies
View Related
Feb 17, 2004
Hi,
I'm trying to copy all the data from a single table across to a remote linked server and I'm having all sorts of problems.
The SQL query I'm attampting is this:-
Code:
EXEC sp_addlinkedserver 'GODZILLA', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'GODZILLA', 'False', 'rmtUser', 'lclUser', 'password'
Go
--DELETE TABLE
DROP TABLE GODZILLA.Racing.dbo.horse
--SELECT INTO
SELECT horse_id, horse_filename, horse_time, horse_new, horse_driveLetter
INTO GODZILLA.Racing.dbo.horse
FROM Racing.dbo.horse
EXEC sp_droplinkedsrvlogin 'GODZILLA', 'lclUser'
EXEC sp_dropserver 'GODZILLA'
Basically - this doesn't work as an error is returned:-
Code:
The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???
Please help!
View 3 Replies
View Related
Apr 30, 2008
Hi,
Can I take the [index] result from a table and store it in a local var?
For example a table
declare @keyWords(
word varchar(40),
wordLen smallint
)
word len
---- ---
test 4
t 1
e 1
declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords
This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)
View 2 Replies
View Related
Mar 10, 2008
It is possible for global temporary tables, but for local, it is said that the names(in the tempdb) change each time the table is created, so i am not sure if there is a way to check it.
Thanks
View 7 Replies
View Related
Jun 25, 2006
Hi All,
I am completely new to VS2005 and .net2.0 but I am learning fast! But one thing that I am struggling with is the deployment of my website, or to be more precise the deployment of the data that was created during development on my local machine.
I have used VS2005 to create a simple personal site. I have created a single user (myself) in an admin and friends role. So far so good. The site works great on my local machine, all the default photos provided with the site display correctly etc and I can log in OK.
My remote server (ie my web hosts) provide me with a separate SQL server on which to install my databases. I have created a database on this server and configured it so that it contains all the correct tables and schemas etc. I have modified my web config file to connect to this database and when I browse to my default.aspx page it displays in my browser fine...EXCEPT that none of the data ( ie the photos and user data) is in the database so therefore no pictures display and I can't log in!!
What I can't figure out is how do I get all the data from my local app_data folder into my new remote database!!?? I can't find anything that explains how to do this. Surely this is one of the most important operations of all since data is what .net2.0 is all about!
Thanks in anticipation
View 2 Replies
View Related
May 12, 2008
I am currently deploying a desktop application that uses SQL Express as a local data source. I load data from other sources into the local database and then use that data for my application. Currently I'm deploying the MDF file into a Data folder within my application folder. I'm finding that most of the users that will be using this application has only power user rights, not admin rights.
My question is this. Is using a data folder within my application folder the ideal place to store this data or is there other more benefitial places within the users computer that would help reduce rights and permissions issues? When users upgrade to Vista will this cause any problems that another location might help me avoid?
Appreaciate any opinions.
View 6 Replies
View Related
May 3, 2007
Hi
I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible?
If i just get the connection part explained, i can figure out the rest by myself.
Thanks
/Jonas
View 3 Replies
View Related
May 23, 2001
In a stored proc, can you declare a local variable that is an existing column in a table & then based on other criteria, do an order by using the local variable?
View 1 Replies
View Related
