24000 Invalid Cursor State. Prepared Statement
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.
I am not using / importing any dlls.
I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.
I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.
First time when I bind paramaters using SQLBindParameter it works perfect.
When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.
If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.
I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.
Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Invalid Cursor State
Hi, I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure. When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors. When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error. I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring. Any suggestions?
View Replies !
Invalid Cursor State
Hi, We have a 2 tier web application utilizing IIS 4.0 and SQL 6.5 on seperate servers. Occasionally, all attempts to reach the SQL server through ODBC from the web server return "Invalid Cursor State", and fail. I usually have to restart MSSSQLSERVER service to repair. This happens at what appears to be random. Any Thoughts? Ed
View Replies !
Invalid Cursor State
When I change the Identity in a table in EM (undependend on which DB and table) the result is: invalid cursor state. I have a productive server running SQL Standard version and an play area running SQL Personal. The funny thing is, when I run the update over QA it works without a problem. With EM the Profiler tells me an error 16954 after droping a table, and it start to roll back. I tried hunderts of things to make it work but with no success. Except one thing. I've reinstalled the Personal SQL, restored my DB's and everything works fine now. This happened first after installing the latest security update from Microsoft on the 10th of February. I don't want necessarily to reeinstall the SQL server on the productive server, because there are hundreds of Databases on it, and a lot of security settings. Any help is very appreciated.
View Replies !
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 !
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 !
Get &&"Invalid Cursor State&&" When Modifying A Table In SQL 2000
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state. SP3 has been applied to SQL Server 2000. Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
View Replies !
Statement Could Not Be Prepared
Hi! If I'm running a DTS with 2 connections(flat file source and SQL table destination) and a Data Driven Query Task, and then I get an error message saying "Stataments sould not be prepared. Line 2: Invalid syntax near "." Which code should I look for the error? The message says Line 2 but i don't know where to look. Thanks.
View Replies !
Associated Statement Is Not Prepared ???
Hi ... I got a job using Web Publishing to generate report based on the data returned from store procedure. Lately, I got some funny problem which never happen before. ************* Error Message ************* SQL Web Assistant: Could not execute the SQL statement. [SQLSTATE 42000] (Error 16805) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed. After I got an alert, I used SQL Query Analyser to run the same store procedure. It works okay. Anyone know what happen and what I should do in order to get it work ? Thanks in advance ...
View Replies !
Finding Prepared Statement Sql
Hi,I'm running SQL Profiler on an SQL Server 2000 database. I see that onestored procedure gets repeatedly executed having a handle of '1'. Thisquery takes a long time to complete.How do I find what the text of the stored procedure is? I cant see anyhandle being created (using sp_prepare) with an id of '1' in theprofiler. Is there any way to force the server to re-prepare allstatements so that I can see the statement text and its preparation inSQL Profiler?Cheers,Birju
View Replies !
Prepared Statement Where Value IS Null Or Value = :x
Hello,I have a table which contains some nullable columns. I want to write asingle query, which can be prepared (ie. prepared statement), that canhandle null or non-null values for the where clause. Is this possiblein a standard-conforming manner?The simple for of the query is this:SELECT * FROM <table> WHERE <column> = <value>But when the value to be matched on is NULL, the syntax of the querymust change to be:SELECT * FROM <table> WHERE <column> IS <value>In the second case <value> is NULL.I know one option might be to change the ANSI NULL handling option,but I am loathe to do this (I have five RDBMS's to support, not justMSSSQL).I thought I might have been able to cheat using an IN clause to makethe SQL consistent, but no luck.ThanksKevin
View Replies !
ODBC Prepared Statement And Triggers
I have an application which uses ODBC to insert, update, delete records in an MSSQL DB. The application uses prepared statements and keeps the odbc statement handles to execute the same statement multiple times. This application works fine until triggers are introduced. The triggers are pretty simple and take the inserted, updated record and insert this information into another table. If we change the application to not re-use the prepared statement handle, by closing the statement handle after each execute, it also works fine. (However, this results in bad performance.) The error message is: "[ODBC SQL Server Driver]Connection is busy with results for another hstmt". If anyone has any information on this, I would greatly appreciate the help!!! Thank you. Christine
View Replies !
Prepared Statement Vs. Management Studio
Hello Folks, I have a problem with a prepared statement against SS2005 SP2 that I do not fully understand. I have a query that is prepared and executed from a java application that looks similar to this: SELECT TOP 1 str.ID FROM bigtable1 big1 LEFT OUTER JOIN bigtable2 big2 ON (big1.ID = big2.ID) LEFT OUTER JOIN htable h ON (big2.HID = h.HID AND h.DIRECTSW=1) WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? )) AND big2.COMPLID <> 2 AND big2.COMPLID <> 3 AND ISNULL(big2.date1, big2.date2) BETWEEN ? AND ? When run as a prepared statement this runs in about 27 seconds. When run under the query component of Management Studio it runs in about 1 second. Before each run I am clearing the buffers and the prepared statement cache. When running these I have looked at the execution plan from profiler and they both look the same. If I change the WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? )) to INNER JOIN it runs ~1 second in both environments. Any pointers would be welcome to explain the disrepency. Thanks, Mark
View Replies !
Lookup Component Error: Statement(s) Could Not Be Prepared.
Hi all, I recieve an error when I use the Lookup component in SSIS that reads: Statement(s) could not be prepared. I'm using a SQL 2005 DB as the source which runs into a lookup table and is use to compare records with an SQL 2000 Database. I've created connection managers successfully to both these databases. When trying to use the results of an SQL Query for the lookup to the SQL 2000 database (which is a linked server) and I try to map the columns, the error pops up and exits out of the lookup properties Window The details to the error read: Program Location: at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.PrepareSQLStatement(String sql, Boolean bypassPrepare) at Microsoft.DataTransformationServices.Design.DtsConnectionCommonControl.CheckSqlQuery() I'm looking to use the results of this comparison to output in some form of a report. Ideas would be greatly appreciated!
View Replies !
Prepared Statement Doesn't Insert String
Hi, I am facing a problem while using pepared statement for inserting data in MS-Access db. Code Snippet private const String CONN_STR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\TestDb.mdb"; conDatabase.Open(CONN_STR, "", "", -1); String sQuery = "INSERT INTO " + sTableName + " ([FileName],[IsCopied],[Size]) VALUES (@FileName,@IsCopied,@Size)"; cmdPrepStmnt.ActiveConnection = conDatabase; cmdPrepStmnt.CommandText = sQuery; cmdPrepStmnt.CommandType = CommandTypeEnum.adCmdText; cmdPrepStmnt.Prepared = true; int iCount = 0; foreach (FileInfo f in fInfo) { Console.WriteLine("Adding File " + ++iCount + " of " + fInfo.Length + "; " + f.Name); Parameter p1 = cmdPrepStmnt.CreateParameter("@FileName", ADODB.DataTypeEnum.adVarWChar, ParameterDirectionEnum.adParamInput, 255, f.FullName); Parameter p3 = cmdPrepStmnt.CreateParameter("@IsCopied", ADODB.DataTypeEnum.adBoolean, ParameterDirectionEnum.adParamInput, 10, false); Parameter p5 = cmdPrepStmnt.CreateParameter("@Size", ADODB.DataTypeEnum.adInteger, ParameterDirectionEnum.adParamInput, 10, f.Length); cmdPrepStmnt.Parameters.Append(p1); cmdPrepStmnt.Parameters.Append(p3); cmdPrepStmnt.Parameters.Append(p5); cmdPrepStmnt.Execute(out objAffected, ref obj, 0); } Code Snippet FileName is coming as blank in db, while in debug mode i can see that it is being set with a proper filename. The other 2 params are inserting fine. The FileName field is set as Text field with width length 255, and the incoming data is always shorter than 255 chars. The insertion happens without any errors/exceptions. I have also tried replacing the data for filename with a string like "Test", still it is inserting blank string. Please help.
View Replies !
Prepared Statement ......... Expects Parameter @CORP_NAME, Which Was Not Supplied
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me... private void Submit1_ServerClick(object sender, System.EventArgs e) { erkaner = Page.Session.Contents["CORP_ID"].ToString(); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString())); if (DropDownListUlke.SelectedIndex == 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM")); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString())); if (CheckBox1.Checked) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları ")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text)); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, "")); sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" + " @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" + "OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" + "_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" + "R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" + "R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" + "PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" + "TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" + "6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID"; sqlUpdateCommand1.Connection = sqlConnection1; sqlUpdateCommand1.Connection.Open(); sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1); myCommand.ExecuteNonQuery(); Page.Response.Redirect("main.aspx"); }
View Replies !
Statement(s) Could Not Be Prepared. Error 8180 With Mobilink Synchronization
Hello, I am trying to get my mobilink aplication to synchronize with MS SQL Server 2000. After getting rid of the global variable references I was using from ASA that the synchonization was failing on, it is now failing on the 8180 error. I am not sure what it is not liking. I set the begin connection script to be blank as before it had "CREATE VARIABLE @UserID VARCHAR ( 128 )", as I found no easy way to delete the script sets with ISQL. Microsoft says it has a hotfix for this particular error code, but I am not interested in calling their tech support line and paying to get it. Any ideas? The log from the Mobilink server says: I. 03/05 11:24:07. <thread 1.4>: Working on a request I. 03/05 11:24:07. <thread 1.4>: Synchronization request from: JM (version: 50data) I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'busler'. (ODBC State = 01000, Native error code = 5701) I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (ODBC State = 01000, Native error code = 5703) I. 03/05 11:24:07. <1.4> [JM]: ODBC isolation set to: Read Committed I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: begin_connection E. 03/05 11:24:08. <1.4> [JM]: Error: ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (ODBC State = 42000, Native error code = 8180) I. 03/05 11:24:08. <1.4> [JM]: Error Context: User Name: JM Modified User Name: JM Transaction: begin_synchronization Table Name: NULL I. 03/05 11:24:08. <1.4> [JM]: Script Version: 50data Script: End of Error Context I. 03/05 11:24:08. <1.4> [JM]: Warning: [10010] No error-handling script is defined. The default action code (3000) will decide error behaviour. I. 03/05 11:24:08. <1.4> [JM]: ROLLBACK Transaction: begin_synchronization I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: end_connection I. 03/05 11:24:08. <1.4> [JM]: Disconnected from consolidated database I. 03/05 11:24:08. <1.4> [JM]: Synchronization failed
View Replies !
[Microsoft][SQLServer 2000 Driver For JDBC]Statement(s) Could Not Be Prepared
When we are using the Microsoft SQL Server 2000 thin driver, we get the following error. java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Statement(s) could not be prepared. at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source ) at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest .processReplyToken( Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.getRow (Unknown Source) at com.microsoft.jdbc.sqlserver.SQLServerImplResultSe t.positionCursor(Un known Source) at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source) at DBConnection.main(DBConnection.java:56) The Query due to which this error was occuring is given below. SELECT CP_CD,COMPANY_NAME_J,COUNTRY_NAME_J,CP_NAME,CP_NAM E_J, (CASE RESIDENT WHEN 'Y' THEN '‹??Z' WHEN 'N' THEN '”ñ‹??Z' END ), (CASE BANK WHEN 'Y' THEN '‹â?s?i‹à—Z‹@ŠÖ?j' WHEN 'N' THEN '‚»‚Ì‘¼' END ), (CASE RISK_BCD WHEN '1' THEN '‹â?s' WHEN '2' THEN '?ØŒ”' WHEN '3' THEN '?¶•Û?E‘¹•Û' WHEN '4' THEN 'Ž–‹Æ–@?l' WHEN '5' THEN 'ŠO?‘?_•{' WHEN '0' THEN '‚»‚Ì‘¼' END ), (CASE RISK_ACD WHEN '1' THEN '’†‰›?_•{‚¨‚æ‚Ñ’†‰›‹â?s' WHEN '2' THEN 'Ž©?s—a‹à“™’S•Û' WHEN '3' THEN '?‘?Â’S•Û' WHEN '4' THEN '?‘?ÛŠJ”_‹â?s' WHEN '5' THEN '–¢Žg—p' WHEN '6' THEN '–{–MŒö‹¤•”–å' WHEN '7' THEN '–{–M‹â?s‹y‚Ñ–{–M?ØŒ”‰ïŽÐ'WHEN '8' THEN 'OECD?”?‘‹â?s‹y‚ÑOECD?”?‘?ØŒ”‰ïŽÐ?A‚Ü‚½‚Í‚»‚Ì‘¼‚Ì‹ â?s‚Ì’ZŠú?–±' WHEN '9' THEN 'OECD?”?‘‹â?sˆÈŠO‚Ì‹â?s‚Ì’·Šú?–±' WHEN '11' THEN '’n•ûŒö‹¤’c‘Ì' WHEN '10' THEN '‚»‚Ì‘¼' END ) , (CASE RISK_CLASS WHEN '1' THEN '–{–M' WHEN '2' THEN 'OECD?”?‘' WHEN '0' THEN '‚»‚Ì‘¼' END ), (CASE SUBSIDIARY WHEN '1' THEN 'Œ»’n–@?l' WHEN '2' THEN 'ŠCŠO“X' WHEN '0' THEN '‚»‚Ì‘¼' END )FROM CP LEFT OUTER JOIN COUNTRY ON CP.COUNTRY_CD = COUNTRY.COUNTRY_CD LEFT OUTER JOIN COMPANY ON CP.COMPANY_CD = COMPANY.COMPANY_CD ORDER BY CP_CD ASC Note : ==== The Same Query when run in SQL Server 2000 Query Analyser is working fine and also in ORACLE 9i. Please tell us where is the problem ?
View Replies !
The State Information Is Invalid For This Page And Might Be Corrupted
i know many people are having this error problem and i hope that anyone can solve this for me... the link below is my program http://www.driveway.com/x2b8l2x6a9 once you downloaded execute the readxml.aspx page. click on the dropdown list and select "~/templates/template2.html" the page will be loaded now try "~/templates/template1.html" you will see "The state information is invalid for this page and might be corrupted" any idea how i can resolve this ?
View Replies !
Error: 17805, Severity: 20, State: 3 Invalid Buffer Received From Client.
Howdy Folks, Our site has been experiencing this issue for a couple of months now.. Hopefully someone else can assist, as I’ve got to a point where I think issue lies within the application or a Microsoft bug. Web searches have reveled a number installations that have also had this error but its has not revealed an actual fix. I understand this error code basically means data being returned to the client is getting either corrupted or the data is too large to fit into the buffer on the client side. Client in our case is the terminal server, here after called a application server As the user base has increased form 5 to 20, I have noticed that the issue is occurring more frequently in comparison to when the company first started using the application /database. Its just about daily now... The db server also has 8 other db's residing on it - but they are all less than 300 meg. The attached PowerPoint doc has the trace info & the surrounding code for each process that has suffered a buffer error over a period of a week. DB Server Environment: Win2000 SP4, SQL2000 SP3a, MDAC 2.7 SP1 refresh. Application Environment: Written In house in VB.NET Framework 1.1 – setup as a published app on a terminal server – running windows 2000 SP4 Common features of the issue, that have been omitted from trace output for visibility reasons are: -Involves 1 particular DB (thats accessed by VB app) & its developement db -All connections are coming from 1 application server -Issue does not relate to any particular site connecting to the application server using this application I have reviewed & fixed several potential server side causes but this error still is occuring with in the environment: MDAC versions must be common on application & server: - Removed from equation by updating MDAC version on App server to be the same as DB Server: 2.7 SP1 Refresh Network related - Considered unlikely; we are also not experiencing network card errors on either server; also all db's would be experiencing connectivity errors. Which leaves us with Application related options to review: - Compilation error of application - App parameter definitions to stored procedures are the correct data type - Ensure values being passed do not exceed 4000 characters as that has also been known to create this error message - I've asked the developer to review MS KB 827366 article, as it may be a valid test - In regard to MSKB 832977 - I am not using pssdiag & have a later version of the MS Analyzer. But what I thought was interesting, is the statement that this problem occurs more frequently when an application submits a large remote procedure call (RPC) input buffer, especially when the RPC input buffer is greater than or equal to 8 KB. However, this problem may occur even if the input buffer is less than 4 KB - A is using datatypes varchar & char - not nvarchar & nhar Any assistance with this issue would be appreicated as server performance is being effected - these processes hang around for 1 - 5 mins before terminating (refer duration times in the powerpoint traces) Thanks In advance Suze.
View Replies !
Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5
hi I have a float column . only in cursor I get a type missmatch. does any one knows it ? the error Msg 8114, Level 16, State 5, Line 14 Error converting data type varchar to float. the code DECLARE @RON_FLOAT FLOAT DECLARE RON_CURSOR CURSOR FOR SELECT RON_FLOAT FROM RON_TABLE1 OPEN RON_CURSOR FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'VALUE IS ' + @RON_FLOAT FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT END CLOSE RON_CURSOR DEALLOCATE RON_CURSOR the code for the table CREATE TABLE [dbo].[RON_TABLE1]( [RON_FLOAT] [float] NULL, [RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL )
View Replies !
Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View Replies !
Invalid Udate SQL Statement DOES NOT Cause Error... Does Anyone Know Why??
Here's my update statement:UPDATE Item1SET reviewloop = 1, currentreviewstate=5WHERE itemid in(SELECT itemid FROM Item2)The thing is: the table Item2 DOES NOT HAVE a field called itemid.So, I should receive an error, right? Not so.Instead, every singlerecord in Item1 was updated.Does anyone know why SQL Serverr does not trown an error???Thanks guys,-Silvio Souza
View Replies !
Invalid Insert Statement In The Function
Dear Friends. I m trying to use the insert statement with in the function ! and i m getting this errror ! Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16 Invalid use of 'INSERT' within a function. Please help me how to rectify it and how i can use the Insert statement with in the function ! Here is the code for the function. create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as begin Declare @valCost integer Declare @TotService integer Declare @TotParts integer Declare @TotLabour integer Declare @TotTravel integer Declare @TotSubContract integer select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1) return (@valCost) end
View Replies !
PRINT Debug Messages And CURSOR In Stored Procedure Confuses DTS; &"Invalid Pointer&"
I have the following stored procedure that is called from the source ofa transformation in a DTS package. The first parameter turns on PRINTdebug messages. The second, when equals 1, turns on the openning,fetching, and closing of a cursor. Things are fine if only one of thetwo parameters was set to 1.When run with both parameters turned on, "dba_test_dts 1, 1", DTSsource (tab) preview fails because it thinks no row was returned. "Norowset was returned from the SQL statement". Understanbly then thetransformation step would also fail with the "Invalid Pointer" error.As you'd see, I have SET NOCOUNT ON in the code. Has anyoneexperienced this? Is this a known bug? This occurs in SQL Server 2000running on Windows Server 2003.-----------------------------------------------------------------------------------------------------------------CREATE PROC dba_test_dts ( @debug BIT = NULL, @cur BIT = NULL )AS-- Always have these 2 options set or unset so DTS would not error out-- with the Invalid Pointers message.SET NOCOUNT ONSET ANSI_WARNINGS OFFDECLARE @FMT_FILE_NAME VARCHAR(256)DECLARE @OUTPUT_FILE_NAME VARCHAR(256)DECLARE @emp_id INTDECLARE @lastname VARCHAR(70)IF ( @debug = 1 )BEGINPRINT '=== BEGIN ==='PRINT 'Stored Procedure dts_calling_stored_proc'PRINT 'Begin timestamp: ' + CONVERT(VARCHAR(32), CURRENT_TIMESTAMP,109 )PRINT 'Server : ' + @@SERVERNAMEPRINT 'Host Name/ID : ' + HOST_NAME() + '/' + CONVERT(VARCHAR(32),HOST_ID())PRINT 'Database : ' + DB_NAME()PRINT 'User/NT User : ''' + USER_NAME() + '''/''' + SYSTEM_USER +''''PRINT 'DEBUG FLAG : ' + CONVERT( CHAR(1), @DEBUG )PRINT '=== BEGIN ==='PRINT SPACE(1)ENDIF ( EXISTS ( SELECT 1 FROM sysobjects WHEREid=object_id(N'Employees_temp') ) )DROP TABLE Employees_tempCREATE TABLE Employees_temp(emp_id INTEGER, lastname VARCHAR(70))INSERT INTO Employees_temp([emp_id],[lastname])SELECT EmployeeID, lastnameFROM EmployeesIF ( @cur = 1 )BEGINDECLARE curEmp CURSOR FORSELECT emp_id, lastnameFROM Employees_tempOPEN curEmpFETCH NEXT FROM curEmpINTO@emp_id, @lastnameWHILE ( @@FETCH_STATUS = 0 )BEGINFETCH NEXT FROM curEmpINTO@emp_id, @lastnameENDCLOSE curEmpDEALLOCATE curEmpENDSELECT emp_id, lastnameFROM Employees_tempGO
View Replies !
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
How To Capture The Value For A CURSOR Statement
Hi everyone, The following snippet of code returns something like that: string;string1;string2 Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task) Thanks a lot, declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
View Replies !
SQL Statement Not Producing Any Error For Invalid Column In A Table
Hi, I am using SQL Server 2005 with SP1 patch update.I have tow tables X table fields: ClientID,ClientName,ClientRegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings Y table fields: ClientID,ClientName,RegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings If i run a query for X table: SELECT RegisteredNumber FROM X it produces the error like this Msg 207, Level 16, State 1, Line 1 Invalid column name 'RegisteredNumber'. But if i run the query for X,Y table: SELECT * FROM Y WHERE RegisteredNumber NOT IN (SELECT RegisteredNumber FROM X) It's not producing any errors. Why this? Is this the SQL Bug or my query problem? Can anyone explain how to solve this? Balaji
View Replies !
[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions) Code: declare @sql char(100) set @sql = 'select ma_kh, ten from _khang' Now, I want to create a cursor to recalculate some values I've tried: Code: declare cur_T cursor for exec(@sql) open cur_T but it doesn't work. Can I have another way to do that???
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
Components Design State Or Running State
Hi, I am doing a custom destination transformation, How would I know wether the Destination component is in Design state (getting edited by BIDS) or wether the component is getting executed(by BIDS or DTEXEC) Is there any property or ENUM by name DesignState. Thanks Dharmbir
View Replies !
What Need To Be Prepared To Export A Database?
I want to export a database from localhost anh import it to hosting server. Which version of SQL database server I need to install to have the export feture? Also I would like to know which version of SQL database server can be installed on Windows XP and Vista? I have nightmare to restore my database to the godaddy hosting envirenment because the host server does not allow to restore the database that was backup from localhost. Please help me to solve this problem. Thank you,
View Replies !
View List Of Prepared Statements
Is there a way to retrieve a list of the currently prepared statements? I am trying to understand the workings of an application but when I do a trace to find out what the application is doing to the dbs it shows lots of "exec sp_execute 4, 4, 2" lines. How do I find out what these exec statements are actually doing? TIA Nick
View Replies !
Execution Plan Caching For Prepared Statements
I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise. When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters. I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen. The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms). Please comment and advise, Thanks
View Replies !
SQLDescribeParam With Subselect: Invalid Parameter Number/Invalid Descriptor Index
Hello, I've got the following query: SELECT zA."ID" AS fA_A , zA."TEXT" AS fA_B , ( SELECT COUNT(zC."ID") FROM Test."Booking" AS zC ) AS fA_E FROM Test."Stack" AS zA WHERE zA."ID" = ? With this query I call: - SQLPrepare -> SQL_SUCCESS=0 - SQLNumParams -> SQL_SUCCESS=0, pcpar = 1 - SQLDescribeParam( 1 ) -> SQL_ERROR=-1, [Microsoft][ODBC SQL Server Driver]Invalid parameter number", "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index" Is there a problem with this calling sequence or this query? Or is this a problem of SQL Server? Regards Markus
View Replies !
|
TRACKER
