Extending the CSV Export to add Text Padding etc.

Hi All,
I am looking at using are report to generate an export file that is similar
to the CSV export...
I need to be able to the text length of the columns and remove the ","
between columns eg:
CSV
Angus,Logan,Data#3
REQUIRED EXPORT
Angus Logan Data#3
text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to 10
(add 4 spaces)
I am thinking about programatically downloading the CSV (or Xml) and parsing
them into the right format but thought there may be an easier solution...
Any Ideas?
Regards
Angus Logan
MCDBA / MCADYou can use the PadRight() method to add the spaces and then use the CSV
renderering extension and set the deviceInfo named FieldDelimiter to
emptystring. You will have to use URL Access or the SOAP API.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angus Logan" <angus_logan@.data3.com.au> wrote in message
news:ePRIhZnYEHA.556@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking at using are report to generate an export file that is
> similar
> to the CSV export...
> I need to be able to the text length of the columns and remove the ","
> between columns eg:
> CSV
> Angus,Logan,Data#3
> REQUIRED EXPORT
> Angus Logan Data#3
> text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to
> 10
> (add 4 spaces)
> I am thinking about programatically downloading the CSV (or Xml) and
> parsing
> them into the right format but thought there may be an easier solution...
> Any Ideas?
> Regards
> Angus Logan
> MCDBA / MCAD
>

Extending SQL Server Management Studio

I am wondering if it is possible to extend the Sql Server Management Studio
user interface by adding plugins. Similar to the way plugins can be
generated for Visual Studio 2005.Not yet--it's something they are talking about adding to a future release.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andy S" <AndyS@.discussions.microsoft.com> wrote in message
news:1FFA776D-388F-413E-81C4-B2AA996FD572@.microsoft.com...
>I am wondering if it is possible to extend the Sql Server Management Studio
> user interface by adding plugins. Similar to the way plugins can be
> generated for Visual Studio 2005.
>|||That's what I feared, thanks!
"Adam Machanic" wrote:

> Not yet--it's something they are talking about adding to a future release.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Andy S" <AndyS@.discussions.microsoft.com> wrote in message
> news:1FFA776D-388F-413E-81C4-B2AA996FD572@.microsoft.com...
>
>

extend time out setting

I have similar problems on some remote sites. Some are running the client
over ISDN which naturally timesout and should pick up the line again when
there is activity. But when the user generates some activity we get the db
process dead message. We migrated these sites from Sybase which never had
these issues and seemed more robust in these situations.
Is there any config options we can use on the client or server to make the
connections less fickle?
Is there any updated client that might be more reliable?
We are using SQL Server 2000 Sp4 but the client is the one on the original
SQl Server 2000 CD.
Thanks,
"Dan Guzman" wrote:

> The dbprocess dead error indicates a terminated connection rather than a
> timeout. The application needs to be smart enough to attempt to reconnect
> following the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <masterwee@.gmail.com> wrote in message
> news:1162278624.684826.264940@.m73g2000cwd.googlegr oups.com...
>
Hi Marty
Unless you have the SQL Server tools on the client PC then there is no point
in using it on the client. You should make sure that a recent version of MDAC
is on there, although I don't think this will cure the issue as the server
can not reach the client and it can not instigate the ISDN connection.
As Dan has stated you will need to handle the error and reconnect the client
if you get this error.
Has the client application changed from powerbuilder?
John
"Marty" wrote:
[vbcol=seagreen]
> I have similar problems on some remote sites. Some are running the client
> over ISDN which naturally timesout and should pick up the line again when
> there is activity. But when the user generates some activity we get the db
> process dead message. We migrated these sites from Sybase which never had
> these issues and seemed more robust in these situations.
> Is there any config options we can use on the client or server to make the
> connections less fickle?
> Is there any updated client that might be more reliable?
> We are using SQL Server 2000 Sp4 but the client is the one on the original
> SQl Server 2000 CD.
> Thanks,
>
> "Dan Guzman" wrote:
|||John,
When any activity happens on the client, it wakes up the ISDN connection and
should carry on where it left off in the application, but it looks like the
Client does not re-connect to the server correctly.
I'm not sure which SQL Server tools you refer to?
As for MDAC, it will be the one on the CD, should there be a later version?
The application is written in C++ not Powerbuilder. It is the database which
has been migrated to SQL Server from Sybase, and the old Sybase Open Client
did not report these errors on the same ISDN line.
Thanks,
M
"John Bell" wrote:
[vbcol=seagreen]
> Hi Marty
> Unless you have the SQL Server tools on the client PC then there is no point
> in using it on the client. You should make sure that a recent version of MDAC
> is on there, although I don't think this will cure the issue as the server
> can not reach the client and it can not instigate the ISDN connection.
> As Dan has stated you will need to handle the error and reconnect the client
> if you get this error.
> Has the client application changed from powerbuilder?
> John
> "Marty" wrote:
|||Hi Marty
Use the MDAC checker from
http://msdn.microsoft.com/data/ref/mdac/downloads/. MDAC can be updated
through other products such as XP SP1, so you may not be on the same version
at all sites. You will need to change the client application so that if the
error is detected the connection is closed and re-open it. I don't know if
the Open Client has the intellegence to silently reconnect or if it was
sending a keep alive.
John
"Marty" wrote:
[vbcol=seagreen]
> John,
> When any activity happens on the client, it wakes up the ISDN connection and
> should carry on where it left off in the application, but it looks like the
> Client does not re-connect to the server correctly.
> I'm not sure which SQL Server tools you refer to?
> As for MDAC, it will be the one on the CD, should there be a later version?
> The application is written in C++ not Powerbuilder. It is the database which
> has been migrated to SQL Server from Sybase, and the old Sybase Open Client
> did not report these errors on the same ISDN line.
> Thanks,
> M
>
> "John Bell" wrote:

