AutoFetch Option With Fast Forward Cursors With SQLOLEDB
Jan 24, 2007
Hi,
I am using the AutoFetch Option with Fast Forward Cursors with SQLOLEDB to access SQL Server 2005. This really works out but for the first I execute the query only.
But as I use parameterized statements, when I re-use the cursor, just re-binding new variables and re-executing it again, the AutoFetch does not work any more.
I noticed that thru the SQL Server Profiler. I see a sp_cursorfetch been called at the second time the cursor is re-executed.
A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
Hello, I've found some strange behavior of SQLOLEDB provider. When I set "Preserve on Abort" on true and delete some row in transaction, I cannot see this row after transaction rollback. Is this correct? My code: Dim Conn As ADODB.Connection Dim recSet As ADODB.Recordset Dim cmd As ADODB.Command Dim c1 As Long, c2 As Long ' ## Open a connection Set Conn = New ADODB.Connection Conn.Open "Provider='SQLOLEDB';Integrated Security=SSPI;Data Source='PENASSQLEXPRESS';Initial Catalog='PokusDB';" ' ## Open a table Set recSet = New ADODB.Recordset '## Open cursor Set cmd = New ADODB.Command Set cmd.ActiveConnection = Conn cmd.Properties("Preserve on Abort") = True cmd.CommandText = "SELECT * FROM zkouska order by text" recSet.Open cmd, , adOpenStatic, adLockOptimistic, adCmdUnknown '## TransBegin Conn.BeginTrans '## CursorMove recSet.MoveFirst '## RecordDelete recSet.Delete adAffectCurrent recSet.Requery adOptionUnspecified '## TransAbort Conn.RollbackTrans '## CursorMove recSet.MoveFirst c2 = recSet.Fields("cislo").Value If c1 <> c2 Then Err.Raise 1000, "Transaction test failed" End If
When I call Requery after RollbackTrans everything is OK, but what is then "Preserve on Abort" for? Also I must call Requery after delete or I will get an error after MoveFirst. Do I miss something? Hello, I've found some strange behavior of SQLOLEDB provider. When I set "Preserve on Abort" on true and delete some row in transaction, I cannot see this row after transaction rollback. Is this correct? My code: Dim Conn As ADODB.Connection Dim recSet As ADODB.Recordset Dim cmd As ADODB.Command Dim c1 As Long, c2 As Long ' ## Open a connection Set Conn = New ADODB.Connection Conn.Open "Provider='SQLOLEDB';Integrated Security=SSPI;Data Source='PENASSQLEXPRESS';Initial Catalog='PokusDB';" ' ## Open a table Set recSet = New ADODB.Recordset '## Open cursor Set cmd = New ADODB.Command Set cmd.ActiveConnection = Conn cmd.Properties("Preserve on Abort") = True cmd.CommandText = "SELECT * FROM zkouska order by text" recSet.Open cmd, , adOpenStatic, adLockOptimistic, adCmdUnknown '## TransBegin Conn.BeginTrans '## CursorMove recSet.MoveFirst '## RecordDelete recSet.Delete adAffectCurrent recSet.Requery adOptionUnspecified '## TransAbort Conn.RollbackTrans '## CursorMove recSet.MoveFirst c2 = recSet.Fields("cislo").Value If c1 <> c2 Then Err.Raise 1000, "Transaction test failed" End If
When I call Requery after RollbackTrans everything is OK, but what is then "Preserve on Abort" for? Also I must call Requery after delete or I will get an error after MoveFirst. Do I miss something?
I am trying to execute a query on a table which is in 6.5 from 7.0
I get the following error...
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: The catalog stored procedures installed on server WEB_SERVER are version 6.50.193; version 07.00.0330 or later is required to support IDBSchemaRowset. Please contact your system administrator.]
Can I use the "SQLOLEDB" provider to connect to a SQL Native Client? Follow-up question: does the "SQLOLEDB" provider use the same TDS transport protocol as the SQL Native Client provider?
I am attempting to duplicate a nifty feature that one of my colleagues used:
We have parameters where a user can select a client or an individual account. There are available values for each parameter from two queries. I tried to have the available values in the account list dependent on the user's selection in the client list by passing the client parameter's value to the query for the account data set and using it in a where clause, but I get a "forward dependency" error.
The weird thing is that my colleague tried the same strategy and it worked. We together tried to set my report up the same as his, but cannot find why his works and mine doesn't. Any ideas?
His report works when deployed from my machine, so there must be something in the actual report that is different...
Hello all - I am trying to come up with a reasonable solution to an intermittent network. Specifically, I have a SQL Server 2005 installation that receives data from a client across a wireless network via OLE-DB. There are times when the network connection may not be available, and I would like to buffer the transaction (somehow!) locally, and then have it passed to the database upon re-establishing connectivity.
Any suggestions or recommendations would be greatly appreciated!
It seams as if the Connect Timeout property is not working correctly if the server that one is trying to connect to is down. This is my connection string.
I am remotely logging onto a MS SQL 6.0 server using sp_addlinkedsrvlogin. When I try to query against the table on this server I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]
I know that 6.0 doesn't support OLEDB but shouldn't 7.0 be able to access this using odbc. Do I need to do anything other than set up the dsn on odbc.
Any feedback on this would be great. Cheers, bhanly@apcc.com
I am having trouble logging into my sql server from an asp application using SQLOLEDB.
I am running XP, IIS5 and SQL Svr 2000 on my developement machine. I am able to log into the database using localhost/application.
When I port the code and database over to the server (Server 2003, IIS6, SQL Svr 2000), I am unable to connection using the actual url of the site.
I set the login up on the logins for the database manager along with password (using multi, not Windows authentication). Added that user to the database security logins, giving it dbo, srvr admin, etc. Like I said, works on my dev machine.
The SQLOLEDB request is Provider=SQLOLEDB;Data Source=(local);User ID=UID;Password=PWD;Initial Catalog=DBName.
I set the database up as a System DSN in ODBC, using SQL Server Authentication. The connection tests out just fine.
When I attempt to connect, I get an ADO message indicating that the user id that I supplied could not sign in to the server.
This is driving me nuts.
Does anyone out there have any ideas as to what I have missed on the server vs local developement machine?
if we see provider=sqloledb in the connection string of a legacy sql server 2000/2005 system we're trying to understand better, can we conclude that ODBC is definitely not in the client picture, that possibly OLEDB is and that SQL Native isnt? Can we conclude for sure what middleware must be in use?
My limited understanding of this "middleware" space is that ODBC is old and restrictive, OLEDB is a lot more state of the art and general, and that SQL Native is more proprietary than OLEDB.
Even if we can conclude what middleware must be in use, is it generally as simple as changing a connection string and ensuring installation of the preferred middleware in upgrading to better performing middleware?
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen *-- The connection is already open. ELSE create connection ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @ on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
I have a client application that creates a COM object using the program ID 'SQLOLEDB enumerator' to get an ISourcesRowset. Once I retrieve the ISourcesRowset, I call ISourcesRowset.GetSourcesRowset to obtain a list of available servers.
This works if the client computer (the one running the application) also has SQL Server (or SQL Express) installed. But on a clean computer after installing SQLNCLI.MSI, the call to GetSourcesRowset returns an error 80004005.
My question is, what do I need to install on my client's machines so I can retrieve available servers using this method? Is there another MSI that needs to be installed? Do I need to install SQL Express Client_Components? Or is there something else?
Thanks in advance for any information you can provide.
Hello All,DDL Statements:CREATE TABLE [dbo].[Table1] ([MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOI have a varchar column which represents dates in YYYYMMDD and MM/DD/YYformats. If I query using:SELECT MyDate FROM Table1WHERE DOB LIKE '________'Why aren't the dates in MM/DD/YY returned ? Is the / a specialcharacter in T-SQL ?Thanks in advance
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
I have 2 identical sql servers one of which is linked to the other via a linked server config which for one particular query gives back the error I give in the subject of this thread: java.sql.SQLException: Could not get the data of the row from the OLE DB provider 'SQLOLEDB'
if I run the exact same sql string in query analyser it runs with no problem. if have similar queries also running from java which use the linked server and these do not give an error. I am at a loss as to explain why this is happening.
the query in question joins together a number of views which reference the linked server.
I have tried everything I can think of including recreating the views and linked server definition. If all is OK in query analyser, how can it fail when called over jbdc (especially when all other db requests succeed) ?
I'm having issues with a query to a view that references a view on a linked server, that in turn references a view on a third server.
Query to view on Server A --> View on Server B --> View on Server C
I can query from A to B using the linked server. I can query from B to C. I can query from A to C.
All servers at the same location on the same domain.
I cannot query a view on A, that references a view on B, where the view on B references a view/table on C
Each linked server configuration uses "Be made using this security context", with the user specified being 'sa'. I was hoping to get security out of the equation for debug purposes, hence the sa use.
I have tried from Query Analyzer using both windows and sql auth.
MSDTC is running on all servers as local system.
The full error returned is:
Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Hello, we are trying to find a way to send all SQL traffic from multiple machines in a DMZ trough only one point. This way the firewall doesn't need to be opened to each and every machine.
This may be a simple question but I have never done it before. Any recommendations?
alter proc [ProGeneral_College_Structure] @Year nvarchar(4) as begin DECLARE @SQLStatement nvarchar(1000)
Set @SQLStatement = 'SELECT School AS Level1Code, DIVISIONS.Div AS Level2Code, DIVISIONS.ProgArea AS Level3Code, DIVISIONS.progName AS LevelName , ' + SUBSTRING(@Year,1,2) + '/' + SUBSTRING(@Year,3,2) + ' AS AcademicYearID FROM DIVISIONS WHERE (((DIVISIONS.[' + @Year + '])=1)) ORDER BY DIVISIONS.School, DIVISIONS.Div, DIVISIONS.ProgArea'
EXEC(@SQLStatement) end
It's something to do with the / concatenation I think, is it an escape character or something tried // obviously and CHAR(47). before I get comments I know it's dynamic sql and it's not great but I can't edit the divisions table so have to use a dynamic column.
When you restore a backup from a point in time, how do you then knowwhich transaction ID to start with when you want to roll forward fromthat point in time to another point in time?
I want to set a Report Parameter on a field. The Report Parameter is called 'filter'. In the statement I put the Report Parameter in the WHERE-part: WHERE ([DatabaseName$TableName].[FieldName] = @filter). After this I set the 'Available values' on the Report Parameter in the lay-out to Non-queried. When the report is running, no problems.
But.....
Now I want to set 'Available values' on 'From Query' and refer to the data set, so the user can choose on which value he want to filter. But now, after running the preview the following error displays: Error 1 [rsInvalidReportParameterDependency] The report parameter €˜filter€™ has a DefaultValue or a ValidValue that depends on the report parameter €œfilter€?. Forward dependencies are not valid.
Why can't I set the Report Parameter to 'From Query'? Anyone any suggestions???
(you can see the rest of my statement here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1098540&SiteID=1)
I am getting following Error: Oracle linked server in SQL server 2005 to update tables in Oracle database. I have used OPENQuery to update tables in Oracle from a SQL Server 2005 Stored procedure.
If we run query from sql management studio for inserting or updating table of linked server then it runs fine. But if we use Begin distributed transaction then its gives error -
Server: Msg 7391, Level 16, State 1, Procedure sp_test, Line 105 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. We are using Sql server stored procedure to run the open query syntax for updating oracle interface table.
I am trying to get Event Forwarding to work between two SQL 2000 servers. Both are running SQL 2000 Enterprise SP4 and Windows 2003 Enterprise SP2. I am managing them from my local desktop running XP. I have both servers registered in Enterprise Mgr.
<Server XInstance Z> (named instance) is the server on which I have a MAPI client installed and configured along with SQL Operators and SQL Alerts. <Server Y> (default instance) is the server from which I want to forward events > Sev 17.
<Server XInstance Z> is set up to receive error msgs 9002 and 1105 and email operators that either a log is out of space or a data file is out of space. It has been verified on <Server XInstance Z> that it will send locally generated errors to the operators correctly.
<Server Y> is set to forward to <Server XInstance Z> any event that is sev 17 or greater. However, when I simulate an 1105 on <Server Y> I get this error msg in its Application Event log:
Event Type: Error Event Source: SQLSERVERAGENT Event Category: Alert Engine Event ID: 316 Date: 6/4/2007 Time: 11:37:55 AM User: N/A Computer: <Server Y> Description: Unable to open the eventlog on forwarding server '<Server XInstance Z>' (reason: Sockets error 11004).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I figured this may be due to the fact that SQLAgent is trying to forward a Windows event to another Windows server running SQL but there is no default instance of SQL Server. So I thought I would add a bogus entry to my Enterprise Manager that was just <Server X> with no instance name. Obviously, EM failed to connect but asked if I wanted to register anyway. I chose Yes. So I was then able to change the server in Forward To on <Server Y> to <Server X>.
That actually worked and it began forwarding error messages to to <Server X>. But the error messages are going to the System error log as opposed to the Application error log where I need for them to go. This is the message from the System error log on <Server X>:
Event Type: Error Event Source: MSSQLSERVER Event Category: Disk Event ID: 17052 Date: 6/4/2007 Time: 11:32:40 AM User: N/A Computer: <Server X> Description: The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error: 1105, Severity: 17, State: 2 Could not allocate space for object 'MyTestTable' in database 'Northwind' because the 'PRIMARY' filegroup is full.. Data: 0000: 51 04 00 00 11 00 00 00 Q....... 0008: 0c 00 00 00 43 00 48 00 ....C.H. 0010: 41 00 52 00 4c 00 45 00 A.R.L.E. 0018: 53 00 53 00 2d 00 58 00 S.S.-.X. 0020: 50 00 00 00 0a 00 00 00 P....... 0028: 48 00 4f 00 55 00 34 00 H.O.U.4. 0030: 51 00 31 00 31 00 30 00 Q.1.1.0. 0038: 35 00 00 00 5...
I'm stuck here now. Anyone else run into this problem?
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online: • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician. • The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
I have a client that I provide financial modelling services to (using Excel). They have a requirement to start capturing subscriber movements in their SQL DB. how the table should be set-up and how to extract the necessary movements report. This is largely so that I may include these components in some of the financial models that I am working on.
Subscribers are reported as follows:
Opening subs (the prior periods closing balance; or the sum of new sales at point of 1st entry)
+ New Sales (new subs)
+ Upgrades (movement from a lower product package to the associated package)
- Downgrades (movement to a lower product package from the associated package)
- Churn (subscriber losses)
Closing Balance
All transactions are captured against a specific product package, on a specific date (ymd), and for an associated platform (e.g. digital TV, broadband TV, cable TV).
I believe we only need to capture new sales, upgrades, downgrades and churn. And then used a SP to compile the movements behaviour as described above.
So perhaps the table would appear as follows:
Platform Package Date Movement Value
DTV PROD 1 2014-11-02 New Sales 8
DTV PROD 1 2014-11-02 Upgrades 1
[code]....
So I am assuming that given a table such as the above, we could write a SP to produce an output such as (note, below looks at monthly total so will not agree back to sample above which contains only 2 days):
Platform Package Movement September October November
DTV PROD 1 OPEN 600 676 776
DTV PROD 1 New Sales 92 106 88
[code]....
how one is best to accumulate the balances given that the open date for any given reporting period is in fact an accumulation of all balances since day 1.
How does one typically capture this type of thing in SQL?
I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems] ( [Mobileno] [varchar](50) NOT NULL, [TripNo] [int] NOT NULL, [CustomerName] [varchar](50) NULL, [TripEndTime] DATETIME NOT NULL,
Hello,I'm struggling a giant fight with my webprovider (ASP.net 2.0) in the following case:I'm using the default connectionstring which automatic is provided when I create an ASP.net webstarterskit. This connectionstring is working fine when i deployed that kit to a W2003 server testenvironment and is also running on my laptop. But when I tried to deploy the website to my (new) hostingserver, also a W2003 server, I landed in ERROR-land. The last error I received was:Cannot open database "Club" requested by the login. The login failed.> Login failed for user 'NT AUTHORITYNETWORK SERVICE'.NB: I gave that user the right privileges and roles. My provider is not willing in sending me an example of a good working connectionstring and is directing me to their FAQ-site in which the right connectionstring is mentioned. That string looks like this (for a MS SQL database):strConnectie = "Provider=SQLOLEDB;" & _ "Data Source=(local)SQLEXPRESS;" & _ "Initial Catalog=[GEBRUIKERSNAAM];" & _ "User Id=[GEBRUIKERSNAAM];" & _ "Password=[WACHTWOORD]"My connectionstring in web.config looks like this:<connectionStrings><clear/><add name="ClubSiteDB" connectionString="Data Source=.SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/><remove name="LocalSqlServer"/><add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /></connectionStrings>Is there anybody in this forum that can help me out and explain to me how to use the string, as provided by the host, in the default connectionstring i use in web.config ??Any help is welcome and appreciated.NB: I'm using VWD 2005 Express Edition and SQL Server Management Studio ExpressHarry Elzinga
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a Insert statement which i am using to insert data into a remote server from my local server. I have added the remote server as a linked server in my local server but when i try to execute the statement i am getting the above mentioned error. I am using Windows Enterprise Edition 2003 and SQL Server 2000.
I have also enabled the Allow InBound and Allow OutBound in the security configuration of My Computer properties but still i am facing the same problem. Kindly let me know how to resolve this issue.
