Programming + MSDE 2000

Hi.
I want to be able to programmatically access MSDE
databases. I would rather not use Visual Basic. My
preference would be to either access MSDE databases using
ODBC, or to use a DLL, so that I am more flexible in my
choice of programming tools. If all else fails, then I
would have to use some API (I do not use .NET, the latest
version of Visual C++ I have is 6.0, and I would rather not
have to upgrade). What are my options? Where do I get the
API, if one is necessary?
TIA,
Joseph
From classic VB, ADO is probably your best choice. ADO is a COM object, and
it comes with MDAC (I believe), which you already have on your machine
assuming you have a decently recent OS (W2K or higher). There are some ADO
newsgroups I believe, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joseph" <msde2000@.techscribe.com> wrote in message
news:18a301c4110f$ddb27be0$7d02280a@.phx.gbl...
> Hi.
> I want to be able to programmatically access MSDE
> databases. I would rather not use Visual Basic. My
> preference would be to either access MSDE databases using
> ODBC, or to use a DLL, so that I am more flexible in my
> choice of programming tools. If all else fails, then I
> would have to use some API (I do not use .NET, the latest
> version of Visual C++ I have is 6.0, and I would rather not
> have to upgrade). What are my options? Where do I get the
> API, if one is necessary?
> TIA,
> Joseph
|||"Joseph" <msde2000@.techscribe.com> wrote in message
news:18a301c4110f$ddb27be0$7d02280a@.phx.gbl...
> Hi.
> I want to be able to programmatically access MSDE
> databases. I would rather not use Visual Basic. My
> preference would be to either access MSDE databases using
> ODBC, or to use a DLL, so that I am more flexible in my
> choice of programming tools. If all else fails, then I
> would have to use some API (I do not use .NET, the latest
> version of Visual C++ I have is 6.0, and I would rather not
> have to upgrade). What are my options? Where do I get the
> API, if one is necessary?
> TIA,
> Joseph
As you mention C++ and VB 6, I'll assume you're doing Windows apps?
There are MFC classes that support database access, since you say you'd
prefer not to use VB.
CDatabase, CRecordset, etc are what you want.
Peter [MVP Academic]
|||MSDE 2000 supports the same API set as the database engine in the other
versions of SQL Server 2000:
ADO.NET using the SQLClient namespace.
ADO and OLE DB using the SQLOLEDB provider
ODBC using the SQL Server ODBC Driver.
For more information, see this topic in the latest update to the SQL Server
2000 Books Online:
http://msdn.microsoft.com/library/?u...asp?frame=true
You can also download the latest version of the Books Online from this
location:
http://www.microsoft.com/sql/techinf...2000/books.asp
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

Programmatically Specify SqlDataSource Parameters

I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find ishttp://forums.asp.net/thread/1233258.aspx but no answer was posted.

What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])

Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?

Thank you very much for your help.

Yes, you can set the value of a select parameters by name, for example

SqlDataSource1.SelectParameters["usertype"].DefaultValue ="role"

|||

Both parameter name and index are working here.

protected

void Page_Load(object sender,EventArgs e)

{

SqlDataSource1.SelectParameters[0].DefaultValue=

"yourvalue";

or

SqlDataSource1.SelectParameters["usertype"].DefaultValue="yourvalue";

}

|||

You can also set the value in the SqlDataSource_Selecting event via the e parameter:

Private Sub SqlDataSource1_Selecting(stuff)

e.command.paramter("param1").value=something

end sub

Programmatically show & don't show texboxes

Good day!

It is possible that you can programatically show & don't show texboxes in the reports?

Coz the user can choose if he want to view ir not the names.

can it be possible?

thanx....

On the Visible propery you can place an expression under Hidden..

Like:

=iif(expression,True,False)

Programmatically setting UpdateParameters for a SQLDataSource control

I need to provide defaultsand sometimes overrides for items in SQLDataSource's UpdateParameters. I am attempting to do this in a FormView's ItemUpdating and ItemInserting events as follows:

//======================================================================== // FormView1_ItemUpdating: //========================================================================protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) {// not sure if this is the bets place to put this or not? dsDataSource.UpdateParameters["UpdatedTS"].DefaultValue = DateTime.Now.ToString(); dsDataSource.UpdateParameters["UpdatedUserID"].DefaultValue = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; } 

