error parsing the query

hi all

I try to run the query below it throw me an exception, error parsing , is there anywat to resolve it?

SELECT TYPE2.list_price as 'TYPE2'
FROM TBL_MST_PRICE TYPE2
WHERE
price_grp_code = (SELECT price_grp_code FROM TBL_MST_PRICE_GRP WHERE prd_code = '' AND cust_code = '')

Sub-selects (nested select statements) are not supported by SQL CE. You will have to rephrase your query to use a join or use 2 select statements.

error Openrowset

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 XML\0212025_0212279\DATABASE\Access\SavingsMart\SavingsMart.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?

Moving to DM forum.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

You should make sure that:

- Ad hoc open rowset queries are supported by the server (the AllowAdHocOpenRowsetQueries server property)

- the specified OLE DB provider, Microsoft.Jet.OLEDB.4.0 is included in the list of supported providers (the AllowedProvidersInOpenRowset server property)

More details: http://msdn2.microsoft.com/en-us/library/ms175443.aspx

|||

thank you.

I checked AllowAdHocOpenRowsetQueries and AllowedProvidersInOpenRowset is SQLOLEDB.1
but run my DMX query:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=Mart;Data Source=hong',
'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:
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Analyze Shipments_Structure ~MC' cube from the data2 database.

can you help me?

|||

INSERT INTO does column mapping by ordinal, i.e. the order of the columns in the INSERT INTO clause has to be the same as in the source data query. In your DMX statement the order is:

SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]

In your source data query, the order is

ShipmentID
DaysSinceLastShipped
StoreID
Quantity
VendorName
ProductType

which means you are doing things like putting the Vendor Name into the Days Since Last Shipped column, which would give type conversion errors - if you're lucky. If you were unlucky, and you happened to mismatch columns to places where they actually fit, it would have worked and you would have a meaningless model.

sql

Error on Stored Procedure

I need to perform a select statement across servers... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #tableTake out GO statements after SET and re-run the SP.|||Originally posted by Satya
Take out GO statements after SET and re-run the SP.

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

I get the same error telling me to enable ansi_nulls and ansi_warnings...|||Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.|||that is a GOOD CALL!!!!!!!!!!!

You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

First transact-sql statement in job:
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

Second transact-sql statement in job
exec procedure1

also tried combining
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO|||mmm...do have already tried to remove the "GO" statement?

to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)sql

Error on Select Statement when Using IIF

What's wrong with this query? I get an error at ">" sign.
SELECT MyCol1, MyCol2, MyCol3Balance,
IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
from MyTableHi,
There is no IIF in SQL Server. Instead use CASE statement.
Eg:-
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
Thanks
Hari
SQL Server MVP
"Shariq" <Shariq@.discussions.microsoft.com> wrote in message
news:88174876-91BB-48E7-BD1B-3481D6B32FDE@.microsoft.com...
> What's wrong with this query? I get an error at ">" sign.
> SELECT MyCol1, MyCol2, MyCol3Balance,
> IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
> from MyTable
>

Error on Query to Sys Views (e.g. sys.objects)

I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.

I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.

I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.

Can someone *please* tell me what I'm doing wrong here?

I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.

Thanks!

what i feel is you are still connecting to SQL Server 2000. post the result of

Select @.@.Version

run the above query in query anlyser from where you are getting this error and post the result

you might have only installed Tools...

Madhu

|||

That was it Madhu. Thanks for the prompt reply. I had assumed that in upgrading to Sql Server 2005 all databases were pushed to the 2005 engine. Is there a manual step I must complete in order to do just that?

In addition, can you tell me how to query the user account I'm logged in as?

Thanks again

|||

(a) install SQL Server 2005

(b) use backup/restore or detach /attach method to transfer the database

(c) make script of other server level objects like jobs etc and run it in sql server 2005 instance

To fine user account : SELECT SUSER_SNAME()

Madhu

|||Thanks again for the feedback!

Error on Query to Sys Views (e.g. sys.objects)

I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.

I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.

I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.

Can someone *please* tell me what I'm doing wrong here?

I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.

Thanks!

what i feel is you are still connecting to SQL Server 2000. post the result of

Select @.@.Version

