openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo
|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.

openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.

Open Table > can't add data into the new row of the grid

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

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

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Open Table > can't add data into the new row of the grid

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

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

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Only seeing data from last row in dataset

I have a master detail report that I'm having trouble
with, I have the report layed out and populating fields in
the master report with data, my report uses fields from the
master report and passes it as parameters to the subreport, that's all
working well, the problem is I only get data for the last
record in the master dataset... It's not displaying a new
page for each records in the master dataset... None of my
fields use the First function... My grouping in my query
is setup correctly, is there any other place to setup
grouping in the report designer...? What am I doing
wrong...? In the wizard it asks me how I would like to scope my fields
either Page, Group or Details, where can I access this functionality outside
the wizard, I created all of my reports without the wizard...I am not sure but it sounds like you need to put either a table or rectangle
onto the report that you can tie back to you dataset. If you just create a
report and put textboxes on the blank page you will only get the last row of
data.
"Alien2_51" wrote:
> I have a master detail report that I'm having trouble
> with, I have the report layed out and populating fields in
> the master report with data, my report uses fields from the
> master report and passes it as parameters to the subreport, that's all
> working well, the problem is I only get data for the last
> record in the master dataset... It's not displaying a new
> page for each records in the master dataset... None of my
> fields use the First function... My grouping in my query
> is setup correctly, is there any other place to setup
> grouping in the report designer...? What am I doing
> wrong...? In the wizard it asks me how I would like to scope my fields
> either Page, Group or Details, where can I access this functionality outside
> the wizard, I created all of my reports without the wizard...
>|||Hi John,
I'm pretty confident we're on to something here, I'm certain I'm just using
a report with text boxes on it, which is most definately why I'm seeing only
the last row of data. I put my data elements in a rectangle which seemed more
appropriate because I'm displaying header or master data in the "top" report,
so I want a new page for every row in the master dataset. I noticed in the
property pages for the rectangle that there is a drop down list thats labeled
Data region, the description reads: Type or select the data region with which
to repeat the rectangle on every page that the data region appears. My
question is this; Could you define the term "Data region" and how do I create
one in the designer. I typed in the field from the dataset but got an error.
In your post you had mentioned that I can tie it back to the dataset, what
did you mean by that...?
Thanks for your help!!!
Dan
"johnE" wrote:
> I am not sure but it sounds like you need to put either a table or rectangle
> onto the report that you can tie back to you dataset. If you just create a
> report and put textboxes on the blank page you will only get the last row of
> data.
> "Alien2_51" wrote:
> > I have a master detail report that I'm having trouble
> > with, I have the report layed out and populating fields in
> > the master report with data, my report uses fields from the
> > master report and passes it as parameters to the subreport, that's all
> > working well, the problem is I only get data for the last
> > record in the master dataset... It's not displaying a new
> > page for each records in the master dataset... None of my
> > fields use the First function... My grouping in my query
> > is setup correctly, is there any other place to setup
> > grouping in the report designer...? What am I doing
> > wrong...? In the wizard it asks me how I would like to scope my fields
> > either Page, Group or Details, where can I access this functionality outside
> > the wizard, I created all of my reports without the wizard...
> >|||Why do you have two datasets? As far as I know you cannot join two seperate
datasets in a report. I may be wrong. What might be easier is to join the
datsets in your query. When you do that you create a table and create a
group by the changing rows of the master data set. If that won't work then
use a subreport rather than two different datasets. the subreport will bring
in the detail. When you do it this way you put the rectangle on the report
for the master dataset. After you add the rectangle right click on it and
select properties. On the general tab there will be an option to tie it to a
data set. Then inside the main rectangle you add another one and add the
subreport to that. Let me know which way you decide to go and I can help you
with this.
"Alien2_51" wrote:
> Hi John,
> I'm pretty confident we're on to something here, I'm certain I'm just using
> a report with text boxes on it, which is most definately why I'm seeing only
> the last row of data. I put my data elements in a rectangle which seemed more
> appropriate because I'm displaying header or master data in the "top" report,
> so I want a new page for every row in the master dataset. I noticed in the
> property pages for the rectangle that there is a drop down list thats labeled
> Data region, the description reads: Type or select the data region with which
> to repeat the rectangle on every page that the data region appears. My
> question is this; Could you define the term "Data region" and how do I create
> one in the designer. I typed in the field from the dataset but got an error.
> In your post you had mentioned that I can tie it back to the dataset, what
> did you mean by that...?
> Thanks for your help!!!
> Dan
> "johnE" wrote:
> > I am not sure but it sounds like you need to put either a table or rectangle
> > onto the report that you can tie back to you dataset. If you just create a
> > report and put textboxes on the blank page you will only get the last row of
> > data.
> >
> > "Alien2_51" wrote:
> >
> > > I have a master detail report that I'm having trouble
> > > with, I have the report layed out and populating fields in
> > > the master report with data, my report uses fields from the
> > > master report and passes it as parameters to the subreport, that's all
> > > working well, the problem is I only get data for the last
> > > record in the master dataset... It's not displaying a new
> > > page for each records in the master dataset... None of my
> > > fields use the First function... My grouping in my query
> > > is setup correctly, is there any other place to setup
> > > grouping in the report designer...? What am I doing
> > > wrong...? In the wizard it asks me how I would like to scope my fields
> > > either Page, Group or Details, where can I access this functionality outside
> > > the wizard, I created all of my reports without the wizard...
> > >|||I don't have two datasets, I'm using a subreport, the subreports parameters
are tied to fields in my master dataset using the parameters collection in
the subreport, I don't see the option in the general tab to "tie it back to
the dataset" Are you talking about the "Data region"...? How do I create one
in the designer...? I typed in the field from the dataset I'm grouping on but
got an error. In your post you had mentioned that I can tie it back to the
dataset, what did you mean by that...?
I appreciate your help...
"johnE" wrote:
> Why do you have two datasets? As far as I know you cannot join two seperate
> datasets in a report. I may be wrong. What might be easier is to join the
> datsets in your query. When you do that you create a table and create a
> group by the changing rows of the master data set. If that won't work then
> use a subreport rather than two different datasets. the subreport will bring
> in the detail. When you do it this way you put the rectangle on the report
> for the master dataset. After you add the rectangle right click on it and
> select properties. On the general tab there will be an option to tie it to a
> data set. Then inside the main rectangle you add another one and add the
> subreport to that. Let me know which way you decide to go and I can help you
> with this.
> "Alien2_51" wrote:
> > Hi John,
> >
> > I'm pretty confident we're on to something here, I'm certain I'm just using
> > a report with text boxes on it, which is most definately why I'm seeing only
> > the last row of data. I put my data elements in a rectangle which seemed more
> > appropriate because I'm displaying header or master data in the "top" report,
> > so I want a new page for every row in the master dataset. I noticed in the
> > property pages for the rectangle that there is a drop down list thats labeled
> > Data region, the description reads: Type or select the data region with which
> > to repeat the rectangle on every page that the data region appears. My
> > question is this; Could you define the term "Data region" and how do I create
> > one in the designer. I typed in the field from the dataset but got an error.
> > In your post you had mentioned that I can tie it back to the dataset, what
> > did you mean by that...?
> >
> > Thanks for your help!!!
> >
> > Dan
> >
> > "johnE" wrote:
> >
> > > I am not sure but it sounds like you need to put either a table or rectangle
> > > onto the report that you can tie back to you dataset. If you just create a
> > > report and put textboxes on the blank page you will only get the last row of
> > > data.
> > >
> > > "Alien2_51" wrote:
> > >
> > > > I have a master detail report that I'm having trouble
> > > > with, I have the report layed out and populating fields in
> > > > the master report with data, my report uses fields from the
> > > > master report and passes it as parameters to the subreport, that's all
> > > > working well, the problem is I only get data for the last
> > > > record in the master dataset... It's not displaying a new
> > > > page for each records in the master dataset... None of my
> > > > fields use the First function... My grouping in my query
> > > > is setup correctly, is there any other place to setup
> > > > grouping in the report designer...? What am I doing
> > > > wrong...? In the wizard it asks me how I would like to scope my fields
> > > > either Page, Group or Details, where can I access this functionality outside
> > > > the wizard, I created all of my reports without the wizard...
> > > >|||click on the rectangle you drew. Go to the properties tab. One of the
properties is dataset name. there should be a drop down list that allows you
to select the dataset. After you select the data set insert the subreport.
Pass the fieild you want to use as a parameter. On the properties tab for
the sub report set the Page break after to True.
"Alien2_51" wrote:
> I don't have two datasets, I'm using a subreport, the subreports parameters
> are tied to fields in my master dataset using the parameters collection in
> the subreport, I don't see the option in the general tab to "tie it back to
> the dataset" Are you talking about the "Data region"...? How do I create one
> in the designer...? I typed in the field from the dataset I'm grouping on but
> got an error. In your post you had mentioned that I can tie it back to the
> dataset, what did you mean by that...?
> I appreciate your help...
>
> "johnE" wrote:
> > Why do you have two datasets? As far as I know you cannot join two seperate
> > datasets in a report. I may be wrong. What might be easier is to join the
> > datsets in your query. When you do that you create a table and create a
> > group by the changing rows of the master data set. If that won't work then
> > use a subreport rather than two different datasets. the subreport will bring
> > in the detail. When you do it this way you put the rectangle on the report
> > for the master dataset. After you add the rectangle right click on it and
> > select properties. On the general tab there will be an option to tie it to a
> > data set. Then inside the main rectangle you add another one and add the
> > subreport to that. Let me know which way you decide to go and I can help you
> > with this.
> >
> > "Alien2_51" wrote:
> >
> > > Hi John,
> > >
> > > I'm pretty confident we're on to something here, I'm certain I'm just using
> > > a report with text boxes on it, which is most definately why I'm seeing only
> > > the last row of data. I put my data elements in a rectangle which seemed more
> > > appropriate because I'm displaying header or master data in the "top" report,
> > > so I want a new page for every row in the master dataset. I noticed in the
> > > property pages for the rectangle that there is a drop down list thats labeled
> > > Data region, the description reads: Type or select the data region with which
> > > to repeat the rectangle on every page that the data region appears. My
> > > question is this; Could you define the term "Data region" and how do I create
> > > one in the designer. I typed in the field from the dataset but got an error.
> > > In your post you had mentioned that I can tie it back to the dataset, what
> > > did you mean by that...?
> > >
> > > Thanks for your help!!!
> > >
> > > Dan
> > >
> > > "johnE" wrote:
> > >
> > > > I am not sure but it sounds like you need to put either a table or rectangle
> > > > onto the report that you can tie back to you dataset. If you just create a
> > > > report and put textboxes on the blank page you will only get the last row of
> > > > data.
> > > >
> > > > "Alien2_51" wrote:
> > > >
> > > > > I have a master detail report that I'm having trouble
> > > > > with, I have the report layed out and populating fields in
> > > > > the master report with data, my report uses fields from the
> > > > > master report and passes it as parameters to the subreport, that's all
> > > > > working well, the problem is I only get data for the last
> > > > > record in the master dataset... It's not displaying a new
> > > > > page for each records in the master dataset... None of my
> > > > > fields use the First function... My grouping in my query
> > > > > is setup correctly, is there any other place to setup
> > > > > grouping in the report designer...? What am I doing
> > > > > wrong...? In the wizard it asks me how I would like to scope my fields
> > > > > either Page, Group or Details, where can I access this functionality outside
> > > > > the wizard, I created all of my reports without the wizard...
> > > > >|||Hi John,
Sorry man, we must be using different versions of the report designer or
something, I'm just not seeing the "dataset name" property you're talking
about... I've looked both on the property tab in the VS IDE and the property
pages accessed from the context menu... If you send me email at
dan.billow@.re.mo.ve.this.monacocoach.com, I'll send you some screen captures
of my property pages, I don't know what else to do.
"johnE" wrote:
> click on the rectangle you drew. Go to the properties tab. One of the
> properties is dataset name. there should be a drop down list that allows you
> to select the dataset. After you select the data set insert the subreport.
> Pass the fieild you want to use as a parameter. On the properties tab for
> the sub report set the Page break after to True.
> "Alien2_51" wrote:
> > I don't have two datasets, I'm using a subreport, the subreports parameters
> > are tied to fields in my master dataset using the parameters collection in
> > the subreport, I don't see the option in the general tab to "tie it back to
> > the dataset" Are you talking about the "Data region"...? How do I create one
> > in the designer...? I typed in the field from the dataset I'm grouping on but
> > got an error. In your post you had mentioned that I can tie it back to the
> > dataset, what did you mean by that...?
> >
> > I appreciate your help...
> >
> >
> > "johnE" wrote:
> >
> > > Why do you have two datasets? As far as I know you cannot join two seperate
> > > datasets in a report. I may be wrong. What might be easier is to join the
> > > datsets in your query. When you do that you create a table and create a
> > > group by the changing rows of the master data set. If that won't work then
> > > use a subreport rather than two different datasets. the subreport will bring
> > > in the detail. When you do it this way you put the rectangle on the report
> > > for the master dataset. After you add the rectangle right click on it and
> > > select properties. On the general tab there will be an option to tie it to a
> > > data set. Then inside the main rectangle you add another one and add the
> > > subreport to that. Let me know which way you decide to go and I can help you
> > > with this.
> > >
> > > "Alien2_51" wrote:
> > >
> > > > Hi John,
> > > >
> > > > I'm pretty confident we're on to something here, I'm certain I'm just using
> > > > a report with text boxes on it, which is most definately why I'm seeing only
> > > > the last row of data. I put my data elements in a rectangle which seemed more
> > > > appropriate because I'm displaying header or master data in the "top" report,
> > > > so I want a new page for every row in the master dataset. I noticed in the
> > > > property pages for the rectangle that there is a drop down list thats labeled
> > > > Data region, the description reads: Type or select the data region with which
> > > > to repeat the rectangle on every page that the data region appears. My
> > > > question is this; Could you define the term "Data region" and how do I create
> > > > one in the designer. I typed in the field from the dataset but got an error.
> > > > In your post you had mentioned that I can tie it back to the dataset, what
> > > > did you mean by that...?
> > > >
> > > > Thanks for your help!!!
> > > >
> > > > Dan
> > > >
> > > > "johnE" wrote:
> > > >
> > > > > I am not sure but it sounds like you need to put either a table or rectangle
> > > > > onto the report that you can tie back to you dataset. If you just create a
> > > > > report and put textboxes on the blank page you will only get the last row of
> > > > > data.
> > > > >
> > > > > "Alien2_51" wrote:
> > > > >
> > > > > > I have a master detail report that I'm having trouble
> > > > > > with, I have the report layed out and populating fields in
> > > > > > the master report with data, my report uses fields from the
> > > > > > master report and passes it as parameters to the subreport, that's all
> > > > > > working well, the problem is I only get data for the last
> > > > > > record in the master dataset... It's not displaying a new
> > > > > > page for each records in the master dataset... None of my
> > > > > > fields use the First function... My grouping in my query
> > > > > > is setup correctly, is there any other place to setup
> > > > > > grouping in the report designer...? What am I doing
> > > > > > wrong...? In the wizard it asks me how I would like to scope my fields
> > > > > > either Page, Group or Details, where can I access this functionality outside
> > > > > > the wizard, I created all of my reports without the wizard...
> > > > > >

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
Andrew
Just a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only first row appearing

New to Reporting services, I am trying to create a simple header/
detail report. I am not using subreports due to problems with
mysterious pagebreaking before a lengthy subreport. However, my
problem is now everything looks good in the preview screen, but in a
print or print layout I am only seeing the first header in the group
and the first row for that header.
-wylieOn Jun 21, 1:36 pm, Wylie <wyli...@.gmail.com> wrote:
> New to Reporting services, I am trying to create a simple header/
> detail report. I am not using subreports due to problems with
> mysterious pagebreaking before a lengthy subreport. However, my
> problem is now everything looks good in the preview screen, but in a
> print or print layout I am only seeing the first header in the group
> and the first row for that header.
> -wylie
It sounds kind-of like you have possibly set 'Page break at end' as
part of your grouping (if applicable). Right-click the table control
(if applicable) and select Properties. Select the Groups tab, then
select the 'Edit...' button and uncheck 'Page break at end.' Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 21, 9:39 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 21, 1:36 pm, Wylie <wyli...@.gmail.com> wrote:
> > New to Reporting services, I am trying to create a simple header/
> > detail report. I am not using subreports due to problems with
> > mysterious pagebreaking before a lengthy subreport. However, my
> > problem is now everything looks good in the preview screen, but in a
> > print or print layout I am only seeing the first header in the group
> > and the first row for that header.
> > -wylie
> It sounds kind-of like you have possibly set 'Page break at end' as
> part of your grouping (if applicable). Right-click the table control
> (if applicable) and select Properties. Select the Groups tab, then
> select the 'Edit...' button and uncheck 'Page break at end.' Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I do not have any page breaks set. I could see that happening, but
I'm not seeing any other records. In preview I have 4 groups with
2-30 records in each group. On print layout ALL I get is the header
and first record of the first group.
-wylie

