refreshing index after updates to indexed column in database?

I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
Change tracking does near real time updates. To enable change tracking use the following proc
sp_fulltext_table 'TableName','start_change_tracking'
sp_fulltext_table 'TableName','start_background_updateindex'
You do not need a timestamp column on your table.
If you want to do an incremental index and your table has a time stamp column you have to manually kick off the indexing process. If your table does not have a timestamp column a full population will be run, and again you have to manually kick it off.
use
sp_fulltext_table 'TableName','start_incremental' -- for an incremental population
or
sp_fulltext_table 'TableName','start_full' -- for a full population
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- TR wrote: --
I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
|||Thanks!

refresh identity value

hi,
how can I refresh a identity value of a table after a record gets deleted.

for example a table has 100 rows. One of its column name is rowIndex set to identity 1, incremental 1. After I remove the row with rowIndex value 100, if I insert a new record the rowIndex will be 101, but I want its rowIndex value to be 100. Is there a way to update the identity value after a record gets removed?Yes, but that's not the intention of an identity column. An identity column is intended to automaticaly give you unique record ids.

If you want your rowindex to represent something else then add a trigger to get the max(rowindex) and then add 1.sql

reformatting DATE

I currently have a date column that draws from the now() function in my
asp script. The date looks like this:

2/5/2004 10:09:52 AM

As you can see, there is the day and the time data all in the same
string.

What I would like to do is run a group by clause that groups
all the dates that are on the same day togeather. But how do I seperate
this data in SQL? I know there are VB functions that can do this, but in
order for the group function to work when running a sql statement, I
have to do it in SQL itself. Does anyone know how this is done?

Thank you!

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:4023cc79$0$196$75868355@.news.frii.net...
> I currently have a date column that draws from the now() function in my
> asp script. The date looks like this:
> 2/5/2004 10:09:52 AM
> As you can see, there is the day and the time data all in the same
> string.
> What I would like to do is run a group by clause that groups
> all the dates that are on the same day togeather. But how do I seperate
> this data in SQL? I know there are VB functions that can do this, but in
> order for the group function to work when running a sql statement, I
> have to do it in SQL itself. Does anyone know how this is done?
> Thank you!
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I'm not completely sure what you're looking for, but perhaps something like
this?

select convert(char(8), DateColumn, 112) as 'Day', sum(SomeColumn) as
'Total'
from dbo.MyTable
group by convert(char(8), DateColumn, 112)

Simon

Referential Integrity - Which Column violates this?

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

Cyherus|||It should be done with sp, not with FE-based action queries.|||The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

-PatP|||If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.|||Alright.. as per your advise.. I am now performing these actions on the BE via sp.
now, I am using both return value, out parameters and capture @.@.ERROR.
Issue is that the system it prompting me before I can capture the errors returned.. sp example:

ALTER PROCEDURE dbo.sp_insert_bl
(
@.AAA varchar(12),
@.BBB int,
)
AS
DECLARE @.err int

SET NOCOUNT ON
BEGIN TRAN
INSERT INTO dbo.Table ([AAA], [BBB])
VALUES (@.AAA, @.BBB)
SELECT @.err = @.@.ERROR
IF @.err <> 0
BEGIN
ROLLBACK TRAN
RETURN @.err
END
ELSE
BEGIN
SELECT @.blid = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END

oh yes, using ado to execute exec the sp
cmd.parameter.........
cmd.execute
cmd.parameter("return value")

do you suggest using RAISERROR?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

Referencing Textbox Values

Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.

You can try this:

=ReportItems!textbox3.value

or

You should be able to share a value in a hidden textbox using code.

Create a hidden textbox. Add the expression =Code.SetValue(mytextboxcontent) to the textbox.

Add the expression =Code.GetValue() to the matrix.

Create 2 functions

public myval as object

function setValue(value as object) as object

myval = value

return value

end function

function getValue() as object
return myval
end function

|||Thanks. You just helped me solve a bigger problem I've been working on for a week now.

Referencing query results by number