Expressions and Datareader Source

Greetings my SSIS friends,

Apologies for asking a similar question again but I am still non the wiser with this problem!

Let me explain to you my situation and the method I've adopted to try and solve it.

I have some source data residing in a SQL Server 6.5 database. The source data consists of a single table. For this example I will assume that my table contains only 2 columns, an ID column called result_ID and a Result_Name.

The idea is to retrieve new data each time the package is run. We will know this because the result_IDs in the source table will be greater than the maximum result_ID in my destination table . The way the package should work is like this :

1) Retrieve maximum result_ID from destination table

2) retrieve data from source table where result_ID > maximum result_ID from destination table.

My package consists of a

1) SQL Query Task which retrieves the maximum result_ID and places it in a user variable (type Int32).

2) A Data flow task with a Datareader source adapter which uses an expression to retrieve the data. My expression looks like this : "select * from result where result_id > " + (dt_str, 10, 1252) @.[User::max_result_id]

When I run my package the first time all the rows are retrieved (as my destination table is empty to begin with). BUT when I run it the second time the same thing happens again!! All rows are retrieved.

I placed a breakpoint at the point where the variable gets populated with the maximum result_ID and true enough, the variable gets populated with the correct result_ID BUT then that variable gets reset to 0 in my expression!

This problem is driving me crazy! Has anybody out there experienced this kind of problem before?! What are the ways to solve it?!

Thanks for your help in advance.

What is the scope of the variable, User::max_result_id?|||

The scpe of my variable is Package so my data flow component shouldn't have a problem accessing it surely.

|||

Phil,

I have just read an interesting article by Kirk Haselden about Variables and their scope and I have now solved my problem! It turns out that I had another variable with a lower scope but with the same name and that was set to 0. In other words my Package level variable was working until SSIS got to the second variable and used that one instead.

I am officially an idiot today.

|||Glad you got it.

EXPORTING SQL.XML data to file

I have a similar problem to that of Barry van Dijk
I need to save SQL.2000 Invoice Data as an XML structured file in a
designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
filename needs to be based on INVOICE number.
Whilst I understand Select .. FOR XML, doing something v.useful with it
{save to file} is taxing my brain! This process must be automatic (stored
proc.)
Simon Bateman
In SQL Server 2000, it is not easy to save any data to a file in the server.
Recommended is to write a small client app that streams the result stream of
the ADO/ADO.Net XML result generated by the FOR XML into a file stream.
If it really has to be done on the server, in SQL Server 2000, you would
have to call the above client app via the sp_OA stored procedures. In SQL
Server 2005, you can use the CLR to write a user-defined function to do so.
Best regards
Michael
"SimonBA" <SimonBA@.discussions.microsoft.com> wrote in message
news:A93EAF21-F891-4897-96BE-AC5184B47B42@.microsoft.com...
>I have a similar problem to that of Barry van Dijk
> I need to save SQL.2000 Invoice Data as an XML structured file in a
> designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
> filename needs to be based on INVOICE number.
> Whilst I understand Select .. FOR XML, doing something v.useful with it
> {save to file} is taxing my brain! This process must be automatic (stored
> proc.)
> --
> Simon Bateman

EXPORTING SQL.XML data to file

I have a similar problem to that of Barry van Dijk
I need to save SQL.2000 Invoice Data as an XML structured file in a
designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
filename needs to be based on INVOICE number.
Whilst I understand Select .. FOR XML, doing something v.useful with it
{save to file} is taxing my brain! This process must be automatic (stored
proc.)
Simon BatemanIn SQL Server 2000, it is not easy to save any data to a file in the server.
Recommended is to write a small client app that streams the result stream of
the ADO/ADO.Net XML result generated by the FOR XML into a file stream.
If it really has to be done on the server, in SQL Server 2000, you would
have to call the above client app via the sp_OA stored procedures. In SQL
Server 2005, you can use the CLR to write a user-defined function to do so.
Best regards
Michael
"SimonBA" <SimonBA@.discussions.microsoft.com> wrote in message
news:A93EAF21-F891-4897-96BE-AC5184B47B42@.microsoft.com...
>I have a similar problem to that of Barry van Dijk
> I need to save SQL.2000 Invoice Data as an XML structured file in a
> designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
> filename needs to be based on INVOICE number.
> Whilst I understand Select .. FOR XML, doing something v.useful with it
> {save to file} is taxing my brain! This process must be automatic (stored
> proc.)
> --
> Simon Bateman