Extra xml node

This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent

> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

External scripts / imports / updates

Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.

As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.

Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.

FYI, they are running SQL 2000 on both XP Pro and W2K3 server.

Thanks
StarbYou can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.|||You can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.
Thanks. Dont want to use a third party tool (OSQL etc) and can't use such as DTS and Exec as it is the 'End User' that will use the funtion. It must be run via a simple script from the Mac app.

Cheers
Starbsql

Extending the number of rows returned per page in RS

Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.

Anyone out there have any thoughts on how this might be accomplished?

Thanks!

Travis

Here are a couple of options:

- set PageBreakAtEnd and PageBreakAtStart to false on your tables/matrices/grouping panels

- make your row heights smaller, use a smaller font

- filter your data better, or group it into smaller groups and have each group rendered into its own table, turn off paging on those tables

But turning off paging looks okay when viewing the report in the browser, but can make the report look like *** when exported to PDF.

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a we
ek.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
.................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know wha
t
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using
a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need a
t
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain,
a
> C# program is probabaly better than an extended stored procedure. Extend
ed
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL.
So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation o
n
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a week.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?
"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David
|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
..................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know what
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>
|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David
|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need at
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain, a
> C# program is probabaly better than an extended stored procedure. Extended
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation on
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...
> > In my extended stored procedure, I'm trying to return rows as data is
> > available. The client is expecting to use this data to show progress
> > notification (10%, 20% etc etc).
> >
> > Even with variations of the srv_describe, srv_sendrow & srv_senddone
> > I'm not able to get the client to display the data as it becomes
> > available. Instead, the rows come all together at the end of the call.
> >
> > Has anyone succeeded in acheiving this? What am I doing wrong?
> >
> > Any help appreciated,
> > Kartik
> >
> > ----
> > Here is a snippet of the code..
> >
> > ...
> > srv_describe ( This.srvproc_,
> > 1,
> > "messages",
> > SRV_NULLTERM,
> > SRV_TDS_NVARCHAR, // Dest data type.
> > (DBINT) 512, // Dest data length.
> > SRV_TDS_NVARCHAR, // Source data type.
> > (DBINT) 512, // Source data length.
> > NULL );
> > ...
> >
> > ...
> > srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> > srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> > );
> > if ( !srv_sendrow( This.srvproc_ ) )
> > {
> > ASSERT ( 0 );
> > }
> > ...
> >
> > ...
> > srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> > rowcount );
> > ...

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAm
AEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...|||Hi,
In my experience that isn't the case. The process is internal to SQL Server
(within the memory space) so you would have to jump out of your process and
return to the calling process. I see no facility that does this.
You might want to get in touch with a guy names Gert ER Drapers at
www.sqldev.net. Gert was the architect of the SQL Server Tools in SQL
Server. Gert's a good bloke and will be able to tell you if it can or can't
be done. If it can he can tell you the correct way to do it.
regards
Greg O
http://www.ag-software.com
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403050653.2505cc9c@.posting.google.com...
> Greg,
> I can receive the notification in the extended stored proc. Now to
> inform the client about the notification, I had intended to send a
> single row with the message. But somehow, the client seems to be
> getting all the rows all at once at the end of the extended stored
> procedure.
> The documentation seems to indicate that the API does provide a way to
> send data as it is produced.
> Thanks,
> Kartik
>
> "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...

expression syntax for variable

What would be the correct syntax if I wanted to add the following lines into a variable using an expression? The lines should be the first two rows before my XML.

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">

+@.[User::xml_output]

Thanks,

Phil

Sigh... I hope there's an easier way and I'm just missing it myself...

"<?xml version=" + "\x0x22" + "1.0" + "\x0x22" + "encoding=" + "\x0x22" + "UTF-8" + "\x0x22" + "?>
<dataroot xmlns:od=" + "\x0x22" + "urn:schemas-microsoft-com:officedata" + "\x0x22" + ">" +

@.[User::xml_output]|||The reference guide: http://msdn2.microsoft.com/en-gb/library/ms141001.aspx|||

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

|||

notalreadytaken wrote:

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

Agreed. I must've had another syntax problem when I tried escaping them that way. So, Phil, now you have two options! The way I did it is great for injecting special characters...

Exporting to PDF

I have some rows in a table that are visible based on a Condition. The report renders fine in HTML. After exporting the report to PDF, there are some white spaces appearing at the end of the report. Even after the summary row. ( There is another sub report starts after that report). If I take the hidden condition then the next report appears after this report. No white space.

Suggestions Please?

Thanks again!

Interestingly If you set the height of all your subreports to the same value then the space between the reports are disappearing.

|||

What if you were to select the "Can decrease to accomodate contents" checkbox in the cell that holds the subreport?

HTH

Richard

|||

No. That didn't work. I have changed for each cell in the Sub-Report and for the Cells that Sub report placed. Again it was not a problem with the Report Viewer only if you export to PDF.

Either through the Code or through Report Viewer PDF Export.

Exporting to Excel; timeout

Exporting to Excel; timeout

I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”

Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service

Follow this KB http://support.microsoft.com/default.aspx/kb/909678

Exporting to Excel Tips

Hi !
I am exporting a report to Excel. I have this issue where my labels that
are to the left of my data columns are exported to different rows.
Is there anyone out there can give me some tips or lessons learned when
designing a report that will be exported to Excel?
Thanks,
YI am having a similar problem. I am not using the column headings of the
grid because I need the column headings up in the page header. Instead I
created a row of textboxes that line up with the datagrid up in the header.
It looks great in pdf and html, but as soon as I export to excel some of the
textboxes get pushed down into a lower row. I haven't figured out why yet,
but I think it has something to do with merged cells in excel. If I find
anything I'll post it here.
sebring1130
"Yoshi" wrote:
> Hi !
> I am exporting a report to Excel. I have this issue where my labels that
> are to the left of my data columns are exported to different rows.
> Is there anyone out there can give me some tips or lessons learned when
> designing a report that will be exported to Excel?
> Thanks,
> Y
>
>|||Thanks. I will do the same. Glad to know there are others working on the
same issue.
Take care,
Y
"sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
news:8F97B4EC-D501-4C9F-B053-D7C867C39332@.microsoft.com...
>I am having a similar problem. I am not using the column headings of the
> grid because I need the column headings up in the page header. Instead I
> created a row of textboxes that line up with the datagrid up in the
> header.
> It looks great in pdf and html, but as soon as I export to excel some of
> the
> textboxes get pushed down into a lower row. I haven't figured out why
> yet,
> but I think it has something to do with merged cells in excel. If I find
> anything I'll post it here.
> sebring1130
>
> "Yoshi" wrote:
>> Hi !
>> I am exporting a report to Excel. I have this issue where my labels that
>> are to the left of my data columns are exported to different rows.
>> Is there anyone out there can give me some tips or lessons learned when
>> designing a report that will be exported to Excel?
>> Thanks,
>> Y
>>

Exporting to Excel - File gets too big

Hi,

I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...

My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...

Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this

thanks

Wait for SQL Server 2008 apparently.

One of the biggest resource hogs is output to Excel. One of the whitepapers suggests 2x-3x Ram (ratio to dataset size) is required for Excel gen.

Otherwise, set it on a schedule and email the report when it is done.

Exporting to Excel - File gets too big

Hi,

I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...

My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...

Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this

thanks

Wait for SQL Server 2008 apparently.

One of the biggest resource hogs is output to Excel. One of the whitepapers suggests 2x-3x Ram (ratio to dataset size) is required for Excel gen.

Otherwise, set it on a schedule and email the report when it is done.