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

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:

> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
>
>
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in quer
y
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
>
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
----
---
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
----
---|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ----
---
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ----
----
thanks I will try that

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
Michael
Use the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
|||If you now the column name you can do a search with object browser in query
analyzer on the database.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:

> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>
|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
thanks I will try that
|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.
|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegr oups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>
|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------
|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> > Hi,
> >
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> >
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> >
> > TIA
> > Michael
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > Hi,
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> > TIA
> > Michael
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL :)
> "faux...@.gmail.com" wrote:
> > Use the profiler ... do the activity again to have a look at that
> > data ... In the backend it would definately show you the table or the
> > SP the application is calling ... Dig in further.
> > Regards
> > Bharat Butani.
> > On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > > Hi,
> > > I have an application which shows a certain piece of data on the
> > > screen, but I do not know which table it comes from (281 user tables).
> > > How can I easily find which table has this data ?
> > > (without opening each table and scanning through 1000's of rows
> > > manually)
> > > TIA
> > > Michael
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that

Find which SPs have quoted_identifier set wrong

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

(No huge rush, the someone-else has already fixed the recent case)

SQL 2K, SP3Ed Murphy wrote:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

You can query against the SP text as follows.

select o.[name] as 'ProcName', c.[text] as 'ProcText'
from sysobjects o
join syscomments c on o.[id] = c.[id]
where o.[name] like 'usp%'

This is based on the assumption that all your SPs start with 'usp'.
Applying a WHERE clause should enable you to filter out certain procs.

Somebody please step in and correct this as I have a feeling it's not
the best way.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

SELECT name
FROM sysobjects
WHERE objectproperty(id, 'ExecIsQuotedIdentOn') = 0

I'm assuming here that OFF is the incorrect position.

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

Find what procedure was execd

Hello

I need to find out what stored procedure was executed when I click on
a button in a third party application, is there a way to find this
information out in some sort of log?

Some kind of profiling to see what procedure was executed and with
what parameters.

Kind Regards

Matt
www.fruitsalad.org"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0312130930.377f4d7c@.posting.google.c om...
> Hello
> I need to find out what stored procedure was executed when I click on
> a button in a third party application, is there a way to find this
> information out in some sort of log?
> Some kind of profiling to see what procedure was executed and with
> what parameters.
> Kind Regards
> Matt
> www.fruitsalad.org

You can use SQL Profiler to trace all the SQL sent to the server - see Books
Online for details.

Simon

Find value match in SQL table

Find value match in SQL table

Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?

Taod for ms sql server has that feature to compare rows from 2 tables..

I nkow that are theare also others but Toad i did use it..

Sorry for my bad english

|||

There is a tablediff.exe that comes with SQL Server 2005, if you are using 2005.

Find SQL Servers 2000 & 2005 on the net

In order to find SQL Servers 2000, in my .NET application, I successfully use
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel Hecker
To see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>
|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:

> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>

Find SQL Servers 2000 & 2005 on the net

In order to find SQL Servers 2000, in my .NET application, I successfully use
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> > In order to find SQL Servers 2000, in my .NET application, I successfully
> > use
> > "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> > instances
> > of SQL Servers 2005.
> >
> > Is there anything else available to do this job? Thanks for any hint.
> >
> > Axel Hecker
> >
>
>

Find SQL Servers 2000 & 2005 on the net

In order to find SQL Servers 2000, in my .NET application, I successfully us
e
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:

> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitl
y
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:

> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:

> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find Current Date Time Span SQL

Any body have an Idea on this... Last problem of my Calendar Application.

To keep my calendar loading fast and efficent I have a SP that loads data to a datatable to read from c#.

My sp reads this

(
@.ID int,
@.DateStart datetime,
@.DateEnd datetime
)
AS

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND JobDateStart Between @.DateStart AND @.DateEnd + 1
OR JobDateEnd Between @.DateStart AND @.DateEnd + 1)

Now My problem is that the DateStart and DateEnd values are used to pull information from a DB. So lets say that I have a job that starts on November 30th and ends on January 3rd.
My calendar will display the record on the 30th day of Nov and on the 1st through 3rd fo Jan, however when I look at December there is NO DATA there. which is the expected result because the dates do not fall within the start and ends dates.

My question is how query the database to pull back my month spanding record without pulling back all the data in the database for performace?

My thought is something like the date span falls into the range between the start and end, but I cannot find anything????? Please help, I know it is probaly simple right??Found the answer, just took THINKING LESS rather than more..

add and OR statement like this, it will get the 1 day events and current month event and the spanning events.

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND
/*FIX SPANNING MONTHS / YEAR JOBS*/
@.DateStart BETWEEN JobDateStart AND JobDateEnd +1
/*SELECT 1 DAY OR 1 MONTH JOBS*/
OR JobDateStart Between @.DateStart AND @.DateEnd + 1)

It really was that easy. Less load on the db so I can loop through the DataTable and do my sorting with no effect on the DB.

Financial Accounting Cube

Hi,

I'm designing a cube for analyzing financial data from an accounting application.

Basically, the facts table looks like:

(idAccount, idTime, nValue)

that shows every financial operation for any account, and it's very easy to build a cube for analysing the accounting movements.

But there is another important measure, that's the balance of every account in any time.

A tipical report would be, for a whole year, the sum of movements of any account and the balance at the end of the period.

I think adding a new column to the facts table like:

(idAccount, idTime, nValue, nBalance)

wouldn't work, because any account has a balance in any time, even thought there isn't movements in that time period.

What do you think would be a good design approach?

Why not break it into two (Seems like they are unrelated facts that happen to have common dimensions):

idAccount, idTime, nValue

idAccount, idTime, nBalance

|||

Could work, but with that approach I would have a row in the fact table for every day and for every account, and that doesn't look very efficient.

Imagine a "sales" account, that has a balance value in day 10 of 10,000$, and there are more sales after that day:

(idAccount="sales", idTime=10, nBalance=10000)

(idAccount="sales", idTime=15,nValue=500)

so, (idAccount="sales", idTime=15, nBalance=10500)

That means, the balance value changes after any accounting operation.

And now, what happens if the user makes a report that has a row for every account with two more columns: nBalance and nValue, and chooses only the day 20?

For the "sales" account nValue = 0, that's clear, but, what happens with nBalance?

It should be 10500, becuase the balance didn't change after the day 15.

The problem is that I don't know how to create the nBalance measure in order to get that behaviour.

The only way I can figure out is filling the (idAccount, idTIme, nBalance) table with the balance of every account for every day, and that seems to be a waste of resources, because there're many accounts that have very few changes in their balance over the year.

Any suggestion?

P.S. Another problem is the calculation of aggregations for nBalance. Aggregations for nBalance in the account dimension must be a sum of nBalance for any account. But for time dimension, nBalance is the value of nBalance in the last time of the time dimension.

I can figure out many "real wolrd" problems similar to this. For example, imagine that you have a solution for identifying the stock in a warehouse. There are, at least, two main measures: the ins and outs of the warehouse and the actual stock in a certain value of time.

Filtering SqlDataSource to show all vs. non-null records

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (?) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance.

Bill

Construct a better SELECT that uses a parameter.

SELECT ...

FROM ...

WHERE (@.ShowAll=1)

OR (@.ShowAll=0 AND (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL))

|||Sweet. Thank you much.

Filtering records between 2 dates for the report(Using CR 9.2)

HI,
I am developing this application which stores purchases requisitions information from the user in a d/b.I have to generate reports based on the user inputted dates.The reports should list all the records between the 2 dates which the user inputs at run time.
This is the code I have provided to do the filtering of records for the CR.
********************************************************
MsgBox "Please enter 2 dates in between which to generate the Cost allocation Report", vbOKOnly + vbInformation, "Printing Reports.."
date1 = InputBox("Please enter the starting date for the Report!", "Cost Allocation Report")
date2 = InputBox("please enter the ending date for the Report!", "Cost Allocation Report")
date1 = CDate(MakeDateFormated(date1, "/"))
date2 = CDate(MakeDateFormated(date2, "/"))
Dim str3
str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= '" & date1 & "' AND {purchase.podate} <= '" & date2 & "'"
CrystalReport6.RecordSelectionFormula = str3
CrystalReport6.PrintOut True, 1
CrystalReport6.DiscardSavedData
********************************************************
Date1 and date 2 are of type Date.
When this code is run,I get an error message which says that "a Date-Time is required here" for the line giving the Record selection formula.
When I checked the CR in the designer,the datatype of the field "podate" is shown as Date-Time instead of just Date,even though,it is of type date in the Database.
I have tried deleting the field and adding it again to the report.It still shows datatype to be Date-Time.
How can I change this?
Please help me as I am very very new to this area.
Thank you for your time.Your help is highly appreciated.
Lakshmi VinayTry this

str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= #" & date1 & "# AND {purchase.podate} <= #" & date2 & "#"