Exec sproc in a update function

Folks

Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity ....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.

update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID

FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.

ThanxUse (create) function instead of sp in this case.|||Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx|||Originally posted by kir441
Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx

What about this draft?

drop table test
drop table test2
create table test(id int)
create table test2(id int, code varchar(10))
go
insert test values(1)
insert test values(2)
insert test values(3)
insert test2 values(1,'a')
insert test2 values(2,'b')
insert test2 values(3,'c')
go
CREATE FUNCTION getit(@.id int)
RETURNS varchar
AS
BEGIN
declare @.ret varchar(10)
select @.ret=code from test2 where id=@.id
RETURN @.ret
END
GO
select *,dbo.getit(id)
from testsql

Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret

exclude rows

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:

table 1
----
id
----
a
b
c
d
e
f
g

table 2
----
id / id2
----
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4

need results
----
id
----
a
e
g

any suggestions?

thanksSELECT * FROM tblOne WHERE id_field IN (SELECT id_field FROM tblTwo WHERE other_id = 1)

exclude names that have numbers

Eg:
Create table test
(col1 char(20))
insert test values ('test')
insert test values ('test1')
insert test values ('te1st')
insert test values ('tes')
Basically I want the output to return those values that do not contain a
number in the entire value..
So the output from above should only include
test
tes
How can I do so ?
ThanksTry
select * from test
where col1 not like '%[0-9]%'
Ben Nevarez, MCDBA, OCP
Database Administrator
"Hassan" wrote:

> Eg:
> Create table test
> (col1 char(20))
> insert test values ('test')
> insert test values ('test1')
> insert test values ('te1st')
> insert test values ('tes')
> Basically I want the output to return those values that do not contain a
> number in the entire value..
> So the output from above should only include
> test
> tes
> How can I do so ?
> Thanks
>
>

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROMQuote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
Ruz
Ruz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>
|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:

> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> graphically?!?!
> END),0)
>
>
|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"[vbcol=seagreen]
>
> Cheers,
> "Uri Dimant" wrote:
date[vbcol=seagreen]
this[vbcol=seagreen]

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:

> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> graphically?!?!
> END),0)
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"[vbcol=seagreen]
>
> Cheers,
> "Uri Dimant" wrote:
>
date[vbcol=seagreen]
this[vbcol=seagreen]

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:
> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > Hi,
> >
> > I'm trying to run a sproc that was created on SQL server and has two date
> > parameters. I can run the sproc easily enough with fixed values but it
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
> >
> > I have seen that the suggested solution is to create a view but as this
> > query uses group by, using the where clause in a view to specify the
> > parameteres will not give the correct result set.
> >
> > This is part of the SQL from the sproc.....
> >
> > SELECT SG.SchemeName as SchemeName,
> > 'Fail' as Type,
> > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> END),0)
> > as Value
> > FROM Quote Q
> > INNER JOIN SchemeGroup SG
> > ON Q.SchemeGroupID = SG.ID
> > INNER JOIN SubScheme SS
> > ON Q.ID = SS.QuoteID
> > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > GROUP BY SG.SchemeName
> > ORDER BY SG.SchemeName,Type desc
> >
> > Any help or ideas would be appreciated.
> >
> > Thanks,
> >
> > Ruz
> >
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"
>
> Cheers,
> "Uri Dimant" wrote:
> > Ruz
> > CREATE PROC myProc
> > @.SDate DATETIME,
> > @.FDate DATETIME
> > AS
> > --Your code here
> >
> >
> > If not sure I understand you
> > What does it mean?
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> >
> >
> >
> > "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> > news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > > Hi,
> > >
> > > I'm trying to run a sproc that was created on SQL server and has two
date
> > > parameters. I can run the sproc easily enough with fixed values but it
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> > >
> > > I have seen that the suggested solution is to create a view but as
this
> > > query uses group by, using the where clause in a view to specify the
> > > parameteres will not give the correct result set.
> > >
> > > This is part of the SQL from the sproc.....
> > >
> > > SELECT SG.SchemeName as SchemeName,
> > > 'Fail' as Type,
> > > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> > END),0)
> > > as Value
> > > FROM Quote Q
> > > INNER JOIN SchemeGroup SG
> > > ON Q.SchemeGroupID = SG.ID
> > > INNER JOIN SubScheme SS
> > > ON Q.ID = SS.QuoteID
> > > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > > GROUP BY SG.SchemeName
> > > ORDER BY SG.SchemeName,Type desc
> > >
> > > Any help or ideas would be appreciated.
> > >
> > > Thanks,
> > >
> > > Ruz
> > >
> >
> >
> >

Excel Transformation Problems

Hi guys,

I have encountered inconsistencies using the excel data source. When I try to transform some of the fields in excel, though it has values, it sometimes returns null. For example, I have this column below:

X
--

50

30
20

70
60

Sometimes it works, meaning I can get all the values, sometimes I cant, which means it returns all null. Anybody encountered this issue before?

By the way, it only happens if there is some empty values within the working cell.

Thanks.

Cheers,

Ryan Tan
Your column contains what the driver has deemed to be a mix of numeric and text values. It has ruled in favor of the majority (numeric?) and returns other cell values as null. This is an age-old issue with the Excel driver. Please see the BOL topic on the Excel Source for workarounds for common issues.

I can email you a more detailed known issues document (than I was able to include in BOL) tomorrow if you'll remind me, please, at douglasl@.microsoft.com.

-Doug
|||Have a look at the article below. This explains the age-old issue with Excel.

http://www.sqldts.com/default.aspx?254

Thanks

Sutha

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug