Dynamically retrieve the name of all the parameters for a report.

Does anyone know, if you are inside the body of a report, of a way to have a
textbox dynamically populated with the name of all the parameters that are on
that report?
So, the idea is, this textbox in the body of the report would have some
expression, and that expression would always return the names of all
parameters that are defined for the report. So, if someone changes the report
to use have a new parameter, this textbox would automatically show the name
of that new parameter.
Any help would be greatly appreciated.
Thanks.Sorry, this is not supported at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Barzilli" <Mike Barzilli@.discussions.microsoft.com> wrote in message
news:D21AC308-F48C-4F68-B189-4737E03DFEC1@.microsoft.com...
> Does anyone know, if you are inside the body of a report, of a way to have
> a
> textbox dynamically populated with the name of all the parameters that are
> on
> that report?
> So, the idea is, this textbox in the body of the report would have some
> expression, and that expression would always return the names of all
> parameters that are defined for the report. So, if someone changes the
> report
> to use have a new parameter, this textbox would automatically show the
> name
> of that new parameter.
> Any help would be greatly appreciated.
> Thanks.

Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.

Dynamic Update with a sub select

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set@.SQL = @.SQL +'@.XDeliveryDate'

Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate

Any Help appreciated

Mr Tumnus wrote:

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

Any Help appreciated

I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).

|||

No change, I still get the same error

I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).

I am fairly new to SQL and would appreciate any advice.

|||

Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.

Another option is to split the operation into a batch of 2 steps like:

Code Snippet

SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'
SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'

For your @.SQL setting code.

|||Hi,

try this:

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.TABLENAME VARCHAR(100)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.TableName = 'SomeTable'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '

Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

PRINT @.SQL

UPDATE dbo.[SomeTable]

SET

UID =@.XCustomerID ,

SlotClosed=1

where WorkingSheduleID =

(

Select Max (WorkingSheduleID) From dbo.[SomeTable]

Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))

)

Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Try to change the query as follows :

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'

set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

SELECT @.XWorkingSheduleID

Dynamic SQL with output values

Hi:
How can I in SQL Server 2000 (using Transact SQL) execute a dynamic sql string and at the same time retrieve output params ?
ThanksPlease give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?|||Originally posted by rnealejr
Please give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?

Here's an simple example:

SELECT @.num_records = COUNT(*)
FROM @.TableName

Dynamic SQL String

I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()

This is what I would write if the name of the table was known:

Select @.RecordNo = MAx([VehicleID]) from Alto

This is what my dynamic SQl string looks like:

Select @.SqlStatement = 'Select Max([VehicleID]) from ' + @.TableName

So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?look at
sp_execute|||Thanks for the reply.

Do you mean sp_executesql?

I can't see that I can get a value returned by using this|||Let me rephrase that...

I can't see how I can return a value into a local variable by using sp_executesql|||This doesn't generate any errors, but doesn't assign @.recordNo with a value. Am I anywhere close with this?

Select @.SqlStatement='Select Max([VehicleID]) From ' + @.TableName
exec @.RecordNo = sp_executesql @.Sqlstatement|||try this :

declare @.tbl as nvarchar(20),@.stmt as nvarchar(100)
declare @.maxid int
set @.tbl='users'
select @.stmt='select max(userid) from ' + @.tbl
exec @.maxid=sp_Executesql @.stmt
print @.maxid

hth|||oops yes sorry I dropped the sql bit!|||You have used different variable names, but apart from that I am doing exectly the same as you. Should this work?|||yes i just tried it on my tables...so you should change the table names and column names...it works for me.

hth|||Yes I agree that a value gets printed. But it doesn't come from the last line in your example. It comes from the execution of the exec statement.

I also get a value printed but the variable assignment doesn't take place. No matter what I try, I get get the assignment to happen.|||are you trying to get the id into asp.net ? you can always get the id like this :

dim MySQL as string = "select max(userid) from " & tbl
...
intid=cms.executescalar()
...

and send the tablename dynamically
and send in the table name...though its not completelyt safe...i cant think of anything else...

hth|||No i'm not trying ti get the Id into asp.net, well not directly. When the user creates a new record, I need to give it a RecordNo so it can be referenced later. So all i'm trying to do here is find the last record in the table and add 1 to it. The trouble is that the same sp will be used to work with several tables. So I need to construct this dynamic Sql string.|||you can do a select max(userid) from the table but that will not always be accurate...you can get an id of 35 ( for xample) but if some other user just made an insert while you are querying for the maxid...you can get the new id after the insert...using SCOPE_IDENTITY() function..

hth|||We seem to be losing the point here. I have no problem querying the table if the table name is fixed.

I need to be able to specify the table name in a parameter supplied to the sp. This is where I am stuck.|||I've been stuck on this problem for a week now. I need to get it sorted out. I've put the offending code into a sp of it's own. Here it is:

CREATE PROCEDURE GetNextRecordNo

@.TableName nvarchar(15)
AS

declare @.SqlStatement nvarchar(100)
declare @.RecordNo int

Select @.sqlstatement = 'Select max(VehicleID) from ' + @.TableName

execute @.RecordNo = sp_executesql @.sqlstatement

If @.RecordNo = Null select @.RecordNo = 0

select @.RecordNo=@.RecordNo+1

Return (@.RecordNo)
GO

From my main sp I am calling the above sp like this:

execute @.recordNo=GetNextRecordNo 'Alto'

If the table in question has a maximum Vehicle ID of 10 then the value returned is also 10. The only explanation for this is that the Execute command half way down the sp is what is actually returning the value.

What am I doing wrong?