Declare Or Create Cursor
May 27, 2004Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?
Is Declare Cursor same as Create Cursor and if not what is the major difference?
ADVERTISEMENT
DECLARE CURSOR
Oct 4, 2000Is there any way to create a cursor, based on a dynamically created select_statement? Something like:
DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect
where @strSelect is previously declared as let's say varchar.
I don't want to create a stored procedure for this.
Thanks!
Help With Declare And Cursor
Aug 13, 2006
I keep getting the message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'declare'.
What am I doing wrong?
declare @dbname varchar(8000),
declare @countyname varchar (200) ,
declare @sql varchar(8000)
declare county_name cursor for
select distinct county from Zipcodes
open county_name
fetch next from county_name
into @countyname
declare dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open dbname_name
fetch next from dbname_name
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end
set @sql = ''
fetch next from dbname_name into @dbname
fetch next from county_name into @countyname
CLOSE county_name
DEALLOCATE county_name
CLOSE dbname_name
DEALLOCATE dbname_name
Declare Cursor With Dynamic SQL?
Jul 23, 2002Hello..
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?
Parameter In Declare Cursor Statement
Jan 10, 2000I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'
Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
but I can't seem to come up with the right statement.
Any help greatly appreciated.
Thanks,
Judith
How To Declare Cursor In Stored Procedure?
Jan 23, 2008I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.
CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
Declare Dynamic Cursor From String
Apr 27, 2006Hi,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 5 Replies View RelatedDeclare Cursor For Execute Stored_procedure
Nov 17, 2006Hello,
I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable).
Something like this:
DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable
i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)
Not sure if i am doing this right all together.
any help would be greatly appreciate.
T-SQL (SS2K8) :: Cursor - Declare Variable Error
Sep 9, 2014The below cursor is giving an error
DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)
DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Why does the delete statement give an error ?
Declare Cursor Based On Dynamic Query
Sep 18, 2006Hi,
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
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
May 7, 2008The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance
R.
The code:
Code Snippet
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author: -----
-- Create date:
-- Description: inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
-- Add the parameters for the stored procedure here
@importedReportID int,
@authCode varchar(12)
AS
BEGIN
DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--IF (@authCode <> 'TX-TEC')
--BEGIN
-- SET @errmsg = 'Unsupported reporting format:' + @authCode
-- RAISERROR(@errmsg, 11, 1);
--END
DECLARE srcRecsCursor CURSOR LOCAL
FOR (SELECT
ImportedRecordID
,ImportedReportID
,AuthorityCode
,[ID]
,[Field1] AS RecordType
,[Field2] AS FormType
,[Field3] AS ItemID
,[Field4] AS EntityCode
,[Field5] AS LastName
,[Field6] AS FirstMiddleNames
,[Field7] AS Title
,[Field8] AS Suffix
,[Field9] AS AddressLine1
,[Field10] AS AddressLine2
,[Field11] AS City
,[Field12] AS [State]
,[Field13] AS ZipFull
,[Field14] AS OutOfStatePAC
,[Field15] AS FecID
,[Field16] AS Date
,[Field17] AS Amount
,[Field18] AS [Description]
,[Field19] AS Employer
,[Field20] AS Occupation
,[Field21] AS AttorneyJob
,[Field22] AS SpouseEmployer
,[Field23] As ChildParentEmployer1
,[Field24] AS ChildParentEmployer2
,[Field25] AS InKindTravel
,[Field26] AS TravellerLastName
,[Field27] AS TravellerFirstMiddleNames
,[Field28] AS TravellerTitle
,[Field29] AS TravellerSuffix
,[Field30] AS TravelMode
,[Field31] As DptCity
,[Field32] AS DptDate
,[Field33] AS ArvCity
,[Field34] AS ArvDate
,[Field35] AS TravelPurpose
,[Field36] AS TravelRecordBackReference
FROM ImportedNativeRecords
WHERE ImportedReportID IS NOT NULL
AND ReportType IN ('RCPT','PLDG')
ORDER BY ImportedRecordID -- this should work but gives syntax error!
);
END
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
Oct 17, 2006This 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.
How Can I Create View Within Declare ?
Sep 30, 2006
dear all
how can i create view within declare ?
if i had one script contains declare if it is possible to combine both table with union ?
declare @a varchar(20)
select @a = ptraceno from phonelevel
where(ptraceno = '0913787170' or otherno = '0913787170')
select * from phonelevel where ptraceno = @a
declare @b varchar(20)
select @b = ptraceno from phonelevel
where(ptraceno = '0913011676' or otherno = '0913011676')
select * from phonelevel where ptraceno = @b
How Do I Create A "Cursor" In SQL Server 7.0??
Feb 11, 2000How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios.
1. If the record exist in the Destination and the Source I will do nothing.
2. If the record exist in the Source and not in the Destination then I will add the record to the destination.
3. If the record exist in the Destination and not in the Source then I will write to the transaction Log.
Note: I am only concerned about updating one column in Destination which matches the only column in the source.
Someone Please help??
Create View From Cursor
May 12, 2006I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend
View 2 Replies View RelatedError: Could Not Create An Acceptable Cursor.
Jul 25, 2007I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.
View 2 Replies View RelatedHow To Use Cursor To Create Re-occuring Numbers?
Jun 9, 2007SELECT pid, lname, visit_date, quantity
FROM customer
ORDER BY pid
pid lname visit_date quantity
-------------------------------------------
23 wang 07/23/2006 100
23 wang 07/30/2006 140
23 wang 08/05/2006 130
23 wang 08/15/2006 135
23 wang 08/22/2006 110
34 linden 06/23/2006 99
34 linden 07/06/2006 110
34 linden 07/15/2006 120
34 linden 08/26/2006 99
How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid
index pid lname visit_date quantity
----------------------------------------------------------
1 23 wang 07/23/2006 100
2 23 wang 07/30/2006 140
3 23 wang 08/05/2006 130
4 23 wang 08/15/2006 135
5 23 wang 08/22/2006 110
1 34 linden 06/23/2006 99
2 34 linden 07/06/2006 110
3 34 linden 07/15/2006 120
4 34 linden 08/26/2006 99
Please let me know if I can explain the my question better. Thanks in advance
Create A Cursor Inside A Sproc
May 16, 2008I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
StudentInfo
StudentID StudentName DeptID
101 John 10
102 Alex 10
103 Beth 20
ClassInfo
ClassID DeptID
901 10
902 10
225 20
I want to create a Sproc which will retreive the student's classes in DeptID 10
Following is the Sproc and cursor:
use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int
Declare classCursor CURSOR FOR
SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID)
DECLARE @ClassID INT
DECLARE @DeptID INT
OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
@ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
(Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
+CONVERT(NVARCHAR (10), @ClassID) + ','
+CONVERT(NVARCHAR (2), @DeptID)+')'
FROM [myTest].dbo.StudentInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID))
PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101
Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
Jul 26, 2006I have a SQL statement stored in a SQL varriable (after a lot of conditions)
Code:
declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'
Now, I want to create a cursor to recalculate some values
I've tried:
Code:
declare cur_T cursor for exec(@sql) open cur_T
but it doesn't work.
Can I have another way to do that???
Transact SQL :: Cursor To Create Multiple Dynamic Procedures
Jun 29, 2015The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.
Table A
Col1
Col2
A
Alpha
For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.
Result:
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie
Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor
Aug 12, 2015 In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Jul 20, 2005Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies View RelatedCould Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
Sep 20, 2007I'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
Join Cursor With Table Outside Of Cursor
Sep 25, 2007part 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?
DECLARE In SQL CE
Aug 6, 2007Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?
Matt
Declare @var????
Aug 23, 2006I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it????
CREATE PROCEDURE dbo.Tracking_GetStatus
AS
DECLARE @Date DateTime
DECLARE @Begining DateTime
DECLARE @Ending DateTime
SET @Date = GETDATE()
SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1),
DATEADD(mi,(DATEPART(mi,@Date)*-1),
DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))
SET @Ending = DATEADD(ss,-1,
DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1),
DATEADD(mi,(DATEPART(mi,@Date)*-1),
DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))))
SELECT
Vehicl,
UpdateTi
XCoord,
YCoord,
Status
FROM Track
WHERE UpdateTime >= @Begining
AND UpdateTime <= @Ending
RETURN
GO
How Do I Declare A Recordset ?
Aug 25, 2006Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy
View 3 Replies View RelatedMust Declare Scalar @.....
Oct 26, 2007Hi everyone,
I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script.
I post the relevent code below:
SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int)) sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@LoginName").Value = userName sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime)) sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection)
C4LConnection.Open() CandidateID = sqlSetCandidateEmail.ExecuteScalar()
Catch Exp As SqlException lblResults.Visible = True lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try
All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition
So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script.
Any help would be appreciated.
My DropDownList Won't Declare?
Mar 3, 2008 Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong?
Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value
If MyVar = "" Then
ErrorMessage.Text = "Please select a test"
Else
'Insert selection into databaseDim oConnection As New SqlConnection
Dim oCommand As SqlCommand
Dim sConnString As String
Dim sSQL As String
sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString)
sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)"
oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar))
oCommand.ExecuteNonQuery()
oConnection.Close()
ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database."
End If
End Sub
<asp:TemplateField HeaderText="Test">
<EditItemTemplate>
<asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" >
<asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
<asp:ListItem Value="5" >5</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
Declare @var Text
Dec 17, 2001Hi,
I want to store more than 8000 characters. Ideally it might have been fine if decalre @var text had worked for me.
Can anyone tell is it possible....
thanks,
sajai.
Declare A Concatenated Value.
May 5, 2008OMG i'm so stupid, i edited my original post instead of replying!!
I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.
Example.
Table 1.
Column1 (Modem) Column2 (Highspeed) Column3 (Black)
Concatenated to be MODEM HIGHSPEED BLACK.
The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.
Column1 (MODEM HIGHSPEED BLACK) Column2 (44)
Declare Table
May 29, 2008When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help.
Declare @pc2 Table
([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)
Insert into @pc2
([prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id])
Select
[prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id]
from property_char
Go
SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM
property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id
WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')
How To Declare Variables Per Row
Oct 13, 2014I know how to set a variable for the entire query, but how do I set one per row?
I have one query which is growing beyond my ability to understand/maintain it. I factor in the sale price of an item based on cost, our markup, shipping & channel fees. The problem is that each one of these has it's own variability. I would like to be able to store inline calculations as a variable such that I can call & add them. Example:
My code currently looks like this
CREATE TABLE #tempPrice (
product NVARCHAR(40)
,price DECIMAL(18,2)
)
insert into #tempPrice (product,price) values('prod1','18.00')
[Code] ....
I would like to be able to work with it this way:
SELECT
product
,price
,Cast(price * 1.1 as decimal(18,2)) as markup
,@markup + 5 as 'markup-and-shipping' -- <-- This factors in all of the previous calculations, plus something new.
FROM #tempPrice
I use Microsoft SQL 2008