detecting whether all items are selected in a multi-value paramete

Is there an easy way to detect whether the user has selected all possible
items in a multi-value parameter? If they've selected everything I want to
put "All Regions" as a label in the header of the report. If they've selected
only some, I want to list the regions they've selected [accomplished by doing
Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
header, I can't just compare Parameters!Region.Label.Count to
Count(Fields!Region.Value,"RegionsDataset") because you can't use fields in
the header. (The workaround of adding a hidden field to the body of the
report then referencing it by ReportItems!HiddenField.Value is just too
kludgy for me... plus the hidden field's gotta repeat on every page.)
If anyone has any good suggestions, I'd be interested in hearing them.
The solution I decided upon was builiding a hidden parameter which is
dependent upon the multi-value parameter... it's query figures out if they've
selected all possible options. But it seems silly to have to fire off another
query to the DB to accomplish this.If you know the count of parameters you can use that:
This is something I do in a textbox where I show the parameters selected:
="Containers= " & iif(Parameters!Container.Count > 8," more than 8
Selected",Join(Parameters!Container.Value, ", "))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> Is there an easy way to detect whether the user has selected all possible
> items in a multi-value parameter? If they've selected everything I want to
> put "All Regions" as a label in the header of the report. If they've
> selected
> only some, I want to list the regions they've selected [accomplished by
> doing
> Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> header, I can't just compare Parameters!Region.Label.Count to
> Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> in
> the header. (The workaround of adding a hidden field to the body of the
> report then referencing it by ReportItems!HiddenField.Value is just too
> kludgy for me... plus the hidden field's gotta repeat on every page.)
> If anyone has any good suggestions, I'd be interested in hearing them.
> The solution I decided upon was builiding a hidden parameter which is
> dependent upon the multi-value parameter... it's query figures out if
> they've
> selected all possible options. But it seems silly to have to fire off
> another
> query to the DB to accomplish this.|||Bruce-
That would work if the list of items in the multi-value parameter was a
constant set. But the list can change over time, so I can't hard code it to a
specific count. Any other suggestions? Thanks for the reply, though.
"Bruce L-C [MVP]" wrote:
> If you know the count of parameters you can use that:
> This is something I do in a textbox where I show the parameters selected:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
> Selected",Join(Parameters!Container.Value, ", "))
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> > Is there an easy way to detect whether the user has selected all possible
> > items in a multi-value parameter? If they've selected everything I want to
> > put "All Regions" as a label in the header of the report. If they've
> > selected
> > only some, I want to list the regions they've selected [accomplished by
> > doing
> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> > header, I can't just compare Parameters!Region.Label.Count to
> > Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> > in
> > the header. (The workaround of adding a hidden field to the body of the
> > report then referencing it by ReportItems!HiddenField.Value is just too
> > kludgy for me... plus the hidden field's gotta repeat on every page.)
> >
> > If anyone has any good suggestions, I'd be interested in hearing them.
> >
> > The solution I decided upon was builiding a hidden parameter which is
> > dependent upon the multi-value parameter... it's query figures out if
> > they've
> > selected all possible options. But it seems silly to have to fire off
> > another
> > query to the DB to accomplish this.
>
>|||If your list is based on a query you could have a query that returns a count
and then you can use that count to compare to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:EAFC9B4C-3371-4159-8917-4CAA8595FC7E@.microsoft.com...
> Bruce-
> That would work if the list of items in the multi-value parameter was a
> constant set. But the list can change over time, so I can't hard code it
> to a
> specific count. Any other suggestions? Thanks for the reply, though.
> "Bruce L-C [MVP]" wrote:
>> If you know the count of parameters you can use that:
>> This is something I do in a textbox where I show the parameters selected:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
>> Selected",Join(Parameters!Container.Value, ", "))
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
>> > Is there an easy way to detect whether the user has selected all
>> > possible
>> > items in a multi-value parameter? If they've selected everything I want
>> > to
>> > put "All Regions" as a label in the header of the report. If they've
>> > selected
>> > only some, I want to list the regions they've selected [accomplished by
>> > doing
>> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in
>> > the
>> > header, I can't just compare Parameters!Region.Label.Count to
>> > Count(Fields!Region.Value,"RegionsDataset") because you can't use
>> > fields
>> > in
>> > the header. (The workaround of adding a hidden field to the body of the
>> > report then referencing it by ReportItems!HiddenField.Value is just too
>> > kludgy for me... plus the hidden field's gotta repeat on every page.)
>> >
>> > If anyone has any good suggestions, I'd be interested in hearing them.
>> >
>> > The solution I decided upon was builiding a hidden parameter which is
>> > dependent upon the multi-value parameter... it's query figures out if
>> > they've
>> > selected all possible options. But it seems silly to have to fire off
>> > another
>> > query to the DB to accomplish this.
>>

Detecting when an user connects to a database

Greetings,
Is there any way to detect when a user connects to my database?
I mean, I want to know when a user connects to my database and when he/she
disconnects, and do something when this happens (a trigger, I guess).
Any ideas'
Thanks!Hi
If the user is only connecting to your database through a bespoke
application, then you will have control of this. If you don't have this
option you may want to check out the C2 auditing abilities and processing
the audit file, using SQL profiler or poling the sysprocesses table.
John
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

---

|||HI,
Simple option is enable Audit level (ALL)
How to enable audit,
1. In Enterprise manager , select the server name and right click and select
properties
2. Select the security tab and select Audit level as "ALL"
3. Restart SQL server Service
From now all the successful Login and Failure attempts will stored in SQL
server errorlog, but we can not set a trigger for this.
I belive to set a trigger we may need write our own custom trigger in
sysprocesses table.
Thanks
Hari
MCDBA
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

---

Detecting processing in progress

Is there an easy way through AMO to detect whether a cube (or database, or dimension...) is currently being processed?

I've tried registering for trace events, but that doesn't seem to be a very reliable way to detect an in-progress process - the gap between events are unknown, so I don't know how long I would need to listen for an event to be able to say definitively that something is being processed. Any other ideas?

I'm not aware of anyway of doing this. You can use performance counters to see if *something* is processing, but you can't see which object. You can use AMO to see when something was last processed, or if it is unprocessed and needs re-processing. But I can't see anything that would tell you if anything is currently being processed. Tracing seems to be one of the only ways of doing this, but you would almost need to write a service that could watch for the start, stop or cancelling of processing jobs in order to be able to figure this out reliably.

To look at this from another angle - why do you need to know? If something is already being processed, I would expect you should get a meaningful error stating this if you tried to process it again. And in most cases, end users should not care that something is being processed, they will just see the new data once the processing transaction has been committed.

|||Darren,
Knowing that *something* is processing is actually good enough for our needs right now. Perfmon counters are a great suggestion - I didn't think to look there. Processing Pool Busy threads seems accurate enough for our needs. I wish there were a counter for "Active Transaction", which would be even more useful for us, but it doesn't look like there is.

Our needs are somewhat unusual - we've actually built a product on SSAS, and our QA department needs to know when processing is in progress to avoid stepping on each other's toes while testing. I think you've given me what I need for now. Thanks!
|||

Well if you use a certain userid (ESP, SQLSERVER, or make a unique one) for processing, you could use the XMLA Discover command for DISCOVER_SESSIONS. If the userid is returned, something is processing.

We do something similiar. All jobs that process any cube use a specific userid.

|||

Yeah, DISCOVER_SESSIONS would be useful, there is also DISCOVER_TRANSACTIONS that might be worth a look.

<Discover xmlns="urnchemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_TRANSACTIONS</RequestType>

<Restrictions>

<RestrictionList></RestrictionList>

</Restrictions>

<Properties>

<PropertyList></PropertyList>

</Properties>

</Discover>

|||Thanks fellas. I think between DISCOVER_TRANSACTIONS and DISCOVER_SESSIONS I've got the info I need.

Detecting or monitoring for long-running queries.

I can't seem to find a step-by-step guide to how to detect or
continuously monitor for long-running queries that is suitable
for a comparative SQL Server novice. I know that it is possible
to monitor for such with other database products - can anyone
enlighten me as to how this is done with SQL Server? Ideally,
I'd like to snapshot running queries (preferably with query
text) at a particular instant via a script, although any help
to show what queries are running at an instant will be much
appreciated. Any ideas?

__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com

100s of FREE email addresses -->
http://www.UltimateEmail.com

Send an Online Greeting Card http://www.UltimateEcards.comsqlserver yeahbaby (Use-Author-Address-Header@.[127.1]) writes:
> I can't seem to find a step-by-step guide to how to detect or
> continuously monitor for long-running queries that is suitable
> for a comparative SQL Server novice. I know that it is possible
> to monitor for such with other database products - can anyone
> enlighten me as to how this is done with SQL Server? Ideally,
> I'd like to snapshot running queries (preferably with query
> text) at a particular instant via a script, although any help
> to show what queries are running at an instant will be much
> appreciated. Any ideas?

On my web site there is a stored procedure, aba_lockinfo, that is not
a perfect match for your request, but which does give the information
you are asking for - and maybe information which you do not care
about. You could look at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html, and see if
it anything for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Detecting an existence of local temporary table

Hi,
I create a temporary table. for example:
create table #t1 (ID_T1 integer)
Next I would like to detect If the table already exists:
exec sp_table #t1
if @.@.ROWCOUNT > 0
--Table #t1 exists
else
--Table #t1 doesn't exist
But the sp_table stored proc.works only for non-temporary tables and
the result using temporary tables is allways @.@.ROWCOUNT = 0.
Does anyone know, if there exists a solution to detect an existing
temporary table ?
Thanks a lot for any suggestions
LiborIf exists(Select name from sysobjects where name='#t1')
--Table #t1 exists
else
--Table #t1 doesn't exist
Madhivanan|||I'd prefer to use
IF object_id('TableName')IS NOT NULL
PRINT 'Yes'
ELSE
PRINT 'No'
<madhivanan2001@.gmail.com> wrote in message
news:1109142292.789540.177840@.o13g2000cwo.googlegroups.com...
> If exists(Select name from sysobjects where name='#t1')
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
>
> Madhivanan
>|||Temp tables are special. You can't just check against sysobjects, and you ca
n't just check the
object id locally (as they live in tempdb). Try below:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
PRINT 'exists'
ELSE
PRINT 'not exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:738o11lltb576hp4tdf345qcibnh156sld@.
4ax.com...
> Hi,
> I create a temporary table. for example:
> create table #t1 (ID_T1 integer)
> Next I would like to detect If the table already exists:
> exec sp_table #t1
> if @.@.ROWCOUNT > 0
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
> But the sp_table stored proc.works only for non-temporary tables and
> the result using temporary tables is allways @.@.ROWCOUNT = 0.
> Does anyone know, if there exists a solution to detect an existing
> temporary table ?
> Thanks a lot for any suggestions
> Libor|||On 22 Feb 2005 23:04:52 -0800, madhivanan2001@.gmail.com wrote:

>If exists(Select name from sysobjects where name='#t1')
> --Table #t1 exists
>else
> --Table #t1 doesn't exist
>
>Madhivanan
Yes, the query should be exactlyt:
if exists(select * from tempdb..sysobjects where name like '#t1%')
.
.
but such query cannot be used for this purpose: while one session
creates a #t1 table and then other session performs such query
without creating is's own #t1 table, the result is allways more
than 0 rows.
Somewhere on server resides a relationship between a record of
tempdb..sysobject with name like '#t1%' and the user session. I tried
to recover such relationship, but (so far) I was unsuccessfull.
Libor|||On Wed, 23 Feb 2005 08:59:44 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Temp tables are special. You can't just check against sysobjects, and you c
an't just check the
>object id locally (as they live in tempdb). Try below:
>IF OBJECT_ID('tempdb..#t') IS NOT NULL
>PRINT 'exists'
>ELSE
>PRINT 'not exists'
This works very good. Thanks
Libor

detecting a dead connection

Hi all -
I need a way to detect a dead connection.
I realize there has been a problem with SQL_ATTR_CONNECTION_DEAD , so
I use
SQLGetConnectAttr with SQL_COPT_SS_CONNECTION_DEAD instead.
I still dont get the expected result.
(btw, SQL_COPT_SS_CONNECTION_DEAD is defined as
SQL_ATTR_CONNECTION_DEAD. What kind of a fix is that?)
Any help appreciated,
Gideon.
I'm curious what problem you've heard exists with SQL_ATTR_CONNECTION_DEAD?
Why do you need to test whether or not the connection is dead? Just try to
use it and have the correct error handling in place.
What happens if you successfully test that the connection is alive, and then
it dies a second later? You have to handle that error case anyway, so why
worry about testing to see if it's dead or not?
Brannon
"Gideon" <grashkes@.yahoo.com> wrote in message
news:fb0829e1.0409170355.70ffd16b@.posting.google.c om...
> Hi all -
> I need a way to detect a dead connection.
> I realize there has been a problem with SQL_ATTR_CONNECTION_DEAD , so
> I use
> SQLGetConnectAttr with SQL_COPT_SS_CONNECTION_DEAD instead.
> I still dont get the expected result.
> (btw, SQL_COPT_SS_CONNECTION_DEAD is defined as
> SQL_ATTR_CONNECTION_DEAD. What kind of a fix is that?)
> Any help appreciated,
> Gideon.
|||Hi Brannon, and thanks for you reply.
I've done some more research and here's what I came up with.
Lets focus for now solely on SQLGetConnectAttr.
I need to use it to find out whether the connection is alive or not
before I attempt to use it, as I want to refrain from using exception
handling in this case.
I've found out that on SQL Server 2000, the const
'SQL_COPT_SS_CONNECTION_DEAD' has a different value than
'SQL_ATTR_CONNECTION_DEAD' (1244 and 1209, respectively).
However, I am still experiencing some strange behaviour.
Using SQLGetConnectAttr with 'SQL_COPT_SS_CONNECTION_DEAD' on a dead
SQL Server connection (i.e. using a SQL Server 2000 driver) I can
detect that the connection is dead. After reconnecting however, the
function does not detect that the connection is running again, and
says that the connection is dead still.
As my system needs to work with Oracle and DB2 connections as well,
things get messier.
With DB2, there's no problem, other than I have to use the old const
'SQL_ATTR_CONNECTION_DEAD' to make it work.
With the Oracle driver, neither seem to work.
To sum up:
On SQL Server - I cannot detect that the connection is running again.
On DB2 - fine.
On Oracle - the function always says the connection alive.
Thanks for your help,
Gideon.
"Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...
> I'm curious what problem you've heard exists with SQL_ATTR_CONNECTION_DEAD?
> Why do you need to test whether or not the connection is dead? Just try to
> use it and have the correct error handling in place.
> What happens if you successfully test that the connection is alive, and then
> it dies a second later? You have to handle that error case anyway, so why
> worry about testing to see if it's dead or not?
> Brannon
>
|||Hmm, I think that the SQL driver must cache the state of the connection
after the first check. Sounds like a bug.
But I don't think you should use the SQL_ATTR_CONNECTION_DEAD attribute at
all (or SQL_COPT_SS_CONNECTION_DEAD). You need to have code that can handle
disconnect errors anyway, so why have a special check? Also, the bigger
problem is, what happens if you detect that the connection is alive, and
then try to use it, and during that brief period of time, the connection
dies? If you don't handle that case, then your app will experience random
weird behavior. However, if you correctly handle that case, then you don't
even NEED to detect if the connection is alive or not.
Brannon
"Gideon" <grashkes@.yahoo.com> wrote in message
news:fb0829e1.0409181058.74858989@.posting.google.c om...
> Hi Brannon, and thanks for you reply.
> I've done some more research and here's what I came up with.
> Lets focus for now solely on SQLGetConnectAttr.
> I need to use it to find out whether the connection is alive or not
> before I attempt to use it, as I want to refrain from using exception
> handling in this case.
> I've found out that on SQL Server 2000, the const
> 'SQL_COPT_SS_CONNECTION_DEAD' has a different value than
> 'SQL_ATTR_CONNECTION_DEAD' (1244 and 1209, respectively).
> However, I am still experiencing some strange behaviour.
> Using SQLGetConnectAttr with 'SQL_COPT_SS_CONNECTION_DEAD' on a dead
> SQL Server connection (i.e. using a SQL Server 2000 driver) I can
> detect that the connection is dead. After reconnecting however, the
> function does not detect that the connection is running again, and
> says that the connection is dead still.
> As my system needs to work with Oracle and DB2 connections as well,
> things get messier.
> With DB2, there's no problem, other than I have to use the old const
> 'SQL_ATTR_CONNECTION_DEAD' to make it work.
> With the Oracle driver, neither seem to work.
> To sum up:
> On SQL Server - I cannot detect that the connection is running again.
> On DB2 - fine.
> On Oracle - the function always says the connection alive.
> Thanks for your help,
> Gideon.
> "Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
SQL_ATTR_CONNECTION_DEAD?[vbcol=seagreen]
to[vbcol=seagreen]
then[vbcol=seagreen]
why[vbcol=seagreen]
|||Hi again.
What I initially wanted to do was this:
In my exception handling, find out if it was a disconnection error and if
so, try to reconnect and then deal with it.
The problem with that was, that the SQLStates for disconnection errors, are
different from one version of a driver to the other;
I dont want to have to maintain a list of SQLStates that may change when a
new version of one of the drivers I support comes out.
I have also found on a few occasions that the SQLStates are not %100
reliable. In some cases, it throws a general warning when a disconnection
has occurred, and that isnt specific enough.
So I needed an alternate way of detecting a disconnection.
Thanks again for your help,
Gideon.
"Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
news:OdNunD1nEHA.556@.tk2msftngp13.phx.gbl...
> Hmm, I think that the SQL driver must cache the state of the connection
> after the first check. Sounds like a bug.
> But I don't think you should use the SQL_ATTR_CONNECTION_DEAD attribute at
> all (or SQL_COPT_SS_CONNECTION_DEAD). You need to have code that can
> handle
> disconnect errors anyway, so why have a special check? Also, the bigger
> problem is, what happens if you detect that the connection is alive, and
> then try to use it, and during that brief period of time, the connection
> dies? If you don't handle that case, then your app will experience random
> weird behavior. However, if you correctly handle that case, then you
> don't
> even NEED to detect if the connection is alive or not.
> Brannon
> "Gideon" <grashkes@.yahoo.com> wrote in message
> news:fb0829e1.0409181058.74858989@.posting.google.c om...
> news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...
> SQL_ATTR_CONNECTION_DEAD?
> to
> then
> why
>
|||Ah .. I see the problem. It's unfortunate that SQL States can vary between
drivers. I don't have any good suggestions though.
Brannon
"Gideon Rashkes" <gideon@.theglcompanyMAPS_ON.com> wrote in message
news:e1dB1FJoEHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi again.
> What I initially wanted to do was this:
> In my exception handling, find out if it was a disconnection error and if
> so, try to reconnect and then deal with it.
> The problem with that was, that the SQLStates for disconnection errors,
are[vbcol=seagreen]
> different from one version of a driver to the other;
> I dont want to have to maintain a list of SQLStates that may change when a
> new version of one of the drivers I support comes out.
> I have also found on a few occasions that the SQLStates are not %100
> reliable. In some cases, it throws a general warning when a disconnection
> has occurred, and that isnt specific enough.
> So I needed an alternate way of detecting a disconnection.
> Thanks again for your help,
> Gideon.
>
> "Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
> news:OdNunD1nEHA.556@.tk2msftngp13.phx.gbl...
at[vbcol=seagreen]
random[vbcol=seagreen]
and[vbcol=seagreen]
so
>

Detecting (local) sql-server

Can someone please point me to some code (preferably C#, but C++ or C or VB
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!

-- Rob"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FADFA7C4F67defffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
> VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob

MSSQL is just another service, so you should look for code which shows how
to retrieve service states from C# - it looks as if there's a sample with
Visual Studio:

http://msdn.microsoft.com/library/d...scontroller.asp

If you have multiple instances, then see questions 12 and 13 here for how to
find the instance names:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in news:420dc8db$1_1@.news.bluewin.ch:

> MSSQL is just another service, so you should look for code which shows
> how to retrieve service states from C# - it looks as if there's a
> sample with Visual Studio:
> http://msdn.microsoft.com/library/d...ry/en-us/cssamp
> le/html/vcsamprocesscontroller.asp
> If you have multiple instances, then see questions 12 and 13 here for
> how to find the instance names:
> http://support.microsoft.com/defaul...257716&Product=
> sql2k
> Simon

Thank you, Simon! That's *EXACTLY* what I was looking for!

-- Rob

detecting & deleting duplicates in batch vs proc

I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster: my question is: How do I display those rows in a proc for detecting what the problem is. The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream. The proc creates okay but I'm stuck after that part.

thx

Kat -- very rough code below

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@.count int
as
set nocount on
select categoryID,
CategoryName,
Count(*) As Dups
from Categories
group by Categoryid, CategoryName
having count(*) >1

set @.count = @.@.Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@.count) + CategoryName

/*set @.count = @.@.RowCount
IF @.count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@.count)*/

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

|||

Hi Joey,

Thx for all your help... should I do this in a batch or in a proc?

Kind Regards,

Kat

|||

hi kat,

batch operation will do since this is just a one time operation.

you dont want to have so many SP's in your db.

anyway. Are you done with the delete statement?

I mean the delete query which will delete the duplicates?

joey

|||

Yes, thank you very much! I'm trying to find test situations and how to resolve them. This was one that I've seen happen before.

Kat

|||

joeydj wrote:

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

Never save deleted records. Transactions and error handling ensure wanted records are not deleted.

If you want to delete them, delete them and be done with it. It's going to save a lot of purging time later on.

...and PRINT is for Query Analyzer and Management Studio not for application display.

Adamus

|||

If you want to display them in an application form, use SELECT not PRINT.

Research "Views" in SQL Server.

Adamus

|||

hello adam,

lets help the girl.

how do we delete duplicates

and of course leave one of them in the table

lets say i have a table name tablex

tablex

id , lastname, first,

1, cruz, juan

1, cruz, juan carlos

2, penduko, pedro

2, penduko, peter

2, penduko, pedro

3, falaypay,pacifico

3, falaypay, pacifico

3, falapay, pacifica

4, khan, cynthia

4, luster, cynthia - got married

4, khan, agnes cynthia - the encoder missed the second name

In this example i have a very dirty employees table in the data warehouse and i have 8 million of them. and it happens in real life. hahaha. if this is a warehouse there should be a timestamp but sad to say the architect has failed to remember of placing one

how can i remove the duplicates and maintain the integrity. Ok never mind integrity how do we delete the duplicate first just using the id as qualifier

regards,

joey

|||I was just wondering if anyone had an answer to Joey's post. It appears that I would have to manually look at the data to resolve the issue, any ideas?|||

You can detect duplicates using query below:

select id

from tbl

group by id

having count(*) > 1

For the delete logic to work efficiently, you need some other key column(s) in the table to determine which row to keep. This can also be some columns that uniquely identifies a row within each group.

delete tbl

where timestamp < (select max(t1.timestamp) from tbl as t1 where t1.id = tbl.id)

go

For these type of tables in a data warehouse that doesn't have any constraints you can add an identity column and then do below:

alter table tbl add uqid int not null identity

go

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

go

If you have large number of rows to delete then you can perform the delete in batches like:

-- Works only on SQL Server 2005. Uses TOP clause support in DML

declare @.n int

set @.n = 1000

while(1=1)

begin

-- delete N rows at a time

delete top(@.n) tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

go

-- Older versions of SQL Server

declare @.n int

set @.n = 1000

set rowcount @.n

while(1=1)

begin

-- delete N rows at a time

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

set rowcount 0

go

If you cannot add identity column or have other columns that identifies a duplicate within each group you can use approach below. This will however be slower due to use of cursor.

declare @.id int, @.cnt int

declare @.dupes cursor

set @.dupes =

cursor fast_forward for

select id, count(*) - 1

from tbl

group by id

having count(*) > 1

open @.dupes

while(1=1)

begin

fetch @.dupes into @.id, @.cnt

if @.@.fetch_status < 0 break

/* Older versions of SQL Server

set rowcount @.cnt -- don't care which N-1 rows are deleted

delete tbl

where id = @.id

*/

/* SQL2005

delete top(@.cnt) tbl

where id = @.id

*/

end

/* Older versions of SQL Server

set rowcount 0

*/

go

However, this is a bad way to maintain a data warehouse. You need to build rules into the data loading and transformation process to track changes easily. You need to model your dimensions so that they can accomodate changes to attribute values. There are many standard technique available to do this.

|||

DECLARE c1 CURSOR FAST_FORWARD FOR

AS

DECLARE @.FirstName varchar(25)

Select firstname, count(firstname)

from Employees

Group by firstname

having count(firstname) > 1

open c1

fetch next from c1

into @.Firstname

While @.@.fetch_status = 0

BEGIN

insert into DeletedItems(firstname) Values(@.FirstName)

Delete from Employees

Where firstname = @.Firstname

END

fetch next from c1

into @.Firstname

CLOSE c1

DEALLOCATE c1

|||

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

|||

katgreen777 wrote:

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

CTE is the replacement in 2005 and I'm learning to code it, but yes cursors do carry a lot of overhead. Notice I use a "fast_forward" It makes a big difference.

My argument with cursors and millions of rows is, if you're dealing with millions of rows, there is no lightning fix other than hardware enhancements. You could argue until you're blue in the face with seconds, execution plans, alternative methods, and the list goes on, only to discover a well written cursor and ample thought will give you the result you need.

Adamus

|||

katgreen777 wrote:

Hi Adamus,I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

Please take a look at the code that I posted before. You don't need cursors unless your table(s) doesn't have any key information or unique identifiers or other required columns. To delete large number of rows efficiently, you can use a batching mechanism using SET ROWCOUNT or TOP. See the examples I posted. And CTEs as mentioned in the other post doesn't really help in this case. CTE is just a query expression similar to derived tables or views and provides some additional capabilities like writing recursive queries.

|||

Hi,

Looking at the 'dirty' warehouse table above, wouldn't we just use the id number to delete the rows because that is unique and the firstname isnt?

thx

sql

Detect whether runtime or executiontime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether runtime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether executiontime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?

|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether executiontime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect Stored Procedure that refer to tables that do not exist

It is possible to construct a query that will list all stored procedures
that refer to tables that no longer exist in the database?
Thanks,
ChrisHi
Execute each SP with "SET NOEXEC ON" set.
From BOL
"The execution of statements in SQL Server consists of two phases:
compilation and execution. This setting is useful for having SQL Server
validate the syntax and object names in Transact-SQL code when executing. It
is also useful for debugging statements that would usually be part of a
larger batch of statements"
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:eFzSEi3lFHA.3900@.TK2MSFTNGP09.phx.gbl...
> It is possible to construct a query that will list all stored procedures
> that refer to tables that no longer exist in the database?
>
> Thanks,
> Chris
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Execute each SP with "SET NOEXEC ON" set.
> From BOL
> "The execution of statements in SQL Server consists of two phases:
> compilation and execution. This setting is useful for having SQL
> Server validate the syntax and object names in Transact-SQL code when
> executing. It is also useful for debugging statements that would
> usually be part of a larger batch of statements"
> Regards
Mike,
NOEXEC doesn't seem to work for me. For some reason, it doesn't detect
the drop of the table in my example. However, when I tried using SET
FMTONLY ON, it worked and does not depend on any branching in the
procedure itself that would prevent access to the underlying missing
table.
create table abc123 (co1l int)
go
drop proc abc123test
go
create proc abc123test @.b bit
as
begin
if @.b = 0
Select co1l from abc123
else
Select id from sysobjects
end
go
exec abc123test 1
exec abc123test 0
drop table abc123
go
set fmtonly on
exec abc123test 1
exec abc123test 0
set fmtonly off
drop proc abc123test
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks,
Is it possible to perform the checking at the time the stored procedure is
created. The problem I have got is that it would take a long time to test a
thousand+ number of stored procedures using exec, supplying different
parameters to each one etc. So I thought it would be easier to generate a
script that attempts to drop and then recreate the stored procedures and
stop when it fails because the table does not exist.
So is it possible to perform the check when I actually do the CREATE PROC...
create proc abc123test @.b bit
as
Select co1l from abc123
go
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
> NOEXEC doesn't seem to work for me. For some reason, it doesn't detect the
> drop of the table in my example. However, when I tried using SET FMTONLY
> ON, it worked and does not depend on any branching in the procedure itself
> that would prevent access to the underlying missing table.
> create table abc123 (co1l int)
> go
> drop proc abc123test
> go
> create proc abc123test @.b bit
> as
> begin
> if @.b = 0
> Select co1l from abc123
> else
> Select id from sysobjects
> end
> go
> exec abc123test 1
> exec abc123test 0
> drop table abc123
> go
> set fmtonly on
> exec abc123test 1
> exec abc123test 0
> set fmtonly off
> drop proc abc123test
> go
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks,
> Is it possible to perform the checking at the time the stored
> procedure is created. The problem I have got is that it would take a
> long time to test a thousand+ number of stored procedures using exec,
> supplying different parameters to each one etc. So I thought it
> would be easier to generate a script that attempts to drop and then
> recreate the stored procedures and stop when it fails because the
> table does not exist.
> So is it possible to perform the check when I actually do the CREATE
> PROC...
> create proc abc123test @.b bit
> as
> Select co1l from abc123
> go
>
> Thanks,
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
No. You can't do that at creation time. You could probably write
something pretty quickly to iterate through the syscolumns table for
each procedure and generate a dummy set of parameters. There is also
likely sample code out there to do the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
> No. You can't do that at creation time. You could probably write something
> pretty quickly to iterate through the syscolumns table for each procedure
> and generate a dummy set of parameters. There is also likely sample code
> out there to do the same.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks David
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
Ok. I whipped something together that you can use directly in a T-SQL
script. The script is mildly tested, so additional testing is required
at your end before using on a production system. The script creates the
necessary dynamic SQL to execute each procedure with NULL parameters and
then executes using SET FMTONLY ON and stored the results of the
execution in the temp table.
Good luck.
-- Stores the objects, execution SQL, and result
Create Table #ExecProcs (
ID INT IDENTITY NOT NULL,
Owner nvarchar(128) NOT NULL,
SPName nvarchar(128) NOT NULL,
ParameterCount INT NOT NULL,
ExecResult INT NULL,
ExecSQL nvarchar(2000) NOT NULL,
PRIMARY KEY (ID))
go
-- Get a list of procedures and create the execution SQL
Insert into #ExecProcs (
Owner,
SPName,
ParameterCount,
ExecSQL )
Select
USER_NAME(o.uid) as "Owner",
o.name as "SPName",
COUNT(p.name) as "ParameterCount",
'SET FMTONLY ON;' +
'EXEC [' +
USER_NAME(o.uid) + '].[' + o.name + '] ' +
CASE SIGN(COUNT(p.name))
WHEN 0
THEN ''
WHEN 1
THEN
REPLICATE('NULL,', COUNT(p.name) - 1) +
REPLICATE('NULL', COUNT(p.name) - (COUNT(p.name) - 1))
END + ';'
from
dbo.sysobjects o
left outer join
dbo.syscolumns p
on
o.id = p.id
where
o.type = 'P'
Group By
o.uid,
o.id,
o.name
Order By
o.uid,
o.id,
o.name
go
-- check the results
Select * from #ExecProcs
go
-- script to execute each procedure and store result in temp table
Declare @.Result INT
Declare @.SQL nvarchar(2000)
Declare @.ID INT
Declare @.Loop BIT
Set @.Loop = 1
While (@.Loop = 1)
Begin
SET @.ID = NULL
Select TOP 1
@.ID = ID,
@.SQL = ExecSQL
From
#ExecProcs
Where
ExecResult IS NULL
If @.ID IS NULL
SET @.Loop = 0
Else Begin
EXEC (@.SQL)
Update
#ExecProcs
Set
ExecResult = @.@.ERROR
Where
ID = @.ID
End
End
go
-- check results
Select * from #ExecProcs
go
-- drop the temp table
drop Table #ExecProcs
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David - this works great.
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OuJKWnEmFHA.2904@.TK2MSFTNGP14.phx.gbl...
> C-W wrote:
> Ok. I whipped something together that you can use directly in a T-SQL
> script. The script is mildly tested, so additional testing is required at
> your end before using on a production system. The script creates the
> necessary dynamic SQL to execute each procedure with NULL parameters and
> then executes using SET FMTONLY ON and stored the results of the execution
> in the temp table.
> Good luck.

Detect servers on the network?

I'm wondering how to detect SQL servers on the network, the way Enterprise
Manager locates servers when it populates combo boxes or list boxes with
active servers on the network. Can anybody give me a hint? Some C# code
would be a plus. Thanks!
Hi,
Execute the below command from command prompt:-
ISQL -L
Thanks
Hari
SQL Server MVP
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
> I'm wondering how to detect SQL servers on the network, the way Enterprise
> Manager locates servers when it populates combo boxes or list boxes with
> active servers on the network. Can anybody give me a hint? Some C# code
> would be a plus. Thanks!
|||Sorry, I meant progmatically, not at the command line. The idea being that I
detect available servers on the network in order to populate a combo box with
their names. Thanks!
"Hari Prasad" wrote:

> Hi,
> Execute the below command from command prompt:-
> ISQL -L
>
> --
> Thanks
> Hari
> SQL Server MVP
>
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>
|||Try using COM interop and the SQLDMO ( SQL Server Distributed Management
Objects ).
Heres a sample in C#
http://www.csharphelp.com/archives2/archive342.html
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
> I'm wondering how to detect SQL servers on the network, the way Enterprise
> Manager locates servers when it populates combo boxes or list boxes with
> active servers on the network. Can anybody give me a hint? Some C# code
> would be a plus. Thanks!
|||Yes, quite. That's the ticket! Thanks much!
"Darren" wrote:

> Try using COM interop and the SQLDMO ( SQL Server Distributed Management
> Objects ).
> Heres a sample in C#
> http://www.csharphelp.com/archives2/archive342.html
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>
|||Hello,
I tried this. but i am getting the following error ' QueryInterface for
interface SQLDMO.NameList failed.' I have reference SQLDMO.dll file in my
project.
here is my code--
Dim sqlapp As SQLDMO.Application
sqlapp = New SQLDMO.ApplicationClass
Dim sqlservers As SQLDMO.NameList
sqlservers = sqlapp.ListAvailableSQLServers
Dim i As Integer
Dim srv As Object
For i = 0 To sqlservers.Count
srv = sqlservers.Item(i + 1)
cboServers.Items.Add(srv)
Next
Thanks,
"Darren" wrote:

> Try using COM interop and the SQLDMO ( SQL Server Distributed Management
> Objects ).
> Heres a sample in C#
> http://www.csharphelp.com/archives2/archive342.html
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>

Detect servers on the network?

I'm wondering how to detect SQL servers on the network, the way Enterprise
Manager locates servers when it populates combo boxes or list boxes with
active servers on the network. Can anybody give me a hint? Some C# code
would be a plus. Thanks!Hi,
Execute the below command from command prompt:-
ISQL -L
Thanks
Hari
SQL Server MVP
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
> I'm wondering how to detect SQL servers on the network, the way Enterprise
> Manager locates servers when it populates combo boxes or list boxes with
> active servers on the network. Can anybody give me a hint? Some C# code
> would be a plus. Thanks!|||Sorry, I meant progmatically, not at the command line. The idea being that
I
detect available servers on the network in order to populate a combo box wit
h
their names. Thanks!
"Hari Prasad" wrote:

> Hi,
> Execute the below command from command prompt:-
> ISQL -L
>
> --
> Thanks
> Hari
> SQL Server MVP
>
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>|||Try using COM interop and the SQLDMO ( SQL Server Distributed Management
Objects ).
Heres a sample in C#
http://www.csharphelp.com/archives2/archive342.html
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
> I'm wondering how to detect SQL servers on the network, the way Enterprise
> Manager locates servers when it populates combo boxes or list boxes with
> active servers on the network. Can anybody give me a hint? Some C# code
> would be a plus. Thanks!|||Yes, quite. That's the ticket! Thanks much!
"Darren" wrote:

> Try using COM interop and the SQLDMO ( SQL Server Distributed Management
> Objects ).
> Heres a sample in C#
> http://www.csharphelp.com/archives2/archive342.html
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>|||Hello,
I tried this. but i am getting the following error ' QueryInterface for
interface SQLDMO.NameList failed.' I have reference SQLDMO.dll file in my
project.
here is my code--
Dim sqlapp As SQLDMO.Application
sqlapp = New SQLDMO.ApplicationClass
Dim sqlservers As SQLDMO.NameList
sqlservers = sqlapp.ListAvailableSQLServers
Dim i As Integer
Dim srv As Object
For i = 0 To sqlservers.Count
srv = sqlservers.Item(i + 1)
cboServers.Items.Add(srv)
Next
Thanks,
"Darren" wrote:

> Try using COM interop and the SQLDMO ( SQL Server Distributed Management
> Objects ).
> Heres a sample in C#
> http://www.csharphelp.com/archives2/archive342.html
>
> "William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
> message news:570D002D-3661-4E08-9F22-DC8B62DE2CBE@.microsoft.com...
>
>

detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
--
Aleksandar Grbic
MCDBA
"FJC" wrote:
> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?

detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
Aleksandar Grbic
MCDBA
"FJC" wrote:

> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?sql

Detect Record Conflict:

In a table or 'shifts' for employees i'm trying to test a proposed new shift
for conflicts, the employee's shifts must have 12 hours between them,
This makes sense to me but, is it going to be efficiant considering the
table could have ten's of thousands of shifts.
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
My basic concern is the use of the criteria on two seperate fields: START &
END is it possible to use indexes to maintain performance?
Kind regards,
Graeme.You can definitely increase performance by having, clustered index on
operatorid = and non clustered indedx on EndTime and Start Time
USE BETWEEN -AND so that the = is also taken into consideration
Regards
R.D
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> In a table or 'shifts' for employees i'm trying to test a proposed new shi
ft
> for conflicts, the employee's shifts must have 12 hours between them,
> This makes sense to me but, is it going to be efficiant considering the
> table could have ten's of thousands of shifts.
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> My basic concern is the use of the criteria on two seperate fields: START
&
> END is it possible to use indexes to maintain performance?
>
> Kind regards,
> Graeme.
>
>|||Hi R.D.
thanks for your response.. does this make sense?
Assumeing I calculate 12h before the proposed shifts start as 'A' and
12 after the proposed shifts end as 'B'
Method 1:
if the DB looks for shifts with a START < B
and END > A [this is simple criteria but would it cause massive table
scaning to determine results, it woiuld also locate very long shifts (months
even) that conflicted]
it would have to check every shift that starts before B to see if the shift
ends after A would this be done using two (seperatoe) index ss [on two
differnt DB Fields, (START & END) and then find matchese.
Method 2:
I''m going to have to assume the shift can't be more than 12h (or say 24 to
be safe) and then use BETWEEN on START AND BETWEEN on END.
for instance:
... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
R.StartTime BETWEEN A-24 and B)....
Notice the 24 hours beeing added/subtracted to limit the window of matched
records. as I'm restricting the results by OperatorID i'm only going to
expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
CONFLICT, more than 1 conflicts should be very rare!
Is this sort of thing necessary or does the Query Optimizer do this
automaticly? somehow?
Thanks for you help so far,
Kind regards,
Graeme.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
> You can definitely increase performance by having, clustered index on
> operatorid = and non clustered indedx on EndTime and Start Time
> USE BETWEEN -AND so that the = is also taken into consideration
> Regards
> R.D
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Graeme Stow
Query optimiser generally does everything if you have indexes. However the
final selection can be made only by analysing the execution plans. When you
have index on that, It would not choose Table scan.
Try different combination and select the best one using execution plan.
Thumb rule is thst execution plan should avoid( unless you are selecting
everything) table scan, book marks, filters and should use Index s or
clustered s.
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R.D.
> thanks for your response.. does this make sense?
> Assumeing I calculate 12h before the proposed shifts start as 'A' and
> 12 after the proposed shifts end as 'B'
> Method 1:
> if the DB looks for shifts with a START < B
> and END > A [this is simple criteria but would it cause massive table
> scaning to determine results, it woiuld also locate very long shifts (mont
hs
> even) that conflicted]
>
> it would have to check every shift that starts before B to see if the shif
t
> ends after A would this be done using two (seperatoe) index ss [on two
> differnt DB Fields, (START & END) and then find matchese.
> Method 2:
> I''m going to have to assume the shift can't be more than 12h (or say 24 t
o
> be safe) and then use BETWEEN on START AND BETWEEN on END.
> for instance:
> .... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
> R.StartTime BETWEEN A-24 and B)....
> Notice the 24 hours beeing added/subtracted to limit the window of matched
> records. as I'm restricting the results by OperatorID i'm only going to
> expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
> CONFLICT, more than 1 conflicts should be very rare!
>
> Is this sort of thing necessary or does the Query Optimizer do this
> automaticly? somehow?
> Thanks for you help so far,
> Kind regards,
> Graeme.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
>
>|||Graeme Stow
I missed something in my first response. I think R.operatorid is not
unique. And If combination of ShiftID and operatorid is unique(should be)
try creating clustered primary index on this. And Non clustered index on
start and end dates.
Regards
R.D
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Graeme Stow
> Query optimiser generally does everything if you have indexes. However the
> final selection can be made only by analysing the execution plans. When yo
u
> have index on that, It would not choose Table scan.
> Try different combination and select the best one using execution plan.
> Thumb rule is thst execution plan should avoid( unless you are selecting
> everything) table scan, book marks, filters and should use Index s or
> clustered s.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Hi R,
After creating the following indexs:
create index tblShiftRecords_IX1
on tblShiftRecords (OperatorID, StartTime)
create index tblShiftRecords_IX2
on tblShiftRecords (EndTime)
Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
would it be better to have that as NON-CLUSTERED and have a more usefull
index as clustered like StartTime?
The folowing [testing] query
SELECT *
FROM tblshiftrecords
WHERE operatorid = 546 AND
StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh,
12, '20050516 17:00:00') AND
EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
36, '20050516 17:00:00')
Produces -
1)Index S - cost 0.003327 (98%)
2)Bookmark Lookup - 0.000070 (2%)
3)Filter - 0.000001 (0%)
It's looking better,
Cheers for your help.
Kind regards,
p.s. the query plan is ignoring IX2 on EndTime.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
> Graeme Stow
> I missed something in my first response. I think R.operatorid is not
> unique. And If combination of ShiftID and operatorid is unique(should be)
> try creating clustered primary index on this. And Non clustered index on
> start and end dates.
> Regards
> R.D
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>|||Graeme
Thats great. You can last do one thing.
have covering index. create individual non clustered indexes on operators
id, start time and end time and see you can eliminate book mark.
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R,
> After creating the following indexs:
> create index tblShiftRecords_IX1
> on tblShiftRecords (OperatorID, StartTime)
> create index tblShiftRecords_IX2
> on tblShiftRecords (EndTime)
>
> Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
> would it be better to have that as NON-CLUSTERED and have a more usefull
> index as clustered like StartTime?
> The folowing [testing] query
> SELECT *
> FROM tblshiftrecords
> WHERE operatorid = 546 AND
> StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh
,
> 12, '20050516 17:00:00') AND
> EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
> 36, '20050516 17:00:00')
> Produces -
> 1)Index S - cost 0.003327 (98%)
> 2)Bookmark Lookup - 0.000070 (2%)
> 3)Filter - 0.000001 (0%)
> It's looking better,
> Cheers for your help.
> Kind regards,
> p.s. the query plan is ignoring IX2 on EndTime.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
>
>

