OPENROWSET Query Will Give Syntax Error - Please Help Me

Feb 28, 2008

I am not able to use WHERE Clause in my query. What am I doing wrong?


Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '

I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.


Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'

Using SQL Server 2000
DSN to a CACHE database on local network

Thanks in advance,

Sam

View 3 Replies


ADVERTISEMENT

Passing A Variable To A Linked Query (OPENROWSET For Excel Syntax)

May 11, 2007

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)



but I have not gotten all the ''''' + @variable syntax right.



Here is my raw openrowset with what I am aiming at.






Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @FIL VARCHAR(65)

SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT



Anyone game? Many thank-yous, in advance.



Kind Regards,

Claudia.

View 1 Replies View Related

Syntax Of OpenRowset With UDL?

Oct 25, 2007



Hi All,

Does anyone know or have a sample of a syntax of OpenRowset using a UDL file?

Regards,
Joseph

View 1 Replies View Related

Syntax OpenRowset With AS400?

Oct 25, 2007



Hi All,

Does anyone know the syntax of OpenRowSet when used to connect to AS400?

Regards,
Joseph

View 1 Replies View Related

Syntax Of OpenRowset With ODBC

Oct 25, 2007



Hi All,

Does anyone know the syntax of OpenRowset when using a pre-configured connection in ODBC?

Regards,
Joseph

View 4 Replies View Related

What Syntax Should I Use To Connect To Access DB (having System Database) Via OPENROWSET?

Aug 9, 2007

MS SQL Server 2005 Express.
I'm trying to connect to Access DB (having System Database) via OPENROWSET.
Everything (client, server and access file) is on local drive.

This works (ODBC):


select *
from openrowset('MSDASQL',
'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MBK.mdb;SystemDB=C:SECURED.MDW;Uid=me;Pwd=pw;',
'select * from [Mbk]')

This works (Jet.OLEDB):

select *
from opendatasource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:MBK.mdb;Jet OLEDBystem Database=C:SECURED.MDW;User ID=me;Password=pw;')
...Mbk

This won't work (Jet.OLEDB):

select *
from openrowset('Microsoft.Jet.OLEDB.4.0',
'MS Access;Database=C:MBK.mdb;System Database=C:SECURED.MDW;UID=me;PWD=pw;',
'select * from [Mbk]')

saying ... "Wrong argument".

This won't work (Jet.OLEDB):

select *
from openrowset('Microsoft.Jet.OLEDB.4.0',
'MS Access;Database=C:MBK.mdb;SystemDB=C:SECURED.MDW;UID=me;PWD=pw;',
'select * from [Mbk]')

saying ... "There are no permissions for usage of object C:MBK.mdb". It seems that it simply hasn't found system database file C:SECURED.MDW, cause when I change SystemDB=C:SECURED.MDW to something like BlahBlahBlah=C:SECURED.MDW the same message is shown.

So, what is the right syntax for stating System Database in OPENROWSET query string? And why 'System Database' won't work?

Thank you.

View 1 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Query Has A Syntax Error

Mar 15, 2008

I have modelled this query after another query which works fine and retrieves a result set. It accepts a search parameter and brings up all the websites in the database by searching on various columns for the search text.  The article search query works the same way.  I just changed the tables and parameters to match the tables.  It uses the "view" and I had to go into the query and check a few columns in the query designer to add the column to the view.
Here's the query with the syntax error: 1 set ANSI_NULLS ON
2
3 set QUOTED_IDENTIFIER ON
4
5 GO
6
7 ALTER PROCEDURE [dbo].[_spArticleSearch]
8
9 @search varchar (100),
10
11 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, ArticleTitle, DateAdded DESC'
12
13 AS
14
15 EXEC('SELECT * from vArticle
16
17 where Active=1 AND ShowInDirectory=1 AND
18
19 (
20
21 Articletitle like ''%' + @search + '%''
22
23 OR Articletext LIKE ''%' + @search + '%''
24
25 OR ShortDesc LIKE ''%' + @search + '%''
26
27 OR Keywords LIKE ''%' + @search + '%''
28
29 IN (SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND (ACategoryName LIKE ''%' + @search + '%'' OR AParentID IN(SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND ACategoryName LIKE ''%' + @search + '%'')))
30
31 )
32
33 ORDER BY ' + @OrderBy )
34
35
36

 When I run the stored procedure and input a word into the search box, and it gives me this:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
(1 row(s) affected)
This is the query it was modelled after which works fine and retrieves all the websites.
1    set ANSI_NULLS ON2    set QUOTED_IDENTIFIER ON3    GO4    5    ALTER PROCEDURE [dbo].[_spWebSiteSearch]6    @search varchar (100),7    @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, TotalExchangedLinks Desc, SiteTitle, SiteURL, DateAdded DESC'8    9    AS10   11   EXEC('SELECT * from vWebSite12   where Active=1 AND ShowInDirectory=1 AND13   (14   sitetitle like ''%' + @search + '%''15   OR SiteURL LIKE ''%' + @search + '%''16   OR SiteDescription LIKE ''%' + @search + '%''17   OR CategoryID IN18   (SELECT CategoryID FROM tblCategory WHERE Active=1 AND (CategoryName LIKE ''%' + @search + '%'' OR ParentID IN(SELECT CategoryID FROM tblCategory WHERE Active=1 AND CategoryName LIKE ''%' + @search + '%'')))19   )20   ORDER BY ' + @OrderBy )21  
Does anyone know where the problem may be? Do the columns in the view have to be in order? When I added my columns, it adds them to the very far right side of the view. Does the columns to be in order in the same way it searches in the query?

View 6 Replies View Related

Query Syntax Error

Feb 23, 2004

This thing is giving me 'Incorrect syntax near the keyword declare'. What's the correct form?



declare @Query varchar(8000)

set @Query = 'insert into PortfolioStock (PortfolioID, StockSymbol) select ' + cast(@Portfolio as varchar) + ', StockSymbol from PortfolioStock where StockSymbol in (''' + replace(ltrim(rtrim(@Textbox)), ' ', ''', ''') + ''')'

exec @Query

View 1 Replies View Related

Sql Query Syntax Error

Jun 15, 2004

Ok I can run the query below in SQL Query Analyzer with no problems. However when I place the SQL query in my asp.net page I get a syntax error. It looks like there is some issue I am not seeing can someone help me. The error i get is "Incorrect syntax near 'pb_sub_recipes_1'. " I have narrowed it down to the area in bold as to where the syntax error appears to be occuring.


SELECT DISTINCT pb_customers.customer_name FROM pb_sub_recipes AS pb_sub_recipes_1 INNER JOIN ((((((pb_jobs INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id) INNER JOIN pb_recipes ON pb_jobs.recipe_id = pb_recipes.recipe_id) INNER JOIN pb_recipes_sub_recipes ON pb_recipes.recipe_id = pb_recipes_sub_recipes.recipe_id) INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id) INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id) LEFT JOIN pb_report_shippers ON pb_jobs.job_id = pb_report_shippers.job_id) ON pb_sub_recipes_1.sub_recipe_id = pb_recipes_sub_recipes.sub_recipe_id WHERE (((pb_jobs.date_time)> '5/30/2004') AND pb_customers.customer_id ='228' AND ((pb_report_shippers.shipper_date_time) Is Null) AND ((pb_jobs.job_deleted)=0)) GROUP BY pb_customers.customer_name, pb_jobs.date_time, pb_sub_recipes.energy,pb_sub_recipes.dose,pb_jobs.job_id,pb_sub_recipes.specie,pb_sub_recipes_1.cost_per_wafer, pb_sub_recipes_1.setup_cost pb_sub_recipes_1.wafers_in_batch"

View 2 Replies View Related

Query Syntax Error - Help

Apr 28, 2005

Hi,

I am using SQL query analyzer and typed the following,

Update xupaddress as A1, xupaddress as B1
SET A1.address1 = upper(B1.address1)
set A1.address2 = upper(B1.address2)
set A1.city = upper(B1.city)
set A1.state = upper(B1.state)
where A1.ProfileID = B1.ProfileID

I am getting followin error :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

Please point out what is wrong
Thank you

View 1 Replies View Related

MDX Query (was Syntax Error)

Mar 2, 2005

" ORDER ( [Accounting Date].[Account Week].Members , DESC ) ON ROWS "

what is wrong with this line???

View 9 Replies View Related

Can {oj Be Used In Query? Gives Syntax Error

Mar 4, 2004

Hi all
i am using a query
SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM
{oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'}
WHERE lcactivityT.licensedclient_id = '1'

This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a
t token ON , line 0 offset 84.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
.......

i removed the white spaces and i also have the correct driver, what can i do?

Thanku

View 4 Replies View Related

Insert Query Syntax Error

Dec 14, 2006

I cannot identify where the syntax error is, please help.

INSERT INTO [Attendence/Activity Log] (ID Number, Date, Activity, Duration) VALUES ('39', '12/14/06', 'Health & Nutrition', '2')

View 1 Replies View Related

XML Query Error Incorrect Syntax Near '&&<'

Feb 6, 2007

Hi all,

I use

="<Query><XmlData>" & Parameters!XMLData.Value & "</XmlData><ElementPath>Product {@}</ElementPath></Query>"



and I enter XMLData with value "<Products><Product>Chair</Product><Product>Table</Product></Products>"

for the query expression of the dataset, and I always got error:

Query failed for dataset "Dataset1"

Incorrect syntax near '<'



What's wrong with it?



Thanks,

Jone

View 1 Replies View Related

Syntax Error - Parameters In A Derieved Query

Jun 1, 2006

I am geting syntax error with the following query. I created a connection with SQL Native Client as Provider. I looks there is bug when we try to parameterise the derieved query



Select [EvidentiaryDataDefinitionHistoryID]
, EvidentiaryDataDefinitionID, Name, ModifiedUTCDate
From HistoryEvidentiaryDataDefinition HEDD (nolock)
Inner Join
(Select max([EvidentiaryDataDefinitionHistoryID]) AS EDDHistoryID
from HistoryEvidentiaryDataDefinition (nolock)
where ModifiedUTCDate >= ? and ModifiedUTCDate < ?
group by EvidentiaryDataDefinitionID,ModifiedUTCDate) T1
On T1.EDDHistoryID = HEDD.EvidentiaryDataDefinitionHistoryID




Regards

Koya

View 3 Replies View Related

Syntax Error (missing Operator) In Query Expression

Jan 22, 2008

Hi, Please could someone assist - the above error occurs.  This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs)        ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue)        Dim myConnection As New OleDbConnection(connString)        Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text        Dim cmd As New OleDbCommand(Str, myConnection)        Dim ds As New DataSet        Dim da As New OleDbDataAdapter(cmd)        da.Fill(ds)        Label7.Text = ds.Tables(0).Rows(0).Item("clientid")        ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid"))    End Sub  Thank you in advance 

View 1 Replies View Related

Syntax Error (missing Operator) In Query Expression '?

Jan 31, 2008

I'm getting the error listed above.  To create this I used the wizard in VS2005 for the datagrid.  The delete works but not the updating.  I can't seem to find what's wrong.  In the update parameters I've removed eventID and added it, neither works.  Delete does work.  Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>

View 4 Replies View Related

Report Bulider - Semantic Query Syntax Error

Apr 27, 2008

Hi,

I am having trouble getting the following query to execute in the report builder:


OPEN Symmetric KEY DemoKey Decryption BY Certificate DemoCertificate;
SELECT

PIN,
EffDate,
Cast(DecryptByKey(Address1) AS varchar(200)) AS Address1,
Cast(DecryptByKey(Address2) AS varchar(200)) AS Address2,
Cast(DecryptByKey(Address3) AS varchar(200)) AS Address3,
Cast(DecryptByKey(City) AS varchar(125)) AS City,
Cast(DecryptByKey(State) AS varchar(125)) AS State,
Cast(DecryptByKey(ZipCode) AS varchar(90)) AS ZipCode,
Cast(DecryptByKey(Country) AS varchar(125)) AS Country
FROM Address
CLOSE Symmetric KEY DemoKey;

I am getting the following error when I try to generate a report in the report builder:

Semantic query execution failed. Incorrect syntax near the keyword 'Open'.
Incorrect syntax near ')'.
----------------------------
Query execution failed for data set 'dataSet'.
----------------------------
An error has occurred during report processing.

I was getting a similar error when I tried to enter the query using the Named Query window of the dsv file. However, I was able to overcome this obstacle by entering the query directly into the code of the dsv file and it works perfectly. That is, when I click on "Explore Data" in the dsv design view the data is retrieved from the database and is decrypted properly.

It appears that the report builder's semantic query engine does not accept transact-sql statements that involve data decryption. Does anybody know if this is true? Or is there some workaround for this situation? Any help would be greatly appreciated.

Thanks,

Mark

View 5 Replies View Related

Nested Select Query Generating Syntax Error

Jan 23, 2008

I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:

Oracle Version:



Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)





SQL Version:



select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)





In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?

Thanks,
Lee

View 4 Replies View Related

Query To Give All Table Sizes On A Database (was Query Help)

Mar 9, 2006

Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks

View 2 Replies View Related

Can Anyone Give Me A Query For This ?

Jul 20, 2005

These are my table structures . Could you please suggest me analternative:tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDatetblArticlePages: ArticlePageID,ArticleID,PageNum,ContenttblAuthors:AuthorID, NametblArticleAuthors:ArticleID,AuthorIDtblContextSubtype:ContextSubTypeID,NameI need to get Author,PostDate,Title,tblContextSubType.Name,PageN um,Content,- these have to grouped accordingly by the respective fields. Contentneed not be grouped by.

View 2 Replies View Related

Give Sql Query For This Illustrations

Mar 28, 2008

Hai friends
 i have table student with 2 fields s_id,s_name
s_id      s_name1            raja2           ramu3           vinoth4            muthu5            arun
i have another table test with s_id who wrote test...
s_id    25
now i have to display like below....give me sql query
s_id   s_name2        ramu5        arun
Thanks in Advance...pl Help me......  

View 2 Replies View Related

Give Me Sql Query For One Illustrations

Mar 28, 2008

Hai friends i have table
s_team
id           name       game
 1           raja         cricket
2            ravi          football
3            muthu     cricket
4           pravin      cricket
5          hurry       football
 I have to diplay the above table in the following ...pl give me sql query
game          totalplayers
cricket            3
football           2
 

View 2 Replies View Related

Wht Does This Give Divide By 0 Error?

May 24, 2007

I am using iif which works just fine in Access to prevent divide by zero errors but this:



=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")



Fails



As Does:





=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)



Why? How do I get around this?

View 4 Replies View Related

OPENROWSET Error

Mar 25, 2004

I am trying to use OPENROWSET in SQL Server to connect to my Access database, but I keep getting the following error:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'X:SetupDatabaseKDB_X2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I have checked the database, it is not in use, and there is no password set on the database that would prevent me from getting access to it. Any ideas? Here's the syntax I'm using:

SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'X:SetupDatabaseKDB_X2.mdb';'Admin';'', subPSEL_PList))

View 3 Replies View Related

Openrowset Error

Jul 22, 2006

I have an access database from which i want to use data in sql server if a table has column with values like 6.32404E-244 i've got an error message like:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)' returned invalid data for column '[Microsoft.Jet.OLEDB.4.0].Stoc_Max'

a simple select like 'SELECT * FROM OPENROWSET(.....,table) gives me this error.

View 3 Replies View Related

Error With Openrowset

May 17, 2007

I have a text file, that I need to import to SQL. I am trying to do this using OPENROWSET. This is the qry i use below.



select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver
(*.txt; *.csv)};
DefaultDir=c:edge;',
'select * from
vwConstrLendingLoanDefiDetailReady.txt')



I have created a System DSN, For the microsoft text driver. Anyhow it throws this error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].



Any idea why?

View 5 Replies View Related

OPENROWSET Error

Feb 23, 2007

I am trying to execute the following ad-hoc query (I have already configured the remote server to allow ad-hoc queries):

--EXECUTE AS USER = suser_name()
SELECT c.*
INTO #MyTempTable
FROM OPENROWSET('SQLNCLI', 'Server=MyRemoteServer;Trusted_Connection=yes;',
'select * from mydb..mytable (nolock)
where PurchaseOrderID in (''19509114'', ''24075854'')) AS c;

I receive the following error message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

I have admin privileges on the remote box-how can I get this to execute in the context of my account rather than this NT AUTHORITYANONYMOUS LOGON account?

Thanks,
-Dave

View 2 Replies View Related

OPENROWSET Error

Apr 15, 2008

Hi, I've been having a really weird error with a one-liner:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:directExport.xls;HDR=YES','SELECT * FROM [directExport$]')

Error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I managed to fix this since the account SqlServer was using didn't have right privelages to wwwroot or windir apparently, where the saved excel file was.

However, I wrote a .net page letting me save a gridview as an Excel, and then I tried using that Excel file (same format of information inside) and the error popped up again. I then cut out the information in that excel file (created by my .net page), pasted it into a new excel file and then renamed it, and the new file would work when I ran the query analyzer. The only difference is that in the original excel file created, the lines seperating the cells are not there (the gray ones that are there by default), but I really doubt this is the problem, as gray lines are there just to help the user see the different cells.

This is really puzzling to me. Why would it not work in one file, but when I cut it out and put it into an excel file it would work? I wasn't really sure where to post this, as this could be a .net error, and something's wrong with my saving-gridview-as-excel, but I just wanted to know if this could be related to T-SQL first before I try elsewhere. Thanks!

View 1 Replies View Related

Error Openrowset

Oct 25, 2006

my DMX query is:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:data mining va XML212025_0212279DATABASEAccessSavingsMartSavingsMart.mdb;Persist Security Info=False',
'SELECT DISTINCT
"vw_Shipments"."ShipmentID"
AS "Shipment Id",
"vw_Shipments"."DaysSinceLastShipped"
AS "Days Since Last Shipped",
"vw_Shipments"."StoreID"
AS "Store Id",
"vw_Shipments"."Quantity"
AS "Quantity",
"vw_Shipments"."VendorName"
AS "Vendor Name",
"vw_Shipments"."ProductType"
AS "Product Type"
FROM "vw_Shipments"')



and error is:

Error (Data mining): This server is not configured to support the 'microsoft.jet.oledb.4.0' provider used in the OPENROWSET clause.



Can you help me,please?

View 5 Replies View Related

DateRange Query Give Wrong Results

Sep 1, 2004

I am running some query with the following where clause

where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'

It gives the records where DateCreated is of 2004-07-01 21:48:02.377

the default language on the server is British English.

Could anybody please tell me the reason why records from july are also coming

View 1 Replies View Related

Please Give Me Your Suggestions On Report Query - Very Urgent

Oct 25, 2006

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar











View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved