-E switch to start SQL Server?

Hi,
What is the -E switch in SQL Server?
(not -e but -E)
I have read this:
SQL Server can allocate 4 extents instead of 1 extent when the -E switch is
specified during startup. Hence, the -E switch enables SQL Server to issue
256 KB I/O even if extent fragmentation exists because of proportional fill.
Does this switch can improve the performance of a data warehouse database?
thanks.
Jerome.
Hey J,
Have you tried this switch? and is it making a difference?
Myles
|||not tested for the moment.
I plan a test this week maybe.
But I want to know your feedback in case of anyone has a bad experience with
it ;)
<Myles.Matheson@.gmail.com> wrote in message
news:1120549219.051767.175210@.z14g2000cwz.googlegr oups.com...
> Hey J,
> Have you tried this switch? and is it making a difference?
> Myles
>

E switch to start SQL Server?

Hi,
What is the -E switch in SQL Server?
(not -e but -E)
I have read this:
SQL Server can allocate 4 extents instead of 1 extent when the -E switch is
specified during startup. Hence, the -E switch enables SQL Server to issue
256 KB I/O even if extent fragmentation exists because of proportional fill.
Does this switch can improve the performance of a data warehouse database?
thanks.
Jerome.Hey J,
Have you tried this switch? and is it making a difference?
Myles|||not tested for the moment.
I plan a test this week maybe.
But I want to know your feedback in case of anyone has a bad experience with
it ;)
<Myles.Matheson@.gmail.com> wrote in message
news:1120549219.051767.175210@.z14g2000cwz.googlegroups.com...
> Hey J,
> Have you tried this switch? and is it making a difference?
> Myles
>sql

Dynamicaly switching between 2 datasources for a report in order to switch between the pro

