MS SQL Problem: Fill Down Variable Number Of Rows From Last Known Value.

May 15, 2006

Using Microsoft query analyser SQL version 8, I believe.

Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!

________________

Update dbo.NSTS

Set dbo.NSTS.Number = NSTS_1.Number

FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)

WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')

________________

Example of data table here:

ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
_________________

I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).

Help would be very much appreciated!

View 6 Replies


ADVERTISEMENT

TSQL - Create A Variable Number Of Rows

May 4, 2008

I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :)
Thanks



ORIGINAL DATA
EventID StartDate EndDate
1 1/1/08 1/1/08
2 20/1/08 22/1/08


REQUIRED DATA
EventID EventDate
1 1/1/08
2 20/1/08
2 21/1/08
2 22/1/08

View 2 Replies View Related

Dataset Into A Table Or Matrix With Fixed Number Of Columns, Variable Rows

Jul 13, 2007

Hi

I have a dataset with 2 columns, a rownumber and a servername - eg



rownumber servername

1 server1

2 server2

....

15 server15



I want to display the servernames in a report so that you get 3 columns - eg



server1 | server2 | server3

server4 | server5 | server6

...

server13 | server14 | server15



I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap



I have also tried using a matrix control but cant find a way to do this.



Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005



Thanks



Anthony

View 1 Replies View Related

T-SQL (SS2K8) :: Fill In The Gap Between 2 Number Range?

Apr 2, 2014

In my SQL Server database, I have table with the following records

counter, value1
12345, 10.1
12370, 10.5
12390, 9.7

Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.

For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.

1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3

Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.

why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.

View 9 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Integration Services :: Using Rows Returned In Object Variable And Email When Rows Exist

Sep 11, 2015

I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.

How I can use this variable to email fellow users.  For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.

View 4 Replies View Related

T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows

Aug 5, 2014

I managed to transpose rows into columns.

;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference

[code]...

But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..

ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view

ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

View 6 Replies View Related

Transactions With Variable Number Of Inserts

Nov 30, 2004

I'm stumped how to achieve my goal using stored procedures. I have a front end application that calls stored procedures to insert data. For example, customer pays a bill. To complete the payment process a command in the app is run which calls a stored procedure to insert a tender record and a second stored procedure to insert a transaction record. A customer can pay one to many bills at one time so the number of times the insert stored procedures are called is variable. I want to be able to use the begin transaction, commit and rollback statements so if there's a problem inserting a record all the inserts are rolled back. Previously the app used the visual basic begin transaction, commit, rollback, statements however I want to now use SQL stored procedures for inserts and updates. I've been struggling with this for some time and hope someone can help me.

View 4 Replies View Related

Using Variable Number Of Search Strings With VBA

Mar 5, 2015

I have created a kind of search function in my Excel sheet where the user can select multiple items from different listboxes.

To save the items I use an array. So in one case only 1 items off the array is filled, but another time maybe 3 items are filled in the array.

I use the array to search in a SQL database (this is string rva(i) in the code). But when the array is empty it results in an error.