run the above query in query anlyser from where you are getting this error and post the result

you might have only installed Tools...

Madhu

|||

That was it Madhu. Thanks for the prompt reply. I had assumed that in upgrading to Sql Server 2005 all databases were pushed to the 2005 engine. Is there a manual step I must complete in order to do just that?

In addition, can you tell me how to query the user account I'm logged in as?

Thanks again

|||

(a) install SQL Server 2005

(b) use backup/restore or detach /attach method to transfer the database

(c) make script of other server level objects like jobs etc and run it in sql server 2005 instance

To fine user account : SELECT SUSER_SNAME()

Madhu

|||Thanks again for the feedback!

Error on query execution in VBA Excel using ADODB on SQL Server

We're trying to upsize from MS Jet (Access) db to SQL Server and am having a
major problem. Because we've not seen this error with the MS Jet db, I
suspect it's related to the user configuration in SQL Server.
The servername is "sqlbox" and the database name is "TCR". We use WinNT
authentication on the db and have a user profile for all our domain users.
The connection string executes, but the query does not. If the VBA Project
is unlocked, an error stating "Application Error...Unspecified Error" occurs
at the line noted below (--> ). When the VBA Project is locked (normally),
the error states "Invalid object name" and then lists the database table (in
this example, "Version").
Here's the pertinent code:
strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
"Persist Security Info=False;Initial Catalog=TCR;" & _
"Integrated Security=SSPI"
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = strConn
adoConn.Open
strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "';
"
' open a recordset from Version table
Set adoRec = New ADODB.Recordset
adoRec.CursorLocation = adUseClient
--> adoRec.Open strSQL, adoConn <-- Error occurs here
I can run the application without problems; perhaps it is because I am the
dbowner (which implicitly grants me full access to the tables). However, I
have granted full access to the Domain User profile without success by any
other user.
Unless I find a way to solve this, there will be no migration! Thanks in
advance!Hi Glenn
You posted in the wrong place. There is a dedicated new group forACCESS/SQL
SERVER/ADP Integration. You will get better answers there as they are
specialized in that.
Regards
R.D
"Glenn Ray" wrote:

> We're trying to upsize from MS Jet (Access) db to SQL Server and am having
a
> major problem. Because we've not seen this error with the MS Jet db, I
> suspect it's related to the user configuration in SQL Server.
> The servername is "sqlbox" and the database name is "TCR". We use WinNT
> authentication on the db and have a user profile for all our domain users.
> The connection string executes, but the query does not. If the VBA Projec
t
> is unlocked, an error stating "Application Error...Unspecified Error" occu
rs
> at the line noted below (--> ). When the VBA Project is locked (normally)
,
> the error states "Invalid object name" and then lists the database table (
in
> this example, "Version").
> Here's the pertinent code:
> strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
> "Persist Security Info=False;Initial Catalog=TCR;" & _
> "Integrated Security=SSPI"
> Set adoConn = New ADODB.Connection
> adoConn.ConnectionString = strConn
> adoConn.Open
> strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "
';"
> ' open a recordset from Version table
> Set adoRec = New ADODB.Recordset
> adoRec.CursorLocation = adUseClient
> --> adoRec.Open strSQL, adoConn <-- Error occurs here
> I can run the application without problems; perhaps it is because I am the
> dbowner (which implicitly grants me full access to the tables). However,
I
> have granted full access to the Domain User profile without success by any
> other user.
> Unless I find a way to solve this, there will be no migration! Thanks in
> advance!

Error on query (Select distinct)

