global code: code

Showing posts with label code. Show all posts

Tuesday, March 27, 2012

General network error

Hi guys,

Anyone encountered this error.

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.

Need your help...

thanks

I have the same error and we still do not have a solution. We have over 100 remote locations we are sending data to and just one of them this week started to give us this error. We are sending just 39 rows, all columns allow null values, nothing looks bad with the data. Can use ISQL to insert a row mannually, but using DTS or our ETL tool (with OLE DB) gives us the error.sql

Friday, March 23, 2012

GAC issue

I have .NET 1.1/Visual Studio 2003 and am trying to make the .NET C# code visible in SSIS -- I am a newcomer to GAC and did the following:

Set up a strong-name key pair

Put in the reference to it inside assemblyinfo

Put in PreBuild event to uninstall in GAC and PostBuild to install in GAC

All appeared fine

Set up project in SSIS

Put up Add Reference dialog and looked for project dll -- not there

I see from above something about putting the assembly into c:\windows\framework\... I do not have

a directory "framework" under "windows"

Is there another place I should put the assembly>

It references when I put the assembly in the V 2.0 dir, that is,

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

Note: this is a version 1.1 assembly. Would there be any

problems with just going ahead and doing this?

|||GAC is a runtime thing. Putting the assembly in the appropriate .NET folder allows you to reference it for debugging and intellisense.sql

Fuzzy Street Address Search Code

Anybody have any good code for this case...

Given @.address1 as a parameter.

Look in the providers table for providers that match that Address.

But it needs to be a fuzzy lookup. Meaning it has to find these to be matched

123 Fake St.

123 Fake Street

Even possible some misspellings like

123 Fke St would be nice as well.

I know it will possibly involve a Split function, and/or Soundex and Difference functions. But what is best?

I can't find any code when I google this subject.

Help?!?!?

You may be best served to establish FULL TEXT SEARCH for the address column.

Refer to Books Online, Topic: Full Text Search

|||

I just set up the full-text search... This does not seem to be helpful. I can't do a search like:

SELECT *

FROM providers

WHERE CONTAINS (sv_addr, '"123 Fake St"')

Because it will pull up nothing... However I would need to break up the values with some sort of Split function. Then even still it would not pull up ST as matching Street... or get me results spelled wrong like 123 Fke St.

Help?!?! Is there some functionality I'm missing for using a Full-text Search? Or does someone have code already. It would seem that this functionality is needed in all kinds of projects, and that my inquery would be answered completely immediately.

-Robert

|||

This underscores the need to have a good address validation process in place BEFORE bad and/or mis-spelled addressed are put into your server. You may wish to investigate using the US Postal Service web service for addresses and address correction.

I have assisted in incorporating the USPS web service in applications and the results have been excellent. For example, I can misspell my street name and it will still come back with the correct address. In addition, the Zip+4 is provided, there is cross-check between city and zipcode, abbreviations are standardized, etc.

https://secure.shippingapis.com/Registration/

http://www.codeproject.com/useritems/USPS_Web_Tools_Wrapper.asp

|||

umm... no. I have a legacy address database... and I'm hired to write code to search it. I'm not hired to correct the database. The code I'm looking for has to be out there, I just have to look hard enough... or someone will help me and post it.

Thank you for your suggestions.

|||

I've been there, spent quite a bit of time researching Name and Address issues (on a client's nickel of course.)

There are excellent third party 'add-ins' -very expensive though ($100k+) But not too much that really solves the problems.

Deconstruction can help, storing the address components in separate columns -but still a groaner...

Good luck, and keep us posted. Perhaps there is a new approach out there that would be wonderful to share.

|||bump|||There is no "good" way to do what you want with one field called "Address", with any number of values in it.

What I have done in the past is use an exact search for the value and if that does not return any values, use the SOUNDEX() function to try to match something. On large string lengths, this will have a very large number of hits.

Good luck
|||

Here is a beginning to what I want

Code Snippet

CREATE Function [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS int AS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100), -- street name in var1

@.b varchar(100), -- street name in var2

@.i int

select @.a = Value from dbo.Split(@.var1, ' ') where TokenID = 2

select @.b = Value from dbo.Split(@.var2, ' ') where TokenID = 2

set @.i = difference(@.a, @.b)

RETURN (@.i)

END

The Split function is available as well, if you don't have or understand. This will do a fuzzy match on whatever the second word in the street address is.

so 1) 123 2) Fake 3) Street

It'll fuzzy match 2. I think I can work with this till I get somewhere...

|||

Code Snippet

ALTER Function [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS int AS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100), -- street name in var1

@.b varchar(100), -- street name in var2

@.c varchar(100), -- street name in var1

@.d varchar(100), -- street name in var2

@.e varchar(100), -- street name in var1

@.f varchar(100), -- street name in var2

@.i char(1),

@.j char(1),

@.k char(1),

@.var1Tokens int,

@.var2Tokens int,

@.var1Plus int,

@.var2Plus int

set @.var1Plus = 0

set @.var2Plus = 0

select @.var1tokens = TokenID from dbo.Split(@.var1, ' ')

select @.var2tokens = TokenID from dbo.Split(@.var2, ' ')

set @.k = '0'

select @.a = Value from dbo.Split(@.var1, ' ') where TokenID = 1

select @.b = Value from dbo.Split(@.var2, ' ') where TokenID = 1

if @.a = @.b set @.k = '1'

select @.c = value from dbo.Split(@.var1, ' ') where TokenID = 2

select @.d = Value from dbo.Split(@.var2, ' ') where TokenID = 2

set @.i = Convert(varchar, difference(@.c, @.d))

if Convert(int, @.i) < 3 begin

if @.var1tokens > 3 begin

-- select @.c = @.c + ' ' + value from dbo.Split(@.var1, ' ') where TokenID = 3

select @.c = value from dbo.Split(@.var1, ' ') where TokenID = 3

set @.var1Plus = 1

end

if @.var2tokens > 3 begin