This is what I have so far. (the rule at the bottom won't work also )

rc.Open "SELECT [Datum],[RvA_Nr], [RvA_Letter], [Afdeling], [EVAL], [Matrix], [Component], [AS3000], [AP04], [Rec] FROM dbo.QHSE_2ndline_history " _
& "WHERE [Afdeling] = '" & afd & "' AND [Datum] >= '" & datum1 & "' AND [Datum] <= '" & datum2 & "' AND " _
& "CASE WHEN '" & rva(1) & "' <> '' then [RvA_Nr] = '" & rva(1) & "' END AND" _
& "([Matrix] LIKE '%" & matrix(1) & "%' OR [Matrix] LIKE '%" & matrix(2) & "%' OR [Matrix] LIKE '%" & matrix(3) & "%' OR

[Code] .....

View 1 Replies View Related

SP With Variable Number Filled Of Parameters

Jun 14, 2007

I have a SP search_post (e.g)

@id int,
@author varchar(40),
@keyword varchar(40),


select * from
posts
where
id = @id and author =@author and message like @keyword


in my case a user can choose to search by one or more of this elements.

what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled


one approach I have thought out is this

if (@id IS NOT null)
select * from
posts where id = @id


if (@author IS NOT null)
select * from
posts where author= @author



if (@keyword IS NOT null)
select * from
posts where keyword = (@keyword



but this does not really take care of the posibility that all of them or some of them will be null while others will not

any suggestions ?

View 5 Replies View Related

How To Handle A Variable Number Of Self-joins?

Nov 9, 2007

Background: Customer sale records are kept in table (userHistory) that records: bookID, userID, and salesDate.

Objective: To find all the customers that have purchased the same books as any given customer, say customer1. For example, customer1 bought books bookA, bookB, and bookC; the goal is to find all of the other customers who also bought these books.

My First Attempt:

SELECT DISTINCT UH1.userID
FROM userHistory as UH1, userHistory as UH2, userHistory as UH3
WHERE UH1.userid = UH2.userID
ANDUH2.userid = UH3.userID
ANDUH1.bookID = ‘bookA’
ANDUH2.bookID = ‘bookB’
ANDUH3.bookID = ‘bookC’
AND UH1.userID <> ‘customer1’

The problem here is that it cannot handle any more/less than 3 books. The above should work well for 3 books, but what if there are 2 books or 12 books? Does anybody have any suggested queries to handle a variable amount of books (eg joins)?

Thank you kindly.
Adam

View 6 Replies View Related

Variable Number Of Input Columns

May 2, 2007

We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.

The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?



Thank You

View 3 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008



Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.

I can get a table of which fields I need to update using:

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'

and I can do the update with UPDATE tblxxx SET column = LOWER(column)

But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?

Thanks,

Dave Stephens

View 3 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008

Hi,



I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.



I know that I can get a lit of columns using:



SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'


and do the update using:



UPDATE tblABCDE SET column = LOWER(column).



In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.



If so, how do you do it?



Thanks,



Dave Stephens

View 1 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008

Hi,



I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.



I know that I can get a lit of columns using:



SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'


and do the update using:



UPDATE tblABCDE SET column = LOWER(column).



In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.



If so, how do you do it?



Thanks,



Dave Stephens

View 1 Replies View Related

Auto Insert A Variable Number Of Records?

Sep 20, 2006

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC  combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam

View 3 Replies View Related

How Can I Have A Variable Number Of Parameter Values In A Dataset?

Apr 20, 2007

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter.  The problem is I don't know how many.  I have a textbox that the user can enter search words in.  The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray)    strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method?  How?  If I can't, what are my options?Diane 

View 6 Replies View Related

Variable Number Of Where Clauses In A Stored Procedure

Jun 20, 2001

I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?

A simple illustation of this is as follows
A statement with two clauses :-

Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'

The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back

View 2 Replies View Related

Variable Number Of Columns In A Temporary Table

Nov 8, 1999

How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.

View 1 Replies View Related

PIVOT Operator For Variable Number Of Transformations

Feb 19, 2007

Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:

SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt


this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks

View 1 Replies View Related

Combining Data From Variable Number Of Tables

Jul 20, 2005

I have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell

View 2 Replies View Related

Variable Number Of Arguments In A Stored Procedure

May 5, 2008

Can a Sql Server SP have variable number of arguments??? If yes, can somebody point me to a resource demonstrating that??? I need to send variable number of arguments to some of my SPs & iterate them with either foreach or traditional for loop. Any support would be greatly appreciated.

If variable number of arguments are not feasible, then can I pass them as an array to the SP (again a Sample code would be desirable)???

View 15 Replies View Related

Need Help Using Bulk Insert From A CSV With A Variable Number Of Fields

Dec 6, 2007

Hi folks....

I am able to import a CSV file into a temporary table as long as I know the number of fields in the CSV file. Here is what I would like to do:

I would like to have a CSV file which has UP to 6 entries per row. I would like to insert each row into a table; if the there three fields, then I want to insert them into the first three columns to the temporary table. If there are four, then insert into the first four fields. Is this possible?

Does anyone have any suggestions?

Thanks!

Forch

View 4 Replies View Related

Pulling Variable Number Of Segments Out Of Data

Mar 19, 2008

I've got a table full of data like so:

varchar(MAX) as
'MSG|John|J|Smith EVN|2008-02-01|A03 ADD|101 Highland St|Mount Vernon|WA|55231 OBS|Flu|Severe OBS|Mumps|Mild'

To explain this more thoroughly, the data is organized into segments: MSG, EVN, ADD, and OBS.

Each message may have 1+ of each of these segments.

What I'd like to do is pull the contents of all OBS segments and put them into a table.

I know I can identify them using the regex of ' OBS|' but I'm not clear on how to capture anything but the first one easily. Programmatically, it's simple. SQL I'm not so sure.

Can anyone give me a gentle nudge in the right direction?

Thanks!

View 2 Replies View Related

Stored Procs With Variable Number Of Parameters

Oct 26, 2006

I have received a change request for a project i am working on which im unsure if possible or not.

at the minute i have a very simple sp that simply selects data from a table by passing in one parameter

CREATE PROCEDURE PHAR_SelectGrade

@int_ID int

AS

 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID = @int_ID
ORDER BY Grade

the user now wants the option to select more than one grade type (there are 100's of different grades)

so they might decide they want to see details for 2 grades, 22 grades etc. these would be selected and stored in an array (via an asp.net project) and then the stored procedure is called from within a web service

my question is how would i pass this array into the stored procedure??

i am assuming i would need to do something as follows for sp syntax, but im stumped on how i pass my array of values into the sp from the webservice and then for the sp to read the array in SQL??

CREATE PROCEDURE PHAR_SelectGrade

@myArray <what datatype?>

AS

 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID IN (@myArray)
ORDER BY Grade


any ideas are greatly appreciated. or maybe its just not possible?!?

Cheers,
Craig

View 3 Replies View Related

Getting Number Of Rows

Sep 16, 2006

Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

View 13 Replies View Related

Number Rows

Mar 14, 2007

I have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!

Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3

--counter restarts
Order 2
Product 1 - 1
Product 2 -2

thanks?

View 2 Replies View Related

Number Of Rows.

Jun 20, 2008

Hi,
I have the following issue:

Here is my query:
The result is below:
(SELECT 'Succesful' AS metric_value,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('S Infr', 'S Entitl', 'S Provi') THEN 1 ELSE 0 end) AS S,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Ading', 'Bending Floor', 'Net Ops' ) THEN 1 ELSE 0 end) AS N,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Management NET', 'erprise') THEN 1 ELSE 0 end) AS ENTERP,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS TS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%NMS%' THEN 1 ELSE 0 end) AS MS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ = 'Fading' ) AR TS,
month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) as month
FROM Change

