Iterating updated fields within a Trigger

Hi,

I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?
Thanks in advance,

Julie Vazquezjv (julie_vazquez@.hotmail.com) writes:
> I want to log updates to specific fields, storing the new and old
> values. Is there any way I can iterate the collection of updated
> fields within a trigger in order accomplish this?

Not in a way that I would call painless. You could use something
with dynamic SQL, but that would come with a performance cost, and
you really don't want to spend to much time in triggers, since you
are in a transaction. You can easliy get into locking issues.

It is better - although boring - to type the SQL for each column to
log. Writing a program that generates the trigger code could be an
option to save time.

If you are in for massive auditing, consider using a third-party
product. A trigger-based solution is SQLAudit from Red Matrix.
Lumigent offers Entegra which works from the transaction log.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.

Thanks.|||On 17 Jan 2005 17:02:45 -0800, jv wrote:

>Do you know if there is away I could iterate through a table and get
>all the field names? That would also be a great help.
>Thanks.

Hi jv,

Check out the INFORMATION_SCHEMA.COLUMNS view.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jv (julie_vazquez@.hotmail.com) writes:
> Do you know if there is away I could iterate through a table and get
> all the field names? That would also be a great help.

Yes. But don't do it. If you are going roll your own, write code for
each column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Iterating the rows of a script component

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Any particular reason you aren't posting in your other thread on this topic? This is a dup.|||

No particular reason. I just thought I wasn't specific about my problem. I didn't know how to modify the title. I am in pressure to finish off a task and didn't take time to explore how i could modify the title of the post.

Thanks.

|||

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Probably the best way is to push the data into recordset destination and then loop over them with a ForEach loop.

Hopefully this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx)

In this example the recordset is populated by an Execute SQL Task rather than a recordsset destinaiton, but teh principle thereafter is the same I think.

-Jamie

|||

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

|||

Sam_res03 wrote:

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

You're right so far. You have two options to use your value in your SQL statement:

1) Use '?' parameters

2) Use expressions. Some info here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

-Jamie

|||

Hi Jamie,

I took your approach and I get this error..

Error: ForEach Variable Mapping number 1 to variable cannot be applied.

I tried to use a foreach loop container. I mapped index 0 to a user variable of type integer. IN the script component I have the row object cast as an integer.

I also get this error..

Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Do you see what mistake I am doing? Can you please reply?

Thanks.

|||

What's the SQL statement? What's the type of the first column?

-Jamie

|||

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

|||

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.|||

Jon Limjap wrote:

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.

Yeah, Jon is right. If I were you I would investigate why you can't store it in an Integer variable. Try Int16, Int32 and Int64.

-Jamie

|||

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Thanks.

|||

Thanks Jon. I really appreciate your response. I will try your trick and will let you know soon.

|||

Sam_res03 wrote:

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Yes.

|||

Then that is not an efficient way either making 1200 calls. The only reason i tried to do this was, I didn't want to scan a table with 600k rows and do a look up in a temp table for the needed 1200 rows. I wanted to scan only for these 1200 rows/ids. Is there a way to achieve this? If I have the ids in an object, would i be able to concatenate those ids into a string varible and use that sring variable in a where clause of a select statement? I am getting this data from an Oracle DB. Is there any limitation on using a where clause in a select statement. If so, what is the other to solve this problem?

Thanks Jamie. I really appreciate your response. Thanks a lot!!

iterate/loop indexing services files

