Change Default Langauge for a database

Hello All,
We have an SQL Server installation setup with US english as the default
language. Now we need that one of the databases use British english as the
default language for that database, but I have not been able to find an
option to do so in the SQL Server management interface, is there a way to do
that ? if so how can it be done.
Thanks a lot.
Imran.
Default language is set at the login level. If you don't specify a language for a login, then a
system default is used, which you can set using sp_configure. There is no such thing as default
language for a certain database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.bindweb.com> wrote in message news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl...
> Hello All,
> We have an SQL Server installation setup with US english as the default language. Now we need
> that one of the databases use British english as the default language for that database, but I
> have not been able to find an option to do so in the SQL Server management interface, is there a
> way to do that ? if so how can it be done.
> Thanks a lot.
> Imran.
>
|||Thanks a lot for the response, that sorted my problem
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OW5edVdsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> Default language is set at the login level. If you don't specify a
> language for a login, then a system default is used, which you can set
> using sp_configure. There is no such thing as default language for a
> certain database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.bindweb.com> wrote in message
> news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl...
>

Change Default Langauge for a database

Hello All,
We have an SQL Server installation setup with US english as the default
language. Now we need that one of the databases use British english as the
default language for that database, but I have not been able to find an
option to do so in the SQL Server management interface, is there a way to do
that ? if so how can it be done.
Thanks a lot.
Imran.Default language is set at the login level. If you don't specify a language for a login, then a
system default is used, which you can set using sp_configure. There is no such thing as default
language for a certain database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.bindweb.com> wrote in message news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl...
> Hello All,
> We have an SQL Server installation setup with US english as the default language. Now we need
> that one of the databases use British english as the default language for that database, but I
> have not been able to find an option to do so in the SQL Server management interface, is there a
> way to do that ? if so how can it be done.
> Thanks a lot.
> Imran.
>|||Thanks a lot for the response, that sorted my problem :)
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OW5edVdsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> Default language is set at the login level. If you don't specify a
> language for a login, then a system default is used, which you can set
> using sp_configure. There is no such thing as default language for a
> certain database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.bindweb.com> wrote in message
> news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl...
>> Hello All,
>> We have an SQL Server installation setup with US english as the
>> default language. Now we need that one of the databases use British
>> english as the default language for that database, but I have not been
>> able to find an option to do so in the SQL Server management interface,
>> is there a way to do that ? if so how can it be done.
>> Thanks a lot.
>> Imran.
>sql

Change Default Langauge for a database

Hello All,
We have an SQL Server installation setup with US english as the default
language. Now we need that one of the databases use British english as the
default language for that database, but I have not been able to find an
option to do so in the SQL Server management interface, is there a way to do
that ? if so how can it be done.
Thanks a lot.
Imran.Default language is set at the login level. If you don't specify a language
for a login, then a
system default is used, which you can set using sp_configure. There is no su
ch thing as default
language for a certain database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.bindweb.com> wrote in message news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl
..
> Hello All,
> We have an SQL Server installation setup with US english as the default
language. Now we need
> that one of the databases use British english as the default language for
that database, but I
> have not been able to find an option to do so in the SQL Server management
interface, is there a
> way to do that ? if so how can it be done.
> Thanks a lot.
> Imran.
>|||Thanks a lot for the response, that sorted my problem
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OW5edVdsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> Default language is set at the login level. If you don't specify a
> language for a login, then a system default is used, which you can set
> using sp_configure. There is no such thing as default language for a
> certain database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.bindweb.com> wrote in message
> news:uGUhRRdsEHA.2560@.tk2msftngp13.phx.gbl...
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default...b;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.
|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default...b;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> > There's a setting in properties for SQL server in Enterprise Manager where
> > you can point out new default location for databases and logs. I want to
> > do
> > just that, but have two questions:
> >
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> >
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> >
> > [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> >
> > Looking forward to any help.
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/defaul...kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFil
es
> or F:\DBLogFiles). These don't need to match the default SQL Server folde
r
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/defaul...kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

change DB owner

I have several Production Databases on my SQL Server 2000 Standard Edition Server (mixed mode authentication) that I'd like to change DBO Permissions on. When I look at the Properties of the DB(s) from EM, the Database owner is showing up as a Windows NT USer who is no longer even with the company!!

Can I use the sp_changedbowner 'sa' command to change the database owner to sa without disrupting Production? Might sound like a dumb question.. but ya never know!!!!! Also, I had someone on another forum tell me that 'sa' as db owner is a bad idea but I don't know why??? can anyone elaborate on that???I am also interested in these questions (and so have posted to just put it back to the top of the list in the hope someone can answer - is that bad etiquette?)|||Yes, you can always change the database owner to sa. At least as far as I know, this is a good idea in almost every case.

