losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

Losing my parameters and fields?

Dear MSDN!
I have designed a report in VS 2005 Report Server Projekt. The report
connects to a Analysis 2005 server. Everything works fine in Layout tab and
preview tab until i click data tab. After i have clicked the data tab the
report starts to show errors in the output and error list window:
[rsFieldReference] The Value expression for the textbox
â'Sales_Price_Gross_Incl_Discount_1â' refers to the field
â'Sales_Price_Gross_Incl_Discountâ'. Report item expressions can only refer to
fields within the current data set scope or, if inside an aggregate, the
specified data set scope.
Visual studio have automatically checked out the report and changed the xml,
deleted the parameters and field definitions.
What is the problem?
Thanks in advance!Hello Grundh,
I found some similar issue in our internal database. But I did not found
the solution yet.
I am performing research on this issue and I appreciate your patience.
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.

Loosing primari keys when system crash

Hello,
When we our MS-SQL server has system crash a lot of all our primair keys are lost.
Some time even fields are lost.
We are running MS-SQL server on a cluster and on a stand alone server
Dos some one no the problem and nows a solution.What kind of the backup strategy do you use?

looping through field in table

Dear All

I have a table, and in one of the fields is the following information:
1,2,3,4,5, etc...

How do I 'loop' through this field to remove each comma and put the number into it's own field in another table - so remove the first comma, and put the number 1 into its own field, remove the 2nd comma and put the number 2 into it's own field etc, until all the numbers are in their own fields.

So it ends up being like this:

Col1 Col2 Col3 Col4 Col5 Col n.............
1 2 3 4 5 n...........

There may not be 5 numbers in the field, sometimes more, sometimes less, so i need to be able to tell when there are no more commas and numbers left

Your help is much appreciated

Thanks

GillI have a function for extracting the nth element of a delimited string, if you can use functions try this:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement
GO

CREATE FUNCTION GetStringElement(
@.String varchar(100)
, @.Element int
, @.Seperator char(1) = ',')

RETURNS varchar(100)
AS
begin
declare @.Pass int, @.Index int, @.LastIndex int, @.Return varchar(100)

if (@.String like '%' + replicate(',%',@.Element - 1)) begin
select @.Pass = 1
, @.Index = 1
, @.LastIndex = 0

while (@.Pass <= @.Element) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)
, @.Pass = @.Pass + 1
end

if (@.LastIndex > 0 and @.Index = 0) set @.Index = len(@.String) + 1

set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)
end

RETURN @.Return
end
GO

if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.pass int, @.ColCount int, @.TSQL varchar(255)
select @.pass = 1
, @.ColCount = 6
while (@.pass <= @.ColCount) begin
select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = dbo.GetStringElement(f2,' + cast(@.pass as varchar) + ',default)'
, @.pass = @.pass + 1
exec(@.TSQL)
end

select * from #psy

IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement

if object_id('tempdb..#psy') is not null
drop table #psy|||if functions are not your thing you could try:
if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.RecordID int, @.TSQL varchar(255), @.pass int
, @.Index int, @.LastIndex int, @.Return varchar(100)
, @.String varchar(100), @.Element int

select @.RecordID = min(f1) from #psy
while (@.RecordID is not null) begin
select @.String = f2 from #psy where f1 = @.RecordID

select @.Index = 1
, @.LastIndex = 0
, @.pass = 1

while (@.Index > 0) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)

if (@.LastIndex > 0 and @.Index = 0)
set @.Return = substring(@.String,@.LastIndex + 1, 100)
else
set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)

select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = ''' + @.Return + ''' ' +
'where f1 = ' + cast(@.RecordID as varchar)
, @.pass = @.pass + 1

exec(@.TSQL)
end

select @.RecordID = min(f1) from #psy where f1 > @.RecordId

end

select * from #psy

Looping database queries

Hi, newbie here:

I have created a small (5 fields) Access table which I will be
converting to MS-SQL Server format when the ASP code has been
completed. It is basically a collection of links to news releases from
mining companies. The group of people who will be browsing this
database want to know if the news release pertains to their area.
Sometimes the news release pertains to multiple areas if the mining
properties are scattered. Given the possibility of a one-to-many
relationship, ie one news release, many areas, I created an additional
table for the areas. I created the ASP code to pull down the news
release information, then loop through the area records such as:

set RSNewsRelease = Server.CreateObject("ADODB.Recordset")
NewsRelSQL = "Select date, company, title, newsreleaseID from
newsreleases;"

do while not RSNewsRelease.EOF
'display news release date, company and title
response.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &
RSNewsRelease(2).Value

'loop through areas
set RSAreas = Server.CreateObject("ADODB.Recordset")
'run query
do while not RSAreas.EOF
'display areas
Loop
set RSAreas = nothing
Loop

In other words, the only way I could get the results I wanted was to
set the Recordset to nothing, then reset it with each iteration of the
outer loop.

Is there a better way to do this?

JulesJules (julian.rickards@.ndm.gov.on.ca) writes:
> I have created a small (5 fields) Access table which I will be
> converting to MS-SQL Server format when the ASP code has been
> completed. It is basically a collection of links to news releases from
> mining companies. The group of people who will be browsing this
> database want to know if the news release pertains to their area.
> Sometimes the news release pertains to multiple areas if the mining
> properties are scattered. Given the possibility of a one-to-many
> relationship, ie one news release, many areas, I created an additional
> table for the areas. I created the ASP code to pull down the news
> release information, then loop through the area records such as:

It would probably be more effecient to bring up all information in
in one query:

SELECT nr.date, nr.company, nr.title, a.area
FROM newsreleases nr
JOIN areas a ON nr.newslreaseid = a.newsrleaseid
ORDER BY nr.date, nr.company, nr.title

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:

> It would probably be more effecient to bring up all information in
> in one query:
>
> SELECT nr.date, nr.company, nr.title, a.area
> FROM newsreleases nr
> JOIN areas a ON nr.newslreaseid = a.newsrleaseid
> ORDER BY nr.date, nr.company, nr.title

I basically understand your code - nr and a are aliases. The only
problem I have with your example is that, as I understand it, if a
newsrelease pertains to 3 areas, then this SQL code will result in
three "entries" in the recordset array such as (simplified):

June IBM New President Toronto
June IBM New President Cleveland
June IBM New President New York

If this is correct, I then have to find a way to cycle through the
identical recordsets (identical except for the area field). OK, just a
sec, I could add the newsrelease id to the SELECT statement and then
do a:

do while "id is the same"
response.write location
recordset.movenext
loop

I won't be back at work until Monday so I will have to wait till then
to try this out.

Thanks,

Jules|||Jules (julian@.jrickards.ca) writes:
> I basically understand your code - nr and a are aliases. The only
> problem I have with your example is that, as I understand it, if a
> newsrelease pertains to 3 areas, then this SQL code will result in
> three "entries" in the recordset array such as (simplified):
> June IBM New President Toronto
> June IBM New President Cleveland
> June IBM New President New York

Yes, this is what you would receive.

> If this is correct, I then have to find a way to cycle through the
> identical recordsets (identical except for the area field). OK, just a
> sec, I could add the newsrelease id to the SELECT statement and then
> do a:
> do while "id is the same"
> response.write location
> recordset.movenext
> loop

Yes, doing that sort of logic is not very complicated.

There is something called the Shape Provider in ADO, so that you can
bring up two related recordsets in one query. ADO is not my home ground,
and I've only read about shape, so I'm not providing any example.
And for many purposes a non-normalized recordset like this one is
the simplest way to go.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

loop over fields?

Hi,
i have to check all fields for a certain value, can I loop over all fields
in a custom code function?
Thx, NigelHello,
I have a similar situation as to the one you've described below. Did you
ever get an answer to your question or did you come up with a solution that
you can share?
Thanks,
Dave
"Nigel Jensen" wrote:
> Hi,
> i have to check all fields for a certain value, can I loop over all fields
> in a custom code function?
> Thx, Nigel
>
>

Loop a select statement?

