How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
Libor
Look here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micros...2557646f8b9a9b
http://groups.google.de/group/micros...144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6496fbdbbd4cffd9/952557646f8b9a9b?lnk=st&q=serversettings+date+format&rnum=1&hl=en#952557646f8b9a9b
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/6f543ebd963f6e20/46144e7348a7b6ff?lnk=st&q=%22Jens+Suessmeyer%22+boss+tibor&rnum=1&hl=en#46144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micro...52557646f8b9a9b
http://groups.google.de/group/micro...6144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default "filter" on Executionlog?

In the ReportServer database there is a table named Executionlog, which
contains data on the reports executed. But at any given time, it only seems
to contain data for the last 2 months. How do I change this filter?Found it!
Open SQL Server Management Studio and connect to Reporting Services. Right
click on the server name in the Object Explorer and select Properties. Under
the "Logging" folder you can change days of history.
"Soren" wrote:
> In the ReportServer database there is a table named Executionlog, which
> contains data on the reports executed. But at any given time, it only seems
> to contain data for the last 2 months. How do I change this filter?

How to change the caption of parameter at run time

Hi,
I would like to change the caption of parameter based upon the other
parameter value in RS 2000 / 2005.
Thankstry using conditional logic
eg
=iif(Parameters!FieldName.Value = testvalue, value if true , value if
false)|||oops posted to wrong thread - sorry
andygadget wrote:
> try using conditional logic
> eg
> =iif(Parameters!FieldName.Value = testvalue, value if true , value if
> false)

how to change server option for isolaiton level?

I'm having problem with locking in update and select.
my update takes long and select from clients happens all the time.
I know with NOLOCK in select or set transaction isolation level, I can
read data uncommited.
I want to know if there is an option I can change on server or
database
to change isolation level for all incoming connections.

thanks for your help in advance.AFAIK, you can't set READ UNCOMMITTED at the server level. This is
probably a good thing since one should read uncommitted data only in
specific situations where all off the ramifications have been fully
evaluated and understood.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Hi

To add to Dan's comment...

Are you wanting to make the database read only?

John

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Thanks to Dan/John.

No I just wanted to allow dirty-read, not read-only db.

I thought there was an option in SQL to set db to allow read uncommited.
Maybe for SQL 7?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> Hi
> To add to Dan's comment...
> Are you wanting to make the database read only?
> John
> "neo" <second714@.hotmail.com> wrote in message
> news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > I'm having problem with locking in update and select.
> > my update takes long and select from clients happens all the time.
> > I know with NOLOCK in select or set transaction isolation level, I can
> > read data uncommited.
> > I want to know if there is an option I can change on server or
> > database
> > to change isolation level for all incoming connections.
> > thanks for your help in advance.|||Hi

This is not something you would want do as a default, and when you do
require it, then it has to be a concious decission to allow this;
therefore not having it at server level or as a default for the
connection is probably a good thing. You can always set it for the
connection/session.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311101122.ca78281@.posting.google.com>...
> Thanks to Dan/John.
> No I just wanted to allow dirty-read, not read-only db.
> I thought there was an option in SQL to set db to allow read uncommited.
> Maybe for SQL 7?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> > Hi
> > To add to Dan's comment...
> > Are you wanting to make the database read only?
> > John
> > "neo" <second714@.hotmail.com> wrote in message
> > news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > > I'm having problem with locking in update and select.
> > > my update takes long and select from clients happens all the time.
> > > I know with NOLOCK in select or set transaction isolation level, I can
> > > read data uncommited.
> > > I want to know if there is an option I can change on server or
> > > database
> > > to change isolation level for all incoming connections.
> > > > thanks for your help in advance.|||Thanks,
one more thing,
what about Query analyzer?
is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
everytime in Query Analyzer?|||Hi

As far as I know, you can't do this, configurable options appear on
the tools/options dialogues and that is not one of them. If it was you
could use a specific configuration file. The only thing I can suggest
it to put it in a template.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311111702.4609998d@.posting.google.com>...
> Thanks,
> one more thing,
> what about Query analyzer?
> is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
> everytime in Query Analyzer?

How to change font type for annotations?

Hi all of you,

I'd like to find where can I change the fonttype for annotations done in Control Flow. I mean, every time that I do that appears the one I chose by default

TIA

In a short, set by default the font for comments

|||

