Reposition Subtotals: servers

Showing posts with label servers. Show all posts

Wednesday, March 28, 2012

Resilience Options

Hi,
I have two new servers with a view to consolidating all our databases on one
server. We intend to use the other server for resilience. We do not have
enterprise licenses so Log Shipping is not an option
What would be the best practice - using replication (what method etc.) or
backup and restore
SActually log shipping is an option... You would simply have to write some
stored procedures to do the work yourself... Since replication does NOT
replicate system tables, in my opinion it is not the best option for a warm
stand-by...There were some stored procedures included in the Resource Kit
for SQL 7 you might try to find and enhance, or maybe you could find
something posted on one of the web sites to get you started... But this is
definitely something you could do ...
"BigSi" <webmaster@.shine.net> wrote in message
news:O#Q1O4gIEHA.308@.tk2msftngp13.phx.gbl...
> Hi,
> I have two new servers with a view to consolidating all our databases on
one
> server. We intend to use the other server for resilience. We do not have
> enterprise licenses so Log Shipping is not an option
> What would be the best practice - using replication (what method etc.) or
> backup and restore
> S
>|||Thanks for that Wayne,
I am aware of the facts that you can develop your own log shipping, my only
concern is that it is not support by MS
S
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:ujbctXhIEHA.1220@.tk2msftngp13.phx.gbl...
> Actually log shipping is an option... You would simply have to write some
> stored procedures to do the work yourself... Since replication does NOT
> replicate system tables, in my opinion it is not the best option for a
warm
> stand-by...There were some stored procedures included in the Resource Kit
> for SQL 7 you might try to find and enhance, or maybe you could find
> something posted on one of the web sites to get you started... But this
is
> definitely something you could do ...
> "BigSi" <webmaster@.shine.net> wrote in message
> news:O#Q1O4gIEHA.308@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I have two new servers with a view to consolidating all our databases on
> one
> > server. We intend to use the other server for resilience. We do not have
> > enterprise licenses so Log Shipping is not an option
> >
> > What would be the best practice - using replication (what method etc.)
or
> > backup and restore
> >
> > S
> >
> >
>|||Yeah that's true...
"BigSi" <webmaster@.shine.net> wrote in message
news:etJQzlhIEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Thanks for that Wayne,
> I am aware of the facts that you can develop your own log shipping, my
only
> concern is that it is not support by MS
> S
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:ujbctXhIEHA.1220@.tk2msftngp13.phx.gbl...
> > Actually log shipping is an option... You would simply have to write
some
> > stored procedures to do the work yourself... Since replication does NOT
> > replicate system tables, in my opinion it is not the best option for a
> warm
> > stand-by...There were some stored procedures included in the Resource
Kit
> > for SQL 7 you might try to find and enhance, or maybe you could find
> > something posted on one of the web sites to get you started... But this
> is
> > definitely something you could do ...
> >
> > "BigSi" <webmaster@.shine.net> wrote in message
> > news:O#Q1O4gIEHA.308@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > I have two new servers with a view to consolidating all our databases
on
> > one
> > > server. We intend to use the other server for resilience. We do not
have
> > > enterprise licenses so Log Shipping is not an option
> > >
> > > What would be the best practice - using replication (what method etc.)
> or
> > > backup and restore
> > >
> > > S
> > >
> > >
> >
> >
>|||Maybe someone from MS can answer that for me''?/
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23u%2370ajIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> Yeah that's true...
> "BigSi" <webmaster@.shine.net> wrote in message
> news:etJQzlhIEHA.2908@.TK2MSFTNGP09.phx.gbl...
> > Thanks for that Wayne,
> >
> > I am aware of the facts that you can develop your own log shipping, my
> only
> > concern is that it is not support by MS
> >
> > S
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:ujbctXhIEHA.1220@.tk2msftngp13.phx.gbl...
> > > Actually log shipping is an option... You would simply have to write
> some
> > > stored procedures to do the work yourself... Since replication does
NOT
> > > replicate system tables, in my opinion it is not the best option for a
> > warm
> > > stand-by...There were some stored procedures included in the Resource
> Kit
> > > for SQL 7 you might try to find and enhance, or maybe you could find
> > > something posted on one of the web sites to get you started... But
this
> > is
> > > definitely something you could do ...
> > >
> > > "BigSi" <webmaster@.shine.net> wrote in message
> > > news:O#Q1O4gIEHA.308@.tk2msftngp13.phx.gbl...
> > > > Hi,
> > > >
> > > > I have two new servers with a view to consolidating all our
databases
> on
> > > one
> > > > server. We intend to use the other server for resilience. We do not
> have
> > > > enterprise licenses so Log Shipping is not an option
> > > >
> > > > What would be the best practice - using replication (what method
etc.)
> > or
> > > > backup and restore
> > > >
> > > > S
> > > >
> > > >
> > >
> > >
> >
> >
>

