Updating Remote Table With OPENDATASOURCE
May 25, 2008
Greetings all
Have another issue with OPENDATASOURCE
The following SQL appears to run successfully:
Update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=******').TRANSFERSTN.TSADMIN.SALESDAY
set STARTDTTM = L.STARTDTTM,ENDDTTM = L.ENDDTTM,CLOSED = L.CLOSED,
CASHEDUP = L.CASHEDUP,CASHINTILL = L.CASHINTILL,CHEQUESINTILL = L.CHEQUESINTILL,
EPSALES = L.EPSALES,EPCUTORETURN = L.EPCUTORETURN,REPFLAG = R.REPFLAG
from OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=******;Password=*****').TRANSFERSTN.TSADMIN.SALESDAY AS R inner join
TSADMIN.SALESDAY as L on R.SALESDAYID = L.SALESDAYID where L.REPFLAG = 1
However, all records in the remote table get updated if there are any records satisfying the REPFLAG clause, if more than 1 then I guess the last record in the source set.
Running the same SQL against 2 tables in the local DB works perfectly.
These are both MSDE V8.00.2039 SP4 connecting via RAS dial up.
Any ideas where to focus my attention will be gratefully received
Thanks
<EDIT>
Haven't solved the issue, but staggered around it. I have simply reversed the tables in the SQL and executed at the remote server instead, so it is updating a local table while getting data from a remote table. This functions as it should. Any clues to the above would still be good tho' - cheers
<END EDIT>
View 2 Replies
ADVERTISEMENT
May 21, 2008
Hi everyone
I have a Stored Procedure mySP that updates a table in a linked server.
The same table is also being updated by other processes in the remote server
Usually mySP updates 10-20 records at a time, and that works fine.
BEGIN TRANSACTION
......
......
UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2
WHERE ID IN (SELECT ID FROM LOCAL_TABLE)
COMMIT
The problem begins when mySP tries to update 100+ records, it gets a time out probably due to locking issues
(indexes are properly set in both remote and local tables )
I read some articles where they suggest to break the transaction in several batches of a smaller size to avoid locking issues.
Something like:
BEGIN TRANSACTION
.....
.....
DECLARE @Rows int
DECLARE @Batchsize int
SET @Batchsize = 10
SET @Rows = 1
WHILE (@Rows > 0 )
BEGIN
UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2
WHERE ID IN (SELECT TOP (@Batchsize) ID FROM LOCAL_TABLE)
DELETE TOP (@Batchsize) FROM LOCAL_TABLE
SET @Rows = @@ROWCOUNT
END
COMMIT
But it seems to make no difference.
Any ideas/suggestions?
Thank you
View 2 Replies
View Related
Apr 25, 2008
Hello all, quick question and then a little background.
Could someone point me in the right direction when it comes to updating remotely deployed instances of a SQL CE database? I don't expect someone to code my project for me, but I'm getting lost in the sea of information and haven't found anything that looks like what we need yet.
What's going on is that I was given the wonderful opportunity to learn SQL.NET programming from the other side of application development. I'm not a beginner programmer by any means, but kind of a newbie when it this level of SQL deployment. What we are trying to do is give our users applications that use a local instance of the larger SQL database we have on the server. We were thinking that deploying SQL CE databases created from the master DB would be a good solution since it is supposed to have updating capabilities, which would be wonderful since then the users wouldn't have to be burdened by having to manually download and update a huge database install file every time we updated the information in the master.
What I'm finding though (after going throught the tutorials), is that the RDA feature (this would only need a pull function) that we were hoping to use requires that you delete the entire table, and then replace. If the master DB has only a few record changes in several scattered tables, it seem that this operation would be similar in size and resource demands as just re-downloading the entire database again (~2.7 gigabytes and growing).
Is there a function that can use a feature on the field properties that can download and replace only those fields/records that have changed intead of basically replacing the entire database?
Sorry if this seems like a painfully obvious question, but I have been looking for a while and haven't been able to find it.
thanks a ton in advance!
Brian
View 4 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Jun 10, 2004
Hello everyone.
I'm currently trying to figure out a good way to keep my Local and Remote Databases in sync. Whenever I make changes to one or the other I spend a lot of time manually adjusting the other to match. I want to be able to set up an update page that accesses my Remote database and Local database and Updates the two accordingly.
I really don't know where to start with this one. I'm not sure at all as to how to simultaneously connect to two different databases, on two different servers, and Udate from one another. Does anyone know any good articles to get me started?
Thanks ahead for your replies.
-Alec
View 1 Replies
View Related
Apr 12, 2008
I have a SqlExpress DB on my server. I have a remote copy for security/backup reasons. Because the DB file(and .bak file) is about 2 Gb, it take a while to copy the whole thing to the remote machine. I believe there is a way to use the log file to simply update my remote copy ratherr than copying whole files, but I don't know what this process is called to research it. I have Brust's book on Sql Programming, but I have not found what I am looking for in there. Can anyone tell me where I should be looking and what this process is called?
thanks,
Steve
View 8 Replies
View Related
Apr 20, 2005
I have a table that has triggers on insert update and delete. The triggers perform modifications on another table on a remote databased(linked server).
All triggers make a join between the tables inserted, deleted and the remote table.
When running a profile on the remote database it seems that the following query is done on the remote table by the triggers.
"Select * from Database.dbo.Table"
The triggers are the only entities accessing the remote table from the original server so the query must come from them. My only conclusion is that MSSQL server is doing this query for some kind of "optimization".
Has anyone seen this before?
How can I work arond this? The remote table is BIG and this query happening every few seconds in a problem for me.
Thanks
Robert
View 4 Replies
View Related
Oct 24, 2005
What is the syntax for using parameters in an opendatasource query? I tried using the below:
SELECT top 10 *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.22.10.78;User ID='+@user+';Password='+@pword
).Northwind.dbo.Categories
View 4 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Aug 17, 2007
I am trying to open and query a Microsoft Access "like" database from SQL Server 2000 using OpenDataSource. This will work for a little while, but if I am to update my ASP.NET code base I have to completely reboot the machine for these queries to work again. I also have two reboot the machine periodically because this functionality decides to crap out. I am doing this from a stored procedure. My code looks like this:DECLARE @SelectCode varchar(5000)SET @SelectCode ='SELECT CONVERT(varchar, Value) AS UnitName FROMOPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''Data Source = "' + @Path + @DataFile + '.fcd";User ID=Admin;Password=;'')...Properties WHERE Property LIKE ''UnitName'''EXEC(@SelectCode)Now, when I am developing on my local machine that uses Windows Authentication, I have absolutely no problems, unless the source file has a schema error.Any ideas?
View 1 Replies
View Related
Jan 13, 2008
Hi all, In a select sql statement, I have to query 2 databases from 2 different servers. The only way I found to do this, is to call OPENDATASOURCE like this :<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:bdConnectionString1 %>" SelectCommand="SELECT * FROM [Table1] t1 left join OPENDATASOURCE('SQLOLEDB','Data Source=servername;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx').dbname.dbo.table2 t2 on t1.id = t2.id"> </asp:SqlDataSource>My question is the following :Is there a way to connect to the second server without using the full server declaration into the sql statement ? For information, the server connection is already declare into my web.config like that :<add name="bdConnectionString2" connectionString="Data Source=servername;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" />so is there a way to use 'bdConnectionString2' directly ?If not, is there a way to connect to the second server without entering informations like User ID and Password ? My problem being that I'm not sure it's safe to send this informations directly from the asp.net page. Thank you
View 4 Replies
View Related
Jan 12, 2005
hi to every one....
To delete records in tableA which is not found in tableB i am using the following :
DELETE tableA from tableA aa left join tableB bb on aa.fld = bb.fld where bb.fld is null
** This will work fine **
but if i Use similer but using OPENDATASOURCE , like this:
delete OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="c: empdata2004.mdb";
User ID="Admin";Password="";Jet OLEDB:Database Password="lea"')...tableA
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="c: empdata2004.mdb";
User ID="Admin";Password="";Jet OLEDB:Database Password="lea"')...tableA aa left join tableB bb on aa.lno=ba.lno where b.lno is null
the result is deleteing whole of TableA.....
any help is appriciated
Thanks.
View 2 Replies
View Related
Jan 23, 2004
begin
select @datasource = 'Data Source="c:/'
+@FOLDERNAME
+'/'
+@FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'
select
*
into
#excel_table
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @DATASOURCE)...sheet1$
end
need to do this ... but using variables in opendatasource not allowed.
so how do we go about it
What i need to do is allow upload of any excel file ... may contain errors and then perform some checks on the data before putting it into another table with a fixed structure.
View 2 Replies
View Related
Jun 13, 2008
Is it possible to query another server using a sql statement and connecting though windows authentication?
Thanks
View 15 Replies
View Related
Mar 12, 2008
Hello:
Is is possible to query Active Directory using OPENDATASOURCE? If so, what is the syntax? If not, what other alternatives are there?
Thanks in advance!
Tim
View 5 Replies
View Related
Feb 8, 2008
I need to read a tables in an acess database. The access database has no user/password. All the examples of openrowset show a userid and password. How do I read access table without them?
Should I use opendatasource?
thanks
View 1 Replies
View Related
Dec 2, 2005
Hi,
I have written a stored procedure which extracts job history from all our servers using the OPENDATASOURCE function using NT authentication.
It's works fine when the stored procedure is called from Query Analyser, however I want it to be called hourly so are calling it from a SQL agent job.
I have set the job to run as myself, and the owner is myself, but the procedure fails when trying to connect to the first server, giving the error :-
"Executed as user: NT AUTHORITYSYSTEM. about to query : aadmin01 [SQLSTATE 01000] (Message 0) Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452). The step failed."
It doesn't seem to be able to make the NT authenticated connection when the OPENDATASOURCE function is called from within the context of a SQL agent job.
I know a solution may be to use a regular SQL login for the OPENDATASOURCE calls, but this means hard-coding passwords into the stored procedure - I'd read this is bad practice.
Any thoughts appreciated.
Thanks
View 2 Replies
View Related
May 10, 2002
Does anyone know if OPENDATASOURCE or OPENROWSET can be called via NT authentication, rather than with a SQL Login? If so, what would be the correct syntax to use?
View 4 Replies
View Related
Jan 27, 2004
Ok .. Gurus ... here's my problem
I am able to successfully run a opendatasource query against a flat file from SQL Server. However the problem I am facing is that the resultset that is returned has a line as one column and one row ... is there any way i can get the opendatasource query to recognize tabs as column seperators ????
View 7 Replies
View Related
Jun 17, 2006
Hi folks.I'm trying to connect to a Medisoft Advantage SQL db through SQL Serverusing OpenDataSource or OpenRowSet. I have general connections to the dbworking fine, but not with OpenDataSource or OpenRowSet.I've tried variations on:select * fromOpenDataSource('Advantage OLE DB Provider','Data Source=C:MediDataTutormwddf.add;User ID=user;Password=password;Advantage Server Type=ADS_LOCAL_SERVER;Initial Catalog=mwddf.add;')...MWPATWhich gives:OLE DB error trace [OLE/DB Provider 'Advantage OLE DB Provider'IColumnsInfo::MapColumnIDs returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOGORDINAL=-1], [COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAMEORDINAL=-1], [COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUIDORDINAL=-1]].I've also tried:select * fromOpenrowset('Advantage OLE DB Provider','Data Source=C:MediDataTutormwddf.add;UserID=user;Password=password;Initial Catalog=mwddf.add;Advantage ServerType=ADS_REMOTE_SERVER',MWPAT)and 'Select * from MWPAT'.These last yield:[OLE/DB provider returned message: No Data Source specified]which seems closer.Can anyone help? I think this is all Advantage-specific. I've posted ontheir boards, but their not very active...Thanks.David
View 1 Replies
View Related
May 4, 2006
I would like to create a stored procedure that imports an Excel file toa sql server table. I need to be able to pass the path to thespreadsheet to the stored procedure like so:CREATE PROCEDURE dbo.ImportSpreadsheet(@Path nvarchar(120))ASSELECT * INTO AuditFROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source = ' + @Path + ';Extended Properties=Excel 8.0')...Audit$I will call the stored procedure from within my .NET application andpass it the path as @Path. The path will be chosen by the user at runtime and will be something similar toC:SpreadsheetsAudits.xls.I can make this run in the Query Analyzer:SELECT * INTO AuditFROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source = ''C:SpreadsheetsAudits.xlsAudit_TC.xls'';Extend edProperties=Excel 8.0')...Audit$I could run the SQL directly from my app, but I like to use storedprocs whenever I can for the speed and flexibility. I can change thestored proc and not have to recompile/reinstall the app.Any help would be greatly appreciated.
View 1 Replies
View Related
May 21, 2008
Hi All
I'm trying to update feilds in a local table from a remote table using the following:
update schema.table1 set acc = r.acc, add = r.add
from OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=user;Password=mypw').db.schema.table1 as R, schema.table1 as L where R.acc = L.acc
When run from Query Analyser, it fails with:
Could not find server '"db"."schema"."table1" in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
On both servers, the database, schema and table names are the same.
Running SQL2K SP4 on both servers. Don't want to set up linked servers, as they will only connect on a scheduled basis to perform updates.
Other queries such as insert work, as well as update OPENDATASOURCE( . . .) set x=L.x from schema.table where . . . so it's a prob with the From clause of the update.
Am I missing something, or is what I'm trying to do just not possible?
TIA
View 4 Replies
View Related
Jun 4, 2008
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
View 2 Replies
View Related
Jul 30, 2015
If the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 1 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 3 Replies
View Related
Dec 21, 2004
I have the following T-SQL commands which run without errors:
select Description_Text
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:Import;','select * from company_finance.csv')
select Description_Text
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:Import;Extended Properties="Text;HDR=Yes;FMT=Delimited"')...company_finance#csv
When I run either command on our TEST server, all the text in the fields retrieved is returned, but when I run on our PRODUCTION server, all fields are truncated to 255 characters. The source file company_finance.csv is identical on both servers. Both servers have the same O/S (Win 2K Server), same version of SQL Server 2000 and MDAC (2.71). I cannot figure out if the problem is on the SQL Server side, or has something to do with some registry settings or something else, but both the Text Driver and the Jet drivers produce the similar results on each servers. Are there some config settings in SQL Server or in the registry I can look at to determine why the truncation is occurring? Help!
Thanks for your time.
View 2 Replies
View Related
Jan 26, 2007
I want to use an input parameter as my filename, but I get a synataxerror message. Howerve, when I hard code the filename the proc compilessuccessfully.Thanks for any help. I'm using SQL Server 2005LTR_90,LTI_ELIG_pct,LTI_REC_pct,LOW_SALARY,HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0","Data Source=C:inetpubwwwrootORC_Beta_Companies"' + @infilename +'"Extended Properties=Excel 8.0")...[summary_data$]Syntax message:Msg 102, Level 15, State 1, ProcedureimportExcelSpreadSheetIntoeNavigator_DataORC, Line 244Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.
View 2 Replies
View Related
Jul 20, 2005
Version 2000.How do I do something like the exampleSELECT *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...xactionsbut use a .txt-file instead ?I tried building it using Access (that usually works :-) ) and that gives aconnectionstring of:Text;DSN=LinkSammenkædningsspecifikation;FMT=Delimited;HDR=NO;I MEX=2;CharacterSet=850;DATABASE=c: empSourcetablename=link.txtbut I can't seem to "massage" it into working on the sql-server.If I quick and dirty swap 'Microsoft.Jet.OLEDB.4.0' with 'Text' it giveserror:Could not locate registry entry for OLE DB provider 'Text'.tia/jim
View 4 Replies
View Related
Jan 8, 2008
I'm trying to do an Ad Hoc connection to a Progress database from SQL query Analyzer. I have successfully created a DataDirect ODBC connection and I can access the progress database tables through Excel. However, I can't get it working in SQL.
My stetament is the following:
SELECT *
FROM OPENDATASOURCE(
'DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E',
'DSN=datasourcename;host=servername;port=2501;db=rdatabasename;uid=SYSPROGRESS'
)..schema.tablename
Thanks, Susan
View 6 Replies
View Related
Feb 9, 2008
Hi
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone =
from import
inner join stat
ON stat.id = import.id
CASE
WHEN stat.A = import.field2 THEN import.gone = sec.A
WHEN stat.B = import.field2 THEN import.gone = sec.B
WHEN stat.C = import.field2 THEN import.gone = sec.C
WHEN stat.D = import.field2 THEN import.gone = sec.D
WHEN stat.E = import.field2 THEN import.gone = sec.E
WHEN stat.F = import.field2 THEN import.gone = sec.F
ELSE import.gone = null
END
Any help would be greatly appreciated
View 3 Replies
View Related
Oct 19, 2007
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master
UPDATE [dbname].dbo.table
SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1
WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2
AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3
AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
What is wrong?
View 1 Replies
View Related
Apr 7, 2005
:(
Hey guys. I got some problem here.
I have an Excel spreadsheet. the data is something like :
COL1 COL2 COL3
DATA1 1 DETAIL1
DATA2 2 DETAIL2
DATA3 3 DETAIL3
DATA4 4 DETAIL4
DATA5 5 DETAIL5
DATA6 6 DETAIL6
DATA7 7 DETAIL7
etc
there are 1877 * 3 cells in total in the spreadsheet.
but when I execute a query like:
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
It returns 2132 * 5 units of data!!! And the "F4" and "F5" column just have NULL. so do row 1878 to 2132. I'm confused with that.
I need your help~ 3X in advance. :)
View 4 Replies
View Related
May 14, 2015
While trying to insert data into existing XLS file, using below command, i am getting following error.
Insert into OPENDATASOURCE( 'Microsoft. ACE.OLEDB.12.0','Data Source=e:ediuploadhello1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Select top 50 product_no From product_mst
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". Unknown provider error.
View 2 Replies
View Related
