Finding installed MS Server in PCs over Network

Hello y'all,
I am trying to find out if there is code to find out all the MS Servers installed over a Network.
ThanksDownload SQLScan.

http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&displaylang=ensql

Finding installed MS Server in PCs over Network

Hello y'all,
I am trying to find out if there is code to find out all the MS Servers installed over a Network.
ThanksPlease don't double post.|||There are a number of ways to do it, all with their own pluses and minuses. Are you thinking in terms of AD (and if so one domain or many), polling, or something else? LAN, WAN, or other? Approximately how many servers, and how are they managed? Will you be able to administer them from the PC where you want to enumerate them, or is this a "drive by" poll?

If you are looking for the "shoot low boys, they're riding shetland ponies" approach, check out SQLDMO (http://support.microsoft.com/default.aspx?scid=kb;en-us;287737).

-PatP

Finding 'Error Column' Causing Error

Good Morning,

Am I new at this so please bear with me. I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting. It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error. It identifies the colum as 5301.

I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced. I have traced the entire process using this information and cannot find a reference lineage id of 5301. Was that thread information accurate, and if so what do I do now? If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this.

Work so far:

I have checked for integrity between column definitions and source flat file. I applied derived column changes to make the data transform to the appropriate data type/order where necessary. This part works without error. (Or seems to, there is no error output from this piece.) It is only on the final attempt to load that the process errors with these messages.

Thank you in advance to anyone who can help me.

Rog

It should be, I think, the INPUT column lineage ID of the offending component.|||

Tracing input columns I can find a lineage ID of 5103 NOT 5301. There is no reference in any of the ID fields, including the lineage, of 5301. Any other ideas?

|||Edit the .dtsx package file in your favorite text editor and look for 5301. Look around there a bit to see what column it references.|||

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

|||

CaptainMyCaptain wrote:

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

Right, well, there is. You should be able to find the INPUT column of 5301 in your data flow when looking at the advanced editor for any given component.

The lineage ID, 5109, in this case tells me that this column has gone through some transformations along the way, but it was originally 5109 somewhere along the line. If you look back a ways, you should find an id="5109" on column further up in the .dtsx source. That is this column's "parent."

finding duplicates

quick easy querstion:
I have an inventory table in a datbase and each record has
a serial number. what is the sql code to find duplicates.
tiaSee following example:
create table #cartype(manufacturer varchar(500), score int)
insert into #cartype values('Toyota', 1 )
insert into #cartype values('Toyota', 2)
insert into #cartype values('Toyota', 3)
insert into #cartype values('Honda', 2)
insert into #cartype values('Honda', 5)
insert into #cartype values('Honda', 8)
insert into #cartype values('GE', 1)
insert into #cartype values('GE', 3)
insert into #cartype values('GE', 9)
insert into #cartype values('GE1', 6)
Following query will give you manufacturers that are appearing more than
once.
select manufacturer from #cartype
group by manufacturer
having count(*) > 1
--Following is the query to get the complete row details
select a.* from #cartype a inner join
(select manufacturer from #cartype
group by manufacturer
having count(*) > 1) b
on a.manufacturer = b.manufacturer
--
-Vishal
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||Katie,
SELECT serial_number, COUNT(serial_number) [Count]
FROM dbo.inventory
GROUP BY serial_number
HAVING COUNT(serial_number) > 1
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1
And if you want the actual rows:
SELECT * FROM inventory i
INNER JOIN (
SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1) AS j
ON i.serial_number = j.serial_number
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||not very sure but,
I think you are using wrong column name, make sure all the column names that
you use in the query exists in the table.(may be some spelling mistake)
--
-Vishal
"katie" <kadf@.hsd.com> wrote in message
news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> i am doing this in access and it asks me to put a
> parameter value in. what does this mean
> >--Original Message--
> >Katie,
> >
> >SELECT serial_number, COUNT(serial_number) [Count]
> >FROM dbo.inventory
> >GROUP BY serial_number
> >HAVING COUNT(serial_number) > 1
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"katie" <kari@.wom3c.com> wrote in message
> >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> >> quick easy querstion:
> >> I have an inventory table in a datbase and each record
> has
> >> a serial number. what is the sql code to find
> duplicates.
> >>
> >> tia
> >
> >
> >.
> >|||I think square brackets are used in Access to indicate a parameter, so if
you leave them of you should be ok. Note that I last used Access quite a few
years ago, so don't bank on it :-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:u7VqUTGUDHA.1912@.tk2msftngp13.phx.gbl...
> not very sure but,
> I think you are using wrong column name, make sure all the column names
that
> you use in the query exists in the table.(may be some spelling mistake)
> --
> -Vishal
> "katie" <kadf@.hsd.com> wrote in message
> news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> > i am doing this in access and it asks me to put a
> > parameter value in. what does this mean
> > >--Original Message--
> > >Katie,
> > >
> > >SELECT serial_number, COUNT(serial_number) [Count]
> > >FROM dbo.inventory
> > >GROUP BY serial_number
> > >HAVING COUNT(serial_number) > 1
> > >
> > >--
> > >Dinesh.
> > >SQL Server FAQ at
> > >http://www.tkdinesh.com
> > >
> > >"katie" <kari@.wom3c.com> wrote in message
> > >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> > >> quick easy querstion:
> > >> I have an inventory table in a datbase and each record
> > has
> > >> a serial number. what is the sql code to find
> > duplicates.
> > >>
> > >> tia
> > >
> > >
> > >.
> > >
>

Find tables in packages (SQL2000)

Hi!!!
We have about a houndred packages made with Enterprise Manager. Is
there any tool in SQL (or code) to find a certain table involved in a
package?
eg: I need to know in which packages is involved the table
cost_center...in other words which packages affects a determined table
Thanks for your help!
EzequielI'm affraid it is impossible
select packagedata from msdb..sysdtspackages
where name.....
<esapoznik@.gmail.com> wrote in message
news:1148477673.385381.177230@.38g2000cwa.googlegroups.com...
> Hi!!!
> We have about a houndred packages made with Enterprise Manager. Is
> there any tool in SQL (or code) to find a certain table involved in a
> package?
> eg: I need to know in which packages is involved the table
> cost_center...in other words which packages affects a determined table
> Thanks for your help!
> Ezequiel
>|||The only way I know of to search packages is to save them
out to Visual Basic files. You can then search the bas files
for the table name.
I've worked places where bas files for packages were kept
under source control which made it easy to find specific
objects.
-Sue
On 24 May 2006 06:34:33 -0700, esapoznik@.gmail.com wrote:

>Hi!!!
>We have about a houndred packages made with Enterprise Manager. Is
>there any tool in SQL (or code) to find a certain table involved in a
>package?
>eg: I need to know in which packages is involved the table
>cost_center...in other words which packages affects a determined table
>Thanks for your help!
>Ezequiel

Find tables in packages (SQL2000)

Hi!!!
We have about a houndred packages made with Enterprise Manager. Is
there any tool in SQL (or code) to find a certain table involved in a
package?
eg: I need to know in which packages is involved the table
cost_center...in other words which packages affects a determined table
Thanks for your help!
EzequielI'm affraid it is impossible
select packagedata from msdb..sysdtspackages
where name.....
<esapoznik@.gmail.com> wrote in message
news:1148477673.385381.177230@.38g2000cwa.googlegroups.com...
> Hi!!!
> We have about a houndred packages made with Enterprise Manager. Is
> there any tool in SQL (or code) to find a certain table involved in a
> package?
> eg: I need to know in which packages is involved the table
> cost_center...in other words which packages affects a determined table
> Thanks for your help!
> Ezequiel
>|||The only way I know of to search packages is to save them
out to Visual Basic files. You can then search the bas files
for the table name.
I've worked places where bas files for packages were kept
under source control which made it easy to find specific
objects.
-Sue
On 24 May 2006 06:34:33 -0700, esapoznik@.gmail.com wrote:
>Hi!!!
>We have about a houndred packages made with Enterprise Manager. Is
>there any tool in SQL (or code) to find a certain table involved in a
>package?
>eg: I need to know in which packages is involved the table
>cost_center...in other words which packages affects a determined table
>Thanks for your help!
>Ezequiel

Find out the percentage complete of Stored Procedure

hi,

In my code i have to increment progress bar based on percentage
completion of Stored Procedure.I am not to get any solution on this.
Please Help me on this issue.
I am using win forms Visual studio 2005 and Sql server 2005.

Thanx in advance

NitinThere is no way to measure the progress of a single SQL command, so
what you an do depends on what is going on inside the stored
procedure. If the procedure has multiple steps you could probably
return something between each that would let the front end indicate
progress. If all the time is in one big SELECT or UPDATE or whatever,
then you can not show true progress.

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

Roy Harvey
Beacon Falls, CT

On 12 Sep 2006 02:21:12 -0700, nitinsharma717@.gmail.com wrote:

Quote:

Originally Posted by

>hi,
>
>In my code i have to increment progress bar based on percentage
>completion of Stored Procedure.I am not to get any solution on this.
>Please Help me on this issue.
>I am using win forms Visual studio 2005 and Sql server 2005.
>
>Thanx in advance
>
>Nitin

|||Roy Harvey wrote:

Quote:

Originally Posted by

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

In SQL Server you may or may not be correct. But other RDBMS products
do contain the ability to monitor progress, adjust for workload, and
accurately report back progress.

One example is Oracle's DBMS_APPLICATION_INFO built-in package
(http://www.psoug.org/reference/dbms..._info.html#aplo)
--
Daniel Morgan
University of Washington|||On Tue, 12 Sep 2006 06:24:41 -0700, DA Morgan <damorgan@.psoug.org>
wrote:

Quote:

Originally Posted by

>Roy Harvey wrote:
>

Quote:

Originally Posted by

>Of course you could cheat and just pretend to show progress. I have
>long been convinced that many progress bars are fakes intended to keep
>users from getting too anxious.

>
>In SQL Server you may or may not be correct. But other RDBMS products
>do contain the ability to monitor progress, adjust for workload, and
>accurately report back progress.
>
>One example is Oracle's DBMS_APPLICATION_INFO built-in package
>(http://www.psoug.org/reference/dbms..._info.html#aplo)

Interesting. Thanks for the enlightenment.

Roy

Find In...Stored Procedures?

All:
Is there a way to do a "Find" across the text of a set of stored
procedures? (SQL Server 2000)
I am in the process of doing some code refactoring and have eliminated
a column in one of my tables. Now, I want to find all the stored
procedures that use the column name.
Is there a way to do this? Alternatives?
Many thanks,
JohnHow do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"jpuopolo@.mvisiontechnology.com" wrote:

> All:
> Is there a way to do a "Find" across the text of a set of stored
> procedures? (SQL Server 2000)
> I am in the process of doing some code refactoring and have eliminated
> a column in one of my tables. Now, I want to find all the stored
> procedures that use the column name.
> Is there a way to do this? Alternatives?
> Many thanks,
> John
>|||Look at the syscomments table; it contains the text for your stored
procedures and views.
SLECT *
FROM syscomments
WHERE text like '%[column name]%'
HTH,
Stu|||Stu:
Thanks - worked like a charm.
Best,
John|||Use dbname
go
SELECT OBJECT_NAME(ID) from syscomments
where TEXT like '%columnname%'
Thanks
Hari
SQL Server MVP
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126359684.486000.4260@.g49g2000cwa.googlegroups.com...
> Look at the syscomments table; it contains the text for your stored
> procedures and views.
> SLECT *
> FROM syscomments
> WHERE text like '%[column name]%'
> HTH,
> Stu
>

Find fields with an index

Hi.
I am trying to find a way to establish whether a field has an index on it or
not. This is relevant because I am writing code to loop through the fields i
n
a table and shrink columns according to the length of the longest item in
them, and you cannot alter a column that has an index on it. This is
terminating the loop whenever I hit an indexed field.
Cheers,
NeilNeil
Something like this
SELECT OBJECT_NAME(id) AS table_name,name,
COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
GROUP BY id,name
order by table_name
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
> Hi.
> I am trying to find a way to establish whether a field has an index on it
> or
> not. This is relevant because I am writing code to loop through the fields
> in
> a table and shrink columns according to the length of the longest item in
> them, and you cannot alter a column that has an index on it. This is
> terminating the loop whenever I hit an indexed field.
> Cheers,
> Neil
>
>|||Thanks Uri.
Unfortunately, I still need to find the fields that the indexes are on.
Given the slgihtly shoddy index naming protocol we are using, it's ly not
jsut a case of stripping off a substring of the index name.
Is there an easy way to find the keys for the index?
Aha! Just found the sysindexkeys table. Feel a bit daft.
Thanks for your help.
Neil
Cheers,
Neil
"Uri Dimant" wrote:

> Neil
> Something like this
> SELECT OBJECT_NAME(id) AS table_name,name,
> COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> GROUP BY id,name
> order by table_name
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
>
>|||Hi Jones
Probably, this can help you.
select object_name(sysindexes.id),
index_col(object_name(sysindexes.id),indid,1)
from sysindexes
INNER JOIN sysobjects on sysobjects.id = sysindexes.id
where
sysobjects.xtype in ('u','v') and
sysindexes.status = 0 and
index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Uri.
> Unfortunately, I still need to find the fields that the indexes are on.
> Given the slgihtly shoddy index naming protocol we are using, it's ly n
ot
> jsut a case of stripping off a substring of the index name.
> Is there an easy way to find the keys for the index?
> Aha! Just found the sysindexkeys table. Feel a bit daft.
> Thanks for your help.
> Neil
> Cheers,
> Neil
> "Uri Dimant" wrote:
>|||Thanks Chandra, that does it nicely.
I found the details this way instead:
select a.name, b.name from sysobjects a
inner join syscolumns b on a.id = b.id
inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
where a.name = 'tableA'
Cheers,
Neil
"Chandra" wrote:
> Hi Jones
> Probably, this can help you.
>
> select object_name(sysindexes.id),
> index_col(object_name(sysindexes.id),indid,1)
> from sysindexes
> INNER JOIN sysobjects on sysobjects.id = sysindexes.id
> where
> sysobjects.xtype in ('u','v') and
> sysindexes.status = 0 and
> index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
> please let me know if u have any questions
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NeilDJones" wrote:
>|||Yes jones
quite interesting! yes ur solution is correct. i tried for colid and
couldnot find it.
then, i checked how sp_help was doing it.
sp_help uses sp_helpindex,
and i got the code sniplett from that SP
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Chandra, that does it nicely.
> I found the details this way instead:
> select a.name, b.name from sysobjects a
> inner join syscolumns b on a.id = b.id
> inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
> where a.name = 'tableA'
> Cheers,
> Neil
> "Chandra" wrote:
>

Find Counties in area

We have the need to determine surrounding counties of a particular county.

For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa

Could it be determined by the longitude and latitude of the zip code in comparison with the counties?

Hello,

Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)

tCounty (CountyID int, CountyName varchar)

tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)

So if Contra Costa was near Alameda, tCountry might contain rows:

1, Contra Costa

2, Alameda

And tCountyAdjoined would contain:

1, 2

Obviously, a simple join then returns all counties that adjoin a particular County.

Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)

Cheers,

Rob

Find all chars in table that are ASCII code 128 and Greater

Does anyone know how to query a field in a table where it contains an
ASCII code >= 128 - without looping through every field for every
record in table (using charindex)?

Ex of char I would like to find: which is char(252)DennBen (dbenedett@.hotmail.com) writes:

Quote:

Originally Posted by

Does anyone know how to query a field in a table where it contains an
ASCII code >= 128 - without looping through every field for every
record in table (using charindex)?
>
Ex of char I would like to find: which is char(252)

select *
from tbl
where col COLLATE Latin1_General_BIN
LIKE '%[^' + char(32) + '-' + char(126) + ']%'

If you want to run this for many in columns in many tables, you
will to run the query once per column and table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx