Am I using the cursor feature properly here? -- of course there would be actual processing (replacing a while loop) going on and not simple print statements.
Declare @BadgeNumber varchar(20), @name varchar(100), @phone int, @status varchar(25)
Declare cursor Cursor For
Select jt.BadgeNumber, tj.Name, jt.Phone, zt.status
From employees jt
join employeestatus zt
On jt.id = zt.id
[code]....
Hi All,
I failed to find record when using variable in cursor in WHERE clause:
ID is uniqueidentifier field in the table
DECLARE @EncounterID uniqueidentifier
........
WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger
WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found
Tried to CAST(@EncounterID as char(40)) but still no luck.
I would greatly appreciate any advise hot to make it working.
Thank you very much in advance
Roman
I have two xml queries that take long: the 1st query takes about 5 minutes (returns 700 rows) and the 2nd query takes about 10 minutes (returns 4 rows). The total rows in the table is about 2 million. There are three secondary indexes: Property, Value and Path in addition to the clustered index on CardID and Primary XML index. Here is the table definition:
CREATE TABLE [dbo].[Cards]
(
[CardId] [int] NOT NULL,
[Card] [xml] NOT NULL,
CONSTRAINT [PK_dbo_Cards_CardId] PRIMARY KEY CLUSTERED
([CardId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[code]...
Looking at the execution plan, the query uses the Primary XML Index even if I add any of the secondary xml indexes. My question is why does not the optimizer use the Property 2ndary index instead of the Primary XML Index? Microsoft recommends that creating a Property index for Value() method of the xml datatype would work to provide a performance benefit. What would be another alternative to make the query run faster?
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.
This syntax is not displaying verified OR timespentinclass accurately. Where did I miss syntax?
DECLARE
@studentid varchar(50)
SET @studentid = 'ALL'
declare
@count int,
@course varchar(100),
[Code] ....
Hi all,
I need to run a long process in a cursor. I'd like to perform a special action every 10,000th row. How would you implement that using Transact-SQL?
Many thanks,
-Kevin Kline
I have a Stored Procedure. In that SP, I am calling 10 table-valued user defined function to calculate different pricing charges (lets say delivery charge, fuel surcharge, etc). In that SP, there is one Temporary table to store the data required for calculating charges and some columns will hold the calculated data in the same temp table.
1. First step is loading the columns in Temporary table in the SP which are required for calculating charges (will be having 1000 records for example)
2. Second step is to calculate charges ( delivery charge, fuel surcharge, etc). for each rows(1000 rows) from the temporary table using table-valued user defined functions one by one*Call function to calculate DeliveryCharge in the SP and calculate DeliveryCharge for all 1000rows. For this step i am using cursor to loop through 1000records and find DeliveryCharge for each row and update it in the DeliveryCharge column in the same temporary table. I am using 10 cursors for 10 different price calculations in the SP
3. Finally, the SP will return that 1000 records with calculated prices. The question is how to avoid Cursor for these operations. How to pass all 1000 records to a function and get table valued results from that function and update those results in the Temporary table without using cursors?
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected)
6255.84
(1 row(s) affected)
Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real
DECLARE @updatedprice real
DECLARE @updatedRe real
DECLARE @updatedAUX real
SET @lastprice = (
SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
)
[code].....
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
I have a table which is a configuration table, I have declared cursors whereby the cursor doesn't get to all the rows in the configuration table even though there is no where clause in the select statement and the cursor ought to loop/go through every single row. Changed the cursor to the below and it started to go through all rows.
DECLARE XXX CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING FOR
Now with the definition above, I am now having a situation whereby a column in row 1 which is a bit data type and has a value of 0, then the cursor gets to row 2 the same column but with a value of 1 and an if statement in the cursor saying
IF @row2columnX = 0 set @myval = 99
For some reason within the cursor the IF statement is being implemented even though the value in row2 columnX is = 1
I find it so weird. Is there a database property that affects the way cursors react or is there something that I am doing incorrectly ? Lastly, I would like to have a simple cursor template which simply goes through a configuration table or any table.
i read in the SET ROWCOUNT documentation URL.... that 'The ROWCOUNT option does not affect dynamic cursors', it does affect my dynamic cursor created in a table function which looks like this :
CREATE FUNCTION MyTableFunction()
RETURNS @MyTable TABLE (MYFIELD INTEGER)
AS
BEGIN
DECLARE @xxx INTEGER
DECLARE My_Cursor CURSOR DYNAMIC FOR
[code]...
I would like the number of rows retruned by MyTableFunction limited to 2, but NOT the inside proc cursor's select !Set Rowcount 0 is forbidden in table function. I cannot use TOP in select * from MyTableFunction instead of setting ROWCOUNT to 2. I'm using SQL Server 2008 or 2012.
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
The 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
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
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 1 Replies View RelatedI'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
Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE " Dim ACItem As ListItem Dim CATItem As ListItem For Each ACItem In cbl_CustomersearchAREA.Items If ACItem.Selected Then CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe For Each CATItem In cbl_CustomersearchCAT.Items If CATItem.Selected Then CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR " End If Next CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4) CSC_SqlString &= "ORDER By Categories.Category" sql_CustomersearchGrid.SelectCommand = CSC_SqlString End SubAny help on this is much appreciated, many thanks --
View 5 Replies View RelatedT-SQL offers UPPER and LOWER functions for formatting text strings. Is there a PROPER function? Thanks.
View 1 Replies View RelatedWhat are the proper procedures to move a SQL 6.5 to another 6.5 (new box)
I need to move everything including stored procedures?
Any tips would be helpful.
Thanks,
Jason
I have a tsql where I need to do a patindex on a variable and check if a record exists to meet the where clause for a IF statement below. What am I doing wrong
declare @l_orderid int
set @l_orderid = 18
declare @l_SIGShort varchar(20)
set @l_SIGShort = '~KOP~'
-- KOP Orders
print patindex ( '%~KOP~%', @l_SIGShort)
if patindex ( '%~KOP~%', @l_SIGShort) <> 0 and (if exists (select * from orderoptions
where orderid = @l_orderid and ordertype = 'KOP'))
Greetings, I seem to be getting a problem during installing SQL 7.0 over the SQL 7.0 beta. It tells me that there are ODBC components need to be upgraded and they are read only ... I can find no way of changing this.
Alternatively if I remove the beta and then install the proper version, will all the old db created under the beta still be recognised ?
Kris Klasen
Act. Manager, Data Warehouse Project
Information Management Branch
Department of Education
E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6994
Fax: 03 6233 6969
Mobile: 0419 549237
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
Australia
Greets!
I have been told that simply stopping the SQL server service and backing up the data directory is all I have to do to do a backup of my data. Is this accurate?
Thanks,
Jimmy Ipock
Hello: a nice simple question (I hope). Is there a MS SQL equivilant to PROPER (string) which would return "Fred Bloggs" from "FRED BLOGGS" and equally from "FrEd bLoggs" ? I cant find such ....
Gerry
I have a very simple query that gets a field to use as constraints in another query.
Code:
SELECT A.RIN
FROM apcType T
INNER JOIN apcAttribute a on A.T = T.RIN
WHERE T.Name like '%Sales'
The results of the first query are used in the following query where it is bolded and marked with and <<<<========
Code:
SELECT AP.Arg2, AP.Arg3, M.Parcel,
M.Serial, M.Name, M.Acres, M.District,
V.YearBuilt, V.Code, V.Size,
(SELECT SUM(V1.Acres)
FROM TRValue V1
WHERE V1.Year = V.Year and V1.Parcel = V.Parcel and
SUBSTRING(V1.Code, 1, 1) = 'L'
[code]....
My question is How can I fold the first query into the second?
I am no stranger to Databases, I worked a lot with MySQL but never really cared about proper DB design as long as it worked. Now I am playing with SQL in a ASP.NET project and want to get things done the right way.Let's say I have a Movies database. My movies can have multiple genres so I set my tables up like this:
[Movies]
MovieID
MovieName
MovieRelease
[code]....
Is this the proper way of doing things? The problem with this is when I want to enter a record manually I have to know the ID of the movie and the ID of the Genres of the movie. And what about naming conventions? By default the identifier is always Id, from my MySQL experience I liked naming it like the table, same goes with other columns. This is my T-SQL code for above tables in VS-2013.
CREATE TABLE [dbo].[Movies] (
[MovieID] INT IDENTITY (1, 1) NOT NULL,
[MovieName] VARCHAR (50) NOT NULL,
[MovieRelease] NUMERIC (18) NOT NULL,
CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieID] ASC)
[code]....
I read some questions where questioners ask "Sometimes client gives data where dates are expressed as float or integer values.
How do I find maximum date?".
Ex
March 02, 2006 can be expressed as
02032006.0
020306
2032006
20306
020306.0000
2032006
Assuming the values are expressed in dmy format
The possible way is convert that value into proper date so that all types of date related calculations can be done
Create function proper_date (@date_val varchar(25))
returns datetime
as
Begin
Select @date_val=
case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
else @date_val
end
return
cast(
case
when @date_val like '%[a-zA-Z-/]%' then case when ISDATE(@date_val)=1 then @date_val else NULL end
when len(@date_val)=8 then right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=7 then right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
when len(@date_val)=6 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=5 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
else
case when ISDATE(@date_val)=1 then @date_val else NULL end
end
as datetime
)
End
This function will convert them into proper date
select
dbo.proper_date('02032006.0') as proper_date,
dbo.proper_date('020306.000') as proper_date,
dbo.proper_date('02032006') as proper_date,
dbo.proper_date('020306') as proper_date,
dbo.proper_date('20306') as proper_date,
dbo.proper_date('020306') as proper_date
Apart from converting integer or float values to date, it will also convert date strings to date
Select
dbo.proper_date('March 2, 2006') as proper_date,
dbo.proper_date('2 Mar, 2006') as proper_date,
dbo.proper_date('2006 Mar 2') as proper_date,
dbo.proper_date('2-Mar-2006') as proper_date,
dbo.proper_date('3/02/2006') as proper_date,
dbo.proper_date('02-03-2006') as proper_date,
dbo.proper_date('2006/03/02') as proper_date,
dbo.proper_date('March 2006') as proper_date,
dbo.proper_date('2 Mar 2006') as proper_date
Madhivanan
Failing to plan is Planning to fail
What is the proper way to return the identity of a newly inserted row from a stored procedure? Using a return value or a select statement? (I guess as an output parameter should also be considered...) As in
RETURN SCOPE_IDENTITY()
or
SELECT SCOPE_IDENTITY()
What are the pros/cons of using one approach over the other?
- Jason
Hello, I have one package that seems to have continuous problems with memory. As of right now it loads a little over 1 million records. I tried leveraging the property, BufferTempStoragePath, but I don't seem to have the right path name. What sort of path do you put there? File? Folder? If it is a file, what sort of file should it be... text, dat, xml? If someone could point me in the right direction it would be greatly appreciated. Thanks.
PS: Below are the error messages I am getting:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
[DTS.Pipeline] Error: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Hi.
I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"
I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.
Thanks,
Chris
Hi,
I have a .csv file that lists date like this: "20070522", no hyphens or spaces. In the file connection manager I have the column defined as string. The database column is a datetime.
When I attempt to load the file to the table, I get this error:
[OLE DB Destination [9]] Error: There was an error with input column "effective date" (155) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".
My question is, what do I need to do to this column so that I can load it into the database?
Thanks much
Hi There
View 4 Replies View Related