Executing SP Having A Dynamic Cursor Fails In Calling SP
Hi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !
Thanks: Peter
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement. Using the examples given in Books Online returns compilation errors. See below. Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement? James -- SQL --------------- EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE author_cursor CURSOR FOR select_statement; EXEC SQL PREPARE select_statement FROM :szCommand; EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName; --Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
View Replies !
Calling A SP Inside A Cursor Loop..
I have SP, which has a cursor iterations. Need to call another SP forevery loop iteration of the cursor. The pseudo code is as follows..Create proc1 asBeginVariable declrations...declare EffectiveDate_Cursor cursor forselect field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()---/////Assuming the above query would result in 3 recordsOpen EffectiveDate_CursorFetch next From EffectiveDate_Cursor Into @FLD1,@FLD2begin/*Calling my second stored proc with fld1 as a In parameterand Op1 and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endWhile @@Fetch_Status = 0Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2/* Assume If loop count is 3.and If the Fetch stmt is below the begin Stmt, the loop iterations are4 else the loop iterations are 2*/begin/*Calling my second stored proc with fld1 as a In parameter and Op1and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endThe problem I had been facing is that, the when a stored proc is calledwithin the loop, the proc is getting into infinite loops.Any Help would be appreciated.Satish
View Replies !
Calling .net Assembly In Script Task When Executing Via Web Service
I have a web service that executes packages using the object model. One of my packages calls a .net assembly from a script task. The .net assembly provides custom logging and sends an email with the package execution results. When I use remote desktop connection to conect to the server and execute that package using dtexeui.exe it runs with no problems. When I call the package via the web service I can access the file system throughout the network and various databases but it says it can't open the .net assembly. The web service runs under an application pool with administrator rights to the server and database. Does anyone know what the problem is or more importantly, what the solution is?
View Replies !
SQL Job Fails Calling DTS Using UNC Path
DTS package uses UNC path to get access to an access file. If I run it manually works fine. But it fails if I use a SQL job to run it. Anyone know why? I'd reallly appreaciated if you help me solve this one. The error msg is: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Delete from Table [ProdRept].[dbo].[tblUM-RollsPerCase] Step DTSRun OnFinish: Delete from Table [ProdRept].[dbo].[tblUM-RollsPerCase] Step DTSRun OnStart: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step DTSRun OnError: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -67568648 (FBF8FBF8) Error string: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 DTSRun OnFinish: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step DTSRun: Package execution complete. My conclusion is SQL Job can't take UNC paths. Please comfirm! http://www.sqlserverstudy.com
View Replies !
DTSRun Fails While Executing From COM
Hi, i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute. Function ExeDTSRun(DTSName, mKey As String) Dim mDTS, spServerName, spUid, spPwd As String spUid = "sa" spPwd = "test" spServerName = "server" mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0""" Shell mDTS, vbNormalFocus ExeDTSRun = "Successfully Run DTS : " & DTSName End Function Regards Adi
View Replies !
SSIS Package Fails While Executing
While executiing an SSIS package i get the follwoing error: Executed as user: <User>. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-06 23:00:01.02 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:01 PM Elapsed: 0.859 seconds. The package execution failed. The step failed. The SSIS package contains one "Execute DTS 2000 package" which is written in SQL server 2000. The DTS written in SQL server 2000 and then i made the SSIS package for the same to be executed as "Execute DTS 2000 package" BUt the package execution fails. I have seen some workaround also for same where it was saying to install some dll files for SQL server 2000 Meta data services. SQL server 200 meta data services is required for running DTS packages in SQL server 2005 server. I installed that also but still the same error. I have done all but all in vain. Your help will be very helpfull since this is the production issues. Thanks, Ashok
View Replies !
Executing Parent Packages Fails
My SSIS packages which has parent packages and childpackages. the parent package is configured to pull environment specific information from an xml configuration file, to execute child pakages. i have connection string as well. and every child package has its variables mapped. when i build the solution in VS studion it builds and then i deployed to different machine as file deployment and manually copy .dtsconfig file to same folder. but whenever i run that parent package it gives me error saying can't load child packages. i am tired to look at configuration ( connection string) ..preety new to SSIS , so expecting somehelp guys. Thanks Derek Patterson.
View Replies !
Calling All Dynamic SQL Gods!
HelloI'm desperate for help with the following dynamic SQL. It used to work forages but suddenly stopped working today! I can't recall changing anything ofimportance.. So I say. Anyway, I'm getting this error: "Cannot use emptyobject or column names. Use a single space if necessary."I've identified the location within the script that causes this message it'sthis line:(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,3rd line of Set @cmdSQL =.I've been trying to insert a single space between "" which eliminates halfof the error but I can't figure out what quotes to use around 'MG' and 'MA'.I'd be grateful if you can have a look at this and let me know how tocorrect this problem.Like I said it used to work and I'm perplexed about this sudden error. Isthere any change that can cause this behaviour?Many thanks for your efforts!!Have a nice day!!MartinPaging Script:CREATE PROCEDURE dbo.sp_ListKeyOut(@page_number INT,@number_of_records INT,@cmdWHERE VARCHAR(200),@cmdORDERBY VARCHAR(200)) ASSET NOCOUNT ONDECLARE@SizeString VARCHAR(5),@PrevString VARCHAR(5),@cmdSQL varchar(2000)SET @SizeString = CONVERT(VARCHAR, @number_of_records)SET @PrevString = CONVERT(VARCHAR, @number_of_records * (@page_number - 1))SET QUOTED_IDENTIFIER OFFSET @cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,i2b_vw_keytransactionlog.KeyTransactionLogID,i2b_vw_keytransactionlog.KeyID,(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,A.ProgUserName AS ProgUserName,CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) ASTransactionDate,CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) ASReturnByDateFROM i2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID IN'IF @cmdWHERE IS NULL OR @cmdWHERE = ''BEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog')ENDELSEBEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' AND KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog WHERE ' + @cmdWHERE)ENDSET QUOTED_IDENTIFIER ONRETURN 0GO
View Replies !
Problem In Executing A Dynamic Sql.
Hi All, I am facing a problem in a dynamic sql query. My query is EXEC (' SELECT A.Atm_Model, CU.Credit_Union_Name, CASE WHEN LICENSED_SERVICES_NAME=''VISA'' THEN 1 ELSE 0 END AS "VISA", CASE WHEN LICENSED_SERVICES_NAME=''PLUS'' THEN 1 ELSE 0 END AS "PLUS", A.Atm_Make FROM ATM A LEFT JOIN Credit_Union CU ON CU.Credit_Union_ID=A.Credit_Union_ID LEFT JOIN Credit_Union_Licensed_Services CULS ON CU.Credit_Union_ID=CULS.Credit_Union_ID LEFT JOIN Licensed_Services LS ON LS.Licensed_Services_ID=CULS.Licensed_Services_ID ') when i execute this query it works fine but when i try to execute the following query DECLARE @VAR VARCHAR(MAX) DECLARE @VAR1 VARCHAR(MAX) SET @VAR=dbo.DYN() SET @VAR1=('SELECT A.Atm_Model, CU.Credit_Union_Name, '+@VAR+' A.Atm_Make FROM ATM A LEFT JOIN Credit_Union CU ON CU.Credit_Union_ID=A.Credit_Union_ID LEFT JOIN Credit_Union_Licensed_Services CULS ON CU.Credit_Union_ID=CULS.Credit_Union_ID LEFT JOIN Licensed_Services LS ON LS.Licensed_Services_ID=CULS.Licensed_Services_ID') EXEC (@VAR1) it throws an error that "Incorrect syntax near VISA" dbo.DYN is a function that writes string CASE WHEN LICENSED_SERVICES_NAME=''VISA'' THEN 1 ELSE 0 END AS "VISA", CASE WHEN LICENSED_SERVICES_NAME=''PLUS'' THEN 1 ELSE 0 END AS "PLUS", can you please help me in this? Thanks Ashutosh
View Replies !
Loop Still Fails In Cursor
I have been working on a loop that needs to run inside of a cursor statement. It has to check for a difference in days and create transaction records for the difference. The problem is that if I include this while statement it will only process 1 record with the cursor and stop. If I remove the while it will work for all the records the cursor should be reading but doesn't give the multiple transactions I need if there is a day difference. Is there a limitation to using a while inside of a cursor. Below is the code. ANy hep is appreciated. cursor stuff declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null and date_stopped <> start_date order by uniq_id open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid while (@@FETCH_STATUS =0) begin --freq stuff select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code --check for evey other day set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate) select @nodays while @cnter < @nodays begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid ) set @fcnter = 1 set @sdate = @sdate + 1 Set @cnter = @cnter + 1 end fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid END close dbcursor deallocate dbcursor
View Replies !
Dynamic SQL In Cursor
I need to pass a list of values into a cursor as such... DECLARE @group_SQL varchar(255) SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')' DECLARE groupContact_import_cursor CURSOR FOR EXEC(@group_SQL) OPEN groupContact_import_cursor FETCH NEXT FROM groupContact_import_cursor INTO @group_id WHILE (@@FETCH_STATUS = 0) BEGIN insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid) FETCH NEXT FROM groupContact_import_cursor INTO @group_id END CLOSE groupContact_import_cursor DEALLOCATE groupContact_import_cursor But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light? TIA
View Replies !
Dynamic Cursor
I am trying to use a dynamic cursor in a stored procedure: The code looks like this : /************************************************** *** set @sFormula = 'Monthlyformula' set @sStartDate = '02/01/2004' set @sEndDate = '02/01/2004' exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' ) /************************************************** *** And this is what it is interpreting select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004) My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned Thanks in advance SK
View Replies !
Dynamic Cursor
Hello ! I´m having a big problem with a dynamic cursor. There is my problem: I have two cursors. One I use to select a master table. The other I use to select a detail table. I want to fetch all rows of the master and for each row select the details. How can I pass to the detail cursor the master key ? Thanks and I´m sorry for my poor English ! :)
View Replies !
How Get The Dynamic Sql In To Cursor
Dear folks, In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation. Declare @sql varchar(5000); set @sql='select * from Role where Role_id in('+ @role_ids +')'; Exec @sql; I want take this record set in to cursor .How to do it. please help me.
View Replies !
Executing SSIS Package From Agent Fails
Hi, I have an SSIS package that utilises a 3rd party ftp program to transfer files (over HTTPS). This software stores details in the users profile relating to addresses, user names and password for transfers. As this is the case the Package needs to be executed by the domain user who has the details set in their profile. The package needs to be executed at a scheduled interval - so I have set up an Agent job to do this, and have the the 'Run As' setting, as a proxy which maps to the required domain user. The package works fine when executing manually when the required user is logged in. If, however, the user is not logged in - ie when the job kicks off at the schecduled time, the file transfer fails. On debugging I can see that the agent job does not load the user's profile -but instead uses the 'Default User' profile. The job owner is set as the same domain user that the Run As setting for the step is set. The SQL Server Agent services runs as a different Domain user. Has anyone else had similar problems - Are there any extra permissions I need to set? cheers..
View Replies !
SQL Server Agent Fails The Job While Executing The Package
H! Very critical!!!! some body help.. I am execute paakge correctly from my local machine through Agent and Command line. but when i try to execute in another server it fails. I am invoking and executing as an administrator. when i run through the command prompt or as an individual pacakge in file system it works fine. I am exceuting the package with Protection level Don'tSaveAsSensitive. I also want to know a way to fine out what the error is exactly.not just the View History. If i set the logging for the package where do i view them??? Thanks, Jas
View Replies !
Repeating Cursor Queries - Fails
When I try to repeat a query using cursors the first run is succesful but the second returns nothing; I then have to disconnect and reconnect to get the query to work again. Here's sample code (which does nothing except print returned items) SET ANSI_NULLS ON SET ANSI_WARNINGS ON declare @login sysname , @password sysname declare sourcelogins cursor for select name , password from master.dbo.syslogins open sourcelogins while ( @@fetch_status = 0) begin fetch sourcelogins into @login , @password print @login end close sourcelogins deallocate sourcelogins SET ANSI_NULLS OFF SET ANSI_WARNINGS OFF GO First time through this prints all logins. Second time it returns "completed successfully" and prints nothing. Similarily, if I run two queries consecutively, the second using a differenct cursor name, the second still fails. Any ideas?
View Replies !
Gridview Button For Calling Sqldatasource.update Fails
<!-- Trying to create a GridView Survey form: Hi [User] Do you have these assets? [Gridview] Tag Number Response 123 [Yes ] [No] [Comments textbox] 234 [Yes ] [No] [Comments textbox] Goal: The desire is for the user to click Yes or No and have the database updated with the user's name, date, response and any comments. So far, I have created SqlDataSource with Select and Update commands, created the gridview and response buttons, setup the RowCommand and On_Updating Functions. Problems:1) If I call the update() function for SqlDataSource1 and misuse the Defaultvalue parameters to run the sql, no update is posted to the database. 2) If I use the On_Update function to set the parameter values, I get "Data type mismatch in criteria expression." (Additionally, the On_Update function runs twice which I don't understand)Can anyone tell me what/why? (and how to fix it?)Sorry for the deluge, but here is the code: --> <%@ Page Language="VB" %> <html> <head id="Head1" runat="server"> <title>Asset Survey</title> </head> <SCRIPT runat="server"> Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting e.Command.Parameters("Name1").Value = User.Identity.Name End Sub Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating 'For some reason, this function executes twice '2nd execution gets error: "Data type mismatch in criteria expression." 'e.Command.Parameters("PrimaryKey1").Value = intPrimaryKey 'e.Command.Parameters("Responder1").Value = strUser 'e.Command.Parameters("ResponseDate1").Value = dtModDate 'e.Command.Parameters("Response1").Value = strResponse 'e.Command.Parameters("ResponseComments1").Value = strComments End Sub Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) _ Handles GridView1.RowCommand Dim GridRow1 As Integer = Convert.ToInt32(e.CommandArgument) If e.CommandName = "UpdateYes" Then 'code here to disable buttons and textbox '... SqlDataSource1.UpdateParameters("Response1").DefaultValue = "Yes" End If If e.CommandName = "UpdateNo" Then 'code here to disable buttons and textbox '... SqlDataSource1.UpdateParameters("Response1").DefaultValue = "No" End If 'if I use the SqlDataSource1_Updating function, I get the mismatch error 'but if I (mis)use the DefaultValue parameter, no update occurs. SqlDataSource1.UpdateParameters("ResponseComments1").DefaultValue = _ GridView1.Rows(GridRow1).FindControl("txtComments").ToString SqlDataSource1.UpdateParameters("Responder1").DefaultValue = User.Identity.Name SqlDataSource1.UpdateParameters("ResponseDate1").DefaultValue = DateTime.Now SqlDataSource1.UpdateParameters("PrimaryKey1").DefaultValue = _ GridView1.Rows(GridRow1).Cells(0).Text Try SqlDataSource1.Update() 'Error Here if I use on_updating: "Data type mismatch in criteria expression." Catch except As Exception ' Handle the Exception. End Try End Sub </SCRIPT> <body> <form id="formInv" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalTestMDB %>" ProviderName="<%$ ConnectionStrings:LocalTestMDB.ProviderName %>" SelectCommand="SELECT [PrimaryKey], [Name], [AssetTag], [Response], [Responder], [ResponseDate],[ResponseComments] FROM [Tablename] WHERE ([Name] = ?) ORDER BY [Login Name], [AssetTag]" UpdateCommand="Update [Tablename] SET [Response]=@Response1, [Responder]=@Responder1, [ResponseDate]=ResponseDate1, [ResponseComments]=ResponseComments1 WHERE [PrimaryKey]=@PrimaryKey1" OnSelecting="SqlDataSource1_Selecting" OnUpdating="SqlDataSource1_Updating"> <SelectParameters> <asp:Parameter DefaultValue="" Name="Name1" Type=String /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ResponseComments1" Type=String DefaultValue="" /> <asp:Parameter Name="Response1" Type=String DefaultValue="" /> <asp:Parameter Name="ResponseDate1" Type=DateTime DefaultValue="#8/1/2007 12:00:00PM#" /> <asp:Parameter Name="Responder1" Type=String DefaultValue="" /> <asp:Parameter Name="PrimaryKey1" Type=Int32 DefaultValue=0 /> </UpdateParameters> </asp:SqlDataSource> <span>Hi, Please respond by clicking "Yes" or "No" for each asset</span> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PrimaryKey" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="PrimaryKey" HeaderText="PrimaryKey" AccessibleHeaderText="PrimaryKey" Visible=False/> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" AccessibleHeaderText="Name" /> <asp:BoundField DataField="AssetTag" HeaderText="AssetTag" SortExpression="AssetTag" AccessibleHeaderText="AssetTag" /> <asp:TemplateField HeaderText="Please Respond"> <ItemTemplate> <asp:Button ID="btnYes" runat="server" Text="Yes" Visible="true" CommandName='UpdateYes' CommandArgument='<%# CType(Container, GridViewRow).RowIndex %>' /> <asp:Button ID="btnNo" runat="server" Text="No" Visible="true" CommandName='UpdateNo' CommandArgument='<%# CType(Container, GridViewRow).RowIndex %>' /> <br /> <asp:Label ID="lblComments" runat="server" Text="Comments:" Visible=True></asp:Label> <asp:TextBox ID="txtComments" runat="server" Text='<%# Bind("ResponseComments") %>' Visible=True MaxLength=512 Height=24 Width=320 Wrap=True Enabled=True TextMode=MultiLine></asp:TextBox> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form> </body> </html>
View Replies !
MSXML6.dll Fails To Register When Calling SSIS From Win Service
Has anyone encountered this error? I get it when I run the installer (c#). I have seen a few posts on google and msdn, but still can't get past the error. I have tried uninstall/resinstalling the parser, registering from the command line, setting the property of the interop to "donotregister" and others with no luck. Any ideas? Thanks, Lee
View Replies !
Dynamic Cursor Generation..
Hi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee
View Replies !
Declare Cursor With Dynamic SQL?
Hello.. Can you declare a cursor with dynamic SQL? I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement. these attempts did not work: DECLARE crsCursor CURSOR FOR @vchrSQL DECLARE crsCursor CURSOR FOR (@vchrSQL) Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?
View Replies !
Dynamic Select For CURSOR
Hi all I am trying to do dynamic Select for Cursor. The dynamic would be like this: IF CONDITION1 IS TRUE: SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE CustomerTenderID = @CustomerTenderID IF CONDITION2 IS TRUE: SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE CustomerTenderID = @CustomerTenderID AND CustomerSiteID = @CustomerSiteID etc etc Here's the cursor DECLARE RateList CURSOR FOR SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE (BASED ON CONDITION) ORDER BY CustomerTenderID, CustomerSiteID, SupplierID, ContractPeriod OPEN RateList FETCH NEXT FROM RateList INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID WHILE @@FETCH_STATUS = 0 BEGIN SET @rowNum = @rowNum + 1 -- DO SOME FUNKY STUFF FETCH NEXT FROM RateList INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
View Replies !
Executing SSIS Package Through SQL Server Agent Fails
Hello, I try to run a job from SQL Server Agent to execute an SSIS package, but it always fails. However, if I execute the SSIS package directly, it will succeed. The SQL Agent Job History shows "Unable to start execution of step 1 (reason: Error authenticating proxy DB1DB1_CREDENTIAL, system error: Logon failure: unknown user name or bad password.). The step failed." P.S. DB1 is my server name, DB1_CREDENTIAL is the account creating the credential The SQL Server Agent Error Log shows "[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]" I've created an account on OS and assigned it to a credential. Then, I created a proxy with the credential and then created the SQL Server Agent job with that proxy. P.S. My SSIS package protection level is created as "Rely on server storage and roles for access control", because if it is set as "Do not save any sensitive data", package execution will fail even I run it directly. How can I successfully run SSIS package from SQL Server Agent? Do I lose any setting steps? Thanks for help. Lilia
View Replies !
Probem In Executing A Long Dynamic MDX From SQL Server
Hi ,What i exacly want to do is1. Connect to OLAP server from my Sql server using following querystring'SELECT a.* FROMOpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; InitialCatalog="MRS";2. I want to execute my dynamically created MDX query . This query canbe greater than 8000 varchar limit.When my query length exceeds 8000 length i break it up into 2 parts..Here I have broken my query into 2 parts@mdx1 and @mdx2Now i execute the entire statement asexec('SELECT a.* FROMOpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; InitialCatalog="MRS";'',' + @mdx1 + mdx2 ') as ' )Still error comes that :Unclosed quotation mark before the character string 'WITH MEMBER ..('With member' is the starting statement of my MDX query)Is there any other way to connect to OLAP server and execute an MDXstatement with a Length greater than 8000 charsTIA
View Replies !
Error Executing Dynamic Select Query
Hi, i have problem executing the dynamic query. I have straight forward query as below and works fine declare @count bigint declare @varcount varchar(max) set @varcount ='5' If convert(bigint, @varcount) <> 4 print ' not 4 ' else print 'Its 4' Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc declare @count bigint declare @varcount varchar(max) declare @tempTable varchar(max) declare @vsSql varchar(max) declare @No bigint set @No = 5 set @table = 'sam' set @varcount = '' select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')' When executed it throws the follwing error Msg 8114, Level 16, State 5, Line 10 Error converting data type varchar to bigint. Can anyone point out what to change in the query to work ~mohan
View Replies !
Executing Dynamic Query And Storing Resultset
Hi All, I am really in a great trouble. My requirement is quite complex, as I feel, it may be quite simple for some of you. Here is my problem - Actually I am doing a project, where client has a specific requirement. i.e. he want's to build a query on runtime for selecting particular record he wants, so that we have provided a user interface where he can select any datasource e.g. SQL, Oracle, Excel etc. He also specifies the database name. He is displayed all the tables and columns under those tables. He selects columns from those table boxes and write the query he wants, with where clause, if required. I am saving these query in a table, storing column names in another table where we map those oringinal column names with columns of another table, wehre we want to store actual result set of the prepared query by the user. for examaple .. if user preapare query like - 'SELECT CUST_ID, CUST_NAME FROM CUSTOMER WHERE CUST_ID = 10' In case of above query I will store CUST_ID in column COL1 of table TAB1 and CUST_NAME in COL2 of table TAB1, like that we have such fifty columns in that table. Now question is here every thing is dynamic data provider, database, tables, columns and where clause, then how can get the result set out of those queries and store that query output in the that storage table with columns col1, col2 and so on, upto 50 columns. Please help me on this, as it is so urgent. I will be very much thankful to you people. Thanks & regards, Praveen Kadam
View Replies !
Calling Child Package Out Of Process Fails With Access Denied.
Has anyone ever used an Execute Package Task to call a child package, and the Execute Package Task's ExecuteOutOfProcess = True? Unless the account it runs under is an Administrator on the box, it fails for me with "Error 0x80070005 while loading package file "C:program filesmicrosoft sql server90dtsPackagesETLFact_SalesTransaction_Tracking.dtsx". Access is denied." This is eating up hours and hours of my time, time we can't afford. Is anyone able to successfully call a child package out of process?
View Replies !
Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View Replies !
Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas
View Replies !
Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time. Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work. e.g. declare @A varchar(10), @B varchar(10), @sFetch varchar(255) --Open the cursor Execute ('Open ' + crAnyCursor) --create fetch string Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B' execute (@sFetch) The following error is returned... Msg 137, Level 15, State 1 Must declare variable '@A'. Is there another means of working with dynamically executed cursors? Thanks JohnG
View Replies !
Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot. CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS declare @cmdtxt as varchar(4000), @SQLString NVARCHAR(4000), @SQLString1 NVARCHAR(4000), @pid varchar(22), @lname varchar(60) begin EXEC SQL BEGIN DECLARE SECTION; char prep[] = @p_query; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE prep_stat FROM :prep; EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat; OPEN contact_crsr FETCH NEXT FROM contact_crsr INTO @pid, @lname -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt" exec master..xp_cmdshell @cmdtxt FETCH NEXT FROM contact_crsr INTO @pid, @lname END CLOSE contact_crsr DEALLOCATE contact_crsr end
View Replies !
Dynamic Query Calling User Defined Function
I have the following procedure, that calls a Padding function to pad characters to a field. Here is what the procedure looks like Code: CREATE PROCEDURE [dbo].[Pad_Left] @Table VARCHAR(255), @Column VARCHAR(255), @PadChar CHAR(1), @PadToLen INT AS DECLARE @Query Varchar(5000) SET @Query = 'UPDATE ' + @Table + ' SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')' EXECUTE(@Query) GO When I run this I get the error Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13 Syntax error converting the varchar value 'UPDATE Lincoln SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int. But when I just run this query, it works Code: CREATE PROCEDURE [dbo].[Pad_Left] @Table VARCHAR(255), @Column VARCHAR(255), @PadChar CHAR(1), @PadToLen INT AS UPDATE Lincoln SET Baths = dbo.Function_PadLeft(Baths, '0', 4) GO Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically? I must be missing something very obvious Thanks for any help!
View Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
View Replies !
Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure. For sample: In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds. How can I do ??? My code: Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50) SELECT @idRelat = idRelat, @cmd_Det = cmd_DET FROM Relatórios WHERE Nome = @p_Relat Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint, @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024) Select @Tot_Col = 0 Select @Tot_Lin = 0 Select @Campos = 0 Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma" Select @Aux_Group = "GROUP BY " Select @Aux_Order = "ORDER BY " Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50) Declare cur_Aux insensitive cursor for SELECT Local, Linha, Campo From Relatórios_Margens WHERE (idRelat = @idRelat) ORDER BY Local, Linha Open cur_Aux Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo While @@FETCH_status = 0 begin If @a_Local = "C" Select @Tot_Col = @Tot_Col + 1 Else Select @Tot_Lin = @Tot_Lin + 1 Select @Campos = @Campos + 1 If @Aux_Group <> "GROUP BY " begin Select @Aux_Group = @Aux_Group + ", " If @Aux_Order <> "ORDER BY " begin Select @Aux_Order = @Aux_Order + ", " Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos Select @Aux_Group = @Aux_Group + @a_Campo Select @Aux_Order = @Aux_Order + @a_Campo Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo End Select @Aux_Select = @Aux_Select -- <<<< MONTA COMANDO SQL Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order Declare @Cursor_Aux cursor Set @Cursor_Aux = cursor for @Aux_Select Open @Cursor_Aux Not working !!!!
View Replies !
Issue With Executing Dynamic Sql With Inbound Paramter As Varchar Striong
Hi All: I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar. When I try to execute it fails, because it does not plavce the inbound paramter in quotes. Any help would be appreciated. In the bound search as an eaxmple can be" 'NY' @P_SEARCH_VALUE='NY' SET @V_SQL_FILTER = N' WHERE STATE = '+@P_SEARCH_VALUE SET @V_SQL=@V_BASE_SQL+@V_SQL_FILTER EXEC sp_executesql @V_SQL Here is the v_sql out put: SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY As you can see the sql will fail because the NY is not in quotes. I tried using '@P_SEARCH_VALUE''' and other forms but could not get it work.
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View Replies !
Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
|
TRACKER