-- select @.d = @.d + ' ' + Value from dbo.Split(@.var2, ' ') where TokenID = 3

select @.d = value from dbo.Split(@.var1, ' ') where TokenID = 3

set @.var2Plus = 1

end

set @.i = Convert(varchar, difference(@.c, @.d))

end

select @.e = Value from dbo.Split(@.var1, ' ') where TokenID = 3 + @.var1Plus

select @.f = Value from dbo.Split(@.var2, ' ') where TokenID = 3 + @.var2Plus

if @.e is null or @.f is null or @.e = '' or @.f = ''

set @.j = '4'

else

set @.j = Convert(varchar, difference(@.e, @.f))

RETURN Convert(int, (@.k + @.i + @.j))

END

It works fine...

|||

And then, without address standardization, there will be:

123 N Fake St

123 N E Fake St

123 North East Fake St

123 Fake N

123 N Fake St S

As you will discover, there is no 'easy or simple' way to handle addresses unless you follow the USPS rules of standardization -and even then it will still be a major headache. (That's partially why the third party tools are so expensive.) Almost all situations where addressing is a mission critical part of the database structure, addresses are deconstructed into constuitent parts, Number, Direction, StreetName, StreetType, etc. -Think Fire, Police, 911 services. Fuzzy doesn't handle it.

A great many of us have had to tackle this problem. If you 'blow off' our suggestions, you most likely won't find a workable solution.

You would do your client a better service by opening the conversation about why and how to standardize the existing data. It is not a complex process.

|||

The above solution I have found does work. I complained of it's slow-ness but once you narrow down the field using Zip &/or city,state it becomes manageable and an acceptable slowness.

I understand your concerns about the USPS address. Our company has had clients that give us raw addresses from there database, and I've been on the task of turning those into distinct locations.

A lot of the time I have to eye-ball address after address and assign them the same location-id because of the above...
123 N fake st = 123 fake st = .... and so on.

In the future your suggestion does make sense... But for now a fuzzy search on the current database is acceptable.

Now you bring up another point however, a bit unrelated to the question.

The USPS would standardize the database and make it...

123 N Fake St...

And i have a guy search for 123 Fake St... and I would send that to get standarized...

How would the USPS system resolve the two above to be equal?

Would I then take the searched address and see if USPS has it on file? Because it would not match my now standardized database. Or would I take each search and get USPS to tell me the correct way to represent the search and then match it to my database?

-Robert

|||

If there was only a N Fake Street, the return is a standardized address.

If there is both a N and S Fake St, usually 123 would be one North (or South), and 125 would be the other. Rarely would exact same numbering scheme be both North and South. Or the ZIP will be used to determine which is correct.

Again, if address searching is mission critical, you MUST deconstruct

|||I agree with Arnie.

The way the USPS standardization service will return a standard address in the form:
House Number, Direction, Street Name, Modifier (DR, ST, AVE, etc), and city/st/zip, etc

The only way to do any kind of search is you have to know if you are looking for N or S or ST or DR or Ave or whatever. Then you prompt the user for 4 fields, which can have blanks representing "any". Then you can use SOUNDEX on the street name to find something similar.

|||

I spent quite a bit of time for several clients working out the name/address matching possibilities for very large databases. Here is a summary of the super-condensed executive briefing abstract. Wink

None of the available 'free' options can hold a candle to some of the third party products (a couple of which are 'Homeland Securty certified' and often paid for by Homeland Security for governmental requirements). But the cost is high. Obviously, some of these folks have made a large investment in working out name search algorythms, and they rightfully expect a 'good' ROI -and with HS's backing, they are getting it.

Soundex was created to solve a Census Bureau problem at the 20th century, and is biased toward northern european names (primary immigrant influx at the time.) That bias still exists and soundex does NOT handle asian, eastern european, and arabic names worth a crap.

Double Metaphone and NYSIIS are a 'recent' alternatives to soundex (there is documentation and SQL functions available on the 'interweb'.) Double Metaphone and NYSIIS are more robust than soundex(), doing a 'plausible' job handling eastern european, asian, and arabic names.

If you are attempting to 'roll you own' name/address search algorythms, DON"T! Explore using Double Metaphone instead. Don't use soundex() -you will be disappointed.

The Double Metaphone code is publically available, expand on it and publish your enhancements to the larger SQL Community. For addresses, use the USPS web service to standardize the address, and deconstruct the address, storing the 'street' name in its own column.

Fuzzy Street Address Search Code

Anybody have any good code for this case...

Given @.address1 as a parameter.

Look in the providers table for providers that match that Address.

But it needs to be a fuzzy lookup. Meaning it has to find these to be matched

123 Fake St.

123 Fake Street

Even possible some misspellings like

123 Fke St would be nice as well.

I know it will possibly involve a Split function, and/or Soundex and Difference functions. But what is best?

I can't find any code when I google this subject.

Help?!?!?

You may be best served to establish FULL TEXT SEARCH for the address column.

Refer to Books Online, Topic: Full Text Search

|||

I just set up the full-text search... This does not seem to be helpful. I can't do a search like:

SELECT*

FROM providers

WHERECONTAINS(sv_addr,'"123 Fake St"')

-Robert

|||

https://secure.shippingapis.com/Registration/

http://www.codeproject.com/useritems/USPS_Web_Tools_Wrapper.asp

|||

Thank you for your suggestions.

|||

I've been there, spent quite a bit of time researching Name and Address issues (on a client's nickel of course.)

There are excellent third party 'add-ins' -very expensive though ($100k+) But not too much that really solves the problems.

Deconstruction can help, storing the address components in separate columns -but still a groaner...

Good luck, and keep us posted. Perhaps there is a new approach out there that would be wonderful to share.

|||bump|||There is no "good" way to do what you want with one field called "Address", with any number of values in it.

Good luck
|||

Here is a beginning to what I want

Code Snippet

CREATEFunction [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS intAS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100),-- street name in var1

@.b varchar(100),-- street name in var2

@.i int

select @.a = Value from dbo.Split(@.var1,' ')where TokenID = 2