Is there a way to reference the results of a query by column number?
i.e. select Name, City from tblEmployee
so referencing field 1 from the query in a table will list all the employee
names
Thanks.select Name As Field1, City As Field2 from tblEmployee
OR
select au_id As [1], city As [2], state As [3] from authors
[In the latter case, the fields will be generated as ID1, ID2, and ID3
respectively.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ali Jaffer" <Ali Jaffer@.discussions.microsoft.com> wrote in message
news:0BB8CBE4-0FCD-4444-B00B-99EDE49CF5D0@.microsoft.com...
> Is there a way to reference the results of a query by column number?
> i.e. select Name, City from tblEmployee
> so referencing field 1 from the query in a table will list all the
employee
> names
> Thanks.

Referencing a variable set in an EXEC statement

I have code like this:
DECLARE @.CurrentValue nvarchar(1000)
EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
PRINT @.CurrentValue
When I execute this code, I get an error message that I have to declare
@.CurrentValue. Apparently the code in the EXEC statement runs in it's own
scope and it doesn't see variables outside that scope.
But if I try to declare the variable inside the EXEC string, I can't access
it outside the EXEC statement.
I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
to work either."MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Hi,
try the following
DECLARE @.CurrentValue nvarchar(1000)
set @.CurrentValue = (Select min(Column) FROM Table)
Hope it helps
Regards
Alex|||Have a look here:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||http://www.aspfaq.com/2492
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Look in the posting from ED and today (15.04.2005 20:47) (for Google
searcher the article
http://support.microsoft.com/defaul...kb;en-us;262499)
It describes using the Return value from an Execute. The problem is that in
the context of executing a new session is established and closed when the
statement was executed. So in the next line the value doesnt exists anymore
(in your query)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||> I tried creating a global variable (@.@.CurrentValue),
Where did you get the idea of a "global variable"? This is merely a
variable called @.CurrentValue, with a @. prefix to indicate it is a variable.
There is absolutely no difference between @.@.CurrentValue and @.CurrentValue.
A|||Thanks for the link. sp_executeSQL with OUTPUT parameters worked great!
"Andrew J. Kelly" wrote:

> Have a look here:
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
>
> --
> Andrew J. Kelly SQL MVP
>
> "MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
> news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>
>

Referencing a field in another table as part of a formula in a computed column

Hi guys, I am new to all of this stuff so please bare with me. I will try to explain this as simply as I can. I have 2 tables.. Table1 and Table2 they are linked using both the STOCKCODE and SUPPLIER fields. In Table 1 is the STOCKCODE, LENGTH AND SUPPLIER fields (there are others but these are the 3 that I need to use) In Table2 is STOCKCODE, SUPPLIER and SUPPLER_MASS. What I want to do is put a calculated column into Table2. The formula I need is fairly simple it is: 1/(LENGTH*SUPPLIER_MASS)

Ok so here is what I would like:

1/(SUPPLIER_MASS*(SELECT Table1.LENGTH FROM Table1 WHERE Table1.STOCKCODE=Table2.STOCKCODE AND Table1.SUPPLIER=Table2.SUPPLIER)

From what I understand I can't have a query in a calculated column's "formula" field?

So how would I do this? I don't want to run a query because LENGTH or SUPPLIER_MASS could change on a day to day basis meaning I will have to run the query everything one of these changes. The benefit of having the calculated column is that it will update as soon as LENGTH or SUPPLIER_MASS changes.

Is there any way I can do this?

Thanks guys (and girls)You can have insert, update trigger which will update calculated column every time you update a record or insert a new one.

Thank you.

Referencing a conditional column in the WHERE clause -- Possible?

I have two tables which I'm joining in a query. In the join, I'm creating a column whose value is conditional (see columns Actual_Latitude and Actual_Longitude below). Is it possible to reference the created column in the WHERE clause?

Consider the following query:

---

SELECT
S.StationID
, P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID

---

I'd like to be able to add the following:

---

WHERE Actual_Latitude > 50

---

...But, I'm getting an "Invalid column name" error. Is this possible in some way?

The benefit, of course, would be that I wouldn't have to repeat the conditions in the WHERE clause.

Unfortunately, you can't.

You are creating an ALIAS for an expression, and the expression is not 'known' by that ALIAS in the 'acquisition' part of the query. Once the data is acquired, you can refer to the ALIAS in the ORDER BY because a 'derived table' has been determined.

You could, however, wrap this query in another, and use the ALIAS in the outer query. That does't provide much help with filtering though...

The 'best' option is to repeat the CASE structure for Actual_Latitude in the WHERE clause.

|||

I agree with Arnie for the most part. This will work:

SELECT S.StationID , P.PoleID

--NOTE: change to this means a change to the where clause for Actual_latitude!!
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END > 50 --Actual_Latitude > 50

In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think):

WITH stationQuery AS (

SELECT S.StationID , P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
select *

from stationQuery

where actual_latitude > 50

The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000.

Referencing a Cell

In reporting services, is there a way to reference a cell?
I need to get an aggregate of the first column of a report. In order to do
it, I was thinking of hiding the value in the first row and then at the end i
will sum up the total of that value in the first row....
I'm wondering if I can resolve the problem by referencing the value of
another cell rather than doing the sum in this cell (like you can in Excel)
as reporting service wont let me....
Thanks for your response...!!Can you give us a laid out example of what you want to do and what you want
to sum?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"SQLDBA" <SQLDBA@.discussions.microsoft.com> wrote in message
news:63FB27B1-9E10-4C4A-991B-AF54314A3AF2@.microsoft.com...
> In reporting services, is there a way to reference a cell?
> I need to get an aggregate of the first column of a report. In order to do
> it, I was thinking of hiding the value in the first row and then at the
> end i
> will sum up the total of that value in the first row....
> I'm wondering if I can resolve the problem by referencing the value of
> another cell rather than doing the sum in this cell (like you can in
> Excel)
> as reporting service wont let me....
> Thanks for your response...!!
>|||Hi,
You can use the below syntax to sum the values of two or more cells in the
same manner.
= ReportItems!textbox50.Value + ReportItems!textbox51.Value
But you may get some errors notifying that the cells are not in the same
aggregate group.
But from your explanation I guess that you can use Grouping and then use the
below syntax
=Sum(Fields!FieldName1.Value)
Eralper
http://www.kodyaz.com
"Mary Bray [MVP]" wrote:
> Can you give us a laid out example of what you want to do and what you want
> to sum?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "SQLDBA" <SQLDBA@.discussions.microsoft.com> wrote in message
> news:63FB27B1-9E10-4C4A-991B-AF54314A3AF2@.microsoft.com...
> > In reporting services, is there a way to reference a cell?
> >
> > I need to get an aggregate of the first column of a report. In order to do
> > it, I was thinking of hiding the value in the first row and then at the
> > end i
> > will sum up the total of that value in the first row....
> >
> > I'm wondering if I can resolve the problem by referencing the value of
> > another cell rather than doing the sum in this cell (like you can in
> > Excel)
> > as reporting service wont let me....
> >
> > Thanks for your response...!!
> >
>
>

REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri

Reference previously calculated values in table

I have a table with two columns.
Column A contains a complicated calculation. Column b contains a new calculation which requires the value from Column A. Is there a way to reference the Column A value without re-entering the formula.
Thanks for your help in advance.=ReportItems!textbox1.Value.
[Replace textbox1 with the appropriate cell name in the table.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:1A68EE0F-1789-4F2E-973B-61790F19D657@.microsoft.com...
> I have a table with two columns.
> Column A contains a complicated calculation. Column b contains a new
calculation which requires the value from Column A. Is there a way to
reference the Column A value without re-entering the formula.
> Thanks for your help in advance.

Reference BackGround Color

Hi:
Is it possible to reference the background column in a cell on a table and apply it to another cells background?
Thanks,Not in the current release, no.
The ability to reference report item properties other than Value is on our
wishlist for a future version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:444408E1-3F6E-4692-B76A-68EE646780B3@.microsoft.com...
> Hi:
> Is it possible to reference the background column in a cell on a table and
apply it to another cells background?
> Thanks,
>

Reference an aggregate in where clause

I have two tables that are joined on 1 column.

I want to update one table using an aggregate from the other table.

I could just update all rows even if the aggregate value has not changed, but I wondered if there was a way to only update the records where the aggregate has changed.

I tried this code but assigning the name m to aggreate gives an error

UPDATE t2 SET t2.ColumnB = SELECT MAX(ColumnB) AS m From Table t1

JOIN t2 ON t1.ColumnA = t2.ColumnA

WHERE t2.ColumnB <> m

GROUP BY t1.ColumnA

Niall:

Does this meet your needs?

set nocount on

declare @.t1 table (ColumnA char(1), ColumnB int)
declare @.t2 table (ColumnA char(1), ColumnB int)

insert into @.t1 values ('A', 5)
insert into @.t1 values ('B', null)
insert into @.t1 values ('C', null)
--select * from @.t1

insert into @.t2 values ('A', 3)
insert into @.t2 values ('A', 5)
insert into @.t2 values ('A', 7)
insert into @.t2 values ('B', 2)
--select * from @.t2

set nocount off

update @.t1
set ColumnB = xt.columnb
from @.t1 as yt
inner join
( select t1.ColumnA,
max (t2.columnB) columnB
from @.t1 as t1
inner join @.t2 as t2
on t1.columnA = t2.columnA
group by t1.columnA
) xt
on xt.columnA = yt.columnA

set nocount on

select * from @.t1

-- --
-- Output
-- --

-- (2 row(s) affected)

-- ColumnA ColumnB
-- - --
-- A 7
-- B 2
-- C NULL

|||

Use a subquery - replace the 0 in the isnull calls with blank strings if your ColumnB is character data.

UPDATE Table2 SET ColumnB = t1.MaxB
FROM Table2 t2
INNER JOIN
(SELECT ColumnA, MAX(ColumnB) AS MaxB
FROM Table1
GROUP BY ColumnA) t1 ON t2.ColumnA = t1.ColumnA
WHERE isnull(t2.ColumnB, 0) <> isnull(t1.MaxB, 0)

Reference a column in UDF in a FROM or WHERE clause

Hello there,

I got a problem with using a table returning UDF in
FROM/WHERE clause.

For example...

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
E.EmployeeID = EID.EmployeeID

Works without a problem. But when i reference a column in
the udf...

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept(E.Department) AS EID ON
E.EmployeeID = EID.EmployeeID

Gives me the following error message

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

Joining in the WHERE Clause gives the same Message

NOTE. This is a simplified example of what i'm actally
trying to achieve.
The point is referencing a column in a UDF in a FROM or
WHERE clause

Why is this? Thanks

CREATE TABLE [tb_employees]
(
[EmployeeID] [int] NOT NULL ,
[EmployeeName] [nvarchar] (50) ,
[Department] [nvarchar] (50) ,
CONSTRAINT [PK_tb_employees] PRIMARY KEY
CLUSTERED
(
[EmployeeID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE FUNCTION fn_EmployeesInDept (@.Department nvarchar
(50))
RETURNS @.Employees TABLE
(
EmployeeID int
)
AS
BEGIN
INSERT INTO @.Employees(EmployeeID) SELECT
EmployeeID FROM tb_employees WHERE Department = @.Department

RETURN
END

INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (1, 'Kees', 'Shipping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (2, 'Piet', 'Shipping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (3, 'Jan', 'Accounting')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (4, 'Klaas', 'Accounting')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (5, 'Dirk', 'Houskeeping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (6, 'Arie', 'Houskeeping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (7, 'Bob', 'Houskeeping')RE:
Q1 [Is it possible to pass a UDF a non-scalar as in the example in the From clause; and why]? Example in the From clause:

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept(E.Department) AS EID ON
E.EmployeeID = EID.EmployeeID


A1 Not directly. Sql Server 2k UDFs parameters currently may accept constants (or certain kinds of expressions evaluateing to scalar constants within the current execution context). Sql Server 2k UDFs parameters do not currently accept table, or other non scalar referenced database objects. One may instead use an iterative approach, (passing each sucessive value in as a scalar) as for example:

Declare
@.vDepartment As Nvarchar (128)
Select @.vDepartment = (Select Top 1 Department From dbo.tb_Employees)
Select @.vDepartment As '@.vDepartment', EmployeeID From dbo.fn_EmployeesInDept(@.vDepartment)
.
.
.

RE:
Q2 [Is it possible to reference a column in a UDF in a Where Clause]?

A2 Yes. Referencing Sql Server 2k UDFs in a Where clause is supported. The following, for example, should work:

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
E.EmployeeID = EID.EmployeeID
Where EID.EmployeeID > 5|||/*
USE SCALAR FUNCTION
*/
create FUNCTION fn_IsEmployeeInDept (@.Department nvarchar (50), @.EmployeeID int)
RETURNS bit
with schemabinding
AS
BEGIN
declare @.IsEmployeeID bit
if exists(select Department from dbo.tb_employees WHERE Department = @.Department and EmployeeID=@.EmployeeID)
set @.IsEmployeeID=1
else
set @.IsEmployeeID=0
RETURN @.IsEmployeeID
END
GO

/*
HAS DIFFERENT FUNCTIONALITY
*/
SELECT E.EmployeeID,E2.EmployeeID
FROM tb_Employees AS E
JOIN tb_Employees AS E2 on fn_IsEmployeeInDept(E.Department,E2.EmployeeID)=1

/*
IT IS NOSENCE, CAN BE REPLACED BY JOIN
*/
SELECT E.EmployeeID,E2.EmployeeID
FROM tb_Employees AS E
JOIN tb_Employees AS E2 on E.Department=E2.Department

/*
BUT SCALAR FUNCTION JOIN CAN USED TO JOIN HIERARCHIES IN MSSQL2K
*/

refence a field by column position

Is it possible to reference a field from its position in the column as opposed to its name?

=Fields!Ownership.Value maybe something like =Fields(2).value this doesn't work however, but is there a way.

Not sure why you'd need to do this...What are you trying to accomplish? Maybe there is a workaround. Something like, you want to use the same table with 2 different queries that produce different column names?

Redudant Data on a table column...

Good mornig,

I want my table column to accept redudant data type, so what to do?

Regards

What do you mean by that ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

every day i got information from my client, like an xls file (MS EXCEL sheet), when trying to import this data on the database that i have created using SSIS, i got an message,.... the data that i want to import content redudant data. then i want to know what to do in order to import data from an .xls file, event this file content redudant data ( same data in differents field of a culomn) using SSIS or other methode.

my english is not correct but i do my best.

please help me.

Best regards.

|||Are you getting an error ? if yes, what is it ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

It's most likely that the fields in your table that you want to contain redundent data actually have a unique constraint on them so that they don't accept redundent data. This happens automatically if the field is a Primary Key.

If the problem field is not the Primary Key, then you may be OK removing the constraint, if it is the Primary Key, then it must be unique beause it's is being used to uniquely identify the record. In this case you would need to either find a different Primary Key (which must be unique) or put your redundent data in a differnt field.

If you data is very redundent, then you may need to consider normalizing it by putting it into multiple tables to eliminate redundent entries. SSIS can do this as part of the transformation step. You can find information about what database normalization is and how to do it. Consider starting here to learn more.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Reducing database size after dropping text column

Hello,

A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.

I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.

Does anyone know what I should run?

Thanks,
TomAre you talking about DBCC Shrinkdatabase?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.c om...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.c om...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
> more details. But as Kevin states this will not shrink the db, it just
> reclaims the wasted space from the dropped text column.

Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?

Thanks,
Tom|||Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311061632.17a824e@.posting.google.co m...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> > I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL
for
> > more details. But as Kevin states this will not shrink the db, it just
> > reclaims the wasted space from the dropped text column.
> Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
> in the database where the text columns were removed. No filesize
> change as you indicated. I then did the 'shrinkdatabase' and even the
> 'shrinkfile' commands, and the database size is still almost 3GB, same
> as the database that is on my SQL Server 7 machine. Any other ideas?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> Do you know for sure there is enough free space to effectively shrink the
> db? Is SQL Server still reporting the size of that table to be what it was
> before you dropped the columns? Do you have a clustered index on the table?
> IF so you might want to do a DBREINDEX and see if that helps any.

Hi Andrew,

Thanks for the help on this, it's quite bizarre.

There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)

I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.

At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.

Thanks,
Thomas|||One other point that may be a factor here. You can only shrink the db and
log file down to the size it was originally created at and no more. So if
the db was created at 3GB you can run shrink all you want and nothing will
happen. Does Shrinkfile show any estimated pages that can be removed?

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311081249.52f030cb@.posting.google.c om...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> > Do you know for sure there is enough free space to effectively shrink
the
> > db? Is SQL Server still reporting the size of that table to be what it
was
> > before you dropped the columns? Do you have a clustered index on the
table?
> > IF so you might want to do a DBREINDEX and see if that helps any.
> Hi Andrew,
> Thanks for the help on this, it's quite bizarre.
> There is definitely enough free space on the disk, if that is what you
> mean. I didn't check the individual table sizes before I did the
> cleantable commands, so I'm not sure if they are being reported as
> different, but the size of the entire database is still the same or
> even slightly larger! (?)
> I do have a clustered index on all the tables that had text columns
> dropped, and I performed the dbreindex on all those tables, then did
> another 'shrinkdatabase', and the size of the db has not gone down at
> all.
> At one point I read another way to go about this is to use BCP, but
> I've not used that before so I will have to do some research.
> Thanks,
> Thomas

Reducing database size after dropping text column

Hello,
A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.
I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.
Does anyone know what I should run?
Thanks,
TomAre you talking about DBCC Shrinkdatabase?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.com...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.com...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
> more details. But as Kevin states this will not shrink the db, it just
> reclaims the wasted space from the dropped text column.
Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?
Thanks,
Tom|||Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311061632.17a824e@.posting.google.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> > I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL
for
> > more details. But as Kevin states this will not shrink the db, it just
> > reclaims the wasted space from the dropped text column.
> Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
> in the database where the text columns were removed. No filesize
> change as you indicated. I then did the 'shrinkdatabase' and even the
> 'shrinkfile' commands, and the database size is still almost 3GB, same
> as the database that is on my SQL Server 7 machine. Any other ideas?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> Do you know for sure there is enough free space to effectively shrink the
> db? Is SQL Server still reporting the size of that table to be what it was
> before you dropped the columns? Do you have a clustered index on the table?
> IF so you might want to do a DBREINDEX and see if that helps any.
Hi Andrew,
Thanks for the help on this, it's quite bizarre.
There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)
I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.
At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.
Thanks,
Thomas|||One other point that may be a factor here. You can only shrink the db and
log file down to the size it was originally created at and no more. So if
the db was created at 3GB you can run shrink all you want and nothing will
happen. Does Shrinkfile show any estimated pages that can be removed?
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311081249.52f030cb@.posting.google.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> > Do you know for sure there is enough free space to effectively shrink
the
> > db? Is SQL Server still reporting the size of that table to be what it
was
> > before you dropped the columns? Do you have a clustered index on the
table?
> > IF so you might want to do a DBREINDEX and see if that helps any.
> Hi Andrew,
> Thanks for the help on this, it's quite bizarre.
> There is definitely enough free space on the disk, if that is what you
> mean. I didn't check the individual table sizes before I did the
> cleantable commands, so I'm not sure if they are being reported as
> different, but the size of the entire database is still the same or
> even slightly larger! (?)
> I do have a clustered index on all the tables that had text columns
> dropped, and I performed the dbreindex on all those tables, then did
> another 'shrinkdatabase', and the size of the db has not gone down at
> all.
> At one point I read another way to go about this is to use BCP, but
> I've not used that before so I will have to do some research.
> Thanks,
> Thomas

Reducing column size

I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:

> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?