Hi folks.
I'm trying to connect to a Medisoft Advantage SQL db through SQL Server
using OpenDataSource or OpenRowSet. I have general connections to the db
working fine, but not with OpenDataSource or OpenRowSet.
I've tried variations on:
select * from
OpenDataSource(
'Advantage OLE DB Provider',
'Data Source=C:MediDataTutormwddf.add;
User ID=user;Password=password;
Advantage Server Type=ADS_LOCAL_SERVER;
Initial Catalog=mwddf.add;')...MWPAT
Which gives:
OLE DB error trace [OLE/DB Provider 'Advantage OLE DB Provider'
IColumnsInfo::MapColumnIDs returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOG
ORDINAL=-1], [COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAME
ORDINAL=-1], [COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID
ORDINAL=-1]].
I've also tried:
select * from
Openrowset(
'Advantage OLE DB Provider',
'Data Source=C:MediDataTutormwddf.add;User
ID=user;Password=password;Initial Catalog=mwddf.add;Advantage Server
Type=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 on
their boards, but their not very active...
When I import data I first import it from a text file into a table ofit's own, then using some logic insert some of the records into apermanent table.I am considering having the table that the data from the text file isplaced in being there all the time and just clearing it out after I dothe import, or creating it and after using it then drop it, or using atemporary table.What are the advantages of a temporary table as opposed to creating aregular table and dropping it after use?
Can anyone tell the advantage of using the new catalog views over the traditional system tables which were given as compatiblity views in sql server 2005
hi all, i need to create a report in reporting services, in reporting services there are two ways to develope a report by using "Report Server Project" and "Report Model Project". i need to know the purpose of each thing and which one will be advantage.
On 64 bit SSIS platforms (win2k3 and vista), where the amount of virtual memory is practically unlimited, is there any advantage to running child Integration Services Execute Package Tasks out of process? The question pertains to 64 bit processes on a 64 platform, not 32 on 32, or 32 bit emulation processes on 64.
Would the answer be different based on the amount of physical memory on the machine, supposing one had a "reasonable" amount of memory on the machine (e.g. 4Gb or more )
"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."
I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it€™s still pointing to DS1.
I guess this is because it€™s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?
suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required.
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?
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
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
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.
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?
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.
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?
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 ????
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.
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?
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!
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'.
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
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.
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>
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.
The following statement fails when using SQL Analyzer under sa but works on all of our development and staging server. All are SQL Server 2005 SP1. We upgraded production over the weekend from SQL Server 2000, creating a new instance machinenameSQL2005.
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:data est.xls',
'SELECT * FROM [Sheet1$]')
The error we are getting only in prodcution is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
