Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







ODBC/SQL Cursor Error


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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
ODBC Inserting Against Open Cursor
Im receiving 'HY000 Connection is busy with results for another hstmt' when trying to insert. The reason is down to another hstmt with a cursor still open (as not all results have been fetched) against the same connection handle. My current design is one connect handle per file, with multiple statement handle depending on sql issued so i dont need to rebind.
I'm guessing, but is there a sqlsetconnectattr/sqlsetstmtattr setting to which will allow me to insert/update/delete to a file with a open cursor that may include the result ???

sqlserver version 8

View Replies !   View Related
How To Clear 'ODBC 24000 Invalid Cursor State'
Hello experts. Excuse my SQL newbie status. I was writing a stored procedure to crunch some data against an MS SQL 2000 database. I didn't entirely know what I was doing so I was using Crystal Reports 11 as an "application layer" pulling against a stored procedure. Well I guess ODBC connections don't support temp table output from stored procedures, or at least I don't know how to do it.

So I've rewritten my stored procedure so it inputs and outputs data using BCP, rather than just sending a simple select query.

My problem is that I cannot get CR to add the stored procedure back in to the report. Everytime I try I get an 'ODBC 24000 error - Invalid Cursor State.'

Where is this current set of records? How do I find it and close it? I've recreated my ODBC connection, every related table and SP to no avail. This is my crusial question.

Less crucial but helpful: How do I write an SP so it can be called from a report program like CR and will return its result set to the application? What's a good, simple "application layer" I can use if I'm doing all the logic in native SQL stored procedures? ASP.NET looks like a pain in the ass, and SQL Reporting Services requires Visual Studio .NET 2003. Is Perl my answer, since I can build web interfaces and forms in my sleep?

View Replies !   View Related
ODBC Error: [Microsoft][ODBC SQL Server Driver]Communication Link Failure
Hi

When I open design table in 2000 Enterprise Manager and clicked on Index properties tab ,I am getting following error "ODBC error: [Microsoft][ODBC SQL Server Driver]Communication link failure"

Please help me.

thanks in advance

mike

View Replies !   View Related
Cursor Error . Please Help .
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 Replies !   View Related
Cursor Error
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 Replies !   View Related
ERROR [HY000] Or ERROR [23000] At Random Times When Using Sql Native Client Via ODBC
The following (VB.Net) code causes exceptions at seemingly random times.
Any suggestions?
Not sure if the problem is in .Net's ODBC support or in Sql Native Client.

If MARS is off, usually after less than 100 loops:



Code Snippet

System.Data.Odbc.OdbcException was unhandled
  ErrorCode=-2146232009
  Message="ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command"
  Source="SQLNCLI.DLL"
Turning MARS is on bypasses that error, so it will sometimes survive a thousand or so runs before hitting:




Code Snippet

System.Data.Odbc.OdbcException was unhandled
  ErrorCode=-2146232009
  Message="ERROR [23000] [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'Number', table 'tempdb.dbo.#TempTable___..(shortened to fit).. _________000000002F3F'; column does not allow nulls. INSERT fails.
  Source="SQLNCLI.DLL"
Code here:
(Console Application, empty database,
Visual Studio 2005, .Net 2.0, Windows XP,SQL Server 2005 or SQL Express 2005, Local or Remote)





Code Snippet

Module Module1

    Sub Main()

        'Dim connection As New SqlClient.SqlConnection("database=TestDB;server=.sqlexpress;Integrated Security=SSPI;") 'Doesn't crash
        'Dim connection As New OleDb.OleDbConnection("Provider=SQLNCLI;database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Doesn't crash

        'Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Crashes
        Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;MARS_Connection=yes") 'Crashes

        VBMath.Randomize()

        Dim run_count As Integer = 1
        connection.Open()
        Console.WriteLine("Connected!!")

        While True
            'connection.Open()

            Dim testcmd As New Odbc.OdbcCommand("CREATE TABLE [#TempTable] (Number int PRIMARY KEY)", connection)
            testcmd.ExecuteNonQuery()
            testcmd.Dispose()
            testcmd = Nothing

            Dim dtTemp As New DataTable
            Dim daTemp As New Odbc.OdbcDataAdapter("SELECT * FROM [#TempTable]", connection)
            daTemp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            daTemp.Fill(dtTemp)
            Dim cbTemp As New Odbc.OdbcCommandBuilder(daTemp)
            Dim viewTemp As New DataView(dtTemp, Nothing, "Number", DataViewRowState.CurrentRows)

            Dim i As Integer
            For i = 1 To 1000
                Dim test_number As Integer = CInt(Rnd() * 2000)
                If viewTemp.Find(test_number) = -1 Then 'Keep it unique
                    Dim new_temp_row As DataRowView = viewTemp.AddNew()
                    new_temp_row("Number") = test_number
                    new_temp_row.EndEdit()
                End If
            Next

            daTemp.Update(dtTemp)

            'daTemp.Dispose()
            'daTemp = Nothing

            'cbTemp.Dispose()
            'cbTemp = Nothing

            'dtTemp.Dispose()
            'dtTemp = Nothing

            'viewTemp.Dispose()
            'viewTemp = Nothing


            Dim testcmd2 As New Odbc.OdbcCommand("DROP TABLE [#TempTable]", connection)
            testcmd2.ExecuteNonQuery()
            testcmd2.Dispose()
            testcmd2 = Nothing

            'connection.Close()
            'GC.Collect()

            'Console.Write(".")
            run_count += 1
        End While

    End Sub

End Module

Usually the ".Update" triggers the exception, but sometimes the other sql commands do it.
Have tried various experiments with disposing of objects immediately when finished, but no effect.
Opening and closing the connection each loop seems to delay the errors, but it still happens eventually.

View Replies !   View Related
Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Hi Everybody,


On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.

Thanks,

Arif



Server Error in '/' Application.
--------------------------------------------------------------------------------

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.]
Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27
Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838
Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80
Calgary.venues.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootCalgarySitevenuesvenues.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

View Replies !   View Related
Why I Get This Error:Microsoft OLE DB Provider For ODBC Drivers Error '80040e14'
When i run the web site in Windows 2003 Server,and I click on the "view" hyperlink to view customer infor,I got the error message as below:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '/'.
/bc/cust_info.asp, line 132
and the SQL statement in line 132 is as below:
strsql1 = "select sum(unit_price*qty) as SubTotal from dailySchedule where customer_code='" & request.QueryString("login") & _              "' and [date]=" & dbldate1
where [date] is reserved word
 
But,if I run the web in Windows 2000 Server,I don't get the error message as stated above.
Pls help.Thanks.

View Replies !   View Related
ODBC Error: SQLState 28000 && Server Error 18452
 

We have an application that connects to SQL 2005 thru ODBC with the following string:

driver=SQL Server;server=server1;database=db2005;uid=serveruser;pwd=pwd1;

When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain)

SQLState: 28000

SQL Server Error: 18452

Login failed for user ". The user is not associated with a trusted SQL Server connection.

Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the 'Use Trusted Connection' is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the 'Use Trusted Connection' then login using the credentials above.

Need help on this one please. Thanks.

View Replies !   View Related
ODBC Error: SQLState 28000 && Server Error 18452
I have an application that connects to SQL 2000 thru ODBC. 


When our administrators login on the workstation the application
works well. But when ordinary users login they get the following error.

SQLState: 28000

SQL Server Error: 18452

Login failed for user ". The user is not associated with a trusted SQL Server connection.



I have restarted SQL Server and IIS to no avail.  We are using SQL and Windows auth mode.



Any ideas?  This just started this morning. 

View Replies !   View Related
Where To Put Error Routine When Using Cursor
Where is the proper place to put error trapping routines when using cursor
statement. Say for example you do below:

if @@error <> 0
error trap routine
start cursor routine
do process
close cursor

Will this placement trap any errors which occur during the cursor routine, or
should the error trap be inside the cursor
Thanks

View Replies !   View Related
Error Handling + Cursor
 

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 Replies !   View Related
Error On Update, But Not When Using Cursor
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 Replies !   View Related
OLE DB Error: OLE DB Or ODBC Error: You Do Not Have Permission To Run 'SP_TRACE_CREATE'.; 42000
I keep getting this error:
OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000
 
I do not want to add the login account to sysadmin role on my production server and giving the logon account db_reader rights to my database is not working. Is there another way around it.

View Replies !   View Related
Error: Cursor Not Returned From Query
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 Replies !   View Related
Invalid Cursor State Error
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 Replies !   View Related
Error: Could Not Create An Acceptable Cursor.
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 Replies !   View Related
Error Creating Cursor Handle
Can someone please help me with this stored procedure? When
I execute it in ISQL/w, it works fine, but when one of my programmers
tries to execute it from another program (Delphi3) he receives an
error of “Error creating cursor handle”. Can someone tell me what
this means? I don't really know anything about cursors... how can
I be using them???

CREATE PROCEDURE EMPLOY_TEST
AS

declare @msg varchar(30)
declare @newnum int


insert dbo.employ_xref_id default values

SELECT @newnum=(max(Idcolumn+1))

from employ_xref_id
where IDCOLUMN between 0 and 9999
and not exists (select * from EMPLOY_XREF e
where e.EMP_ID=(idcolumn+1))

select @msg=""+convert (varchar(10), @newnum)
print @msg
GO

Thanks for any advice!!
Toni Eibner

View Replies !   View Related
Urgent: Cursor Already Open Error
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 Replies !   View Related
Cursor Error After Upgrade To SQL2000
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 Replies !   View Related
Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure:

Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000]
Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000]

The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?

View Replies !   View Related
Cursor Is READ ONLY.----HELP!!!--error 16929.
CREATE PROCEDURE RemoveDuplicates
AS

Declare c_Work Cursor For
SELECT storeid, stocknumber, invnumber FROM inventorytest
ORDER BY storeid, stocknumber, invnumber

-- declare variables for the columns
-- data types made up

Declare @storeid char(20)
Declare @stocknumber char(10)
Declare @invnumber char(12)

-- declare a duplicate set of variables now for working with

Declare @storeid_new char(20)
Declare @stocknumber_new char(10)
Declare @invnumber_new char(12)

-- declare a counter variable for Transaction Commits

Declare @Count int

Open c_Work
Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber

BEGIN TRANSACTION
While @@FETCH_STATUS = 0
BEGIN
If @storeid = @storeid_new
BEGIN
If @stocknumber = @stocknumber_new
BEGIN
If @invnumber = @invnumber_new
BEGIN
DELETE FROM inventorytest Where Current Of c_Work
SET @Count = @Count + 1
IF @Count = 1000
BEGIN
Set @Count = 0
COMMIT TRANSACTION
BEGIN TRANSACTION
END
End
ELSE
BEGIN
SET @invnumber_new = @invnumber
END
END
ELSE
BEGIN
SET @invnumber_new = @invnumber
SET @stocknumber_new = @stocknumber
END
END
ELSE
BEGIN
SET @invnumber_new = @invnumber
SET @stocknumber_new = @stocknumber
SET @storeid_new = @storeid
END
Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber
END

Close c_Work
Deallocate c_Work

COMMIT TRANSACTION

----When i execute this Sp i get "error 16929 cursor is READ ONLY" . It had worked a week ago and now it doesn't work. Can anyone help me out and its urgent. Thanks in advance.

View Replies !   View Related
The Cursor Does Not Include The Table ... Error?
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 Replies !   View Related
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
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 Replies !   View Related
Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !   View Related
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !   View Related
Microsoft Cursor Engine Error '80040e38'
Hi,

I have issue updating date field on table. Here is the situation.

I create a new record onto table:
dim fieldsArray(1)
dim valuesArray(1)
fieldsarray(0) = "sdate"
valuesarray(0) = date
fieldsarray(1) = "stime"
valuesarray(1) = FormatDateTime(now(),vbshorttime)

Recordset1.addImmediate fieldsarray, valuesarray

Then on 1 other page I go back to this record to update 2 other Date and Time fields:
Recordset1.fields.setValue "edate",date
Recordset1.fields.setValue "etime",FormatDateTime(now(),vbshorttime)

Recordset1.updateRecord

it is then that I get the error:

Microsoft Cursor Engine error '80040e38'
Row cannot be located for updating. Some values may have been changed since it was last read.

If I change the fields type to tinytext it is working otherwise nothing.

I'm fighting with this for the last 2 days...

PLEASE HELP...

Fabrice

View Replies !   View Related
Error Creating Cursor Handle ** Update **
***Original message:*** (see update below)
Can someone please help me with this stored procedure? When
I execute it in ISQL/w, it works fine, but when one of my programmers
tries to execute it from another program (Delphi3) he receives an
error of “Error creating cursor handle”. Can someone tell me what
this means? I don't really know anything about cursors... how can
I be using them???

CREATE PROCEDURE EMPLOY_TEST
AS

declare @msg varchar(30)
declare @newnum int


insert dbo.employ_xref_id default values

SELECT @newnum=(max(Idcolumn+1))
from employ_xref_id
where IDCOLUMN between 0 and 9999
and not exists (select * from EMPLOY_XREF e
where e.EMP_ID=(idcolumn+1))

select @msg=""+convert (varchar(10), @newnum)
print @msg
GO