select @.b = Value from dbo.Split(@.var2,' ')where TokenID = 2

set @.i =difference(@.a, @.b)

RETURN(@.i)

END

The Split function is available as well, if you don't have or understand. This will do a fuzzy match on whatever the second word in the street address is.

so 1) 123 2) Fake 3) Street

It'll fuzzy match 2. I think I can work with this till I get somewhere...

|||

Code Snippet

ALTERFunction [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS intAS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100),-- street name in var1

@.b varchar(100),-- street name in var2

@.c varchar(100),-- street name in var1

@.d varchar(100),-- street name in var2

@.e varchar(100),-- street name in var1

@.f varchar(100),-- street name in var2

@.i char(1),

@.j char(1),

@.k char(1),

@.var1Tokens int,

@.var2Tokens int,

@.var1Plus int,

@.var2Plus int

set @.var1Plus = 0

set @.var2Plus = 0

select @.var1tokens = TokenID from dbo.Split(@.var1,' ')

select @.var2tokens = TokenID from dbo.Split(@.var2,' ')

set @.k ='0'

select @.a = Value from dbo.Split(@.var1,' ')where TokenID = 1

select @.b = Value from dbo.Split(@.var2,' ')where TokenID = 1

if @.a = @.b set @.k ='1'

select @.c = value from dbo.Split(@.var1,' ')where TokenID = 2

select @.d = Value from dbo.Split(@.var2,' ')where TokenID = 2

set @.i =Convert(varchar,difference(@.c, @.d))

ifConvert(int, @.i)< 3 begin

if @.var1tokens > 3 begin

-- select @.c = @.c + ' ' + value from dbo.Split(@.var1, ' ') where TokenID = 3

select @.c = value from dbo.Split(@.var1,' ')where TokenID = 3

set @.var1Plus = 1

end

if @.var2tokens > 3 begin

-- select @.d = @.d + ' ' + Value from dbo.Split(@.var2, ' ') where TokenID = 3

select @.d = value from dbo.Split(@.var1,' ')where TokenID = 3

set @.var2Plus = 1

end

set @.i =Convert(varchar,difference(@.c, @.d))

end

select @.e = Value from dbo.Split(@.var1,' ')where TokenID = 3 + @.var1Plus

select @.f = Value from dbo.Split(@.var2,' ')where TokenID = 3 + @.var2Plus

if @.e isnullor @.f isnullor @.e =''or @.f =''

set @.j ='4'

else

set @.j =Convert(varchar,difference(@.e, @.f))

RETURNConvert(int,(@.k + @.i + @.j))

END

It works fine...

|||

And then, without address standardization, there will be:

123 N Fake St

123 N E Fake St

123 North East Fake St

123 Fake N

123 N Fake St S

A great many of us have had to tackle this problem. If you 'blow off' our suggestions, you most likely won't find a workable solution.

You would do your client a better service by opening the conversation about why and how to standardize the existing data. It is not a complex process.

|||

The above solution I have found does work. I complained of it's slow-ness but once you narrow down the field using Zip &/or city,state it becomes manageable and an acceptable slowness.

I understand your concerns about the USPS address. Our company has had clients that give us raw addresses from there database, and I've been on the task of turning those into distinct locations.

A lot of the time I have to eye-ball address after address and assign them the same location-id because of the above...
123 N fake st = 123 fake st = .... and so on.

In the future your suggestion does make sense... But for now a fuzzy search on the current database is acceptable.

Now you bring up another point however, a bit unrelated to the question.

The USPS would standardize the database and make it...

123 N Fake St...

And i have a guy search for 123 Fake St... and I would send that to get standarized...

How would the USPS system resolve the two above to be equal?

-Robert

|||

If there was only a N Fake Street, the return is a standardized address.

Again, if address searching is mission critical, you MUST deconstruct

|||I agree with Arnie.

The way the USPS standardization service will return a standard address in the form:
House Number, Direction, Street Name, Modifier (DR, ST, AVE, etc), and city/st/zip, etc

|||

If you are attempting to 'roll you own' name/address search algorythms, DON"T! Explore using Double Metaphone instead. Don't use soundex() -you will be disappointed.

Monday, March 19, 2012

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Funky formatting of percentages in Excel export

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Functions and Execution Plan

The cost of query with usage of functions is as same as that of without
functions

In the below code, the query cost of insert is 0.02% and two select
statements costs same 0.04%

Declare @.t table(mydate datetime)
Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into @.t values(getdate())
set @.i=@.i+1EndSelect mydate from @.t
Select convert(varchar,mydate,112) from @.t

But I thought usage of convert function will take more query cost
What do you think of this?

MadhivananAdding a CONVERT() to the output is very little extra work, but I
suspect that you're referring to the fact that using a function on a
column in the WHERE clause can prevent MSSQL from using an index. That
can have a significant impact on the query plan, eg:

create table dbo.m (mydate datetime primary key)

Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into m values(getdate() + @.i)
set @.i=@.i+1
End

-- now run these two queries in the same batch

select *
from m
where mydate between '20100815' and '20100917'

select *
from m
where convert(char(8), mydate, 112) between '20100815' and '20100916'

On my test server, the first query takes 11% of the batch, the second
is 89% - although they are functionally equivalent, the first one can
do a seek in the clustered index, but the second must scan it. That's
not to say that functions in the SELECT will never affect the query
plan or cost, but when reviewing code it's probably more important to
look at the WHERE clause first.

Simon|||Thanks Simon

So only in Where condition it affects the performance and not in select
isnt it?

Madhivanan|||I'm sure that functions in the SELECT clause can affect the query cost
- nested string functions, nested CASE expressions, a scalar UDF which
looks up other tables etc. And all other things being equal, "SELECT
col1" will be more efficient than "SELECT somefunc(col1)", simply
because MSSQL has less work to do.