I have 2 datasources, one for the production and one for the development
database and I use 'SetReportDataSources' to switch between the 2
datasources. Unfortunately 'SetReportDataSources' permanently switches the
datasource for the report on the Report Server. So, if before the 'Render
gets called another process switches the datasource for that report, the 1st
one would run with the wrong datasource.
Anyone know any way to do this without having to write a custom 'Data
Processing Extension'?
Thanks,
TirdadHello MSFT,
I was wondering if someone from Reporting Services or SQL Server would
address my question?
Sincerely,
Tirdad Lohrasb.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> I have 2 datasources, one for the production and one for the development
> database and I use 'SetReportDataSources' to switch between the 2
> datasources. Unfortunately 'SetReportDataSources' permanently switches the
> datasource for the report on the Report Server. So, if before the 'Render
> gets called another process switches the datasource for that report, the
1st
> one would run with the wrong datasource.
> Anyone know any way to do this without having to write a custom 'Data
> Processing Extension'?
> Thanks,
> Tirdad
>|||The answer is no, dynamic connection strings are not supported. Several
workarounds have previously been proposed in the newsgroup.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
> Hello MSFT,
> I was wondering if someone from Reporting Services or SQL Server would
> address my question?
> Sincerely,
> Tirdad Lohrasb.
>
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > I have 2 datasources, one for the production and one for the development
> > database and I use 'SetReportDataSources' to switch between the 2
> > datasources. Unfortunately 'SetReportDataSources' permanently switches
the
> > datasource for the report on the Report Server. So, if before the
'Render
> > gets called another process switches the datasource for that report, the
> 1st
> > one would run with the wrong datasource.
> > Anyone know any way to do this without having to write a custom 'Data
> > Processing Extension'?
> >
> > Thanks,
> > Tirdad
> >
> >
>|||Thank you with the response back. Now, I have 2 questions, 1. Is there any
plan to support dynamic connections in the future.
2. Can you tell me what the workarounds are? The only one I can think of is
writing custom Data Processing Extension, Is this
the around?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:O22HW9BWEHA.2716@.tk2msftngp13.phx.gbl...
> The answer is no, dynamic connection strings are not supported. Several
> workarounds have previously been proposed in the newsgroup.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
> > Hello MSFT,
> >
> > I was wondering if someone from Reporting Services or SQL Server would
> > address my question?
> >
> > Sincerely,
> > Tirdad Lohrasb.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the
development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report,
the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>|||1. Yes, dynamic connection strings will be included in the SQL 2005 version.
2. You can write a stored procedure that uses other databases on the same
box, use OPENROWSET, or use linked servers
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:%23U2wbvGWEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Thank you with the response back. Now, I have 2 questions, 1. Is there any
> plan to support dynamic connections in the future.
> 2. Can you tell me what the workarounds are? The only one I can think of
> is
> writing custom Data Processing Extension, Is this
> the around?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:O22HW9BWEHA.2716@.tk2msftngp13.phx.gbl...
>> The answer is no, dynamic connection strings are not supported. Several
>> workarounds have previously been proposed in the newsgroup.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
>> news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
>> > Hello MSFT,
>> >
>> > I was wondering if someone from Reporting Services or SQL Server would
>> > address my question?
>> >
>> > Sincerely,
>> > Tirdad Lohrasb.
>> >
>> >
>> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
>> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
>> > > I have 2 datasources, one for the production and one for the
> development
>> > > database and I use 'SetReportDataSources' to switch between the 2
>> > > datasources. Unfortunately 'SetReportDataSources' permanently
>> > > switches
>> the
>> > > datasource for the report on the Report Server. So, if before the
>> 'Render
>> > > gets called another process switches the datasource for that report,
> the
>> > 1st
>> > > one would run with the wrong datasource.
>> > > Anyone know any way to do this without having to write a custom
>> > > 'Data
>> > > Processing Extension'?
>> > >
>> > > Thanks,
>> > > Tirdad
>> > >
>> > >
>> >
>> >
>>
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> why you don't change the datasource definition after your deployment?
> you can create the report in visual studio using a specific server, and,
> after the first deployment, change the server on report server. Your
> published reports will used the production server, and the development in
> visual studio used the dev. server.
>
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > I have 2 datasources, one for the production and one for the development
> > database and I use 'SetReportDataSources' to switch between the 2
> > datasources. Unfortunately 'SetReportDataSources' permanently switches
the
> > datasource for the report on the Report Server. So, if before the
'Render
> > gets called another process switches the datasource for that report, the
> 1st
> > one would run with the wrong datasource.
> > Anyone know any way to do this without having to write a custom 'Data
> > Processing Extension'?
> >
> > Thanks,
> > Tirdad
> >
> >
>|||Hi Toby,
when I try to download your sample I get the following popup:
"Operation could not be completed. Some resource(s) may have been removed."
I have installed the GOTDOTNET workspaces.
Regards,
Peter.
"Toby" wrote:
> I have released a DPE that achiveves this. It can be downloaded at
> http://workspaces.gotdotnet.com/appworld
> Regards
> Toby
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> > why you don't change the datasource definition after your deployment?
> > you can create the report in visual studio using a specific server, and,
> > after the first deployment, change the server on report server. Your
> > published reports will used the production server, and the development in
> > visual studio used the dev. server.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report, the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>
>|||I go to the URL, but I don't see anything to download
"Toby" wrote:
> I have released a DPE that achiveves this. It can be downloaded at
> http://workspaces.gotdotnet.com/appworld
> Regards
> Toby
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> > why you don't change the datasource definition after your deployment?
> > you can create the report in visual studio using a specific server, and,
> > after the first deployment, change the server on report server. Your
> > published reports will used the production server, and the development in
> > visual studio used the dev. server.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report, the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>
>

Dynamically selected columns with column switch option

