error in WHERE statement

dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...

I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.

what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...

regards,
kanishkit's because this sql is perfectly valid in terms of syntax:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

The only reason it fails is because there is no column named smallint in that table. That's not a syntax error.

Now this query, on the other hand, works, because both columns exist, and has exactly the same form as yours:

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = LOCAL_TYPE_NAME

is that what you are asking?|||not actually, i was saying that i was looking for a value in the column TYPE_NAME named smallint

I was giving the WHERE clause to look for a value called smallint in the column TYPE_NAME. the column has many vaules in it...smallint is one of them...

maybe i should have written it as:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

Since i am new to SQL language....what were you refering to...??...Although i quite got my answer...

Can you please tell me...what is the intersection of the column and row called??....for example in ms Excel when the row 7 and column D meet then a cell is formed...called D7

But in SQl server...what is the intersection of the Column and Row called...?? Like the cell in ms Excel...??

regards,
kanishk|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?|||the intersection of row and column is called "the value of column Y for row X"

:cool:

i realize that sounds somewhat flippant, but relational database theory is based upon primary keys, so "row X" means "the row where the primary key value is X" since that's how you tell rows apart, and "column Y" means "the column with 'Y' as the column name" since that's how you tell columns apart

simple, innit ;)|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?

thanks, i was wondering that when i give the command that you gave up...viz,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

when i run it, then automatically the TYPE_NAME column takes brackets,,,why is that??...i have shown it below...

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

regards,
kanishk|||That is because you are running this query in the SQL section of enterprise manager. Enterprise manager will try to bracket all where conditions
so if your query is something like

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint' OR TYPE_NAME = 'int'

Then on running it you will get

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint') OR (TYPE_NAME = 'int')

Try running the query in SQL Analyzer and you will not get the brackets..... however i guess the brackets are purely for readability and in the end finally makes no difference.....|||dear sir, i have started using the SQL query Analyzer, i have another question,
i just put in the LIKE condition...

SELECT * FROM spt_datatype_info WHERE TYPE_NAME LIKE '%s'

now this will give all the values in the TYPE_NAME column which start with any characters but end with 's' , thats why i have put '%s' , now suppose i want all the values that have 2 letters as starting and the 3rd letter as 's' , so what do symbol do i use??

Like in windows when we used to give in command prompt the ! , exclamation mark for the characters we did not know but were sure of the count of them, and the star '*' for the characters we did not know and were not sure of their count..

Similarly in SQL what would we give for the LIKE condition..??

regards,
kanishk|||... LIKE '__s%'this is all explained nicely in the manual

ERROR in Where Cluase

Hello EveryBody,

I have a little problem in Sql Statement .. When I am adding a Where condtion to my sql statement ..I got an error shown in the pic blew :

I am waiting for your solution

Best regards,

It means that you have an error in your WHERE clause.

What is the complete SELECT statement?

Don

|||

Good eveningdonkiely,

This is my SQL Statement:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID= @.CategoryID

and this is the erro shown in pic :-

Thank you for your concern

|||

Ah. I'm glad you sent the image. There cannot be a space between the @. and the CategoryID on the right side of the = sign.

Don

|||

Unfortunately, I was trying with no space ..but the same error happen >>

Shown in pic blew:-

Thanks Don

|||

ban:

This is my SQL Statement:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID= @.CategoryID

When I copy and paste that into Query Analyzer and run it against the Northwind database I receive no error. Are you sure that is your exact SQL statement?

|||

Dear tmorton,

yes , I am .It is the exact SQL Statemet .

but I am thinking maybe there is missing file on my program or somthing wrong with my keyboard.

I do not konw ,but this is not the first time that error happen to me ,every time I use WHERE Cluase .

I hop to find the solution for it .

anyway thanks to trying help me

|||

Just a silly question, and don't kill me if it sounds really stupid to ask this please!

You are using SQL Server with the default sql server provider, yes?

|||

shados

OoOoOOOOOh God I am just a beginner programmer in ASP.NET ... I am so confuse

I did not get it your Q , but I am using a Microsoft Visual Studio .Net 2005 (Full Edition)

This version include Microsoft SQL Server 2005

Note : I can run SQL Statement with this program but I can not use WHERE Clause when it is taking a parameter

I appreciateyour help

|||

Okie, that answer my question, you are. All good then, I really don't get what the issue is. I was asking, because different databases have issues with SQL statements like the one you're writting (with parameters in the where clause), so I thought... But thats not the problem, so back to the drawining board >.<

|||

shados,

so back to the drawining board

What do you mean by that ?

|||I just meant that my idea was wrong :)|||

Hellow again,

I find the solution for my problem>>>shown in pic blew :-

Not: I am using Microsoft Acess DataBase.

Speical Thanks to >>donkiely ,tmorton,shados

for more info:-

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=874535&SiteID=1&mode=1

Have a nice day!

|||

ban:

I find the solution for my problem

That's great!!

ban:

Not: I am using Microsoft Acess DataBase.

This is the SQL Server forum. Next time please post in the Access forum. You would have had an answer days ago

|||

Gah, thats why I asked if you were using SQL Server :) In databases like MS Access, PervasiveSQL, a lot of databases accessed through ODBC, etc, you have to use the ? placeholder instead of @.variablename...

Indeed, since this is a SQL Server forum, we all assumed you were using SQL Server, hahahaha. Ahh well, glad you found your problem.

sql

error in sql statement using if statement

can anyone one help me with this statement.

select if tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

im quite new to sql and cant figure out the problem

can anyone help
colin

Colin:

See if this works better:

if ( select tdy.classyear
from tei_degree td
left join tei_degreeyears tdy
on td.degree_id = tdy.degree_id
) = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

|||I'm still getting an error with that statement.

the error says incorrect syntax near then and else statement.

i've check all the tables names and field names and there fine.

anyo other suggestions
|||

Remove the word 'then' and you should be fine.

Chris

|||cheers that worked
|||

hi!

You cannot use if inside a select statement.... Use CASE and it will work for sure....

select CASE tdy.classyear

WHEN tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end

ELSE
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end
END
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

try this and let me know ur comments...

Thanks

sql

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 open statement