As far as the person that thought it was a bad idea, I'd love to hear their reasoning... While I can concoct a case where it was a bad idea, it would take some considerable doing, and would probably never happen in "the real world".

-PatP|||Thank's Pat.

jpotucek, even though the user that own's the database has left, does their Windows account still exist? Does it cause you any issues?|||The only reason I ever had a person other than sa as dbo, was when I had a person who kept forgetting to put dbo in front of objects she created.

Tim S|||I don't manage the NT accounts here, just the logins on the SQL Server. The accounts still exist and they may even be disabled, i don't know. I'm going to change the DB Owner on all to sa and drop the users from my sql servers - then I know i won't have any problems!!!!

THank you one and all

Change datasource on the fly

is it possible to change the differece datasource when execution of report,
since I need to keep the data into 5 difference databases by some reasons,
but I don't want to create 5 new reports for that each time. it wastle a lot
of time to reproduce the same reports. any ideas I appreciated.
Thanks in advance.Hi,
you can use parametrization to use IIF to change the datasource
properties.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Could you please give me some samples, I am a newbie for RS.
"Jens" <Jens@.sqlserver2005.de>
'?:1168941447.869460.85450@.11g2000cwr.googlegroups.com...
> Hi,
> you can use parametrization to use IIF to change the datasource
> properties.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Hi,
i don=B4t have actually a RS Server at hand but it is like the following
query (non-tested), change the connection string to listen to a
parameter:
=3D"Data Source=3D" & Parameters!DataSourceSelector.Value & ";Initial
Catalog=3DSomeDB"
You can also use IIF in the above mentioned statment, like:
=3D"Data Source=3D" & IIF(Parameters!DataSourceSelector.Value =3D
"TestEnvironment";"TestServer";ProductionServer) & ";Initial
Catalog=3DSomeDB"
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Change data source dynamically in report server.

Hi,

I need to change the data source for report server objects (rdl files) dynamically. In other words, I am having databases with identical structures but different data for different clients. Then I need to use same set of reports for different customers. They will access reports through web and they should provide user id/pwd and database name in web login interface, and then it should direct to the the particular database and should be able to access the same set of reports.

Any thoughts !

Roshan.

You can accomplish this using Report Parameters. The connection string would look something like the following.

="Data Source=ServerName;Initial Catalog=" & Parameters!Database.Value

Ian|||

Thanks, it is working.

Is there any way to change datasource in report model dynamically ?. i.e. sharing reports created from Report Builder between multiple databases.

I would like to create one standard report model with standard set of reports and letting usrs (different customers) add their own reports.

-Roshan

Change Authentication

SQL Server has been installed using Windows Authentication.
I want to change one of my databases to mixed mode.
Can someone tell me if this is possible and how to go about it ?
TIAthe authentication is done at the server level so I don't think you can do
this at DB level.
anyone?
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ucmjj36pDHA.2772@.TK2MSFTNGP12.phx.gbl...
> SQL Server has been installed using Windows Authentication.
> I want to change one of my databases to mixed mode.
> Can someone tell me if this is possible and how to go about it ?
> TIA
>|||Authentication modes apply to the server and can't be set
differently for individual databases.
-Sue
On Mon, 10 Nov 2003 17:13:00 -0000, "Poppy"
<paul.diamond@.NOSPAMthemedialounge.com> wrote:
>SQL Server has been installed using Windows Authentication.
>I want to change one of my databases to mixed mode.
>Can someone tell me if this is possible and how to go about it ?
>TIA
>

change all char columns to varchar

Hi ,
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>

Central vs separated databases

What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
>
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -
On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
>
>
>
>
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.

Central vs separated databases

