Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Monday, March 26, 2012

Garbage appearing when displaying Oracle character fields

Hi,
I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
Does anyone have any ideas?
Thanks!
MelindaI found out that our issue was that we were using the Oracle 8.1.5 client to connect to an Oracle 9i database. This was causing garbage characters to appear in our reports. Once I upgraded to use the Oracle 9.1 client, it worked as expected. I did some digging and found a small line in the documentation that suggests the minimum client is Oracle 8.1.7 or later.
"Melinda" wrote:
> Hi,
> I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
> Does anyone have any ideas?
> Thanks!
> Melinda

Wednesday, March 21, 2012

Fuzzy Lookup and Case

Hi,

Could someone please help!

Im doing a fuzzy lookup based on 3 fields (Surname/DOB/Gender). The only difference between the two sets of data is the case of the first letter of the Surname.

Reference table has "Stuart" Lookup has "stuart", I have set Fuzzy Lookup Input for Surname to Ignore Case but still it won't match.

The DOB/Gender are Exsactly the same.

Why does this not work? I there a work around?

Many Thanks, Deano

This isn't a "fuzzy" workaround, but for the scenario you describe it may well work.

Try adding explicitly lower-cased versions of your comparison columns to your data flow source and to the lookup query. Use the standard Lookup transformation, with the lower-cased columns being used for the lookup matching. This will allow you to perform a case-insensitive match without the overhead of the Fuzzy Lookup transformation. If there is a match, send the rows down your data flow path. If there is not, send the rows down an error path, and then use the Fuzzy Lookup only on the error rows.|||No, still not working, I have looked at the data and converted them all to they same type (Unicode String/ String etc both now varchar(50) ) and they still don't match up .It works if I group all the datain one import but if import one of the records into the database then try and import the other it does not match (Same logic). I can't help but thing it is a data type issue but I can't see any difference.

Someone please help!

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.
Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>
|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields ar
e
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any
language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gb
l...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Dat
e_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>> Hello all
>> I have two fields Date and Time. I've just created a new filed called
>> Date_Time, I need put the old information in the new field. All fields
>> are DateTime type data.
>> Thanks a lot.
>

Funny search error

I have two fields I am searching on ... address and zipCode

When I have both pieces of data, everything is fine, i get back the expected results, but i am unable able to search on the individual pieces. Here is the SQL I am using. It's placed within a store procedure in Sqlserver that I amreferencing from my VB.net code... any help??


IF ((NOT @.address IS NULL) AND( NOT @.zipCode IS NULL))
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address AND ShpAddr_ZipCode = @.zipCode
END

ELSE IF NOT @.address IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address
END

ELSE IF NOT @.zipCode IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_ZipCode = @.zipCode
END

any help would be great. thanksMy guess is that your parameters (@.address and @.zipCode) contain empty strings, not NULLs.

You VB.NET code should be setting the parameter value to SQLString.Null if the user did not choose to use the parameter.

I would then also consider changing your stored procedure to simply this:


SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = ISNULL(@.address,ShpAddr_Address1) AND ShpAddr_ZipCode = ISNULL(@.zipCode,ShpAddr_ZipCode)

I make this suggestion for ease of code maintenance. I myself have gotten burned several times over the past month by not changing all relevant similar blocks of code in a stored procedure when the logic needed to change.

Terri|||I'm trying this right now as we speak, but my program refuses to reconize what a "SqlString" is. Is there anything special i have to import?|||It's in the System.Data.SqlTypes namespace, so you can either import the namespace or reference it with the fully qualified name: System.Data.SqlTypes.SqlString.Null.

Terri

Monday, March 19, 2012

Functionality Like Pivot Chart

There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?

TIA
DeanDiscover the joy of the 'matrix control' or as I like to think of it:

A design time pivot table, but much less fun to use.

You just set the groups on the columns or rows. Simple. You can even have (right click menu) subtotals.

If you need to have 'other data' displayed, you will need to fiddle about with the Inscope, and create phantom groupings.

see this:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

green bar matrix:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx|||Can the matrix control display data as a CHART?|||http://msdn2.microsoft.com/en-us/library/aa964128(SQL.90).aspx

Monday, March 12, 2012

Function to return "remaining" of field after it finds a character in the field.

Hi,

another problem I have is that have compounded fields in my sql table.

Example

product@.customer

I need a simple function to return "customer", so it should return the value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.

regards
JorgenSolutions was :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [sapserviceaccount].[UNTRUNK2] (@.inp as varchar(100))

RETURNS varchar(20) AS

BEGIN

declare @.out varchar(20);

if LEN(@.inp) 0

begin

set @.out = right( @.INP, (LEN(@.INP)-CHARINDEX('@.', @.INP)))

end

else

begin

set @.out = @.inp;

end

return @.out

END

"Jorgen [DK/2600]" <nyhedsgruppe_hejhej_@.gmail.comwrote in message
news:45daf1e7$0$90272$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi,
>
another problem I have is that have compounded fields in my sql table.
>
Example
>
product@.customer
>
I need a simple function to return "customer", so it should return the
value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.
>
regards
Jorgen
>
>
>

Function to convert a 'date range' to table of starting and ending