In the example above I am attempting to set new values for the parameters which will replace the existing values.
I have found that using the DefaultValue property works ONLY if there is no current value for the parameter.  Otherwise the values I specify are ingnored.
The parameters of an ObjectDataSource provide aValue property but SQLDataSource parameters do not.
How can I provide an override value without needing to place the value in the visible bound form element?

If you can answer this you will be the FIRST person ever to answer one of my questions here!!!
Thanks,
Tony

I figured it out. I still don't know if this is the best way to provide defaults but this works. If anyone has a better method I would appreciate any input

You can use the NewValues property of FormViewUpdateEventArgs and the Values property of FormViewInsertEventArgs

//======================================================================== // FormView1_ItemUpdating: //========================================================================protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) {// not sure if this is the bets place to pout this or not? e.NewValues["UpdatedTS"] = DateTime.Now.ToString(); e.NewValues["UpdatedUserID"] = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; }

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();

programmatically set the paper size and and paper layout

how can i set the paper size and paper layout programmatically in RS. im using c#.net as prog. lang. for this. Pls help thanks!

Use the SOAP API SetProperties to modify the PageHeight and PageWidth of the report.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setproperties.aspx

|||

In addition, you may want to read these two articles:
* http://blogs.msdn.com/bwelcker/archive/2005/09/28/474898.aspx
* http://blogs.msdn.com/bwelcker/archive/2005/09/07/461758.aspx

-- Robert

|||iam using RS 2000. is this SOAP API SetProperties is available in this version? thanks
|||Yes, the code shown in this article should work for RS 2000: http://blogs.msdn.com/bwelcker/archive/2005/09/07/461758.aspx

-- Robert

|||http://blogs.msdn.com/bryanke/articles/71491.aspx , in this article ive seen Creating a Reference to the Web service, what is the use of the web service? im confused. im doing a window application and in order to view the reporting services reports i used the web browser control to display my reports. i just navigate the said report and pass the needed information in the url (reportserver) example: webbrowser.navigate(URL)
and in my reportserver i dont have web service just reports and the data source. ur help would be much appreciated! thanks..
|||With the Webservice you can communicate with the server and get informaion about reports and could do management jobs such as creating subscriptions. you could also render a report thru the SOAP-Webservice.
Its similar to a database-connection (if you are more familar with db-connections).
With the SOAP-Connection you don't need to call URLs, you could make a web-reference in any type of VS-Project and then use it like a reference to a dll..
Here's some more MS-stuff to read..
http://msdn2.microsoft.com/en-us/library/ms152787(SQL.90).aspx

|||are the link you've given also works in rs 2000?
thank you! :)
|||

So what namespaces must be imported to use this code.

Thank you,

Brian

|||

I cannot get beyond this line:

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

I get a soap exception everytime stating that the user does not have the permissions to perform the task. Any thoughts on where and what I need to edit to get this working?

Thank you.

programmatically set the paper size and and paper layout

how can i set the paper size and paper layout programmatically in RS. im using c#.net as prog. lang. for this. Pls help thanks!

Use the SOAP API SetProperties to modify the PageHeight and PageWidth of the report.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setproperties.aspx

|||

In addition, you may want to read these two articles:
* http://blogs.msdn.com/bwelcker/archive/2005/09/28/474898.aspx
* http://blogs.msdn.com/bwelcker/archive/2005/09/07/461758.aspx

-- Robert

|||iam using RS 2000. is this SOAP API SetProperties is available in this version? thanks
|||Yes, the code shown in this article should work for RS 2000: http://blogs.msdn.com/bwelcker/archive/2005/09/07/461758.aspx

-- Robert

|||http://blogs.msdn.com/bryanke/articles/71491.aspx , in this article ive seen Creating a Reference to the Web service, what is the use of the web service? im confused. im doing a window application and in order to view the reporting services reports i used the web browser control to display my reports. i just navigate the said report and pass the needed information in the url (reportserver) example: webbrowser.navigate(URL)
and in my reportserver i dont have web service just reports and the data source. ur help would be much appreciated! thanks..
|||With the Webservice you can communicate with the server and get informaion about reports and could do management jobs such as creating subscriptions. you could also render a report thru the SOAP-Webservice.
Its similar to a database-connection (if you are more familar with db-connections).
With the SOAP-Connection you don't need to call URLs, you could make a web-reference in any type of VS-Project and then use it like a reference to a dll..
Here's some more MS-stuff to read..
http://msdn2.microsoft.com/en-us/library/ms152787(SQL.90).aspx

|||are the link you've given also works in rs 2000?
thank you! :)
|||

So what namespaces must be imported to use this code.

Thank you,

Brian

|||

I cannot get beyond this line:

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

I get a soap exception everytime stating that the user does not have the permissions to perform the task. Any thoughts on where and what I need to edit to get this working?

Thank you.

Programmatically Set Sever Timeout Using AMO Classes and Objects

Using DSO object model, We use to Specify the Server Timeout for 4 hours as follows:

oServer.Timeout = 14400

But using the AMO Classes and Objects, How will I set the Server timeout programmatically?

Many Thanks

Subhash Subramanyam

hello Subhash,

i think you can specify the Timeout property in connection string. I.e. something like this:

Server server = new Server();

server.Connect("Data Source=localhost; Timeout=14400;");

// do something

server.Disconnect();

(there is also a Timeout property on DataSource object - though that one affects data source connection i think).

hope this helps,

|||

Hi Mary,

Thanks for the reply, I got what I was looking for.

Programmatically set position of report items

Hi All. I have a report with various items (lines, textboxes) that may or may not be visible, depending upon the values of other report items.

In one list, there is a horizontal line of textboxes and lines. If a line in the middle of it all is set to hidden=true, then all the controls to the right of it are shifted to the left. If it is set to hidden=false then they are shifted back to the right.

Is there a way to fix the position of controls (something like ReportItems!txtTextbox.left=0.444 etc.)? According to BOL, the only property in the ReportItems collection is Value.

Any help is appreciated. D. LewisTry grouping items inside of a container. A rectangle with no visible border can be very useful to keep other items from shifting on the page.

Programmatically Set Flat File Destination Name (relating to timestamp)

One simple question:
I need dumping data from table to a file daily with file name relating to time stamp. The flat file connection manager editor only allow you to set the STATIC file name. Suppose I programmtically passing the time now to a User::TimeNowVariable, how do I set the file name programmatically relating to this variable?

This is a very classic issue associated to Graphical Programming, it seems to be very difficult to understand how to do a very simple programmatic property setting like this, uncless you KNEW it.

I appreciate any helpful info provided.

JordanUse expressions on the Flat File Connection Manager to set the Connection String using the variable.

Programmatically set a Flat File Connection Manager

Hello,

I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value.

The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it.

I do not want to have to maintain 2 DFTs. How can I get around this problem?

Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do that. Does not necessarily HAVE to be a pure programmatic approach.

ANY help would be greatly appreciated!

Feel free to email me at ccorbin@.topcoder.com with any questions, or leave me some good news here :)

THANKS!

Chris Corbin

Software Developer

TopCoder Inc.

I thought this was a simple question. Apparently, you can use an expression to set every property of the File Connection Manager except the column delimiter. Maybe that will change in the near future. There must be a reason.|||ColumnDelimiter is a property of the FlatFileColumn, and not the connection manager. Due to which currently it is not expressionable. Feel free to raise a DCR(Design Change Request) for this issue.|||

How would I go about raising a DCR?

Thanks

|||

To file a bug/DCR, start here:

http://connect.microsoft.com/SQLServer

programmatically schedule the package for later execution in store procedure -

Hello,
Is it possible programmatically schedule the DTS package for later execution in store procedure or maybe in trigger?When you say later execution, please be more specific and describe the scenario you are trying to apply this to.|||Originally posted by rnealejr
When you say later execution, please be more specific and describe the scenario you are trying to apply this to.

For example, user sets flag thru user interface. This action updates some field in table. I would like to have an "update" trigger for this table that will schedule the DTS package to be executed at night. There is some option to do it in VB but it will be cool to perform this thru store procedure.|||You can use the sp_add_schedule or sp_update_schedule - however, a job must already exist for the DTS package.|||Thank you. It's msdb..sp_update_jobschedule.

