Error loading Query Execution Plan

I am trying to tune a very long running query (18 minutes on an Axim X51, 8secs on my laptop), but I can't get the query plan file that is generated on the device to load in the Sql Server Management Studio. I am using the Sql Everywhere CTP on the device, and version 9.00.2047 of the management studio shell.

FWIW, when I try to create the execution plan by running the same query on a .sdf file local on my laptop, I get a similar error trying to view the query plan.

Apart from the query plan issues, it would appear (just from the query execution time) that the indexes defined on the sdf file are not being used when executing the query on the device, but are being used when executing the query on the laptop. This is pure SWAG on my part, though.

I can't figure out how to attach a file to the post, unfortunately.

Thanks for any help you can offer.

Matthew Belk, BizSpeed, Inc.

Matthew,

What is the error that you get when loading the query execution plan in Management Studio? My guess is that it would be an issue in rendering the graphical execution plan. However, you can still see the plan information from the plan file (xml format).

Also, the query plan usually does not differ on the laptop and the device.

|||

I was able to identify the query bottleneck by manually interpreting the plan file, but it was an arduous task sifting through all that XML. There was a similar issue posted to the Feedback section in the SQL Server area of the Microsoft Connect website (issue id 231137) that I validated and commented on. That issue was marked closed/fixed, but there were no additional comments.

Thanks, Matthew

Here is the text of the plan file:

===================================

Error loading execution plan XML file C:\Documents and Settings\mbelk\My Documents\batitem.sqlplan. (SQLEditors)

Program Location:

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.LoadXml(String xmlFile)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.Editor.Microsoft.VisualStudio.Shell.Interop.IPersistFileFormat.Load(String fileName, UInt32 grfMode, Int32 readOnly)

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

Program Location:

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertToObjectWrapper(Object item)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.PropertyFactory.CreateProperty(PropertyDescriptor property, Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectParser.ParseProperties(Object parsedItem, PropertyDescriptorCollection targetPropertyBag, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanParser.Parse(Object item, Object parentItem, Node parentNode, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder.Execute(Object dataSource)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.LoadXml(String xmlFile)

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

Program Location:

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.PropertyFactory.CreateProperty(PropertyDescriptor property, Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectParser.ParseProperties(Object parsedItem, PropertyDescriptorCollection targetPropertyBag, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ExpandableObjectWrapper..ctor(Object item, String defaultPropertyName, String displayName)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.Convert(SeekPredicateType item)

===================================

Object reference not set to an instance of an object. (SqlMgmt)

Program Location:

at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ExpandableArrayWrapper.PopulateProperties(ICollection collection)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertToObjectWrapper(Object item)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.PropertyFactory.CreateProperty(PropertyDescriptor property, Object value)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectParser.ParseProperties(Object parsedItem, PropertyDescriptorCollection targetPropertyBag, NodeBuilderContext context)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ExpandableObjectWrapper..ctor(Object item, String defaultPropertyName, String displayName)
at Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.ObjectWrapperTypeConverter.Convert(ScanRangeType item)

|||

Sorry about that. I checked out the details.

This error shows up whenever more than one column of a composite index is being used for a seek or scan.

e.g.

select * from
stores, sales where
sales.stor_id = stores.stor_id
and sales.ord_num = 'test'
- with a composite index on sales.stor_id and sales.ord_num.

For a work around, choose only one column from the index while tuning the query initially.

e.g.

select * from
stores, sales where
sales.stor_id = stores.stor_id

The plan that you get should be similar to the actual plan. For the final pass of tuning the query, use the full query!

Thanks

Pragya

sql

Error linked server query after installing SP4 on SQL 2000

Hello
SQL 2000 SP3a run fine with an linked server query like
select * from openquery( linkedOlapServer, .. here is the MDX ...')
After installing SP4 build 2039 this query fails with error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSOLAP'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP
returned 0x80040154].
Appreciate hints from experience on which hot fix do I need to install?
Nic
Does changing the "AllowInProcess" to something non zero fix this problem?
http://msdn2.microsoft.com/en-us/library/ms190918.aspx
In Object Explorer, right-click the provider name and select Properties. The
following table describes the available provider options...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Nic" <Nic@.discussions.microsoft.com> wrote in message
news:E4A1CCBC-331D-4C70-B83C-F1AFE5CFAA62@.microsoft.com...
> Hello
> SQL 2000 SP3a run fine with an linked server query like
> select * from openquery( linkedOlapServer, .. here is the MDX ...')
> After installing SP4 build 2039 this query fails with error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'MSOLAP'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP
> returned 0x80040154].
> Appreciate hints from experience on which hot fix do I need to install?
> Nic
>

Error in X-Path Query

I am new to XML in SQL Server and X-Path Query

Recently I have done the following virtual lab in techNet site

SQL Server 2005 XML Capabilities

The following query is worked fine in the lab

SELECTTOP 10 Demographics.query('declare default element namespace=

"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"

/IndividualSurvey/YearlyIncome')

FROM Sales.Individual

But later I installed SQL Server Express in my system and also the Express Manager CTP

but i am getting the following error if I execute the above query in my system

Msg 9317, Level 16, State 1, Line 1

XQuery [Sales.Individual.Demographics.query()]: Syntax error near '=', expected string literal.

Check ou this link: http://tinyurl.com/avqxx

Check out this syntax. I think your syntax is wrong and are missing a ref to the namespace and a ' ; '.

I.e

SELECT MyXml.query('

declare namespace s="http://myns/mydemoschema";

/s:root/s:product[@.s:id="304"]/s:name')FROM MyTable

notice the ; indeclare namespace s="http://myns/mydemoschema";

|||i think the name space is required when we are assigning the declared name space to a reference such as "s=" in the above example|||take default element out too.|||

Finally It is solved

I found the problem from another forum athttp://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=248323

Here is the answer given by Veteran

To declare default namespace, there is no "=" after namespace keyword. And there is a ";" after the namespace string. The following query works:

SELECTTOP 10 Demographics.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";

/IndividualSurvey/YearlyIncome')

FROM Sales.Individual

Error in X-Path Query

I am new to XML in SQL Server and X-Path Query

Recently I have done the following virtual lab in techNet site

SQL Server 2005 XML Capabilities

The following query is worked fine in the lab

SELECT TOP 10 Demographics.query('declare default element namespace=

"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"

/IndividualSurvey/YearlyIncome')

FROM Sales.Individual

But later I installed SQL Server Express in my system and also the Express Manager CTP

but i am getting the following error if I execute the above query in my system

Msg 9317, Level 16, State 1, Line 1

XQuery [Sales.Individual.Demographics.query()]: Syntax error near '=', expected string literal.

The correct syntax for the XQuery expression is below:

SELECT TOP 10 Demographics.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
/IndividualSurvey/YearlyIncome')
FROM Sales.Individual

sql

error in web service reporting

hi friends i created a web service for reporting service. When i try
to send the parameter i am getting error of "here is an error in the
query. Failed to execute web request for the specified URL." I am
pasting the web method and the xml query can u please help me out in
this i am new to this field.
<WebMethod()> _
Public Function GetUserID(ByVal User_ID As Integer) As DataSet2
Dim ta As New
DataSet2TableAdapters.core_ApplicationUserTableAdapter()
Dim ds As New DataSet2
Dim dt As New DataSet2.core_ApplicationUserDataTable
ta.ClearBeforeFill = True
ta.FillByUSerID(dt, User_ID)
ds.Merge(dt)
Return ds
End Function
<Query>
<Method Namespace="http://tempuri.org/" Name="GetUserID"/>
<SoapAction>http://tempuri.org/GetUSerID
</SoapAction>
<Parameters>
<Parameter Name="User_ID">
<Value>User_ID</Value>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="true">
GetUSerIDResponse{}/
GetUSerIDResult/diffgram{}/DataSet2/core_ApplicationUser
</ElementPath>
</Query>
Please help me outHi
The easiest way to resolve this issue is to use an HTTP tracer like
fiddler. As a starting point try creating a parameterless web method
and querying against that just to verify that you are hitting the end
point. If that works , the next step would be to check the element
path . I would look at the query being sent by using fiddler and then
compare it to the wsdl definition to see if the web service is getting
all its parameters in the format it expects. This article pertains to
using ssrs to query a MOSS webservice but should help you out.
http://rockstarguys.com/blogs/colin/archive/2006/11/10/accessing-sharepoint-list-items-with-sql-server-2005-reporting-services.aspx
Cheers
Shai
On Nov 19, 4:05 pm, ayrookuzhy <arujgeo...@.gmail.com> wrote:
> hi friends i created a web service for reporting service. When i try
> to send the parameter i am getting error of "here is an error in the
> query. Failed to execute web request for the specified URL." I am
> pasting the web method and the xml query can u please help me out in
> this i am new to this field.
> <WebMethod()> _
> Public Function GetUserID(ByVal User_ID As Integer) As DataSet2
> Dim ta As New
> DataSet2TableAdapters.core_ApplicationUserTableAdapter()
> Dim ds As New DataSet2
> Dim dt As New DataSet2.core_ApplicationUserDataTable
> ta.ClearBeforeFill = True
> ta.FillByUSerID(dt, User_ID)
> ds.Merge(dt)
> Return ds
> End Function
> <Query>
> <Method Namespace="http://tempuri.org/" Name="GetUserID"/>
> <SoapAction>http://tempuri.org/GetUSerID
> </SoapAction>
> <Parameters>
> <Parameter Name="User_ID">
> <Value>User_ID</Value>
> </Parameter>
> </Parameters>
> <ElementPath IgnoreNamespaces="true">
> GetUSerIDResponse{}/
> GetUSerIDResult/diffgram{}/DataSet2/core_ApplicationUser
> </ElementPath>
> </Query>
> Please help me out|||Tks buddy
Cheers
Aruj

Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

Error in use of order by in over clause

Hi,

I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,

Emil

I quote from Books Online the complete sytax related to the 3 key words :RANK, OVER and COUNT :

"

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

"

so, the second is the correct SELECT

and

"

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

"

so using RANK is the right decision because you count STD_CLL_CNTR_KEYSTD_CLL_CNTR_KEY that i guess is a primary key

|||Thanks Gigi

Error in Stored Procedure while working with temp. table

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :

String or binary data would be truncated.The statement has been terminated.

The procedure i created is as :

ALTER PROCEDUREfetchpersondetails

AS

CREATE Table#tempperson (personIDint,FirstNamenvarchar(200),LastNamenvarchar(250),titlenvarchar(150),Professionnvarchar(200),StreetAddressnvarchar(300),

StateAddressnvarchar(200),CityAddressnvarchar(200),CountryAddressnvarchar(200),ZipAddressnvarchar(200),Telephonenvarchar(200),Mobilenvarchar(200),

Faxnvarchar(200),Emailnvarchar(250),NotesPubntext,Affiliationnvarchar(200),Categorynvarchar(200))

Insert into#tempperson

SELECTdbo.tblperson.personID,ISNULL(dbo.tblperson.fName, N'') +' '+ISNULL(dbo.tblperson.mName, N'')ASFirstName, dbo.tblperson.lnameASLastName,

dbo.tblperson.honorASTitle, dbo.tblperson.titleASProfession, dbo.tblperson.street +' '+ISNULL(dbo.tblperson.suite, N'')ASStreetAddress,

dbo.tblperson.cityAScityaddress, dbo.tblperson.stateASstateaddress, dbo.tblperson.postalCodeASzipaddress,

dbo.tblperson.Phone1 +','+ISNULL(dbo.tblperson.Phone2, N'') +','+ISNULL(dbo.tblperson.Phone3, N'')ASTelephone,

dbo.tblperson.mobilePhoneASmobile, dbo.tblperson.officeFax +','+ISNULL(dbo.tblperson.altOfficeFax, N'') +','+ISNULL(dbo.tblperson.altOfficeFax2,

N'')ASFax,ISNULL(dbo.tblperson.Email1, N'') +','+ISNULL(dbo.tblperson.Email2, N'') +','+ISNULL(dbo.tblperson.Email3, N'')ASEmail,

dbo.tblperson.notesASNotesPub, dbo.tblOrganizations.orgNameASAffiliation, dbo.tblOrganizations.orgCategoryASCategory,

dbo.tblCountry.countryNameFullAScountryaddress

FROMdbo.tblpersonINNER JOIN

dbo.tblOrganizationsONdbo.tblperson.orgID = dbo.tblOrganizations.orgIDINNER JOIN

dbo.tblCountryONdbo.tblperson.countryCode = dbo.tblCountry.ISOCode

please let me know the solurion of this error.

What the error msg means is that you are trying to insert a value that is larger than what the column can take. Check your column lengths in the temp table and the data coming in from your SELECT statment. Apparently, you have crossed the limit somewhere.

|||

I made it working.Its not the column length that creating problem.

Error In SQL Statement

Hi, I m Trying TO use A sql insert Query but it showing an error

i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field

my code for insert query is

Dim q1As OleDb.OleDbCommand =New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)

and Error is Shown by browser is as follows

Server Error in '/WebApplication1' Application.

---

Missing semicolon (;) at end of SQL statement.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.

Source Error:

Line 466: con.Open()Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)Line 468: q1.ExecuteNonQuery()Line 469: con.Close()Line 470: End Sub

Source File:C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb Line:468

Stack Trace:

[OleDbException (0x80040e14): Missing semicolon (;) at end of SQL statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 WebApplication1._2.Button2_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb:468 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

I have to ask this, even though it seems so simple. Since the error says the query is missing a semi-colon at the end of the SQL statement, did you add a semi-colon and test it?

Jeff

|||

Is this SQL Server or Access?

In any case, your syntax is incorrect. Using a WHERE clause on an INSERT statement is invalid.

Also, use Parameters instead of concatenating UI-supplied text to SQL statements which will be executed.

|||

Try modify you q1 definition.

does you pnr contain number and you would like to subtract 1 from it?

you have to cast you pnr. to integer next subtract 1 and next convert result to string and insert it into query or do it this way:

Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1=(" & pnr.Text & "-1) ",

Thanks

Error in SQL Query Analyzer when running script in ISQL

I am using SQL 2000 Named Instance with SP3a on a Windows 2000 Server OS
When running this script below I get the following error:
declare @.answer as int
declare @.load_id as int
declare @.dos_command as varchar(500)
set @.load_id = 1
exec @.answer = cp_is_file_ready @.load_id
if (@.answer = 1)
begin
set @.dos_command =
'isql -Sserver -sa -Ppassword -database -Q"exec cp_import_data ' +
convert(varchar(10), @.load_id) + '"'
exec master..xp_cmdshell @.dos_command
end
ERROR MESSAGE:
Msg 18456, Level 14, State 1:
Login failed for user 'sa'.
DB-Library: Login incorrect.
NULL
I checked the sa password and it works for registering the SQL Instance via
the SQL Client and works when connecting to SQL Query Analyzer with it. But
for some reason it blows up when using ISQL.
I am baffled at this point.
Any help is greatly appreciated.Hi,
Replace -U (user) and -P (password) with -E option in ISQL .
I feel the password you enter for the instance is wrong.
Thanks
Hari
MCDBA
"GI" <glenn.illig@.healthtrio.com> wrote in message
news:ej6JV6s1DHA.1752@.tk2msftngp13.phx.gbl...

quote:

---

> I am using SQL 2000 Named Instance with SP3a on a Windows 2000 Server OS
> When running this script below I get the following error:
> declare @.answer as int
> declare @.load_id as int
> declare @.dos_command as varchar(500)
>
> set @.load_id = 1
>
> exec @.answer = cp_is_file_ready @.load_id
> if (@.answer = 1)
> begin
>
> set @.dos_command =
> 'isql -Sserver -sa -Ppassword -database -Q"exec cp_import_data ' +
> convert(varchar(10), @.load_id) + '"'
> exec master..xp_cmdshell @.dos_command
> end
>
> ERROR MESSAGE:
> Msg 18456, Level 14, State 1:
> Login failed for user 'sa'.
> DB-Library: Login incorrect.
> NULL
>
> I checked the sa password and it works for registering the SQL Instance

---

via

quote:

---

> the SQL Client and works when connecting to SQL Query Analyzer with it.

---

But

quote:

---

> for some reason it blows up when using ISQL.
> I am baffled at this point.
> Any help is greatly appreciated.
>
>
>

---

|||GI (glenn.illig@.healthtrio.com) writes:

quote:

---

> I am using SQL 2000 Named Instance with SP3a on a Windows 2000 Server OS
> When running this script below I get the following error:
> declare @.answer as int
> declare @.load_id as int
> declare @.dos_command as varchar(500)
> set @.load_id = 1
> exec @.answer = cp_is_file_ready @.load_id
> if (@.answer = 1)
> begin
> set @.dos_command =
> 'isql -Sserver -sa -Ppassword -database -Q"exec cp_import_data ' +
> convert(varchar(10), @.load_id) + '"'
> exec master..xp_cmdshell @.dos_command
> end

---

If the instance name is SERVER\INSTANCE, does your command line
actually say -S SERVER\INSTANCE? If you only say -S SERVER, you are
trying to connect to the default instance which may have a
different password.
Rather than hardcoding the server name, it may be better to use the
global variable @.@.servername.
Another possible cause is that the password includes non-ASCII characters.
The will be converted to the OEM charset - or a fallback if not available
in the OEM charset. In any case, using -E is much better, since you don't
have to hardcode the password.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Error in SQL Query

Hi,

I am trying this query
rsOFF.Open "SELECT SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0)) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

but is is giving me this error:
Wrong number of argument used with function in query expression SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0))

I could not find error.

can anyone help me.Not sure what is causing the error, but I doubt that you need the ISNULL functions anyway. If either value is Null then the product will be null and won't roll up into the SUM anyway. Aggregate functions ignore NULLs, so they are mathematically equivalent to zeros for summation.

Try simplifying your query statement to this, and see if it does not solve the problem:

rsOFF.Open "SELECT SUM(OFF_QUANT*OFF_PRICE) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

Error in query; "Invalid length parameter passed to the substring function"

Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254

SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday) AND
(__CustomerMachines002.folder_id = @.folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@.%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESCGenerally, you get this error when you pass in a bad parameter, like a negative startpoint, or a length parameter that takes you out of bounds of the string. Maybe instead of convert (varchar), you should try convert(varchar(10)). I don't see substring in there.|||naa cant get it to work... that damn error pop up no matter what i do...|||is i reverse the order and put in a AND insteed it works... like this
[AND
(CONVERT (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@.%')] AND (varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday)]

how come o got that error the other way?

//Mr|||Read the sticky at the top of this forum..Post us the DDL and some sample data and what the expected results are suppose to be...|||How about this?

DECLARE @.date1 datetime, @.date2 datetime
SELECT @.date1 = CONVERT(datetime, @.fyear + @.fmonth + @.fday)
, @.date2 = CONVERT(datetime, @.tyear + @.tmonth + @.tday)

SELECT TOP 10 a.VLF_VirusName
, COUNT(a.VLF_VirusName) AS number
FROM tb_AVVirusLog a
INNER JOIN __CustomerMachines002 b
ON a.CLF_ComputerName = b.FalseName
WHERE ( a.CLF_LogGenerationTime > @.Date1 AND a.CLF_LogGenerationTime <= @.Date2
AND b.folder_id = @.folderId)
OR ( a.CLF_LogGenerationTime > @.Date1 AND a.CLF_LogGenerationTime <= @.Date2
AND a.VLF_InfectionDestination LIKE N'%@.%')
GROUP BY a.VLF_VirusName
HAVING a.VLF_VirusName NOT LIKE N'cookie'
ORDER BY COUNT(a.VLF_VirusName) DESC|||thx, i manage this to work, and i got some new ideas how to do things... :)

//Mr|||thx, i manage this to work, and i got some new ideas how to do things... :)

//Mr

...and you're not gonna share it with us...I'll never gety thos 5 minutes back...

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution plan
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
Dan D.
I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution plan
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,
|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>
|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution plan
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
--
Dan D.I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution plan
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:
> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >One of our jobs stopped working. It gets to a certain update statement and
> >never finishes. We've tried new linked servers. I looked at an execution plan
> >yesterday and then today I added another linked server to test with and got
> >this error when I tried to look at the execution plan - "OLE DB provider
> >'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
> >'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
> >[Non-interface error: OLE/DB provider returned an invalid bookmark ordinal
> >from the index rowset.]."
> >
> >And now when I go back to using the original linked server I get the same
> >error.
> >
> >Any ideas?
> >
> >Thanks,
>

error in query execution plan when using linked server

One of our jobs stopped working. It gets to a certain update statement and
never finishes. We've tried new linked servers. I looked at an execution pla
n
yesterday and then today I added another linked server to test with and got
this error when I tried to look at the execution plan - "OLE DB provider
'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
[Non-interface error: OLE/DB provider returned an invalid bookmark ordi
nal
from the index rowset.]."
And now when I go back to using the original linked server I get the same
error.
Any ideas?
Thanks,
--
Dan D.I guess the issue as to why you get the error now on the
original linked server is answered in your post on settings.
In terms of the specific error you are getting, it can
depend on what provider you are using and what options you
have selected for the provider. I've seen the error when
using Index As Access path for the provider option. You
typically use the option when the data source is on the
same server as SQL Server. You can find more information on
this option in books online under the topic:
Keyset-Driven Cursors Requirements for OLE DB Providers
Try removing the option to see if that specific error goes
away.
In terms of troubleshooting why the job stopped working, you
can turn on a trace flag to try to get additional error
information. Execute:
dbcc traceon(7300,3604)
and then run the query used by the job in Query Analyzer.
You could also use profiler and capture the OLEDB Errors
event.
-Sue
On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>One of our jobs stopped working. It gets to a certain update statement and
>never finishes. We've tried new linked servers. I looked at an execution pl
an
>yesterday and then today I added another linked server to test with and got
>this error when I tried to look at the execution plan - "OLE DB provider
>'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index
>'IX_PESTACCT_3' with incorrect bookmark ordinal 0. OLE DB error trace
>[Non-interface error: OLE/DB provider returned an invalid bookmark ord
inal
>from the index rowset.]."
>And now when I go back to using the original linked server I get the same
>error.
>Any ideas?
>Thanks,|||Yes that's the option. When I stopped using it the error went away.
I'll try the traceon and profiler.
Thanks Sue,
Dan D.
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Hi,
I am developing an ole db provider and am getting the same error. It occurs
right after sql server reads the INDEXES schema rowset. I have set the
"Index as access path option" on and need to make it work with this option
on.
I have found that if I change the schema rowset to indicate that it is an
integrated index, the error goes away. Also, it seems odd to me that the
message is generated before the IOpenRowset call to open the index rowset
even occurs. There must be something in the schema rowset that sql server
uses to produce this error message.
What changes do I need to make to the index schema entries to prevent this
error ?
Thanks,
Bill Emerson
"Sue Hoegemeier" wrote:

> I guess the issue as to why you get the error now on the
> original linked server is answered in your post on settings.
> In terms of the specific error you are getting, it can
> depend on what provider you are using and what options you
> have selected for the provider. I've seen the error when
> using Index As Access path for the provider option. You
> typically use the option when the data source is on the
> same server as SQL Server. You can find more information on
> this option in books online under the topic:
> Keyset-Driven Cursors Requirements for OLE DB Providers
> Try removing the option to see if that specific error goes
> away.
> In terms of troubleshooting why the job stopped working, you
> can turn on a trace flag to try to get additional error
> information. Execute:
> dbcc traceon(7300,3604)
> and then run the query used by the job in Query Analyzer.
> You could also use profiler and capture the OLEDB Errors
> event.
> -Sue
> On Tue, 18 Jan 2005 07:53:15 -0800, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>

Error in Query

The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc,
laptop, or printer). It is assumed that model numbers are unique for
all the makers and product types. For each model number specifying pc
in the relation "PC", its listed speed (of the processor in MGz), total
RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
and the price. The relation "Laptop" is similar to that one of PCs
except for the CD ROM speed which is replaced by screen size (in
inches). For each printer model in the relation "Printer" it is pointed
whether the printer is color or not (color attribute is 'y' for color
printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
and the price.

I need to write a query for Find printer makers.
Result set: maker.

My Query is

select distinct product.maker from product inner join printer on
product.model = printer.model

I get the message
Your query produced correct result set on main database, but it failed
test on second, checking database.
* Wrong number of records (less by 1)

This question is an SQL exercise in URL http://www.sql-ex.ru/

Can anyobe explain me "correct result set on main database," why "but
it failed test on second, checking database."
* Wrong number of records (less by 1)

Thank you very much,

MiksHi Miks

> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)

My guess (emphasis on "guess") is that it has something to do with the fact
that you don't need a second table for this query. The writers of this
website, must be doing some sort of parsing of your query, this is not a
standard SQL Server error message.

This query is sufficient

select distinct maker from Product where type = 'printer'

--
-Dick Christoph
"Miks" <akmeera2k4@.gmail.com> wrote in message
news:1142418307.696828.171550@.j33g2000cwa.googlegr oups.com...
> The database scheme consists of four relations:
> Product(maker, model, type)
> PC(code, model, speed, ram, hd, cd, price)
> Laptop(code, model, speed, ram, hd, screen, price)
> Printer(code, model, color, type, price)
> The relation "Product" shows the maker, model number, and type (pc,
> laptop, or printer). It is assumed that model numbers are unique for
> all the makers and product types. For each model number specifying pc
> in the relation "PC", its listed speed (of the processor in MGz), total
> RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
> and the price. The relation "Laptop" is similar to that one of PCs
> except for the CD ROM speed which is replaced by screen size (in
> inches). For each printer model in the relation "Printer" it is pointed
> whether the printer is color or not (color attribute is 'y' for color
> printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
> and the price.
> I need to write a query for Find printer makers.
> Result set: maker.
> My Query is
> select distinct product.maker from product inner join printer on
> product.model = printer.model
>
> I get the message
> Your query produced correct result set on main database, but it failed
> test on second, checking database.
> * Wrong number of records (less by 1)
> This question is an SQL exercise in URL http://www.sql-ex.ru/
> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)
> Thank you very much,
> Miks|||homework?|||any place where you can find the answer of these exercies?

im stuck at exercse 10:

Exercise: 10
Find the printers having the highest price.
Result set: model, price.

my query:

select model, max(price)price from printer

http://www.sql-ex.ru/exercises.php#answer_ref

any place for the answers?|||Hi Daniel

Here is one answer (One SQL Query that works)

select Model, Price
from Printer
where price = (select max(price) from Printer)

--
-Dick Christoph
"Daniel" <dtukkers@.gmail.com> wrote in message
news:1143709387.198548.201780@.z34g2000cwc.googlegr oups.com...
> any place where you can find the answer of these exercies?
> im stuck at exercse 10:
> Exercise: 10
> Find the printers having the highest price.
> Result set: model, price.
> my query:
> select model, max(price)price from printer
> http://www.sql-ex.ru/exercises.php#answer_ref
> any place for the answers?

Error in Prediction query

Hi,

I have the following association model Structure

Where I am trying to find out the associations between various service activities so that when a customer buys a service activity we can recommend him/her others

CaseTable

CustomerId à Key

IncidentId

Nested Table

ServiceId à Key

ServiceName à Input, Predict

IncidentId à Link to the case table

Firstly is the above structure correct?

Secondly

I have the following prediction query

SELECT

t.[ServiceId],

Predict ([ServicerossSell].[ServiceCross-SellRecommend],3)

From

[ServicerossSell]

PREDICTIONJOIN

SHAPE

{OPENQUERY([Adventure Works Cycle MSCRM],

'SELECT DISTINCT ServiceId FROM Service ORDER BY ServiceId]')

}

APPEND

( {OPENQUERY([Adventure Works Cycle MSCRM],

'SELECT DISTINCT ServiceId FROM Service ORDER BY ServiceId]')

}

RELATE [ServiceId]To [ServiceId]

)

AS [Service]

AS t

ON

[ServicerossSell].[ ServiceCross-SellRecommend].[ServiceId] = t.[Service].[ServiceId]

It throws the following error

“The DMX column was not found in the context”

I am not able to figure out why, any help would be most appretaiated

The SHAPE clause is wrong - it should be joining the customer/incident table with the incident/service table, not doing a self-join.

Given that your case is a customer with the nested table being the services purchased by them (across multiple incidents), I recommend simplifying the input data as well as the modeling by using a named query in the DSV as follows:

SELECT a.CustomerId, b.ServiceName
FROM CustomerIncident a INNER JOIN IncidentService b
ON a.IncidentId = b.IncidentId
ORDER BY CustomerId

The mining structure can then look like this:

CREATE MINING MODEL CustomerServiceAssociations

(

CustomerId TEXT KEY,

CustomerServices TABLE PREDICT

(

ServiceName TEXT KEY

)

)

USING Microsoft_Association_Rules

|||

Hi Raman,

I took your suggestion and modified the dsv and the mining model as mentioned by you.

And I also modified the prediction query as follows.

SELECT

t.[ServiceId],

PredictAssociation([Association].[Services],3)

From

[Association]

PREDICTIONJOIN

OPENQUERY([Adventure Works Cycle MSCRM],

'SELECT DISTINCT

[ProductId]

FROM

(SELECT ServiceId FROM ServiceBase) as [Service]

')AS t

ON

[Association].[Service Id] = t.[ServiceId]

I am facing a new problem now.

The model is predicting the same set of services for every case. Even changes in the algorithm parameter value do not have any impact on the result.

What is the reason for this and how can u rectify it?

I am facing this problem with other association based mining models also.

Error in Prediction query

Hi,

I have the following

association model Structure

Where I am trying to find out

the associations between various service activities so that when a customer

buys a service activity we can recommend him/her others

CaseTable

CustomerId à Key

IncidentId

Nested Table

ServiceId à Key

ServiceName à Input, Predict

IncidentId à Link to the case table

Firstly is the above

structure correct?

Secondly

I have the following prediction

query

SELECT

t.[ServiceId],

Predict

([ServicerossSell].[ServiceCross-SellRecommend],3)

From

[ServicerossSell]

PREDICTION JOIN

SHAPE

{OPENQUERY([Adventure

Works Cycle MSCRM],

'SELECT DISTINCT ServiceId

FROM Service ORDER BY ServiceId]')

}

APPEND

( {OPENQUERY([Adventure Works Cycle MSCRM],

'SELECT DISTINCT ServiceId

FROM Service ORDER BY ServiceId]')

}

RELATE

[ServiceId] To [ServiceId]

)

AS

[Service]

AS t

ON

[ServicerossSell].[ ServiceCross-SellRecommend].[ServiceId]

= t.[Service].[ServiceId]

It throws the following error

“The DMX column was not found in the context”

I am not able to figure out why, any help would be most

appretaiated

The SHAPE clause is wrong - it should be joining the customer/incident table with the incident/service table, not doing a self-join.

Given that your case is a customer with the nested table being the services purchased by them (across multiple incidents), I recommend simplifying the input data as well as the modeling by using a named query in the DSV as follows:

SELECT a.CustomerId, b.ServiceName
FROM CustomerIncident a INNER JOIN IncidentService b
ON a.IncidentId = b.IncidentId
ORDER BY CustomerId

The mining structure can then look like this:

CREATE MINING MODEL CustomerServiceAssociations

(

CustomerId TEXT KEY,

CustomerServices TABLE PREDICT

(

ServiceName TEXT KEY

)

)

USING Microsoft_Association_Rules

|||

Hi Raman,

I took your suggestion and modified the dsv and the mining

model as mentioned by you.

And I also modified the prediction query as follows.

SELECT

t.[ServiceId],

PredictAssociation([Association].[Services],3)

From

[Association]

PREDICTION JOIN

OPENQUERY([Adventure

Works Cycle MSCRM],

'SELECT DISTINCT

[ProductId]

FROM

(SELECT ServiceId FROM ServiceBase)

as [Service]

') AS

t

ON

[Association].[Service

Id] = t.[ServiceId]

I am facing a new problem now.

The model is predicting the same set of services for every case.

Even changes in the algorithm parameter value do not have any impact on the

result.

What is the reason for this and how can u rectify it?

I am facing this problem with other association based mining

models also.

Error in paramterized query - Execute SQL Task

Hi,

I am having some difficulties with a Execute SQL Task, I'll try to describe:

The task contains 2 queries:

UPDATE config SET last_timestamp_int=this_timestamp_int, this_timestamp_int=CAST(GETDATE() AS INT) WHERE company_id=?

SELECT last_timestamp_int AS last_timestamp_int, this_timestamp_int AS this_timestamp_int FROM config WHERE company_id=?

The ? reference to a variable set in Parameter Mapping, which has a initial string value set. Direction set to "Input", Datatype set to "varchar", and parametername to "0". The connectiontype is OLE DB. I have tried to set BypassPrepare to true, but that doesnt help.

The second query fetch 2 values which is stored in the task's Result Set, in two variables. Resultset is set to single row.

When I press Parse Query, I get an error:

"The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

When I try to run the package, I get this error:

SSIS package "Package.dtsx" starting.

Error: 0xC002F210 at Store last timestamp in variable, Execute SQL Task: Executing the query "UPDATE config SET last_timestamp_int=this_timestamp_int, this_timestamp_int=CAST(GETDATE() AS INT) WHERE company_id=?

SELECT last_timestamp_int AS last_timestamp_int, this_timestamp_int AS this_timestamp_int FROM config WHERE company_id=?" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Store last timestamp in variable

Warning: 0x80019002 at Define global variables: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

Can someone please help me identify, what it is I am doing wrong?

Thanks in advance

Your setup looks correct to me, so I have two suggestions for troubleshooting this.

1. Break up the task into two separate tasks, one for the update and a second for the select.

2. Run profiler to see the value and result of your queries when they run.

I don't think there's any issue with the parsing. My understanding is that the parser doesn't know what to do with the ? variable so it always returns an error even if it's a valid working query.

|||Use two parameters, one for the UPDATE and one for the SELECT (even though the values of the parameters are the same)

Error in MDX queries

Dear all Please Help

When I am executing the following queries in MDX the error will come

'Warning 2 Query (15, 1) Parser: The syntax for 'FORMAT_STRING' is incorrect. [Line:15; Column:1] 0 0 '

Query as follows...

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage of Profit]

AS ([Measures].[Profit],[Sales Analysis].[Group Description].CurrentMember)/([Measures].[Profit],[Sales Analysis].[Group Description].[All]),

FORMAT_STRING = "Percent",

NON_EMPTY_BEHAVIOR = { [Quantity] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Parent Percent]

AS iif(IsEmpty(([Measures].[Profit],[Sales Analysis].[Area Description].Parent)),1,

([Measures].[Profit])/([Measures].[Profit],[Sales Analysis].[Area Description].Parent),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

hello,

it looks like your second calculated member (i.e. Parent Percent) is missing the closing ')' for the iif function.

hope this helps,