Tools -> Options... : Environment/Fonts and Colors/Business Intelligence Designers

Thanks,

Bob

sql

How to change Crystal Reprots database at run time from ASP classic code

Dear All,

I'm new to Crystal Reprots and ASP, and need advises regarding to my problem below

I have two database in same SQL 2005 SERVER for TEST and LIVE Environtment, and I've created more than 100 reports with Crystal Reports 11 and call it from ASP classic page.

The problem is how can I change a database from TEST01 to LIVE01 at the run time from ASP code (including the sub reports) as I already using TEST01 database with ADO Connection on Crystal Reports and I do not want to set a new database location inside crystal for each reports

Thanks and wait for your reply soon.

Below is my code, which has no effect to crystal reports although I've change the database from TEST01 to LIVE01:

<%

Dim oADOConnection, oRptTable, oADORecordset, sql
Dim struser, strpwd, strdriver, dblocation, dbname, strConnect

struser = "sa"
strpwd = "sa"
strdriver = "{SQL SERVER}"
dblocation = "SQL200501"
dbname = "LIVE01" ' Changed from TEST01 to LIVE01

strConnect = "User Id=" & strUser & ";"
strConnect = strConnect & "PWD=" & strPwd & ";"
strConnect = strConnect & "DRIVER=" & StrDriver & ";"
strConnect = strConnect & "SERVER=" & DBLocation & ";"
strConnect = strConnect & "DATABASE=" & dbName

sql="Select * from Employee"

Set session("oApp") = Server.CreateObject("CrystalRuntime.Application.11")
Set session("oRpt") = session("oApp").OpenReport("C:\REPORTS\RPT01.RPT", 1) 'USING TEST01 DATABASE

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
session("oRpt").DiscardSavedData

Set oADOConnection = Server.CreateObject("ADODB.Connection")
oADOConnection.Open (strConnect)
Set oADORecordset = Server.CreateObject("ADODB.Recordset")

Set oRptTable = session("oRpt").Database.Tables.Item(1)
oRptTable.SetDataSource oADORecordset, 3

session("oRpt").SQLQueryString = CStr(sql)

session("oRpt").ReadRecords

%>See if you find answer here
http://support.businessobjects.com

How to change ConnectionString programmaticaly for report

Hi!
Is there any way to change the connecting string of a report data source
programmatically at run time. Actually my problem is that I have an ASP.NET
application and connection string is stored in Web.Config file. I have
multiple copies of databases hosted on different SQL Server and I update my
web.config connection string to switch between these databases. I just want
to use this connection string for my reports also. I am using SQL Server
authentication and user name and passwords are different for different
servers.
Please help me as this is becoming a show stopper for my application.
Regards,
NamwarOn Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an ASP.NET
> application and connection string is stored in Web.Config file. I have
> multiple copies of databases hosted on different SQL Server and I update my
> web.config connection string to switch between these databases. I just want
> to use this connection string for my reports also. I am using SQL Server
> authentication and user name and passwords are different for different
> servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
This link should be helpful.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/53e96ed5cde45213/bbe61adc20aeeb87?lnk=st&q=dynamic+datasource+reporting+services&rnum=1#bbe61adc20aeeb87
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 9, 3:50 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> > Hi!
> > Is there any way tochangethe connecting string of areportdata source
> > programmatically at run time. Actually my problem is that I have an ASP.NET
> > application and connection string is stored in Web.Config file. I have
> > multiple copies of databases hosted on different SQL Server and I update my
> > web.config connection string to switch between these databases. I just want
> > to use this connection string for my reports also. I am using SQL Server
> > authentication and user name and passwords are different for different
> > servers.
> > Please help me as this is becoming a show stopper for my application.
> > Regards,
> > Namwar
> This link should be helpful.http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I received your email. The only other thing I can think of is to
create the RDL file and/or the datasource file for the report
programmatically via a custom ASP.NET application.|||Have you tried using an expression as a datasource, as described here
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
... look for the section on "dynamic datasources" or "expressions" or
something like that.
I do understand that you want to read your stuff out of the web config file.
But there are several ways you probably could handle this -- without
programmatically altering the RDL file -- assuming the basic idea of a
datasource based on an expression will work for you. To start with, how are
the reports actually invoked (in the asp.net application? or elsewhere?) and
what access does reporting code have to the web.config file and its
contents?
>L<
"Namwar Rizvi" <namwar@.hotmail.com> wrote in message
news:efkKU0hqHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an
> ASP.NET application and connection string is stored in Web.Config file. I
> have multiple copies of databases hosted on different SQL Server and I
> update my web.config connection string to switch between these databases.
> I just want to use this connection string for my reports also. I am using
> SQL Server authentication and user name and passwords are different for
> different servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
>