Hi,
my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. This is easier
explained with the following statements one can paste into Query
Analyzer:
declare @.sql nvarchar(4000), @.cols nvarchar (500)
set @.cols = '
v1 = case when sel=1 then
v1a else v1b end,
v2 = case when sel=1 then v2a
else v2b end
'
create table ##temp_test (
v1a int,
v1b int,
v2a varchar(15),
v2b varchar(15),
sel bit
)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (1, 2, 'a1', 'b1', 0)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (3, 4, 'a2', 'b2', 1)
set @.sql = 'select ' + @.cols + 'from
##temp_test'
exec sp_executesql @.sql
drop table ##temp_test
Result:
v1 v2
-- --
2 b1
3 a2
This fits bots requirements: @.cols (which is stored in a table in the
real world application) holds the column names to be selected, and it
holds it in a way which also enables to switch between the a and b
version of the columns via the CASE statements. The performance is OK.
My problem: the real world query is a lot more complex, ##temp_test is
actually a table with about 80 columns and there are a lot of other
tables joined in.
Since I am doomed to not use more than 4000 characters for the dynamic
sql part (not using sp_executesql results in a huge performance
penalty in my scenario) this approach in the end works find without
all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
@.cols variable length will grow by factor 2-3, typically from around
1000 - 2000 chars to about 2000 - 6000 chars.
That's the end of the sp_executesql approach.
Maybe our design is wrong in the first place. We have tried to
alternate between the columns by using UNION to a View that contains
the alternate column, but ended up in quiet a performance hit with
more complex queries plus sometimes the SORT function would not work
anymore and the like.
Maybe I can shorten the
v1 = case when sel=1 then
v1a else v1b end
part somehow? Or in the best case someone knows a similar approach...
just better.
TIA for any comments!
Regards
DChttp://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"DC" <dc@.upsize.de> wrote in message
news:5b7bac12.0502250228.4d94de6c@.posting.google.com...
> Hi,
> my requirement is to dynamically select certain columns from a table
> and depending on a flag some columns must be swapped. This is easier
> explained with the following statements one can paste into Query
> Analyzer:
>
> declare @.sql nvarchar(4000), @.cols nvarchar (500)
> set @.cols = '
> v1 = case when sel=1 then
> v1a else v1b end,
> v2 = case when sel=1 then v2a
> else v2b end
> '
> create table ##temp_test (
> v1a int,
> v1b int,
> v2a varchar(15),
> v2b varchar(15),
> sel bit
> )
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (1, 2, 'a1', 'b1', 0)
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (3, 4, 'a2', 'b2', 1)
> set @.sql = 'select ' + @.cols + 'from
> ##temp_test'
> exec sp_executesql @.sql
> drop table ##temp_test
>
> Result:
> v1 v2
> -- --
> 2 b1
> 3 a2
> This fits bots requirements: @.cols (which is stored in a table in the
> real world application) holds the column names to be selected, and it
> holds it in a way which also enables to switch between the a and b
> version of the columns via the CASE statements. The performance is OK.
> My problem: the real world query is a lot more complex, ##temp_test is
> actually a table with about 80 columns and there are a lot of other
> tables joined in.
> Since I am doomed to not use more than 4000 characters for the dynamic
> sql part (not using sp_executesql results in a huge performance
> penalty in my scenario) this approach in the end works find without
> all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
> @.cols variable length will grow by factor 2-3, typically from around
> 1000 - 2000 chars to about 2000 - 6000 chars.
> That's the end of the sp_executesql approach.
> Maybe our design is wrong in the first place. We have tried to
> alternate between the columns by using UNION to a View that contains
> the alternate column, but ended up in quiet a performance hit with
> more complex queries plus sometimes the SORT function would not work
> anymore and the like.
> Maybe I can shorten the
> v1 = case when sel=1 then
> v1a else v1b end
> part somehow? Or in the best case someone knows a similar approach...
> just better.
> TIA for any comments!
> Regards
> DC|||I don't understand why you want to use dynamic SQL for this. Where is
the string in @.cols generated from? You say it comes from a table but
why put it in a table at all? Why can't you define views for these
different views of the data?
David Portas
SQL Server MVP
--|||>> my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. <<
You might want to get any book on **basic software engineering** and
look up the concept of cohesion in a code module.|||Thank you! Can you recommend a good book on this topic? I probably
focussed too much on the loose coupling aspects? I support your claim
for the art of programming, but then again: it's only T-SQL. Hacky
stuff! Wouldn't a real programmer avoid messing around with SQL anyway?|||Thanks David, views or pre-generated stored procs are an option. We are
in the process of upgrading a life application though, and wanted to
integrate this additional requirement (the CASE req.) with the least
effort possible.|||Thank you Vinod, great article with a wealth of dynamic sql info. Does
not provide an exact solution, but pointed out that using EXEC shoud
not make as much of a difference as I experienced.|||> Wouldn't a real programmer avoid messing around with SQL anyway?
Sure. Why go looking for a new peg when you can just keep bashing that
square one into a round hole? After all, tables are only arrays aren't
they?
David Portas
SQL Server MVP
--|||On 25 Feb 2005 05:52:23 -0800, David Portas wrote:

>Sure. Why go looking for a new peg when you can just keep bashing that
>square one into a round hole? After all, tables are only arrays aren't
>they?
Hi David,
A "real" programmer - isn't that a guy who needs nothing but 8 switches
and 8 LED's to program the 'puter? Who needs all that modern assembler
language mnemonics anyway, huh?
(Hmmm - am I showing my age now <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Can you recommend a good book on this topic? <<
Any of the classic by Yourdon, Constantine, DeMarco or Gane & Sarson
are a good place to start.
They would avoid dynamic SQL and do the order of presentation of the
columns in the front end. I have become a fan of the idea that one
team in the shop handles the database and writes all the SQL for
everyone, and the application developers make requests to that team.