Hi,
is there any update on the subject?
Jet has it for ages.
I want to use Scrollable/Updateable Cursors & OLEDB Rowsets opened via Select query.
Last time I have heard that MSFT is planning to add this in the future.
Is there a beta version that I can try?
Thanks,
Konstantin
Howdy folks! I have some questions on cursors.
I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:
Forward-Only:
Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.
This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?
MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?
If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?
Scrollable:
"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."
Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?
Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.
Thanks in advance!
Howdy folks!
I have a need to access random entries in an IRowset object. Looking through the OLEDB documentation there seems to be several options:
1. Just use GetNextRows with positive or negative offset values..
2. Use the IRowsetLocate :: GetRowsAt method
3. Use IRowsetIndex :: Seek to set the cursor
4. Use IRowsetBookmark :: PositionOnBookmark
Which of these are supported and/or recommended for SSCE?
Just to give you an idea of what I'm going for, the application will look something like the following on my own recordset classes:
const BOOL MoveToNext();
const BOOL MoveToPrev();
const BOOL MoveToFirst();
const BOOL MoveToLast();
const BOOL MoveTo(const UINT &cursor);
const UINT GetPosition() const;
Thanks a bunch!
Jeff
How can I implement List on client which represent some SQL query with ability search through this list.
i.e. can I somehow get searched record and records near it (in different sort context) e.g. prev 10 and next 10?
Hello
When I call ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) I am getting the following error when the data type is Numeric(18, 4):
Expression evaluation caused an overflow. [ Name of function (if known) = ]
The numbers involved are not that big and work fine when ExecuteReader() or ExecuteResultSet(SqlServerCe.ResultSetOptions.None) are called on the same SQL.
Any ideas? Thanks in advance!
Cheers,
Dave
Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim errorDescription As String = String.Empty
Dim numericNumber As String = String.Empty
Try
Using sqlCE As New System.Data.SqlServerCe.SqlCeConnection("Data Source = '" & My.Application.Info.DirectoryPath & "MyDatabase.sdf';")
sqlCE.Open()
Dim sqlCECommand As SqlServerCe.SqlCeCommand = sqlCE.CreateCommand()
sqlCECommand.CommandText = "SELECT SUM(MT.TPM_Measure1) AS CurrentAmount FROM BUS_Table MT"
System.Diagnostics.Debug.WriteLine(sqlCECommand.CommandText)
Dim reader As System.Data.IDataReader = Nothing
If RadioButton1.Checked Then
reader = sqlCECommand.ExecuteReader() 'Works fine
ElseIf RadioButton2.Checked Then
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.None) 'Works fine
Else
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) 'Causes the error!
End If
If reader.Read() Then
numericNumber = reader(0).ToString()
End If
reader.Close()
reader.Dispose()
End Using
Catch ex As Exception
errorDescription = ex.Message
Finally
Me.lblError.Text = errorDescription
Me.lblNumeric.Text = numericNumber
End Try
End Sub
TPM_Measure1 datatype is Numeric(18,4)
When the above query works the value is: 4053723.6300
Reader Community
I've just started hosting my newly created Microsoft Visual Web Developer 2005 Express Edition web site. Unfortunately the Login group membership functions will not function correctly. Having contacted the web service hosting provider, They replied: "We do not support SQL express2005. The only way to use the extra functions of ASP.NET2 such as group membership is if it is using an SQL 2000 database to connect to. "
Is it possible to design web sites with Microsoft Visual Web Developer 2005 Express Edition that store membership details on an SQL 2000 database?
I've just paid £88 approx. $140 for a years subscription, have I chosen the wrong web service hosting provider?
Should I have designed the web site with a better web site design software tool that also makes designing membership login functionality easy, just as Microsoft Visual Web developer 2005 express edition?
Look forward to all comments?
Regards
Philip
I posted this question on the Analysis Services forum ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087288&SiteID=1 ) and Irina asked me to post it over here to get an answer.
I'm trying to run an MDX query against SSAS2005 with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?
The following code works:
//using Microsoft.AnalysisServices.AdomdClient;
string MDX = "with member [Measures].[Test] as Str(@Param1) "
+ "SELECT [Measures].[Test] on 0, "
+ "[Product].[Category].[Category].Members on 1 "
+ "from [Adventure Works]";
AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");
conn.Open();
AdomdCommand cmd = new AdomdCommand(MDX, conn);
cmd.Parameters.Add("Param1", "abcde");
System.Data.DataSet ds = new System.Data.DataSet();
AdomdDataAdapter adp = new AdomdDataAdapter(cmd);
adp.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows[0][1]);
conn.Close();
The following code fails:
//using System.Data.OleDb;
string MDX = "with member [Measures].[Test] as Str(@Param1) "
+ "SELECT [Measures].[Test] on 0, "
+ "[Product].[Category].[Category].Members on 1 "
+ "from [Adventure Works]";
OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");
conn.Open();
OleDbCommand cmd = new OleDbCommand(MDX, conn);
cmd.Parameters.AddWithValue("Param1", "abcde"); //changing it to @Param1 doesn't help
System.Data.DataSet ds = new System.Data.DataSet();
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(ds); //produces error: "The following system error occurred: The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."
Console.WriteLine(ds.Tables[0].Rows[0][1]);
conn.Close();
I am putting the below query in a OLEDB SOURCE through a variable (it is a select statement with a where clause from one date to another).
"select TestRecordtype, request_id from department
where LOAD_TMSTP between
(select max(END_TMSTP) LOG) and
(TO_DATE("+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System:tartTime] ), 2) + "-"
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System:tartTime] ), 2) + "-"
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System:tartTime] ), 2) + " "
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System:tartTime] ), 2) + "."
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System:tartTime] ), 2) + "."
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System:tartTime] ), 2) +",'DD-MM-YY HH24.MI.SS')) "
Here is an example of what I am exactly doing!! CREATE VIEW NamesAS SELECT ID, fName, lName FROM Table1 UNION ALL SELECT id, FirstName, LastName FROM TABLE2. On Executing this...I'm getting the following error.
The Query Designer does not support the UNION SQL construct:
Hello-
I have a view that used to be updateable (except for a few fields, but that is fine). When I use the results of a CASE statement as one of the field values, the entire view is not updateable. The view is pasted below. Does anyone know how to keep my view updateable and, somehow, add this new field? Thank you.
-Eric
SELECT
DATEPART(yy, Orders.NextShipDate) AS year,
DATEPART(mm, Orders.NextShipDate) AS month,
DATEPART(dd, Orders.NextShipDate) AS day,
People.LastName,
People.FirstName,
Customers.CustomerID,
Orders.NextShipDate,
Customers.Custom,
Orders.AllocationTemp,
Orders.ShippingTemp,
Orders.HorseName,
Orders.PriorShipDate,
Customers.Username,
Customers.Password,
Orders.OrderID,
Orders.Deleted,
Orders.Deadbeat
FROM
People
INNER JOIN Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ON People.PeopleID = Customers.PeopleID
WHERE
(CONVERT(DATETIME, CONVERT(CHAR(10), Orders.NextShipDate, 101)) <= DATEADD(d, 7, CURRENT_TIMESTAMP)) AND (Orders.Deleted = 0) AND
(Orders.Deadbeat = 0) AND
(Customers.Deleted = 0) AND
(Customers.Pending = 0) AND
(Orders.OrderID NOT IN
(SELECT Orders.OrderID
FROM Items INNER JOIN Products ON Items.ProductID = Products.ProductID INNER JOIN Orders ON Items.OrderID = Orders.OrderID
WHERE
(Items.Dose = 0) AND
(Products.IsPharmaceutical = 1) AND
(CONVERT(DATETIME, CONVERT(CHAR(10), Items.Created, 101)) > DATEADD(d, - 5, CURRENT_TIMESTAMP)) AND
(Items.Deleted = 0))) AND
EXISTS (SELECT ItemID FROM Items WHERE Items.OrderID = Orders.OrderID AND Deleted = 0)
I could have swore I posted this earlier today, but it must not have went through...anyway here goes again.
Is there any way to tell if a view is updateable (updates the underlying table(s))?
Thanks,
Van
I have a view that combines several tables, and lastly one column that is determined by a subquery.
i.e.
CREATE VIEW [View]
AS
SELECT
[TableA].*,
[TableB].*,
(SELECT TOP 1 [Column] FROM [TableC] ORDER BY [Column2])
AS [SpecialColumn]
FROM [TableA] INNER JOIN [TableB]
ON [TableA].[ColumnID] = [TableB].[ColumnID]
I have made INSTEAD OF UPDATE and INSTEAD OF INSERT triggers for this view that will then allow you to modify [View], and it will handle everything, adding/modifying the data in the three [TableX] tables accordingly.
In SQL Management Studio, the following code works fine, and is what is expected...
UPDATE [View] SET [SpecialColumn] = 1 WHERE [ColumnID] = @DesiredID
This will call my instead of trigger and update my special column accordingly exactly how I want it to.
Now, the problem lies in the application layer running on top of the database and how it accesses the view. Any call from the application to update [SpecialColumn] returns an OleDB error stating that [SpecialColumn] can not be modified. I assume that sql server is identifying the column as some sort of a derived column that normally wouldn't be able to be updated, and OleDB is restricting the column to just read only. Is there some way to override that in my view to say that the column is not read-only? Is this an OleDB restriction that can't be handled in the database/view itself?
I've also tried creating a stored procedure that called the exact same
UPDATE [View]
SET [SpecialColumn] = @param2
WHERE [ColumnID] = @param1
and it also works within management studio, but still gives a similar OleDB error about how the stored procedure attempts to update a derived column that can not be modified.
I have a solution where my stored procedure calls the exact same code as the instead of triggers to update the underlying tables of the view manually, however, I'm not asking for a solution like this to my problem, because this works fine, I simply am not happy having to do band-aid workarounds and am wondering if this is a common problem in front-ends and whether it can be handled on the database layer.
I am hoping to create an updatable view from two tables joined on twofields. I have two questions;1. How can I enforce a one to one relation in sql server 2005 based on twofields? Do I need to create one explicitly under relations? Do I cerate anindex on both fields? Is there a step by step process I can look at tocreate one-one relations?2. Is a view updateable if there is a where clause on one of the fields inthe join/relation?Thank again.Regards
View 1 Replies View RelatedHow do I make a view non updateable? I want to create a view so that Iwill not be able to update, insert or delete the view so that basetable is not affected (except dropping the view itself). Thanks.
View 4 Replies View RelatedHi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)
I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..
I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
Will Sql Express handle being a subscriber for an updateable transactional publication from Sql Server 2005?
View 6 Replies View RelatedI have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working Any suggestions for what I should do?
Here is the query:
SELECT t.*
FROM (SELECT TOP 10 itemnum, itemnum FROM (
SELECT TOP 100 itemnum, itemnum
FROM(CatView)
WHERE [CATALOG PAGE] LIKE 'SM%'
ORDER BY itemnum ASC) AS foo
ORDER BY itemnum DESC) AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum
ORDER BY bar.itemnum;
Here is the error i'm now receiving:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement.
I know it was working great before, so i'm not sure what to do.
Michael
Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?
Hi,
I have an Integration Services project which creates a flat file report from Analysis Services, I'm using an OLE DB as data source and running an Openquery in the SQL statement.
the problem is that Integration services runs the query twice before getting the data into the flat file. I know this because the query runs two times in Profiler, and because the same query takes half the time when run in Management Studio.
Integration Services is running the whole query when validating. how can I disable this validation or better make it validate properly.
thanks
Does anyone have advice as choosing between merge replication and updatable subscriptions? I've read the documentaion, they seem to offer the same functionality, and I don't see any clear guidelines as to why to pick one over the other...any suggestions would be appreciated!
Ed
Hello,
We have two updateable subscribers and a publisher using transactional replication. The distributor is located on publisher. Currenetly all 3 sites are SQL 2000 SP3. We plan to upgrade all sites to sp4 with minimum downtime (if possible zero). Is it possible to upgrade publisher/distributor first and subscribers after few days?
Thanks,
np70
Hi,
View 7 Replies View RelatedWe have a database set up for transactional replication with an updateable subscription. When we add log shipping to the publication database (sending the logs to a separate server) the publication and subscription entry show up in Management Studio's replication folder on the log ship target server (although the definitions are correct).
Is this configuration legitimate? Can I add log shipping to the subscription database as well?
Thanks... Liston
I am using RMO to synchronize an updateable transactional pull subscription from Sql Express SP2 to Sql Server 2005 64-bit Standard Edition. The data propogates from the server to the client, but changed data on the client does not replicate to the server. Any ideas?
Thanks!
Learning how to use SSIS...
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
http://msdn2.microsoft.com/en-us/library/ms139904.aspx
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
...cordell...
hi, I created a cursor, can I use "select * from cursorname in the query window?
thanks
Ali
hi friends,
i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go
thnks in adv.,
chakri
We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers)
DB contains a table with a field of type "Attachments" (which is new in MS Access 2007)
DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."
Error using OLEDB (using "RowSetViewer"):
================================
select * from Query1 gives the following error:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: €œNot implemented
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CRowset.cpp
Line: 616
select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] €œUnspecified error€?
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CCommand.cpp
Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
Thanks,
Joe
Hi,
I'm trying to use query parameters with an Oracle OLEDB Source in a data
flow task and I'm having problems.
I've tried formatting the query each of the following ways...
--
select
frq_code,
frq_name,
update_frq,
uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
--
Parameters cannot be extracted from the SQL command. The provider might not
help to parse parameter information from the command. In that case, use the
"SQL command from variable" access mode, in which the entire SQL command is
stored in a variable.
Additional information
---> Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Oracle).
Hi,
Urgent Help required..........
Can anyone explain me steps how to parameterized query to send oracle.
If you know any other control which help to do this rather than OLEDB source.
Please let me know.
THanks