Query a Table to return Non-Date values

Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>

Query (count) question

I am somewhat new to SQL and I have a simple question (I think)... I have a field called results that contains several numbers seperated by columns, such as 3,6,16,22 etc.

I want to write a query that answers how many of each number occurs from the range.

Example:

Select (total) results where id = 6 and results = 16

so the query would have to search within the string results for all the records retrieved and count the instances

how would I do this?

I'd do the following. First you'll need a numbers table as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

now create a view on your table that splits out the numbers

create view SplitResults
as
select id,
cast(substring(results,
Number,
charindex(',',
results + ',',
Number) - Number) as int) as results
from MyTable
inner join Numbers on Number between 1 and len(results) + 1
and substring(',' + results, Number, 1) = ','
GO

Finally, run a query on the view to get your results

select count(*)
from SplitResults
where id=6 and results=16

|||Thanks, I new it was simple

query - specify multiple items

Hi,
I am trying to develop a report where the user is able to specify the
information to be pulled from one specific field.
For example... the table has 2 fields... Company ID, Company Name
I only want to see information where Company ID = ABC and XYZ
but I want the user to be able to specify multiple Company ID's in the
report parameter
Thanks,
JenI've got part of the query figured out:
SELECT Company ID, Company Name
FROM Company Profile
WHERE (Company ID (@.CompanyID))
But this only lets me query one company id at a time. how can I alter the
query to allow users to query multiple company id's?
thanks
jen
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:O6rTcrxZHHA.1300@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to develop a report where the user is able to specify the
> information to be pulled from one specific field.
> For example... the table has 2 fields... Company ID, Company Name
> I only want to see information where Company ID = ABC and XYZ
> but I want the user to be able to specify multiple Company ID's in the
> report parameter
> Thanks,
> Jen
>|||Hello Jennifer,
If you are using the SQL Server 2005, you could use the Multi-value
Parameter.
When you enable the Multi-value for the parameter, you could get a dropdown
list in the report.
Then you need to use the IN statement to query the data.
You could refer this article:
Adding a Multivalue Parameter
http://msdn2.microsoft.com/en-us/aa337396.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||We're currently running sql server 2000. i am using visual studio .net to
build the reports.
The way the query is build now, it allows the user to enter the parameters
in a seperate text box for each company id they query. how can i build the
query so that they can just enter a string of unlimited company id's?
thanks!
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OO42Wm4ZHHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jennifer,
> If you are using the SQL Server 2005, you could use the Multi-value
> Parameter.
> When you enable the Multi-value for the parameter, you could get a
dropdown
> list in the report.
> Then you need to use the IN statement to query the data.
> You could refer this article:
> Adding a Multivalue Parameter
> http://msdn2.microsoft.com/en-us/aa337396.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Jen,
You could use the parameter as the String and use the comma mark to
seperate each company id.
And in the query, you could use the parameter like this:
Select * from tbl_companies where companyid in (@.Parameters)
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||hi wei,
thanks so much for checking in. everything worked out great :)
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:5JytuQ7aHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello Jen,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

Query - All from one Table and 'TOP' from another

Hi - Have query structure language question
Hi have two tables,
Table A has a picklist for a field of limited options - say two, X, Y or Z
Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
but other data is entered, such as a date. So over time there will be many
entries with the linked field being either X, Y or Z, but with a timeline of
dates.
Want I want is a query that will return results for every item in the
picklist (in this case three, X,Y or Z and only the top value (most recent
date) for the date field in Table B.
(FYI - another Table C, links to table A where other fields must be equal.
Thus, what I hope to achieve is that, an entry in the Table C will be
assigned a link to table A, which will inturn return the most up to date
value in Table B.)
Hope someone can help. Ask if you need more detail.
Jon
Jonathan wrote:
> Hi - Have query structure language question
Unfortunately you didn't post any SQL. Instead you posted a word
puzzle. So we have to work out what you meant before we can answer you.
Posting some simple CREATE TABLE statements is a much better way to
describe a problem and it would have saved you some typing too :-)

