changing all fonts

Any idea how come selecting a matrix and changing the font size does not
affect all of the text? It seems like I have to select each row and to make
this type of change.
Do other people have this same problem?
TIA
DeanOn Jul 27, 6:10 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> Any idea how come selecting a matrix and changing the font size does not
> affect all of the text? It seems like I have to select each row and to make
> this type of change.
> Do other people have this same problem?
> TIA
> Dean
This seems to be a common occurrence. This is also true for table
controls. Sorry that I cannot offer any further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Changing a SQL Statement as it's passed to the server.

Wondering if this is possible...

If have a program (a standalone .Exe) that passed the following into our SQL Server.

SELECT l.LabelType, l.PalletLabelID, l.DelSchedID, l.AsnID, d.BasicPartNo, d.LatestPartNo, d.Description, d.PartColour, d.ContainerCode, d.PackFactor FROM Label l INNER JOIN DeliverySchedule d ON l.DelSchedID = d.DelSchedID WHERE l.SupplierID = 1 AND l.WarehouseID = 1 AND l.LabelID = ?

The statement is always the same, the only thing that differs is the LabelID field, which can be any number between 000000 and 999999.

The problem is however is that the statement is wrong! (very wrong), and I was wondering if it was possible to do any of the following:

Create something which could be classed as a "Select Trigger" (that is, I can create a script to determine what values are returned back).

Create something that constantly looks for that sentance been passed and then run my script to return the correct data.

I'm at a loss of ideas on how to achieve what I'm after.

The .Exe can NOT be changed.

Would really appreciate some feedback.

Define "wrong". You could possibly use views to make the data look like the query, if some data is wrong. There are a few things you can do performancewise if it is performance issue.

I don't think there is anything that you can do to capture the statement and change it in stream (unless you wrote your own drivers and used them instead of the OleDb or ODBC drivers, which is probably harder than just rewriting your EXE from scratch.)

>>The .Exe can NOT be changed.<<

Is it that large of a program? Is it a third party thing?

changing a query

This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
Thanks> the DeliveredDate is empty
If by 'empty' you mean NULL, you can add the following to your WHERE clause:
AND DeliveredDate IS NULL
For non-null values:
AND DeliveredDate IS NOT NULL
--
Hope this helps.
Dan Guzman
SQL Server MVP
"johnfli" <john@.here.com> wrote in message
news:us241LQzEHA.2196@.TK2MSFTNGP14.phx.gbl...
> This is my current query that I have in an App I wrote:
> MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
> Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
> PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
> Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
> DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
> LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
> tmpMyShippers & " Order by [" & strSort & "] desc "
>
> This works fine.
> I need to modify it a little I need to have one query that will return
> when
> the DeliveredDate is empty
> and anohter query to return the ones that have somethign in the
> DeliveredDate field.
>
> Thanks
>

Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob

Changing @@version

Hi
I have Sql2000 Server , i want to change the @.@.version.
Can i change the result of SELECT @.@.version ?
Where can i do this ?
Paul
No you can't change it...it's a built in function.
-Sue
On Wed, 31 Aug 2005 04:29:01 -0700, "paul"
<paul@.discussions.microsoft.com> wrote:

>Hi
>I have Sql2000 Server , i want to change the @.@.version.
>Can i change the result of SELECT @.@.version ?
>Where can i do this ?
|||Hi,
No you cant manually change that. As soon as you install the service
pack/security updates the version is automatically updated.
Thanks
Hari
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:26F0A282-D97C-4C78-BAB7-E9518F17E8D7@.microsoft.com...
> Hi
> I have Sql2000 Server , i want to change the @.@.version.
> Can i change the result of SELECT @.@.version ?
> Where can i do this ?
> --
> Paul
|||As the others stated, you cannot change @.@.version, or the values returned by
the other system functions whose names start with @.@.. Also, it would be very
confusing to DBAs or others if @.@.version on an instance of the database
engine started reporting something other than the version of the software.
What do you want to change? Do you want to pull parts of the information
out? If so, have a look at the SERVERPROPERTY function, different parameters
for that function report different parts of the information available in
@.@.version. Or maybe you could use SUBSTRING to pull out the part you're
interested in.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi
I have a aplication (software) that only run if the answerd of the select
@.@.version is : MSDE SQL serrver... ..... .
But my aplication is already with 1,9 Gb , so if i change to SQL SERVER the
aplication will not run.
If i can use SQL Server and altered the result of @.@.version , to MSDE .....
, i think that the aplication will run .
Paul
"Alan Brewer [MSFT]" wrote:

> As the others stated, you cannot change @.@.version, or the values returned by
> the other system functions whose names start with @.@.. Also, it would be very
> confusing to DBAs or others if @.@.version on an instance of the database
> engine started reporting something other than the version of the software.
> What do you want to change? Do you want to pull parts of the information
> out? If so, have a look at the SERVERPROPERTY function, different parameters
> for that function report different parts of the information available in
> @.@.version. Or maybe you could use SUBSTRING to pull out the part you're
> interested in.
> --
> Alan Brewer [MSFT]
> Content Architect, SQL Server Documentation Team
> SQL Server Developer Center: http://msdn.microsoft.com/sql
> SQL Server TechCenter: http://technet.microsoft.com/sql/
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||I suggest you contact the software author or vendor for support. Safest
to assume there's a valid reason why such a constraint would be built
in to the software. For example maybe the vendor only licenses the
product for use with MSDE.
David Portas
SQL Server MVP

Changing @@version

Hi
I have Sql2000 Server , i want to change the @.@.version.
Can i change the result of SELECT @.@.version ?
Where can i do this ?
--
PaulNo you can't change it...it's a built in function.
-Sue
On Wed, 31 Aug 2005 04:29:01 -0700, "paul"
<paul@.discussions.microsoft.com> wrote:
>Hi
>I have Sql2000 Server , i want to change the @.@.version.
>Can i change the result of SELECT @.@.version ?
>Where can i do this ?|||Hi,
No you cant manually change that. As soon as you install the service
pack/security updates the version is automatically updated.
Thanks
Hari
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:26F0A282-D97C-4C78-BAB7-E9518F17E8D7@.microsoft.com...
> Hi
> I have Sql2000 Server , i want to change the @.@.version.
> Can i change the result of SELECT @.@.version ?
> Where can i do this ?
> --
> Paul|||As the others stated, you cannot change @.@.version, or the values returned by
the other system functions whose names start with @.@.. Also, it would be very
confusing to DBAs or others if @.@.version on an instance of the database
engine started reporting something other than the version of the software.
What do you want to change? Do you want to pull parts of the information
out? If so, have a look at the SERVERPROPERTY function, different parameters
for that function report different parts of the information available in
@.@.version. Or maybe you could use SUBSTRING to pull out the part you're
interested in.
--
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I have a aplication (software) that only run if the answerd of the select
@.@.version is : MSDE SQL serrver... ..... .
But my aplication is already with 1,9 Gb , so if i change to SQL SERVER the
aplication will not run.
If i can use SQL Server and altered the result of @.@.version , to MSDE .....
, i think that the aplication will run .
Paul
"Alan Brewer [MSFT]" wrote:
> As the others stated, you cannot change @.@.version, or the values returned by
> the other system functions whose names start with @.@.. Also, it would be very
> confusing to DBAs or others if @.@.version on an instance of the database
> engine started reporting something other than the version of the software.
> What do you want to change? Do you want to pull parts of the information
> out? If so, have a look at the SERVERPROPERTY function, different parameters
> for that function report different parts of the information available in
> @.@.version. Or maybe you could use SUBSTRING to pull out the part you're
> interested in.
> --
> Alan Brewer [MSFT]
> Content Architect, SQL Server Documentation Team
> SQL Server Developer Center: http://msdn.microsoft.com/sql
> SQL Server TechCenter: http://technet.microsoft.com/sql/
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||I suggest you contact the software author or vendor for support. Safest
to assume there's a valid reason why such a constraint would be built
in to the software. For example maybe the vendor only licenses the
product for use with MSDE.
--
David Portas
SQL Server MVP
--

Changing @@version

Hi
I have Sql2000 Server , i want to change the @.@.version.
Can i change the result of SELECT @.@.version ?
Where can i do this ?
PaulNo you can't change it...it's a built in function.
-Sue
On Wed, 31 Aug 2005 04:29:01 -0700, "paul"
<paul@.discussions.microsoft.com> wrote:

>Hi
>I have Sql2000 Server , i want to change the @.@.version.
>Can i change the result of SELECT @.@.version ?
>Where can i do this ?|||Hi,
No you cant manually change that. As soon as you install the service
pack/security updates the version is automatically updated.
Thanks
Hari
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:26F0A282-D97C-4C78-BAB7-E9518F17E8D7@.microsoft.com...
> Hi
> I have Sql2000 Server , i want to change the @.@.version.
> Can i change the result of SELECT @.@.version ?
> Where can i do this ?
> --
> Paul|||As the others stated, you cannot change @.@.version, or the values returned by
the other system functions whose names start with @.@.. Also, it would be very
confusing to DBAs or others if @.@.version on an instance of the database
engine started reporting something other than the version of the software.
What do you want to change? Do you want to pull parts of the information
out? If so, have a look at the SERVERPROPERTY function, different parameters
for that function report different parts of the information available in
@.@.version. Or maybe you could use SUBSTRING to pull out the part you're
interested in.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I have a aplication (software) that only run if the answerd of the select
@.@.version is : MSDE SQL serrver... ..... .
But my aplication is already with 1,9 Gb , so if i change to SQL SERVER the
aplication will not run.
If i can use SQL Server and altered the result of @.@.version , to MSDE .....
, i think that the aplication will run .
Paul
"Alan Brewer [MSFT]" wrote:

> As the others stated, you cannot change @.@.version, or the values returned
by
> the other system functions whose names start with @.@.. Also, it would be ve
ry
> confusing to DBAs or others if @.@.version on an instance of the database
> engine started reporting something other than the version of the software.
> What do you want to change? Do you want to pull parts of the information
> out? If so, have a look at the SERVERPROPERTY function, different paramete
rs
> for that function report different parts of the information available in
> @.@.version. Or maybe you could use SUBSTRING to pull out the part you're
> interested in.
> --
> Alan Brewer [MSFT]
> Content Architect, SQL Server Documentation Team
> SQL Server Developer Center: http://msdn.microsoft.com/sql
> SQL Server TechCenter: http://technet.microsoft.com/sql/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||I suggest you contact the software author or vendor for support. Safest
to assume there's a valid reason why such a constraint would be built
in to the software. For example maybe the vendor only licenses the
product for use with MSDE.
David Portas
SQL Server MVP
--

Change URL

Can any body help me i need to chanage my URL when select my TPM and TPK

i have two option in my Filter 1.TPM

2.TPK

i writen some custome code here but its not working i know some thing wrong in that

its my Code here:-

Public Function access(ByVal parameter As Object) As String

IF parameter Is "TPM" Then

Response.Write("<script language=""Javascript"">window.open ('//localhost/Reportserver/Pages/ReportViewer.aspx?%2fTPM%2fAFM1&rs:Command=Render&rc:Toolbar=true&RUNDATE=" & Parameters!TPM.Value & ' ,'mywindow','location=1,status=1,resizable=yes,scrollbars=1,width=400,height=400');</script>")

ElseIF parameter Is "TPK" Then

Response.Write"<scriptlanguage="javapt"">window.open //localhost/Reportserver/Pages/ReportViewer.aspx?%2fTPM%2fTPM01&rs:Command=Render&rc:Toolbar=true&TPK=" & Parameters!TPK.Value & " ','_blank','location=1,status=1,resizable=yes,scrollbars=1,width=400,height=400');</script>")

End If

End Function

Normal Url is working properly actual problem is i want to change URL depends on parameter selection.Custome code is giving some error like

Error: Response is not declared......

when i written custome code i will declare my parameter=code.access(parameter!sao.value) ? Is it Currect?

Can anybody tell me how to do this one because i really struck here only..

Thanking you.

Your code assumes that you are running in the context of an ASPX page. This isn't the case. The Response object is never declared. So the error message is correct.

|||

Wouldn't it be better if u use the reportview control and embed the report in ur aspx page?
Patrick

|||

thanx for your reply

can u give me some example code.Its actually Report Project only i shoudt use aspx pages

where can i change my custom code can u give me some any example

thanx

kiran

|||

Try reading through this website at:-
http://www.gotreportviewer.com/

|||

it ok that site.Its good

But i didt use any aspx pages in my reports i am using only Business intelligece through i developing my reports only i shoudt use aspx pages any where

i am developing only reports only.

in my report Two Filter i need to change my Url dynamically .This is Report1:

Reortid report Name PD KF TK

1 report1 1 0 0

2 report2 2 2 3

3 report3 2 1 0

Actually Here No need to change anydata Here just change my URL only what ever i select that particual option in my filter

in my filter On option is Change Url filter

in two option in TPM and TPK

This Filter is not related to anything data

this filter job is only change the URL sonly.Suppose i will select TPM option it wil come that related URL only.

when i press PD Data it will take hyper like goes to another report ok

i will select TPK option it will change my URL that related URL only.

i am giving one example:

if(value=TPM)

-its going to what ever declare here jump URL

else if (value=TPK)

-its going to what ever declare here jump URL

end

i dont know how to do this task.can any body

thanx

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoonOn the clientside, you would just run Client Network Utility (cliconfg.exe).
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegroups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac...ds/default.aspx)
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegroups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>|||oj:
Thank you.
JCVoon

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoonOn the clientside, you would just run Client Network Utility (cliconfg.exe).
--
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegroups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac/downloads/default.aspx)
--
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegroups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>|||oj:
Thank you.
JCVoon

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoon
On the clientside, you would just run Client Network Utility (cliconfg.exe).
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegr oups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>
|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon
|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac/...s/default.aspx)
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegro ups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>
|||oj:
Thank you.
JCVoon

Change the table structure

Hi...

I execute select statement on three tables and i get the following table:

ID

Value

Name

1

10

color

2

20

color

3

30

color

4

40

color

and, from this table i want to create the following table:

ID

color

1

10

2

20

3

30

4

40

mean, the value color become the header title of the column.

How can i do that?

thank you...

But you use a GridView or what to show the table ?

If you use a GridView you can set the Visible attribute of a BoundField at runtime.

|||

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

|||

may215:

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

Are you going to insert values in the second table, or its value will be same as the first table but columns' names are diffrents?

If you are not going to insert values in the second table, then I suggest to create a view that get its data from the first table.

1CREATE VIEW MyViewForTheSecondTable23AS45SELECT [ID], [VALUE]AS'Color'67FROM MyFirstTable8910

Now, you can do the SELECT on the view like:

1SELECT *FROM MyViewForTheSecondTable

The returned result will be same as in the first table but columns name will be ID and Color insted of ID and Value.

Good luck.

|||

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

|||

may215:

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....i

I cheked the example I wrote .. it is working fine.

Here is a complete senario that worked on my machine and it should work on your machine as well:

1-- create sample database2create database MyTestDB3go45-- use the sample database6use MyTestDB7go89-- create new table for testing10CREATE TABLE MyTable11(12[ID]INT,13[VALUE]CHAR(10)14)15GO1617-- insert two records in the testing table18INSERT INTO MyTableSELECT 4,'red'19INSERT INTO MyTableSELECT 5,'black'202122-- create a view23CREATE VIEW MyViewForTheSecondTable2425AS2627 SELECT [ID], [VALUE]AS'COLOR'2829FROM MyTable303132-- select data from the created view33SELECT *FROM MyViewForTheSecondTable

Please let me know if it did not work with you for any reason.

may215:

in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

First of all, please make sure those columns are exists in the table and they were typed correctly.

Then try this:

1SELECT prop.PhonePropertyValue, prop.PhonePropertyNameAS'color'23FROM dbo.TblPhoneType pt4INNERJOIN5 dbo.TblPropertyForPhoneType prop6ON pt.PhoneTypeID = prop.PhoneTypeID7INNERJOIN8 dbo.TblPhoneProperty pp9ON pp.PhonePropertyID = prop.PhonePropertyID10WHERE pp.PhonePropertyID ='34'11

Good luck.

Change the Column Heading on select statement

I have a problem I don't know if there's a way to solve it.

I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.

SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID

thanks.RE: I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID thanks.

Q1 [Is it possible to have a result set heading e.g. , 'DRIVER NAME', conditionally appear as something else e.g. , 'HELPER NAME', depending on a passed parameter being either 'D' or 'H'?]
A1 Yes. Assign the passed parameter to a variable in the stored procedure; use a Case statement to evaluate and appropriatly use either 'DRIVER NAME' or 'HELPER NAME' in your result header column.

the logic [psudocode] amounts to something like:
Case @.vPassedParameter = 'H'
Then @.vColumnHeader = 'HELPER NAME'
Case @.vPassedParameter = 'D'
Then @.vColumnHeader = 'DRIVER NAME'

Change the @@Servername

Hi,
We have changed the server name. If I run select @.@.servername, it came out
the same server name.
any ideas>
Thanks,Change the script below to specify the new server name and execute. Then
restart SQL Server.
EXEC sp_dropserver @.@.SERVERNAME
EXEC sp_addserver 'NewServerName', 'local'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||Check out sp_dropserver and sp_addserver in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
Hi,
We have changed the server name. If I run select @.@.servername, it came out
the same server name.
any ideas>
Thanks,|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||In addition, if you had any SQL Agent jobs defined before the rename you
need to take an additional step or you won't be able to modify them. See
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>

Change the @@Servername

Hi,
We have changed the server name. If I run select @.@.servername, it came out
the same server name.
any ideas>
Thanks,Change the script below to specify the new server name and execute. Then
restart SQL Server.
EXEC sp_dropserver @.@.SERVERNAME
EXEC sp_addserver 'NewServerName', 'local'
Hope this helps.
Dan Guzman
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||Check out sp_dropserver and sp_addserver in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
Hi,
We have changed the server name. If I run select @.@.servername, it came out
the same server name.
any ideas>
Thanks,|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||In addition, if you had any SQL Agent jobs defined before the rename you
need to take an additional step or you won't be able to modify them. See
http://www.karaszi.com/SQLServer/in...server_name.asp
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>|||thanks all
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OsK7a4brGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have changed the server name. If I run select @.@.servername, it came out
> the same server name.
> any ideas>
> Thanks,
>

Change SQL Command at runtime?

I have tried some of the fixes for the other posts but it still doesn't work.
My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?Use an expression as your source. Note that once you do this in the dataset
tab that you can no longer execute the query or get the field list returned
so make sure you have the appropriate field list before you change it to an
expression.
="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
Parameters!myParam.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
>I have tried some of the fixes for the other posts but it still doesn't
>work.
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?|||How do I set the source? This query is a very large query with many other
parameters being used to narrow down the search and return data. I use a
text command type in the data tab.
"Bruce L-C [MVP]" wrote:
> Use an expression as your source. Note that once you do this in the dataset
> tab that you can no longer execute the query or get the field list returned
> so make sure you have the appropriate field list before you change it to an
> expression.
> ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> Parameters!myParam.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> >I have tried some of the fixes for the other posts but it still doesn't
> >work.
> >
> > My query looks like this:
> > SELECT *
> > FROM table1 AS t1, table1 AS t2
> > WHERE t1.id =t2.id + @.myParam
> >
> > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> >
> > How can I make this work?
>
>|||Ok I have it figured out. I left the existing parameters setup as is and
just put a =" at the beginning of the query, a " at the end, and took out all
Carriage Returns and it works. Thanks for all of your help!
"KCBTDev" wrote:
> How do I set the source? This query is a very large query with many other
> parameters being used to narrow down the search and return data. I use a
> text command type in the data tab.
> "Bruce L-C [MVP]" wrote:
> > Use an expression as your source. Note that once you do this in the dataset
> > tab that you can no longer execute the query or get the field list returned
> > so make sure you have the appropriate field list before you change it to an
> > expression.
> >
> > ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> > Parameters!myParam.Value
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> > news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> > >I have tried some of the fixes for the other posts but it still doesn't
> > >work.
> > >
> > > My query looks like this:
> > > SELECT *
> > > FROM table1 AS t1, table1 AS t2
> > > WHERE t1.id =t2.id + @.myParam
> > >
> > > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> > >
> > > How can I make this work?
> >
> >
> >

Change SQL command at runtime using a stored procedure?

My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?
I did have my report working correctly with my source as an expression. But
with added complexities to the query, it will be easier to maintain in a
stored procedure.Sounds like you need to research dynamic sql
"KCBTDev" wrote:
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?
> I did have my report working correctly with my source as an expression. But
> with added complexities to the query, it will be easier to maintain in a
> stored procedure.

Change results in column to display different

I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..

SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASCSELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID,
(CASE WHEN ISNUMERIC(SDI.DISPLAYVALUE) = 0 THEN Null
ELSE SDI.DISPLAYVALUE END) SDI_DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

Change query issues

I am trying to take this statement:

Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

and change up (cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

I need that part to do this instead there: (PostedAmount + Sum of all PDC where the PDC in this month + Sum all CC where CC in this month) / FeeSchedule) * 100 as gt

Here is my failed attempt:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

BEGIN
Select Total = SUM(gt)
FROM
(Select Distinct CustomerName,
CustomerNumber,
FeeGoal_AZ AS FG,
FeeSchedule,
PostedAmount
+ SUM(select PCD from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
+ SUM(select CC from dcr where EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dc.DateEntered IS NOT NULL)
* FeeSchedule) * 100 AS gt
from DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule
) as dTable

Once again your problem is that you cannot do a subquery inside a select list, ever. You can use a subquery as a derived table, so long as that subquery is a single sql statement, and then join to that. I am not fully sure what you are trying to do here. First of all, the above query can be simplified somewhat, as I was not reading the SQL thoroughly. The top statement can be simplified to:

Select SUM((cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 )
FROM DCR WHERE branch='00002'
group by CustomerName,
CustomerNumber,
FeeGoal_AZ,
FeeSchedule

As for the rest of the query, you need to explain exactly you are trying to do here. You can probably join your subqueries into the overall query, and sum the PDC column by joining to the DCR column in the from clause, and adding the where conditions to the end of the query. Note that you can join to mulpiple copies of the same table by using different aliases. Ypu may also be able to use the MONTH function to avoid all that complex date calculation logic. Check out this link and see if there is an easier way to do the same thing:

http://msdn2.microsoft.com/en-us/library/ms186724.aspx

Anyway, should be something like:

DECLARE @.today DATETIME,
@.datefirst datetime

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select Distinct dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ AS FG,
dcr.FeeSchedule,
(dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100
from DCR as dcr
join DCR as dcr2
on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dcr2.DateEntered IS NOT NULL
-- enter your own code in this line -- AND dcr2.PrimparyKeyField(s) = dcr.PrimparyKeyField(s)
join DCR as dcr3
on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)
AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND dc3.DateEntered IS NOT NULL)
WHERE dcr.branch='00002'
group by
dcr.CustomerName,
dcr.CustomerNumber,
dcr.FeeGoal_AZ,
dcr.FeeSchedule

You will probably have to rewrite that slightly as I am guessing at your database schema. You also don't need to have the same condition in each join as they are all joining to the same table. So for example having dcr.DateEntered IS NOT NULL once is sufficient. You will also have to join the three copies of the DCR table together on their primary keys, as I do not know what they are.

HTH

Remember, as with the CASE statement problem a few days ago, you cannot have a subquery within the column list of a select statement. You can only have a single subquery as a derived table in the from clause where you join to it.

|||

I'm lost, here is what I put

Select Distinct dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ AS FG,

dcr.FeeSchedule,

((dcr.PostedAmount + SUM(dcr2.CC) + SUM(dcr3.CC)) / dcr.FeeSchedule) * 100

from DCR as dcr

join DCR as dcr2 on dcr2.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dcr2.DateEntered IS NOT NULL

AND dcr2.CustoemrNumber = dcr.CustomerNumber

join DCR as dcr3 on dcr3.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today)

AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND dc3.DateEntered IS NOT NULL)

WHERE dcr.branch='00002'

group by dcr.CustomerName,

dcr.CustomerNumber,

dcr.FeeGoal_AZ,

dcr.FeeSchedule

|||

So what is the problem with that code? You didn't say. You haven't joined on the primary key of the table, which you will need to do otherwise you will get a cartesian product. So you will need a

join dcr2 on dcr.dcrID = dcr2.drcid

or something like that. You may also need to have outer joins.

What subsets are you trying to get from DCR? I suggest you define clearly what they are, write three separate queries to get them, test them and then combine them into one statement. You may not need three copies of one table either, you may be able to do this with one copy. If you want three different calculations performing on the same subset of data, then one table will suffice. However, if you want to intersect three different subsets of the same data, then three table copies will be required.

Please add more explanation, I often have trouble determining what exactly your problems are, I need more information to solve.

|||

I don't know, that's why I need some hand holding here ! You probably have difficulty because this isn't usual requests for an IT programmer do code this kind of crap.

OK, let me try to explain from scratch.

I have a table called DCR

I am basically having to recreate this complicated Excel doc into SQL Server and Reporting Services to automate it.

Right now, I am not able to create the expression I need in my Reporting Services 2005 footer field which simply sums up that group column so I have to resort to doign the sum in SQL then bring in the sum using a dataset in my Report....which to me is totally stupid, overdone.

Anyway, For this total (subtotal of my column), I have to calculate the grand total of the following equation for each customer record. This is a transaction table therefore each customer record will have several entries.

First, let me give you some business formulas.

In-House1 = PostedAmount + Sum of PDC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate between '2/1/2006' and '2/28/2006' + Sum of PDC where EnteredDate not between '2/1/2006' and '2/28/2006' + Sum of CC where EnteredDate not between '2/1/2006' and '2/28/2006'

In-House2 = InHouse1 * FeeSchedule

Now, my table is like this

DCR Table fields:

TransID (not needed in my query, just for fyi)

CustomerNumber

PDC (number)

CC (number)

Entered Date

FeeSchedule (int)

FeeGoal (bigint) - is updated from user entry using an asp.net form, i then update each customer trans record in dcr with the same fee goal that the user entered

Example data

--

TransID CustomerNumber PDC CC EnteredDate FeeGoal FeeSchedule

1 200 100 250 '2/2/2006' 2233 50

2 200 300 200 '2/5/2006' 2233 50

3 400 233 444 '2/6/2006' 1000 30

4 400 3333 332 '2/6/2006' 1000 30

5 400 2233 300 '2/18/2006' 1000 30

So now, if you figure out InHouse1's forumula using the fields from this table

then I want to take InHouse1 for each company and perform InHouse2's formula

this is as best as I can describe it...once again, their trying to shove an excel into a dtabase which I don't believe is normal, at least not in my 8 years of reporting, we wouldn't even touch that and have once and know what pains you have to go through...that's why they make Excel!

|||

Thank you, that makes things a lot clearer.

After a quick look at this, it would appear this problem needs a different approach. I will describe it now, I don;t have time to write it for you. Please try doing it this way and let me know if
it works, I will be online later to check. OK, instead of doing one big query, use a union, with a different query for each subtotal. As you are doing a UNION ALL, you will need to ensure
that each column returned from each unioned query has the same number of columns, with the same data type and precision. The names can differ. Now place all of that UNION ALL into a derived table
(just surround it all in parentheses and refer to the whole thing as if it were a table. The column names out the UNIONEd columns will assume whatever name you specified in the first of your UNIONED query.
Do you follow so far. Then in the outer query, SUM up the subtotal column as one column, and perform your second in house formula in the second statement. To to this, you will have to return the FeeSchedule column
in the UNION as well, and then repeat the formula from the previous column, multiplying it by that amount. It will look something like

SELECT SUM(sub.a), SUM(sub.a * sub.FeeSchedule)
FROM
(SELECT xyz....
FROM DCR
UNION ALL
SELECT xyz....
FROM DCR
SELECT xyz....
FROM DCR) as sub

You will have to fill in the details. Let me know how you get on....

|||

Ok, try this:

select SUM(subTotal) * FeeSchedule
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
)
group by FeeSchedule

You may want to change the date formulas, I just hard coded the values based on your description. Do you need these totals separately, or do you just want the end number per customer? If you want to know the values individually before summing them up, then you will have to resort to using multiple copies of the table, joining by customer number and some other field, to make sure like is joining like, and you are getting a semi cartesian join. I am confused by your date ranges. They are mutually exclusive. If you just want the grandtotal of both, don't bother to add the date filter clauses, adding the value for all the days in february to the value for all the days not in february is the same as saying add the value for all days in the table together! Do you see my point. It is like saying add 3, 4 and 5. Now add all the other numbers, 1 to 10 that are not 3,4 and 5. It is the same as saying add all the numbers from 1 to 10, yet the latter is a lot simpler to perform.

Is the FeeSchedule the same value for each customer? If it is, the above query will work. If it differs for the same customer, then I would assume you would want the total of all FeeSchedules for each customer as well, before multiplying by the SUM(PDC) + SUM(CC). If that is the case, remove FeeSchedule from the group by's in the UNION expression, and add a SUM in front of the colum to get the SUM of FeeSchedules instead in the UNION sub query\derived table (both terms are interchangeable I think).

I hope I explained that well, it is quite a complex query, I am not surprised it is causing some issues. Let me know if this solved the issue or not,

Simon

|||

Oops, slight mistake in above query. I am only human after all.

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL)
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL)
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

I forgot you needed the grand total by customer.

HTH

|||Shughes , I really appreciate your support. I have never used unions before, never had the need until now. I'll give it a try, thanks for really taking the time to show me this! You are saving my life here, I'm still trying to figure this thing out after an entire day!|||

I am getting:

Incorrect syntax near the keyword 'where'.

Msg 156, Level 15, State 1, Procedure SSRS_Get_Tot_InHouse2, Line 29

Incorrect syntax near the keyword 'where'.

let me see if I can fix it...

|||My bad, I added an extra closing parenthesis at the end of the where clause (after the NOT NULL). Sorry, but unable to test my end as do not have database. Try this:

select SUM(subTotal) * FeeSchedule, CustomerNumber
from
(
select SUM(PDC) as subTotal,
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate between '2/1/2006' and '2/28/2006')
or (EnteredDate not between '2/1/2006' and '2/28/2006'))
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(PDC)
and DateEntered IS NOT NULL
union all
select SUM(CC),
CustomerNumber ,
FeeSchedule
from dcr
group by CustomerNumber, FeeSchedule
where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')
or (not between '2/1/2006' and '2/28/2006'))
and DateEntered IS NOT NULL
-- This does not make sense, as these two values are mutually exclusive
-- (they are the opposite of each other!). Just SUM(CC)
)
group by FeeSchedule, CustomerNumber

That should do it!
|||

the where clause needs to come before the group by:

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

selectSUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group by CustomerNumber, FeeSchedule

union all

selectSUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group by CustomerNumber, FeeSchedule

)

group by FeeSchedule, CustomerNumber

Now I'm getting one error but can't see it:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.

|||

in the end I should end up with only one sum, one result. It looks like your query will end up with

Customer tot

Customer tot

...

but that/s now what I want...anyway, still trying to fix the last error.

|||Thar should work. Are you still getting an error?|||

select SUM(subTotal) * FeeSchedule, CustomerNumber

from

(

select SUM(PDC) as subTotal,

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate between '2/1/2006' and '2/28/2006')

or (EnteredDate not between '2/1/2006' and '2/28/2006'))

and DateEntered IS NOT NULL

group byCustomerNumber,

FeeSchedule

union all

select SUM(CC),

CustomerNumber ,

FeeSchedule

from dcr

where ((EnteredDate BETWEEN where EnteredDate between '2/1/2006' and '2/28/2006')

or (not between '2/1/2006' and '2/28/2006'))

and DateEnteredIS NOT NULL

group byCustomerNumber,

FeeSchedule

)

group byFeeSchedule,

CustomerNumber

same error but I don't see anything wrong:

Msg 156, Level 15, State 1, Line 18

Incorrect syntax near the keyword 'where'.

Change Print Range of Reporting Services Report

Hi,
Can anyone tell me how i change the print range of a RS report.
My report is fine when viewed, but when i select print preview, it chops off
a lot of the report and places it on a separate page.
Is there any way of setting the entire report to prin on a single page?
Also, how do i change the orientation or a report from portrait to landscape?
Thanks in advance.see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leigh Moore" <Leigh Moore@.discussions.microsoft.com> wrote in message
news:D87DC570-4D90-4068-8E39-76954E367B89@.microsoft.com...
> Hi,
> Can anyone tell me how i change the print range of a RS report.
> My report is fine when viewed, but when i select print preview, it chops
off
> a lot of the report and places it on a separate page.
You must design your report to fit within the page size - margins, otherwise
a single page will print across multiple pages..
> Is there any way of setting the entire report to prin on a single page?
For instance Excel and powerpoint have options which allow you to compress
the item to fit on a page, RS does not have such an option. ( maybe your
printer driver does tho.)
> Also, how do i change the orientation or a report from portrait to
landscape?
Go to Report Properties -> Layout and set the size from 8.5 X 11 to 11 X 8.5
> Thanks in advance.