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

Transact SQL :: Removing Cursor And Updating Flag For Each Row?

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

Display Flag For OrderType Cycle Values

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

Save Me From A CURSOR

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

Un-flag Reinitialization Flag On Trans Subscription

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

Need To Convert Cursor

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

Transact SQL :: Convert Comma Separated String Values Into Integer Values

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

How To Convert Numeric To Character In CURSOR

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

Does Cursor Convert Table To Read/write?

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

Transact SQL :: Convert Certain Row Values To Next Column Values

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

T-SQL (SS2K8) :: Replace Cursor - Convert To Recursive CTE Or While Loop

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

T-SQL (SS2K8) :: Convert Cursor To Recursive CTE Or A Normal Query?

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

Windows Application - How Can I Save The Values In Table?

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

How To Save Listbox Multiple Select Values

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

Save Boolean Values In A Sql2000 Table

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

Update Package To Save New Property Values

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

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..

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

Add Symbol To Column Values And Convert Column Values To Western Number System

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

Using Stuff To Return A String Of Values From A Column Without A Cursor

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

Convert Int Values Into Words..??

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

Convert NULL Values

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

Convert Null Values To 0

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

Convert Nvarchar Values To Decimals

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

Convert Nvarchar Values To Integer

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

SQL Server 2005 - Save Tran Save Point Name Case Sensitive?

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

How To Convert UTC Based Datetime Values To Local Setting?

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

Transact SQL :: Values In Table - Convert Varchar To Decimal

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

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

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

Need To Convert NULL Values To 0, (zero) In Order To Perform Math Calculations

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

Transact SQL :: Convert Multiple Lines With Different Values In Value Columns In 1 Line

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

How Can I Change The Default Save-As/Save Directory

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

Integration Services :: Replace Blank Strings Values To NULL And Convert To Integer Data Type

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

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved