Save Me From A CURSOR. Flag Values That Won't Convert.
Dec 10, 2007
Hey all:
Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.
Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
I have another table that houses the primary key and the field of the record that cannot convert.
Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.
--Thanks--
View 3 Replies
ADVERTISEMENT
Jul 15, 2015
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.
View 3 Replies
View Related
Aug 9, 2015
I have a table with EquipmentNumber, OrderType, BreakdownIndicator and RowID columns and below are the values for one equipmentnumber,
EuipmentNumber OrderType BreakdownIndicator RowId Flag
145812 PM04 1 Yes
145812 PM07 2 Yes
145812 PM07 3 Yes
145812 PM04 4 Yes
145812 PM07 5
145812 PM07 X 6
145812 PM07 7
145812 PM04 8 Yes
145812 PM04 9 Yes
145812 PM04 10 Yes
I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,
1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.
View 12 Replies
View Related
Oct 3, 2007
Ok, I have a solution for this but it involves a cursor that will take 3 days to run. Please someone save me from this!
I have a table that contains 700,000 records. I need to update a field in this table with an integer to denote what set of records it belongs to. Basically if you look at the table in order by RowID, everytime the AcctNUM changes, the fieldtofill is incremented. The AcctNum will show up multiple times throughout the table.
Its a long story but the file we started with was junk and this is the only way out.
Here's an example of what needs to happen:
RowID AcctNUM FieldToFill
1 123 1
2 456 2
3 123 3
4 123 3
5 123 3
6 456 4
7 123 5
8 123 5
Right now I have a cursor that is steping through, checking the value of the last row, if its the same it gets that same number, if its different it increments the number and updates the row.
Its been running over two hours and only 25,000 or so records have been updated.
Is the cursor my only way out? I wish I had optimized the cursor a bit, as it making it a fast_forward or something. Would that shave off more time than the two and half hours already invested?
Thanks for you help!
View 17 Replies
View Related
Nov 21, 2004
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.
(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)
The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.
HELP !
View 3 Replies
View Related
Dec 11, 2006
I am new to SQL and have created a stored procedure for a .net webapplication. Unfortunately I had to use a cursor in the storedprocedure, so it is taking several minutes to execute because it has toread through about 15,000 records. There must be another way to dowhat I'm trying to do without a cursor. I've tried temp tables andcase statements, but I can't seem to get them to work. I've beentrying to figure this out for over a week and I am just running into awall. Some expert advise would be much appreciated. My code is below.Thank you in advance.--Insert records into first temp tableDECLARE @tempA TABLE(lnkey varchar(10),AuditorIDvarchar(7))INSERT INTO @tempASELECTLNKEY,AuditorIDFROMdbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDate --parameters from myapplicationAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')AND AuditType = @AuditType --parameter from my application--Insert percentage value of Pre-Funding completes for each auditorinto temp table BDECLARE @tempB TABLE(LnkeyCount int,AuditorIDvarchar(7))INSERT INTO @tempBSELECTROUND(COUNT(LNKEY) * @Percent/100, 0) AS 'LnkeyCount',AuditorIDFROM dbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDateAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')GROUP BY AuditorID/*Create cursor to loop through records and add a loan number totblinjectloans if the number of loans in tblinjectloans for eachauditor is less than the percentage value for each auditor from@tempB*/DECLARE @lnkey varchar(10)DECLARE @AuditorID varchar(7)DECLARE @var1intDECLARE @var2intDECLARE @sqlvarchar(4000)DECLARE c1 CURSOR FORSELECT lnkey, auditoridFROM @TempAOPEN c1FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDWHILE @@FETCH_STATUS = 0BEGINSelect @var1 = COUNT(Lnkey) from dbo.tblInjectLoans whereAuditorID=@AuditorIDSelect @var2 = LnkeyCount from @tempB where AuditorID=@AuditorIDIF @var1 < @var2Insert into dbo.tblInjectLoans(lnkey, AuditorID)Values (@LNKEY, @AuditorID)FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDENDCLOSE c1DEALLOCATE c1
View 8 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Aug 19, 2005
Hi All there -
I want to show the o/p of a cursor on a single line. There is a numeric variable that needs to be clubed with the character variable. If I use char() the o/p is not right.
How do I do that?
View 3 Replies
View Related
Apr 10, 2008
Hello,
Any help here much appreciated.
I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.
i suspect this is when i run a cursor through the table.
Is anyone able to confirm for me whether running a cursor changes a table's properties?
Many thanks.
Tim
Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
View 3 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
View 14 Replies
View Related
Jul 2, 2014
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
View 9 Replies
View Related
Sep 25, 2015
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
[code]....
View 5 Replies
View Related
Feb 10, 2008
HelloI use vs2008 and i try to make a simple windows program with add records to a database. The problem is the database don't store the values that i add. When i run my program (in design mode) everythynf looks fine, but if i close vs2008 and restart it again the database is empty.
Why?
Thank you
View 1 Replies
View Related
Jan 11, 2005
Hi,
I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.
I grab the values for the selection as follow:
foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}
but I'm not sure on what to do next and could use some help.
Thanks
Germano
View 3 Replies
View Related
May 21, 2005
I try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.
View 1 Replies
View Related
Jan 4, 2007
Hi,
using custom tasks. My properties got saved properly the first time the task is added to the workspace. After that, changing values in the CustomUI updates the properties, but the package shows, that It doesn't need to get saved. So my properties don't get changed in the package XML. Moving my custom task on the workspace after changing properties, let the package go into "save me" state and my propertiy values get saved to XML.
So now, Im searching for a method or property I have to call or set to show the package it needs to get saved after closing my CustomUI.
Any hint?
Thanks
PS: When I change the property value in the task properties grid (lower right). The package gets notified about the need to get saved.
View 11 Replies
View Related
Mar 21, 2002
Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?
View 1 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
Dec 13, 2007
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.
I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.
The code that is not working is listed below. It does not work. It returns Null. I suck.
DECLARE @column_names varchar(8000)
SET @column_names = ''
SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'
SELECT @column_names
little help?
View 6 Replies
View Related
Apr 15, 2004
Hi,
I'm doing an online invoice system with ASP and MS SQL.
So you got the price and total amount.
And let say the amount is $4000.00 , there is also a portion where the invoice needs to mention it in words, "Fourty Thousand"
Can it be "cast" ? "conver" ? or anything?
Or is there any workaround????
View 3 Replies
View Related
Jul 20, 2005
Hi all,I am trying to convert all the NULL values in a column to "Open". Anyideas??TIA
View 2 Replies
View Related
Aug 26, 2007
Hi
I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.
In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])
This does not seem to work in MS SQL. Is there some equivalent function I could use?
Thanks
David
View 7 Replies
View Related
Feb 1, 2008
I'm new to SQL so please walk me through this step by step.
All the columns in my table have a data type of nvarchar, however, some of them need to be changed to decimals.
Here's an example of what my table (cicc.972file) looks like now
Account nvarchar(8) Balance nvarchar(8) Percent nvarchar(5)
45678935 459600 03500
78965215 005643 10000
and here's what I need it to look like
Account Number Balance Percent
45678935 4596.00 0.3500
78965215 56.43 1.0000
This seems like it should be ridiculously easy, but I keep running into road blocks. Like I said, this is just a sample from my table. My real table has 90 columns in it and about half of them need to be changed to either a dollar representation or a percent.
Thanks for you help!
View 5 Replies
View Related
Mar 13, 2007
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.
example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
Thanks
View 4 Replies
View Related
Feb 11, 2006
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies
View Related
Sep 11, 2007
Hello:
I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).
So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.
I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.
Any suggestions or links to code will be greatly appreciated!
Thanks,
Pete
View 5 Replies
View Related
Jul 22, 2015
I have one of the sample values in my table. I need to convert below value to Decimal(18,5)
DECLARE @i
VARCHAR
SET @i
= '0.9'
Output i m looking for is 0.90000
View 16 Replies
View Related
Aug 18, 2015
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
View 4 Replies
View Related
Feb 20, 2007
Using a reporting services model/report builder we have two related tables:
- Fundings, (parent)
- Draws, (child)
Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount)) from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount" to zero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing.
Can anybody help me with this?
Thanks
Bruce
View 11 Replies
View Related
Dec 1, 2015
I have this query:
SELECT
ID1,
ID2,
type,
(case when type = '1' then sum(value) else '0' end) as Value1,
(case when type = '3' then sum(value) else '0' end) as Value2,
(case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y'
GROUP BY ID1, ID2, Type
Which returns:
ID1 ID2 Type Value1 Value2 Value3
005 11547 0 0.00 0.00 279.23
005 11547 1 15.23 0.00 0.00
005 11547 3 0.00 245.50 0.00
And I want to obtain this result:
ID1 ID2 Value1 Value2 Value3
005 11547 15.23 245.50 279.23
View 5 Replies
View Related
Jun 26, 2007
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
View 2 Replies
View Related
Oct 5, 2015
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies
View Related
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.
View 3 Replies
View Related