Tuesday, March 20, 2012

Requirements to set up on a new server?

I wanted to test drive the reporting services against Crystal.
I have a new stage server system where all servers were just promoted to
Win2003, and SQL 2000 on the data server box.
I understand that I need to put the reporting services on my IIS box. That
has .NET 2003 run time and SP applied.
When I attempt to install Reporting Services I get a lame error back about
lacking resources. This is the Reporting 2000 version. What am I missing?
TIA
__StephenWhat is the error? Please copy in the exact error message, and it's easier
to help. :) What resources are missing?
Have you installed Internet Information Services on your RS box? WIthout IIS
you won't get far with RS.
Kaisa M. Lindahl
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:OddBAscHGHA.1132@.TK2MSFTNGP10.phx.gbl...
>I wanted to test drive the reporting services against Crystal.
> I have a new stage server system where all servers were just promoted to
> Win2003, and SQL 2000 on the data server box.
> I understand that I need to put the reporting services on my IIS box.
> That has .NET 2003 run time and SP applied.
> When I attempt to install Reporting Services I get a lame error back about
> lacking resources. This is the Reporting 2000 version. What am I
> missing?
> TIA
> __Stephen
>

Saturday, February 25, 2012

Repost: Polling the network for SQL servers, including named instances

Hi group,
I am trying to get a list of SQL Server installations running on the
network. I tried to use sqlcmd -L (or osql -L). However, when I do this
from my box the named instances do not show up, whereas when it is run from
a server box those instances do get listed.
What is required to make the command return the full list? Is there other
(simple) way to do it?
Thanks.
QuentinHi Quentin
Check out SQLPing or SQLRecon at
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
John
"Quentin Ran" wrote:
> Hi group,
> I am trying to get a list of SQL Server installations running on the
> network. I tried to use sqlcmd -L (or osql -L). However, when I do this
> from my box the named instances do not show up, whereas when it is run from
> a server box those instances do get listed.
> What is required to make the command return the full list? Is there other
> (simple) way to do it?
> Thanks.
> Quentin
>
>

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it :)
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it :)
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

REPOST: optimizations job for db maintenance plan failed

Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it

> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into DB2 on the case sensetive server and make DB2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into DB2 on the case sensetive server and make DB2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway

At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway

That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
Quentin
Quentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>
|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into db2 on the case sensetive server and make db2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>
|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into db2 on the case sensetive server and make db2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway

At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!
|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway

Repost: Databases with different collation

Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinYes, use unicode.
The problem is you can only have one collation per
database table, so combining two will cause a problem. The
answer is to use unicode ie ncar, nvarhar, ntext which is
collation independant.
J
>--Original Message--
>Hi group,
>we have databases now residing on different servers or
instances with
>different collations. We are considering bringing the
databases on the same
>server, keeping their respective collation.
>Some of our applications access databases with different
collations. We
>have been using linked server and specifying "Use remote
collation" (or
>not). But when putting the DBs with different collation
on the same server,
>you loss this feature. To go over all the applications
and specify the
>collation on the query statement level is of course too
big a task. Is
>there a way to do such a blanket declaration on the
database level?
>Or any other way of doing this efficiently?
>Your responses are greatly appreciated.
>Quentin
>
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> Yes, use unicode.
> The problem is you can only have one collation per
> database table, so combining two will cause a problem. The
> answer is to use unicode ie ncar, nvarhar, ntext which is
> collation independant.
Julie,
Are you suggesting that unicode columns ignore collation settings? That
is not true:
create table #un1 (a nvarchar(20) collate sql_latin1_general_cp1_ci_as)
create table #un2 (a nvarchar(20) collate sql_latin1_general_cp1_cs_as)
select * from #un1
union
select * from #un2
-- Server: Msg 446, Level 16, State 9, Line 5
-- Cannot resolve collation conflict for UNION operation.
If that wasn't what you meant, please explain in more detail.|||Sorry thats not what I meant. from the description two
different databases are being combined, the point I was
trying (unsuccessfully obviously) was that adding the data
from collation table x to table with collation y will
cause problems, so the best way is to convert them first
to unicode, then combine them.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
>> Yes, use unicode.
>> The problem is you can only have one collation per
>> database table, so combining two will cause a problem.
The
>> answer is to use unicode ie ncar, nvarhar, ntext which
is
>> collation independant.
> Julie,
> Are you suggesting that unicode columns ignore
collation settings? That
>is not true:
>
>create table #un1 (a nvarchar(20) collate
sql_latin1_general_cp1_ci_as)
>create table #un2 (a nvarchar(20) collate
sql_latin1_general_cp1_cs_as)
>select * from #un1
>union
>select * from #un2
>-- Server: Msg 446, Level 16, State 9, Line 5
>-- Cannot resolve collation conflict for UNION operation.
>
> If that wasn't what you meant, please explain in more
detail.
>
>.
>|||Julie/Adam,
Thanks for the responses.
Using unicode (or whatever casting) does not solve the problem. The problem
is that there are existing applications that use the linked server where
with the "use remote collation", you have the whole database covered, but
with any casting, you have only the column covered. You will have to go
over the entire code to do the change. What I am trying to find is rather a
solution that do such a blanket change so you do not need to worry where a
change is missed -- let alone to do all the changes.
Again thanks for the discussion. Any further comments are well come.
Quentin
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2bdab01c4682d$cf07d2f0$a301280a@.phx.gbl...
> Sorry thats not what I meant. from the description two
> different databases are being combined, the point I was
> trying (unsuccessfully obviously) was that adding the data
> from collation table x to table with collation y will
> cause problems, so the best way is to convert them first
> to unicode, then combine them.
> J
> >--Original Message--
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> >> Yes, use unicode.
> >>
> >> The problem is you can only have one collation per
> >> database table, so combining two will cause a problem.
> The
> >> answer is to use unicode ie ncar, nvarhar, ntext which
> is
> >> collation independant.
> >
> > Julie,
> >
> > Are you suggesting that unicode columns ignore
> collation settings? That
> >is not true:
> >
> >
> >create table #un1 (a nvarchar(20) collate
> sql_latin1_general_cp1_ci_as)
> >
> >create table #un2 (a nvarchar(20) collate
> sql_latin1_general_cp1_cs_as)
> >
> >select * from #un1
> >union
> >select * from #un2
> >
> >-- Server: Msg 446, Level 16, State 9, Line 5
> >-- Cannot resolve collation conflict for UNION operation.
> >
> >
> > If that wasn't what you meant, please explain in more
> detail.
> >
> >
> >.
> >

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into db2 on the case sensetive server and make db2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > Let me try this one last time.
> >
> > Hi group,
> >
> > we have databases now residing on different servers or instances with
> > different collations. We are considering bringing the databases on the
> same
> > server, keeping their respective collation.
> >
> > Some of our applications access databases with different collations. We
> > have been using linked server and specifying "Use remote collation" (or
> > not). But when putting the DBs with different collation on the same
> server,
> > you loss this feature. To go over all the applications and specify the
> > collation on the query statement level is of course too big a task. Is
> > there a way to do such a blanket declaration on the database level?
> > Or any other way of doing this efficiently?
> >
> > Your responses are greatly appreciated.
> >
> > Quentin
> >
> >
> >
> >
> >
> >
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into db2 on the case sensetive server and make db2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway :)
At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway :)
That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

REPOST: New SQL Server Registration failure

Hi,

I have 3 SQL Servers running here. Here are their configurations:

SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3

Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.

If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.

Any ideas?

Thanks,

Skip.Hi Skippy,

I'm assuming you're running MSDE or SQL Server PE on the XP Pro machine. Is it possible that you have used the access licenses or are you disconnecting from the 1st server before you try to attach wtih the second?

Hope this helps.

John

Originally posted by Skippy_sc
Hi,

I have 3 SQL Servers running here. Here are their configurations:

SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3

Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.

If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.

Any ideas?

Thanks,

Skip.

Wednesday, March 28, 2012

Resilience Options

Resilience Options

Resilience Options

Tuesday, March 20, 2012

Requirements to set up on a new server?

Saturday, February 25, 2012

Repost: Polling the network for SQL servers, including named instances

REPOST: optimizations job for db maintenance plan failed

REPOST: optimizations job for db maintenance plan failed

Repost: Databases with different collation

Repost: Databases with different collation

Repost: Databases with different collation

Repost: Databases with different collation

REPOST: New SQL Server Registration failure

Reposition Subtotals

Blog Archive

About Me