Error 1429: A Server Cursor Cannot Be Opened...

Sep 12, 2006

Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..

We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.

This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.

Once in a while we will get a return from SQL ..

"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."

The OLE error code is 1429. An OLE Exception code 3604 is also returned.

When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.

Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.

Here's the really fun part...

Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.

Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.

Help??

Bill Kuhn - MCSE

The Kuhn Group, Inc.

http://www.kuhngroup.com



View 2 Replies


ADVERTISEMENT

Help: How Do I Change Database Within An Opened Cursor Block??

Dec 4, 2007

Hi All:

Can anyone point out what's wrong with the following script? I wanted to print all the LOGFILE names in all databases which are not in SIMPLE recovery model. Thanks in advance.

Jim



USE masterDECLARE @db_name nvarchar(100), @log_name nvarchar(100);DECLARE DB_Cursor CURSOR FORselect name from msdb.sys.databases where recovery_model_desc <> 'SIMPLE';OPEN DB_Cursor FETCH NEXT FROM DB_Cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN USE @db_name -- Can I do this? GO select @log_name = name from sys.database_files where type_desc ='LOG'; print @log_name; ENDCLOSE DB_CursorDEALLOCATE DB_CursorI got the following error:Msg 102, Level 15, State 1, Line 13Incorrect syntax near '@db_name'.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@log_name".Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@log_name".

View 3 Replies View Related

SQL 2000 TO MS ACCESS DTS Error Message Opened Exclusively By Another User

Aug 8, 2006

I would like to password protect my MS Access Database which is being exported to from SQL 2000 using DTS, but when I password protect it, I get an error message stating "The workgroup information file is missing or opened exclusively by another user". The ACCESS file has to be opened exclusively to add password protection. Any suggestions?

View 1 Replies View Related

RDA Push Error Another User Has Opened The Database With Different Instance-level Initialization Properties. [ 32 ]

Mar 3, 2008



Hi all,

I'm using VS2005 + SQL Compact 3.1 and Enterprise Library 3.1 May 2007

I can successfully create a db using pull method, when I try to push back data I got the following SQLCE error:

"Another user has opened the database with different instance-level initialization properties. [ 32 ]"

Before calling the push method I did

SqlCeDatabase eCE = Helper.GetDB() as SqlCeDatabase;
eCE.CloseSharedConnection();
SqlCeConnectionPool.CloseSharedConnections();
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = webSynchronizationUrl;
rda.InternetLogin = webuser;
rda.InternetPassword = webpass;
rda.LocalConnectionString = eCE.ConnectionStringWithoutCredentials;


but after the assignment the LocalConnectionString is different from ConnectionStringWithoutCredentials, there are much more ssce parameters.

What I don't understand is:

why I'm getting this error even after closing the connection ?

Since the error report [32] the ssce conflicting parameters shoud be AUTO_SHRINK_THRESHOLD, FLUSH_INTERVAL and MAX_DATABASE_SIZE

I tried to cut & paste the same values from LocalConnectionString into app.config connection string, at first It seemed to work, but now I got the same error.

Any help will be greatly appreciated



Thanks

View 3 Replies View Related

Error: Cannot Start Your Application. The Workgroup Information File Is Missing Or Opened Exclusively By Another User.

May 7, 2008

Hi all,
I'm kinda new at programming, and am currently attempting to create a web application.  I have an error when I try to insert information into a sql server database, and can't find how to fix it no matter how hard I try.
The error I get is: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
I have it so a user is logged in and is shown a form to fill out, then submits it (which creates an insert statement and sends it to the database).
My connection string is as follows:
CnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=serverdestination;" + "User id=user;" + "Password=pass;";
If you need any more information, just let me know.
Any help would be greatly appreciated!

View 2 Replies View Related

Cannot Bulk Load Because The File Could Not Be Opened. Operating System Error Code 5(Access Is Denied.).

Jan 23, 2007

I am facing a issue with bulk upload on Test Server.

Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error.

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\ServerNameinputFileName.csv" could not be opened. Operating system error code 5(Access is denied.).

For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine.

If the command is run from Test Server connecting to any SQL Server instance including Test Server it is running fine.

Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server

2) Given the read/write rights on shared folder to everyone.

Query:

SELECT DISTINCT * FROM OPENROWSET

(

BULK '\ServerNameinputFileName.csv',

FORMATFILE='\ServerNameFormat.xml'

)

AS FileList



Please provide me with some solution. What can be the reason for such behaviour?

View 22 Replies View Related

Sql Server Keep Connection Opened

Feb 2, 2007

Hi,

I am working with SQL Server 2005 and Vb.NET, this program is connected to a serial port where it might start receiving lots of data and stores it in the SQL db, but also it could be without receiving anything for hours.

I have discovered that sometimes data arriving is faster than opening the connection to db and an exception is thrown, so I am thinking about leaving the connection opened forever since the program is the only client who connects to that database.

How could I leave the connection opened forever ? or if there's a better way to achieve it I would be eternally thankful.

I am using: SqlClient.SqlConnection

Thanks in advance.



George.

View 1 Replies View Related

SQL Server 2008 :: SSMS - XML Resultset Not Opened In New TAB

May 7, 2015

If you write a query that outputs XML, you'll have a long string in the Results window/tab that is also a link. Clicking on this link opens a new tab in SSMS with the formatted XML string easier to read.

My issue is that clicking on the link opens the "formatted XML string" tab ON THE SAME tab as my code and no way to go back to my code after that ... I need to reload the query from disk, if saved, to erase the "formatted XML" window.

View 0 Replies View Related

Can SQL Express Db Be Opened With SQL Server 2005 Management Studio?

Apr 28, 2008

I'm trying to open a SQL Express database (MDF) with SQL Server 2005 management Studio.  Is that possible?  I'm getting the following error: 
 The specified file is either not a SQL Server Compact Edition file  or is corrupt.
It opens fine in Visual Studio 2005.
Also, is it possible to run a script in Visual Studio 2005?  If so, how?
Thanks, Alex

View 1 Replies View Related

Reporting Services :: JIT Debugging Error When Reporting Configuration Manager Is Opened

Sep 11, 2015

We have installed SQL Server 2005 with reporting services.When tried to open getting errors as below: how to avoid such errors See the end of this message for details on invoking  just-in-time (JIT) debugging instead of this dialog box.

ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. ---> System.Runtime.InteropServices.COMException (0x8000000A)
  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
 
[code]...

View 4 Replies View Related

An Error Occurred While Trying To Access The Database Information. The Msdb Database Could Not Be Opened.

Jun 21, 2007

I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:

An error occurred while trying to access the database information. The msdb database could not be opened.

View 3 Replies View Related

SQL Server 2012 :: Calculate Failure Rates Of Systems Based On Cases Opened Through Support

Jun 5, 2014

I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:

System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000

Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be

System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000

And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.

View 8 Replies View Related

The Database 'x.MDF' Cannot Be Opened Because It Is Version 611. This Server Supports Version 607 And Earlier??

Mar 11, 2006

Hi,

I have tried to attatch a database ,created by SQL server Express within a C# application , in SQL server 2005 Enterprise edition, but the following error message appears:

-----------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'MEDO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1187.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database 'E:X.MDF' cannot be opened because it is version 611. This server supports version 607 and earlier. A downgrade path is not supported.
Could not open new database 'E:X.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

------------------------------



so , how can I solve this problem , I need to reed the data resides in the tables of 'X' database , how can I do it?? please help me.



Thanks in advance for any help.

Aya.

View 7 Replies View Related

Cursor Error . Please Help .

Jun 17, 2002

Hi Friends ,

When i am trying to store a table under a database , it gives me an error that
'Invalid Cursor Status ' and it does not allow me to store that table .

The server is SQL Server 7.0 with Service pack 3.

Can somebody help me in getting this resolved please ?

Thanks
Anita .

View 1 Replies View Related

Cursor Error

Jul 3, 2002

We are getting an error while running a stored procedure,
This is not the exact error but it is roughly:

'cannot complete cursor operation because table schema changed after cursor declaration'

Any ideas as to what causes this?

Thanks,
Eddie

View 1 Replies View Related

T-SQL (SS2K8) :: Inner Cursor Error

Jun 13, 2014

I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?

DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;

[code]...

View 8 Replies View Related

Error Handling + Cursor

Oct 25, 2007



Hello group

I need some help and gaining knowledge per the SQL Standards.

I have a Stor Procedue which Cursor through say MASTER. This table have information/ Counm that guides me to multiple say two different table.
i.e, Col1 in MASTER table have the information on what table I need to work on COL2 in MASTER Table give me what Field I have to work on and accordingly move the data

Example :

MASTER
ID COL1 COL2 COL3
1 Address Street Nichols Rd.
1 Address Zip 90001
2 Address Street Michigan Ave
2 Address Zip 90002
3 Person FName James
3 Person LName Bond
4 Person FName Bill
4 Person LName Gates


Address
ID Street Zip
1 Nichols Rd. 90001
2 Michigan Ave 90002

Person
ID FName LName
3 James Bond
4 Bill Gates

For doing this I created a Store Procedure which Cursor thru MASTER table which for each row goes to Entity on which it has to work (MASTER Table : Col1 Value) and in that entity Its goes to the Field(MASTER Table: Col2 Value) and places the value of the Col3 in it.

It is working fine and superb. Now, I try to introduce Error Handling such that incase in order to see if there is any error while doing this INSERT and UPDATE.

SAMPLE QUERY: (Structure is Same but modified for readability)


UPDATE @ENTITY SET @FIELD = @VALUE WHERE ID = @KEYID
IF @@ERROR <> 0

BEGIN
INSERT INTO dbo.ErrorLog VALUES( @KEYID + ' , '+ @ENTITY + ' , ' + @FIELD +' , ' + @VALUE )
END

This query is working fine . If there is any error and for some reason if its not able to insert/update the data Its populates the ErrorLog Table and instead of failing the store proc goes for the next row and do teh necessary action

Finally SProc does what it has too
1) Get the data in teh respective tabel and colunm
2) if it cannot insert/ update go for the next row and populate the error log table with what it was n't able to update/ insert


SO Whats the problem? Good question , here is the issue or topic of discussion

1) What is the standard way of Error Handling. Did I atleast follow teh minimum Error Handling criteria.
2) I can see from teh table that, as required, its skipping the row into Error Log table whenevr its not able to do the required functions and going for next row
3) I introduce a Error and when I execute the Sproc it gives me Mesaage:

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.
Also I see that "Query Completed with Errors" well I said whenever teh SPROC see error do some thing else, then why would it count it as error and give me this message? Why is the SPROC failing.

What Should I do such that it counts or give when ever there is a genuine error. I mean it should not fail at all because I am saying there whenever there is an incapability of Insert/ Delete just simply skip the row and Insert that row in Error Log Table.

Am I missing something in this Proc?

If it will do that then is tehre any way or query or anything I have to do that handles the issue that
Whennver its not able to do whatever I am asking it to do (Ex: If I am asking it to insert INTEGER in Character Field) then an error is raised then just skipp that row insert that row in erro log and go for next row and dont fail the SPROC.


THANKS FOR THE PATIENCE FOR READING AND REPLYING.





View 11 Replies View Related

Error On Update, But Not When Using Cursor

Dec 7, 2006

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

View 1 Replies View Related

ODBC/SQL Cursor Error

Oct 14, 2007

One of the people I have to support gets an error message when they are trying to use a SQL based application that was developed in house:


an error occured..microsoft odbc sql driver cursor type change. location frn main, load user () source is odbc



Unfortunately, the developer is not available to troubleshoot the issue and I do not understand it. I thought ODBC settings were static, so how could they cause a cursor to change?

View 1 Replies View Related

MDF Cannot Be Opened,How Can I Do?

Jan 23, 2006

The default web diretory works on my locathost,but when I upload to the free website, http://dehim.aspx.sk/  I'm getting the following error.
The database 'D:LOCALUSERDEHIMAPP_DATAWAPDATABASE.MDF' cannot be opened because it is version 611. This server supports version 603 and earlier. A downgrade path is not supported.Could not open new database 'D:LOCALUSERDEHIMAPP_DATAWAPDATABASE.MDF'. CREATE DATABASE is aborted.An attempt to attach an auto-named database for file D:LocalUserdehimApp_DataWapDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
How do I  downgrade the database to match server?
 

View 3 Replies View Related

Urgent: Cursor Already Open Error

