looping through table tom exec an SP many times

Hi,
If i have an SP called mySP that accepts one parameter @.param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN

How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.

Thanks a lot for guidelines

If I understand you correctly, you want to execute mySP for every row in the myTblParams table. To do this, you could write another sp that has a cursor to loop through the myTblParams rows.

...declare variables, etc...

Declare crsMyTblParams Cursor For
Select param_field_name
from myTblParams

Open crsMyTblParams
Fetch Next From crsMyTblParams Into @.Param

While @.@.Fetch_Status = 0 Begin
Exec mySP @.Param
Fetch Next From crsMyTblParams Into @.Param
End

Close and deallocate the cursor|||

thanks a lot man. looks more complicated than i thought. is that the simplest way to do it

thanks

|||

This is actually very easy and something you do with stored procedures all the time. If you want to iterate through a result and do something for each row in the result, this is the way you do it (using a cursor).

You have another post that mentioned looping through all the database in an instance, and you would basically do the same thing. Capture the results of the exec sp_databases command (in a cursor) and loop through the results executing the select * from information_schema command for each database. You can prefix information_schema with the database name (i.e. select * from pubs.information_schema.tables. You can capture the database name from the cursor (results from the sp_database command) and then replace the database name in the information_schema select statement to find your keywords. In this case, it would be more complicated because you would have nested cursors. One that read your table (with words to search for) and one to loop through all the databases.

Cursors sound complicated when you first start using them (I know I was clueless), but once you do it a few times, they really are relatively simple and very powerful..

|||

u mean this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1048114&SiteID=1

cool u saw that:)

|||Can you explain the logic of your SP? Of course, the cursor logic is complicated, slow and hard to maintain. You can just write one SP that does all the operations in a set-based manner. It is hard to suggest the approach though without knowing more details. Please post some sample DDL, data (how the parameters look etc) and the expected results.|||

I am sure u ve already seen this SP. It searches if a given text as parameter occurs if any of the SPs scripts of the current database, and it outputs the occurence of that seaeched text and the line number. Here is the SP:

USE [mozaikDB]

GO

/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 16:54:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_FindText] @.text varchar(8000), @.findtype varchar(1)='P', @.DBvar varchar(30) AS

SET NOCOUNT ON

IF @.findtype='P'

SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @.text + '%', text)))-Len(Replace(SubString(text,1, PatIndex

('%' + @.text + '%', text)),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', text) AS Position,

OBJECT_NAME(id) AS ProcName

FROM DBvar.dbo.syscomments

WHERE text like '%' + @.text + '%'

ORDER BY ProcName, Line

So this scripts searches within the SPs of the current database but me I want to search withing all the instance databases and not only the current one.

Thanks.

|||Your SP logic is slightly flawed. If a SP definition spans multiple entries and a word gets split then your search will not find it. It is easier to use OBJECT_DEFINTION function and search the nvarchar(max) value directly. This might however be slower but better than a temporary table based approach for example (similar to sp_helptext). Lastly, the technique to find line number may not be accurate also given that lot of constructs are optional in TSQL (like statement separators, CRLFs etc). And if the text occurs multiple times then you will miss it also. So why don't you to just use the find mechanism in your source code control system instead rather than writing your own SP. The find mechanism in other tools are more robust and easier to use.|||

I tried this code as per your suggestion but i didn t get the result, i got nothing returned:

declare crsMyTblParams2 cursor for

declare @.param as varchar(8000)

open crsMyTblParams2

select FileName from mozaikDB.dbo.filelist

fetch next from crsMyTblParams2 into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams2 into @.param

end

When i try just:

exec dbo.sp_FindText 'isvirtual'

I get that the searched text: 'isvirtual' exists in one SP at line 10. But when i try the code above, and the table filelist has a row field containing the word: 'isvirtual'

I only get 8 rows with empyty values

any explanantion pls

Thanks

|||

I am not sure what modification you made. Can you post a working sample of your code that demonstrates the issue? If you modified the SP to use OBJECT_DEFINITION then you can just do something like:

select o.name

from sys.objects as o

where o.type = 'P'

and OBJECT_DEFINITION(o.object_id) like '%your_text%'

The above query will return all SPs that contains 'your_text' at least once. And I am not sure what you are doing by passing filename to the SP. Does it use BULK INSERT to read the text from the file? And when I said that you should use the source code control system, I meant the tools that come with it also. For example, if you are using Visual SourceSafe then you can use the find option within the VSS IDE itself. Other source code control systems have similar functionality. You can also use Visual Studio IDE to search through the files.

Lastly, below is a SP that does what you are looking for but the logic uses older technique that will work in any version of SQL Server. You can simplify it in SQL Server 2005.

http://umachandar.com/technical/SQL6x70Scripts/UtilitySPs(6x)/Main11.htm

|||

Sorry, i meant i used the code from DannoCoy mentioned above that uses the cursor to loop through the table where my keywords re located: param1, param2...

i don t use any file

filename is just the name of the table column so that I can get the searched keywords, then i call the SP that seraches the kayword using the cursor methid mentioned by DannoCoy

I haven t modified the code yet to use OBJECT_DEFINITION but i ll do that after everything works first.

Thanks

|||

Here is the SP code I used:

USE [mozaikDB]

GO

/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 17:22:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_FindText] @.text varchar(8000), @.findtype varchar(1)='P'

AS

SET NOCOUNT ON

IF @.findtype='P'

SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @.text + '%', text)))-Len(Replace(SubString(text,1, PatIndex

('%' + @.text + '%', text)),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', text) AS Position,

OBJECT_NAME(id) AS ProcName

FROM dbo.syscomments

WHERE text like '%' + @.text + '%'

ORDER BY ProcName, Line

Then the table mozaikDB.dbo.filelist has the values: Param1, Param2,....isVirtual...,PramN

Then I used the script suggested by DannoCoy like this:

declare @.param as varchar(8000)

declare crsMyTblParams cursor for

select FileName from mozaikDB.dbo.filelist

open crsMyTblParams

fetch next from crsMyTblParams into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams into @.param

end

The problem:

When I execute just the above SP like this;

exec dbo.sp_FindText 'isvirtual'

I get a result showing that the searched word 'isVirtual' is part of the SP CategorySave_sp in line 10

but when I run the script

declare crsMyTblParams cursor for

......etc

suggested by DannoCoy I get no result

Thanks

|||

R.Tutus, not sure what you are doing, but I created the sp_findtext and a filelist table (and added some character strings to search for), ran the code for the cursor and it worked fine. Question: do you have any rows (strings to search for) in your table mozaikDB.dbo.filelist?

Now that I see what you are attempting to do, I totally agree with Umachandar. The logic is flawed. This is going to produce questionable results at best. I have my name in a proc three different times, and sp_findtext is only indicating the first occurance. It is also returning function names and views, (not just stored procedures).

PS. When you use a cursor, you should finish with

close cursorname
deallocate cursorname

|||

yeah guys u re so right (obviously:) ) .

any way in my seached field in the table filelist i had the word: 'isvirtual ' with many spaces since the field is nvarchar(50) instead of 'isvirtual'. that s why the cursor didn t find it.

when i redefined the column in filelist as varchar(9) instead of 50 the cursor worked perfectly.

Now I am gonna try what our friend Umachandar s suggested and i ll let u know if any difficulty.

Thanks for tutoring me guys:)

|||

1/

Now I replaced the code in my seacrh procedure findText mentioned above with this one suggested by:

Create PROCEDURE [dbo].[sp_FindText2] @.text varchar(8000), @.findtype varchar(1)='P'

AS

SET NOCOUNT ON

IF @.findtype='P'