I have a table that contains two fields: effectiveFrom, effectiveTo.
The time elapsed between these day could be greater than one year.
I would like to develop a UDF that would convert these dates into a
table of values. For example, if the dates are 7/1/03 and 4/1/06 the
resulting table would look like:
startingDate endingDate
07/01/03 12/31/03
01/01/04 12/31/04
01/01/05 12/31/05
01/01/06 04/01/06
I am confident that I can do this in a stored procedure, but I'm having
difficulty in a table-set UDF.
Suggestions appreciated.
Craig BuchananAdi-
Not exactly what I was looking for, but useful in other situations.
Thanks a lot for your response.
Craig
Adi wrote:
> You can do it this way:
> create function ShowDates (@.StartDate smalldatetime, @.EndDate
> smalldatetime)
> returns @.DatesTable table (DateCol smalldatetime)
> as
> begin
> while @.startDate <= @.EndDate
> BEGIN
> insert into @.DatesTable (DateCol) values (@.StartDate)
> set @.StartDate = dateadd(dd,1, @.StartDate)
> END
> return
> END
> If you are using SQL Server 2005, then you can use recursive CTE to do
> the same thing
>|||Razvan-
Perfect! Thanks a lot.
Craig Buchanan
Razvan Socol wrote:
> Hello, Craig
> You can use this function (after you give it a better name):
> CREATE FUNCTION YourFunctionName(
> @.EffectiveFrom datetime,
> @.EffectiveTo datetime
> )
> RETURNS @.Result TABLE (
> StartingDate datetime NOT NULL PRIMARY KEY,
> EndingDate datetime NOT NULL UNIQUE,
> CHECK (startingDate<=endingDate)
> ) AS BEGIN
> WHILE YEAR(@.EffectiveFrom)<YEAR(@.EffectiveTo) BEGIN
> DECLARE @.EndOfYear datetime
> SET @.EndOfYear=DATEADD(year,DATEDIFF(year,0,
@.EffectiveFrom)+1,0)-1
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EndOfYear)
> SET @.EffectiveFrom=@.EndOfYear+1
> END
> IF @.EffectiveFrom<=@.EffectiveTo BEGIN
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EffectiveTo)
> END
> RETURN
> END
> Razvan
>|||Uri-
Thanks for the reply. Not exactly what I needed, but I can use this in
another situation.
Thanks,
Craig
Uri Dimant wrote:
> Try this one
> CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> DECLARE @.rc AS INT
> SET @.rc = 1
> INSERT INTO @.Dates VALUES(@.from)
> WHILE @.from + @.rc * 2 - 1 <= @.to
> BEGIN
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> SET @.rc = @.rc * 2
> END
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> WHERE dt + @.rc <= @.to
> RETURN
> END
> GO
> SELECT dt FROM fn_dates('20060101', '20060130')
>
> "Craig Buchanan" <user@.example.net> wrote in message
> news:OAAuwYKMGHA.2992@.tk2msftngp13.phx.gbl...
>

Friday, March 9, 2012

Function required to convert Packed Decimal in binary(6) to int (or float)

I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.

The field contains values such as the following:-

0x20202020200C

0x202020022025

0x20202020DFFA

I don't know how to interpret these. Has anyone got a function that can do this for me?

I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?

Thanks!each of these is 6 bytes wide, which is too big for int (int is 32bit = 4bytes). you can fit them in bigint though.

to convert to bigint just use convert, eg: convert(bigint, 0x20202020200C)

they are already binary so you don't need to do anything special to convert to binary(6).|||Sorry, but you don't understand. They are encoded as "Packed Decimal" (or Binary-Coded Decimal). In that system 0x123456 translates to the integer (yes, integer!) 123456, although the system is a little more complicated than that, because it has special ways of representing negative numbers and floating-point values).

Sunday, February 26, 2012

Full-text usage?

Is it appropriate to use a full-text index and query to search CustomerName and CustomerDBA type fields? I've been told that this type of searching is meant for searching documents, but it seems that Full-text would be useful for fuzzy searching and noise word removal also. Does it make sense to index a varchar field? Does it add performace to searching over native 'Like' queries?

Thanks.

Full-text searching is great when you have multiple words in a column and you'd like to search on those individual words, or if you need to search on different tenses/forms of a word, eliminate noise words, or thesaurus capabilities. So yes, there are benefits to using it on varchar columns. As of SQL Server 2005 your full-text catalogs/indexes are included in your backups and restores so you don't have to rebuild your full-text indexes if you restore the database.

If you are searching on the full value or first few letters of the varchar, then a 'regular' index on the column would likely provide faster performance .. and in SQL Server 2005 we even improved the string searching for LIKEs that start with a wildcard.

Don

Sunday, February 19, 2012

Full-text search - catalog refuses to populate

Hello

I'm trying to get full-text searching to work on SQL Server 2000 (with SP3 installed).

I have a table containing many text fields that I want to search and so far have successfully gone through the "Full-Text wizard" selecting the text fields from the table I want as searchable. I then try and start full population of the catalog but its always remains zero in size when I check its properties. Hence when I try a query like:

SELECT Name FROM tblTextDocs WHERE CONTAINS(mainbody, 'find this')

It results in the error message:

Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.

It would appear that the catalog simply refuses to populate. I've tried starting population via Enterprise Manager and also by doing sp_fulltext_catalog 'MyCatalog', 'start_full'. The Full-text support service says it is running correctly and no other problems with the server have been experienced.

Any suggestions would be much appreciated.

Regards

SamBy the "Full Text Support Service" do you mean the "Microsoft Search" service?|||Originally posted by MCrowley
By the "Full Text Support Service" do you mean the "Microsoft Search" service?

Yes - the Microsoft Search service. I have no problems in stopping/starting it too.|||This error might occur if changing SQL Server Account to Non-Admin for Full-Text Search Makes Existing Catalogs Unusable.

To access existing full-text search catalogs, rebuild and repopulate them. The existing catalogs can also be accessed by switching back to an administrator account.