Hi,
I'm making a study about pro en contra for central and separated
databases in one sql server.
I googled and found some interesting opinions but most of the are
about management.
But what about performance, what if a database has 100-200 tables and
50 views and keeps growing.
Can Sql server handle this?
What about restore?
What about (I'm missing)...
Any help or a good link is welcome.Performance is not generally a function of the number of tables or
views. With a good database design the limiting factors are more the
number of concurrent users and transactions, as well as table size and
database size.
100-200 tables with 50 views, those numbers are not a challenge at
all. How any gigabytes of data do you anticipate? How many users? Is
it transaction processing or reporting?
Roy Harvey
Beacon Falls, CT
On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hdinf@.hotmail.com> wrote:
>Hi,
>I'm making a study about pro en contra for central and separated
>databases in one sql server.
>I googled and found some interesting opinions but most of the are
>about management.
>But what about performance, what if a database has 100-200 tables and
>50 views and keeps growing.
>Can Sql server handle this?
>What about restore?
>What about (I'm missing)...
>Any help or a good link is welcome.|||On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> Performance is not generally a function of the number of tables or
> views. With a good database design the limiting factors are more the
> number of concurrent users and transactions, as well as table size and
> database size.
> 100-200 tables with 50 views, those numbers are not a challenge at
> all. How any gigabytes of data do you anticipate? How many users? Is
> it transaction processing or reporting?
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hd...@.hotmail.com> wrote:
> >Hi,
> >I'm making a study about pro en contra for central and separated
> >databases in one sql server.
> >I googled and found some interesting opinions but most of the are
> >about management.
> >But what about performance, what if a database has 100-200 tables and
> >50 views and keeps growing.
> >Can Sql server handle this?
> >What about restore?
> >What about (I'm missing)...
> >Any help or a good link is welcome.- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
It's should be processing and reporting.
The users are now 50 but keeps growing like all the rest of course.
100-200 tables now are 200-300 tables next year ...|||On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hdinf@.hotmail.com> wrote:
>On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
>wrote:
>> 100-200 tables with 50 views, those numbers are not a challenge at
>> all. How any gigabytes of data do you anticipate? How many users? Is
>> it transaction processing or reporting?
>It's should be processing and reporting.
>The users are now 50 but keeps growing like all the rest of course.
>100-200 tables now are 200-300 tables next year ...
You still did not provide the size estimate (gigabytes).
From what you have mentioned so far there is no reason related to size
or performance that would suggest needing more than a single database.
The one really good reason for considering multiple databases that
always applies regardless of how small the set of data or the number
of users is that the data is unrelated. The company's internal
phonebook probably should not be in the same database as the accounts
payable.
Roy Harvey
Beacon Falls, CT|||On 5 nov, 17:42, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hd...@.hotmail.com> wrote:
> >On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
> >wrote:
> >> 100-200 tables with 50 views, those numbers are not a challenge at
> >> all. How any gigabytes of data do you anticipate? How many users? Is
> >> it transaction processing or reporting?
> >It's should be processing and reporting.
> >The users are now 50 but keeps growing like all the rest of course.
> >100-200 tables now are 200-300 tables next year ...
> You still did not provide the size estimate (gigabytes).
> From what you have mentioned so far there is no reason related to size
> or performance that would suggest needing more than a single database.
> The one really good reason for considering multiple databases that
> always applies regardless of how small the set of data or the number
> of users is that the data is unrelated. The company's internal
> phonebook probably should not be in the same database as the accounts
> payable.
> Roy Harvey
> Beacon Falls, CT
Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
And what if you want a base table for common thing like name,
address,nationality... or a postal code table (wich will be used in
more than one database).
Maybe yours isn't a good exapmle but what about following situation:
Employee database, intranet database, room reservation (for employees
and others),...
People can be in all of the databases.
So shouldn't it be better in this situation to create a general
database for the table people (and use a trigger to check te
integrity).|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI <hdinf@.hotmail.com> wrote:
>Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
>And what if you want a base table for common thing like name,
>address,nationality... or a postal code table (wich will be used in
>more than one database).
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
1 GB is small. These days 50GB is not large. You need not consider
multiple databases because of size.
If you are going to have the same entities for multiple applications
then integrity should be enforced through foreign key constrains, and
that requires that they be in the same database. So yes, if
applications are going to be integrated to that degree that justifies
keeping them in a single database.
Roy Harvey
Beacon Falls, CT|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI wrote:
(snip)
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
Hi HDI,
Employee data, intranet, and room reservations are all part of managing
the internals of a company. I would never spread these tables out over
multiple databases.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
> >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -|||On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
> > Now +- 1GB.
> > >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> > And what if you want a base table for common thing like name,
> > address,nationality... or a postal code table (wich will be used in
> > more than one database).
> > Maybe yours isn't a good exapmle but what about following situation:
> > Employee database, intranet database, room reservation (for employees
> > and others),...
> > People can be in all of the databases.
> > So shouldn't it be better in this situation to create a general
> > database for the table people (and use a trigger to check te
> > integrity).- Hide quoted text -
> > - Show quoted text -- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.

Central Database Design

SQL SERVER 2K
We have 30 databases each for one business units.
Now I am consolidating few tables from 30 databases to one database for
Analysis purpose.
Now my tables in Central db have millions of records.
I need some tips to handle this huge tables for query purpose (already
applied with all Indexes).
Can I go with INDEXED VIEWS '
Thx
ShIndexed views are only really good for aggregations and joining data from
mutiple tables, is this what you need to do? Several of our tables are
over a million rows in our production environment and it all comes down to
indexing. Did you create new indexes designed for queries or are they the
existing indexes from the original tables?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> SQL SERVER 2K
> We have 30 databases each for one business units.
> Now I am consolidating few tables from 30 databases to one database for
> Analysis purpose.
> Now my tables in Central db have millions of records.
> I need some tips to handle this huge tables for query purpose (already
> applied with all Indexes).
> Can I go with INDEXED VIEWS '
> Thx
> Sh
>|||I am sorry, I meant PARTITIONED VIEWS
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> Indexed views are only really good for aggregations and joining data from
> mutiple tables, is this what you need to do? Several of our tables are
> over a million rows in our production environment and it all comes down to
> indexing. Did you create new indexes designed for queries or are they the
> existing indexes from the original tables?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > SQL SERVER 2K
> >
> > We have 30 databases each for one business units.
> > Now I am consolidating few tables from 30 databases to one database for
> > Analysis purpose.
> >
> > Now my tables in Central db have millions of records.
> > I need some tips to handle this huge tables for query purpose (already
> > applied with all Indexes).
> >
> > Can I go with INDEXED VIEWS '
> >
> > Thx
> > Sh
> >
> >
>|||Partition views can help here. You will be able to keep table sizes down
and this can increase query times when done right. It sounds like you moved
multiple tables into one table. If this is the case you will need to break
them back out into smaller tables, add partitioning columns, modify Primary
Keys and build the view. That might be a hassle. I can query a 20million
record table and get a response back in under a second, I just did a query
on 250million and elasped time was 10sec(only one index on there, I know I
could get quicker:)). Did you change indexes for reporting or not?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I am sorry, I meant PARTITIONED VIEWS
> Sh
> "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > Indexed views are only really good for aggregations and joining data
from
> > mutiple tables, is this what you need to do? Several of our tables
are
> > over a million rows in our production environment and it all comes down
to
> > indexing. Did you create new indexes designed for queries or are they
the
> > existing indexes from the original tables?
> >
> >
> > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > SQL SERVER 2K
> > >
> > > We have 30 databases each for one business units.
> > > Now I am consolidating few tables from 30 databases to one database
for
> > > Analysis purpose.
> > >
> > > Now my tables in Central db have millions of records.
> > > I need some tips to handle this huge tables for query purpose (already
> > > applied with all Indexes).
> > >
> > > Can I go with INDEXED VIEWS '
> > >
> > > Thx
> > > Sh
> > >
> > >
> >
> >
>|||Thanks for the suggestions.
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OitSnTFYDHA.2032@.TK2MSFTNGP10.phx.gbl...
> Partition views can help here. You will be able to keep table sizes down
> and this can increase query times when done right. It sounds like you
moved
> multiple tables into one table. If this is the case you will need to
break
> them back out into smaller tables, add partitioning columns, modify
Primary
> Keys and build the view. That might be a hassle. I can query a 20million
> record table and get a response back in under a second, I just did a query
> on 250million and elasped time was 10sec(only one index on there, I know I
> could get quicker:)). Did you change indexes for reporting or not?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> > I am sorry, I meant PARTITIONED VIEWS
> >
> > Sh
> >
> > "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> > news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > > Indexed views are only really good for aggregations and joining data
> from
> > > mutiple tables, is this what you need to do? Several of our tables
> are
> > > over a million rows in our production environment and it all comes
down
> to
> > > indexing. Did you create new indexes designed for queries or are they
> the
> > > existing indexes from the original tables?
> > >
> > >
> > > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > > SQL SERVER 2K
> > > >
> > > > We have 30 databases each for one business units.
> > > > Now I am consolidating few tables from 30 databases to one database
> for
> > > > Analysis purpose.
> > > >
> > > > Now my tables in Central db have millions of records.
> > > > I need some tips to handle this huge tables for query purpose
(already
> > > > applied with all Indexes).
> > > >
> > > > Can I go with INDEXED VIEWS '
> > > >
> > > > Thx
> > > > Sh
> > > >
> > > >
> > >
> > >
> >
> >
>

Catching a General Exception

I am trying to write a query that I only want to run on sql server 2005 databases. If a server isn't 2005, it will throw an exception. I would like to catch this general exception. Here is the query...

DECLARE @.Server [nchar] (100)
SET @.Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))

INSERT INTO [tempdb].[dbo].[User_Auditing] (Server, UserName, WinAuth, SQL_Auth_UserName, PassPolicyOn)
SELECT @.Server, s.name, isntuser, q.name, is_policy_checked
FROM sys.syslogins s FULL OUTER JOIN sys.sql_logins q
ON (s.name = q.name)

The errors I would get are as follows...

Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.syslogins'.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.sql_logins'.

I know in Java, I would just put a try before the declare and a catch("Invalid object name") after the statement, however, I'm not sure if this is even possible in T-SQL. Thanks for any help.
-Kyle

Nope. You cannot. 2005 introduces the concept of try...catch, but it wouldn't catch this error.

You could do something like

if @.@.version like 'Microsoft SQL Server 2005%'

exec ('select ''this is 2005''')

else

exec ('select ''this is NOT 2005''')

I used @.@.version since it will work on any version of SQL Server. Using serverproperty() is another possibilty.

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
Gav
The collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
GavThe collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>