Finding last instance of string

How can I find the last instance of a string? I was thinking of writing a
loop that goes through the string, but that requires writing a few lines of
code. I was also thinking of inverting the string (in this case, charindex
would work since). But I'm not sure how to invert the string w/o writing too
much code.
Any help is appreciated. Thanks.> But I'm not sure how to invert the string w/o writing too
> much code.
Check the REVERSE T-SQL function.
Dejan Sarka|||DECLARE @.s VARCHAR(32);
SET @.s = 'aoodfsdf';
SELECT LastInstanceOfA = CASE CHARINDEX('a', @.s)
WHEN 0 THEN 0
ELSE LEN(@.s) + 1 - CHARINDEX('a', REVERSE(@.s))
END;
"VMI" <VMI@.discussions.microsoft.com> wrote in message
news:109B0CEE-6424-4154-9ED3-7E56C2B41B7F@.microsoft.com...
> How can I find the last instance of a string? I was thinking of writing a
> loop that goes through the string, but that requires writing a few lines
> of
> code. I was also thinking of inverting the string (in this case, charindex
> would work since). But I'm not sure how to invert the string w/o writing
> too
> much code.
> Any help is appreciated. Thanks.

Finding Database Size

Hi,
I am writing a proposal where I need to give an
approximate database size and hence some other team will
work out the hardware size of the system.
Is there any template / formula / resources which can help
me sizing the database?
If you can throw some light?
Thanks in advance.
DibsThere are formulas in Books Online! :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dibyendu Basu" <anonymous@.discussions.microsoft.com> wrote in message
news:077d01c3b4b5$38ce99d0$a401280a@.phx.gbl...
> Hi,
> I am writing a proposal where I need to give an
> approximate database size and hence some other team will
> work out the hardware size of the system.
> Is there any template / formula / resources which can help
> me sizing the database?
> If you can throw some light?
> Thanks in advance.
> Dibs

Finding changes

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

Example:

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

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

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

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

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

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

First you have to specify your different values:

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

UNION

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

UNION

....

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

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

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

Finding BINN directory for Multiple Instances

I am writing an installer that needs to put a dll file with a bunch of
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?

If the instance name = "MSSQLSERVER"
then path to BINN directory = registry value of
HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"

If the instance name != "MSSQLSERVER"
then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"

Thanks in advance for any help."Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0407231210.28d2403c@.posting.google.c om...
> I am writing an installer that needs to put a dll file with a bunch of
> extended stored procedures in the BINN directory. The problem is that
> if the machine is running multiple instances of SQL Server, there is
> more than one BINN directory. The installer has a dynamically
> populated dropdown with the name of the instances, so does anyone know
> if this logic is correct and/or reliable assuming SQL Server 2000?
> If the instance name = "MSSQLSERVER"
> then path to BINN directory = registry value of
> HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
> If the instance name != "MSSQLSERVER"
> then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
> SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
> Thanks in advance for any help.

See questions 12 and 13:

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

Simon

Finding a tape drive

I am writing an interface for the SQL backup in my application. I am
currently trying to determine if a tape drive exists on the system so the
system can add it as a backup device. If I use enterprise manager to add
the tape drive as a device my application can see the device but still can't
see the tape drive directly. My system can see the hard drives and DVD
drive on my system. I have tried sp_helpdevices, xp_availablemedia, and
sysdevices.
Should I be looking somewhere else or does the user have to add the tape
from Enterprise manager first?
Regards,
JohnDepending on what interface you're developing, you can use SQL-DMO in
various languages to iterate through devices on your SQL Server and
tapes as well.
Here's a few helpful links that I found useful when I developed a SQL
Db management interface for the web.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
http://sqldev.net/sqldmo.htm|||Thanks for the replay.
I have been implementing this via the SqlClient in C# using the basic
"BACKUP <db> TO <device>" interface. I obtained a list of devices from the
sys_devices or xp_availablemedia and xp_subdir which all seems to work fine
except I don't see the tape device unless it's added as a device.
I have never worked with SQL-DMO and the example seems to have problem
compiling which I assume is because it was written for an older version of
C# / .NET. What is the advantage of using the SQL-DMO as opposed to just
using the SqlClient built into C#?
Regards,
John
"sze" <szeying.tan@.gmail.com> wrote in message
news:1122596876.912374.115650@.z14g2000cwz.googlegroups.com...
> Depending on what interface you're developing, you can use SQL-DMO in
> various languages to iterate through devices on your SQL Server and
> tapes as well.
> Here's a few helpful links that I found useful when I developed a SQL
> Db management interface for the web.
> http://msdn.microsoft.com/library/d... />
b_3tlx.asp
> http://sqldev.net/sqldmo.htm
>|||you may have problems compiling if you did not reference sqldmo.dll in
your .NET project.
it should be somewhere in your sql server installation, something like
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ ...
as for advantages, i don't really know. SQL-DMO is an API for SQL
Server administration. SQLClient is a data provider for C#... i'm
usually partial to using an API when i want to interface with a
particular application... since the API encapsulates all objects
available in the particular application.. and in this case, all objects
available in SQL Server (this means everything you can do in Enterprise
Manager). but of course, it's really your call.|||Thanks again, the problem was the strong naming I already knew I needed to
link into it. I found a link for the strong naming problem on the microsoft
site so it works now.
Regards,
John|||EXECUTE master.dbo.xp_get_tape_devices seems to do the trick.
Regards,
Johnsql

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:
>