But the difference may be extremely small (as in your example), and I
guess that in most cases, any really big differences in performance
would come from functions in the WHERE clause, not the SELECT clause.
Of course there are many other reasons why a query might run slowly -
missing indexes, out-of-date statistics and so on - which have nothing
to do with functions at all, so if you have a performance problem with
a specific query, then it's best to start by looking at the query plan
before you think about how to re-write the code.

Simon|||Madhivanan (madhivanan2001@.gmail.com) writes:
> So only in Where condition it affects the performance and not in select
> isnt it?

What matters is that if you put an indexed column into an expression,
the index can no longer be used for searches.

If you have

where mydate between '20100815' and '20100917'

and there is an index on mydate, SQL Server can use that index to
find the matching rows. But if you say:

convert(char(8), mydate, 112) between '20100815' and '20100916'

that index can no longer be used, because that index holds datetime
values, and this is a string expression.

Note that if mydate is not indexed, the only cost for the function
call is the function call itself. In this case, it's not more expensive
that having it in the SELECT list. (Except that if it's in a WHERE
clause, it may be applied to more values.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Well
Thanks for the suggesstions

Madhivanan

Monday, March 12, 2012

Function TRIM does not exist?

Hi;
i have this code for my data tab on one of my reports, im getting an error
that says TRIM function does not exists...
Any ideas?
declare @.MESACTUAL INT,@.MESANTERIOR INT;
declare @.ANOACTUAL INT,@.ANOANTERIOR INT;
declare @.MESACT CHAR(2),@.MESANT char(2);
SET @.MESACTUAL=MONTH(@.MES)
SET @.ANOACTUAL=YEAR(@.MES)
IF @.MESACTUAL=12
BEGIN
SET @.MESANTERIOR=1
SET @.ANOANTERIOR=(@.ANOACTUAL-1)
END
ELSE
BEGIN
SET @.MESANTERIOR=(@.MESACTUAL-1)
SET @.ANOANTERIOR=@.ANOACTUAL
END
SET @.MESACT=CONVERT(char(2),@.MESACTUAL)
SET @.MESANT=CONVERT(char(2),@.MESANTERIOR)
IF @.MESACTUAL < 10
begin
SET @.MESACT='0' & Trim(@.MESACT)
end
IF @.MESANTERIOR < 10
begin
SET @.MESANT='0' & Trim(@.MESANT)
end
IF @.MESACTUAL>1
BEGIN
exec('Select c.*,(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
s.s'+@.MESACT+' else 0 end) as Deudor,'+
'(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
s.s'+@.MESACT+' else 0 end) as Acreedor,'+
's.C'+@.MESACT+' as CargosActual,s.A'+@.MESACT+' as
AbonosActual,'+
'(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
s.s'+@.MESANT+' else 0 end) as AcreedorAnt,'+
'(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
s.s'+@.MESANT+' else 0 end) as DeudorAnt '+
' from GrupoVilla.dbo.CuentasContables c INNER JOIN '+
@.BASEDATOS+'.dbo.cnt_Saldos s ON
s.id_CuentasContables=c.id_CuentasContables where 1=1'
)
ENDtry rtrim and ltrim
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%23s6Knw0ZHHA.2436@.TK2MSFTNGP06.phx.gbl...
> Hi;
> i have this code for my data tab on one of my reports, im getting an error
> that says TRIM function does not exists...
> Any ideas?
> declare @.MESACTUAL INT,@.MESANTERIOR INT;
> declare @.ANOACTUAL INT,@.ANOANTERIOR INT;
> declare @.MESACT CHAR(2),@.MESANT char(2);
> SET @.MESACTUAL=MONTH(@.MES)
> SET @.ANOACTUAL=YEAR(@.MES)
> IF @.MESACTUAL=12
> BEGIN
> SET @.MESANTERIOR=1
> SET @.ANOANTERIOR=(@.ANOACTUAL-1)
> END
> ELSE
> BEGIN
> SET @.MESANTERIOR=(@.MESACTUAL-1)
> SET @.ANOANTERIOR=@.ANOACTUAL
> END
> SET @.MESACT=CONVERT(char(2),@.MESACTUAL)
> SET @.MESANT=CONVERT(char(2),@.MESANTERIOR)
> IF @.MESACTUAL < 10
> begin
> SET @.MESACT='0' & Trim(@.MESACT)
> end
> IF @.MESANTERIOR < 10
> begin
> SET @.MESANT='0' & Trim(@.MESANT)
> end
>
>
> IF @.MESACTUAL>1
> BEGIN
> exec('Select c.*,(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> s.s'+@.MESACT+' else 0 end) as Deudor,'+
> '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> s.s'+@.MESACT+' else 0 end) as Acreedor,'+
> 's.C'+@.MESACT+' as CargosActual,s.A'+@.MESACT+' as
> AbonosActual,'+
> '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> s.s'+@.MESANT+' else 0 end) as AcreedorAnt,'+
> '(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> s.s'+@.MESANT+' else 0 end) as DeudorAnt '+
> ' from GrupoVilla.dbo.CuentasContables c INNER JOIN '+
> @.BASEDATOS+'.dbo.cnt_Saldos s ON
> s.id_CuentasContables=c.id_CuentasContables where 1=1'
> )
> END
>|||On Mar 15, 5:00 pm, "Jeje" <willg...@.hotmail.com> wrote:
> try rtrim and ltrim
> "Willo" <willobe...@.yahoo.com.mx> wrote in message
> news:%23s6Knw0ZHHA.2436@.TK2MSFTNGP06.phx.gbl...
> > Hi;
> > i have this code for my data tab on one of my reports, im getting an error
> > that says TRIM function does not exists...
> > Any ideas?
> > declare @.MESACTUAL INT,@.MESANTERIOR INT;
> > declare @.ANOACTUAL INT,@.ANOANTERIOR INT;
> > declare @.MESACT CHAR(2),@.MESANT char(2);
> > SET @.MESACTUAL=MONTH(@.MES)
> > SET @.ANOACTUAL=YEAR(@.MES)
> > IF @.MESACTUAL=12
> > BEGIN
> > SET @.MESANTERIOR=1
> > SET @.ANOANTERIOR=(@.ANOACTUAL-1)
> > END
> > ELSE
> > BEGIN
> > SET @.MESANTERIOR=(@.MESACTUAL-1)
> > SET @.ANOANTERIOR=@.ANOACTUAL
> > END
> > SET @.MESACT=CONVERT(char(2),@.MESACTUAL)
> > SET @.MESANT=CONVERT(char(2),@.MESANTERIOR)
> > IF @.MESACTUAL < 10
> > begin
> > SET @.MESACT='0' & Trim(@.MESACT)
> > end
> > IF @.MESANTERIOR < 10
> > begin
> > SET @.MESANT='0' & Trim(@.MESANT)
> > end
> > IF @.MESACTUAL>1
> > BEGIN
> > exec('Select c.*,(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> > s.s'+@.MESACT+' else 0 end) as Deudor,'+
> > '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> > s.s'+@.MESACT+' else 0 end) as Acreedor,'+
> > 's.C'+@.MESACT+' as CargosActual,s.A'+@.MESACT+' as
> > AbonosActual,'+
> > '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> > s.s'+@.MESANT+' else 0 end) as AcreedorAnt,'+
> > '(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> > s.s'+@.MESANT+' else 0 end) as DeudorAnt '+
> > ' from GrupoVilla.dbo.CuentasContables c INNER JOIN '+
> > @.BASEDATOS+'.dbo.cnt_Saldos s ON
> > s.id_CuentasContables=c.id_CuentasContables where 1=1'
> > )
> > END
Here's an example:
ltrim(rtrim(@.MESACT))
Regards,
Enrique Martinez
Sr. SQL Server Developer|||SQL server doens't have TRIM, but RS has "TRIM" function, which can be used
in layout and not on Data tab. Just for your info.
Amarnath, MCTS.
"Willo" wrote:
> Hi;
> i have this code for my data tab on one of my reports, im getting an error
> that says TRIM function does not exists...
> Any ideas?
> declare @.MESACTUAL INT,@.MESANTERIOR INT;
> declare @.ANOACTUAL INT,@.ANOANTERIOR INT;
> declare @.MESACT CHAR(2),@.MESANT char(2);
> SET @.MESACTUAL=MONTH(@.MES)
> SET @.ANOACTUAL=YEAR(@.MES)
> IF @.MESACTUAL=12
> BEGIN
> SET @.MESANTERIOR=1
> SET @.ANOANTERIOR=(@.ANOACTUAL-1)
> END
> ELSE
> BEGIN
> SET @.MESANTERIOR=(@.MESACTUAL-1)
> SET @.ANOANTERIOR=@.ANOACTUAL
> END
> SET @.MESACT=CONVERT(char(2),@.MESACTUAL)
> SET @.MESANT=CONVERT(char(2),@.MESANTERIOR)
> IF @.MESACTUAL < 10
> begin
> SET @.MESACT='0' & Trim(@.MESACT)
> end
> IF @.MESANTERIOR < 10
> begin
> SET @.MESANT='0' & Trim(@.MESANT)
> end
>
>
> IF @.MESACTUAL>1
> BEGIN
> exec('Select c.*,(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> s.s'+@.MESACT+' else 0 end) as Deudor,'+
> '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> s.s'+@.MESACT+' else 0 end) as Acreedor,'+
> 's.C'+@.MESACT+' as CargosActual,s.A'+@.MESACT+' as
> AbonosActual,'+
> '(case when c.Naturaleza='+@.dato+'A'+@.dato+' then
> s.s'+@.MESANT+' else 0 end) as AcreedorAnt,'+
> '(case when c.Naturaleza='+@.dato+'D'+@.dato+' then
> s.s'+@.MESANT+' else 0 end) as DeudorAnt '+
> ' from GrupoVilla.dbo.CuentasContables c INNER JOIN '+
> @.BASEDATOS+'.dbo.cnt_Saldos s ON
> s.id_CuentasContables=c.id_CuentasContables where 1=1'
> )
> END
>
>

function to format Time (24 hours- 5 digits)

I know this is not a complicated matter.

I'm using this code in my SP to return the time formated in 24 hours

DECLARE @.Hora VARCHAR(5)
SET @.Hora = CONVERT(char(2), DatePart (hh,GetDate())) + ':' + CONVERT(char(2), DatePart (mi,GetDate()))
print @.Hora

te problem is that when the time is any before 10 am for instance 7:23 am the sp returns 7 :23 and I need it ro return 07:23.

What function allows me to insert that '0' to complete the 5 digits format in this case?

thanks

Try this instead:

SELECT My24HrTime = convert( char(5), getdate(), 114 )

For future reference, you may wish to refer to the 'style' chart in Books Online, Topic: 'Cast and Convert'.

(The number 114, above is the 'style' used in the convert() function.)

Function that returns table

Hello,
Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter?

This function will return a variable temp table...

SETANSI_NULLSOFF

SETQUOTED_IDENTIFIERON

CREATEFUNCTION [dbo].[UDF_ADMIN_GetNewSortList]

(

@.tText TEXT,

@.iSortStart INT

)

RETURNS @.NewList TABLE

(

iNewSortOrder INT

)

BEGIN

Declare @.tmpList TABLE

(

iOldSortOrder INT

)

INSERTINTO @.tmpList(iOldSortOrder)

SELECT

[cValue]

FROM

[CPDB].[dbo].[udfCharListToTable]

(

@.tText

,','

)

DECLARE @.iCount INT

SELECT @.iCount =Count(*)

FROM @.tmpList

DECLARE @.iStart INT

SET @.iStart = 1

WHILE @.iStart <= @.iCount

BEGIN

INSERTINTO @.NewList(iNewSortOrder)

VALUES(@.iSortStart + @.iStart)

Set @.iStart = @.iStart + 1

END

RETURN

END

|||Yes, but a table with one column. I want to return a table that has 0 or more columns (depends on the application). In your example the function returns a table with only one column (iNewSortOrder).
Now I have:
CREATE PROCEDURE dbo.GetConfiguration
(
@.type varchar(MAX)
)
AS
IF @.type = 'Tank' SELECT * FROM Tank();
That stored procedure returns a table. Now I want to use this table in a Function... eg:
CREATE FUNCTION dbo.Configuration
(
@.type varchar(max)
)
RETURNS TABLE
AS
RETURN SELECT * FROM GetConfiguration @.type
Obviously that doesn't work. Thanks for any ideas!!!
|||"That does not work" answers are welcome, too (so I know that there's no way) ;-)
|||

Looking at the options for Create Function there are two choices that return a table. The first is an in-line function. In this case you do not have to define the columns of the table, but you can only have a single select statement. So, that won't work.

It is possible to have a general multi-statement function that returns a table, but that requires that the columns be known in advance. Assuming that there was value in having a function that had constant columns, but came from different tables -- maybe you have a number of name-value-pair type tables -- you now have another problem of how to populate the table variable. The most general approach would be to use dynamic SQL, but you cannot populate table variables with dynamic SQL. You could use a series of IF statements, but now you are pretty far away from your original goal.

In general, this is not the correct approach to take.

Have you looked into using Dynamic SQL to solve your problem?

Friday, March 9, 2012

function selects

I have some code for a duntion that looks like the following
alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
@.sectorID VARCHAR(4),
@.manufacturerID VARCHAR(4),
@.rangeID VARCHAR(4),
@.bodyID VARCHAR(4),
@.transmissionID VARCHAR(4),
@.fuelID VARCHAR(4),
@.doors VARCHAR(4),
@.derivativeID VARCHAR(10),
@.vehicleTypeId VARCHAR(4),
@.levelName varchar(20))
returns @.levelTable table(surveyID VARCHAR(10),
code varchar(100),
description varchar(100)) AS
begin
if @.levelName = 'Sector'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblMarketSector.sectorID as code,
tblMarketSector.sector_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
ON tblMarketSector.sectorID = tblDerivative.sectorID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
else if @.levelName = 'Manu'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblManufacturer.manufacturerID as code,
tblManufacturer.manufacturer_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
What it does is based upon what the @.levelname variable is, it picks the
corresponding select statement and puts the result into a table to be
returned later to a stored procedure, the problem is I have only shown 2 of
these select statements but there are quite a few, I wanted to know if there
is a better way of writting this, perhaps using something like a case
statement.
Hope this is enough information.
ThanksIt looks like the only thing different between all the select statements is
which table you are getting the 2nd and 3rd column data from.. If that's the
case, (I don;t know how much simpler this will help or not, but)
you can include the joins from all the tables into one query, (They will
have to be Left [Outer] Joins), and then use a case statement to determine
which table to extract the data value from, based on the @.levelname
variable...
Insert @.levelTable(SurveyID, Code, Description)
Select Distinct tblSurveyDerivative.surveyID,
Case @.levelName
When 'Sector' Then tblMarketSector.sectorID
When 'Manu' Then tblManufacturer.sectorID
End,
Case @.levelName
When 'Sector' Then tblMarketSector.sector_desc
When 'Manu' Then tblManufacturer.sector_desc
End
From tblSurveyDerivative
Join basedata.dbo.tblDerivative tblDerivative
On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
Left Join basedata.dbo.tblMarketSector tblMarketSector
On tblMarketSector.sectorID = tblDerivative.sectorID
Left Join basedata.dbo.tblManufacturer tblManufacturer
On tblMarketSector.sectorID = tblDerivative.sectorID
Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
"Phil" wrote:

> I have some code for a duntion that looks like the following
> alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
> @.sectorID VARCHAR(4),
> @.manufacturerID VARCHAR(4),
> @.rangeID VARCHAR(4),
> @.bodyID VARCHAR(4),
> @.transmissionID VARCHAR(4),
> @.fuelID VARCHAR(4),
> @.doors VARCHAR(4),
> @.derivativeID VARCHAR(10),
> @.vehicleTypeId VARCHAR(4),
> @.levelName varchar(20))
> returns @.levelTable table(surveyID VARCHAR(10),
> code varchar(100),
> description varchar(100)) AS
>
> begin
> if @.levelName = 'Sector'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblMarketSector.sectorID as code,
> tblMarketSector.sector_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_
code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
> ON tblMarketSector.sectorID = tblDerivative.sectorID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
> else if @.levelName = 'Manu'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblManufacturer.manufacturerID as code,
> tblManufacturer.manufacturer_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
> ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
>
> What it does is based upon what the @.levelname variable is, it picks the
> corresponding select statement and puts the result into a table to be
> returned later to a stored procedure, the problem is I have only shown 2 o
f
> these select statements but there are quite a few, I wanted to know if the
re
> is a better way of writting this, perhaps using something like a case
> statement.
> Hope this is enough information.
> Thanks|||Hi CBretana,
Thanks very much for that, I had the principal right just couldn't seem to
write it down, thanks again for you help!!
Phil
"CBretana" wrote:
> It looks like the only thing different between all the select statements i
s
> which table you are getting the 2nd and 3rd column data from.. If that's t
he
> case, (I don;t know how much simpler this will help or not, but)
> you can include the joins from all the tables into one query, (They will
> have to be Left [Outer] Joins), and then use a case statement to determine
> which table to extract the data value from, based on the @.levelname
> variable...
> Insert @.levelTable(SurveyID, Code, Description)
> Select Distinct tblSurveyDerivative.surveyID,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sectorID
> When 'Manu' Then tblManufacturer.sectorID
> End,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sector_desc
> When 'Manu' Then tblManufacturer.sector_desc
> End
> From tblSurveyDerivative
> Join basedata.dbo.tblDerivative tblDerivative
> On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> Left Join basedata.dbo.tblMarketSector tblMarketSector
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Left Join basedata.dbo.tblManufacturer tblManufacturer
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
>
> "Phil" wrote:
>