select @.text, o.name AS ProcName,@.text ,Len(SubString(object_definition(o.object_id),1, PatIndex('%' + @.text + '%', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex

('%' + @.text + '%', object_definition(o.object_id))),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', object_definition(o.object_id)) AS Position

from sys.objects as o

where o.type='P' and object_definition(o.object_id) like '%' + @.text + '%'

ORDER BY ProcName, Line

It s working so far. I understand that this might not generate the desired results in case the searched keyword: @.text occurs many times in the same SP.

Is this fine at least in your opinion. Few more questions pls:

2/ Umachandar suggested: to use OBJECT_DEFINTION function and search the nvarchar(max)value directly. How that fit in my code above and what s the use of nvarchar(max) in this context

3/ also he suggested "You can just write one SP that does all the operations in a set-based manner" which is to use one logic in the original Findtext SP to both search the keyword and loop through the table filelist containing the list of keywords to be searched. He meant that there d be no need to use both one SP called FindText for the search and another SP using cursor to execute the first SP for al the the listFile rows. Would you please be able to suggest a way for doing that.

4/ One last thing, the code above searches only in the SPs of the current Database. I want to customize the code to look up the SPs in all the databases of the current instance and not only the current DB.

Thanks a lot

Thanks

looping through table to exec an SP many times

Hi,
If i have an SP called mySP that accepts one parameter @.param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN

How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.

Thanks a lot for guidelines

If I understand you correctly, you want to execute mySP for every row in the myTblParams table. To do this, you could write another sp that has a cursor to loop through the myTblParams rows.

...declare variables, etc...

Declare crsMyTblParams Cursor For
Select param_field_name
from myTblParams

Open crsMyTblParams
Fetch Next From crsMyTblParams Into @.Param

While @.@.Fetch_Status = 0 Begin
Exec mySP @.Param
Fetch Next From crsMyTblParams Into @.Param
End

Close and deallocate the cursor|||

thanks a lot man. looks more complicated than i thought. is that the simplest way to do it

thanks

|||

This is actually very easy and something you do with stored procedures all the time. If you want to iterate through a result and do something for each row in the result, this is the way you do it (using a cursor).

You have another post that mentioned looping through all the database in an instance, and you would basically do the same thing. Capture the results of the exec sp_databases command (in a cursor) and loop through the results executing the select * from information_schema command for each database. You can prefix information_schema with the database name (i.e. select * from pubs.information_schema.tables. You can capture the database name from the cursor (results from the sp_database command) and then replace the database name in the information_schema select statement to find your keywords. In this case, it would be more complicated because you would have nested cursors. One that read your table (with words to search for) and one to loop through all the databases.

Cursors sound complicated when you first start using them (I know I was clueless), but once you do it a few times, they really are relatively simple and very powerful..

|||

u mean this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1048114&SiteID=1

cool u saw that:)

|||Can you explain the logic of your SP? Of course, the cursor logic is complicated, slow and hard to maintain. You can just write one SP that does all the operations in a set-based manner. It is hard to suggest the approach though without knowing more details. Please post some sample DDL, data (how the parameters look etc) and the expected results.|||

I am sure u ve already seen this SP. It searches if a given text as parameter occurs if any of the SPs scripts of the current database, and it outputs the occurence of that seaeched text and the line number. Here is the SP:

USE [mozaikDB]

GO

/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 16:54:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_FindText] @.text varchar(8000), @.findtype varchar(1)='P', @.DBvar varchar(30) AS

SET NOCOUNT ON

IF @.findtype='P'

SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @.text + '%', text)))-Len(Replace(SubString(text,1, PatIndex

('%' + @.text + '%', text)),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', text) AS Position,

OBJECT_NAME(id) AS ProcName

FROM DBvar.dbo.syscomments

WHERE text like '%' + @.text + '%'

ORDER BY ProcName, Line

So this scripts searches within the SPs of the current database but me I want to search withing all the instance databases and not only the current one.

Thanks.

|||Your SP logic is slightly flawed. If a SP definition spans multiple entries and a word gets split then your search will not find it. It is easier to use OBJECT_DEFINTION function and search the nvarchar(max) value directly. This might however be slower but better than a temporary table based approach for example (similar to sp_helptext). Lastly, the technique to find line number may not be accurate also given that lot of constructs are optional in TSQL (like statement separators, CRLFs etc). And if the text occurs multiple times then you will miss it also. So why don't you to just use the find mechanism in your source code control system instead rather than writing your own SP. The find mechanism in other tools are more robust and easier to use.|||

I tried this code as per your suggestion but i didn t get the result, i got nothing returned:

declare crsMyTblParams2 cursor for

declare @.param as varchar(8000)

open crsMyTblParams2

select FileName from mozaikDB.dbo.filelist

fetch next from crsMyTblParams2 into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams2 into @.param

end

When i try just:

exec dbo.sp_FindText 'isvirtual'

I get that the searched text: 'isvirtual' exists in one SP at line 10. But when i try the code above, and the table filelist has a row field containing the word: 'isvirtual'

I only get 8 rows with empyty values

any explanantion pls

Thanks

|||

I am not sure what modification you made. Can you post a working sample of your code that demonstrates the issue? If you modified the SP to use OBJECT_DEFINITION then you can just do something like:

select o.name

from sys.objects as o

where o.type = 'P'

and OBJECT_DEFINITION(o.object_id) like '%your_text%'

The above query will return all SPs that contains 'your_text' at least once. And I am not sure what you are doing by passing filename to the SP. Does it use BULK INSERT to read the text from the file? And when I said that you should use the source code control system, I meant the tools that come with it also. For example, if you are using Visual SourceSafe then you can use the find option within the VSS IDE itself. Other source code control systems have similar functionality. You can also use Visual Studio IDE to search through the files.

Lastly, below is a SP that does what you are looking for but the logic uses older technique that will work in any version of SQL Server. You can simplify it in SQL Server 2005.

http://umachandar.com/technical/SQL6x70Scripts/UtilitySPs(6x)/Main11.htm

|||

Sorry, i meant i used the code from DannoCoy mentioned above that uses the cursor to loop through the table where my keywords re located: param1, param2...

i don t use any file

filename is just the name of the table column so that I can get the searched keywords, then i call the SP that seraches the kayword using the cursor methid mentioned by DannoCoy

I haven t modified the code yet to use OBJECT_DEFINITION but i ll do that after everything works first.

Thanks

|||

Here is the SP code I used:

USE [mozaikDB]

GO

/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 17:22:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_FindText] @.text varchar(8000), @.findtype varchar(1)='P'

AS

SET NOCOUNT ON

IF @.findtype='P'

SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @.text + '%', text)))-Len(Replace(SubString(text,1, PatIndex

('%' + @.text + '%', text)),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', text) AS Position,

OBJECT_NAME(id) AS ProcName

FROM dbo.syscomments

WHERE text like '%' + @.text + '%'

ORDER BY ProcName, Line

Then the table mozaikDB.dbo.filelist has the values: Param1, Param2,....isVirtual...,PramN

Then I used the script suggested by DannoCoy like this:

declare @.param as varchar(8000)

declare crsMyTblParams cursor for

select FileName from mozaikDB.dbo.filelist

open crsMyTblParams

fetch next from crsMyTblParams into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams into @.param

end

The problem:

When I execute just the above SP like this;

exec dbo.sp_FindText 'isvirtual'

I get a result showing that the searched word 'isVirtual' is part of the SP CategorySave_sp in line 10

but when I run the script

declare crsMyTblParams cursor for

......etc

suggested by DannoCoy I get no result

Thanks

|||

R.Tutus, not sure what you are doing, but I created the sp_findtext and a filelist table (and added some character strings to search for), ran the code for the cursor and it worked fine. Question: do you have any rows (strings to search for) in your table mozaikDB.dbo.filelist?

Now that I see what you are attempting to do, I totally agree with Umachandar. The logic is flawed. This is going to produce questionable results at best. I have my name in a proc three different times, and sp_findtext is only indicating the first occurance. It is also returning function names and views, (not just stored procedures).

PS. When you use a cursor, you should finish with

close cursorname
deallocate cursorname

|||

yeah guys u re so right (obviously:) ) .

any way in my seached field in the table filelist i had the word: 'isvirtual ' with many spaces since the field is nvarchar(50) instead of 'isvirtual'. that s why the cursor didn t find it.

when i redefined the column in filelist as varchar(9) instead of 50 the cursor worked perfectly.

Now I am gonna try what our friend Umachandar s suggested and i ll let u know if any difficulty.

Thanks for tutoring me guys:)

|||

1/

Now I replaced the code in my seacrh procedure findText mentioned above with this one suggested by:

Create PROCEDURE [dbo].[sp_FindText2] @.text varchar(8000), @.findtype varchar(1)='P'

AS

SET NOCOUNT ON

IF @.findtype='P'

select @.text, o.name AS ProcName,@.text ,Len(SubString(object_definition(o.object_id),1, PatIndex('%' + @.text + '%', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex

('%' + @.text + '%', object_definition(o.object_id))),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', object_definition(o.object_id)) AS Position

from sys.objects as o

where o.type='P' and object_definition(o.object_id) like '%' + @.text + '%'

ORDER BY ProcName, Line

It s working so far. I understand that this might not generate the desired results in case the searched keyword: @.text occurs many times in the same SP.

Is this fine at least in your opinion. Few more questions pls:

2/ Umachandar suggested: to use OBJECT_DEFINTION function and search the nvarchar(max)value directly. How that fit in my code above and what s the use of nvarchar(max) in this context

3/ also he suggested "You can just write one SP that does all the operations in a set-based manner" which is to use one logic in the original Findtext SP to both search the keyword and loop through the table filelist containing the list of keywords to be searched. He meant that there d be no need to use both one SP called FindText for the search and another SP using cursor to execute the first SP for al the the listFile rows. Would you please be able to suggest a way for doing that.

4/ One last thing, the code above searches only in the SPs of the current Database. I want to customize the code to look up the SPs in all the databases of the current instance and not only the current DB.

Thanks a lot

Thanks

Looping Question!!..........

Hi all,
I have an initial parameter = 'TST0001'
I want to write an INSERT statement to automatically take the initial
parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'.
Now, my table should store data like these:
TST0001
TST0002
...
...
TST0010
TST0011
...
...
TST9999
Thanks,
Tom dYou can avoid looping by using a Numbers table for this sort of thing. See
the following articles:
http://www.bizdatasolutions.co_m/tsql/tblnumbers.asp
http://www.aspfaq.com/show.asp?id=2516
Here's an example:
CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)
INSERT INTO foo (x)
SELECT 'TST'+
RIGHT('0000'+CAST(N1.number*100+N2.number AS VARCHAR(4)),4)
FROM master.dbo.spt_values AS N1,
master.dbo.spt_values AS N2
WHERE N1.type = 'P'
AND N1.number BETWEEN 0 AND 99
AND N2.type = 'P'
AND N2.number BETWEEN 0 AND 99
I don't recommend you use this in any persistent code because spt_values
isn't documented. This is just to demonstrate what you can do with auxiliary
tables.
David Portas
SQL Server MVP
--

Loop in SSRS Expression

Hi guys,

I got a multi value parameter which displays all my 'companies'. On my report header, I wish to display all the companies selected by the user.

Parameters!Company.Label(0) only shows the first on selected

Please, any help will be greatly appreciated

W Wilmot

W

Try this.

Join( Parameters!Company.value," ,")

the second part of the Join is your delimiter.

Hammer

|||

Thanx Hammer

It worked fine if i use the 'Join( Parameters!Company.label," ,")' instead of 'Join( Parameters!Company.value," ,")'

Thank you, again!!!

|||

W,

Not a problem, can you mark this post helpful?

Thanks,

Hammer

Lookupcube and Parameter not working together

Please help me
I have the following MDx that works perfect, but I need now to attach a
parameter, but it would work. please help
With
Member Measures.[Sales Current Year Target] as
'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
Target],[DistrictGeo].[District Id].["+[DistrictIsManaged].CurrentMember.Name
+"]," + [Time].CurrentMember.UniqueName + ")")'
SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
{NONEMPTYCROSSJOIN(
{ [DistrictIsManaged].[District Id].[Dallas]},
[Territory].[Territory Desc].members)} on
ROWS
FROM [Sales vs RepBudget]
where (" & Parameters!pTime.Value & ")"You need to prefix your mdx with =" and end with ". And the whole mdx
statement needs to be in on one line. It will break by itself, but you can
check it out by copying the whole statement to Notepad, turn off word wrap
and see that it's all on one line.
Also, you might have to escape the quotes "s in your statement. You do this
by adding more quotes.
"
Visual Basic Language Specification
2.4.4 String Literals
A string literal is a sequence of zero or more Unicode characters beginning
and ending with an ASCII double-quote character, a Unicode left double-quote
character, or a Unicode right double-quote character. Within a string, a
sequence of two double-quote characters is an escape sequence representing a
double quote in the string."
Try adding an extra " infront of you "s.
Kaisa M. Lindahl
"Tomas" <Tomas@.discussions.microsoft.com> wrote in message
news:E32EB005-182D-4315-A873-FF6C6606AFCB@.microsoft.com...
> Please help me
> I have the following MDx that works perfect, but I need now to attach a
> parameter, but it would work. please help
> With
> Member Measures.[Sales Current Year Target] as
> 'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
> Target],[DistrictGeo].[District
> Id].["+[DistrictIsManaged].CurrentMember.Name
> +"]," + [Time].CurrentMember.UniqueName + ")")'
> SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
> {NONEMPTYCROSSJOIN(
> { [DistrictIsManaged].[District Id].[Dallas]},
> [Territory].[Territory Desc].members)} on
> ROWS
> FROM [Sales vs RepBudget]
> where (" & Parameters!pTime.Value & ")"
>

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.
Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.

Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

Lookup Transform with Variable Parameter

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

Brandon I don't believe this can be done with the Lookup Transform, as I have ran into this limitation before.
Adrian
|||

The way I did it was to use a derived column transform before the lookup transform that "transform" my variable in a column. By doing this, I can now see the new column in the input column of the "set query parametsrs" parameter window.

Ccote

Looking for table design pattern for different value types

Hi,

I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.

However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.

Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.

Options explored so far:

1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.

Your help in this matter will be appreciated.
Regards, Tonn

Tonn:

Can you take advantage of the SQL_VARIANT data type? Something like:

drop table dbo.parameter
go

create table dbo.parameter
( parmName varchar (40),
parmDescription varchar (80),
parmType tinyint,
parmValue sql_variant
)
go

insert into parameter values ('Integer Parm', 'Just an integer parameter', 1, 1)
insert into parameter values ('Numeric (9,2) parm', 'Yeah', 2, cast (17.50 as numeric (9,2)))
insert into parameter values ('Varchar parm', 'A varchar parm', 3, 'Yes, a varchar')
select * from parameter

-- Sample Output:

-- Warning: The table 'parameter' has been created but its maximum row size (8164) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

-- parmName parmDescription parmType parmValue
-- -- -- --
-- Integer Parm Just an integer parameter 1 1
-- Numeric (9,2) parm Yeah 2 17.50
-- Varchar parm A varchar parm 3 Yes, a varchar