I am not an expert in sql but I am trying to learn. I have what for me is a
complex query, but I am getting an error when sorting out by one of the
parameters, here is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
in the select list if SELECT DISTINCT is specified.
/BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
--
Line 155 is the end of the recordset (When it executes)
<%
Dim CasesAdvancedReports
Dim CasesAdvancedReports_numRows
Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL WHERE A.firmid = " +
Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIKE
'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
false
CasesAdvancedReports.CursorType = 0
CasesAdvancedReports.CursorLocation = 2
CasesAdvancedReports.LockType = 1
CasesAdvancedReports.Open()
CasesAdvancedReports_numRows = 0
%>
This is my query:
SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm, a.approvaldate
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL
WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
ORDER BY MMColParam2
Any help is appreciated.
AleksJust as the error says -- you need to include the column you're ordering by
in the select list if you use the DISTINCT keyword. If this ruins your
distinctness, you will have to re-write query so as not to use DISTINCT. If
you need help with that, please post DDL and sample data. See this link for
more info:
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O5eKN2dEFHA.4052@.TK2MSFTNGP09.phx.gbl...
> I am not an expert in sql but I am trying to learn. I have what for me is
a
> complex query, but I am getting an error when sorting out by one of the
> parameters, here is the error:
> --
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must
appear
> in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
> --
> Line 155 is the end of the recordset (When it executes)
> <%
> Dim CasesAdvancedReports
> Dim CasesAdvancedReports_numRows
> Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
> CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
> CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
> b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
> a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
> Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
> Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
> AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived
LIKE
> '" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY
"
> + Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
> setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
> false
> CasesAdvancedReports.CursorType = 0
> CasesAdvancedReports.CursorLocation = 2
> CasesAdvancedReports.LockType = 1
> CasesAdvancedReports.Open()
> CasesAdvancedReports_numRows = 0
> %>
>
>
> --
> This is my query:
> SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
> e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm, a.approvaldate
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
> JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
> Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL
> WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
> ORDER BY MMColParam2
>
> --
> Any help is appreciated.
> Aleks
>|||Like the error message says if you are going to use an order by clause
with distinct then the order by items MUST appear in the select
portion of the statement.
On Sun, 13 Feb 2005 10:06:07 -0500, "Aleks" <arkark2004@.hotmail.com>
wrote:

>I am not an expert in sql but I am trying to learn. I have what for me is a
>complex query, but I am getting an error when sorting out by one of the
>parameters, here is the error:
>--
>Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
>in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
>--
>Line 155 is the end of the recordset (When it executes)
><%
>Dim CasesAdvancedReports
>Dim CasesAdvancedReports_numRows
>Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
>CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
>CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
>b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
>a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
>Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
>Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
>AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIK
E
>'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
>+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
>setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
>false
>CasesAdvancedReports.CursorType = 0
>CasesAdvancedReports.CursorLocation = 2
>CasesAdvancedReports.LockType = 1
>CasesAdvancedReports.Open()
>CasesAdvancedReports_numRows = 0
>%>
>
>
>--
>This is my query:
>SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
>e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
>f.MaidenNm, a.approvaldate
>FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
>JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
>Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL
>WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
>ORDER BY MMColParam2
>
>--
>Any help is appreciated.
>Aleks
>
We see the world, not as it is, but as we are.

Error on INSERT Query

I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
server . The data type of this column is varchar (100). i am getting the
folloing error - incorrect syntax near '68bf'. I think it is not accepting
the '-'. How can i get a work around for the '-' so that the above values
gets inserted successfully.
Vijay,
can you post some sample DDL for the table, as well as the insert statement
that's throwing the error? that value should be legal in the column.
David
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
|||Are you trying to insert via a stored procedure, or a simple insert
statement?
Try delimiting the string with single quotes. As in:
INSERT INTO YourTable (YourColumn) VALUES
('489d68bf-baa1-415d-94c7-a8e56b-609c61')
Replace YourTable and YourColumn with whatever is appropriate
Keith Kratochvil
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
sql

Error occured while reading data from the query result set

Strange error. Am able to run a stored procedure from query analyzer with ...
EXEC spRS_ShopPAP '2004-03-16', '2004-04-14'
and data returns perfectly. Try and run this from the data preview window in reporting services and you get "Error occured while reading data from the query result set. OLE DB Provider MDSASQL reported an error"
Has anyone seen this before ? Shouldn't it work in the data preview window ?
Thanks, Steve.
Would a stored proc run fine in the preview window without parameters? I'm
curious...
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error occured while reading data from the query result set

Strange error. Am able to run a stored procedure from query analyzer with ..
.
EXEC spRS_ShopPAP '2004-03-16', '2004-04-14'
and data returns perfectly. Try and run this from the data preview window in
reporting services and you get "Error occured while reading data from the q
uery result set. OLE DB Provider MDSASQL reported an error"
Has anyone seen this before ? Shouldn't it work in the data preview window ?
Thanks, Steve.Would a stored proc run fine in the preview window without parameters? I'm
curious...
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error number 8941

Hi All,
when I run a stored procedure thru Query Analyzer, I get this error msg.
Server: Msg 8941, Level 22, State 102, Line 5
Table error: Object ID 5810549, index ID 1, page (0:782677886). Test (offset
+ len < PAGESIZE) failed. Slot 25, offset 0x1089 is invalid.
But when I tried to find out which object is throwing error I could not find
out.
I used the following code :
Select * from sysobjects where id = 5810549
go
Select object_name(5810549)
Any thoughts...
Thanks,
PradI've not seen that error before first hand. I'd personally try running the
DBCC CHECKDB command to check the integrity of the objects within the
database. If this flags up problems, post them back and I'll try to offer a
fix for them.
Hope this helps
--
Adam J Warne, MCDBA
"Pradeep Kutty" wrote:

> Hi All,
> when I run a stored procedure thru Query Analyzer, I get this error msg.
> Server: Msg 8941, Level 22, State 102, Line 5
> Table error: Object ID 5810549, index ID 1, page (0:782677886). Test (offs
et
> + len < PAGESIZE) failed. Slot 25, offset 0x1089 is invalid.
> But when I tried to find out which object is throwing error I could not fi
nd
> out.
> I used the following code :
> Select * from sysobjects where id = 5810549
> go
> Select object_name(5810549)
> Any thoughts...
> Thanks,
> Prad
>
>
>

Error msg 8616

Hello,
I got this massage,
'Warning: The query processor could not produce a query plan from the
optimizer because the total length of all the columns in the GROUP BY or
ORDER BY clause exceeds 8000 bytes'
How I can do it?
Thanks,
JNHi, JN
I believe the full text of the error message is:
Server: Msg 8619, Level 16, State 2, Line <n>
Warning: The query processor could not produce a query plan from
the optimizer because the total length of all the columns in the
GROUP BY or ORDER BY clause exceeds 8000 bytes.
Resubmit your query without the ROBUST PLAN hint.
However, if you try your query without "OPTION (ROBUST PLAN)" you may
get the following error:
Server: Msg 1540, Level 16, State 1, Line <n>
Cannot sort a row of size <x>, which is greater than the allowable
maximum of 8094.
Am I correct so far ? If yes, you should redesign your query so that
any necessary sorting will be done on columns with a total length less
than 8000 bytes. We can help you with this task only if you post DDL,
sample data, expected results and your current query. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Razvan

Error message: Data mining Query builder cannot....

Hello,

I wrote a data mining plug-in algorithm in SSAS and now I am trying to view the lift chart in Management Studio. The studio gives the following error:

"The Data Mining Query builder control cannot be initialized due to the following problem:
Deserialization failed: Multiple definition of element 'Column12' causes the content model to become ambigious. A content model must be formed such that during validation of an element information item sequence, the particle contained directly, indirectly or implicitly therein with which to attempt to validate each item in the sequence in turn can be uniquely determined without examining the content or attributes of that item, and without any information about the items in the remainder of the sequence."

I really do not understand what i did wrong. Can anybody help me?

Thanks, Joris

Can you describe the model you built and list the steps you went through to get the lift chart?

Also, what type of algorithm does your plug-in provide? Does it support discrete or continuous predictable columns?

|||

My algorithm is a support vector machine and the predictable columns are only discrete. The dataset i used is the wdbc dataset of the UCI repository.

I tried to get the lift chart by right-clicking on the build model in SQL Server Management Studio and select "View Lift Chart". When I try to get the lift chart in VS Business Intelligence Studio, VS BI shows the lift chart.

|||