Hi,
I have a select statement which brings back several fields and several
columns. Within one of these columns is an email address. I want to be
able to cycle through each record in the select statement and email the
details attached to their email address. My SP is below:-
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
I know you can use the following to send emails:-
@.sbj varchar(200),
@.msg varchar(2000),
@.recipient varchar(50)
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
but I want to incorporate that xp_sendmail with the select statement.
If anyone has ever done this before and can give me some pointers it would
be greatly appreciated.
Thanks
DamonI think you will need to use a cursor.
Here's some help with your SELECT statement, by the way. It's still largely
unreadable because of the superfluous prefixes and mismatched column names,
but it should be a little easier to tackle. Note that I changed the WHERE
clause to apply transformation to the constant, instead ofto the column, and
kept with the tradition of object-operator-value instead of
value-operator-object. getdate()>column+x is very difficult to process, at
least for me. If the table is huge, you may find an advantage in declaring
a variable of smalldatetime up front and calculating GETDATE()+7 and storing
it in a constant. However, if there is no index on sd_renewal_date, it's
probably all moot.
SELECT
Reg_No = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = d.sd_renewal_date,
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
If the cursor's only purpose is to send e-mail, then you probably want an
inner join against tbl_email. What is the point of getting rows where there
isn't a valid recipient?
I'd write the cursor for you, but it is entirely unclear to me how you are
deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
Please see http://www.aspfaq.com/5006
"Damon" <nonsense@.nononsense.com> wrote in message
news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
> Hi,
> I have a select statement which brings back several fields and several
> columns. Within one of these columns is an email address. I want to be
> able to cycle through each record in the select statement and email the
> details attached to their email address. My SP is below:-
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
> I know you can use the following to send emails:-
> @.sbj varchar(200),
> @.msg varchar(2000),
> @.recipient varchar(50)
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> but I want to incorporate that xp_sendmail with the select statement.
> If anyone has ever done this before and can give me some pointers it would
> be greatly appreciated.
> Thanks
> Damon
>|||Thank you very much for your reply. I can see why you are a SQL Server MVP.
Unfortunately my works have not supplied me with SQL training so have had to
learn myself, thus the messy code.
The @.sbj would be the same everytime, something like "List of renewal
dates". @.msg would literally be a compilation of the other fields i.e.
Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
being emailed can see the list of renewals that they have which are due up
within the next w.
I really appreciate your help. Need to get on to my works to send me on a
SQL course.
Thanks again
Damon.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>I think you will need to use a cursor.
> Here's some help with your SELECT statement, by the way. It's still
> largely unreadable because of the superfluous prefixes and mismatched
> column names, but it should be a little easier to tackle. Note that I
> changed the WHERE clause to apply transformation to the constant, instead
> ofto the column, and kept with the tradition of object-operator-value
> instead of value-operator-object. getdate()>column+x is very difficult to
> process, at least for me. If the table is huge, you may find an advantage
> in declaring a variable of smalldatetime up front and calculating
> GETDATE()+7 and storing it in a constant. However, if there is no index
> on sd_renewal_date, it's probably all moot.
> SELECT
> Reg_No = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = d.sd_renewal_date,
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> If the cursor's only purpose is to send e-mail, then you probably want an
> inner join against tbl_email. What is the point of getting rows where
> there isn't a valid recipient?
> I'd write the cursor for you, but it is entirely unclear to me how you are
> deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
> Please see http://www.aspfaq.com/5006
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
>|||I've taken an example cursor from Books Online and adjusted it somewhat to
fit your situation. However, it's just a rough draft and you will need to
complete it. Basically, the cursor allows you to iternate through the query
result one row at a time, giving you the ability to populate variables and
exec the xp_sendmail call. Every column that you plan to reference will need
to be assigned a variable. I've also added the FAST_FORWARD option so it
should use less resources.
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sbj = ?
select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
"Damon" <nonsense@.nononsense.com> wrote in message
news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
> Thank you very much for your reply. I can see why you are a SQL Server
> MVP. Unfortunately my works have not supplied me with SQL training so have
> had to learn myself, thus the messy code.
> The @.sbj would be the same everytime, something like "List of renewal
> dates". @.msg would literally be a compilation of the other fields i.e.
> Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
> being emailed can see the list of renewals that they have which are due up
> within the next w.
> I really appreciate your help. Need to get on to my works to send me on a
> SQL course.
> Thanks again
> Damon.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||>> I have a select statement which brings back several fields and several co
lumns. <<
Interesting, since SQL does not have fields and columns are a totally
different concept.
More interesting, since SQL does not have records and rows are a
totally different concept. Tables are sets and not files;
sets by definition have no ordering, so cycles make no sense
whatsoever.
You need to use a cursor (explicit or hidden in a called procedure) to
convert the result table into a sequential structure that can have
loops. While you are catching up on the foundations of RM, you might
also want to learn ISO-11179 so that you stop using that silly "tbl-"
in your code, Standard SQL keywords, etc. You are writing SQL like a
procedural or OO programmer because you have not got the right mindset
yet.|||CELKO,
As I mentioned in my previous message, I have not had any official training
in SQL as my employer has not yet put me on a course, I have had to try and
learn this by myself so you will have to forgive my wording and code. I am
a VB programmer so most of the stuff I do I do in VB as I do not know SQL
very well and I find it easier to do in VB.
Thanks for your reply.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134664262.456898.41440@.g14g2000cwa.googlegroups.com...
> Interesting, since SQL does not have fields and columns are a totally
> different concept.
>
> More interesting, since SQL does not have records and rows are a
> totally different concept. Tables are sets and not files;
> sets by definition have no ordering, so cycles make no sense
> whatsoever.
> You need to use a cursor (explicit or hidden in a called procedure) to
> convert the result table into a sequential structure that can have
> loops. While you are catching up on the foundations of RM, you might
> also want to learn ISO-11179 so that you stop using that silly "tbl-"
> in your code, Standard SQL keywords, etc. You are writing SQL like a
> procedural or OO programmer because you have not got the right mindset
> yet.
>|||JT,
Thank you very much for your reply. I really appreciate your time in
replying in such detail.
I will have a crack @. this today.
Thanks again.
"JT" <someone@.microsoft.com> wrote in message
news:eNhERNZAGHA.2560@.TK2MSFTNGP12.phx.gbl...
> I've taken an example cursor from Books Online and adjusted it somewhat to
> fit your situation. However, it's just a rough draft and you will need to
> complete it. Basically, the cursor allows you to iternate through the
> query result one row at a time, giving you the ability to populate
> variables and exec the xp_sendmail call. Every column that you plan to
> reference will need to be assigned a variable. I've also added the
> FAST_FORWARD option so it should use less resources.
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
>
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sbj = ?
> select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
>

Lookup transformation problem

Hello all,

I needed to lookup some table values based on a join of two fields...

I've configured the lookup transform to get the values via a SQL statement to minimize loading time.

However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on

I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes

Any idea how to solve this problem?

Thanks in advance

DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||

Hello,

Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?

|||

lookup supplied many functions can cast datatype

i think you also can use convert or cast in sql statements

|||

Hello,

I've used Dataconversion and SQL functions to perform the requested conversions...

works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:

[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".

and

next line:

[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

However, I never changed the SQL command of the transform in the advanced tab...

Any Idea?

Thanks in advance

lookup over 2 databases and three tables

ok i know i shopuld really put up all the code, but was hoping this would be
a simple one i have a table called table 1 on db1 that contains fields named
sortcode, accountno, licence
on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
fiedl called id
tbl3 has a field called accountno and id. records in tb2 & 3 are linked by
the id field, what i need to do is find the licence from db1 wwhere the sord
conde = db2.tb2 sortcode and accountno = db2.tb3 accountnoHave you got Linked servers up between the 2 dbs?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> ok i know i shopuld really put up all the code, but was hoping this would
be
> a simple one i have a table called table 1 on db1 that contains fields
named
> sortcode, accountno, licence
> on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
> fiedl called id
> tbl3 has a field called accountno and id. records in tb2 & 3 are linked
by
> the id field, what i need to do is find the licence from db1 wwhere the
sord
> conde = db2.tb2 sortcode and accountno = db2.tb3 accountno|||the two databases are on the same server
"Jack Vamvas" wrote:

> Have you got Linked servers up between the 2 dbs?
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> be
> named
> by
> sord
>
>|||On Fri, 31 Mar 2006 05:32:02 -0800, Peter Newman wrote:

>ok i know i shopuld really put up all the code, but was hoping this would b
e
>a simple one i have a table called table 1 on db1 that contains fields name
d
>sortcode, accountno, licence
>on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
>fiedl called id
> tbl3 has a field called accountno and id. records in tb2 & 3 are linked by
>the id field, what i need to do is find the licence from db1 wwhere the sor
d
>conde = db2.tb2 sortcode and accountno = db2.tb3 accountno
Hi Peter,
Putting up all the code is really better. Especially since it allows us
to copy and paste your CREATE TABLE and INSERT statements and test our
proposed solutions, then check the results against your posted required
output. This also helps prevent misunderstandings about the
requirements. And finally, it clarifies the EXACT column names (in your
post, you're refering to one column as "sortcode" or "sord conde").
Oh, and BTW: "table 1" is a poor choice of tablename - the use of a
space in the name forces you to quote the name, which is frankly quite a
pain.
Anyway, here's an untested guess:
SELECT T1.licence
FROM db2.tb2
INNER JOIN db2.tb3
ON tb3.id = tb2.id
INNER JOIN db1."table 1" AS T1
ON T1.sortcode = tb2.sortcode
AND T1.accountno = tb3.accountno
Hugo Kornelis, SQL Server MVP

Lookup fields in SQL Server

I am new to using SQL Server and need some help creating lookup fields if it is possible.

I use the lookup wizard to create lookup fields in access. Can you do the same in SQL?

I have successfully created relationships in SQL but I want to lookup the related fields during data entry like in Access with a dropdown box with the available options.

You would have to write the code to populate the box, but it works the same in access. In fact, you can use access as a front-end to SQL Server tables. Create the tables in SQL Server, open an access project, and connect to the database, linking the tables in to access. Then build everything in Access just like you always have.

You can also use any other application, like Visual Basic or Web pages to create the same thing, but you'll need to learn one of those languages to do so.

Buck Woody

Lookup fields in SQL Server

I am new to using SQL Server and need some help creating lookup fields if it is possible.

I use the lookup wizard to create lookup fields in access. Can you do the same in SQL?

I have successfully created relationships in SQL but I want to lookup the related fields during data entry like in Access with a dropdown box with the available options.

You would have to write the code to populate the box, but it works the same in access. In fact, you can use access as a front-end to SQL Server tables. Create the tables in SQL Server, open an access project, and connect to the database, linking the tables in to access. Then build everything in Access just like you always have.

You can also use any other application, like Visual Basic or Web pages to create the same thing, but you'll need to learn one of those languages to do so.

Buck Woody

Lookup - no matched records

Hi All,

I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA

The line in the import text file like this:

00000023450000012345

in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB

In the Derived Column transformation,

set the column name IDA as expression: SUBSTRING(LINE,1,10)

set the column name IDB as expression: SUBSTRING(LINE,11,10)

In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.

The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.

In the sample above, if i run the sql in the SSMS:

Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'

there is one record retrived.

Any idea?

TIA

Are you casting the Derived Column to a DT_STR to match your char(10), or is it still set to DT_WSTR?

|||

I am using DT_STR

Thanks

|||

Have you used a data viewer immediately before the Lookup to validate that the values are what you expect?

Another potential issue is the collation settings on the database. Do you know what they are?

|||

Thanks again.

I put the data viewer before the Lookup and the data are displayed correctly. I am using the digit to present string, is it related with collation?

|||Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

But why I am able to retrive the correct records via Select in SSMS?

jwelch wrote:

Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

Whe i use Select SERVERPROPERTY(N'Collation'), it returns: Latin1_General_CI_AS

That means:

Case-insensitive, accent-sensitive, kana-insensitive, width-insensitive

is that?

jwelch wrote:

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

That one is unlikely to be causing this problem.

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema ?
This is urgent. Thnaks in advance.
Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca
|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema '
This is urgent. Thnaks in advance.Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>