Hi,
I am new to sql server. I am trying to open a asp page by connecting to a
sql server database. The sql statement is coming correct. However I am
getting the following error:
ADODB.Connection (0x800A0E79)
Operation is not allowed when the object is open.
The error is happening to the following line of the code
conn.Open strSQL
Any help is appreciated here. Thanks.
CODE:
<!-- #include file="adovbs.inc" -->
<%
set conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcde;Initial
Catalog=manpowerweb;Data Source = DOJHLNBCC-003;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096"
Conn.Open sConnString
Set cmd.ActiveConnection = Conn
dim strSQL
UserID = request.Form("UserID")
Passwd = request.Form("password")
FirstName = request.Form("FirstName")
LastName = request.Form("LastName")
AgencyName = request.Form("AgencyName")
EmailAddress = request.Form("emailAddress")
PhoneNumber = Request.Form("PhoneNumber")
strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
" " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
strSQL = "INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
ApplicantFirstName, ApplicantLastName, "
strSQL = strSQL & "ApplicantEmailAddress, ApplicantAgencyText,
ApplicantPhoneNumber, "
strSQL = strSQL & "ApplicantSysCreateDate, ApplicantSysModDate) "
strSQL = strSQL & "VALUES ('" & UserID & "', '" & passwd & "', '"
strSQL = strSQL & FirstName & "', '" & LastName & "', '" & EmailAddress &
"', '"
strSQL = strSQL & AgencyName & "', '" & PhoneNumber & "', '"
strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
response.write strSQL & vbCRLF
'Response.end
Response.Write "This is before the execution of the sql statement" & "<br>"
conn.Open strSQL
Response.Write "This is after the execution of the sql statement" & "<br>"
%>> conn.Open strSQL
The Open method of an ADO Connection object expects a connection string, not
a SQL statement. You can use the Execute method to execute a SQL statement:
conn.Open strConnectionString
conn.Execute strSQL
conn.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B901C4EE-D0CF-4C75-8CA8-823DE4BFF517@.microsoft.com...
> Hi,
> I am new to sql server. I am trying to open a asp page by connecting to a
> sql server database. The sql statement is coming correct. However I am
> getting the following error:
> ADODB.Connection (0x800A0E79)
> Operation is not allowed when the object is open.
> The error is happening to the following line of the code
> conn.Open strSQL
> Any help is appreciated here. Thanks.
> CODE:
> <!-- #include file="adovbs.inc" -->
> <%
> set conn = Server.CreateObject("ADODB.Connection")
> set cmd = Server.CreateObject("ADODB.Command")
>
> sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcde;Initial
> Catalog=manpowerweb;Data Source = DOJHLNBCC-003;Use Procedure for
> Prepare=1;Auto Translate=True;Packet Size=4096"
>
> Conn.Open sConnString
> Set cmd.ActiveConnection = Conn
> dim strSQL
>
> UserID = request.Form("UserID")
> Passwd = request.Form("password")
> FirstName = request.Form("FirstName")
> LastName = request.Form("LastName")
> AgencyName = request.Form("AgencyName")
> EmailAddress = request.Form("emailAddress")
> PhoneNumber = Request.Form("PhoneNumber")
>
>
> strcurrentdate = "'" & year(date) & "/" & month(date) & "/" & day(date) &
> " " & hour(Now()) & ":" & minute(Now()) & ":" & second(Now()) & "'"
> strSQL = "INSERT INTO tblApplicant (ApplicantUserID, ApplicantPassword,
> ApplicantFirstName, ApplicantLastName, "
> strSQL = strSQL & "ApplicantEmailAddress, ApplicantAgencyText,
> ApplicantPhoneNumber, "
> strSQL = strSQL & "ApplicantSysCreateDate, ApplicantSysModDate) "
> strSQL = strSQL & "VALUES ('" & UserID & "', '" & passwd & "', '"
> strSQL = strSQL & FirstName & "', '" & LastName & "', '" & EmailAddress &
> "', '"
> strSQL = strSQL & AgencyName & "', '" & PhoneNumber & "', '"
> strSQL = strSQL & strcurrentdate & ", " & strcurrentdate & ")"
> response.write strSQL & vbCRLF
> 'Response.end
> Response.Write "This is before the execution of the sql statement" &
> "<br>"
> conn.Open strSQL
> Response.Write "This is after the execution of the sql statement" & "<br>"
> %>
>

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 INSERT INTO

Hi,
I'm sure that this is quite a simple program. I am generating an INSERT INTO statement in the Query builder (!) of Visual Basic .NET, and I consistantly get the following error: "Missing semi-colon at the end of the end of the SQL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.
It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.

Error in INSERT INTO

Hi,
I'm sure that this is quite a simple program. I am generating an INSERT INTO
statement in the Query builder (!) of Visual Basic .NET, and I consistantly
get the following error: "Missing semi-colon at the end of the end of the S
QL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles
, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.

error in in nested try catch-

HI,

getting error like this while using nested try catch.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

also it doesnt roll back because of error, instead of other procedures,insert ,update are executing ending with wrong creations......works partially.

if the try catch is removed form subprocedure1 it works perfectly.

below is the example exactly what i use with more exec procedures in main procedure.

main procedure

begin

begin try

begin transaction

exec subprocedure 1

insert.....

update

COMMIT TRANSACTION

END TRY

BEGIN CATCH

insert into spErrorLog(spName, params, errorMsg)

values('dbo.project_inspectionproject_save', @.newprojectnumber, @.@.error)

if @.@.error <> 0

begin

if @.@.trancount > 0 ROLLBACK TRANSACTION

end

END CATCH

end

sub procedure 1

Begin Try

insert into yy(a,c,c)values(a,b,c)

select @.@.identity

End Try

Begin Catch

IF (XACT_STATE())=-1 ROLLBACK TRANSACTION

insert into spErrorLog(spName, params, errorMsg)

values(@.spName, '', @.errorMsg)

select -1

RAISERROR(@.errorMsg, @.errSeverity, 1)

End Catch

please help me. struggling with for long time.

venp..

