Passing NULL to DataSet parmameter

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

@.ID = <Null> - ERROR - [BC30201] Expression expected

@.ID= - ERROR - Return statement in function must return a value

@.ID = Null --ERROR - Null constant not supported use System.DBNull instead

@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

Does anyone know another magic value I can pass to get this to work?

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

Thanks in advance.

Eric Wild

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )

Hello Eric,

Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?

Jarret

|||

Jarret,

That worked!

I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!

Here is steps on how to fix this.

1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.

2) Go to the Layout tab, and from the menu select Report/Report Parameters...

3) a new Parameter is in the list to the left callled ID.

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

Passing a table to a SP

I am making an SP that uses a table as an array. I want the user to pass th
e
table to the SP, where I will then iterate through it and pull out all of th
e
IDs and place them into a string that I can use with the IN keyword. Once I
get the table into the function, I will have no trouble. I just want to mak
e
sure that the table has at least a column of consecutive integers to use for
IDs while looping and another column of WorkIDs to be updated. I am making
this SP because it should be alot faster (I think) to use an IN statement
than having the SP update one record each time it is called and having to
call it many times. I figure that the client can just pass me a recordset
that they wish to be updated and I can pull the WorkIDs out of it.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi,
I'm not sure what the question is bu:
Assuming the table containing the request list is persistent (same name all
of the time):

code:

Update T1
Set T1.Col1 = @.SomeValue
From tbl_Target T1
Where Exists (Select * From tbl_List T2
Where T1.Id = T2.Id)

As long as you already have a table of keys, there is no need to manipulate
it into a string. If the table of keys also has a corresponding value to
assign, you can reference it instead of @.SomeValue and change the "Exists" t
o
a join. (Which you could do anyway here).
Good luck, and I hope I could help.
"Chris Lieb" wrote:

>
I am making an SP that uses a table as an array. I want the user to pass
the
>
table to the SP, where I will then iterate through it and pull out all of
the
>
IDs and place them into a string that I can use with the IN keyword. Once
I
>
get the table into the function, I will have no trouble. I just want to m
ake
>
sure that the table has at least a column of consecutive integers to use f
or
>
IDs while looping and another column of WorkIDs to be updated. I am makin
g
>
this SP because it should be alot faster (I think) to use an IN statement
>
than having the SP update one record each time it is called and having to
>
call it many times. I figure that the client can just pass me a recordset
>
that they wish to be updated and I can pull the WorkIDs out of it.
>
>
Thanks in advance
>
>
--
>
Chris Lieb
>
UPS CACH, Hodgekins, IL
>
Tech Support Group - Systems/Apps|||There are several approaches in t-SQL for such requirements. some of which
can be found at: http://www.sommarskog.se/arrays-in-sql.html
Anith

Passing a result set to a stored procedure

Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?Hi
INSERT INTO TableName EXEC mySP
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
> Hi All,
> I have sometimes used the following sort of query to pull data from one
> table to another:
> INSERT INTO Table1
> SELECT fname, lname
> FROM Table2
>
> Now, let's suppose that I had created a stored procedure to do the
> insert (and any other logic i was concerned about) and I did something
> like this:
>
> EXECUTE Table1 _Insert
> SELECT fname, lname
> FROM Table2
>
> It won't work, giving an error that looks something like this:
>
> Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
> Procedure 'Table1_Insert' expects parameter '@.fname', which was not
> supplied.
>
> I assume I'm not doing things right... how would I pass a result set to
> a stored procedure, with each row corresponding to an input parameter
> of the stored procedure?
>|||Your Table1_Insert proc is expecting a parameter. How about posting the
code for the proc?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?|||Do you mean that you want to pass a table through as an argument, in
which case you can use a table variable something like:
DECLARE @.table table(Col1 int, Col2 varchar(40))
INSERT INTO @.Table(Col1, Col2)
SELECT *
FROM table2
exec table1_insert @.Table
(untested).|||Will,
Table variable cannot be used as an input parameter
"Will" wrote:

> Do you mean that you want to pass a table through as an argument, in
> which case you can use a table variable something like:
> DECLARE @.table table(Col1 int, Col2 varchar(40))
> INSERT INTO @.Table(Col1, Col2)
> SELECT *
> FROM table2
> exec table1_insert @.Table
> (untested).
>|||lol - had a suspicion there was something wrong with that example
(hence the caveat (untested)).|||See the article:
http://www.sommarskog.se/share_data.html
Anith|||Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||The proc you have below actually has two parameters:
@.fname varchar(50),
@.lname varchar(50)
Thus, when you call it, you must feed it values for both of these
parameters. Therefore, this proc would be called like:
EXEC DeleteMe_Insert 'Joe', 'Smith'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144082359.964230.311950@.u72g2000cwu.googlegroups.com...
Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||Will wrote:
> Do you mean that you want to pass a table through as an argument?
No, I want to pass each row to the stored procedure, separately.
Presumably what I would have to do is some sort of loop construct that
reads a row out of the source table, and passes it to the stored
procedure...?

Pass user credentials RS web service?

I'm using the RS web service to pull back customized reports via a C#
app, which is running under a service account.
I need to pass in the user's credentials to the Render method to make
sure the user has been given access to the report on the Security tab
of the front end.
If I pass the default credentials, the service account's credentials
are used.
Can anyone help? Many thanks.
BurtYou need to create an instance of System.Net.NetworkCredential.
Try:
rs.Credentials = new System.Net.NetworkCredential(UserName, Password);
instead of:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
"Burt" wrote:
> I'm using the RS web service to pull back customized reports via a C#
> app, which is running under a service account.
> I need to pass in the user's credentials to the Render method to make
> sure the user has been given access to the report on the Security tab
> of the front end.
> If I pass the default credentials, the service account's credentials
> are used.
> Can anyone help? Many thanks.
> Burt
>|||David,
Thanks, but how do I get the current user's password? I'm using windows
authentication on this intranet app.
Burt|||FYI, the solution was:
WindowsImpersonationContext windowsImpersonationContext = null;
WindowsIdentity currentIdentity =(WindowsIdentity)Thread.CurrentPrincipal.Identity;
windowsImpersonationContext = currentIdentity.Impersonate();
MyService.Credentials =System.Net.CredentialCache.DefaultCredentials;
windowsImpersonationContext.Undo();
windowsImpersonationContext = null;