How can I loop through or get a resultset with all files in an indexing
services catalog?
Is there a way to make the returning recordset contain all records?
I want to go through and grab the FileIndex from all files. The files are
static so I don't have to worry about the value changing.
Thanks in advance
Follow-ups to microsoft.public.inetserver.indexserver
this is a terrible way of doing it but
select * from openquery(webLS,'select path,filename from webCat..scope()
where size >0')
where my catalog name is webCat, and my linked server name is also webLS
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AC" <no@.spam.me> wrote in message
news:el$h5cK%23GHA.5092@.TK2MSFTNGP04.phx.gbl...
> How can I loop through or get a resultset with all files in an indexing
> services catalog?
> Is there a way to make the returning recordset contain all records?
> I want to go through and grab the FileIndex from all files. The files are
> static so I don't have to worry about the value changing.
> Thanks in advance
> Follow-ups to microsoft.public.inetserver.indexserver
>

Iterate through list of parameters

Hello,
I want to concatenate a string like
=Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
+ Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
+ Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
+ ...
But this should be universal for all reports and universal for
single/multivalue parameters (where JOINs would be necessary).
So my question:
Is it possible to iterate through the list of parameters in custom code or
in an assembly to return such a string in a single, report-independant way?
Kind regards,
R."Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>
sure you can ... here is a function I used in a previous report :
function createparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function
enjoy
Peter|||Ralph,
I have used the Join function to concatenate the selected values of my
multivalue parameters like this:
=Join(Parameters!Para1.Label + ": " + Parameters!Para1.Value, ", ")
if you need to wrap anything in quotes you will need to modify, say add
quotes around the whole thing:
=Join("'" + Parameters!Para1.Label + ": " + Parameters!Para1.Value, "', '")
Hope this helps
Reeves
"Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>|||Hello Peter,
thank you very much for your help. Maybe I do not understand your code, but
I can't see the solution to my problem.
So I try to explain in more detail:
I wan't to print out the filters a user selected for displaying a report,
containing all filter descriptions and values. And so I think I need to
write a function like
function concatenateparameter(ByVal param as object) as string
but I do not know how to specify the object "param" which has to consist auf
all available parameters and their descriptions.
Kind regards,
Ralph
"Peter De Rop" <PeterDeRop@.discussions.microsoft.com> schrieb im Newsbeitrag
news:507E6A77-B3E6-4B26-8171-D5CB69764E68@.microsoft.com...
>
> "Ralph Watermann" wrote:
>> Hello,
>> I want to concatenate a string like
>> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
>> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
>> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
>> + ...
>> But this should be universal for all reports and universal for
>> single/multivalue parameters (where JOINs would be necessary).
>> So my question:
>> Is it possible to iterate through the list of parameters in custom code
>> or
>> in an assembly to return such a string in a single, report-independant
>> way?
>> Kind regards,
>> R.
>>
> sure you can ... here is a function I used in a previous report :
> function createparameter(ByVal param as object) as object
> dim strNewParam() as string
> dim countery as int32
> dim x as object
> for each x in param
> countery = countery + 1
> next
> redim strNewparam(countery + 1)
> dim counterx as int32
> counterx = 0
> for each x in param
> strNewParam(counterx) = x
> counterx = counterx + 1
> next
> strNewParam(counterx) = ""
> return strNewParam
> end function
> enjoy
> Peter

Iterate through all Excel files and all their sheets

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.

iterate through a view?

Hello everybody,

obviously, I'm a newbie, so please forgive me for using the wrong terms accidently.

I have created a view that looks like this:

ID Disc Cut
------
1 11 25
1 5 34
2 1 67
3 8 54
4 5 14
4 1 12
...

ID is a bigint, Disc & Cut ar varchars

As you can see, the ID is not the primary key.
Still, what I need is only the record of the first occurance of each ID,
like this:

ID Disc Cut
------
1 11 25
2 1 67
3 8 54
4 5 14
...

Is there a way that I can iterate through the view and compare one record to
the previous or next?
(I didn't find anything in the Microsoft Library yet...)

Thanks!

NinaHave you tried SELECT DISTINCT ID FROM ...|||Originally posted by grahamt
Have you tried SELECT DISTINCT ID FROM ...

That wouldn't work because these rows:

1 11 25
1 5 34

are not distinct but I need only one of them.|||There is no such thing as "First" in SQL server unless you specify a sort order. You obviously have your data sorted by ID, but after that SQL Server make no guarantee as to the order in which your data will be returned.

It could easily be returned like this on some subsequent execution:

ID Disc Cut
------
1 11 25
1 5 34
2 1 67
3 8 54
4 1 12
4 5 14

You will need to specify a secondary sort order in order to select between duplicate ID values.|||Maybe something like:

USE Northwind
GO

SELECT * FROM Orders l
INNER JOIN (SELECT OrderId, MAX(OrderDate) AS OrderDate
FROM Orders
GROUP BY OrderId) AS r
ON l.OrderId = r.OrderId
AND l.OrderDate = r.OrderDate|||...that'll work if he can specify a secondary sort order.

Iterate through a list of files with bcp

I have a folder full of date-stamped files: 010101.txt, 010201.txt,
010301.txt, etc.
I have a working bcp command that points to a specific file.
declare @.bcpCommand varchar(1000)
select @.bcpCommand = 'BCP "Import.dbo.NAV" in
"d:\folder\010101.txt" -c -T -t, -S "SERVER1\SERVER1"'
EXEC master.dbo.xp_cmdshell @.bcpCommand
How can I script the bcp command so it will import all files between say
010101 and 120105? Complicating factor; there are gaps in the sequence. Can
the script continue even if it doesn't find a file?
Alternatively, if it was possible to issue the bcp command against all files
in a folder no matter what the filename, that would work also.
This is a one-time data load with SQL Server 2000/SP4
Thanks to anyone who could help.For sake of simplicity, write a cursor script & get it off. Here is a quick
try:
DECLARE @.cmd VARCHAR(500), @.c VARCHAR(500)
CREATE TABLE #t ( c VARCHAR( 500 ) NOT NULL )
INSERT #t EXEC master.dbo.xp_cmdshell 'DIR d:\folder\*.txt /B'
DECLARE c CURSOR FOR SELECT f FROM #t
OPEN c
FETCH NEXT FROM c INTO @.c
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cmd = 'BCP "Import.dbo.NAV" in "d:\folder\' + @.c + '" -c -T -t, -S
"SERVER1\SERVER1"'
EXEC master.dbo.xp_cmdshell @.cmd
FETCH NEXT FROM c
END
CLOSE c
DEALLOCATE c
DROP TABLE #t ;
Anithsql

Iterate result set inside stored procedure

Hello,

I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).

How can I process result rows inside a stored procedure?

This is what I have so far:

CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)
/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID
/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)
PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)
/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO

Hi,
You can declare a variable and append the results onto it for each row with:

DECLARE @.emails varchar(1000)

SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx

This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"

The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:

SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery

|||

Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?

Also, I get a leading comma - how do I avoid that on the first entry?

Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"

|||

I got it! Needed to assign a derived table.

Still have the comma issue, though.

/* Get email addresses of Service Responsible Parties */
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1

|||

The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''

The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email

if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.

As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)

DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail

FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
 ) subquery

|||

Just seen your post after I did one!
Excellent news that you got it working.

The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with

SET @.emails = 'recip: ' + @.emails

|||

Instead of

SELECT @.email=ISNULL(@.email,'') + ',' + {your field}

use

SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}

|||

I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).

Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail

And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.

Either way, thanks a lot for the help - it's good to go now!

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a procedure on several db

Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
FreddyFrederico
What does the SP do? Is it located in the master database?
Using undocumented storede procedure
EXEC sp_MSForeachDB "Use ? Exec spname 'parameter'"
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
> Hi All,
> My problem is that I have to iterate a procedure on several db ... db
> names are known at runtime. I tried "Use @.dbname" without success.
> Someone can help me?
> Thanks in advance.
> Freddy
>|||Here's an example:
DECLARE @.DBName sysname, @.Qry varchar(1000)
SET @.DBName = ''
WHILE 1 = 1
BEGIN
SET @.DBName = (
SELECT MIN(CATALOG_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME > @.DBName
)
IF @.DBName IS NULL
BEGIN
BREAK
END
SET @.Qry = 'USE ' + QUOTENAME(@.DBName)
SET @.Qry = @.Qry + '; SELECT DB_NAME()'
EXEC (@.Qry)
END
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
Freddy

Iterate a procedure on several db

Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
FreddyFrederico
What does the SP do? Is it located in the master database?
Using undocumented storede procedure
EXEC sp_MSForeachDB "Use ? Exec spname 'parameter'"
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
> Hi All,
> My problem is that I have to iterate a procedure on several db ... db
> names are known at runtime. I tried "Use @.dbname" without success.
> Someone can help me?
> Thanks in advance.
> Freddy
>|||Here's an example:
DECLARE @.DBName sysname, @.Qry varchar(1000)
SET @.DBName = ''
WHILE 1 = 1
BEGIN
SET @.DBName =
(
SELECT MIN(CATALOG_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME > @.DBName
)
IF @.DBName IS NULL
BEGIN
BREAK
END
SET @.Qry = 'USE ' + QUOTENAME(@.DBName)
SET @.Qry = @.Qry + '; SELECT DB_NAME()'
EXEC (@.Qry)
END
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
Freddysql