Function Returning error during compilation.....

Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@.cg1 varchar(25)) returns @.rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @.t1_sys char(10),@.t1_all varchar(11)
declare @.temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@.cg1,1,2)='Q$')
set @.cg1 = (select substring(@.cg1,3,len(@.cg1)) where substring(@.cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @.temp_qcard values(@.cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
WHILE @.@.FETCH_STATUS = 0
BEGIN

insert into @.temp_qcard values(@.t1_all)

declare @.t2_sys char(10),@.t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t2_all)

declare @.t3_sys char(10),@.t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t3_all)
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @.rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @.temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ...
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.Try changing the declaration of all your cursors like this

DECLARE c1 SCROLL CURSOR READ_ONLY FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1

Dont write "FOR READ_ONLY" at the end of the DECLARE. Instead, write it before the "FOR select...."

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

function performance question

can anyone explain to me why the code excerpt 1 performs 60 reads on my DB, and code excerpt 2 performs 140000 ?

I know that specifically the statements are doing different things but they are both inserting into tables based on input parameters.

All relevant fields are indexed so I wouldn't have thought this was the issue?

Does the number of joins really make such a difference to performance?

code excerpt 1 (60 reads)
INSERT INTO @.table_var
SELECT dbo.Organisation.OrganisationName,
dbo.Organisation.DepartmentName,
dbo.Address.BuildingNumber,
dbo.BuildingName.BuildingName,
dbo.SubBuildingName.SubBuildingName,
Thoroughfare_1.ThoroughfareName AS DependentThoroughfareName,
ThoroughfareDescriptor_1.ThoroughfareDescriptor AS DependentThoroughfareDescriptor,
dbo.Thoroughfare.ThoroughfareName,
dbo.ThoroughfareDescriptor.ThoroughfareDescriptor,
dbo.Locality.DoubleDependentLocality,
dbo.Locality.DependentLocality,
dbo.Locality.PostTown,
dbo.Address.Outcode,
dbo.Address.Incode,
dbo.Address.ConcatenationIndicator
FROM dbo.Address INNER JOIN
dbo.BuildingName ON dbo.Address.BuildingNameKey = dbo.BuildingName.BuildingNameKey INNER JOIN
dbo.Locality ON dbo.Address.LocalityKey = dbo.Locality.LocalityKey INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType INNER JOIN
dbo.SubBuildingName ON dbo.Address.SubBuildingNameKey = dbo.SubBuildingName.SubBuildingNameKey INNER JOIN
dbo.Thoroughfare ON dbo.Address.ThoroughfareKey = dbo.Thoroughfare.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ON dbo.Address.ThoroughfareDescriptorKey = dbo.ThoroughfareDescriptor.ThoroughfareDescriptorK ey INNER JOIN
dbo.Thoroughfare Thoroughfare_1 ON dbo.Address.DependentThoroughfareKey = Thoroughfare_1.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ThoroughfareDescriptor_1 ON
dbo.Address.DependentThoroughfareDescriptorKey = ThoroughfareDescriptor_1.ThoroughfareDescriptorKey
WHERE (dbo.Address.AddressKey = @.addresskey) AND
(dbo.Address.OrganisationKey = @.organisationkey) AND
(dbo.Address.PostcodeType = @.postcodetype)

code excerpt 2:

INSERT INTO @.table_var_out
SELECT dbo.Organisation.OrganisationName, dbo.Address.OrganisationKey, dbo.Address.AddressKey, dbo.Address.PostcodeType
FROM dbo.Address INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType
WHERE (dbo.Address.Outcode = @.outcode) AND (dbo.Address.Incode = @.incode)Not all indexes are equal. Have you looked at the query plans for the select statements?

Function in select statement

How can I put a function in a select statement such as

SUM(code.GetValue( A, B, C, D, E)) AS TC_Reserve

I want to pass the function several values and have it perform a complex formula

and return a value. And then sum the value returned for each row.

This data is then used to create a chart "Dollars by Product Category"

Is this possible. I've only been at this for a week so I have no idea if it can done.

I get the message below

===============================================================

TITLE: Microsoft Report Designer

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.GetValue", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

It isn't possible to use a function that you've created in your report in a SQL statement. If you give an in-depth explanation of what you are trying to accomplish there may be a workaround I could help you with.

Another alternative is you could create a User Defined Function (UDF) and store it in your SQL Server database or you can create a stored procedure. Then you could reference it from a Select statement.

See this link for an intro to UDFs

http://msdn2.microsoft.com/en-us/library/ms179545.aspx

See this link for an intro to Stored Procedures

http://msdn2.microsoft.com/en-us/library/ms187451.aspx

|||

Thanks for the offer so here goes:

I need to pass

sales data (25 comma seperated value) - create an array (split function works great)

date of first activity

quantity on hand

factor (.59, .80. 1.00, blank, etc)

units (1.00, 60.0, blank)

months of sales to use ( 6 or 12)

function code (1 or 2)

cost (9999.9999)

what I forgot 1 (I'm sure I left 1 or 2 out)

what I forgot 2

=====================================================================

If function code = 1 then

if the date of first activity is < 365 days from today's date then

return 0.0

else

if the sum of the first 12 values in the sales data array = 0 then

return ( (qoh * (units * cost) ) * .90 )

else

if qoh > the sum of the first 6 values in the sales data array then

return ( (qoh * (units * cost) ) .50)

else

return 0.0

end

If fuction code = 2 then same as above but multiple the return value by the factor value

example: return ( ( (qoh * (units * cost) ) * .90 ) * factor )

Some of the data is string used as numeric values so it has to tested and converted to number.

Some of the string data could be blank so it has to tested and a default inserted.

Thanks for your efforts.

Note: Don't spend a lot of time on this it's not a required part of the report. I can do the basic report as above but I wanted to insert a chart (jazz it up and great learning experience) and I needed to have all the values done in the query so I can call it as a " jump to report". I got everything to work except the sum(function)) part. If I replace it with something like "sum(QOH) as TC_Reserve" it works great just the data is not correct.

|||

I think I understand what you want to do. This blog post has some details on a workaround. It's basically a custom aggregate hacked into Reporting Services:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Let me know if this will work for you or you have some questions.

|||You may want look into Calculated Fields. You could use one based on an expression, which calls your custom code. Calculated field expressions allow you to access other field values--they will be the values from the current row.

In Report Designer, to add a calculated field right-click on the Data Set, and choose Add.... In the Add New Field dialog enter the name for the field, choose Calculated field, and then enter the expression for the Calculated Field. In your case, the expression would look something like what you mentioned. Aggregates are not supported in Calculated field expressions, though.

Your query would not have a reference to this calculation, so you would simply remove it from the field list.

Ian

Wednesday, March 7, 2012

Function call in Dataset Query

Hello Guys,

I have a question that seems easy but I can not figure out...

Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.

I have a Dataset that has a calculation for a column within a select statement

Query Pseudocode:

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")

Custom Code:

Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function

How To use:

If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.

Problem:

How do I add this code reference in the following dataset select statement

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1

I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...

But it did not work...gave me this error:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

BUTTONS:

Help!

P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.

anyone...?|||

Hello,

Unfortunately, you can't use custom code in your SQL query (as you've found). What you can do is supply both fields in the calculation (cost and income) to the report and have it do the percentage, or create a 'SafeDiv' function in SQL and do it there.

