My end users want me to post 2 versions of my 2005 Reporting Services
reports. One that when they click on it automatically opens in Excel w/o
grouping...sort of a data dump. Then a second that is grouped, formatted and
pretty.
I know that I can have them export the report once run into Excel, but they
don't want that. (of course...;) So, does anyone know how to publish in
straight Excel in Reporting Services?
thanks in advance for any help!
MachelleYou didn't say if you are using Report Manager or not. The best way is to
use jump to URL and render in CSV ASCII format. The default for CSV is
unicode which Excel opens up with all the data in its own column.
Depending on your report you may or may not want to have a duplicate report
that you have cleaned up and call that one rather than the pretty one.
Regardless, if you have any data size at all URL is much faster.
If you are using Report Manager you could have a report that has no data,
just the parameters and then textboxes, one for pretty, one for Data Export
(I call it that in my link to differentiate that it is a data export, not
the pretty Excel export).
Note, the fields texboxes of the report have a name property that is set
during design to the name of the field. You can give it friendlier names if
you want.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:A5360BDE-440C-4AAF-8D07-46A9D3472567@.microsoft.com...
> My end users want me to post 2 versions of my 2005 Reporting Services
> reports. One that when they click on it automatically opens in Excel w/o
> grouping...sort of a data dump. Then a second that is grouped, formatted
> and
> pretty.
> I know that I can have them export the report once run into Excel, but
> they
> don't want that. (of course...;) So, does anyone know how to publish in
> straight Excel in Reporting Services?
> thanks in advance for any help!
> Machelle
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Tuesday, March 6, 2012
Sunday, February 19, 2012
2005 or 2000?
We are about to purchase a new Server which will act as a SQL Server and
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it g
et
> bigger, new users ?.....not knowing all the details, i would bet a one pr
oc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
>
>
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it g
et
> bigger, new users ?.....not knowing all the details, i would bet a one pr
oc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
>
>
2005 or 2000?
We are about to purchase a new Server which will act as a SQL Server and
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.
25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.
|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it get
> bigger, new users ?.....not knowing all the details, i would bet a one proc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
>
>
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.
25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.
|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it get
> bigger, new users ?.....not knowing all the details, i would bet a one proc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
>
>
2005 or 2000?
We are about to purchase a new Server which will act as a SQL Server and
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it get
> bigger, new users ?.....not knowing all the details, i would bet a one proc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> > We are about to purchase a new Server which will act as a SQL Server and
> > expand access to our existing MSDE database. Currently 5 users on a Lan
> > but
> > we will be going to approx. 25 users (15 of which will be using Terminal
> > Server). I appreciate this is about the cut off point for the Licensing
> > options (between Processor or Server Licensing). My worry is that I go for
> > the single processor option (and by extension a single processor server)
> > and
> > it isn't able to cope. Anyone got any experience of similar scenarios. I
> > don't really want to go to a Dual Processor option due to cost. Also, I
> > take
> > it it makes sense to pay the extra for a 2005 license (for future
> > benefits)
> > even though existing database is only compatible with 2000 currently.
>
>
expand access to our existing MSDE database. Currently 5 users on a Lan but
we will be going to approx. 25 users (15 of which will be using Terminal
Server). I appreciate this is about the cut off point for the Licensing
options (between Processor or Server Licensing). My worry is that I go for
the single processor option (and by extension a single processor server) and
it isn't able to cope. Anyone got any experience of similar scenarios. I
don't really want to go to a Dual Processor option due to cost. Also, I take
it it makes sense to pay the extra for a 2005 license (for future benefits)
even though existing database is only compatible with 2000 currently.25 users is tiny, how big is the Db and how many transactions...will it get
bigger, new users ?.....not knowing all the details, i would bet a one proc
would do ya on sql 2000
"AK" <AK@.discussions.microsoft.com> wrote in message
news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> We are about to purchase a new Server which will act as a SQL Server and
> expand access to our existing MSDE database. Currently 5 users on a Lan
> but
> we will be going to approx. 25 users (15 of which will be using Terminal
> Server). I appreciate this is about the cut off point for the Licensing
> options (between Processor or Server Licensing). My worry is that I go for
> the single processor option (and by extension a single processor server)
> and
> it isn't able to cope. Anyone got any experience of similar scenarios. I
> don't really want to go to a Dual Processor option due to cost. Also, I
> take
> it it makes sense to pay the extra for a 2005 license (for future
> benefits)
> even though existing database is only compatible with 2000 currently.|||Db is currently about .5 Gb, not a huge number of transactions. Majority of
Users will be accessing for reporting purposes. Sounds like 1 proc should be
ok then
"David J. Cartwright" wrote:
> 25 users is tiny, how big is the Db and how many transactions...will it get
> bigger, new users ?.....not knowing all the details, i would bet a one proc
> would do ya on sql 2000
> "AK" <AK@.discussions.microsoft.com> wrote in message
> news:9EC567CA-2EC2-448B-803E-04A11C256944@.microsoft.com...
> > We are about to purchase a new Server which will act as a SQL Server and
> > expand access to our existing MSDE database. Currently 5 users on a Lan
> > but
> > we will be going to approx. 25 users (15 of which will be using Terminal
> > Server). I appreciate this is about the cut off point for the Licensing
> > options (between Processor or Server Licensing). My worry is that I go for
> > the single processor option (and by extension a single processor server)
> > and
> > it isn't able to cope. Anyone got any experience of similar scenarios. I
> > don't really want to go to a Dual Processor option due to cost. Also, I
> > take
> > it it makes sense to pay the extra for a 2005 license (for future
> > benefits)
> > even though existing database is only compatible with 2000 currently.
>
>
Thursday, February 16, 2012
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
Saturday, February 11, 2012
2005 Datepicker control and dateformat
Hi, we have an installation of MSSQL reporting services where users
can select data based on date ranges. Since upgrading to 2005 we now
have a nice new Date picker control alongside textareas which accept
MSSQL datetime values.
This is all very nice but the date picker automatically formats the
date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
I
have checked the regional settings of both the browser/ server etc.
Scouring the Internet seems to point that this is a post SP2 problem
but there seems to be no fix other than making the datefields a type
of nvarchar and then converting them to dates in the Stored
Procedure.
If there really is no fix, how can I just disable this date picker
control? In 2000 people just entered the dates manually, this was
fine
but know there is a date picker control people are obviously going to
use it.
Thanks
RexOn Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
> Hi, we have an installation of MSSQL reporting services where users
> can select data based on date ranges. Since upgrading to 2005 we now
> have a nice new Date picker control alongside textareas which accept
> MSSQL datetime values.
> This is all very nice but the date picker automatically formats the
> date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> I
> have checked the regional settings of both the browser/ server etc.
> Scouring the Internet seems to point that this is a post SP2 problem
> but there seems to be no fix other than making the datefields a type
> of nvarchar and then converting them to dates in the Stored
> Procedure.
> If there really is no fix, how can I just disable this date picker
> control? In 2000 people just entered the dates manually, this was
> fine
> but know there is a date picker control people are obviously going to
> use it.
> Thanks
> Rex
Aside from the workarounds you have attempted, that is also all I can
think of. If I understand you correctly, to remove the date time
picker in a specific report you would just select the Layout view ->
Report drop-down tab -> Report Parameters... and select the parameter
you want to remove the date time picker for and then change the Data
type: to something other than DateTime. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jul 6, 10:54 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, we have an installation of MSSQL reporting services where users
> > can select data based on date ranges. Since upgrading to 2005 we now
> > have a nice new Date picker control alongside textareas which accept
> > MSSQL datetime values.
> > This is all very nice but the date picker automatically formats the
> > date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> > I
> > have checked the regional settings of both the browser/ server etc.
> > Scouring the Internet seems to point that this is a post SP2 problem
> > but there seems to be no fix other than making the datefields a type
> > of nvarchar and then converting them to dates in the Stored
> > Procedure.
> > If there really is no fix, how can I just disable this date picker
> > control? In 2000 people just entered the dates manually, this was
> > fine
> > but know there is a date picker control people are obviously going to
> > use it.
> > Thanks
> > Rex
> Aside from the workarounds you have attempted, that is also all I can
> think of. If I understand you correctly, to remove the date time
> picker in a specific report you would just select the Layout view ->
> Report drop-down tab -> Report Parameters... and select the parameter
> you want to remove the date time picker for and then change the Data
> type: to something other than DateTime. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Hi Enrique, thanks for your help. I have found a fix which seems to
work so I'm posting here to help anyone in the future who comes across
the problem.
In the folder where Reporting Services is installed fin a file called
ReportView.aspx
e.g.
c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
\ReportServer\Pages\ReportViewer.aspx
Open up this file and add the following to the Page Directive
Culture="en-AU"
This seemed to solve my problem
Regards
Rex|||On Jul 5, 10:17 pm, Markus...@.gmail.com wrote:
> On Jul 6, 10:54 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
> > > Hi, we have an installation of MSSQL reporting services where users
> > > can select data based on date ranges. Since upgrading to 2005 we now
> > > have a nice new Date picker control alongside textareas which accept
> > > MSSQL datetime values.
> > > This is all very nice but the date picker automatically formats the
> > > date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> > > I
> > > have checked the regional settings of both the browser/ server etc.
> > > Scouring the Internet seems to point that this is a post SP2 problem
> > > but there seems to be no fix other than making the datefields a type
> > > of nvarchar and then converting them to dates in the Stored
> > > Procedure.
> > > If there really is no fix, how can I just disable this date picker
> > > control? In 2000 people just entered the dates manually, this was
> > > fine
> > > but know there is a date picker control people are obviously going to
> > > use it.
> > > Thanks
> > > Rex
> > Aside from the workarounds you have attempted, that is also all I can
> > think of. If I understand you correctly, to remove the date time
> > picker in a specific report you would just select the Layout view ->
> > Report drop-down tab -> Report Parameters... and select the parameter
> > you want to remove the date time picker for and then change the Data
> > type: to something other than DateTime. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> Hi Enrique, thanks for your help. I have found a fix which seems to
> work so I'm posting here to help anyone in the future who comes across
> the problem.
> In the folder where Reporting Services is installed fin a file called
> ReportView.aspx
> e.g.
> c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
> \ReportServer\Pages\ReportViewer.aspx
> Open up this file and add the following to the Page Directive
> Culture="en-AU"
> This seemed to solve my problem
> Regards
> Rex
Very nice tidbit to know. So basically, that manually changed your
regional settings I guess.
Regards,
Enrique Martinez
Sr. Software Consultant
can select data based on date ranges. Since upgrading to 2005 we now
have a nice new Date picker control alongside textareas which accept
MSSQL datetime values.
This is all very nice but the date picker automatically formats the
date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
I
have checked the regional settings of both the browser/ server etc.
Scouring the Internet seems to point that this is a post SP2 problem
but there seems to be no fix other than making the datefields a type
of nvarchar and then converting them to dates in the Stored
Procedure.
If there really is no fix, how can I just disable this date picker
control? In 2000 people just entered the dates manually, this was
fine
but know there is a date picker control people are obviously going to
use it.
Thanks
RexOn Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
> Hi, we have an installation of MSSQL reporting services where users
> can select data based on date ranges. Since upgrading to 2005 we now
> have a nice new Date picker control alongside textareas which accept
> MSSQL datetime values.
> This is all very nice but the date picker automatically formats the
> date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> I
> have checked the regional settings of both the browser/ server etc.
> Scouring the Internet seems to point that this is a post SP2 problem
> but there seems to be no fix other than making the datefields a type
> of nvarchar and then converting them to dates in the Stored
> Procedure.
> If there really is no fix, how can I just disable this date picker
> control? In 2000 people just entered the dates manually, this was
> fine
> but know there is a date picker control people are obviously going to
> use it.
> Thanks
> Rex
Aside from the workarounds you have attempted, that is also all I can
think of. If I understand you correctly, to remove the date time
picker in a specific report you would just select the Layout view ->
Report drop-down tab -> Report Parameters... and select the parameter
you want to remove the date time picker for and then change the Data
type: to something other than DateTime. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jul 6, 10:54 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, we have an installation of MSSQL reporting services where users
> > can select data based on date ranges. Since upgrading to 2005 we now
> > have a nice new Date picker control alongside textareas which accept
> > MSSQL datetime values.
> > This is all very nice but the date picker automatically formats the
> > date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> > I
> > have checked the regional settings of both the browser/ server etc.
> > Scouring the Internet seems to point that this is a post SP2 problem
> > but there seems to be no fix other than making the datefields a type
> > of nvarchar and then converting them to dates in the Stored
> > Procedure.
> > If there really is no fix, how can I just disable this date picker
> > control? In 2000 people just entered the dates manually, this was
> > fine
> > but know there is a date picker control people are obviously going to
> > use it.
> > Thanks
> > Rex
> Aside from the workarounds you have attempted, that is also all I can
> think of. If I understand you correctly, to remove the date time
> picker in a specific report you would just select the Layout view ->
> Report drop-down tab -> Report Parameters... and select the parameter
> you want to remove the date time picker for and then change the Data
> type: to something other than DateTime. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Hi Enrique, thanks for your help. I have found a fix which seems to
work so I'm posting here to help anyone in the future who comes across
the problem.
In the folder where Reporting Services is installed fin a file called
ReportView.aspx
e.g.
c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
\ReportServer\Pages\ReportViewer.aspx
Open up this file and add the following to the Page Directive
Culture="en-AU"
This seemed to solve my problem
Regards
Rex|||On Jul 5, 10:17 pm, Markus...@.gmail.com wrote:
> On Jul 6, 10:54 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Jul 5, 4:50 pm, Markus...@.gmail.com wrote:
> > > Hi, we have an installation of MSSQL reporting services where users
> > > can select data based on date ranges. Since upgrading to 2005 we now
> > > have a nice new Date picker control alongside textareas which accept
> > > MSSQL datetime values.
> > > This is all very nice but the date picker automatically formats the
> > > date to US format mm/dd/yyyy. We need it to be in dd/mm/yyyy format.
> > > I
> > > have checked the regional settings of both the browser/ server etc.
> > > Scouring the Internet seems to point that this is a post SP2 problem
> > > but there seems to be no fix other than making the datefields a type
> > > of nvarchar and then converting them to dates in the Stored
> > > Procedure.
> > > If there really is no fix, how can I just disable this date picker
> > > control? In 2000 people just entered the dates manually, this was
> > > fine
> > > but know there is a date picker control people are obviously going to
> > > use it.
> > > Thanks
> > > Rex
> > Aside from the workarounds you have attempted, that is also all I can
> > think of. If I understand you correctly, to remove the date time
> > picker in a specific report you would just select the Layout view ->
> > Report drop-down tab -> Report Parameters... and select the parameter
> > you want to remove the date time picker for and then change the Data
> > type: to something other than DateTime. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> Hi Enrique, thanks for your help. I have found a fix which seems to
> work so I'm posting here to help anyone in the future who comes across
> the problem.
> In the folder where Reporting Services is installed fin a file called
> ReportView.aspx
> e.g.
> c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
> \ReportServer\Pages\ReportViewer.aspx
> Open up this file and add the following to the Page Directive
> Culture="en-AU"
> This seemed to solve my problem
> Regards
> Rex
Very nice tidbit to know. So basically, that manually changed your
regional settings I guess.
Regards,
Enrique Martinez
Sr. Software Consultant
Subscribe to:
Posts (Atom)