only 1 row from a join

I need to select one column of the first row of a left=20
join operation to use it to insert one row in a table, so=20
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario=20
varchar(20),@.grupo varchar(20)=20
set @.fecha =3D(select fechanac from inserted)
set @.alias=3D(select alias from inserted)
set @.usuario=3D(select usuario from inserted)
set @.grupo=3D (select grupo from inserted)
=20
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumplea=F1os ***/
DECLARE @.hora CHAR(5)
SET @.hora=3D(SELECT horasCumples.hora FROM horasCumples=20
LEFT JOIN Vcitas ON horasCumples.hora=3DVcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES=20
(@.usuario,@.fecha,@.hora,'Cumplea=F1os','P
',@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac=20
=3D '10/10/1980' where alias=3D@.alias and usuario=3D@.usuario and=20
grupo=3D@.grupo ***/
end=20
GO=20
this returns me a message saying that the subquery=20
returned more than 1 result and it is not allowed (in the=20
set @.hora=3D(select... line)
What may I do?
Thnx in advance(reply cross-posted to microsoft.public.sqlserver.programming)

quote:

---

> I need to select one column of the first row of a left
> join operation to use it to insert one row in a table, so
> a put (in a trigger):

---

Which row is the 'first'? The row with the earliest datetime value? Rows
in a relational database table have no order so you need to specify your
rules that identify the 'first' row.

quote:

---

> SET @.hora=(SELECT horasCumples.hora FROM horasCumples
> LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)

---

Only horasCumples.hora is referenced in the column so the LEFT JOIN to the
Vcitas table serves no purpose. You will get all rows from horasCumples
regardless of the Vcitas table contents. This may not be exactly what you
want but you can use the following to select the horasCumple.hora value with
the earliest date:
SELECT @.hora= MIN(hora) FROM horasCumples
There are some fundamental problems with your trigger that need to be
addressed. Importantly, more than one row can be inserted with a single
INSERT statement. The example below illustrates a set-based technique that
eliminates the need to select values from the inserted table into variables.
INSERT INTO Vcitas
(
usuario,
fecha,
hora,
motivo,
tipo,
citaCon,
aviso,
descripcion,
categoria,
zona,
horaAbsoluta,
fechaAbsoluta
)
SELECT
i.usuario,
i.fechanac,
@.hora,
'Cumpleaos',
'P',
i.alias,
'Si',
NULL,
NULL,
NULL,
NULL,
NULL
FROM inserted i
Hope this helps.
Dan Guzman
SQL Server MVP
"pedro j." <anonymous@.discussions.microsoft.com> wrote in message
news:01d101c3d893$c5d9e710$a101280a@.phx.gbl...
I need to select one column of the first row of a left
join operation to use it to insert one row in a table, so
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario
varchar(20),@.grupo varchar(20)
set @.fecha =(select fechanac from inserted)
set @.alias=(select alias from inserted)
set @.usuario=(select usuario from inserted)
set @.grupo= (select grupo from inserted)
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumpleaos ***/
DECLARE @.hora CHAR(5)
SET @.hora=(SELECT horasCumples.hora FROM horasCumples
LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES
(@.usuario,@.fecha,@.hora,'Cumpleaos','P',
@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac
= '10/10/1980' where alias=@.alias and usuario=@.usuario and
grupo=@.grupo ***/
end
GO
this returns me a message saying that the subquery
returned more than 1 result and it is not allowed (in the
set @.hora=(select... line)
What may I do?
Thnx in advance

only 1 data row shows up in preview - should have 10

I am relatively new to Reporting services. I have a real basic dataset
select * from tbl1
tbl1 contains 10 rows and 6 fields. I have 6 textboxes in the layout view.
I linked each textbox to a field in the dataset. When I go to preview, I
only see one row of the 10 rows and only 1 page. What do I need to do to
show the rest of the rows (on the same page)? Any suggestions appreciated.
Thanks,
RichWell, I found part of my problem. When I right-click on a textbox I select
"expression" which brings up a dialog box with a variety of selections. I
want to select Fields but see a message that the report has not been linked
to a dataset. So I go to the Dataset option and select fields from there.
But each of those fields has the "First" function leading the expression.
So now my question is: how do you link a dataset to the report?
"Rich" wrote:
> I am relatively new to Reporting services. I have a real basic dataset
> select * from tbl1
> tbl1 contains 10 rows and 6 fields. I have 6 textboxes in the layout view.
> I linked each textbox to a field in the dataset. When I go to preview, I
> only see one row of the 10 rows and only 1 page. What do I need to do to
> show the rest of the rows (on the same page)? Any suggestions appreciated.
> Thanks,
> Rich|||I think I figured this out. You have to add a table to the layout from the
Toolbox and that is where select your dataset.
Just posting for posterity
"Rich" wrote:
> Well, I found part of my problem. When I right-click on a textbox I select
> "expression" which brings up a dialog box with a variety of selections. I
> want to select Fields but see a message that the report has not been linked
> to a dataset. So I go to the Dataset option and select fields from there.
> But each of those fields has the "First" function leading the expression.
> So now my question is: how do you link a dataset to the report?
>
> "Rich" wrote:
> > I am relatively new to Reporting services. I have a real basic dataset
> >
> > select * from tbl1
> >
> > tbl1 contains 10 rows and 6 fields. I have 6 textboxes in the layout view.
> > I linked each textbox to a field in the dataset. When I go to preview, I
> > only see one row of the 10 rows and only 1 page. What do I need to do to
> > show the rest of the rows (on the same page)? Any suggestions appreciated.
> >
> > Thanks,
> > Rich