What version of SQL Server 2005 are you running? (check the version for SQL Server Management Studio under the Help->About in Mgmt Studio's file menu). Did you install a CTP build of SP2?

Would you be able to send your plug-in algorithm .exe as well as the model definition to raman.iyer at microsoft.com? We might need to reproduce your problem to investigate this further.

|||

The version of SQL Server Management Studio i am running is:

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I installed SP2, but the specific build, i cannot remember. But as far as I can see in my control panel it is not a CTP build.

I have to discuss whether I can send my plug-in algorithm with Avanade Netherlands. I will let you know.

Error message: Data mining Query builder cannot....

Hello,

I wrote a data mining plug-in algorithm in SSAS and now I am trying to view the lift chart in Management Studio. The studio gives the following error:

"The Data Mining Query builder control cannot be initialized due to the following problem:
Deserialization failed: Multiple definition of element 'Column12' causes the content model to become ambigious. A content model must be formed such that during validation of an element information item sequence, the particle contained directly, indirectly or implicitly therein with which to attempt to validate each item in the sequence in turn can be uniquely determined without examining the content or attributes of that item, and without any information about the items in the remainder of the sequence."

I really do not understand what i did wrong. Can anybody help me?

Thanks, Joris

Can you describe the model you built and list the steps you went through to get the lift chart?

Also, what type of algorithm does your plug-in provide? Does it support discrete or continuous predictable columns?

|||

My algorithm is a support vector machine and the predictable columns are only discrete. The dataset i used is the wdbc dataset of the UCI repository.

I tried to get the lift chart by right-clicking on the build model in SQL Server Management Studio and select "View Lift Chart". When I try to get the lift chart in VS Business Intelligence Studio, VS BI shows the lift chart.

|||

What version of SQL Server 2005 are you running? (check the version for SQL Server Management Studio under the Help->About in Mgmt Studio's file menu). Did you install a CTP build of SP2?

Would you be able to send your plug-in algorithm .exe as well as the model definition to raman.iyer at microsoft.com? We might need to reproduce your problem to investigate this further.

|||

The version of SQL Server Management Studio i am running is:

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I installed SP2, but the specific build, i cannot remember. But as far as I can see in my control panel it is not a CTP build.

I have to discuss whether I can send my plug-in algorithm with Avanade Netherlands. I will let you know.

error message when trying to create any query

I am trying to run a query on my sql server and get the following error message:

"An error occurred while executing batch. Error message is: The directory name is invalid."

how do I fix?

Simple -Use a valid folder name!

If you were to post your code, and the complete error message, perhaps we could give you more directed assistance.

|||I am experiencing the same problem.
There are no directories or code involved.
The error occurs in SQL server management studio on a simple sql statement like

select * from table_name;

Regards

Zlatko
|||It is resolved in meantime.
Our sysadm changed some user rights on the domain controller - and that was causing the error.
After he returned the old setting - everything went back to normal.

Z.

Error Message is not Displaying in this Query? why?

Hello all

please see this query

select * from Table1 where Table1ID in
(select Table1ID from Table3)

Step1 )select Table1ID from Table3

output->Giving error as Table1ID is not valid column in Table3 .

Step2)select * from Table1 where Table1ID in
(select Table1ID from Table3)

Output-> Giving all records of Table1 as i am expecting error from this query .

Please check with your demo database and reply.

Thanking you.

Ramana.

There are no errors. By TSQL syntaxis, all Table1 columns are visible in subquery.

If you'd alias all your tables, then you will be never confused by such behavior. Look at:

select

*

from Table1 t1

where t1.Table1ID in (

select

t3.Table1ID

from Table3 t3

)

|||

Thanks for your reply .i did that one and i got error .but what was the problem in first query it has to give error .so please tell me what is problem with that one .

Thanks

|||As I said, becuse of all Table1 columns are visible within subquery. As you didn't specify alias for "Table1ID" subquery column, the column have been sought in both Table1 and Table3.|||

Thanks ,

But i want to know that is it necessary to add alias whenever i am using 'in ' in the query .

|||

Sorry, but SQL Server doesn't see an error in your query. If you had the same column in both tables and didn't properly alias them then you'd get an error, because it would need to know which table you were referencing, but since each column referenced in the select only exists in one place, the query parser is happy.

It's really a good idea to ALWAYS alias your columns, and then there's never a doubt regarding the source of the data.

|||

The answer here is that subqueries are technically correlated, whether you add a where clause to join them or not. So in this query:

say that

select table1Id from table1

returns:

table1Id
1
2

select *
from Table1
where Table1ID in (select Table1ID from Table3)

For the row where table1Id = 1, The results of (select Table1ID from Table3)

1 table3.column1 table3.column2
1 table3.column1 table3.column2

For the row where table1Id = 2, The results of (select Table1ID from Table3)

2 table3.column1 table3.column2
2 table3.column1 table3.column2

This allows you to add a where clause of WHERE table3.table1Id = table1.table1Id.

So the advice given on using aliases with subqueries is definitely correct. Or, just use

select *
from Table1
where exists (select *
from table3
where table3.table1Id = table1.table1Id)

where you would be less likely to make that mistake (which I have made a few times myself and asked the same question too :)

Error message

Hello,
If a execute a query
SELECT '200703' AS Expr1, Zahlungen.KundeID, Zahlungen.Periode,
Zahlungen.Datum, Zahlungen.Betrag, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS
Expr5,
0 AS Expr6, 'U' AS Expr7, Kunde.H_Kanton , Kunde.H_Plz
FROM Zahlungen INNER JOIN
Kunde ON Zahlungen.KundeID = Kunde.ID
WHERE (Zahlungen.Betrag > 0) AND (Zahlungen.Periode = '200703' OR
Zahlungen.Periode = '200713' OR
Zahlungen.Periode = '200613') AND (Zahlungen.KundeID >
0)
I get an error message:
Server: Nachr.-Nr. 3624, Schweregrad 20, Status 1, Zeile 1
Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 56
Process ID: 296
Die Verbindung wurde unterbrochen.
If I use "TOP 100000" it works fine. If I use TOP 1000000 (million) I get
the error message.
It seams to bee a new problem, because on Tuesday it worked.
Many thanks for your support and best Regards.
StefanPossibly a data corruption. Check out DBCC CHECKDB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stefan Braun" <stefan@.braun.name> wrote in message news:58osl6F2hrlctU1@.mid.individual.net...
> Hello,
> If a execute a query
> SELECT '200703' AS Expr1, Zahlungen.KundeID, Zahlungen.Periode,
> Zahlungen.Datum, Zahlungen.Betrag, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS
> Expr5,
> 0 AS Expr6, 'U' AS Expr7, Kunde.H_Kanton , Kunde.H_Plz
> FROM Zahlungen INNER JOIN
> Kunde ON Zahlungen.KundeID = Kunde.ID
> WHERE (Zahlungen.Betrag > 0) AND (Zahlungen.Periode = '200703' OR
> Zahlungen.Periode = '200713' OR
> Zahlungen.Periode = '200613') AND (Zahlungen.KundeID >
> 0)
> I get an error message:
> Server: Nachr.-Nr. 3624, Schweregrad 20, Status 1, Zeile 1
> Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 56
> Process ID: 296
> Die Verbindung wurde unterbrochen.
> If I use "TOP 100000" it works fine. If I use TOP 1000000 (million) I get
> the error message.
> It seams to bee a new problem, because on Tuesday it worked.
> Many thanks for your support and best Regards.
> Stefan
>
>
>

Error message

Hello,

When I try to run a query on a server, I get the following error:

"Cannot resolve collation conflict for equal to operation."

The same query, but on another server works without any problems.

Any idea how I can resolve this?You are joining two different coallations which each other. To solve the collation conflict you will have to either change the collation on the server / database / table / or expression server you are using (whereas the latter is the quickfix for your problem. It has be to implemented whereever you are joining different collations:

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description COLLATE SQL_Latin1_General_CP1_CI_AI

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks Jens.

But the query contains tables of the same database.|||That is not just related to different databases. Also just columns within a table can have a different collation. QUery the INFORMATION_SCHEMA.Columns to see which Collation they have and use the mentioned keyword and syntax to use a common collation.

HTH, Jens.|||txs Jens.

I saw some different entries in the syscolumns under the field collation.
Unfortunately I cannot change the collation name.

What can I do (apart from changing my queries)?|||Is there any specific reason why you have different collation settings for tables in the same database? Unless you're willing to "sweat it out" the easiest way is to change the collation settings of one table to be the same as the other.|||I copied the tables from another server, apparently with a different collation setting.

How can I change this setting per table?|||I think there is no command for changing every column within the table. YOu will have to change this by column with the command:

ALTER TABLE <tablename> ALTER COLUMN colname <datatype> COLLATE <collationname>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de