Can it be done? WinForm consumes report, then prints direct to printer

It seems like there must be a way to do this basic task. A winform app
calls a report, passes the parameters and then outputs that report directly
to the printer without user intervention, or browser sessions.
I really dont want to go back to Crystal to do this.
Thanks, MikeNot easily. To integrate with RS from a winform you have to use either web
services or url integration. You can either handle the printing yourself
(render to PDF, save the file to disk, print the PDF) or use the client side
printing but although the client side printing could be driven by URL
integration (which would require a browser control) it will pop up a
printing dialog box. There is no way I know of to prevent this. My
suggestion is to render to PDF, save to disk etc.
You could try beta 2 of VS 2005 which have a winform and webform control but
that is a beta.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Morisoli" <michael@.removeme.vieon.net> wrote in message
news:OyKPcNVYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> It seems like there must be a way to do this basic task. A winform app
> calls a report, passes the parameters and then outputs that report
> directly
> to the printer without user intervention, or browser sessions.
> I really dont want to go back to Crystal to do this.
> Thanks, Mike
>

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?I can't see why not... Have you tried?|||I have done it using the result set property for that. I guess you can do the same using a SP but may be you need to use the parameter tab of the Execute SQl task to set an output parameter that is mapped to the variable.|||I have tried this with just about every combination available with no luck. Do you need to add anything to the exec command? I am passing it one variable and it now looks like this "exec sp_name '20071201'" Do I need to change it to look like this? "exec sp_name '20071201', ?" or this? "exec sp_name '20071201', ? OUTPUT". I also have a variable set up under result set and included it in executevariablevalue. Just to be clear the SP is not putting anything in a variable but builds a report in a fiels and at the end does a select @.report to display what was built in @.report. I hope this is clear. Thanks.|||What *exactly* do you have entered in the SQLStatement box?|||I am building it in a variable that contains

exec sp_name '20071201'|||So just to be clear... You don't have "exec sp_name @.User::Var" listed.|||

Aha!, so the SSIS variable is to pass the SQL statement tothe Execute SQL task?

I thoght you were trying to put the output of the SP into a SSIS variable which is different.

Please clarify this....

|||

agentf1 wrote:

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

There's a downloadable demo of doing this here:

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)

It sounds as though you're not interested in the bit about the Foreach loop but the bit about the Execute SQL Task is exactly what you after.

-Jamie

|||I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.|||

agentf1 wrote:

I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.

So where exactly is the problem?

If you need the sql statment to be dynamic; you may want to use an expression inside of the Execute SQL Task to set the SQLStatementSource property:

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

Now the second part would be to get the SP output into a SSIS variable; for that you can follow Jamies sugestion. If the SP returns more than one row you will need a SSIS variable of Object type.

|||

agentf1 wrote:

I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.

You haven't told us why this doesn't work. Do you get an error? Does anything happen?

-Jamie

|||I looked at Jamies example and it is pretty much what I am doing with the exception I am trying to get a result set from a sp not a select statement.

This is the error message I am getting
.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Run sp_D3ALNAC1, Execute SQL Task: Executing the query "exec sp_D3acls1 '20061204'" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Task failed: Run sp_D3ALNAC1
Error: 0xC0019001 at Run sp_D3ALNAC1: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.

I have a variable named User::spmessage defined as string. I have this in execvaluevariable on the execute sql task that runs the sp. On this task I also have a result set set up for the same variable with a result set name of 0. I also have result set type properties set up to say ResultSetType_Rowset and have also tried singlerow.

I am passing the sql to the task in a different variable and it contains
exec sp_name '20061201'

I have also tried different combination of this by adding ,? and ,? OUTPUT to the end of that exec statement.

I really appreciate all of your help and guidance.|||TTT|||Is User::spmessage a variable of the "object" data type?

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?I can't see why not... Have you tried?|||I have done it using the result set property for that. I guess you can do the same using a SP but may be you need to use the parameter tab of the Execute SQl task to set an output parameter that is mapped to the variable.|||I have tried this with just about every combination available with no luck. Do you need to add anything to the exec command? I am passing it one variable and it now looks like this "exec sp_name '20071201'" Do I need to change it to look like this? "exec sp_name '20071201', ?" or this? "exec sp_name '20071201', ? OUTPUT". I also have a variable set up under result set and included it in executevariablevalue. Just to be clear the SP is not putting anything in a variable but builds a report in a fiels and at the end does a select @.report to display what was built in @.report. I hope this is clear. Thanks.|||What *exactly* do you have entered in the SQLStatement box?|||I am building it in a variable that contains

exec sp_name '20071201'|||So just to be clear... You don't have "exec sp_name @.User::Var" listed.|||

Aha!, so the SSIS variable is to pass the SQL statement tothe Execute SQL task?

I thoght you were trying to put the output of the SP into a SSIS variable which is different.

Please clarify this....

|||

agentf1 wrote:

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

There's a downloadable demo of doing this here:

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)

It sounds as though you're not interested in the bit about the Foreach loop but the bit about the Execute SQL Task is exactly what you after.

-Jamie

|||I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.|||

agentf1 wrote:

I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.

So where exactly is the problem?

If you need the sql statment to be dynamic; you may want to use an expression inside of the Execute SQL Task to set the SQLStatementSource property:

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

Now the second part would be to get the SP output into a SSIS variable; for that you can follow Jamies sugestion. If the SP returns more than one row you will need a SSIS variable of Object type.

|||

agentf1 wrote:

I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.

You haven't told us why this doesn't work. Do you get an error? Does anything happen?

-Jamie

|||I looked at Jamies example and it is pretty much what I am doing with the exception I am trying to get a result set from a sp not a select statement.

This is the error message I am getting
.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Run sp_D3ALNAC1, Execute SQL Task: Executing the query "exec sp_D3acls1 '20061204'" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Task failed: Run sp_D3ALNAC1
Error: 0xC0019001 at Run sp_D3ALNAC1: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.

I have a variable named User::spmessage defined as string. I have this in execvaluevariable on the execute sql task that runs the sp. On this task I also have a result set set up for the same variable with a result set name of 0. I also have result set type properties set up to say ResultSetType_Rowset and have also tried singlerow.

I am passing the sql to the task in a different variable and it contains
exec sp_name '20061201'

I have also tried different combination of this by adding ,? and ,? OUTPUT to the end of that exec statement.

I really appreciate all of your help and guidance.|||TTT|||Is User::spmessage a variable of the "object" data type?