Hope this helps.

Jarret

|||

On the DataSet

Use Generic Query Designer

Then you can use

="Select tableName.ProductID, "& code.Safediv(Parameters) & " as ColumnName

From tableName"

Try to adapts it to your report.

I hope it help you.

|||Thanks I will try it...|||

I think this will work...I will reply with result...

Thank You!

Sunday, February 26, 2012

Fully editable datagrid

I have been trying to formulate a fully editabe datagrid for a couple of days with no luck. I have used code from 4guys and some other sites and am at the point where I can render the datagrid correctly (as a bouncolumn and template column-textbox) but when I try to update the database it all falls apart. I am getting "input string was not in a correct format" and the error references ...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))

I suspect the problem lies in the area of primary indexes and such. The table I am using is a simple two-column table with usernames, passwords. Username is the primary field. Here is the actual code I am using...
<code>
<%@. Page Explicit="True" Language="VB" Debug="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<html>

Sub Page_Load(Sender As Object, E As EventArgs)

If Not IsPostBack Then
SelectStatement = "Select * From Table"
ConnectString = "Provider=SQLOLEDB;UID=;PWD=;" & "Data Source=;Initial Catalog=;"

Connect.ConnectionString = ConnectString
Adapter.SelectCommand = New oleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Connection.Open
Adapter.Fill(DS, "Items")
sls.Datasource = DS.Tables("Items")
Page.Databind
Connect.Close()
End If
End Sub

Sub Click(sender As Object, e As EventArgs)

Dim myConnection as New OleDbConnection(ConnectString)
Dim updateSQL as String = "UPDATE Table SET password = @.Password WHERE Username = @.ID"
Dim myCommand as New oleDbCommand(updateSQL, myConnection)

Dim dgi as DataGridItem
For Each dgi in sls.Items
'Read in the Primary Key Field
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
Dim password as String = CType(dgi.FindControl("txtPass"), TextBox).Text

'Issue an UPDATE statement...

myCommand.Parameters.Clear()
myCommand.Parameters.Add("@.ID", id)
myCommand.Parameters.Add("@.Password", password)

myCommand.ExecuteNonQuery()
Next

End Sub

</script>
<body>
<form runat="Server">

<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">

<Columns>
<asp:BoundColumn HeaderText="UserName" datafield="Username"/>

<asp:TemplateColumn HeaderText="Password">
<ItemTemplate>
<asp:TextBox runat="server" id="txtPass" Columns="10"
Text='<%# DataBinder.Eval(Container, "DataItem.Password") %>' />
</ItemTemplate>
</asp:TemplateColumn>

</Columns>
</asp:datagrid><asp:button id="Update" text="Update All" runat="Server" onclick="Click"/>
</form>
</body>
</html>
</code>
Anyone have any idea as to why the id variable (error message above) is causing problems?

Hi Choochmcgee,
You have the DataKeyField="UserName" in your datagrid setup rather than the Id...
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">
So when you try to pass the key field to the procedure with this line...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
it's going to error because it can't covnert an alphanumeric username to an int32. Change the DataKeyField to match your ID field and that should clear it up.|||

RJA,
Thanks for the response. I have datakeyfield set to "Username" because "Username" is the unique (primary key) column in that table. There are only two columns, username and password. I understand what you are saying about converting alphanumeric to integer but what would I use as the "ID field" you mentioned at the end of your post?

Thanks,
CM

Friday, February 24, 2012

Full-Text Search on Multiple Tables & Columns

Hi,

I have tried this code fromhttp://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.

Here's my code:

SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,
CONTAINSTABLE([tStaffDir], *, @.Name) as A
where
A.[KEY] = e.[ID] and
t.[ID] = e.[ID]

I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.
The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.

Please advise what I should do and look out for.

Many Thanks.

Will appreciate if anyone can advise. Thanks.|||

Seems like I am not able to get a answer.

Anyone able to point me in the correct direction?

Tuesday, March 27, 2012

General network error

Friday, March 23, 2012

GAC issue

Fuzzy Street Address Search Code

Fuzzy Street Address Search Code

Monday, March 19, 2012

Funky formatting of percentages in Excel export

Funky formatting of percentages in Excel export

Functions and Execution Plan

Monday, March 12, 2012

Function TRIM does not exist?

function to format Time (24 hours- 5 digits)

Function that returns table

Friday, March 9, 2012

function selects

Function Returning error during compilation.....

Function performances

function performance question

Function in select statement

Wednesday, March 7, 2012

Function call in Dataset Query

Sunday, February 26, 2012

Fully editable datagrid

Friday, February 24, 2012

Full-Text Search on Multiple Tables & Columns

global code

Blog Archive

About Me