> Hi have two tables,
> Table A has a picklist for a field of limited options - say two, X, Y or Z
> Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
> but other data is entered, such as a date. So over time there will be many
> entries with the linked field being either X, Y or Z, but with a timeline of
> dates.
> Want I want is a query that will return results for every item in the
> picklist (in this case three, X,Y or Z and only the top value (most recent
> date) for the date field in Table B.
> (FYI - another Table C, links to table A where other fields must be equal.
> Thus, what I hope to achieve is that, an entry in the Table C will be
> assigned a link to table A, which will inturn return the most up to date
> value in Table B.)
>
Here's my guess:
CREATE TABLE B (x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, dt
DATETIME NOT NULL, PRIMARY KEY (x,y,z,dt /* ? */));
/* Latest date for each x,y,z */
SELECT x,y,z, MAX(dt) AS dt
FROM B
GROUP BY x,y,z ;

> Hope someone can help. Ask if you need more detail.
DDL with keys and other constraints. Sample data (INSERT statements)
and show what end result you want based on that sample data. Also tell
us what version of SQL Server you have.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Query

I need a little help with this query.
I am querying the value of field, but I need to add to the logic that if
there is no record then the value = 1
This is the query as I have it:
SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
How do I tell the query that "ParamValue" = 1 if no result is returned ?
Thanks.
AleksTry,
select coalesce((select top 1 ParamValue FROM dbo.ParametersUsers where
UserId = 1713 and ParamName = 'showpopups'), 1) as ParamValue
AMB
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||Hi
Probably you are looking for this:
SELECT ISNULL(ParamValue,1), ...<other values>
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||"Aleks" schrieb:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
IF EXISTS (SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups')
SET @.ParamValue = 1
ELSE
SELECT @.ParamValue = ParamValue
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'|||Sorry - of course I meant:
IF NOT EXISTS (...sql

Query

Guys:
I run a query and get a field with 13 characters lenght. Is there any way to get or truncate the lenght of the field in the same scripts??. I need just 10 characters starting from the third one.
ThanksUse the right string function.|||How I can use the right string function??

How I can indicate the lenght and from what position to truncate??

Regards|||Do all the fields have a length of 13 ? Do you always want the 10 characters, starting with the 3rd character - no matter what the length is ?|||Yes, always 13 positions and always I will need 10 positions after third.

Regards,|||select right(fieldname, 10) from ...

Since you know the field is always 13 characters grabbing 10 characters will always start from the 3rd position:

e.g. fieldname = 'abcdefghijklm'
right(fieldname,10) = 'defghijklm'|||I will try it.

Thanks

query

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

Quote:

Originally Posted by priyankanair

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

At this point it's a bit unclear what you want to do.

If you want to have the same field in both tables you have to define them separately as such when defining the tables. If you already have them defined in both tables and want to use data from both tables at the same time, you have to join the tables. If you want to insert data to a new table, you need to use the proper INSERT statement.

Please provide a bit more info on the context and your goal so that we can help.|||

Quote:

Originally Posted by priyankanair

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

Could you please tell us, what you are trying to do.

Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:

>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
Hugo Kornelis, SQL Server MVP

Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:
>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
--
Hugo Kornelis, SQL Server MVP

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:

> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:

> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>

q; create index

I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
--
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||=?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

q; create index

I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||examnotes <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:

> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Q: write this query

Hello,
I have table T1 and F1, F2, F3, F4 and F5, and a Count field
Now I need to write a query that groups by fields and returns sum of count
for each field such as
F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, â?¦F5, Sum(Count)ForF5
How can I do this?On Wed, 19 Apr 2006 08:15:03 -0700, JIM.H. wrote:
>Hello,
>I have table T1 and F1, F2, F3, F4 and F5, and a Count field
>Now I need to write a query that groups by fields and returns sum of count
>for each field such as
>F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, ?F5, Sum(Count)ForF5
>How can I do this?
>
Hi Jim,
It's not clear what you need exactly. Can you post the table structure
(as CREATE TABLE statement, including constraints, properties and
indexes), some rows of sample data (as INSERT statements) and expected
results?
--
Hugo Kornelis, SQL Server MVP

Q: Tooltip

is it possible to display a field value in the tooltip. I put value there, it
does not show, it shows only text.Make sure the datatype of the tooltip expression is a string.
E.g. =CStr(Fields!SomeNumericField.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BF011F4D-C810-4FB2-A61B-0EEA75EC3A84@.microsoft.com...
> is it possible to display a field value in the tooltip. I put value there,
it
> does not show, it shows only text.

Q: sum of defined field

Hello,
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >