Changing the spinningwheel.gif

Has anyone had any luck changing the default spinningwheel.gif icon that
displays as a report is being generated?Hi,
I'm converting many many Access reports into a Reporting Services project.
Some of these reports are form letters with a lot of meticulously formatted
static text. The conversion performed by Reporting Services recreates the
report inside a List, as if the List groups the whole thing together.
In order to make some of the text look nice, I created a table that contains
no expressions other than plain text for the sole purpose of helping to ease
the alignment of many text blocks. This works fine in the Layout pane.
However, when I go to Preview it, I get this error:
"The table 'table1' is in a list that has no group expressions defined for
it. To use a data region in a list, the list must have group expressions."
To this I thought, fine, I'll take some time to remove everything from the
outside containing List and reassemble all the pieces (lines, text blocks,
etc.) so they look as I want. This worked ok until Preview, which produced
no errors but resulted in a horrible display - lines that met perfectly in
layout were no longer aligned, etc. The whole thing kind of exploded. When
I previewed before, with everything in the List, but without the table, the
preview looked perfect.
If I can get that table of plain text to work, I'll be very happy. Any
suggestions to circumventing the error above?
Many thanks in advance,
Mark|||Did you ever find a solution for this? I am attempting the same thing and am getting the exact same problem.
Thanks
From http://www.developmentnow.com/g/115_2006_4_0_0_732976/Changing-the-spinningwheel-gif.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
Steve
If you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>
|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
SteveIf you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing the Double Click Default in Object Explorer

Is there a way so that when I click on a stored proc it would default to the
modify option? Or when I double click on a table it would just open a query
window and show all records?
It would be great to be able to override the current default nature, for I
rarely need to see the params of an sp, but I do need to quickly edit an sp.
Keep the + for property info, but give me editablility!
Sorry for the tirade, lots of features, but I wonder how many developers
they asked for feed back on workflow when they designed the interface.
The standard behavior for Double clicking in a treeview when there are sub
nodes is to expand the node. What you are asking can be done by simply
right clicking and choosing the appropriate option. For instance with
stored procedures you can right click and choose Modify to open a window
with the sp code ready for editing. For a table you can right click then
choose Open to see all the rows. Either way it is two clicks of the mouse
so I really don't see where it is worth complaining about. It's really worth
everyone's while to spends a few moments exploring all the features that
SSMS has to offer. I agree that some things may not be intuitive or even
ideal but it has a lot of functionality if you give it the chance.
Andrew J. Kelly SQL MVP
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:8505D340-5A31-4878-9F2E-6A3D03D29B0C@.microsoft.com...
> Is there a way so that when I click on a stored proc it would default to
> the
> modify option? Or when I double click on a table it would just open a
> query
> window and show all records?
> It would be great to be able to override the current default nature, for I
> rarely need to see the params of an sp, but I do need to quickly edit an
> sp.
> Keep the + for property info, but give me editablility!
> Sorry for the tirade, lots of features, but I wonder how many developers
> they asked for feed back on workflow when they designed the interface.
>
|||Not to get really into a back and forth, but when I double click in any type
of explorer...what I am doing is executing or actioning an item. I would
think that the parameters is only part of an sp...if I click on a folder in
Explorer, do I want to see part of the folder (parameters) or all the
contents (complete script) of a folder?
Just thoughts and yes I am more than willing to try something new.
"Andrew J. Kelly" wrote:

> The standard behavior for Double clicking in a treeview when there are sub
> nodes is to expand the node. What you are asking can be done by simply
> right clicking and choosing the appropriate option. For instance with
> stored procedures you can right click and choose Modify to open a window
> with the sp code ready for editing. For a table you can right click then
> choose Open to see all the rows. Either way it is two clicks of the mouse
> so I really don't see where it is worth complaining about. It's really worth
> everyone's while to spends a few moments exploring all the features that
> SSMS has to offer. I agree that some things may not be intuitive or even
> ideal but it has a lot of functionality if you give it the chance.
> --
> Andrew J. Kelly SQL MVP
>
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:8505D340-5A31-4878-9F2E-6A3D03D29B0C@.microsoft.com...
>
>

changing the default value for a column

Does anybody know how I can change the default value for a column?
I was trying to remove the default value in order to add the new one
afterwards. This is what I tried:

alter table /table-name/ drop default for /column-name/
alter table /table-name/ alter column /column-name/
(/new-decl-without-default/)

It did not work. I cannot find a solution in the documentation. Maybe
you can help me out?

Thank you,
JohanALTER TABLE table_name
DROP CONSTRAINT name_of_default_constraint

ALTER TABLE table_name
ADD CONSTRAINT name_of_default_constraint DEFAULT 123 FOR column_name
--
David Portas
SQL Server MVP
--|||David Portas schreef:
> ALTER TABLE table_name
> DROP CONSTRAINT name_of_default_constraint

Thank you for the quick reply. But how do I know the
name_of_default_constraint? I created the default value like this:

alter table /table_name/ add default /default_value/ for /column_name/

Johan|||It's a good idea to give constraints meaningful names when you create
them, otherwise the server assigns them an obscure unique identifier
name. Use a consistent naming convention and then you'll know the
constraint names for tables and columns.

You can find the name of an existing default using the Object Browser
in Query Analyzer or in the output of sp_help 'table_name'
--
David Portas
SQL Server MVP
--|||Hi,

something like this could meet your requirements:

declare @.myStatement nvarchar(4000)

select @.myStatement=('ALTER TABLE RP_CUSTOM_ATTRIBUTE DROP CONSTRAINT ' +
(select so.name
from sysobjects so, syscolumns sc
where sc.name = 'YOUR_COLUMN_NAME' and sc.cdefault = so.id))

EXEC sp_executesql @.myStatement
go

Karsten

"Johan Vervloet" <johanv@.chiro.be> schrieb im Newsbeitrag
news:41fe1db7$0$28977$e4fe514c@.news.xs4all.nl...
> David Portas schreef:
> > ALTER TABLE table_name
> > DROP CONSTRAINT name_of_default_constraint
> Thank you for the quick reply. But how do I know the
> name_of_default_constraint? I created the default value like this:
> alter table /table_name/ add default /default_value/ for /column_name/
> Johan

changing the default value

Hi,
I am facing a problem. Our current database aontains more than 50 tables
having a datefield, whose default value is set as 'GetUTCDate()'. Now what i
need is a Query to replace those default value with GetDate() without any
dataloss. We dont care abt the time differrence.
Thanking u in advance
regards
LaraLara
Drop constraint and create a new one
"Lara" <lara@.hotmail.com> wrote in message
news:OoiUym$lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am facing a problem. Our current database aontains more than 50 tables
> having a datefield, whose default value is set as 'GetUTCDate()'. Now what
> i
> need is a Query to replace those default value with GetDate() without any
> dataloss. We dont care abt the time differrence.
> Thanking u in advance
> regards
> Lara
>

Changing the default stored procedure template (In MS-SQLSMStudio)

Well, the topic says it all, really.

I edit my stored procedures in Microsoft SQL Management Studio 9.00.2047, but whenever I click 'New Stored Procedure..' it coughs up some default and useless thing.

So I started looking how to change this but I can't find the -right- place. I have found a couple of files on my disk called 'Create Stored Procedure (New Menu).sql' - however altering these files does nothing for the default SP.

The template explorer happily lets me EDIT the same file, sadly the edits don't actually have any effect either.

Anyone?

i can edit the Template and add a new template in my scenario.... Just go to the template Press Right Button Select Edit do the modification as u want press the save button and close it...

Madhu

|||

Sadly, that doesn't work.

I can edit the template from the template explorer, sure. But saves don't affect the template my system actually uses. Useless.

I found my own solution though, although not as elegant. I edited the file:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

And Presto.

sql

changing the default port on a clustered 2005 server.

I am unclear as to which Ip address to change the standard port on. When setting up a cluster you have multiple IP address's including the nic that is used for the heartbeat. I have not found any documention on how to change both nodes to listen on a port other than 1433..

Any help would be greatly appreciated.

Set the one titled "IPAll" (you may need to scroll to the bottom of the list). The clustered SQL Server instance will apply it to the actual IP addresses of the cluster.

changing the default port on a clustered 2005 server.

I am unclear as to which Ip address to change the standard port on. When setting up a cluster you have multiple IP address's including the nic that is used for the heartbeat. I have not found any documention on how to change both nodes to listen on a port other than 1433..

Any help would be greatly appreciated.

Set the one titled "IPAll" (you may need to scroll to the bottom of the list). The clustered SQL Server instance will apply it to the actual IP addresses of the cluster.

Changing the default port number and still connecting

Hello,
I have SQL Server 2000 Enterprise Edition, and I'd like to change the
default port to something else (we'll use 2033 for this example). I've
tried to connect from another computer on the same subnet using
"ServerName:2033" as the server, but it doesn't connect. It will connect if
I change the Server to use the default port and change the server to connect
to on the client to "ServerName", but either I"m not referencing the new
port number properly or ?
Any help you could provide would be great.
Thanks In Advance!
Rick
Hi,
Stop all applications and Third party software services in the server.
After the applications and services are stopped, change the port and start
SQL Server
service. This should enable SQL Server to take control of new port. Open the
sql server error log and see if the new port is used.
See additional details from old post.
http://groups.google.co.in/groups?q=...ft.com&rnum=10
Thanks
Hari
SQL Server MVP
"RWC" <rcollens_SPAMONATOR_@.hemmingway.com> wrote in message
news:kUAde.1198452$8l.948157@.pd7tw1no...
> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to
connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ?
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>
|||Hi
"ServerName, 2033" is the correct format.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RWC" wrote:

> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect if
> I change the Server to use the default port and change the server to connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ?
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>
>
|||You're a GENIUS man! ;)
Thanks Mike, that worked like a charm. (I figured it would be the
connection syntax).
Thanks again!
Rick
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C7FAFA8A-7B27-4E48-88CB-90E00FF63573@.microsoft.com...[vbcol=seagreen]
> Hi
> "ServerName, 2033" is the correct format.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "RWC" wrote:

Changing the default port number and still connecting

Hello,
I have SQL Server 2000 Enterprise Edition, and I'd like to change the
default port to something else (we'll use 2033 for this example). I've
tried to connect from another computer on the same subnet using
"ServerName:2033" as the server, but it doesn't connect. It will connect if
I change the Server to use the default port and change the server to connect
to on the client to "ServerName", but either I"m not referencing the new
port number properly or '
Any help you could provide would be great.
Thanks In Advance!
RickHi,
Stop all applications and Third party software services in the server.
After the applications and services are stopped, change the port and start
SQL Server
service. This should enable SQL Server to take control of new port. Open the
sql server error log and see if the new port is used.
See additional details from old post.
gVBHA.237%40cppssbbsa01.microsoft.com&rnum=10" target="_blank">http://groups.google.co.in/groups?q...oft.com&rnum=10
Thanks
Hari
SQL Server MVP
"RWC" <rcollens_SPAMONATOR_@.hemmingway.com> wrote in message
news:kUAde.1198452$8l.948157@.pd7tw1no...
> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to
connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or '
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>|||Hi
"ServerName, 2033" is the correct format.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RWC" wrote:

> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to conne
ct
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or '
> Any help you could provide would be great.
> Thanks In Advance!
> Rick
>
>|||You're a GENIUS man! ;)
Thanks Mike, that worked like a charm. (I figured it would be the
connection syntax).
Thanks again!
Rick
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C7FAFA8A-7B27-4E48-88CB-90E00FF63573@.microsoft.com...[vbcol=seagreen]
> Hi
> "ServerName, 2033" is the correct format.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "RWC" wrote:
>

Changing the default port number and still connecting

Hello,

I have SQL Server 2000 Enterprise Edition, and I'd like to change the
default port to something else (we'll use 2033 for this example). I've
tried to connect from another computer on the same subnet using
"ServerName:2033" as the server, but it doesn't connect. It will connect if
I change the Server to use the default port and change the server to connect
to on the client to "ServerName", but either I"m not referencing the new
port number properly or ??

Any help you could provide would be great.

Thanks In Advance!
RickHi,

Stop all applications and Third party software services in the server.
After the applications and services are stopped, change the port and start
SQL Server
service. This should enable SQL Server to take control of new port. Open the
sql server error log and see if the new port is used.

See additional details from old post.

http://groups.google.co.in/groups?q...oft.com&rnum=10

Thanks
Hari
SQL Server MVP

"RWC" <rcollens_SPAMONATOR_@.hemmingway.com> wrote in message
news:kUAde.1198452$8l.948157@.pd7tw1no...
> Hello,
> I have SQL Server 2000 Enterprise Edition, and I'd like to change the
> default port to something else (we'll use 2033 for this example). I've
> tried to connect from another computer on the same subnet using
> "ServerName:2033" as the server, but it doesn't connect. It will connect
if
> I change the Server to use the default port and change the server to
connect
> to on the client to "ServerName", but either I"m not referencing the new
> port number properly or ??
> Any help you could provide would be great.
> Thanks In Advance!
> Ricksql

Changing the Default Port

Is it possible to change the default port of 1433 for Sql Server after the
database has been installed? If so, can someone point me in the right
direction?Use Server Server Network Utility
"Simon" <nospam@.nospam.nospam> wrote in message
news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> Is it possible to change the default port of 1433 for Sql Server after the
> database has been installed? If so, can someone point me in the right
> direction?
>|||Yes. You can do this from the Server Network Utility program. Select the
TCP/IP netlib and then click properties.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Simon" <nospam@.nospam.nospam> wrote in message
news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> Is it possible to change the default port of 1433 for Sql Server after the
> database has been installed? If so, can someone point me in the right
> direction?
>|||Duh... Brian, thank you!
BTW, great articles in SQL Server magazine!
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:O9oOSxzuDHA.620@.TK2MSFTNGP10.phx.gbl...
> Yes. You can do this from the Server Network Utility program. Select the
> TCP/IP netlib and then click properties.
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Simon" <nospam@.nospam.nospam> wrote in message
> news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> >
> > Is it possible to change the default port of 1433 for Sql Server after
the
> > database has been installed? If so, can someone point me in the
right
> > direction?
> >
> >
>|||I'm glad you enjoy them!
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Simon" <nospam@.nospam.nospam> wrote in message
news:ebG5A2zuDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Duh... Brian, thank you!
> BTW, great articles in SQL Server magazine!
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:O9oOSxzuDHA.620@.TK2MSFTNGP10.phx.gbl...
> > Yes. You can do this from the Server Network Utility program. Select the
> > TCP/IP netlib and then click properties.
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Simon" <nospam@.nospam.nospam> wrote in message
> > news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > >
> > > Is it possible to change the default port of 1433 for Sql Server after
> the
> > > database has been installed? If so, can someone point me in the
> right
> > > direction?
> > >
> > >
> >
> >
>

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

changing the default drive and folder for DBs

Hi;

I feel like an idiot to be asking this,
but how do I change the default drive and folder
in which new databases are created?

I've been looking all over Server Mgmt Studio,
and I can't find anything. I'm thinking that someone
can tell me this off the top of their heads,
or slip me a link to a web page.

Thanks,

Never mind everyone - I found my own solution.

THANKS anyway.

|||

Please tell us how you fix that

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
Simon
Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>
|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The application
s
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main probl
ems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonSimon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is
> that dates are being stored in the British format and I desperately need t
hem to be stored in the
> American format. The applications I'm making are all for the American mark
et and having the dates
> stored in the db as british dates is causing all sorts of problems. The tw
o main problems is that
> my application sends american formatted dates to the application (which th
e db freaks out at) and
> the database tries to send back british dates (which my application freaks
out at).
> Can anyone tell me, is there a way to have the DB store the dates in the A
merican format? I was
> hoping that this could be set on a per database basis because I do have a
couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simonsql

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonHi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon

Changing the default database files path fails

i have the following code segment which works fine as is:

try

{

ServerConnection connection = new ServerConnection("localhost");

Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))

{

Database database = new Database(server, "SMO_TEST");

//FileGroup fileGroup = new FileGroup(database, "PRIMARY");

//database.FileGroups.Add(fileGroup);

//DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");

//dataFile.FileName = "D:\\SMO_TEST_Data.mdf";

//fileGroup.Files.Add(dataFile);

//LogFile logFile = new LogFile(database, "SMO_TEST_Log");

//logFile.FileName = "D:\\SMO_TEST_Log.ldf";

//database.LogFiles.Add(logFile);

database.Create();

}

}

catch (FailedOperationException ex)

{

Console.WriteLine(ex.Message);

}

when i uncomment the lines commented above it fails.

Any help?

Fails with which error ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

hi Jenz

thanks for your reply. the code posted earlier worked smoothly just after a computer restart.the error message was either

"Create failed for Database 'SMO_TEST'." or "Drop failed for Database 'SMO_TEST'." with whatever mistake i made, which provided no help in correcting the problem.

however another issue arised when i tried to use a transaction using either method connection.BeginTransaction(); or server.ConnectionContext.BeginTransaction();

(1) how to correct this problem? and

(2) what kind of transaction occures here? is it a distributed transaction?

also i would like to ask

(3)if there is any way to diable full text indexing other than using

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");?(see the code below) .

sql server management studio creates the database with full text indexing disabled by default. but when using smo it is enabled by default and the documentation claims the opposite.

(4) so where does smo take its default values from?

string sqlServerInstance = "localhost";

string databaseName = "SMO_TEST";

string dataFileLogicalName = databaseName + "_Data";

string logFileLogicalName = databaseName + "_Log";

string dataFilePhysicalName = dataFileLogicalName + ".mdf";

string logFilePhysicalName = logFileLogicalName + ".ldf";

string dataFilePath = "D:\\TEST_DIR\\";

string logFilePath = "D:\\TEST_DIR\\";

if (!Directory.Exists(dataFilePath))

{

Directory.CreateDirectory(dataFilePath);

//throw new DirectoryNotFoundException("The data file directory " + dataFilePath + " does not exists.");

}

if (!Directory.Exists(logFilePath))

{

Directory.CreateDirectory(logFilePath);

//throw new DirectoryNotFoundException("The log file directory " + logFilePath + " does not exists.");

}

string defaultFileGroupName = "PRIMARY";

ServerConnection connection;

connection = new ServerConnection(sqlServerInstance);

Server server = new Server(connection);

try

{

//connection.BeginTransaction();

server.ConnectionContext.BeginTransaction();

if (server.Databases.Contains(databaseName))

{

server.Databases[databaseName].Drop();

server.Alter();

}

Database database = new Database(server, databaseName);

FileGroup fileGroup = new FileGroup(database, defaultFileGroupName);

database.FileGroups.Add(fileGroup);

DataFile dataFile = new DataFile(fileGroup, dataFileLogicalName);

dataFile.FileName = dataFilePath + dataFilePhysicalName;

dataFile.GrowthType = FileGrowthType.KB;

dataFile.Growth = 1024d;

fileGroup.Files.Add(dataFile);

LogFile logFile = new LogFile(database, logFileLogicalName);

logFile.FileName = logFilePath + logFilePhysicalName;

logFile.GrowthType = FileGrowthType.Percent;

logFile.MaxSize = 2147483648d;

database.LogFiles.Add(logFile);

database.Create();

// disable full text indexing

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

//connection.CommitTransaction();

server.ConnectionContext.CommitTransaction();

}

catch (FailedOperationException ex)

{

//connection.RollBackTransaction();

server.ConnectionContext.RollBackTransaction();

Console.WriteLine(ex.Message);

}

finally

{

if (connection != null)

{

if (connection.IsOpen)

{

connection.Disconnect();

}

connection = null;

}

}

thanks again

|||<P align=left><FONT face=Arial size=2>1) Some commands cannot be used within a transaction, but the problem in your case is that the database seems to be in use during the dropping action, see the blog post on my site for more information about that:<BR><BR>2) local one<BR><BR>3) DId not see anything for that, SMO tell that this functionality is deprecated in further versions.</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>4) Which values do you mean ? Some are coded in the SMO library, some are taken from the db system.</P>
<P dir=ltr style="MARGIN-RIGHT: 0px" align=left><BR><BR>Jens K. Suessmeyer<BR><BR><BR><A href="http://www.sqlserver2005.de">http://www.sqlserver2005.de</A><BR></P>|||

Waleed,

You need to add the data and log files before you can assign the filename properties. Try the code here (I've adjusted your code):

try
{
ServerConnection connection = new ServerConnection("localhost");
Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))
{

Database database = new Database(server, "SMO_TEST");

FileGroup fileGroup = new FileGroup(database, "PRIMARY");
database.FileGroups.Add(fileGroup);
DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");
fileGroup.Files.Add(dataFile);
dataFile.FileName = "D:\\SMO_TEST_Data.mdf";
LogFile logFile = new LogFile(database, "SMO_TEST_Log");
database.LogFiles.Add(logFile);
logFile.FileName = "D:\\SMO_TEST_Log.ldf";
database.Create();
}
}
catch (FailedOperationException ex)
{
Console.WriteLine(ex.Message);
}

|||

dear MVPs,

I tried to loop through the inner exception of the failedOperationException and it turned out that the problem is that Database.Create(), Database.Drop() and

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

cannot be contained in a transaction

this answers my question about transactions

thank you very much

Changing the default database

I have a large number of databases on my localhost. Whenever I open up
Management Studio, there is one database that is selected by default
(under Available Databases). But this is not the database that I use
regularly. Everytime I run a query, I have to make sure that the
database is changed.
Is there a way to change the default database selected in the
Available Databases drop down?
Thanks
How do you open the query window? The exact steps for opening the query window determines what steps
you need to take to make this your default database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"spdude" <sandeshmeda@.gmail.com> wrote in message
news:1178132157.841268.133800@.y80g2000hsf.googlegr oups.com...
>I have a large number of databases on my localhost. Whenever I open up
> Management Studio, there is one database that is selected by default
> (under Available Databases). But this is not the database that I use
> regularly. Everytime I run a query, I have to make sure that the
> database is changed.
> Is there a way to change the default database selected in the
> Available Databases drop down?
> Thanks
>