Perhaps your RAISERROR in the called proc is not a severity level high enough to force the error in the calling sproc, thereby when the attempted COMMIT finds no active TRANSACTION, you are getting the Transaction Count error message.

Try checking @.TRANCOUNT before the commit just like you do on the ROLLBACK -OR make sure that your RAISERROR is a high enough severity level (is it over 10?) to case the CATCH failure.

|||

HI,

if i remove the try catch from the main procedure sub procedure works fine always. right now i'm using

if @.@.error >o

rollback transaction

--

in my main procedure . i'm using the above st for every transaction st. I dont want to use this old one. Please help me with try catch.()

it doesnt produce any error right now.(just without try catch on main)

my problem is some other person is working on this sub procedure. I 've the main procedure. we both are in situation ro rollback the whole if something goes wrong.

venp

error in image field when using CASE statement

I've this Stored procedure on a SQLserver 2000 SP3:

SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?

This should work "always":

SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>
|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas

"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,

does anyone have the solution to the error:

The SelectCommand property has not been initialized before calling 'Fill'."

for Execute SQL Task problem? I am also stuck there...

Daren|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in execute sql task

I get the following error when trying to execute an sql statement in oracle and returning the results into an object variable with the execute sql task.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select <columnlist> from <tablename>" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It executes fine if I select no results or first row but I can't get full result set to work. The query and connection string are valid. Any ideas?

Hi,

Have you selected the "Full Result Set" in the ResultSet option? If so, then create a variable of datatype "Object" and link that variable in the ResultSet tab. In that, enter "0" in the Result Name and in the Variable Name, select the variable you created as Object.

Pls try this and let me know if you have any issues.

Sorry for the delayed response. I just gone thru this issue.

Prakash Srinivasan

|||I have the same issues with the Script task in the control flow. I have the result set to full and assigned to a datatype of object. I also have 0 for the name of the result set but it still give me the same error. " selectcommand was in initialized before Fill" It would be a paid to enter 300 column names in the script component in the data flow side just because this the Script task isnt work. I will be glad if someone could help me.|||

How are you doing this? The Script Task isn't really supposed to be used for extracting SQL data. Why does Execute SQL Task not work for you?

-Jamie

|||

I am pulling data through an ado.net connection connected to a DB/C 4 database (odbc database). I needed to pull data based on the last date run so i need a way to insert a date into my sql command where clause. I am using an expression to set the sqlcommand of the Script task to "select .....from tableA where timestamp > = " @.[datetime::mydate] ". I have the resultset set to Full result set. I also have my resultset variable as Object::rs_data and the name set to 0 for Full resultset. I still get the error so I am think its probably the fact that the script task does not work for ado.net odbc connnections.

|||

I am still left wondering why you are not doing this in an Execute SQL Task. And yet you say "I have the resultset set to Full result set." Are you really using a script task?

-Jamie

|||Sorry, i wasnt paying attention. I meant to say Execute SQL Task instead of Script task. With that said, is there any ideas for fixing the situation and once to assign a resultset to a variable. How do you use that resultset(variable) as datasource in a Data flow.|||Hi,
does anyone have the solution to the error:
The SelectCommand property has not been initialized before calling 'Fill'."
for Execute SQL Task problem? I am also stuck there...
Daren
|||

I'm facing the same problem while I'm extracting data from an "SQL Task" using a result set "full result set".

Also can someone tell me how to read the temp result set created by the SQL task ? The documentation found is very poor for a novice like me and doesn't explain how to read the System.Data.Dataset in order to feed a SQL server destination table.

Thanks

Error in cross tab statement

Hi,

I have a coding which state as below:

CREATE PROCEDURE [dbo].[crossTab]
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100)
AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE ' + @.pivot + ' Is Not Null')

SELECT @.sql='' , @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN ' + @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I just woud like why it come out error stated that

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

when i key in

exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

Please help me

ThanxYet another attempt at a universal cross-tab function...

Does your code error out if you comment out the "EXEC (@.select)" line? If not, then try replacing it with "SELECT @.select" or "PRINT @.select" to see exactly what code is being executed that is causing the error.