How to catch stored procedure execution time?

Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:

Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.

ZYT

The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||Below batch query might help you to determine the performance in secounds

declare @.startproc datetime
declare @.endproc datetime
declare @.time integer

select @.startproc = getdate()

exec <stored procedure>
select @.endproc = getdate()

select @.time = DATEDIFF(second, @.startproc, @.endproc)

print str(@.time)|||Pat:

Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.

ZYT

Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!

Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.

I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!

As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).

The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!

-PatP|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.

Thank

ZYT

I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning

As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...

Logging the sproc call from code would not give you a true length due to other resources

I find that log so useful in so many ways

What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.

Well that's pure bull sheet

Blame it on profiler

How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:

By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.

Thanks

ZYT

No experience but Scenario 1in the paper describes exactly this.|||Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH|||Pootle:

I got it.

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)

can work. or refer http://support.microsoft.com/kb/270599.

Thanks everyone. This is a good post.

ZYT

Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.
You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system view
s.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to capute "Execution Reasults" to a text file

How can I save all the text in the SSIS "Execution results" pane to a file? (I can copy 1 line at a time, by right-clicking the message of interest, but there must be a better way.)

TIA,

Barker

Look up how to create log providers in BOL. It will let you dump whatever information you want out to a variety of sources.

How to capture What time zone by DB Server is using?

I need to capture the time zone of the DB server and adjust some date columns in some of my tables to make sure all the dates are using PST.

My database is distributed into different smaller databases (example into laptops, PDAs etc..) around the world.

End of day I will sync all the databases in PST time zone. Is there a command in SQL to capture the timezone.

Thanks

The query below will give the offset:

select datediff(hour, GETUTCDATE(), CURRENT_TIMESTAMP)

You can map this to a table that contains the various timezones (pre-defined) and use it in your queries.

|||

Is there any way to know if the timezone supports daylight saving time? This gets the current offset, but not the timezone meta data.

|||

Thanks for the quick response. This will help me to start working on my code.

Thanks for your time.

|||Not without reading the registry or writing extended SP or calling some OS utility or SQLCLR code. But it is very easy to build a TimeZone dimension/table that contains all the predefined offsets and attributes. You can then use the obtained offset to query the TimeZone table to get the rest of the details.|||That's what I thought. Thanks!

How to capture the only "time" into the database

I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:

txtTime.Text = DateTime.Now.ToLongTimeString()

Dim

parameterDateAs SqlParameter =New SqlParameter("@.Date_5",SqlDbType.DateTime)

parameterDate.Value = txtDate.Text

objCommand.Parameters.Add(parameterDate)

I've tried using Format() but it still get the same results. Can someone help me out? Thanks!

The SQL Datetime datatype will always store a date portion. as effectively all you are doing is storing a time difference since an initial base date/time.

Perhaps change your SQL datatype to varchar and store the time as a string, or else use a constant date along with your time value (IE 1/1/1900).

Dave

How to call just once for multiple report....

Hi all,
I will have to make it possible for one administrator to see one hundred
user's report in just one time.
What I have done is that I just embedded the module which was passing the
parameter to sql reporting server in C# programming code by using looping
statement .
However,if it is the only way, consequently I will have to call sproc as
many as user's count. What it means the performance will be very poor.
I am really curious to hear that. Please could you give me any comment on it?Have another report, fetch for all users and group by user.

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

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

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

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

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate response time.

Hello.
I have a task to do which is as follows:
I have to do an "id query" on a primary key of my db and then calculate the response time of the query.
Task says that i'm able to take screenshots of a graphical view of response time.And as a hint says that there are sql commands that can do such thing.Allthough i've searched the books i have access to and the internet i can't find such thing.I dunno maybe someone more familiar with this stuff can understand what i'm supposed to do.I don't want to give me the whole solution but just if u can tell me where to look.
I don't understand what means "do an id query on primary key 'SMTH' " and i can't find anythin which is related on calculating system response time to execute a query.
Any help is much appreciated.
Thx a lot :)See set statistics statement, set statistics time in particular