WHERE YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND

Product_Cat_Tier_1_2_ = 'Network' AND
Product_Cat_Tier_2__2_ = 'RFC' AND (Change_Request_Status = 11 and Status_Reason = 6000) AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')
GROUP BY YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')), month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM'))
)

=====================
Result
================
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful 0 1 0 0 0 0 5
Succesful 0 1 1 0 0 0 6

And I want the following output, because now is June so month is equal to 6.
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful N N N N N N 1
Succesful N N N N N N 2
Succesful N N N N N N 3
Succesful N N N N N N 4
Succesful 0 1 1 0 0 0 5
Succesful 0 1 1 0 0 0 6

WHERE , N = NULL
THANK YOU

View 1 Replies View Related

Number Of Rows

Dec 27, 2006

Hello!

I try to figure out how to change the number of rows at each page, but I'm stuck.

I have no page break , but I get a number of pages when I view my report. Is there any way to set the number of rows shown at each page?

//Cissi

View 4 Replies View Related

Stored Procedure - Variable Number Of Parameters For Search

Dec 4, 2003

Hi,
I have a repeater control which I populate with search results from SQL Server.

But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?

I'm using a stored procedure with parameters:

input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords

Am using a temporary table to store all records before Select-ing those required for the particular page.

If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.

Am hoping that stored procedures can cope with an unknown number of parameters.

View 3 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View 7 Replies View Related

Illegal Variable Name/number Error In Script Component

Feb 6, 2007

Hi There,

I am trying to populate a table in Oracle Db using SSIS. For some reason, I get this error..

illegal variable name/number

This is what I have....

Public Overrides Sub PreExecute()

oracleCmd = New OracleCommand("INSERT INTO Temp(ID) VALUES(@KEY)", oracleConn)

oracleParam = New OracleParameter("@KEY", OracleType.Number)

oracleCmd.Parameters.Add(oracleParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'With oracleCmd

.Parameters("@KEY").Value = CType(Row.KEY, OracleClient.OracleNumber)

.ExecuteNonQuery()

End With

End Sub

==

Row.Key returns a row from the oracel table which has one column of type numeric..

Can somebody please help me?

Thanks a lot!!

View 1 Replies View Related

Sql Help With Number Of Rows Return Value

May 14, 2008

hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT     SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM         TABLE1WHERE     (ID = nn)     UNION all
 SELECT      SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM         table2WHERE     TABLE2 = nn)

View 1 Replies View Related







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