Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically add subreports to a report?

Is there any way of adding subreports to a "container" report at runtime?
I need to make the reports composable and would prefer not to have the
superset of all reports in the container and just toggle visibility. Right
now the only solution seems to be to generate my own RDL on-the-fly, which
seems like using a sledge hammer to crack a nut.
Any help would be appreciated.
Thanks
StuOn Apr 26, 10:52 am, Stu <s...@.community.nospam> wrote:
> Is there any way of adding subreports to a "container" report at runtime?
> I need to make the reports composable and would prefer not to have the
> superset of all reports in the container and just toggle visibility. Right
> now the only solution seems to be to generate my own RDL on-the-fly, which
> seems like using a sledge hammer to crack a nut.
> Any help would be appreciated.
> Thanks
> Stu
Aside from creating the RDL files via ASP.NET application (custom as
you mentioned), you could include the normal subreports directly as
tables in the main report and then just toggle the visibility based on
if data is returned as part of the dataset (assuming each table
control will have its own dataset). Something like this might work (as
part of the Visibility expression):
=iif(Sum(Fields!SomeFieldName.Value, "SomeDataSetName") > 0, "true",
"false")
Also, you might want to include several table controls in a rectangle
to conserver space when they are not displayed.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. I think creating the RDL files via the ASP.NET application
suits my purposes more than the "toggle visibility" approach. This is mainly
because there are many containers and many sub reports and there can be any
combination of them. So if someone adds a new subreport I would have to go
and add it at design time to all the containers. If I create the RDL myself I
can just create the requested combination on-the-fly.

Can I build WHERE clause dynamically in a Stored Procedure in SQL Server 2005?

Hello,
I am using SQL Server 2005 and I am just wondering if I can build the
WHERE clause dynamically. I only want to build WHERE class if the my
@.VIN count is of exactly 17 charecters. Here is my code
****************************************
*********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@.vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@.vin) = 17
begin
Where VIN = @.VIn
end
END
****************************************
***************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the
way I am doing it or if it is not possible what is the best way of
writing it?
Thanks
-LYou could do something like this:
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN|||Wow this is popular today.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Learner" <pradev@.gmail.com> wrote in message
news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
> Hello,
> I am using SQL Server 2005 and I am just wondering if I can build the
> WHERE clause dynamically. I only want to build WHERE class if the my
> @.VIN count is of exactly 17 charecters. Here is my code
> ****************************************
*********
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
> @.vin varchar(17)
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT V.vin
> ,C.FirstName
> ,C.LastName
> ,C.AccountNum
> ,D.DealerShipName
> ,Contrt.MaturityDate
> FROM ContractInfo Contrt
> Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
> Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
> Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
> if count(@.vin) = 17
> begin
> Where VIN = @.VIn
> end
> END
> ****************************************
***************
> But when I parse the above stored proc its throwing this message
>
> ********
> Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
> 33
> Incorrect syntax near the keyword 'Where'.
> ********
>
> If it is possible to dynamically build WHERE clause please correct the
> way I am doing it or if it is not possible what is the best way of
> writing it?
> Thanks
> -L
>|||> where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN
At first this struck me as incorrect, but technically it's not, just not in
the format I'd typically expect, e.g. compare the readability to:
WHERE VIN = CASE LEN(@.vin) WHEN 17 THEN @.Vin ELSE VIN END|||Hello,
Thank you all for the above suggestions and WHERE VIN = CASE LEN(@.vin)
WHEN 17 THEN @.Vin ELSE VIN END works.
I am not sure if we build this in SQL Server 2000 but SQL Server 2005
rocks!
Thanks
-L|||Question "du jour". :-)
Would you like a nice bottle of Merlot with that?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueXCca6RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Wow this is popular today.
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
>
>
> "Learner" <pradev@.gmail.com> wrote in message
> news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
>|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||"Learner" <pradev@.gmail.com> wrote in message
news:1142376483.643521.89440@.v46g2000cwv.googlegroups.com...
> Well why not :) By the way what do you mean by "du jour". ?
> Thanks
> -L
It's French.
http://en.wikipedia.org/wiki/Soup_du_jour|||>> I only want to build WHERE class if the my @.VIN count is of exactly 17 ch
arecters.<<
This is crazy. A VIN is CHAR(17) by definition; read your ISO
standard. Furthermroe, it has a very fixed format for each position in
the string. What you want is a CHECK() constraint or procedure that
will validate your input
NOTES ON VIN:
In North America, a system is used that is far more stringent than the
ISO Standards but is "backward compatible." Here, the VIN is divided
into four sections:
The first three characters shall uniquely identify the manufacturer,
make and type of vehicle (with the same exception of manufacturers that
produce less than 500 vehicles). Effectively, this is the WMI. There
are indeed examples of manufacturers who have more than one WMI that
use the third character as a code for a vehicle category (for instance
bus or truck). Just as often however this is not the case;
The second section consists of five characters (VIN positions 4-8) and
identifies the attributes of the vehicle. For each type of vehicle
(passenger cars, MPV's, trucks, buses, trailers, motorcycles,
incomplete vehicles other than trailers), different information is
required. For cars, MPV's and light trucks it is required that the
first two characters of this section are alphabetic, the third and
fourth shall be numeric and the fifth alphanumeric. This section is the
VDS in ISO 3779 but there it comprises another position of the VIN;
The third section consists of one character which is the check digit,
calculated over the other 16 characters of the VIN. This character can
be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of
the VIN. The last five shall be numeric for cars, MPV's and light
trucks and the last four shall be numeric for all other vehicles. The
first character represents the vehicle model year, the second character
represents the plant of manufacture. The third through eighth
characters are a sequential production number (for manufacturers
producing more than 500 vehicles per year). For other manufacturers,
the sixth, seventh and eight positions represent the sequential
production number.
This section confirms to the VIS in ISO 3779.
A portion of the VIN is the WMI (World Manufacturer Identifier) Code.
SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S.
manufacturer, please contact:
Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@.sae.org
Related Standards:
There are several standards available on VINs and WMIs:
SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number
Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification
Number Label
SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content
and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code