|||

Hi Waldrop,

Thanks for your reply.
It most certainly is an interesting suggestion. It's like option no.1 except that the serialization has been moved to the database.

Is this the preferred way of handling this type of problem? We're in the financial market and this is a common problem for us, but there seems to be no documentation, best practices etc. available so our individual programmers tend to insist on their personal preference.

|||

I think that your purpose is the type of application that the SQL_VARIANT datatype is intended to address. It would be helpful if you could provide some sample update or select statements where you might use your proposed column. I don't think I would want to have a column for each different potential datatype. Below is an example of how you might load the data into variables. Note the need to CAST the data:

declare @.numericVar numeric (9,2)
declare @.varcharVar varchar (80)
declare @.intVar integer

set @.numericVar = (select cast (parmValue as numeric (9,2)) from parameter where parmName = 'Numeric (9,2) parm')
set @.varcharVar = (select cast (parmValue as varchar (80)) from parameter where parmName = 'Varchar parm')
set @.intVar = (select cast (parmValue as integer) from parameter where parmName = 'Integer Parm')

select @.numericVar as [@.numericVar],
@.varcharVar as [@.varcharVar],
@.intVar as [@.intVar]

-- S A M P L E O U T P U T :

-- @.numericVar @.varcharVar @.intVar
-- -- --
-- 17.50 Yes, a varchar 1

Dave

|||

Hi Dave,
I think you've already made your point and put your suggestion at the top of the list.

It's no use sending an example since every proposed alternative so far works. I'm actually looking for some form of authority to stop spending all those man-hours debating about it.
In the past I've had success regarding programming issues by referencing Gamma's "Design Patterns". I was hoping to do the same for database issues, but I found out that it's not going to be that easy.