Queries Using OPENDATASOURCE
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
ADVERTISEMENT
Opendatasource
Oct 24, 2005What 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
OpenDataSource Quandary
Aug 17, 2007I 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 RelatedOPENDATASOURCE Connections
Jan 13, 2008Hi 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 RelatedOpendatasource Problem
Jan 12, 2005hi 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.
OpenDataSource Problem
Jan 23, 2004begin
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.
OPENDATASOURCE Questions
Jun 13, 2008Is it possible to query another server using a sql statement and connecting though windows authentication?
Thanks
Openrowset Opendatasource
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
OPENDATASOURCE Via NT Authentication Within SQL Agent Job
Dec 2, 2005Hi,
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
OPENDATASOURCE / OPENROWSET Via NT Authentication
May 10, 2002Does 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 RelatedOpendatasource For Text File
Jan 27, 2004Ok .. 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 ????
OpenDataSource Trouble With Advantage SQL
Jun 17, 2006Hi 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 RelatedUse Variable For Datasource In Opendatasource
May 4, 2006I 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 RelatedOPENDATASOURCE In Update Statement
May 21, 2008Hi 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
OPENROWSET And OPENDATASOURCE Truncation To 255 Characters
Dec 21, 2004I 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.
Input Paramter As Part Of OPENDATASOURCE
Jan 26, 2007I 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 RelatedOPENDATASOURCE: Text File As Input ?
Jul 20, 2005Version 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 RelatedOPENDATASOURCE Connection To A Progress Database
Jan 8, 2008I'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
Updating Remote Table With OPENDATASOURCE
May 25, 2008Greetings 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>
Strange Problem With Using OpenDataSource() To Get Data From Excel.
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. :)
Transact SQL :: Insert Data Into Excel Using OPENDATASOURCE
May 14, 2015While 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.
OpenRowSet And OpenDataSource Fail On Production To Open Excel Data
Jun 26, 2006The 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)".
Any help seriously appreciated,
Tronn
Parameterized Queries Running Slower Than Non-parameterized Queries
Jul 20, 2005HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedHow To Run Queries???
Aug 9, 2006Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
Is It Possible To Put Several Queries Into One Sp
Dec 7, 2007I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies View Related2 Queries Together
Dec 18, 2007 how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
Queries Or SP
Jun 18, 2004hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies View RelatedSql Queries
Oct 21, 2004Hi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
Please Help In Queries
Jan 23, 2006Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies View RelatedQueries
Jul 16, 2002Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies View RelatedQueries In SQL
Aug 3, 2000I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
Mdx Queries
Feb 7, 2003Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
SQL Queries...
May 1, 2001I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.