Can a TextBox Height and Width be set dynamically?

SSRS 2005
I have a custom code assembly which I am using to control my textbox font
settings at run time. That works fine.
However I would also like to set the Location Left and Top and Size Width
and Height properties of my textboxes using functions in my custom code
assembly.
However no matter what I try I get this error message:
====================================== Property value is not valid.
=...[my function name]...cannot be parsed as a unit because it does not
contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
======================================
I tried returning a string as indicated, a double value, etc. Nothing works.
Is this just a case where these properties cannot be set dynamically? That
seems to be the indication in this old forum post:
10/15/2004
"Text Box... Width"
"The height and width of a textbox are fixed at design time and cannot use
the database table schema to set them at runtime."
--
Chris, SSSITry this, have a parameter point to the code call.
=@.ParameterName
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:30911811-2F6D-454C-B44F-9F2F69ED11B5@.microsoft.com...
> SSRS 2005
> I have a custom code assembly which I am using to control my textbox font
> settings at run time. That works fine.
> However I would also like to set the Location Left and Top and Size Width
> and Height properties of my textboxes using functions in my custom code
> assembly.
> However no matter what I try I get this error message:
> ======================================> Property value is not valid.
> =...[my function name]...cannot be parsed as a unit because it does not
> contain numeric values. Examples of valid unit strings are "1pt" and
> ".5in".
> ======================================> I tried returning a string as indicated, a double value, etc. Nothing
> works.
> Is this just a case where these properties cannot be set dynamically? That
> seems to be the indication in this old forum post:
> 10/15/2004
> "Text Box... Width"
> "The height and width of a textbox are fixed at design time and cannot use
> the database table schema to set them at runtime."
> --
> Chris, SSSI|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I do not understand Chris Connor's suggestion.
Can you clarify what he is suggesting that I do?
What does the =@.Parameter syntax signify?
Is he suggesting that I can call my custom code assmebly function this way,
or is he saying to assign values to TextBox Height and Width using report
parameters instead?
Please read my original post and provide whatever information you can.
Thanks!
-- Chris
-- Chris
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Chris,
I have posted a reply in your post "Can I obtain a reference to a report
item?" and hope that will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
This was my question in my prior reply, but you did not answer it:
"What does the =@.Parameter syntax signify?"
Can you please answer that question?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hi Chris,
> I have posted a reply in your post "Can I obtain a reference to a report
> item?" and hope that will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights
>|||Hello Chris,
I am not sure about what does Chris Conner means but I assume he just
suggest to you to pass the Report Parameter to your code to check whether
you could modify the textbox layout.
But from your scenario, it seems could not been implement. So I replied and
would like to know whether you feel it helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Did you confirm that Chris Connor's suggestion works?
It seems to me that textbox height and width must be hard coded at design
time with a static value and that they can not be set programatically (either
using a custom assembly or a report parameter) at run time. This seems to be
a limitation in the product. Can you please confirm that this is a limitation
in the product?
Or do you have a way to accomplish this dynamic behavior?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I am not sure about what does Chris Conner means but I assume he just
> suggest to you to pass the Report Parameter to your code to check whether
> you could modify the textbox layout.
> But from your scenario, it seems could not been implement. So I replied and
> would like to know whether you feel it helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
Based on my research, since the Height and Width is readonly, you could not
set the value.
My suggestion is to use the XML web services.
You need to generate the RDL file by your self.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
I understand what you are saying. Thank you for the reply.
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Based on my research, since the Height and Width is readonly, you could not
> set the value.
> My suggestion is to use the XML web services.
> You need to generate the RDL file by your self.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>

Can "CREATE DATABASE" or "CREATE TABLE" be wrapped in transactions?

I have some code that dynamically creates a database (name is @.FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one of the following fails, the
database "creation" is rolledback. Otherwise, I would try deleting on error
detection, but it could get messy.

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE DATABASE ' + @.FullName
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
[int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]
NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
[PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> I have some code that dynamically creates a database (name is @.FullName)
and
> then creates a table within that database. Is it possible to wrap these
> things into a transaction such that if any one of the following fails, the
> database "creation" is rolledback. Otherwise, I would try deleting on
error
> detection, but it could get messy.
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
> [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
[datetime]
> NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END

CREATE DATABASE can't be inside a transaction; CREATE TABLE can be. Instead
of rolling back on an error, you could just drop the database and start
again, although if you have a lot of objects, that could be time-consuming.
One other possibility is to restore an empty database with all the objects
but no data, which would be a lot faster, but may not be suitable depending
on what exactly you're doing.

Simon|||That is exactly what I want to do I guess. So I need to look up "restore"?

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f6f48f2$1_2@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> > I have some code that dynamically creates a database (name is @.FullName)
> and
> > then creates a table within that database. Is it possible to wrap these
> > things into a transaction such that if any one of the following fails,
the
> > database "creation" is rolledback. Otherwise, I would try deleting on
> error
> > detection, but it could get messy.
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] (
[ID]
> > [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
> [datetime]
> > NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> > NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> > [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> CREATE DATABASE can't be inside a transaction; CREATE TABLE can be.
Instead
> of rolling back on an error, you could just drop the database and start
> again, although if you have a lot of objects, that could be
time-consuming.
> One other possibility is to restore an empty database with all the objects
> but no data, which would be a lot faster, but may not be suitable
depending
> on what exactly you're doing.
> Simon