Oct 8, 2001

I get the following errors while running a SQL procedure.

[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The cursor is already open

[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'qst_Ruler_AllExpenses' already exists

I am creating, opening and deallocating the cursor.

Advice please.

View 1 Replies View Related

Cursor Error After Upgrade To SQL2000

Oct 30, 2001

Hello:

We have an old 16-bit app that runs fine in SQL 7.0 but on our test SQL 2000 box some reports generate the following error "System Message 10202 - The application fetch buffer size is less than the cursor select statement requires". Any ideas what this means, I am not much of a coder. The MS Knowledge base does not list much on this.

Thanks,

View 1 Replies View Related

Error: Could Not Create An Acceptable Cursor.

Jul 25, 2007

I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.

View 2 Replies View Related

The Cursor Does Not Include The Table ... Error?

Sep 4, 2007

We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.).

I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good.

The next error is puzzling though:

[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor.

Any suggestions on how to fix this?

View 2 Replies View Related

Error: Cursor Not Returned From Query

May 26, 2006

I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query

View 3 Replies View Related

Invalid Cursor State Error

Jul 20, 2005

I know other people have posted stuff like this before, but I am stillto see a definitive answer. I have created a table in Enterprisemanager called tblUsers. I later added a column in EM and saved thechange. I then noticed I had misnamed the column, so I renamed andwhen I tried to save the change I got the following message:-'tblUsers' table- Unable to rename column from 'fldepCode' to 'fldRepCode'.ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor stateThis is now regularly happening to me, and I am tired of having torecreate tables.I am using SQL Server 2000 with all the latest service packs andpatches. Has anyone any idea what is causing this and how I can cureit or avoid it?

View 2 Replies View Related

Database X Cannot Be Opened

Feb 7, 2012

I'm working on a SQL Server 2005 mirroring database test.The idea is to stop main DB server and activate the standby being mirrored (pretendng that principal DB Server is down)

Mirror works fine.
But for the test I do the following:

- Stop the principal SQLSERVER
- Connect to the standby server and execute the followng:

sqlcmd -S <IP> -d master -U opmirror -P xxxxx -Q "ALTER DATABASE DB_NAME SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;"

And it returns this error:
"Database 'DB_NAME' cannot be opened. It is in the middle of a restore"

Why can't the standby database be opened?

View 3 Replies View Related

Sqlncli.msi Could Not Be Opened

Mar 23, 2007

I was trying to update a named instance of SQL Express SP1.  I double clicked it and during extraction I get this error message:

The file C:Document and SettingsByersNLocal SettingsTempBSISQLEXP05TRACTsetupsqlcli.msi could not be opened. 

SQL Express came bundled with another porgram so I'm thining it could be a bad cd.  I also thought about reinstalling Windows Installer 3.1.  Anybody experience this error?

 

 

 

View 3 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Close And Deallocate Cursor In Error Routine

Oct 26, 2000

I have an error trapping routine within a proc that uses cursors. The routine works but if I run the stored proc from Query analyzer more than once it complains the cursor has already been declared and is still open. Should I close and deallocate as part of my error routine?

View 3 Replies View Related

Error 16916: A Cursor With The Name Tentative_cursor Does Not Exist

Jun 24, 2006

Hi,

I am creating a stored procedure which contains the declaration of a cursor, fetch the cursor, close the cursor, deallocate cursor. I tried executing this code in SQL analyzer and found working. But when I use the same code inside a create stored procedure, it throws the following error message.

Error 16916: A cursor with the name "tentative_cursor" does not exist

As a result, I am not able to proceed in creation of stored procedure with the above SQL statements.

Can any one help me where the problem is..

My SQL statements..
---------
DECLARE tentative_cursor CURSOR
FOR select * from TbTentativeBookingTable where ExpTime < GETDATE()
GO
OPEN tentative_cursor
GO
DECLARE @transactionno BIGINT
...
...
FETCH ...
CLOSE tentative_cursor
---------


Thanks
-Sudhakar

View 1 Replies View Related

T-SQL (SS2K8) :: Cursor - Declare Variable Error

Sep 9, 2014

The below cursor is giving an error

DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)

DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR

[Code] ....

The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.

Why does the delete statement give an error ?

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved