Output Column Width not refected in the Flat File that is created using a Flat File Destination?

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

This may sound confusing for the case of Flat File destination connection, but the same connection manager object is used for sources and destination. There is also the description of these properties in the property grid of the Flat File Connection Manager UI.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

This may sound confusing for the case of Flat File destination connection, but the same connection manager object is used for sources and destination. There is also the description of these properties in the property grid of the Flat File Connection Manager UI.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

sql

OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

Outloook 2002 & SQL Server 2000

Hi ,
I am running SQL server 2000 sp3a and MS Outlook 2002. I have created the
profile and tested it within mail from the ontrol panel which i can received
the email.
From SQL Server , i assigned the profile "Test" to SQL Mail & SQL Mail Agent
and click on the test which said "Successfully started(stopped) a mail
session with this profile".
Then i went ahead to create the operator and put in a valid email addr and i
click on the "test" which gave me a return message that said "the message wa
s
successfully sent" but somehow i did not recieve that email
How can i fix this ?
appreciate ur advice
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1Hi ,
to add on further i used Query Analyzer and i got the "mail sent" using
xp_sendmail @.recipient = 'eee' , @.subject = 'oooo'
tks & rdgs
maxzsim wrote:
>Hi ,
> I am running SQL server 2000 sp3a and MS Outlook 2002. I have created the
>profile and tested it within mail from the ontrol panel which i can receive
d
>the email.
>From SQL Server , i assigned the profile "Test" to SQL Mail & SQL Mail Agen
t
>and click on the test which said "Successfully started(stopped) a mail
>session with this profile".
>Then i went ahead to create the operator and put in a valid email addr and
i
>click on the "test" which gave me a return message that said "the message w
as
>successfully sent" but somehow i did not recieve that email
>How can i fix this ?
>appreciate ur advice
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
Upon further checking i see that all those mails were "stucked" in the MS
Outlook.
I'll have to manually click on the "send/receive" to sent out those emails .
furthermore , i can only send to those company email addr and not the
hotmail/gmail address.
i have set up the SMTP server but not the POP3 server i do not intend to
receive any incoming email
How can i prevent the emails from being saved to MS Outlook and i don't have
to manually click on the "send/receive" and how can i send to all valid emai
l
addresses ?
appreciate ur advice
tks & rdgs
maxzsim wrote:
>Hi ,
> to add on further i used Query Analyzer and i got the "mail sent" using
>xp_sendmail @.recipient = 'eee' , @.subject = 'oooo'
>tks & rdgs
>
>
>[quoted text clipped - 15 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||I am pretty sure you cannot because that is the latest outlook security
feature to prevent software sending out junk emails. Software could be
downloaded and take over your email service to send out emails without you
knowing it.
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5c2572e6256ac@.uwe...
> Hi ,
> Upon further checking i see that all those mails were "stucked" in the MS
> Outlook.
> I'll have to manually click on the "send/receive" to sent out those emails
> .
> furthermore , i can only send to those company email addr and not the
> hotmail/gmail address.
> i have set up the SMTP server but not the POP3 server i do not intend to
> receive any incoming email
> How can i prevent the emails from being saved to MS Outlook and i don't
> have
> to manually click on the "send/receive" and how can i send to all valid
> email
> addresses ?
> appreciate ur advice
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
I want to configure something with SQL Mail & SQL Agent Mail to auto send
out alert email so what software could help me acheive this ?
tks & rdgs
Grant wrote:
>I am pretty sure you cannot because that is the latest outlook security
>feature to prevent software sending out junk emails. Software could be
>downloaded and take over your email service to send out emails without you
>knowing it.
>
>[quoted text clipped - 29 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi ,
Actually i am as to why i ccan only send to company123@..xxx.sss
but not xxx@.hotmail/gmail.com as i thought if you could send via SMTP then i
t
shld send as long as its a valid email addr ?
i got this error message " 554 Relay rejected for policy reasons."
appreciate ur advice
tks & rdgs
Grant wrote:
>I am pretty sure you cannot because that is the latest outlook security
>feature to prevent software sending out junk emails. Software could be
>downloaded and take over your email service to send out emails without you
>knowing it.
>
>[quoted text clipped - 29 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1

outlook express with SQL server 2000

Hi ,
Can i know if outlook express is able to work with SQL server 2000 ?
i have created a acct but sql mail not able to find that profile
tks & rdgs
Hi
No. Only full Outlook.
http://support.microsoft.com/default...b;en-us;263556
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:

> Hi ,
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
> tks & rdgs
>
|||Just as a follow up to Mikes answer:
http://support.microsoft.com/default...311231&sd=tech
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:6A471B90-25E4-4FAE-80EB-BCE1C997B3FA@.microsoft.com...[vbcol=seagreen]
> Hi
> No. Only full Outlook.
> http://support.microsoft.com/default...b;en-us;263556
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
|||"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7D00E3BD-B7D7-405A-B891-FE823F4BBB93@.microsoft.com...
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
AFAIK you must install and configure Outlook (not Express) to get SQLMail
running.
Regards, Wojtek
sql

outlook express with SQL server 2000

Hi ,
Can i know if outlook express is able to work with SQL server 2000 ?
i have created a acct but sql mail not able to find that profile
tks & rdgsHi
No. Only full Outlook.
http://support.microsoft.com/defaul...kb;en-us;263556
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:

> Hi ,
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
> tks & rdgs
>|||Just as a follow up to Mikes answer:
http://support.microsoft.com/defaul...;311231&sd=tech
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:6A471B90-25E4-4FAE-80EB-BCE1C997B3FA@.microsoft.com...[vbcol=seagreen]
> Hi
> No. Only full Outlook.
> http://support.microsoft.com/defaul...kb;en-us;263556
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
>|||"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7D00E3BD-B7D7-405A-B891-FE823F4BBB93@.microsoft.com...
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
AFAIK you must install and configure Outlook (not Express) to get SQLMail
running.
Regards, Wojtek

outlook express with SQL server 2000

Hi ,
Can i know if outlook express is able to work with SQL server 2000 ?
i have created a acct but sql mail not able to find that profile
tks & rdgsHi
No. Only full Outlook.
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
> tks & rdgs
>|||Just as a follow up to Mikes answer:
http://support.microsoft.com/default.aspx?scid=kb;en-us;311231&sd=tech
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:6A471B90-25E4-4FAE-80EB-BCE1C997B3FA@.microsoft.com...
> Hi
> No. Only full Outlook.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
>> Hi ,
>> Can i know if outlook express is able to work with SQL server 2000 ?
>> i have created a acct but sql mail not able to find that profile
>> tks & rdgs
>>|||"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7D00E3BD-B7D7-405A-B891-FE823F4BBB93@.microsoft.com...
> Can i know if outlook express is able to work with SQL server 2000 ?
> i have created a acct but sql mail not able to find that profile
AFAIK you must install and configure Outlook (not Express) to get SQLMail
running.
Regards, Wojtek

OUTER JOIN issue

The following stored procedure was created by one of our developers and it's
taking too long to be executed, we already identified that the section of th
e
coding with the problem are the outer joins, can anybody suggest something t
o
improve it?
Thanks
-- Populate performance layer table with latest data
----
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 1'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
isnull(isnull(isnull(poobd.charge_cost_centre_did,
unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
sep.charge_cost_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
sday.budget_date_did), sep.budget_date_did) as budget_date_did,
isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
sday.initiative_did), sep.initiative_did) as initiative_did,
isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(poobd.measure_POOBD, 0) as measure_POOBD,
isnull(sday.measure_SDAY, 0) as measure_SDAY,
isnull(sep.measure_SEP, 0) as measure_SEP
INTO #activity_measures_part_1
FROM @.measure_UNKNOWN unknown
FULL OUTER JOIN @.measure_POOBD poobd
ON unknown.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
unknown.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
unknown.cost_centre_did = poobd.cost_centre_did and
unknown.charge_cost_centre_did = poobd.charge_cost_centre_did and
unknown.budget_date_did = poobd.budget_date_did and
unknown.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SDAY sday
ON sday.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sday.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sday.cost_centre_did = poobd.cost_centre_did and
sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
sday.budget_date_did = poobd.budget_date_did and
sday.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SEP sep
ON sep.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sep.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sep.cost_centre_did = poobd.cost_centre_did and
sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
sep.budget_date_did = poobd.budget_date_did and
sep.initiative_did = poobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 2'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stobd.
clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stobd.
clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(soobd.cost_centre_did,
wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
cost_centre_did,
isnull(isnull(isnull(soobd.charge_cost_centre_did,
wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_cos
t_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(soobd.budget_date_did,
wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
budget_date_did,
isnull(isnull(isnull(soobd.initiative_did,
wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
initiative_did,
isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
isnull(stobd.measure_STOBD, 0) as measure_STOBD
INTO #activity_measures_part_2
FROM @.measure_SOOBD soobd
FULL OUTER JOIN @.measure_WSEP9 wsep9
ON wsep9.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
wsep9.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
wsep9.cost_centre_did = soobd.cost_centre_did and
wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
wsep9.budget_date_did = soobd.budget_date_did and
wsep9.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_PTOBD ptobd
ON ptobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
ptobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
ptobd.cost_centre_did = soobd.cost_centre_did and
ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
ptobd.budget_date_did = soobd.budget_date_did and
ptobd.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_STOBD stobd
ON stobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
stobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
stobd.cost_centre_did = soobd.cost_centre_did and
stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
stobd.budget_date_did = soobd.budget_date_did and
stobd.initiative_did = soobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
select count(*) from #activity_measures_part_1
select count(*) from #activity_measures_part_2
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - final'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
(clinical_hierarchy_ward_did,
clinical_hierarchy_unit_did,
cost_centre_did,
charge_cost_centre_did,
budget_date_did,
initiative_did,
measure_UNKNOWN,
measure_POOBD,
measure_SDAY,
measure_SEP,
measure_SOOBD,
measure_WSEP9,
measure_PTOBDs,
measure_STOBDs,
batch_id)
SELECT isnull(part1.clinical_hierarchy_ward_did,
part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(part1.clinical_hierarchy_unit_did,
part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(part1.cost_centre_did, part2.cost_centre_did) as
cost_centre_did,
isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
charge_cost_centre_did,
isnull(part1.budget_date_did, part2.budget_date_did) as
budget_date_did,
isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(part1.measure_POOBD, 0) as measure_POOBD,
isnull(part1.measure_SDAY, 0) as measure_SDAY,
isnull(part1.measure_SEP, 0) as measure_SEP,
isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
isnull(part2.measure_STOBD, 0) as measure_STOBD,
@.BatchID
FROM #activity_measures_part_1 part1
FULL OUTER JOIN #activity_measures_part_2 part2
ON part1.clinical_hierarchy_ward_did =
part2.clinical_hierarchy_ward_did and
part1.clinical_hierarchy_unit_did =
part2.clinical_hierarchy_unit_did and
part1.cost_centre_did = part2.cost_centre_did and
part1.charge_cost_centre_did = part2.charge_cost_centre_did and
part1.budget_date_did = part2.budget_date_did and
part1.initiative_did = part2.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_AbortWithout the table structures, index details and size of tables it is very
difficult to say what is wrong - but it may be worth running it through the
index tuning wizard to see what it comes up with
Have a look at the execution plan and see where the expensive bits are.
May be worth replacing all those nested isnulls with a coalesce of all the
columns in the nested isnulls - makes it much easier to read, less code and
may help.
If no luck try posting the executiuon plan and ddl for all the tables -
someone may then have some more suggestions
Mike John
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:30F9AE02-48DD-4FC9-BD5C-636224FB5CE3@.microsoft.com...
> The following stored procedure was created by one of our developers and
> it's
> taking too long to be executed, we already identified that the section of
> the
> coding with the problem are the outer joins, can anybody suggest something
> to
> improve it?
> Thanks
> -- Populate performance layer table with latest data
> ----
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 1'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
> unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
> sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
> unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
> sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
> sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
> isnull(isnull(isnull(poobd.charge_cost_centre_did,
> unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
> sep.charge_cost_centre_did) as charge_cost_centre_did,
> isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
> sday.budget_date_did), sep.budget_date_did) as budget_date_did,
> isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
> sday.initiative_did), sep.initiative_did) as initiative_did,
> isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(poobd.measure_POOBD, 0) as measure_POOBD,
> isnull(sday.measure_SDAY, 0) as measure_SDAY,
> isnull(sep.measure_SEP, 0) as measure_SEP
> INTO #activity_measures_part_1
> FROM @.measure_UNKNOWN unknown
> FULL OUTER JOIN @.measure_POOBD poobd
> ON unknown.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> unknown.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> unknown.cost_centre_did = poobd.cost_centre_did and
> unknown.charge_cost_centre_did = poobd.charge_cost_centre_did
> and
> unknown.budget_date_did = poobd.budget_date_did and
> unknown.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SDAY sday
> ON sday.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sday.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sday.cost_centre_did = poobd.cost_centre_did and
> sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sday.budget_date_did = poobd.budget_date_did and
> sday.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SEP sep
> ON sep.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sep.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sep.cost_centre_did = poobd.cost_centre_did and
> sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sep.budget_date_did = poobd.budget_date_did and
> sep.initiative_did = poobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 2'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
> wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stob
d.clinical_hierarchy_ward_did)
> as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
> wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stob
d.clinical_hierarchy_unit_did)
> as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(soobd.cost_centre_did,
> wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
> cost_centre_did,
> isnull(isnull(isnull(soobd.charge_cost_centre_did,
> wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_c
ost_centre_did)
> as charge_cost_centre_did,
> isnull(isnull(isnull(soobd.budget_date_did,
> wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
> budget_date_did,
> isnull(isnull(isnull(soobd.initiative_did,
> wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
> initiative_did,
> isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
> isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
> isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
> isnull(stobd.measure_STOBD, 0) as measure_STOBD
> INTO #activity_measures_part_2
> FROM @.measure_SOOBD soobd
> FULL OUTER JOIN @.measure_WSEP9 wsep9
> ON wsep9.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> wsep9.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> wsep9.cost_centre_did = soobd.cost_centre_did and
> wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
> wsep9.budget_date_did = soobd.budget_date_did and
> wsep9.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_PTOBD ptobd
> ON ptobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> ptobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> ptobd.cost_centre_did = soobd.cost_centre_did and
> ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> ptobd.budget_date_did = soobd.budget_date_did and
> ptobd.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_STOBD stobd
> ON stobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> stobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> stobd.cost_centre_did = soobd.cost_centre_did and
> stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> stobd.budget_date_did = soobd.budget_date_did and
> stobd.initiative_did = soobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
>
> select count(*) from #activity_measures_part_1
> select count(*) from #activity_measures_part_2
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - final'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
> (clinical_hierarchy_ward_did,
> clinical_hierarchy_unit_did,
> cost_centre_did,
> charge_cost_centre_did,
> budget_date_did,
> initiative_did,
> measure_UNKNOWN,
> measure_POOBD,
> measure_SDAY,
> measure_SEP,
> measure_SOOBD,
> measure_WSEP9,
> measure_PTOBDs,
> measure_STOBDs,
> batch_id)
> SELECT isnull(part1.clinical_hierarchy_ward_did,
> part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(part1.clinical_hierarchy_unit_did,
> part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(part1.cost_centre_did, part2.cost_centre_did) as
> cost_centre_did,
> isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
> charge_cost_centre_did,
> isnull(part1.budget_date_did, part2.budget_date_did) as
> budget_date_did,
> isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
> isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(part1.measure_POOBD, 0) as measure_POOBD,
> isnull(part1.measure_SDAY, 0) as measure_SDAY,
> isnull(part1.measure_SEP, 0) as measure_SEP,
> isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
> isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
> isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
> isnull(part2.measure_STOBD, 0) as measure_STOBD,
> @.BatchID
> FROM #activity_measures_part_1 part1
> FULL OUTER JOIN #activity_measures_part_2 part2
> ON part1.clinical_hierarchy_ward_did =
> part2.clinical_hierarchy_ward_did and
> part1.clinical_hierarchy_unit_did =
> part2.clinical_hierarchy_unit_did and
> part1.cost_centre_did = part2.cost_centre_did and
> part1.charge_cost_centre_did = part2.charge_cost_centre_did and
> part1.budget_date_did = part2.budget_date_did and
> part1.initiative_did = part2.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort|||Consider using temporary tables instead of table variables. The options to
create indexes on table variables are very limited.
For better help, please post DDL, sample data and expected results.
ML
http://milambda.blogspot.com/|||Only a minor point (not performance related), but
you can use a single "coalesce" instead of multiple
nested "isnull"s. This may make your query easier to
read.

Out of the box reports

Hi
My company is planning to deploy SQL Server 2005 Reporting Services to
monitor usage of a number of sites we've created using Sharepoint.
The marketing blurb says that a number of out-of-the-box reports are
available but doesn't say exactly what these reports are. Can someone
help by giving me the details?
Thanks
Tony
> My company is planning to deploy SQL Server 2005 Reporting Services to
> monitor usage of a number of sites we've created using Sharepoint.
> The marketing blurb says that a number of out-of-the-box reports are
> available but doesn't say exactly what these reports are. Can someone
> help by giving me the details?
>
The reports and the deployment guide are available for free from
Microsoft. Do a seach on microsoft.com about "Report Pack for
Sharepoint" and "Report Pack for Sharepoint Deployment Guide".
Regards,
lucm
sql

Out of the box reports

Hi
My company is planning to deploy SQL Server 2005 Reporting Services to
monitor usage of a number of sites we've created using Sharepoint.
The marketing blurb says that a number of out-of-the-box reports are
available but doesn't say exactly what these reports are. Can someone
help by giving me the details?
Thanks
Tony> My company is planning to deploy SQL Server 2005 Reporting Services to
> monitor usage of a number of sites we've created using Sharepoint.
> The marketing blurb says that a number of out-of-the-box reports are
> available but doesn't say exactly what these reports are. Can someone
> help by giving me the details?
>
The reports and the deployment guide are available for free from
Microsoft. Do a seach on microsoft.com about "Report Pack for
Sharepoint" and "Report Pack for Sharepoint Deployment Guide".
Regards,
lucm

Out of the box reports

Hi
My company is planning to deploy SQL Server 2005 Reporting Services to
monitor usage of a number of sites we've created using Sharepoint.
The marketing blurb says that a number of out-of-the-box reports are
available but doesn't say exactly what these reports are. Can someone
help by giving me the details?
Thanks
Tony> My company is planning to deploy SQL Server 2005 Reporting Services to
> monitor usage of a number of sites we've created using Sharepoint.
> The marketing blurb says that a number of out-of-the-box reports are
> available but doesn't say exactly what these reports are. Can someone
> help by giving me the details?
>
The reports and the deployment guide are available for free from
Microsoft. Do a seach on microsoft.com about "Report Pack for
Sharepoint" and "Report Pack for Sharepoint Deployment Guide".
Regards,
lucm

OSQL restore Database

Hi

I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!

Best Regards
PhilipI'd use a VBS script. Easier then a full application...|||Thanks

OSQL Question

I created a sql script that transfers data from my test system to production adn vice versa. I would like to use OSQL and be able to pass parameters to the SQL script.

Example: param 1 = test; param 2 = production.

Can anyone tell me if this is possible and how to do it?

Thanks in advance for all the help.You can't directly pass parameters as part of the OSQL command-line utility, but you have several alternatives for solving your problem.

As stated in Winnet mag article :
OSQL won't let you pass in parameters, but you can use the sp_executesql stored procedure, which can process parameterized SQL. (For detailed information about sp_executesql, see SQL Server Books OnlineBOL.) The following example lets you pass parameters into a T-SQL command but doesn't completely address how to pass a parameter as part of an OSQL command-line session (remember that you must issue the OSQL command from a command prompt):

osql -E -MyServer -Q "EXECUTE sp_executesql N'SELECT *
FROM northwind..
orders WHERE OrderId = @.OrderId' ,N'@.OrderId int' ,@.OrderId = 10248"
Another solution relies on the power of Windows to handle the parameterization for you. For DBAs who aren't familiar with Windows-level command-file processing, a batch file is a text file that has a .bat extension. Windows treats batch files as executables that run in the Command Prompt environment. You can think of batch files as mini programs that Windows runs.

You can simply create a file called SQLVariableBatch.bat, and put the following text in it:

osql -E -MyServer -Q "SELECT *
FROM northwind..orders WHERE OrderId = %1"
From the directory where you saved the .bat file, issue the following command from a command prompt window:

SQLVariableBatch 10248
When running this command, Windows will replace the %1 in the SQLVariableBatch.bat file with what comes after the batch file's name in the command linein this case, 10248. This is a simple example of batch processing in Windows; to learn more about batch files, see the Windows Help files.

You can also use Windows Scripting Host (WSH) to manage the parameterization. Using WSH for scripting and batch processing is much more flexible and powerful than using simple Windows batch files. I'm not a WSH expert, so I don't include an example of this solution, but I wanted to note that the option exists. For information about using WSH, see the MSDN Web site for Windows Script at

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001169 .|||Thanks for your help. I will try your suggestions and see if I can figure it out.

osql

i'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>osql -S (local)\\SQLEXPRESS -U sa -P sablah -i run.sql.......

this is the error i'm getting in my logs

Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

First, you only use EITHER username/password OR Trusted_Connection -NOT both. So the second connection string should fail.

Second, in the connection string, the server 'should' have only one [\] between the machinename and the instancename.

|||Yah, i believe i posted that. for asp.net the string actually works. i know why its not supposed to be there. and thats obivously why i can't add that argument to the osql statement. but i'm still left with the original posting of my error. i'm not missing anything in my osql statement?|||

Reading your post again, it seems that you indicated that your ASP connection string failed.

is98 wrote:

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

ASP may be able to handle the two backslashes in the servername, but OSQL cannot. And if I recall, doesn't ASP use (localhost) instead of (local)?

Try: "server=(local)\SQLExpress" INSTEAD OF "Server=(local)\\SQLEXPRESS"

|||who the man? you the man. the localhost part works still ok. i just took out the extra slash which obiviously now explains why it couldn't find the server. thanks muchly.|||

The double back-slash is an escape character for C based languages, such as C#. When ever you want to pass a backslash in a C based language, you either need to use the "\\" escape sequence or use the @. to indicate a litteral string. You are passing literal strings to oSQL, not C#, so the "\\" is not needed, as you've found.

Just thought you'd be interested in understanding that this wasn't just random behaivor.

Mike