Thanks again!|||Happy to help.

Programmatically Saving As PDF?

Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.

So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...

Any ideas, thoughts, resources are all welcome.

Well, the answer was actually quite easy to implement as the code below shows...

using (FileStream stream = File.OpenWrite(resultByte))

{

stream.Write(resultByte, 0, resultByte.length);

}

For those that may be interested in the full implementation then check out this article at http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp?df=100.

programmatically restore a database

I recently had to find ways to programmatically backup and restore an
SQL Server Express database.
For backup, I found a one-liner stored procedure that does the job
(BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
My question is, is there something as simple for a restore operation?

Thanks in Advance

-KostasYes, use : RESTORE DATABASE MyNwind FROM MyNwind_1 (i've taken this from
BOL) for more involved examples check BOL

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<kskartsiounis@.yahoo.com> wrote in message
news:1144224541.969405.253280@.g10g2000cwb.googlegr oups.com...
> I recently had to find ways to programmatically backup and restore an
> SQL Server Express database.
> For backup, I found a one-liner stored procedure that does the job
> (BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
> My question is, is there something as simple for a restore operation?
> Thanks in Advance
> -Kostas|||Pardon my ignorance, but what is BOL?|||BOL is the SQL Server Books Online documentation.

Note you may need to specify additional RESTORE options in some situations.
For example, database files will be restored to the original locations by
default. If you are restoring to a machine with a different drive/folder
structure, you'll need specify different file paths using the WITH MOVE
option.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<kskartsiounis@.yahoo.com> wrote in message
news:1144229322.586326.67220@.u72g2000cwu.googlegro ups.com...
> Pardon my ignorance, but what is BOL?|||We use

RESTORE DATABASE DBName FROM DISK='d:\files\Backups\DBName.bak' WITH
REPLACE

Programmatically Render Report to PDF

Hello,

I'm looking for an example on how to programmatically render a report to pdf in C#. I don't want to use a url with the rs:Format=pdf querystring. Any help would be much appreciated!!

Hello,

Have a look at this doc page:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

There is a full example of how to render reports programmatically using the Render method.

-Chris

|||

Hi Chris,

Thanks for your response. I took a look at the link you gave me and now I'm getting the following error:

There was an error opening this document. The file is damaged and cannot be repaired.

Here is the code I'm using:

localhost.ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream;

string[] StreamIdentifiers;

string OptionalParam = null;

string deviceInfo = "<DeviceInfo>" +

" <OutputFormat>PDF</OutputFormat>" +

" <PageWidth>8.5in</PageWidth>" +

" <PageHeight>11in</PageHeight>" +

" <MarginTop>0.5in</MarginTop>" +

" <MarginLeft>0.5in</MarginLeft>" +

" <MarginRight>0.5in</MarginRight>" +

" <MarginBottom>0.5in</MarginBottom>" +

"</DeviceInfo>";

string mimetype;

string encoding;

string historyid = null;

localhost.ParameterValue[] optionalParams = new localhost.ParameterValue[1];

optionalParams[0] = new localhost.ParameterValue();

optionalParams[0].Name = "applicantid";

optionalParams[0].Value = "10";

localhost.Warning[] optionalWarnings = null;

ResultStream = rs.Render("/employmentapplication/printableemploymentapplication",

"pdf",

historyid,

deviceInfo,

optionalParams,

null,

null,

out encoding,

out mimetype,

out optionalParams,

out optionalWarnings,

out StreamIdentifiers);

Response.BinaryWrite(ResultStream);

No matter which report I try to open, I get the same error. Any help is greatly appreciated!!!

Thanks!!

Daniel

|||

You get that error when you attempt to open the document?

It could be that there is some extraneous stuff being written to the HTTP stream to which you do your BinaryWrite. I suggest writing "ResultStream" to a file to isolate where corruption, if any, may be occurring.

Also, the OutputFormat value in DeviceInfo is not necessary when rendering to PDF since you're specifying the render format in the Render method call.

-Chris

|||There shouldn't be any extra bytes in the response stream so you shouldn't worry about that. Make sure that you are actually closing the BinaryWriter. Many of the CLR streams buffer internally, so if they are not closed completely then there can be problems where all of the data is not being written.
|||

Hi John -

Thanks for you response. How do I close Response.BinaryWriter? Do you have an example of programmatically rendering a report to pdf that I can look at? Thanks again for all your help!!!

|||

From the RS side of the things, you are doing things correctly. As Chris pointed out, I would look into making sure that you are properly handling the Response stream. If you are writing any other data to it, then you will run into problems.

You should be able to call .Flush() on the stream to make sure the contents are completely written out.

-John

|||

I was running into the same issue today... calling Flush() and Close() on the response object fixed my issues.

--Andy

|||

Hello,

I have successfully implemented the sample referenced above with a VB.NET console app. I would like to build a CLR stored procedure that implements the same code, programmatically generating a PDF report and outputting it to a file. I'm running into permissions issues.

Anybody had any luck calling the Reporting Services Web Service API from inside a CLR stored procedure?

Ken

|||

Renskemo, you might want to take this question over to the following forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1

Programmatically Render Report to PDF

Hello,

I'm looking for an example on how to programmatically render a report to pdf in C#. I don't want to use a url with the rs:Format=pdf querystring. Any help would be much appreciated!!

Hello,

Have a look at this doc page:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

There is a full example of how to render reports programmatically using the Render method.

-Chris

|||

Hi Chris,

Thanks for your response. I took a look at the link you gave me and now I'm getting the following error:

There was an error opening this document. The file is damaged and cannot be repaired.

Here is the code I'm using:

localhost.ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream;

string[] StreamIdentifiers;

string OptionalParam = null;

string deviceInfo = "<DeviceInfo>" +

" <OutputFormat>PDF</OutputFormat>" +

" <PageWidth>8.5in</PageWidth>" +

" <PageHeight>11in</PageHeight>" +

" <MarginTop>0.5in</MarginTop>" +

" <MarginLeft>0.5in</MarginLeft>" +

" <MarginRight>0.5in</MarginRight>" +

" <MarginBottom>0.5in</MarginBottom>" +

"</DeviceInfo>";

string mimetype;

string encoding;

string historyid = null;

localhost.ParameterValue[] optionalParams = new localhost.ParameterValue[1];

optionalParams[0] = new localhost.ParameterValue();

optionalParams[0].Name = "applicantid";

optionalParams[0].Value = "10";

localhost.Warning[] optionalWarnings = null;

ResultStream = rs.Render("/employmentapplication/printableemploymentapplication",

"pdf",

historyid,

deviceInfo,

optionalParams,

null,

null,

out encoding,

out mimetype,

out optionalParams,

out optionalWarnings,

out StreamIdentifiers);

Response.BinaryWrite(ResultStream);

No matter which report I try to open, I get the same error. Any help is greatly appreciated!!!

Thanks!!

Daniel

|||

You get that error when you attempt to open the document?

It could be that there is some extraneous stuff being written to the HTTP stream to which you do your BinaryWrite. I suggest writing "ResultStream" to a file to isolate where corruption, if any, may be occurring.

Also, the OutputFormat value in DeviceInfo is not necessary when rendering to PDF since you're specifying the render format in the Render method call.

-Chris

|||There shouldn't be any extra bytes in the response stream so you shouldn't worry about that. Make sure that you are actually closing the BinaryWriter. Many of the CLR streams buffer internally, so if they are not closed completely then there can be problems where all of the data is not being written.
|||

Hi John -

Thanks for you response. How do I close Response.BinaryWriter? Do you have an example of programmatically rendering a report to pdf that I can look at? Thanks again for all your help!!!

|||

From the RS side of the things, you are doing things correctly. As Chris pointed out, I would look into making sure that you are properly handling the Response stream. If you are writing any other data to it, then you will run into problems.

You should be able to call .Flush() on the stream to make sure the contents are completely written out.

-John

|||

I was running into the same issue today... calling Flush() and Close() on the response object fixed my issues.

--Andy

|||

Hello,

I have successfully implemented the sample referenced above with a VB.NET console app. I would like to build a CLR stored procedure that implements the same code, programmatically generating a PDF report and outputting it to a file. I'm running into permissions issues.

Anybody had any luck calling the Reporting Services Web Service API from inside a CLR stored procedure?

Ken

|||

Renskemo, you might want to take this question over to the following forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1

Programmatically loop over variables in the variable dispenser?

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

I think you can use Me.variables(x).getType to get the type of each variable. If that is not correct, you can define variables name sXXX for string, iXXX for integer, etc. Then parse the variable name, get the first character and determine the variable type.|||

That would work, but I should clarify my question:

I have the VariableDispenser object passed into my Execute method. I do not know ahead of time what variables will be accessible via this instance of the VariableDispenser object. I would like to loop over each variable that is accessible (again, without knowing its name), then perform an operation on it conditionally based on its type (which I will get from the getType method).

Sorry for not being clearer in my original post!

|||I know that we can say variables(0).xxxx to read the first variable. But I am not sure whether we can get the count of variables. If you can't get the count, what you can do is read variables(0), variables(1), etc like that, and put that code in a try..catch. If you get an exception as 'index out of range', come out of the loop.|||This would be a great way to loop over all the variables assuming I could get access to the entire variables collection to begin with. The problem is that all I have is the VariableDispenser object. The VariableDispenser object methods that interact with the Variables collection (LockForRead, LockOneForRead, etc.) require that I know the name of the variables I would like to access. In my component, I do not know this upfront. Unless I am missing something (and I hope I am), I think I am out of luck?|||

Hi David,

I am not sure whether I understood your problem correctly or not. If I am confusing you, I am sorry. So, what you want is variable name and its type before calling variableDispenser object method. If that is true, it is pretty much simple.

DTS.Variables collection has 'count', 'Name' and 'DataType' properties. Loop thru this collection to get the name of a variable and its datatype. If the datatype is what you are looking for, call the VariableDispenser method using the name that you got.

Hope it will work.

|||

Thiru,

No worries. I am probably not being as clear as I could be - I always find it difficult to describe technical problems on the first 3 or 4 tries. Thanks for the help thus far.

I should have clarified from the beginning that I writing my custom task in C#. All of my code exists within the Execute method of my task. I do not appear to have the equivalent of the DTS.Variables collection available to me. Instead, all I have is the VariableDispenser object. Again, I hope I am missing something simple here.

Thanks again for your help,

David

|||

Can I ask why you want to this? How does the type identify the variables you want to modify? I ask in case there is some better way which we could suggest to acheive your business case.

Donald

|||

The answer is kind of convoluted, but here goes:

I have a package that manages the execution of child packages. Exactly what child packages are to be executed in a given run of the parent package are store in a database table. The child packages stored in the database table are looped over in a ForEach loop, and executed sequentially.

Each child package also requires a set of variables for successful execution. The list of variables required differs for each child package. The value for each of the required variables is not going to be known until run-time, immediately before the child package executes. The easiest way for me to manage this is to have a database table that lists what variables are necessary for each child package, and what expression / database query should be executed at run-time to evaluate each variable.

I have found a number of limitations (perceived or real) within the current SSIS achitecture that makes what I am trying to accomplish difficult. To get around these limitations, I have created my own set of objects to interact with the variables stored in the database, evaluate them at run-time, and put them into a single custom "variable list" object (to which an SSIS variable is assigned) that is available to the child packages.

The problem I have is that my parent package contains the variable(s) assigned to the list object(s). For maximum portability, I do not want my child packages to have to know what the names of those variables are. Instead, I would like to write a custom task that loops over all variables accessible via the variable dispenser, identifies those of my custom type, iterates through all the custom variables in the list, and assigns those values to corresponding ssis child variables (of the same name).

I know - that is quite a mouthful, and likely is not as clear as it could be. I may be going down a path that is unnecessary as I have only been playing around with SSIS for about 3 weeks now. Any help / advice is much appreciated.

Thanks,

David

Programmatically iterating tasks/components in the data flow portion of a package.

HI All,

In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.

Thank you.

Steve.

Hiya Steve,

I know we discussed this offline but just for anyone else's benefit.... the following may help:

Iterate over a package programatically
http://blogs.conchango.com/jamiethomson/archive/2007/03/06/SSIS_3A00_-Iterate-over-a-package-programatically.aspx

-Jamie