Detect Picture/File Presence

I am linking to photographs/pictures of employees. Some employees have a
picture.gif file while others do not have a physical file present (i.e. John
Doe has not "jdoe.gif" in the file share) depending on whether their picture
has been taken or not. Can I detect the presence/abscence of this picture
using vb code or a reporting services expression and change to a
"notavailable.gif" if the picture is not there. I'm trying to get rid of the
ugly X that appears when the file is not there.Scott:
This is what I've done to resolve this issue. I added a filename field to
the table to store the name of the image. I default it to none.gif which is
really a transparent nothing image. If I do have an image for an employee
then the name goes into the field and in the report I just display whatever
image is in the field.
HTH
Richard
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:3D75745E-4332-4D27-A9DA-41D0A33B4BF7@.microsoft.com...
>I am linking to photographs/pictures of employees. Some employees have a
> picture.gif file while others do not have a physical file present (i.e.
> John
> Doe has not "jdoe.gif" in the file share) depending on whether their
> picture
> has been taken or not. Can I detect the presence/abscence of this picture
> using vb code or a reporting services expression and change to a
> "notavailable.gif" if the picture is not there. I'm trying to get rid of
> the
> ugly X that appears when the file is not there.

Detect new files and populate date

Hi, I need to create a SSIS package for following usage:

I have a folder called c:\test. Every month, I copy a file with following format testMMYY.txt. (Month Year) from a client (A) for vendor B. I have a sql table which contains filemonth(, clientname and vendorname. How can I create a SSIS package to detect any new files, if any, and populate the month and year into file month, and populate clientname and vendorname.

Thanks,

You can use the FileWatcher task (available on SQLIS.com) to monitor for a file. If it is only a once a month thing, you could just run the package and use the File System task to check for existence of the file. You'll need to use a expression to set the ConnectionString property of your FlatFile connection manager to the correct value by concatenating the proper month and year into the file name.|||

Thanks for the reply.

Maybe my description is a little bit misleading, my issue is to identify latest file and transform the file name, e.g. I0407.txt into April 2007 and insert it into the filemonth field in the vendorfile table.

Any thoughts?

Thanks,

|||You can use a script task and the DateAndTime class's MonthName function to translate the file name. This post (http://forums.microsoft.com/msdn/showpost.aspx?postid=1467831&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1) has the script for sorting the list of files in a directory and outputting in a DataTable object. Just take the last row of the data table for the latest file.