Moving From 2000 To 2005: Issue Accessing Cursor Returned By Sp
I have a number of triggers that call a stored procedure that returns a cursor. The triggers then use the results of this cursor to do other actions.
My problem is that this works fine in SQL2000 but just won't work in SQL2005. When I try to access the results of the returned cursor, I get an error -2147217900 could not complete cursor operation because the set options have changed since the cursor was declared.
If I port the code contained in the sp into the trigger, it runs fine. But having to port over the sp's code defeats the whole concept of being able to re-use the sp.
Does anybody have any ideas of what could be going on?
I look forward to a quick response.
Dennis
View Complete Forum Thread with Replies
Related Forum Messages:
Mdac 2.8 And Sql 2005 Cursor Performace Issue
HelloI have a VB6 application using classic ado (MDAC 2.8) for connectingms sql 2000 server. Application uses a lot of server side cursors. NowI want to switch to ms sql 2005 server but I have noticed very seriousperformance problem. Sql profiler results of execution of followingcommands:declare @p1 intset @p1=180150131declare @p3 intset @p3=1declare @p4 intset @p4=16388declare @p5 intset @p5=22221exec sp_cursoropen @p1 output,N' Select ... from ... where .... orderby ...',@p3 output,@p4 output,@p5 outputselect @p1, @p3, @p4, @p5on sql server 2000:CPU: 234Reads:82515Writes:136Duration:296and on sql server 2005:CPU: 4703Reads:678751Writes:1Duration:4867Both databases are identical, the servers runs on the same machine(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forumsI've read that Microsoft doesn't recommend using server side cursorson sql 2005 but is there any way to increase performance to someacceptable level?thanks in advanceszymon strus
View Replies !
Mdac 2.8 And Sql 2005 Server Side Cursor Performace Issue
Hello I have a VB6 application using classic ado (MDAC 2.8) for connecting ms sql 2000 server. Application uses a lot of server side cursors. Now I want to switch to ms sql 2005 server but I have noticed very serious performance problem. Sql profiler results of execution of following commands: declare @p1 int set @p1=180150131 declare @p3 int set @p3=1 declare @p4 int set @p4=16388 declare @p5 int set @p5=22221 exec sp_cursoropen @p1 output,N' Select ... from ... where .... order by ...',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 on sql server 2000: CPU: 234 Reads: 82515 Writes: 136 Duration: 296 and on sql server 2005: CPU: 4703 Reads: 678751 Writes: 1 Duration: 4867 Both databases are identical, the servers runs on the same machine (Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums I've read that Microsoft doesn't recommend using server side cursors on sql 2005 but is there any way to increase performance to some acceptable level? thanks in advance szymon strus
View Replies !
SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this. Below is the exception from my log file The cursor type/concurrency combination is not supported. com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source) The following is the piece of code where the problem I'm assuming is happening, how can I correct it. varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); Have tried using both JDC v1.1 and 1.2 but of no use.
View Replies !
Moving 2000 To 2005 Box
Hey guys I need to move a 2000DB to a 2005 just the database and the logins Any suggestions of the best way to do this.... DTS backup attach database Use the Upgrade Advisor....
View Replies !
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 !
Problems Accessing SQL After Going From SQL 2000 To Mgmt Studio SQL 2005
I installed Mgmt Studio and SQL 2005 on a new server - I imported my complete old SQL 2000 databases - then shut down my Original SQL 2000 server and used that Idenitical IP as the Mgmt Studio SQL 2005's IP I am using Linux Jakarta Tomcat v5.0.27 and all SQL references are referring only to the IP number of ther server and not using DNS - Originally I was getting an error when my system tried to access the SQL stating that it was an untrusted connection - I then realized that the IP number referred to was not the primary IP and I changed that so the it was. Both the Linux and SQL 2005 are now using network IPs and then I got a message that the password that I was using was not strong enough so I changed that as well - then all my logging dissappeared When I try to connect now there is no indication in any log (SQL nor Windows) that I am attempting to connect - the website just hangs for like 3 minutes and fails the login stating that the username/password is incorrect (or it could not verify it) even though I changed nothing on the Tomcat side and it was logging before the IP werent matching. Is there a significance difference between SQL 2000 and SQL 2005 that will not allow my Tomcat to connect? Few Addl Notes I can ping to and from each server I can telnet from Linux to SQL 2005 Linux of course is not part of my Windows 2003 Domain but it is on the same network Any and all help would be greatly appreciated
View Replies !
Moving Reports From 2000 To 2005
Greetings, I have an installation of SQL Server Reporting Services 2000 which already contains several published reports connected to a 2000 database. I need to transfer/move these reports to SSRS 2005 on a new server which uses a 2005 DB. So that there are no mistakes... no, I don't want to move the entire Reporting Services instance and no, I don't want to move the database either. All I need to do is to get the published reports over to SSRS 2005. Is there a way to get the published 2000 reports back to RDL files... or is there an easier way to do this? Thanks in advance...
View Replies !
HELLLP !!! Moving From Sql 2000 To 2005
Hi , I am working on a DWH project, and we decided few days ago to move from 2000 to 2005. we installed the 2005 on the same server with two different instances for testing the 2005. I migrate everything with the indexes but the issue is that queries at the 2005 take much more time than the same query on the 2000. for example i have a nasty qry which join 14 tables. at the 2000 its take around 2.5 - 3 mins and at the 2005 its infinity , they both have the same indexes. PLS ANYONE HAVE ANY IDEA WHATS THE PROBLEM ?
View Replies !
Moving DB From Sql Server 2000 To 2005
Hello, what is the easiest way to move a DB (tables and data) from SQL server 2000 to 2005. Ive been trying to export the DB tables from 2000 using Excel, yet i get "cannot expand named range" errors on several of the tables. Is excel the way to go? another way? Can i use backup/restore? Note: also moving from one machine to another. Thanks for any help.
View Replies !
Moving Data From SQL 2005 To SQL 2000 Daily
We need to upgrade our HR system from SQL 2000 to SQL 2005. Support for SQL 2000 ends 12/31/2007. We have a third party vender that do not currently support SQL 2005. Employee's data is transfered from our HR database to the third party database for the purpose of yearly performance evaluation on a daily basis. Both databases are currenlty on a SQL 2000 server. Can data be transferred from a SQL 2005 database to a SQL 2000 database?
View Replies !
Moving CRM, SPS, Project Web Server, SQL 2000 To SQL 2005
I am reciving a new powerful database server My old database server runs SQL 2000 and has the CRM 3.0 app and database, our corporate web site (SharePoint Services 3.0) database, and Project Server 2007 database. The corporate site and project apps and web sites reside on our file server. I want to move, upgrade or migrate the databases, corporate site and Project site to this new SQL 2005 server. I am a little unsure the best way to do this. Should I move all the database first, then move the apps? Should I do a restore of everything on the new server, and after I know it works just turn off the other server? Am I missing a better way? Anyone have any insight, let me know. Thanks, Ken
View Replies !
Accessing Global Cursor
hi friends, Here is the stored procedures that I used. ------------------------------------------------------------------------------------ create procedure globalCursor AS DECLARE abc CURSOR GLOBAL FOR select * from sales OPEN abc create procedure globalCursorTest AS DECLARE @sdate datetime DECLARE @sperson varchar(15) DECLARE @sregion varchar(15) DECLARE @sales int EXECUTE globalCursor FETCH NEXT FROM abc INTO @sdate, @sperson, @sregion, @sales print @sdate print @sperson print @sregion print @sales ------------------------------------------------------------------------------------ When I execute globalCursorTest using SQL Query Analyser, it says ------------------------------------------------------------------------------------ Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4 A cursor with the name 'abc' already exists. Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5 The cursor is already open. ------------------------------------------------------------------------------------ how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem. Jake
View Replies !
DB Accessing Problems After Moving The DB
Hi guysI just moved my system from a workstation to a notebook. So far so good. But that's the beginning of my trouble. I backed up all databases I need for my developing work installed the SQL Server (Dev Edition) on the notebook an restored the databases. Following to that I enabled the Shared Memory, the TCP/IP and the Named Pipes for the Instance. When I now try to run an ASP.Net Website using one of my databases I'm getting this error message:Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'DOMAINuser.name'. Additionalliy the log (you can find it under SSMS->Management->SQL Server Logs) reports following:Error: 18456, Severity: 14, State: 16 what means that the incoming user does not have permissions to log into the target database. I checked this out by logging the user into some other database (master) and then tryed using the USE DATABASE command to switch to the target database to get a better error message:Msg 911, Level 16, State 1, Server ComputerName, Line 1Could not locate entry in sysdatabases for database "DatabaseName". No entry found with that name. Make sure that the name is entered correctly.Do you guys have any idea what I can do?BTW: The notebook has two NICs. I don't know if has something to do with that.
View Replies !
Moving Sql 2000 DTS Packages To Sql 2005 Standard Edition
I have been tasked with upgrading around 150 SQL Server 2000 DTS packages to SSIS in SQL Server 2005 standard edition. I made a backup of the 2000 database upon which the DTS packages operate and restored it to the SQL 2005 server. So far, so good. I have the database in place. Now I need to get the DTS packages themselves into the SLQ 2005 server. I think I need to check my install and make sure that I have the SQL Server 2000 DTS services installed on the SQL 2005 server. I can do that. However, I wonder what would be the most effective way to physically get the packages from the SQL 2000 server to the SQL 2005 server. Should I use structured storage files? If so, how do I go about opening them in SQL 2005 in order to save them to SQL server 2005? I should mention that these packages make heavy use of ActiveX scripting so I am looking at rewriting them from scratch to be SSIS packages. I just need the packages on the SQL Server 2005 box so I can make sure I am creating exactly the same functionality in 2005 as existed in SQL server 2000. Each DTS 2000 individual package tends to be fairly simple and I think I can greatly improve the process by consolidating them. Thanks in advance for any advice. S. Wells
View Replies !
How To Accessing Multiple Oracle Cursor.
I have an SP which return multiple cursors in Oracle. How can i read the data from these cursors in SSRS and then form a relation between them to show them on a report. Is this possible in SQL server reporting server. What are the work around for this. The data base is Oracle 10G .
View Replies !
Newbie Question, Moving Data Between Sql Server 2000 And 2005
Hi, I'm a newbie on SSIS and am trying to grasp my way through this. I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database. What I want is to move the data to a staging table, then drop the main table and rename the staging table to the main table, to minimize the down-time of the data. I can't get the workflow to work, because the staging table has to exist when I run the package. I thought I could use an "Execute SQL" task to generate the table before I would run the task, but that doesn't work. Am I going about this the wrong way? Is there an optimal solution to this problem so my data can be accessible as much as possible. Regards, Atli
View Replies !
After Moving 2000 Databses To 2005 Error With Maintanance Plan
I have moved few databases in 2000 to a different server which run's on sql server 2005 and its not a instance ogf 2000. I backed up databases in 2000 moved those files to other server and then restored them. I had solved the problem of orphand users by deleting and adding again. Now when i try to setup maintenance plan i have the following error after walking through the wizard TITLE: Maintenance Plan Wizard Progress ------------------------------ Adding tasks to the maintenance plan failed. ------------------------------ ADDITIONAL INFORMATION: Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.MaintenancePlanWizard) ------------------------------ BUTTONS: OK ------------------------------ what do i need to fix this????Any help is appreciated... And what else do i need to make sure everything in 2005 are working fine apart from orphand user. Please let me know Thanks in advance
View Replies !
Error Connecting Database Problem After Moving Databases 2000 To 2005
I have moved my databases to 2000 to instance of 2005 on the same server. Now i set databases offline in 2000 as i did upgrade use backup & recovery method. & my connection string Password=test;Persist Security Info=True;User ID=test;Initial Catalog=databasename;Data Source=ipaddress i was using above connection string to connect when using 2000 databases form applications in the production machine I get the following error when i am running my applications now after moving to 2005 An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) IS it because i have two server on one server or What do i need to do in order to fix this....tried several thing by searching nothing worked out.. Let me know thanks
View Replies !
Moving Back To MSDE 2000 After Removing SQLServer 2005 Express
Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option. Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again. Any ideas on how to break the chain here are welcome.
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 !
SQL 2005 && SQL 2000 Issue
hey, I have three servers, lets say Svr1, Svr2 & Svr3. On all three i have sql2000 & sql 2005. On Svr1 & Svr2 sql2005 is default and sql2000 is an instance and they work perfect. However on Svr3 I have sql2000 as default and sql2005 as an instance, It seems to work fine but there is an issue when trying to create a new management plan in sql2005, i get an error saying =================================== Cannot show requested dialog. =================================== Unable to execute requested command. ------------------------------ Program Location: at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc) at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread() =================================== Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'. (Microsoft.SqlServer.Management.MaintenancePlanWizard) ------------------------------ Program Location: at Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm..ctor(XmlDocument doc, IServiceProvider serviceProvider) Has anyone else come accross this problem ? Any help would be fantastic, Thanks, Karl.
View Replies !
Concurrency Issue In Result Returned From Stored P
I am working on application developed in ASP.NET 2.0 that uses lots of stored procedures from the SQL Server 2000 backend database.When the app is under load you occasionally see incorrect data returned to the page after running stored procedures. All the stored procedures have been tested and appear to be fine. it's as if under load the returned data from the stored procedures is getting 'mixed' with another concurrent session. Please let me know what could be the reason behind it
View Replies !
In Place Upgrade Issue From 2000 To 2005
Hi All, Well basically I am doing an in place upgrade from SQL Server 2000 to SQL Server 2005 and it appears to freeze when running the msdb_upgrade.sql script. I have investigated the issue and it appears that I failed to run the sp_delete_backuphistory procedure before running the setup. OK so my question is this if I cancel the installation at this point what affect will it have on the installation and exactly where will it rollback to and what issues will I face. Also is there any way to truncate the tables during the intallation process as this will allow the process to finish much faster or is it best just to let it run through. Thanks in advance
View Replies !
User Issue After Upgrade From SQL 2000 To 2005
I set up a test server with Win2003 R2 and SQL 2000 Standard Ed., just like our production server and copied a couple of smaller databases to it. I'm testing an upgrade to SQL 2005 Standard. Went through the Upgrade Advisor. There were some minor issues that I resolved prior to the install. Ran the installation to upgrade (not migrate). Installation went fine. Installed SP2 from CD, as well. Again, no installation issues. When finished, I found that the test databases couldn't be used. I checked and found only the default users in the Security -> Logins. However, the databases still had all the users listed. All of the added SQL and Windows Domain users/groups were no longer there. By the way, we have set the security on SQL 2000 to be both SQL Server and Windows Authentication Mode to accomodate an older application. If I try to add the user in Security -> Logins and grant the rights/roles to the database, I get an error that the user already exists in the database. If I try to remove the user from the database, I get an error that they own a schema and can't be dropped. I can resolve that, but I was wondering if this issue will occur when I attempt this on the production server. There will be a lot more work re-establishing the server/database logins and roles. Is there a way to avoid this issue during the upgrade? TIA Rick
View Replies !
2000 To 2005 SQL Statement Order By Issue
Hi, I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is: ALTER VIEW dbo.SELECT_PP_END AS SELECT TOP 100 PERCENT PP_PERIOD_ID, CONVERT(VARCHAR, PP_END_DATE, 101) AS PP FROM dbo.PP_PERIODS ORDER BY PP_END_DATE DESC The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help - T.A.
View Replies !
SSRS 2005 To SQL AS 2000 - Issue In Connection
Hi, I'm connnectiong from SSRS 2005 to SQL AS 2000 using OLE DB provider for MSOLAP. It works find if I open the report in web-browser where SSRS service is present. Whereas, if i open the published report in a different PC other than the PC where SSRS service is hosted, I'm getting the below error. An error has occurred during report processing. Cannot create a connection to data source 'MBSROB-FinanceDM'. For more information about this error navigate to the report server on the local server machine, or enable remote errors It seems to be an issue on user id that is used for connection, but not sure...I don't see a way to check AS 2000 to find who is accessing (no profiling). Any help in this regard would be really helpful. Regards, Kart
View Replies !
Migration Issue From DTS 2000 To SSIS SQL 2005
Hi, Hi, I am trying to migrate DTS 2000 packages to SQL 2005 SSis using Package Wizard. When I am invoking and after the passing server information of source and destination, while reading the packages it is giving error TITLE: Microsoft SQL Server ------------------------------ This wizard will close because it encountered the following error: For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib) Could someone can help in this regards. As this is a important task for me and I am stuck on this which has to be resolved by end of today. Thanks -VD Venkatesh
View Replies !
Transactional Replication From 2005 To 2000 Issue
Hi, I setup transactional replication between 2005-> 2000 database for only one table. It works fine no problem. I checked replication monitor everything works well. My Subscription was Push Subscription on Publisher. This morning I restored main database at publication server, I saw all my publication configuration were gone. I then went to create a publication and push subscription again..and did it, But when I went to replication monitor. I checked that Snapshot are being created, but from distributor to Subscriber is saying below message? "The concurrent snapshot for publication is not available because it has not been fully generated.....lONG CHPPPED off meassage" Can any body tell me hwo to get over this issue? Many Thanks SKR
View Replies !
Msde 2000 - Sql 2005 Express Upgrade Issue.
Hey, This is my first time posting here but here goes nothing... Ok I upgraded an xp machine running to sql express 2005 by doing the inplace upgrade. The upgrade process went great, after the upgrade I could connect to the databases that had been upgraded using the management studio. BUT. When I try to connect to the databases using the third party software which had always worked in the past I get the error when trying to access from both the client and server machines. [DBNETLIB][Connection open()).]SQL Server does not exist or access denied. I then tried setting up SQL express 2005 on another xp station just to test how things worked if it wasn't an upgrade. I installed, attached the databases (which I copied to that local machine) and I was able to connect to them through the third party software from the server machine, but not client machines. The client machines gave me the same error as above. Does anyone have any ideas, I have been talking to the third party support but haven't gotten any where yet... does anyone have any suggestions? Thanks,
View Replies !
Issue Accessing File In Lan
Hi, I have a SSIS package which reads XXX.rpt file from a LAN share. This is a simple text file from a mainframe job. The package is stored in file system and invoked by a third party scheduler. The scheduler uses a specific NT account to run the package and the NT account has read access to the share. The SSIS package is not updating the rpt file in the share. It reads the rpt file and updates sql database. The issue is when the package is run, it returns the following error: Error: 2008-02-07 14:52:33.18 Code: 0xC001401E Source: Mycustom Data Extract Connection manager "MyText File" Description: The file name "\myserverdev est.rpt" specified in the connection was not valid. End Error I have verified that the NT account has read access to the LAN share. Does the account should also have write access? Thanks Saravanan Kanagaraj
View Replies !
Issue With Cursor
Hi I have created a cursor with the following syntax: "DECLARE costs_cursor CURSOR SCROLL DYNAMIC FOR SELECT RegNumber, FirstName, LastName, Assessment, Catering, Travel, Accommodation, Other from dbo.T_Course_Data ORDER BY LastName OPEN costs_cursor" which works. What I don't understand is why, when I attempt to update a value in the cursor, irrespective of whether I use the 'FOR UPDATE' option or not, I get the error message to the effect that the cursor cannot be updated because it is READ ONLY. Clearly (to my mind anyway) the cursor wasn't created as read only. My update statement is "Update dbo.T_Course_Data set Assessment='222' WHERE CURRENT OF costs_cursor" The odd thing is I have another cursor in my app using the exact same statements and it doesn't give this error. Please help if you can. Neil
View Replies !
Cursor Issue
Can someone tell me what's wrong with this cursor? if @senddisputed=1 declare x cursor for select email from dbo.Users where UserType='A' and email is not null open x fetch next from x into @email while @@fetch_status = 0 BEGIN insert into emails (transno,reqid,efrom,eto,subject,body,notified,emailtype) (select 0,0,@fromaddress,@email,'Disputed Transaction Notification', 'This Email is to alert you that you currently have '+ ltrim(str(count(distinct th.transno)))+ ' transactions in resolve that are under the status of declined. Please log into the system and review these records.' + char(13), null,8 from transhdr th join cards c on c.cardid = th.cardid join users u on c.mgrid = u.userkey join transdtl td on th.transno = td.transno join recon r on th.transno = r.transno where u.email is not null and th.reqid=1 group by u.email) fetch next from x into @email END close x deallocate x Results: Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 212 A cursor with the name 'x' does not exist. Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 215 A cursor with the name 'x' does not exist. Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 234 A cursor with the name 'x' does not exist. Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 235 A cursor with the name 'x' does not exist. --- http://www.ssisdude.blogspot.com/
View Replies !
Cursor Issue - Order Not Correct
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records. I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc. In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode). Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed. /*CREATE Sales_Table table */ CREATE TABLE [dbo].[Sales_Table] ( [count_id] [int] NULL , [org_id] [int] NULL , [postalcode] [varchar] (20) NULL, [sales] [numeric](18,6) NULL , [sales_rank] [integer] NULL, [org_criteria_input_date] [datetime] NULL ) ON [PRIMARY] insert into Sales_Table select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date fromorg_model_data omd join org o on o.org_id = omd.org_id where o.client_id = @ClientID and model_Criteria_id = 27 and org_criteria_value <> 0 order by omd.org_id asc, omd.org_criteria_value desc -- DECLARE CURSOR for Sales_Table declare SalesRankCursor CURSOR SCROLL dynamic FOR select org_id, sales, sales_rank from Sales_Table for update of sales_rank -- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values OPEN SalesRankCursor while exists (Select * from Sales_Table where sales_rank = 0) Begin FETCH NEXT FROM SalesRankCursor set @StoreNext = @StoreCurrent set @SalesRank = (@SalesRank + 1) update Sales_Table set@StoreCurrent = org_id where current of SalesRankCursor if @StoreCurrent <> @StoreNext begin set @SalesRank = 1 end update Sales_Table setsales_rank = @SalesRank where current of SalesRankCursor End CLOSE SalesRankCursor DEALLOCATE SalesRankCursor Any ideas?
View Replies !
Linked Server Issue: OLE DB Error Trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface Returned 0x80004005
Window Server 2003 R2 Standard Edition (x64) SP1 Sql Server 2000 8.00.2039 SP4 Enterprise Edition (32 bit version) A linked server is configured to a sql 2000 server and when I execute sql statement SELECT * FROM [LinkedServer].[Database].[dbo].[TableName] it gives following error message: - Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.]. In case if anyone has a solution to it, please let me know.
View Replies !
Pass Linked ServerName To A Cursor Issue...
Need to loop through a Cursor to linked server: ----------------------------------------------- Declare Cursor_Loop_serverName Cursor for select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID, cast(crdate as varchar(25)) crdate from ServerName_A.master.dbo.sysdatabases ***How could I pass @serverName to change the from to from @RemoteServer.master.dbo.sysdatabases? I have tried dynamic sql, it did not work after the Declare Cursor for... thanks for the help David
View Replies !
Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005
Hi I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions. I am getting the following error while accessing the linked server in management studio based on the scenario given below ; ------ Error Message Starts OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'abc'. ------ Error Message Ends Consider login name is abc. Now this login abc has sysadmin rights on sql server 2005. The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role. I have configured the linked server using the following options; 1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 . 2. I have also tried specifying remote login / password option. Anyone having any idea, would be of great help. Regards, Salman Shehbaz.
View Replies !
Moving Projects From Server To Workstation Issue.
Dear folks, I€™ve moved my server folder which contains about 10 dtsx and its ADO .net connection as well as OLEDB into my folder workstation. Then, when I€™m gonna to open them appears this error: Error 1 Error loading M_HAC_Modelo198_CECA.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Clave no válida para utilizar en el estado especificado.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:ssishacienda_anualM_HAC_Modelo198_CECA.dtsx 1 1 Translate into english: "Clave no válida para utilizar en el estado especificado" = Invalid key for to use in the specified state (more or less) What really happen? Well, I wrote these packages using a domain user (belong to Administrators group in Active Directory) via Terminal Server and now I want to modify them by mean my domain user (I already have got installed sql25k client tools and the stuff required) I thouht that these connections were saved in the same folder where dtsx, dtsproj, etc.. Solution? Create them again? I hope that it will be a piece of cake otherwise... Thanks in advance for any though or advice,
View Replies !
Conducting A Radius Search Via Postcodes - Cursor Issue?
Hi, I’ve developed the following stored procedure to query a table of services following a user search. The user can specify a radius from a certain location(UK postcode) in which to search, and its with this Im having a few problems. Here’s my method: STEP1 - I first check the table of services and insert all services matching the specified criteria (regardless of location) into a temporary table (#tMatches). STEP 2 - I get the postcode entered by the user and retrieve its co-ordinates. STEP 3 – I open a cursor and loop through the records in #tMatches. For each record, I first retrieve their co-ordinates and then use a bit of Pythagoras to work out whether its location falls within the radius specified by the user. STEP 4 – If the record falls outside the range specified by the user, I deleted from #tMatches. STEP 5 – I bind the remaining (matching) records from the #tMatches to a dataset. Now, I think that seems pretty simple, and the easiest way to achieve what I’m trying to do. How then when querying just 600 records am I finding it times out? I think I’ve pinned it down to the STEP 3 (cursor), but not being the SQL expert I’d like to be, I’ve no further ideas where I’m going wrong? Any ideas? The following is an excerpt from the script - don't worry about the fact several parameters may not be declared. Many thanks ====================================== --STEP 1 CREATE TABLE #tMatches ( record_uid uniqueidentifier, service_name varchar(500), service_address varchar(500), service_description varchar(500), GRE integer, GRN integer ) BEGIN INSERT INTO #tMatches (record_uid,service_name,service_address,service_description,GRE,GRN) SELECT service_loc_uid,title_return,service_address,service_overview,GRE,GRN FROM v_RecordsLive_short WHERE (title_return LIKE '%' + @lookingfor + '%' OR service_category LIKE '%' + @lookingfor + '%' OR service_overview LIKE '%' + @lookingfor + '%') END -STEP 2 DECLARE @UserCoordX integer DECLARE @UserCoordY integer --1) USE POSTCODE DATA FOR REGION ONLY SELECT @UserCoordX = GRE,@UserCoordY = GRN FROM tPCD WHERE tPCD.PCD = @postcode --STEP3 --open a cursor containing record ids with co-ordinates DECLARE @record_uid uniqueidentifier DECLARE @CoordX integer DECLARE @CoordY integer DECLARE @XLen integer DECLARE @YLen integer DECLARE @range real DECLARE locs_cursor CURSOR FOR SELECT record_uid,GRE,GRN FROM #tMatches OPEN locs_cursor FETCH NEXT FROM locs_cursor INTO @record_uid,@CoordX,@CoordY WHILE @@FETCH_STATUS = 0 BEGIN --calculate range from entered postcode using pythagorus SET @XLen = Abs(@UserCoordX - @CoordX) SET @YLen = Abs(@UserCoordY - @CoordY) SET @range = SQRT((@XLen * @XLen) + (@YLen * @YLen)) END --convert range to miles SET @range = COALESCE(@range,0) IF (@range > 0) SET @range = @range/160.9 --IF OUT OF RANGE, DELETE RECORD FROM TABLE STRAIGHTAWAY IF @range > CAST(@miles as float) DELETE FROM #tMatches WHERE record_uid = @record_uid --get next record FETCH NEXT FROM locs_cursor INTO @record_uid,@CoordX,@CoordY CLOSE locs_cursor DEALLOCATE locs_cursor --------------------RETURN ALL RESULTS----------------------------------------------- SELECT record_uid,service_name,service_address,service_description FROM #tMatches DROP Table #tMatches
View Replies !
SQL Server 2000 Database To SQL Server 2005 Standard Security Issue
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003. We have the following setup: SQL Server 2000 - DatabaseA - asdfUser (SQL User) - asdfUser is (dbowner) of DatabaseA - DatabaseB - asdfUser (SQL User) - asdfUser is (dbowner) of DatabaseB SQL Server 2005 Standard -asdfUser is NOT Setup as a user yet. -We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB. -We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions. -We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions. -When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values. I assume this is a bug... any word on a fix?
View Replies !
Accessing SQL Server 2000 With ASP.NET
Hi Everyone, I just started using ASP.NET and I cannot figure out how to use a connection string to connect to a SQL Server database using ASP.NET. I want to use Windows Authentication to bring up a database called upsizedCandidate on a server called SQLSERVER and display a table within that database through internet explorer. I'm not sure if I should be using OLE DB or ODBC. Can anyone help me with this? Thanks in advance!
View Replies !
Accessing Sql Server 2000 With Http
Hi there, I want to access to sql server 2000 on localhost with http .Following instructions on "Sql Server books online", If I perform on Window Server 2003 R2 operating System, the result was not unsuccessful. However, on Window XP, the result was successful.
View Replies !
Accessing SQL Server 2000 Via UNIX
Hi,I am having a problem accessing SQL Server 2000 via UNIX. I amaccessing SQL Server 2000 from Solaris using Sybase Open Client(CT-Lib). Here is the error message:CT-LIBRARY error:ct_connect(): network packet layer: internal net libraryerror: Net-Library operation terminated due to disconnectI have another SQL server 6.5 and I do not have any problems accessing6.5 using the same strategy. Can someone tell me how I shouldconfigure to access SQL Server 2000 from UNIX?Thanks,Amy
View Replies !
|
TRACKER