***Update***

I have figured out that it is the 'select @newnum=(max(idcolumn+1))' that was causing the problem. Actually just the
@newnum portion. I've changed that to just 'select newnum=(max(idcolumn+1))', but how can I get this value to
be returned to the user without having the @newnum variable to use in a PRINT statement?

CREATE PROCEDURE EMPLOY_TEST with recompile
AS
begin

declare @msg varchar(30)


insert dbo.employ_xref_id default values

select newnum = (max(idcolumn +1))
from employ_xref_id
where idcolumn between 100 and 999
and not exists (select * from employ_xref e
where e.emp_id=(idcolumn +1))

end
GO

Thanks for any advice!!
Toni Eibner

View Replies !   View Related
Close And Deallocate Cursor In Error Routine
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 Replies !   View Related
Error 1429: A Server Cursor Cannot Be Opened...
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 Replies !   View Related
ODBC Error Returns Error, But Sql Is Fine
When I run some code through an odbc driver onto a SQL server 6.5 database, from vb an error is being returned along the lines of

22005 Syntax error converting 'string' to INT4

if I copy this code, either from vb or from sql trace, and run it in isql the code runs without any errors.

The only thing I have found is about NULLs so I have removed these from the code that is passed through?

Any more suggestions?

View Replies !   View Related
OLE DB Error: OLE DB Or ODBC Error: Class Not Registered.
I have been searching high and low for days and I am perplexed and appalled at the lack of information regarding what can not be an isolated incident.

We are trying to get an Analysis Services 2005 to process on a x64 server.  We have both versions of the Oracle client installed and .ora file in both places.  I have seen a couple of posts regarding this issue, but they are 2 years old and MS was supposed to have resolved the 'parentheses' issue long ago.

The test connection succeeds, and I can explore the data in the datasource view, but I cannot process the cube.  All I get is the "OLE DB error: OLE DB or ODBC error: Class not registered"

Microsoft Windows Server 2003 R2
Enterprise x64 Editon
Service Pack 2

Installed Oracle Client 10g and ODAC home

I tried a few things that were suggested in posts, but nothing has worked.  I tried the cmd line starting SSMS from progra~2 instead of (x86).  I tried renaming the directory with no parentheses.  This is the second time we've tried to get it to work in 2 years.  We'll probably give up again and stay with x86, um, because it works without all these silly mickey-mouse tricks.  Most of our relational DBs are ORACLE so this is a showstopper for us as far as trying to go x64.

Sorry for the attitude, but I'm really frustrated!

