Hi,
I have a SQL server running in our production environment. I would like to
create a test/reporting environment running on a seperate server. I will
take my DB backups from the production server and then restore it onto my
2nd server. What is the best way to get the 2nd system updated? I do not
want a live replication all day long. I simply need a method to update the
2nd server once a day at a scheduled time.
Ideas?
Thank you.You can use the log shipping model. This means that you can restore the logs
from your production server on the reporting server.
Here are some details you can actually see how it could be implemented:
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Let me know if it helps..
"Dragon" wrote:
> Hi,
> I have a SQL server running in our production environment. I would like to
> create a test/reporting environment running on a seperate server. I will
> take my DB backups from the production server and then restore it onto my
> 2nd server. What is the best way to get the 2nd system updated? I do not
> want a live replication all day long. I simply need a method to update the
> 2nd server once a day at a scheduled time.
> Ideas?
> Thank you.
>
>|||The log shipping model will leave your second server in a read only
state, rendering it useless for testing.|||The link I posted states pros and cons of this model. It all depends of what
type of testing.
"mryan" wrote:
> The log shipping model will leave your second server in a read only
> state, rendering it useless for testing.
>|||Thank you all for your replies.
Unfortunately I will need a totally indipendent system for my testing.
Having read only will not work as we will not be able to test applications
against it. Any other ideas? What about incremental backups and restores?
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:5B49860B-A765-49CE-9719-EE982B37ED00@.microsoft.com...
> The link I posted states pros and cons of this model. It all depends of
> what
> type of testing.
> "mryan" wrote:
>> The log shipping model will leave your second server in a read only
>> state, rendering it useless for testing.
>>
Saturday, February 25, 2012
Reporting Server - 2nd SQL Server
Hi,
I have a SQL server running in our production environment. I would like to
create a test/reporting environment running on a seperate server. I will
take my DB backups from the production server and then restore it onto my
2nd server. What is the best way to get the 2nd system updated? I do not
want a live replication all day long. I simply need a method to update the
2nd server once a day at a scheduled time.
Ideas?
Thank you.
You can use the log shipping model. This means that you can restore the logs
from your production server on the reporting server.
Here are some details you can actually see how it could be implemented:
http://www.sql-server-performance.co...g_shipping.asp
Let me know if it helps..
"Dragon" wrote:
> Hi,
> I have a SQL server running in our production environment. I would like to
> create a test/reporting environment running on a seperate server. I will
> take my DB backups from the production server and then restore it onto my
> 2nd server. What is the best way to get the 2nd system updated? I do not
> want a live replication all day long. I simply need a method to update the
> 2nd server once a day at a scheduled time.
> Ideas?
> Thank you.
>
>
|||The log shipping model will leave your second server in a read only
state, rendering it useless for testing.
|||The link I posted states pros and cons of this model. It all depends of what
type of testing.
"mryan" wrote:
> The log shipping model will leave your second server in a read only
> state, rendering it useless for testing.
>
|||Thank you all for your replies.
Unfortunately I will need a totally indipendent system for my testing.
Having read only will not work as we will not be able to test applications
against it. Any other ideas? What about incremental backups and restores?
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:5B49860B-A765-49CE-9719-EE982B37ED00@.microsoft.com...[vbcol=seagreen]
> The link I posted states pros and cons of this model. It all depends of
> what
> type of testing.
> "mryan" wrote:
I have a SQL server running in our production environment. I would like to
create a test/reporting environment running on a seperate server. I will
take my DB backups from the production server and then restore it onto my
2nd server. What is the best way to get the 2nd system updated? I do not
want a live replication all day long. I simply need a method to update the
2nd server once a day at a scheduled time.
Ideas?
Thank you.
You can use the log shipping model. This means that you can restore the logs
from your production server on the reporting server.
Here are some details you can actually see how it could be implemented:
http://www.sql-server-performance.co...g_shipping.asp
Let me know if it helps..
"Dragon" wrote:
> Hi,
> I have a SQL server running in our production environment. I would like to
> create a test/reporting environment running on a seperate server. I will
> take my DB backups from the production server and then restore it onto my
> 2nd server. What is the best way to get the 2nd system updated? I do not
> want a live replication all day long. I simply need a method to update the
> 2nd server once a day at a scheduled time.
> Ideas?
> Thank you.
>
>
|||The log shipping model will leave your second server in a read only
state, rendering it useless for testing.
|||The link I posted states pros and cons of this model. It all depends of what
type of testing.
"mryan" wrote:
> The log shipping model will leave your second server in a read only
> state, rendering it useless for testing.
>
|||Thank you all for your replies.
Unfortunately I will need a totally indipendent system for my testing.
Having read only will not work as we will not be able to test applications
against it. Any other ideas? What about incremental backups and restores?
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:5B49860B-A765-49CE-9719-EE982B37ED00@.microsoft.com...[vbcol=seagreen]
> The link I posted states pros and cons of this model. It all depends of
> what
> type of testing.
> "mryan" wrote:
Reporting Servcies Sessions
Hey
I have a problem in controlling the report session.
I created a Reporting Model using SQL reporting Services 2005 with Forms authentication on which I implemented the security Filter based on the function GetUserID() to report only against data that belong to the login User, and I deployed the Model.
Using report Builder application I created a report that contain in the first column the user name (UserName) from entity “Users”, and in the remaining columns data from other entities related to table users, I saved the report on the report server.
I logged in into report manager with user “User1” and I ran the report, the result was the same as expected (in the first column “User1” appeared and in the remaining column other data related to this user appeared) every thing went good
After that I logged in with the user “User2” and I ran the report and here was the surprise the same data that appeared for "User1" appeared for "User2". but what was Expected is different data that belong to "User 2"
After some research that says that this problem may be caused by a session issue (the session created for the first user who enters the report server will remain for the other users that enter after him), I reset IIS and I logged in with “User2”, and I ran the report the correct data for “User2” appeared So it may be a session issue.
My question is:
Is there any way to control the session content and the session expiration?
Please note I used all the solution provided in my research such as URL parameter rs:ClearSession = true, and the Report Execution Options in report manager but non of this helped me.
I will be thankful for any one who could help me in this urgent issue.
Thank you
BOB
Hi Bob,
I have the same issue here. Did you manage to get a solution for your problem?
Julian Kooiker
Reporting Servcies Sessions
Hey
I have a problem in controlling the report session.
I created a Reporting Model using SQL reporting Services 2005 with Forms authentication on which I implemented the security Filter based on the function GetUserID() to report only against data that belong to the login User, and I deployed the Model.
Using report Builder application I created a report that contain in the first column the user name (UserName) from entity “Users”, and in the remaining columns data from other entities related to table users, I saved the report on the report server.
I logged in into report manager with user “User1” and I ran the report, the result was the same as expected (in the first column “User1” appeared and in the remaining column other data related to this user appeared) every thing went good
After that I logged in with the user “User2” and I ran the report and here was the surprise the same data that appeared for "User1" appeared for "User2". but what was Expected is different data that belong to "User 2"
After some research that says that this problem may be caused by a session issue (the session created for the first user who enters the report server will remain for the other users that enter after him), I reset IIS and I logged in with “User2”, and I ran the report the correct data for “User2” appeared So it may be a session issue.
My question is:
Is there any way to control the session content and the session expiration?
Please note I used all the solution provided in my research such as URL parameter rs:ClearSession = true, and the Report Execution Options in report manager but non of this helped me.
I will be thankful for any one who could help me in this urgent issue.
Thank you
BOB
Hi Bob,
I have the same issue here. Did you manage to get a solution for your problem?
Julian Kooiker
Reporting Servcies Configuration
Hi,
I am new to sql server 2005 and reporting servics.
I have installed sql server 2005 with reporting services on windows vita.
After that when i go to Reporting Servcies Configuration i get follwoing error,
A WMI error has occurred and no additional error information is available.
details,
ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. > System.Runtime.InteropServices.COMException (0x8000000A)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)
at ReportServicesConfigUI.WMIProvider.RSInstance.RefreshServerStatus() ..........
Any one has any idea?
Regards,
SSRS only will work on Vista Ultimate? If you have Ultimate let me know and I can tell you how to do it. It is still a lot compatibility issues but their is a work around if you want to get it installed.
Reporting Records in groups of 10
I have a SQL 2000 table with the following fields:
Order_id
cust_pn
Seq_nbr
chassis_nbr
Qty
The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
left-hand part.
For a given order_id, I need to report the part numbers in groups of 10
lefts, 10 rights, 10 lefts and so one.
Does anyone know of a way to do this? Thanks!Data types, sample data, desired results?
http://www.aspfaq.com/5006
"prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>I have a SQL 2000 table with the following fields:
> Order_id
> cust_pn
> Seq_nbr
> chassis_nbr
> Qty
> The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
> left-hand part.
> For a given order_id, I need to report the part numbers in groups of 10
> lefts, 10 rights, 10 lefts and so one.
> Does anyone know of a way to do this? Thanks!
>|||I hope this is ok:
CREATE TABLE [dbo].[tblOrders] (
[ORDER_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINE_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITEM_ID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUST_PN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY] [decimal](20, 0) NULL ,
[CHASSIS_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LINE_SEQ_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BUILD_STATION] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_SCHEDULED] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CUSTOMER_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_SHIP_REQD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'164882','P1410C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174984','P1416C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'175627','P1424C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174601','P1439C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'172491','P1407C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'173813','P1408C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'175620','P1411C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'169974','P1413C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'161421','P1415C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'166073','P1417C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
5','R25-1096-217111','DR
ASSY LH DL ELE
STDSTP','R25-1096-217111',1,'163603','P1437C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
4','R25-1096-216221','DR
ASSY LH DL ELE RSTSTP
BLCK','R25-1096-216221',1,'996384','P1421C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
3','R25-1096-216121','DR
ASSY LH DL ELE STDSTP
BLCK','R25-1096-216121',1,'992741','P1447C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
2','R25-1096-216111','DR
ASSY LH DL ELE
STDSTP','R25-1096-216111',1,'169264','P1444C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'996487','P1402C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'170399','P1406C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
0','R25-1096-113211','DR
ASSY LH DL MAN
RSTSTP','R25-1096-113211',1,'165294','P1445C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
9','R25-1096-113121','DR
ASSY LH DL MAN STDSTP
BLOCK','R25-1096-113121',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
8','R25-1096-113111','DR
ASSY LH DL MAN
STDSTP','R25-1096-113111',1,'167500','P1422C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
3','D5301-1025','DAYLITE
DR ASSY,ELE LH
W/BLOCK','R25-1077-2121211',1,'992577','P1445C1','82','04/13/2006','46889','
KENWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
2','D5301-1003','DAYLITE
DR ASSY, MANUAL
W/90','R25-1077-1123211',1,'995181','P1426C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
I need to be able to report line_seq_nbr, cust_pn, chassis_nbr in groups of
10 lefts, 10 rights and so on based on the last character of the cust_pn (se
e
below) for a selected order_id and order then by line_seq_nbr
Thanks for your help!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Data types, sample data, desired results?
> http://www.aspfaq.com/5006
>
> "prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
> news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>
>|||What is the key for the table ?
Anith|||There isn't one for this table.
"Anith Sen" wrote:
> What is the key for the table ?
> --
> Anith
>
>|||I'm not entirely clear on your requirements,
however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
this should give you what you want
SELECT ORDER_ID,
LINE_SEQ_NBR,
CUST_PN,
CHASSIS_NBR
FROM (
SELECT t1.ORDER_ID,
CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
LR,
t1.LINE_SEQ_NBR,
t1.CUST_PN,
t1.CHASSIS_NBR,
(SELECT COUNT(*)/10
FROM tblOrders t2
WHERE t2.ORDER_ID=t1.ORDER_ID
AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
(RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
FROM dbo.tblOrders t1
) X
ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR|||WOW! That's exactly what I wanted!! Works perfectly!! Thanks!!!!!!
"markc600@.hotmail.com" wrote:
> I'm not entirely clear on your requirements,
> however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
> this should give you what you want
>
> SELECT ORDER_ID,
> LINE_SEQ_NBR,
> CUST_PN,
> CHASSIS_NBR
> FROM (
> SELECT t1.ORDER_ID,
> CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
> LR,
> t1.LINE_SEQ_NBR,
> t1.CUST_PN,
> t1.CHASSIS_NBR,
> (SELECT COUNT(*)/10
> FROM tblOrders t2
> WHERE t2.ORDER_ID=t1.ORDER_ID
> AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
> (RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
> AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
> FROM dbo.tblOrders t1
> ) X
> ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR
>
Order_id
cust_pn
Seq_nbr
chassis_nbr
Qty
The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
left-hand part.
For a given order_id, I need to report the part numbers in groups of 10
lefts, 10 rights, 10 lefts and so one.
Does anyone know of a way to do this? Thanks!Data types, sample data, desired results?
http://www.aspfaq.com/5006
"prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>I have a SQL 2000 table with the following fields:
> Order_id
> cust_pn
> Seq_nbr
> chassis_nbr
> Qty
> The cust_pn ends with an "R" if it's a right-hand part otherwise it's a
> left-hand part.
> For a given order_id, I need to report the part numbers in groups of 10
> lefts, 10 rights, 10 lefts and so one.
> Does anyone know of a way to do this? Thanks!
>|||I hope this is ok:
CREATE TABLE [dbo].[tblOrders] (
[ORDER_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINE_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITEM_ID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUST_PN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY] [decimal](20, 0) NULL ,
[CHASSIS_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LINE_SEQ_NBR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BUILD_STATION] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_SCHEDULED] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CUSTOMER_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_SHIP_REQD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'164882','P1410C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174984','P1416C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'175627','P1424C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
2','R25-1096-238221R','DR
ASSY RH DL ELE FRES RS
BLCK','R25-1096-238221R',1,'174601','P1439C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'172491','P1407C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'173813','P1408C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'175620','P1411C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'169974','P1413C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'161421','P1415C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'002
0','R25-1096-228221R','DR
ASSY RH DL ELE PPR RS
BLCK','R25-1096-228221R',1,'166073','P1417C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
5','R25-1096-217111','DR
ASSY LH DL ELE
STDSTP','R25-1096-217111',1,'163603','P1437C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
4','R25-1096-216221','DR
ASSY LH DL ELE RSTSTP
BLCK','R25-1096-216221',1,'996384','P1421C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
3','R25-1096-216121','DR
ASSY LH DL ELE STDSTP
BLCK','R25-1096-216121',1,'992741','P1447C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
2','R25-1096-216111','DR
ASSY LH DL ELE
STDSTP','R25-1096-216111',1,'169264','P1444C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'996487','P1402C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
1','R25-1096-113221','DR
ASSY LH DL MAN RSTSTP
BLCK','R25-1096-113221',1,'170399','P1406C0','82','04/13/2006','46889','KENW
ORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'001
0','R25-1096-113211','DR
ASSY LH DL MAN
RSTSTP','R25-1096-113211',1,'165294','P1445C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
9','R25-1096-113121','DR
ASSY LH DL MAN STDSTP
BLOCK','R25-1096-113121',1,'158190','P1435C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
8','R25-1096-113111','DR
ASSY LH DL MAN
STDSTP','R25-1096-113111',1,'167500','P1422C0','82','04/13/2006','46889','KE
NWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
3','D5301-1025','DAYLITE
DR ASSY,ELE LH
W/BLOCK','R25-1077-2121211',1,'992577','P1445C1','82','04/13/2006','46889','
KENWORTH-RENTON (DOCK Z)','04/14/2006')
INSERT INTO [tblorders]
([ORDER_ID],[LINE_NBR],[ITEM_ID],[DESCRI
PTION],[CUST_PN],[QUANTITY],[CHASSIS_NBR
],[LINE_SEQ_NBR],[BUILD_STAT
ION],[DATE_SCHEDULED],[CUSTOMER_ID],[COM
PANY],[DATE_SHIP_REQD])VALUES('06RX0413'
,'000
2','D5301-1003','DAYLITE
DR ASSY, MANUAL
W/90','R25-1077-1123211',1,'995181','P1426C0','82','04/13/2006','46889','KEN
WORTH-RENTON (DOCK Z)','04/14/2006')
I need to be able to report line_seq_nbr, cust_pn, chassis_nbr in groups of
10 lefts, 10 rights and so on based on the last character of the cust_pn (se
e
below) for a selected order_id and order then by line_seq_nbr
Thanks for your help!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Data types, sample data, desired results?
> http://www.aspfaq.com/5006
>
> "prenfrow" <prenfrow@.discussions.microsoft.com> wrote in message
> news:C7E29EAF-2CE2-44F3-A282-78149A5A949D@.microsoft.com...
>
>|||What is the key for the table ?
Anith|||There isn't one for this table.
"Anith Sen" wrote:
> What is the key for the table ?
> --
> Anith
>
>|||I'm not entirely clear on your requirements,
however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
this should give you what you want
SELECT ORDER_ID,
LINE_SEQ_NBR,
CUST_PN,
CHASSIS_NBR
FROM (
SELECT t1.ORDER_ID,
CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
LR,
t1.LINE_SEQ_NBR,
t1.CUST_PN,
t1.CHASSIS_NBR,
(SELECT COUNT(*)/10
FROM tblOrders t2
WHERE t2.ORDER_ID=t1.ORDER_ID
AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
(RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
FROM dbo.tblOrders t1
) X
ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR|||WOW! That's exactly what I wanted!! Works perfectly!! Thanks!!!!!!
"markc600@.hotmail.com" wrote:
> I'm not entirely clear on your requirements,
> however, assuming that ORDER_ID,LINE_SEQ_NBR is unique,
> this should give you what you want
>
> SELECT ORDER_ID,
> LINE_SEQ_NBR,
> CUST_PN,
> CHASSIS_NBR
> FROM (
> SELECT t1.ORDER_ID,
> CASE WHEN RIGHT(RTRIM(t1.CUST_PN),1)='R' THEN 1 ELSE 0 END AS
> LR,
> t1.LINE_SEQ_NBR,
> t1.CUST_PN,
> t1.CHASSIS_NBR,
> (SELECT COUNT(*)/10
> FROM tblOrders t2
> WHERE t2.ORDER_ID=t1.ORDER_ID
> AND ((RIGHT(RTRIM(t2.CUST_PN),1)='R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)='R') OR
> (RIGHT(RTRIM(t2.CUST_PN),1)<>'R' AND
> RIGHT(RTRIM(t1.CUST_PN),1)<>'R'))
> AND t2.LINE_SEQ_NBR < t1.LINE_SEQ_NBR) As Num
> FROM dbo.tblOrders t1
> ) X
> ORDER BY ORDER_ID,Num,LR,LINE_SEQ_NBR
>
Reporting PWA HTML data?
We are using the SQL Reporting services to report on PWA workspaces.
(Project Server 2007)
A number of workspaces have data items stored as multi-line Rich Text
- which becomes embedded HTML when stored to the database.
The problem is that when it is included in any SQLRS reports it looks
*awful* because the html tags are just embedded within the text,
rather than formatted.
I've seen a couple of posts on SQLRS which basically say 'tough luck
until SQL2008' which isnt very encouraging :(
Is this really true - is there really no way to format html within
SQLRS reports?
Seems like a pretty critical ommission.
Crystal reports can do this stuff standing on its head :)
Any ideas on how to format HTML text within a SQLRS report? (built in
functions, add-ins etc)
Thanks
Al BlakeAl
Certainly this post would be better placed on the dev group, but you might
even consider asking the question in the appropriate SQL group.
--
Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com
"scubaal" <al@.blakes.net> wrote in message
news:e0323a2a-37d7-4a85-b1c6-59d7ed10d65d@.s13g2000prd.googlegroups.com...
> We are using the SQL Reporting services to report on PWA workspaces.
> (Project Server 2007)
> A number of workspaces have data items stored as multi-line Rich Text
> - which becomes embedded HTML when stored to the database.
> The problem is that when it is included in any SQLRS reports it looks
> *awful* because the html tags are just embedded within the text,
> rather than formatted.
> I've seen a couple of posts on SQLRS which basically say 'tough luck
> until SQL2008' which isnt very encouraging :(
> Is this really true - is there really no way to format html within
> SQLRS reports?
> Seems like a pretty critical ommission.
> Crystal reports can do this stuff standing on its head :)
> Any ideas on how to format HTML text within a SQLRS report? (built in
> functions, add-ins etc)
> Thanks
> Al Blake|||2008 will definitely have support for rich text. Not sure exactly what that
means though. For instance, does it mean you can have html from the database
and show that in a field? I don't know, but I sure hope to. I'm going to
investigate some out of my own curiosity on this.
But, for today, the only option is to strip HTML tags and convert the html
to straight text. Not sure if that will do it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"scubaal" <al@.blakes.net> wrote in message
news:e0323a2a-37d7-4a85-b1c6-59d7ed10d65d@.s13g2000prd.googlegroups.com...
> We are using the SQL Reporting services to report on PWA workspaces.
> (Project Server 2007)
> A number of workspaces have data items stored as multi-line Rich Text
> - which becomes embedded HTML when stored to the database.
> The problem is that when it is included in any SQLRS reports it looks
> *awful* because the html tags are just embedded within the text,
> rather than formatted.
> I've seen a couple of posts on SQLRS which basically say 'tough luck
> until SQL2008' which isnt very encouraging :(
> Is this really true - is there really no way to format html within
> SQLRS reports?
> Seems like a pretty critical ommission.
> Crystal reports can do this stuff standing on its head :)
> Any ideas on how to format HTML text within a SQLRS report? (built in
> functions, add-ins etc)
> Thanks
> Al Blake
(Project Server 2007)
A number of workspaces have data items stored as multi-line Rich Text
- which becomes embedded HTML when stored to the database.
The problem is that when it is included in any SQLRS reports it looks
*awful* because the html tags are just embedded within the text,
rather than formatted.
I've seen a couple of posts on SQLRS which basically say 'tough luck
until SQL2008' which isnt very encouraging :(
Is this really true - is there really no way to format html within
SQLRS reports?
Seems like a pretty critical ommission.
Crystal reports can do this stuff standing on its head :)
Any ideas on how to format HTML text within a SQLRS report? (built in
functions, add-ins etc)
Thanks
Al BlakeAl
Certainly this post would be better placed on the dev group, but you might
even consider asking the question in the appropriate SQL group.
--
Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com
"scubaal" <al@.blakes.net> wrote in message
news:e0323a2a-37d7-4a85-b1c6-59d7ed10d65d@.s13g2000prd.googlegroups.com...
> We are using the SQL Reporting services to report on PWA workspaces.
> (Project Server 2007)
> A number of workspaces have data items stored as multi-line Rich Text
> - which becomes embedded HTML when stored to the database.
> The problem is that when it is included in any SQLRS reports it looks
> *awful* because the html tags are just embedded within the text,
> rather than formatted.
> I've seen a couple of posts on SQLRS which basically say 'tough luck
> until SQL2008' which isnt very encouraging :(
> Is this really true - is there really no way to format html within
> SQLRS reports?
> Seems like a pretty critical ommission.
> Crystal reports can do this stuff standing on its head :)
> Any ideas on how to format HTML text within a SQLRS report? (built in
> functions, add-ins etc)
> Thanks
> Al Blake|||2008 will definitely have support for rich text. Not sure exactly what that
means though. For instance, does it mean you can have html from the database
and show that in a field? I don't know, but I sure hope to. I'm going to
investigate some out of my own curiosity on this.
But, for today, the only option is to strip HTML tags and convert the html
to straight text. Not sure if that will do it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"scubaal" <al@.blakes.net> wrote in message
news:e0323a2a-37d7-4a85-b1c6-59d7ed10d65d@.s13g2000prd.googlegroups.com...
> We are using the SQL Reporting services to report on PWA workspaces.
> (Project Server 2007)
> A number of workspaces have data items stored as multi-line Rich Text
> - which becomes embedded HTML when stored to the database.
> The problem is that when it is included in any SQLRS reports it looks
> *awful* because the html tags are just embedded within the text,
> rather than formatted.
> I've seen a couple of posts on SQLRS which basically say 'tough luck
> until SQL2008' which isnt very encouraging :(
> Is this really true - is there really no way to format html within
> SQLRS reports?
> Seems like a pretty critical ommission.
> Crystal reports can do this stuff standing on its head :)
> Any ideas on how to format HTML text within a SQLRS report? (built in
> functions, add-ins etc)
> Thanks
> Al Blake
Subscribe to:
Comments (Atom)