Finding jobs that wont run due to Daylight Savings Time?

Since this weekend is Daylight Savings, we want to make sure of any
jobs that won't run due to the "missing hour".

I have written a small query against sysjobschedules that tells me
which ones have a schedule that runs between 2 and 3. However, it
doesn't include any jobs that run every X minutes/hours, and so might
be run during that time. (i.e. a job that starts at midnight and runs
until 6, running every 2 hours)

Is there any (easy) way to determine this? I might be able to build a
function that uses the other fields in sysjobschedules to give a list
of times that the job will run, but I haven't gotten to that point yet.
Figured someone might have something already, rather than reinvent the
wheel.

Thanks,
MichaelI don't believe there's any easy way, but sysjobhistory might be a good
starting point - if a job ran between 2 and 3 in the past, it will
probably do so again. You could join on sysjobschedules to narrow it
down to daily schedules, schedules on a certain weekday or date etc.
And you can check the next_run_date column as well, of course.

Simon

Finding End of Month

Does anyone have a VB function they could pass along for
converting a date to the end of the last month?
I have a report that will be run for the period ending the prior
month and want the parameter date to default as follows:
Assum they are running it on 12/21, date should default to 11/30/2005
Thanks!"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
@.TK2MSFTNGP15.phx.gbl:
> Does anyone have a VB function they could pass along for
> converting a date to the end of the last month?
> I have a report that will be run for the period ending the prior
> month and want the parameter date to default as follows:
> Assum they are running it on 12/21, date should default to 11/30/2005
> Thanks!
>
>
DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Outstanding, many thanks!
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Asher
I am using timedate format; can it be set to the end of the month 11:59 pm?
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||If your using 2005, you use my misc date project at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
hour:minute:second:millisecond of that day (i.e. adding one more ms would
move the date to start of next day.)
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Asher
> I am using timedate format; can it be set to the end of the month 11:59
> pm?
> "Asher_N" <compguy666@.hotmail.com> wrote in message
> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>|||William
Thanks for your reply but I an still on RS 2000
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> If your using 2005, you use my misc date project at
> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
> hour:minute:second:millisecond of that day (i.e. adding one more ms would
> move the date to start of next day.)
> --
> William Stacey [MVP]
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>|||I missed the VB function need. Here is a c# method you can convert:
public static DateTime GetEndOfMonth(DateTime date)
{
int daysInMonth = DateTime.DaysInMonth(date.Year, date.Month);
return new DateTime(date.Year, date.Month, daysInMonth, 23, 59,
59, 999);
}
private void button9_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("12/22/2005"); // Any date.
DateTime lastMth = date.AddMonths(-1);
DateTime endOfLast = GetEndOfMonth(lastMth);
Console.WriteLine("End of Last Month:" + endOfLast);
}
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:unxvFipBGHA.3496@.TK2MSFTNGP11.phx.gbl...
> William
> Thanks for your reply but I an still on RS 2000
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
>> If your using 2005, you use my misc date project at
>> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
>> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
>> hour:minute:second:millisecond of that day (i.e. adding one more ms would
>> move the date to start of next day.)
>> --
>> William Stacey [MVP]
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>>
>

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRD
Check the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database...m-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>
sql

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
[url]http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html[
/url]
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:
>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding Difference In Strings

Hey everyone, I am hoping someone can help me with a command that I am struggling to find. What I want to do is run a T-SQL command where it will return only the difference between 2 strings.

For example: If i have "This is a test" and "This is a test which will be good", then i want the command to return "which will be good" I believed there had to be this sort of a command, but i could not find anything close to it. Any ideas on how i would accomplish this?

Quote:

Originally Posted by willwmagic

Hey everyone, I am hoping someone can help me with a command that I am struggling to find. What I want to do is run a T-SQL command where it will return only the difference between 2 strings.

For example: If i have "This is a test" and "This is a test which will be good", then i want the command to return "which will be good" I believed there had to be this sort of a command, but i could not find anything close to it. Any ideas on how i would accomplish this?

You may need a stored procedure to do this. CHARINDEX and SUBSTRING functions may be useful|||what do expect when the strings are

"This is a test" and "wow,This is a test" ?|||

Quote:

Originally Posted by debasisdas

what do expect when the strings are

"This is a test" and "wow,This is a test" ?

I am not so concerned with what is before the string match, but more so with what is after the matched string.

Finding database on virtual servers

C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce

>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>
This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:

> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> going to move my
> Now when my web
> will they find which
> build a connection
> here. I be grateful
>
|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce

>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient[vbcol=seagreen]
>use of my hardware.
>"Bruce de Freitas" wrote:
concurrently[vbcol=seagreen]
active[vbcol=seagreen]
call[vbcol=seagreen]
your[vbcol=seagreen]
instance.[vbcol=seagreen]
to[vbcol=seagreen]
look
>.
>
|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>
|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>

Finding database on virtual servers

C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce

>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>This is an active/active cluster. All instances of sql need to be running a
t
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plu
s
a couple on control databases. I don't want to have the control databases i
n
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:

> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> going to move my
> Now when my web
> will they find which
> build a connection
> here. I be grateful
>|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce

>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient
>use of my hardware.
>"Bruce de Freitas" wrote:
>
concurrently[vbcol=seagreen]
active[vbcol=seagreen]
call[vbcol=seagreen]
your[vbcol=seagreen]
instance.[vbcol=seagreen]
to[vbcol=seagreen]
look[vbcol=seagreen]
>.
>|||You don't understand my question. The fail-over I'm not worried about and w
e
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>sql

Finding changes

Hoping one of you experts can help me - im writing an application and need to run a query that returns only the difference between 2 rows...

Example:

----------------
SELECT *
FROM summary, inventory
WHERE summary.fkInventory = inventory.ID
AND inventory.ComputerName = '[SOME_NAME_HERE]'
----------------

Now - this will return 2 rows with all columns from my tables. I need to know which columns that are different - and return only those to my application.

I am a bit new to SQL ( getting there fast ) but i have no clue how to do this - or if it can be done. Otherwise i will have to compare the data in my application but i'd much prefer to let the database do the work if possible...see
http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

It has code for logging changes to a column that has changed in a trigger.

You will have to loop through each column checking the values.|||Do you have exactly two rows (summeries) per inventory (computer)?

Do you realize that your query if created as you required returns everytime different columns? You can beter consider to let the query return your columnname, the original value and the changed value.

First you have to specify your different values:

SELECT 'YourFirstColumnName' AS ColName,
cast(O.YourFirstColumnName AS VARCHAR(255)) AS OriginalValue,
cast(N.YourFirstColumnName AS VARCHAR(255)) AS NewValue,
fkInventory
FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
FROM Summary S
GROUP BY S.fkInventory ) OldNew
INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
WHERE O.YourFirstColumnName <> N.YourFirstColumnName

UNION

SELECT 'YourSecondColumnName' AS ColName,
cast(O.YourSecondColumnName AS VARCHAR(255)) AS OriginalValue, cast(N.YourSecondColumnName AS VARCHAR(255)) AS NewValue,
fkInventory
FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
FROM Summary S
GROUP BY S.fkInventory ) OldNew
INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
WHERE O.YourSecondColumnName <> N.YourSecondColumnName

UNION

....

You got the idea to repeat the query for every column you want to compare? It's not very elegant, but under the assumptions above, you get what you want.

Then, you may use this query Q in your query like:

SELECT ColName, OriginalValue, NewValue
FROM Q
WHERE Q.fkInventory = (SELECT ID
FROM inventory WHERE ComputerName = '[SOME_NAME_HERE]')

Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>

Find startup parameters using QA

I have some startup parameters that i have added using the EM->
Properties->General->Startup Parameters
Is there a way i can run a query and see them using QA instead of using the
GUI . Using SQL 2000Hi Hassan,
Those parameters are stored in the registry, not in SQL Server itself. The
key they are stored is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters.
Replace the second MSSQLServer with the instance name if you use an
instance.
You can get these values with the undocumented extended procedure
xp_instance_regenumvalues :
EXEC xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OHb1eWmxDHA.1616@.TK2MSFTNGP11.phx.gbl...
> I have some startup parameters that i have added using the EM->
> Properties->General->Startup Parameters
> Is there a way i can run a query and see them using QA instead of using
the
> GUI . Using SQL 2000
>

Find database access by tcp connection

Ok, lets see if I can put what I am looking for into words.
I run a MSSQL server, I show in my logs that a connection is being made to my server from a remote ip. I have contact the owner of the ip in question and they state that they have no buisness with us and they should not be connected to us. According to netstat the connection to us is established on 2 different remote ports to 1433. My question, is there any logging on sql that I can turn on, look at to see what database this connection is accessing? ThanksHow about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?|||Originally posted by Brett Kaiser
How about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?

They must have a userid and password to connect yes, I am trying to find out who it is. None of our clients that I know of are connecting from this location. I'm not familiar with the sql profiler. Where might this be.

Thanks|||It comes with the sql server client tools...

And this guy gave a presentation on it...

http://weblogs.sqlteam.com/billg/archive/2003/11/08/460.aspx|||Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?|||Originally posted by MCrowley
Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?

Is this a setup?

http://weblogs.sqlteam.com/brettk/archive/2003/10/02/223.aspx

find all users in a sql server group

Hi There,
is it possible find all users in a sql server group, I'm not administrator,
I can use query analyser to run scripts that's it.
Thanks
GaneshUse sp_helpgroup system procedure, for instance:
EXEC sp_helpgroup 'db_owner'
Anith

Financial functions for SQL Server

Hi everybody,

Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?

Thanks in advance

Jaime

Here might be a good place to start?
http://www.google.se/search?hl=sv&q=future+value%2C+interest+rate%2C+payments+function+%2BSQL+Server&meta=

/Kenneth

|||

No so good...

I have found some commercial libraries but none (free) that lists all financial functions. At least, I have found future value implementation. That's why I asked if someone has already a list of functions ready to use.

Jaime