If anyone can tell me a way to resolve this (or that it can't be resolved), I'd really appreciate it!

View Replies !   View Related
Error: Table Schema Changed After The Cursor Is Declared
Hi,

I have a package which loads data from one sql server table to another. I am loading 15million records in that. Earlier I tested that package with smaller data (less than a million) and it worked fine. So, I put it in production to load that 15 million records. But strangely after loading over 1.5million records, the job aborted with error at destination. The log says 'the table schema has changed after the cursor is declared'. But there is no change made in both destination as well as source.

In my package I am using a 'OLEDB Source' to read data from a SQL Server table, using 'Script component' making some changes and loading data to a sql server table using 'OLE DB Destination'. Both source and destination are in same server, but under different schema.

Do you have any idea about the problem?

Thanks.

View Replies !   View Related
Merge Replication Subscriber Error: Invalid Cursor State
These errors occur at the subscriber. First, "The merge process could
not query the last sent and received generations" is generated, then
immediately afterwards, "invalid cursor state" is generated. We are
trying to pull a new subscription snapshot from the republisher. The
subscriber has been added and removed from the replusher's pull
subscriptions a number of times for testing. Now, we cannot get the
subscriber to re-sync with the republisher.

Any ideas?

View Replies !   View Related
ODBC Error
Hi there

Any one can help me !!!!!

If i add the user in the database userid table, it indicates the error as

follows (given user name is not added in the userid table due to this error)

[Microsoft][ODBC SQL Server Driver][SQL Server]
The updatelineage function requires 2 arguments
[Microsoft][ODBC SQL Server Driver][SQL Server]
GETMAXVERS is not recognized
[Microsoft][ODBC SQL Server Driver][SQL Server]
The updatelineage function requires 2 arguments
[Microsoft][ODBC SQL Server Driver][SQL Server]
GETMAXVERS is not recognized

Thanks in advance

by
yasin

View Replies !   View Related
ODBC Error
Hi All,
I have a database using Access 2003 as a front end and SQL as a backend. I set up all permissions in SQL and linked all tables to the Access frontend. After putting the front end on the network for other users, they receive the following error: ODBC — connection to <name> failed. (Error 3151) What do I need to do please

View Replies !   View Related
ODBC Error
I recently rebuilt an NT 4.0 server, SP4, with IIS 4.0. The installation media and path that I used gave me ODBC version 3.50.33.30. Our web application works fine on another server that is identical except for ODBC version 3.510.3002.23. ODBC returns error : Procedure executed with 'EXEC'. No output parameters returned." in the first case.
Does anyone have an idea what is going here? And second, how can I find what version of ODBC comes from what source? It appears that ODBC is installed/upgraded from a multitude of sources.

Thanks in advance,
Ed Molinari

View Replies !   View Related
ODBC Error
I just set up a w2k server running SQLServer 2000.

I can connect to the server using the enterprise manager and make changes.

However if I try to create an ODBC connection connection or connect using the Query Analyser I get a timeout.

This even has the DBAs I know stumped.

View Replies !   View Related
ODBC Error Help!!!
Odbc Driver Error!!!
An user receiving this error message
when running a financial application.

SQL Error!
Query Timed out--ODBC SQL Server Driver.
Timeout expired.

Any idea about this error?

View Replies !   View Related
The ODBC Error
Hi,

Does anybody know that ODBC error S1003: Program type out of range means? Thanks.

View Replies !   View Related
ODBC Error
We are using ASP to connect to a remote database running on SQL server 7.0. On our testmachine we get no trouble, maybe because the SQLserver and Webserver is running on the same machine. But when we upload our code to a webserver and make a copy of our database on a remote SQLserver we get this error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Seksjon'.

As we are working on a student project and our deadline is tomorrow, any help whatsoever would be of great assistance!!

View Replies !   View Related
Odbc Error For BCP
Hi,

I am using this command to bcp data out on D drive of the sql server in text file and I receive the following ODBC error message.

I used the same command from my (client machine) command prompt. It worked fine and created file on my local D drive.

Could you please suggest me what should I check?
Server is running on service pack 1.


Use master
exec xp_cmdshell 'bcp testdb..inv_master out d:inv_master_out.txt -c
-S testserver -U sa -P *******'


output
-------------------------------------------------------------------
SQLState = 08001, NativeError = 1326
Error = [Microsoft][ODBC SQL Server Driver]Client unable to establish connection
SQLState = 01000, NativeError = 1326
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).

(4 row(s) affected)

Thank you

View Replies !   View Related
Odbc Error
When I use the dsn to connect to the sybase system I get this error
The setup routines for the sybase system 11 odbc driver could not be loaded due to system error code 127.

It also gives the following message when I hit ok for the above message
Driver's configdsn,configdriver or config translator failed
could not load the setup or translator library.

Any pointer could be highly appreciated.

View Replies !   View Related
Odbc Error
hi,
i am getting following error, while connecting to sql server.
The specified table is existing in the database.

Unhandled database exception:
invalid object name 'table'

ODBC Retcode: -1
ODBC State: State:S0002,Native:208,ORigin:[microsoft]{odbc sql server driver][sql server]

Thank you
--Rk

View Replies !   View Related
ODBC Error
Error - SQL State HY000, SQL Server error 183, Microsoft ODBC Driver cannot generate SSPI context.

View Replies !   View Related
ODBC ERROR
I got this error message while I was trying to backup the last transaction log before I start restoring a database that is marked SUSPECT. SQL Server is running on Windows 9x. Please how can I handle it because I can't even restore the db. Thanks for your help!


Server: Msg 4060, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'Memphis. Login fails.

Connection Broken

View Replies !   View Related
ODBC Error
Hello everyone,

I am having a problem with and ODBC Connectin (Centura SQLBase). I have created my DNS and then use DTS to import my tables and data into SQL 7. All of the tables import fine but, when it starts to import data I get nothing except an error on each "copy data from table to table:

[Intersolv][ODBC SQLBase driver][SQLBase]09727 GCI NTI Not initialized

Is there anyone that might be able to help me?

Thanks so much in advance.

Kathrine

View Replies !   View Related
ODBC Error
Hi,
I am running a query which gives me an ODBC Sql Server Driver error:
Cannot sort a row of size 8130, which is greater than the allowable maximum
of 8094.

I was wondering if there is any workaround for this error? Is there a place where I can download the latest SQL Server drivers for SQL Server 7

Thank you.

-Jay

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved